LinkedIn
Twitter
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?
Solved
1 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
