Hello everybody!
I have here a table schema looking quite like this
(its from the opsview data warehouse odw, table state_history, documented here
ODW Documentation)
check datetime status statustype prior_status
CPU Usage 2013-31-12 11:11:11 OK HARD UNKNOWN
CPU USAGE 2013-31-12 11:22:00 CRITICAL SOFT OK
CPU USAGE 2013-31-12 11:23:00 CRITICAL HARD CRITICAL
CPU USAGE 2013-31-12 11:30:20 OK HARD CRITICAL
prior_status_datetime
2013-01-01 00:00:00
2013-31-12 11:11:11
2013-31-12 11:22:00
2013-31-12 11:23:00
So, you can see that there is an entry everytime the status changes; it changes from OK to CRITICAL SOFT to CRITICAL HARD and then back to OK. What i want to know is:
How long did it take to get from CRITICAL HARD back to OK HARD?
It is possible to change from status CRITICAL type HARD to status UNKNOWN or status WARNING, so the OK HARD state doesnt immediately follow the CRITICAL HARD (only sometimes it does).
Additional Informations:
- There is an index on datetime:
Type BTREE
Unique NO
Columns datetime, servicecheck
- There are ~ 1.5 million rows in the table
- servicecheck column is int(11) containing mostly 4 or 5 digit integers
- statustype is always SOFT or HARD
- Possible values for status are CRITICAL, OK, WARNING or UNKNOWN.
I tried this:
SELECT start.servicecheck, start.datetime AS start_datetime, MIN(end.datetime) AS end_datetime,
(MIN(end.datetime)-start.datetime) AS duration
FROM state_history start
JOIN state_history end ON start.servicecheck=end.servicecheck
AND end.datetime>start.datetime
AND end.status="ok" AND end.status_type="hard"
AND start.status="Critical" AND start.status_type="Hard"
AND start.servicecheck=12345
GROUP BY start.datetime
ORDER BY start.datetime;
Is this the best / most efficient way?
I tried the query with several servicecheck id and it takes 30-40 seconds.
But I have to do this not for one, but a lot of servicechecks joined in from another table; then its adding up to ~140 - 200 seconds, depending on ho many servicecheck ids i have to handle.
Any suggestions / ideas would be great (not looking for someone doing my work, just help).
Greetings