"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.

The past few months we have focused on building rules and detections in Chronicle using the entity graph. We are going to take a break from that because I believe that this topic will be a popular one for all Chronicle users. Today we are going to start looking at a new capability that has just been added to UDM search called Pivot. In fact there is so much goodness here, that this is only part one!

During my time working with Chronicle, I have been asked how Chronicle can generate statistical output.  Questions of this nature vary, but include:

• What are the top N values for a field?

• How many (choose an entity) were associated with an event?

• What was the average and standard deviation value?

Previously, Chronicle could answer some of these questions using the quick filters in UDM search or writing a rule in YARA-L, but with the introduction of pivot, a whole host of new capabilities are available within UDM search.

To get started pivoting within your data, we need to build a search and execute it. For our first example, we will use Zeek data to return HTTP data for the previous day. Pretty simple so far, huh? This gives us a dataset to engage with and analyze further. 

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

The UDM search looks much like it does for any search except that there is an item that says Pivot next to Event. Clicking on Pivot will open the Pivot Table Settings that we will work with.

The pivot table settings are broken into three sections; group by, values and order by. If we want a listing of the user agent strings and their associated event counts. We can click on the Add Field in the Group By section and select network.http_user_agent from our UDM picklist. Notice that as we type the field name, we get an autocomplete.

Because we are just looking for an event count in this example, we can click on Add Field and select Event count in the list. Order By fields will be based on what we have selected in the Group By and Values sections. For our example, we will sort ascending by the user agent.

Once we have made our selections for each of the three sections, we can click Apply and the pivot will generate our results based on the data in the underlying search. Here we can see that the bulk of the user agents are Microsoft-Delivery-Optimization/10.0, but there are a few that look a little out of place, like Havana/1.0.

With that basic pivot under our fingers, let’s try another one. This time, we are going to look at network connections based on our Zeek data.

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

Let’s generate a listing of IP address pairs, their associated event counts and the sent and received bytes summed by IP address pairs. This time, our group by section would be populated by two fields, principal.ip and target.ip. Under values, we will again choose Event count, but we will also add the fields network.received_bytes and network.sent_bytes. 

Notice in the values section, we have a number of Summarize By options in our picklist. For numeric fields like these two byte fields, we can use sum, average, standard deviation, min and max in addition to count and count distinct. For non-numerics, we would use count and count distinct. We will sort descending by event count in the order by section and click Apply.

Here we can see by event count our most commonly seen IP address pair is 10.10.20.60 and 10.10.60.100 but there are other pairs like 10.10.20.60 and 93.184.221.240 that have a lower event count but a much higher bytes received sum. Depending upon the question you are trying to answer, you have a few different ways to continue your investigation.

If we see something in our pivot that we no longer want to be considered, we can easily use in-line filters to rapidly remove and recompute our pivot. Let’s click Add Filter and select our field, the operator and value that we want to filter.

Notice that we chose the field ip. Wait? IP isn’t a field; target.ip, principal.ip, and src.ip are UDM fields, but what is ip? IP is a grouped field. That’s right, we can also search across multiple fields of the same type. Notice in the image below that the filter pop-up calls out that ip is a grouped field and the tooltip displays the fields that are part of the group field.

Once we are happy with our filter, we can click Apply and the pivot recomputes with the filter applied to our data set.

At the top of the page, we have the filtered event count as well as the unfiltered count. Also notice the capsule with the filter criteria and finally the result set with the same fields and sort as before, just a few less rows.

We are going to stop here for today, but this is really only scratching the surface of pivot. I’m really excited to see this come to Chronicle. In part two, we are going to explore some additional capabilities that pivot brings.