time difference between two events

[surekhasplunk]

Hi

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

[rrovers]

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

for example:

index=_internal
| delta_time as timedifference
| table timedifference _time

[gcusello]

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

[surekhasplunk]

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

[gcusello]

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

[surekhasplunk]

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

[gcusello]

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

[to4kawa]

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

How about this?

[to4kawa]

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