Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

SplunkTrust
SplunkTrust

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
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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