Events in a data table can easily be grouped to facilitate analysis. The result of grouping is a data table presenting all the different row value combinations of the grouped columns. Grouping is also required in order to subsequently apply aggregation operations on the data. Select the Group icon in the query toolbar and the Operations Over Columns window appears with the Group By option selected.
There are two different types of grouping:
- No temporal - Select No temporal in the Every field to get all the possible combinations of the columns added as arguments. For example, in the following example we have grouped the data using the Server and OperatingSystem columns as arguments to get all the possible combinations of operating systems and servers.
- Temporal - You can include a time period when you group data in order to facilitate data analysis. Select the period you want to group by in the Every field. Note that the more columns you add as arguments in a temporal grouping, the less information you will extract, since the result will look more and more like the original table. In this way you can, for example, see the different combinations of operating systems and IPs each 15 minutes.
Select the time period you want to use to group the events and any additional arguments (columns) you want to use to define the groups. Once grouped, the result will be a row for each unique combination of arguments and time period. After grouping the data, you can continue applying groups as many times as necessary. When you perform temporal groupings, any subsequent grouping must use increasing time intervals.
Example: temporal aggrupation
- Go to Data Search and run the following search: firewall.paloalto.traffic.
- Click the create new column button and create two new columns for Longitude and Latitude that we calculate based on the dstIP column values. Here's how to do it:
- Select Group from the table toolbar.
- Select 1 day in the Every field, and add the Longitude and Latitude columns as arguments.
- Click Group by to group the data. The final result contains all the combinations of those two columns (Longitude and Latitude) for each day grouped in one single line.
Example: Calculate the standard deviation of a set of averages
- Go to Data Search and run the following search: demo.ecommerce.data.
- Select Group from the toolbar and group the data every 30m. Click Group by.
- Now select Aggregation from the toolbar and choose the Average operation in the Aggregation field. Then select the timeTaken column as the Argument. Click Aggregate Function.
- Now, group the data again, this time every 1h.
- Finally, perform another aggregation. This time, select Standard deviation (biased) in the aggregation field and the timeTaken column as Argument again. The result is the standard deviation of the average values of the timeTaken column, grouped every hour.