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?
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.
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(*_*)