stats aggregate SIEM search output - using 'max(time)'

[Chris_B]

Hi Chroniclers

I have a search with log_types, hostnames, users, serial#s.  I'm creating time buckets by minute using this:

$date = strings.concat(timestamp.get_date(metadata.event_timestamp.seconds)," ", timestamp.get_hour(metadata.event_timestamp.seconds),":",timestamp.get_minute(metadata.event_timestamp.seconds),":00")

In the stats aggregation search output  I want to group by

match:
$date, $logs, $host, $user, $serial

and order by max(time)

I'm struggling to do this.

I also noticed when I use the pivot tab in SIEM search results, I get an error message that Pivot can't use metadata time in aggregation functions 😞

(image)

thoughts?

[AymanC]

I believe the following UDM query is what you're trying to achieve. It's worth noting I am unsure what udm field you are referring 'serial' to. However, follow the same concept on lines 2 - 5, and declare the variable, and include it in line 7

$date = strings.concat(timestamp.get_date(metadata.event_timestamp.seconds)," ", timestamp.get_hour(metadata.event_timestamp.seconds),":",timestamp.get_minute(metadata.event_timestamp.seconds),":00")
$logs = metadata.log_type
$user = principal.user.userid
$host = principal.hostname

match:
    $date, $logs, $user, $host

order:
$date desc

 

[Chris_B]

Thanks for the reply.

Described a different way I want output comparable to this Splunk syntax

| stats max($date) count by  $logs, $user, $host

So the output will have one row per distinct combination of values of  $logs, $user, $host and ordered by the most reent timestamp for each combination seen.

If i understood your reply correctly that would give me a table of rows with one row per time value.

Thanks!

[jstoner · Accepted solution]

I will continue with @AymanC example but refine it a bit based on your comment. When comparing the stats search to Splunk SPL, think of the stats command as two pieces, there is the aggregation functions like max and count, which we put into the outcome section and the by statement which we put into the match section.

There is a by keyword that can be used in the match section to perform so additional bucketing is possible and is in preview. We may do a little more tweaking of this, so hopefully there is more to come in that regard.

While I have done the strings concat that Ayman showed above and it works well, because you are not looking to group by the date as well, you just want the max value, we can leave that out of the filtering statement at the top of the page but because we need to calculate it, we put it into the outcome. We use the max aggregation function to get the max date, but to get it into a format that is not epoch time for readability, we then prepend that max timestamp with the function timestamp.get_timestamp  which outputs that max value in a nice readable date time format. If you want it in a different format, you can add a comma followed by one of the format strings shown here.

With that, here is the search and below are my admittedly ugly results, clearly I would need to tune my filtering statement a bit but hopefully this gets you going in the direction you want to go.  

$logs = metadata.log_type
$user = principal.user.userid
$host = principal.hostname

match:
    $logs, $user, $host

outcome:
    $event_count = count_distinct(metadata.id)
    $max_date = timestamp.get_timestamp(max(metadata.event_timestamp.seconds))

order:
$max_date desc

[Chris_B]

Thx John - you got me 95% of the way there and helped me connect my SPL to my "YARAL" so to speak.  My grokking that the outcome sections is where I needed my aggregation / math was key.   

$event_count = count_distinct(metadata.id)
$max_time = timestamp.get_timestamp(max(metadata.event_timestamp.seconds))

Bonus points for helping me see that I was wasting keystrokes with my concat of time and instead could just  timestamp.get_timestamp(max(metadata.event_timestamp.seconds)) 

...and I got this search  working too

metadata.vendor_name = "Okta"
principal.user.userid = /user.name/
$user = principal.user.userid
$ip = principal.ip
$city = principal.location.city
$country = principal.location.country_or_region

match:
$user, $ip, $city, $country

outcome:
$event_count = count_distinct(metadata.id)
$max_time = timestamp.get_timestamp(max(metadata.event_timestamp.seconds))

order:
$max_time desc

I'm still a bit sore I have to run the search in two tabs if I want to both see the events table AND  see stats agg output, but we're getting there  🙂

 thanks