Splunk Search

How to calculate duration of two events with same fields that contain the same value occurred at different time

avni26
Explorer

I have following sample events of a problem having field State open and Resolved.

_time              ID    Title      Severity    State    hostname
08-05-2019 13:14    450 Monitoring   AAA         OPEN     host2
08-05-2019 13:15    450 Monitoring   AAA         RESOLVED  host2
16-05-2019 18:58    660 Slow disk   BBB      RESOLVED  host1
16-05-2019 18:09    660 Slow disk   BBB      OPEN     host1
29-05-2019 08:02    450 Slow disk   BBB      OPEN     host3
29-05-2019 08:06    450 Slow disk   BBB      RESOLVED  host3
18-04-2019 18:43    450 Slow disk   BBB      OPEN     host3
25-03-2019 18:30    660 Slow disk   BBB      RESOLVED  host1
25-03-2019 18:19    660 Slow disk   BBB      OPEN     host1
18-04-2019 18:49    450    Slow disk    BBB         RESOLVED  host3

I want to calculate the duration of each problem at every occurrence and want to display results in single table.
For example : Result should be like below

_time             ID     Title      Severity   State       hostname   duration(Min)
18-04-2019 18:49   450    Slow disk    BBB        RESOLVED  host3      3.00
08-05-2019 13:15   450    Monitoring   AAA        RESOLVED    host2      1.02
29-05-2019 08:06   450  Slow disk   BBB     RESOLVED    host3      4
25-03-2019 18:30   660  Slow disk   BBB     RESOLVED    host1      11
16-05-2019 18:58   660  Slow disk   BBB     RESOLVED    host1      49

Please suggest.

0 Karma
1 Solution

kmorris_splunk
Splunk Employee
Splunk Employee

You can use transaction for this. You would use both ID and host as the key:

[BASE SEARCH]
| transaction ID hostname startswith="(State=OPEN)" endswith="(State=RESOLVED)"
| stats latest(_time) as time  latest(Title) as Title latest(duration) as duration by ID hostname
| eval time = strftime(time,"%Y-%m-%d %H:%M:%S.%3N") 
| eval duration = duration/60 | sort duration

Try something like this. You will probably want to use a table command to format the fields in the correct order at the end of the search.

View solution in original post

0 Karma

DavidHourani
Super Champion

Hi @avni26,

This will do the trick :

Yoursearch 
| stats earliest(_time) as start, latest(_time) as end, values(severity) as severity,values(Title) as Title, last(State) as State by hostname, ID
|eval durationMinutes=(end-start)/60

Let me know if that helps.

Cheers,
David

0 Karma

avni26
Explorer

Hey David,
I tried this also , by doing this , event having same values occurred at different time gets grouped and duration takes the start time as first Open state and last resolved state of last occurrence of that event
For example :
_time ID Title Severity State hostname
29-05-2019 08:02 450 Slow disk BBB OPEN host1
29-05-2019 08:06 450 Slow disk BBB OPEN host1
18-04-2019 18:49 450 Slow disk BBB RESOLVED host1
18-04-2019 18:43 450 Slow disk BBB OPEN host1
BY using above trick , durationMinutes showing for these events as one
ID Title Severity State hostname durationMinutes
450 Slow disk BBB RESOLVED host1 58392.36666666667

I want both events should be counted as individual . Please suggest some other way.

Thanks.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

You can use transaction for this. You would use both ID and host as the key:

[BASE SEARCH]
| transaction ID hostname startswith="(State=OPEN)" endswith="(State=RESOLVED)"
| stats latest(_time) as time  latest(Title) as Title latest(duration) as duration by ID hostname
| eval time = strftime(time,"%Y-%m-%d %H:%M:%S.%3N") 
| eval duration = duration/60 | sort duration

Try something like this. You will probably want to use a table command to format the fields in the correct order at the end of the search.

View solution in original post

0 Karma

avni26
Explorer

Hi Kmorris,

I already tried this, from transaction its not fetching all desired output . There is some Problem events also exist whose State is only "OPEN" or only RESOLVED . Those events count are not coming after using this.
And also due to Transaction command , Its taking lots of time to load.
Can't it be done by some other way without using Transaction.

Please suggest .

Thanks

0 Karma

avni26
Explorer

@DavidHourani @kmorris Sorry for delay response.
Thank you so much it worked 🙂

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.