topic Re: time difference between two events in Splunk Search

time difference between two events

surekhasplunk — Wed, 19 Aug 2020 03:38:01 GMT

I am using below query to get the details of alarms which has (one Warning and one OK status) or (one Critical and one OK status) per checkname and device. Now how to get the time difference(duration) between the ok and warning messages or ok and critical messages?

index=abc sourcetype=alarms |stats count by Device CheckName Status _time | sort - _time

please help

Re: time difference between two events

to4kawa — Wed, 19 Aug 2020 04:46:38 GMT

I don't know anything because you've deleted all the important parts.

Re: time difference between two events

gcusello — Wed, 19 Aug 2020 07:50:34 GMT

Hi @surekhasplunk,

as @to4kawa said, it's difficoult to help you without informations!

Anyway, analyzing your search, I can image that for each device you have different checknames.

In this case you could modify your search in this way:

index=abc sourcetype=alarms | stats earliest(_time) AS earliest latest(_time) AS latest values(Status) AS Status dc(Status) AS dc_Status by Device CheckName | where dc_Status>1 | eval diff=latest-earliest

The logic is that:

you take all the statuses and times for each Device and CheckName,
then you take only the ones with two differtent Statuses (if you can have more conditions, you can add other conditions to identify the ones you want to monitor),
Then you can calculate the difference between the earliest and the latest.

Ciao.

Giuseppe

Re: time difference between two events

surekhasplunk — Wed, 19 Aug 2020 08:31:46 GMT

Hi @gcusello @to4kawa

Sorry for not uploading valued info.

I am uploading again... here the First Column Device i am giving details of 1 single device but here multiple devices can come when i dont filter for that device name.

And for each checkname there can be one or more ok and warning or ok and critical messages

Re: time difference between two events

gcusello — Wed, 19 Aug 2020 09:04:05 GMT

Hi @surekhasplunk,

is there ani other information that can be used to identify each transaction?

if yes, use it in the BY clause and you'll have the results.

If not, you have to use the transaction command (very slow!), something like this:

index=abc sourcetype=alarms | transaction Device CheckName startwith="Critical OR Warning" endswith"OK" | where duration>0

Ciao.

Giuseppe

Re: time difference between two events

surekhasplunk — Wed, 19 Aug 2020 09:32:44 GMT

Hi @gcusello ,

Do i have to concatenate the CheckName and Status fields together for the query to work ?

As i modified your query to add the Status field as that holds the value Critical/Warning/OK

whereas CheckName field is common between the events. And i receive 0 results am not sure why

index=abc sourcetype=alarms | transaction Device CheckName Status startswith="Critical OR Warning" endswith="OK" | where duration>0

Re: time difference between two events

to4kawa — Wed, 19 Aug 2020 09:47:48 GMT

index=abc sourcetype=alarms |stats range(_time) as duration min(_time) as _time by Device CheckName | sort - _time

How about this?

Re: time difference between two events

rrovers — Wed, 19 Aug 2020 09:56:20 GMT

Maybe the delta command is what you're looking for?

for example:

index=_internal
| delta_time as timedifference
| table timedifference _time

Re: time difference between two events

gcusello — Wed, 19 Aug 2020 10:23:54 GMT

Hi @surekhasplunk,

no, if you use also Status in the transaction keys you'll never be able to build the transaction between Critical or Warning and OK because the Status is different.

You need to correlate events with the same Device and Checknames, that starts with Critical or Warning and finish with OK.

Ciao.

Giuseppe