"New to Chronicle" is a deep-dive series by Google Cloud Principal Security Strategist John Stoner which provides practical guidance for security teams that are either new to SIEM or replacing their SIEM with Chronicle. You can view the entire series here.

In our last installment, we introduced the new UDM search and pivot capabilities. I would strongly suggest you take ten minutes and read that blog before diving into this one. 

Today, we will build on the examples we covered and explore some additional capabilities that pivot brings to our UDM search.

Like the last time, we will start with a basic search:

metadata.event_type = "NETWORK_CONNECTION" and metadata.vendor_name = "Zeek"

Our search collects all of the network connection events from our Zeek sensors. We have applied pivot to group by our principal and target IP addresses, generated a count and sum for sent and received bytes.

While this configuration provides us specific IP address pairs, let’s roll these values up into CIDR netblocks. To do this, we can click the drop down under Transform and select (IP) CIDR Prefix length in bits and specify an integer value. For our purposes, let’s set the principal.ip prefix to 16 and the target.ip to 22.

When we apply the new values to our pivot table, we can see that the first entry in our list has rolled up individual IP addresses into larger netblocks of 10.10.0.0/16 and 10.10.60.0/22 with the count and sum fields for bytes recalculated accordingly.

Let’s continue with this use case and apply time to our pivot. Our initial search will be bound by a time range. However, we can use time fields like metadata.event_timestamp within our pivot table to create buckets of data with their own statistical values.

To do this, we can add our timestamp field to the Group By section of the pivot. Notice there are a series of transforms from milliseconds to days along with an integer field. In our example, we are going to bucketize all IP address pairs in five minute buckets by selecting (Time) Resolution in Minutes and setting that resolution to 5 which will update our calculations for each group.

When we update our pivot, we can see the IP address pairs with the new timestamp field added along with our calculations. Notice in the red box in the tabular section that we have sorted the pivot by timestamp ascending and the sum of the received bytes descending. Being able to quickly and easily look at groups of data in specific time buckets like this can help identify bursts of activity that could be drilled into further for additional investigation. Also notice that at the top of the screenshot we filtered out IPv6 addresses just to tighten up our result set. This was done with an inline filter but we could easily add that back into our result set if we wanted to.

Another handy transform is available to roll-up various levels of domains and summarize them. Let’s start with our search:

metadata.event_type = "NETWORK_HTTP" and metadata.vendor_name = "Zeek" With our results, we can pivot and group by target.hostname, get an event count and sort by event count descending to see the most frequently visited domains. Our results are below.

If we add the transform Top N-level Domain = 1 and run our pivot, notice that our result set changes to just the first level domain value, which would be the root.

Changing the transform to a value of two results in the output being grouped by the domain name. Alternatively, a transform of Get Registered Domain could be used as well. This provides an analyst the ability to easily focus on domain and additional subdomain levels without having to worry about performing additional parsing within their search, Chronicle does it for you!

We’ve spent a good deal of time using network data as we introduced pivot capabilities in UDM search, so let’s end with a user authentication example. Our search is looking at blocked user logins from Microsoft Security events, specifically failed login events with the event code 4625.

Doing some quick data analysis of the logs, we can see that the src.hostname is where a workstation is logging into a domain server, represented as principal.hostname. We will group on these two fields. From there, we can get an event count and generate a distinct count of the target.user.userid. Here are our results.   

Does anything appear odd?

We see a large number of failed logins from a specific workstation to a server (based on the event count) but we also see what is likely a larger than normal number of userids being used that one might expect from a workstation (based on the count distinct).

The new pivot capability in UDM search provides analysts the ability to quickly and easily aggregate events together by common fields and visualize this information. The ability to apply transforms to domains, timestamps and IP addresses also makes it simple to roll up these values. We look forward to continuing bringing more capabilities to Chronicle that improve your ability to hunt and investigate!