Splunk Search
Highlighted

Field calculations in groups of events

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
Highlighted

Re: Field calculations in groups of events

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
Highlighted

Re: Field calculations in groups of events

Contributor

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

0 Karma