Is it possible to be able to calculate the total length of time that this host has had a CRITICAL status for if it?
In the screenshot, it has had a CRITICAL status for approx 3 hours 43 minutes indicated.
Below query for one host
index=ad source=dfs host=nas01n
| eval host = lower(host)
| table _time, host, "Folder Name", "Group Name", State
| fillnull value="Folder Not Enabled" "Folder Name"
| fillnull value="No Group Name" "Group Name"
Try below. It uses streamstats to calculate a running duration of a certain state and keeps track of the last timestamp. This last timestamp is then used to find the events that mark the end of period in a certain state. These events will have the duration of that period in them from the streamstats command. Summing duration by state will then give you the total duration a host has been in each state.
index=ad source=dfs host=nas01n
| eval host = lower(host)
| table _time, host, "Folder Name", "Group Name", State
| fillnull value="Folder Not Enabled" "Folder Name"
| fillnull value="No Group Name" "Group Name"
| sort _time
| streamstats reset_on_change=true range(_time) as duration latest(_time) as lastTime by State
| where _time = lastTime
| stats sum(duration) by State
Thanks but all that was returned was two columns that looks like the below , I added a PIPE to separate the fields.
State | sum(duration)
CRITICAL | 599
State | 598
What else would you like to see in the results?
PS: that sum(duration) will be in seconds if that wasn't clear.
I want to have each individual host listed with a column showing the total time in which the CRITICAL status has been showing.
So the table would look like
| table _time, host, "Folder Name", "Group Name", State , Duration
Try remove that last | stats...
line from my solution, is that what you're looking for?
Note, if you want to apply this to multiple hosts, you'll need to make some more adjustments. At least it needs to be added to the by clause of the streamstats and to make that work properly, you'll also need to change the sorting to have the results grouped by host (and then sorted by _time).