"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 Security Operations Platforms or replacing their Security Operations Platforms with Chronicle. You can view the entire series here.

In security operations, there are numerous questions asked that draw on the same data sets, but need to be visualized in slightly different ways. In our last blog, we built a tabular view of detections by severity for the past seven days. While that answers one question, it’s entirely possible the next question is, how many critical severity detections occurred each day for the past seven days? And while you are at, can you help me visualize detection severities by day in something other than a tabular view? These kinds of questions are the type that we are going to cover today and to do this we are going to use the pivot capability to build a crosstab, quickly followed by a time chart. Previously, we created a new visualization from scratch, but the ability also exists within dashboards to duplicate a tile and then edit it. Let’s start there. As always, make sure that we are in edit mode for the dashboard, so click the three buttons in the top corner of the dashboard and select Edit dashboard. From there, we can mouse over the tile that we want to duplicate. When we do that, the three dots in the upper right corner of the tile will appear and we can click on it, followed by Duplicate tile.

With that, a new, identical tile is created. The only difference is that (Copy) is appended to the end of the tile name. If your dashboard has multiple tiles already on it, you may need to scroll to the bottom of the dashboard to find it. On the new tile, mouse over the upper right corner, click on the three dots and then click Edit.

At this point, we are going to be in the Edit Tile pop-up window. This is the same interface we used in previous blogs to create our tiles. Let’s start by changing the name of our tile to Detections By Severity By Date - Past 7 Days.

We don’t need to modify our filters this time, but we do need different fields to address our requirements. If we are going to visualize this data by severity and by date, we probably don’t need both the integer and visualization for severity. Let’s remove the visualization by clicking on the cog in the top right corner of the column and select Remove.

We also need to add a date so that we can group our detections by both severity and date. We have used the text box called Find a Field on the left side of the pop-up in the past, but we can also click on the In Use sub-tab and view the fields we are currently using in our tile. Notice how the Date field is not in gray but the Filter button is. This is because the field is not in the data section, but it is being used in the filter. Let’s go ahead and click on the field in the list and it will be added to the data section.

Once we have made those changes, we can click Run to see what our data set would look like. I’m going to be honest and say I don’t love it. If I am trying to inform leadership around the daily status of detections, providing them an integer value is probably not ideal, nor is a listing of dates side by side with each integer.

So we have a little more work to do. We can start by going back to our field list and adding the field Severity and removing Severity Int because Severity is a text value of Info, Low, Medium, High and Critical where Security Int is the integer value for each severity value. If we click Run, we see that our data now is populated with a descriptive term for severity and if we expand our Visualization section and select area chart, we can even get a nice visualization. 

The problem though is that each count value is grouped by the date of the detection and severity which could get a little confusing to read. Ideally, we want to have a count of each severity for each day, which is where a crosstab comes into play. For those unfamiliar with crosstabs, this is a table containing two or more variables. To do this, we are going to use the pivot function. 

The first question is what field should we pivot on? This is going to depend on how we want to visualize our data. When building a pivot we will have variables represented both horizontally and vertically in our result set so some thought needs to be put toward this.

To demonstrate this, let’s first pivot on the detection date. If we click on the cog next to the date field and select Pivot, we will see our severity values appear as rows in our data section while each value in the date range will appear as columns. Because we are limiting our results to seven days with our filter condition, we will have seven columns, one for each day. When we click Run, we will get a count for each severity per day. 

In a tabular view, this may look nice and may meet our needs. Unfortunately, if we are trying to visualize the number of detections by severity by day, this doesn’t tell the story we are trying to tell. As we look at the area chart, we can see there are a large number of low severity detections, but understanding how many happened each day is a bit difficult to discern here.  Notice the values on the X axis of the visualization align with the Severity column in the Data section. If we are looking to visualize our data by day (or any other time value) in an area (or line) chart, we want that field to be represented as rows and instead pivot on the other value, in this case Severity.

Wait! Does that mean I need to start over if I pivot on the wrong field(s)?

Not at all, we’ve used the cog next to the field in the data section to hide data and remove columns; we can also use it to pivot or in this case unpivot a specific field. By the way, pivot can also be selected for a specific field from the field list just like a filter.

If we pivot on the severity and click Run, notice that our severity values are now across the top of the data table and our area chart takes our dates and maps them across the X axis of the area chart. The counts for each severity for each day are laid out across the graph as a count of each severity for each day. Now we have a tile our leadership can quickly consume!

We can further refine our chart or criteria if we need to test further, but we can click Save at this point and we will see our tile rendered in our dashboard. Remember to save your dashboard, even if you plan to build more tiles or modify your dashboard further so you don’t lose your work.

Today we took our existing tile, duplicated it and modified it to provide a time series based view of our detection severity broken out by day. Pivot is a powerful tool that can be used in dashboards to provide crosstab output for any number of datasets. Remember these tips when building dashboards for your team in Chronicle!