1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

Query: Subtract time value from a changing state event

Discussion in 'SQL - Questions and Answers' started by zabbix_zen, Aug 31, 2009.

  1. zabbix_zen New Member

    Hi all.

    Having a table with an incremental actionid, which stores the time an event occured and it's description, several actionids to for each message type(the same for a corresponding Problem/OK) was sent,
    that looks like this,
    +--------------------+---------------+------------+----------+
    | subject | message | clock | actionid |
    +--------------------+---------------+------------+----------+
    | CLIENT AAA-2009.08.27 | 13:30:33 PROBLEM severity Average: bla bla Delays | 1251376235 | 17 |
    | CLIENT AAA-2009.08.27 | 13:39:33 severity Average: bla bla Delays. Now OK | 1251376775 | 17 |
    | CLIENT BBB-2009.08.27 | 14:28:08 PROBLEM severity Average: Delivery Report Expired | 1251376789 | 21
    | CLIENT AAA-2009.08.27 | 15:25:44 PROBLEM severity Average: Delivery Report Expired | 1251376793 | 21
    | CLIENT ZZZ-2009.08.31 | 15:50:22 PROBLEM severity Information: Machine beta is returning 91.23% Availability | 43
    | CLIENT YYY-2009.08.31 | 15:48:51 PROBLEM severity Information: Machine zeta is returning 88.23% Successf. Terms. | 46
    | CLIENT AAA-2009.08.27 | 16:31:33 PROBLEM severity Average: Delivery Report Expired | 21
    | CLIENT ZZZ-2009.08.31 | 16:50:22 PROBLEM severity Information: Machine beta is returning 99.89% Availability. Now OK | 43
    | CLIENT YYY-2009.08.31 | 16:13:27 severity Information: Machine zeta is returning 88.23% Successf. Terms. Now OK | 46

    How can I:
    1) query to return the Problem events and compute the time it took to change from Problem -> OK.

    2 ) There are several Problem events from multiple clients, so I need to process from the first event to the last, something like:

    select alertid,message FROM alerts where message like '%PROBLEM%' GROUP BY (select DISTINCT clock) ORDER BY alertid asc

    4) My problem lies finding each message like '%OK%' corresponding to a given message like '%PROBLEM%' line, and subtracting the clock of one to the other(returning NULL for the ones who aren't OK yet)!

    using a for loop of some sort.
    Can somebody help me here?

    Thanks in advance!
    zabbix_zen, Aug 31, 2009
    #1

Share This Page