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.
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.
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
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.
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.
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
@DavidHourani @kmorris Sorry for delay response.
Thank you so much it worked 🙂