Splunk Search

How to find the average time difference between eventTime and recordTime in IDS?

hartfoml
Motivator

In IDS, I have an eventTime and a recordTime. The recordTime is the timestamp that Splunk uses to record the events. I know how to get the diff between the eventTime and the recordTime.

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval diff_sec=(recordTime - eventTime) | eval diff_min=(diff_sec/60) | eval Diff=IF(diff_min<0,0,diff_min) | search Diff>10

What I need now is to find the avg(Diff) per hour for each of the sensors so I can see when I an having a latency issue and for which sensor.

I tried this search but didn't get what I needed.

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval diff_sec=(recordTime - eventTime) | eval diff_min=(diff_sec/60) | eval Diff=IF(diff_min<0,0,diff_min) | search Diff>10 | bucket eventTime span=1h | chart avg(Diff) by sensor

and this

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval diff_sec=(recordTime - eventTime) | eval diff_min=(diff_sec/60) | eval Diff=IF(diff_min<0,0,diff_min) | search Diff>10  | timechart span=1h avg(Diff) by sensor

Also the true time of the event is the eventTime so I would like to show the timechart with reference to the eventTime, not the recordTime.

Any suggestions would be greatly appreciated

0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval Diff=(recordTime - eventTime)/60 | eval Diff=IF(Diff<0,0,Diff) | search Diff>10 | eval _time=eventTime  | timechart span=1h avg(Diff) by sensor

View solution in original post

0 Karma

somesoni2
Revered Legend

Try something like this

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval Diff=(recordTime - eventTime)/60 | eval Diff=IF(Diff<0,0,Diff) | search Diff>10 | eval _time=eventTime  | timechart span=1h avg(Diff) by sensor

View solution in original post

0 Karma

hartfoml
Motivator

thanks I had the same though just after I posted the question

0 Karma

somesoni2
Revered Legend

Yeah...The one you've is probably the best method for calculating that. Just few eval that you can remove to improve search performance a little.

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval Diff=(recordTime - eventTime)/60 | search Diff>10 | eval _time=eventTime  | timechart span=1h avg(Diff) by sensor
0 Karma

hartfoml
Motivator

I think I figured this one out myself

I needed to convert _time to eventTime and then the chart worked and looked better

eventtype=IDS_Event | convert timeformat="%b %d %H:%M:%S" mktime(*Time) | eval _time=eventTime | eval diff_sec=(recordTime - eventTime) | eval diff_min=(diff_sec/60) | eval Diff=IF(diff_min<0,0,diff_min) | search Diff>10 | timechart span=1h avg(Diff) by sensor

Any other suggestions would be great

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!