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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...