Splunk Search

How do I get a subtraction of two fields when they share a common value?

pal_sumit1
Path Finder

I am having three columns in primary_key, service_name , timestamp.

I want to get a subtraction of values present in the timestamp where their corresponding service_name is same.

And, if we are having more that 2 same fields, then we should get the average of both of the results.

Sample Data :

primary_key  ------service_name------timestamp
123                  "before abc"                " 2-2-2018"
 123                 "after    "abc"               "2-3-2018"
123                     "before abc"               " 2-4-2018"
123                           "after abc"            "2-5-2018"

Desired result :

value=(("2-3-2018" - "2-2-2018")+("2-5-2018"-"2-4-2018" ))/2

Thanks in Advance

Tags (2)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@pal_sumit1,

Try

"your current search"|eval _time=strptime(timestamp,"%d-%m-%Y")
|sort primary_key,_time
|streamstats last(_time) as prev_time current=f window=1 by primary_key
|eval diff=_time-prev_time
|streamstats count as rowno by primary_key reset_on_change=true
|eventstats avg(eval(if(rowno%2==0,diff,null()))) as average by primary_key
|table primary_key,service_name,timestamp,diff,average
  • You shall replace eventstats with stats if you just want the final result with primary_key and avg difference
  • Ignored events if there is only one event - for eg only "before" event and not an "after" event
  • Average is displayed in all events for that particular primary key

Sample data used :

primary_key,service_name,timestamp
123,"before abc",2-2-2018
123,"after  abc",2-3-2018
123,"before abc",2-4-2018
123,"after  abc",2-5-2018
456,"before xyz",2-2-2018
456,"after  xyz",2-3-2018
789,"before pqr",2-4-2018
789,"after  pqr",2-5-2018
789,"before pqr",2-6-2018    --> this event is ignored for the calculation since there is no pair for it.

Lets know if you need any modification.
Only those highlighted values are considered for avg calculation

alt text

Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@pal_sumit1,

Try

"your current search"|eval _time=strptime(timestamp,"%d-%m-%Y")
|sort primary_key,_time
|streamstats last(_time) as prev_time current=f window=1 by primary_key
|eval diff=_time-prev_time
|streamstats count as rowno by primary_key reset_on_change=true
|eventstats avg(eval(if(rowno%2==0,diff,null()))) as average by primary_key
|table primary_key,service_name,timestamp,diff,average
  • You shall replace eventstats with stats if you just want the final result with primary_key and avg difference
  • Ignored events if there is only one event - for eg only "before" event and not an "after" event
  • Average is displayed in all events for that particular primary key

Sample data used :

primary_key,service_name,timestamp
123,"before abc",2-2-2018
123,"after  abc",2-3-2018
123,"before abc",2-4-2018
123,"after  abc",2-5-2018
456,"before xyz",2-2-2018
456,"after  xyz",2-3-2018
789,"before pqr",2-4-2018
789,"after  pqr",2-5-2018
789,"before pqr",2-6-2018    --> this event is ignored for the calculation since there is no pair for it.

Lets know if you need any modification.
Only those highlighted values are considered for avg calculation

alt text

Happy Splunking!

renjith_nair
SplunkTrust
SplunkTrust

@pal_sumit1, did it work for you ? If yes, accept it as answer or lets know whats missing.

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...