topic Re: How to calculate duration of two events with same fields that contain the same value occurred at different time in Splunk Search

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

avni26 — Fri, 07 Jun 2019 18:13:37 GMT

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.

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

kmorris_splunk — Fri, 07 Jun 2019 18:46:02 GMT

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.

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

avni26 — Mon, 10 Jun 2019 06:21:56 GMT

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

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

DavidHourani — Mon, 10 Jun 2019 07:57:14 GMT

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

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

avni26 — Mon, 10 Jun 2019 16:59:05 GMT

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.

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

avni26 — Thu, 11 Jul 2019 06:22:19 GMT

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