Visualize Alerts that lasted long time

[BanditBBS]

OK, so as the subject says, I want to run a report, do something in excel, something....that will visualize alerts that were in a bad start over X number of days. I have state history kept for the past year, so I have the data, just can't wrap my head around a way to do this.

Has anyone else done something similar?

[swolf]

We had a similar situation in an internal project where someone was trying to get information about the durations of certain events from the timestamps. I'm not sure if he ever came up with a solution, but I know it caused him some trouble.

My first thought is that you should probably start with a prepared statement that transforms the data into something more usable. If you were able to wrangle the data to have columns like ('state_time', 'object_id', 'state', 'duration') then your problem becomes relatively simple, regardless of how you choose to complete the rest of it.

[BanditBBS]

Feel free to close this......

My resolution: Ran a state history report for time period I wanted and exported to csv. In the CSV I sorted by host - service - time

Created a new column that has this formula:

Code: Select all

=IF(E2="OK",A2-A3,"")

Created a new column that concats the Host and Service column and then did this pivot table:

Capture.PNG

We can now visualize items that were left in a bad state over x number of days and try to figure out why that is the case.

[swolf]

Nice! Thanks for sharing your solution.