Splunk Search

Field calculations in groups of events

andrey2007
Contributor

Basically, I need to group my events by 3 fields (LOGIN, field 1, field 2) and make custom calculations in each group(activity duration, login attempts, custom operation counts and so on).
For example, events have 2 additional fields “TIME” and “TYPE”, besides 3 grouping fields.
So in each group I need to find one event with “min (Time) AND Type=Login” and another one event with “max (Time) AND Type=Logout”. And calculate time difference between these two events.

timeDifference = Time2 - Time1.

Also, after timeDifference calculation I need to calculate some additional fields. For example, count of events included in group with “Type=Access denied”
What is the best way to do this?

Tags (1)
0 Karma

jonuwz
Influencer

Try this. Should work

... | stats min(eval(if(TYPE=="Login",TIME,NULL))) as min_time 
                 max(eval(if(TYPE=="Logoff",TIME,NULL))) as max_time by LOGIN field1 field2 | ...

So what we're doing is dynamically creating the values that stats works on.

i.e. get the minimal value of all events where TYPE=Login.

we test if TYPE=Login, and if so return the value of TIME, otherwise we return null
we then use the minimum value of all those and return it as min_time.

The usual "by" clauses are still effective.

Contrived example

index=_internal sourcetype=splunkd_access earliest=-24h 
| stats min(eval(if(method=="POST",_time,NULL))) as earliest_post
        max(eval(if(method=="POST",_time,NULL))) as latest_post
        min(eval(if(method=="GET",_time,NULL))) as earliest_get
        max(eval(if(method=="GET",_time,NULL))) as latest_get by user status
| convert ctime(*_*)
0 Karma

andrey2007
Contributor

Thanks, but this request works not the way i need,

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...