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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...