HTEMS HB Omnibus trigger

Hi List.

We recently had our HTEMS crash, and nobody noticed it for many hours over the weekend. So we are going to setup an 'HTEMS HB' Omnibus trigger to watch for when the event flow from ITM stops for 'too many minutes', and generate an alarm when that happens. For our environ, I think that if we don't see an event from ITM in 5 minutes, there is likely to be a problem. My first attempt at this trigger is below (hopefully email won't mangle it) - it seems to work OK in the lab.

One question I had, since this is my first semi-complicated trigger - Is there a way to store the value of max(LastOccurrence) in the below sql for later use within the trigger?

select max(LastOccurrence) from alerts.status where Agent = 'ITM';

It seems like it would be more efficient than the way I did it, which was to loop through results one at a time. Any other suggestions for improvement  on this trigger are welcome too.

Thanks in advance,

-James

------------------------------





create or replace trigger my_itm_hb
group default_triggers
priority 20
comment 'Generate escalating alarms if ITM event flow appears to have stopped'
every 300 seconds
-- every 5 seconds (testing)
declare
    hostname char(255);
    now utc;
    maxt utc;
    difft integer;
    max_difft integer;
    old_sev integer;
    new_sev integer;
begin
    set hostname = 'my_htems';  -- MODIFY AS NEEDED (evt hostname)
    set now = getdate();
    set maxt = 0;
    set max_difft = 300;  -- MODIFY AS NEEDED (max seconds since last ITM evt)
    -- set max_difft = 30;  -- (testing)
    set old_sev = -1;

    -- write into test values ('NOW:', to_char(now,'%Y%m%d_%H:%M:%S'));

    for each row r in alerts.status where r.Identifier = 'ESM_HB:ITM_HTEMS_EVT_FLOW'
    begin
        set old_sev = r.Severity;
    end;

    -- write into test values ('OLD_SEV:', old_sev);

    for each row r in alerts.status where r.Agent = 'ITM'
    begin
        if (r.LastOccurrence > maxt) then
            set maxt = r.LastOccurrence;   -- most recent ITM event update
        end if;
    end;

    -- write into test values ('MAXT:', to_char(maxt,'%Y%m%d_%H:%M:%S'));

    set difft = now - maxt;

    -- write into test values ('DIFFT:', difft);

    if (difft > max_difft) then
        -- write into test values ('Most recent ITM event is too old (', difft, ' seconds).');
        if (  old_sev > 0 ) then           -- problem evt existed already
            if ( old_sev < 5 )then
                set new_sev = old_sev + 1; -- ESCALATE problem evt
            else
                set new_sev = 5;           -- problem evt already at max sev
            end if;
        else
            set new_sev = 3;               -- INITIAL problem evt
        end if;
    else
        set new_sev = 0;                   -- CLEARING evt
    end if;

    -- write into test values ('NEW_SEV:', new_sev);

    if (new_sev > 0) then
        if (old_sev = -1) then
            -- evt did not already exist
            insert into alerts.status (
                        Identifier,
                        Summary,
                        Node,
                        Severity,
                        FirstOccurrence,
                        LastOccurrence,
                        AlertGroup,
                        AlertKey)
            values
                        ('ESM_HB:ITM_HTEMS_EVT_FLOW',
                        'ITM event flow has stopped - NO events in ' + to_char((30+difft)/60) + ' minutes',
                        hostname,
                        new_sev,
                        getdate(),
                        getdate(),
                        'ITM_HTEMS_EVT_FLOW',
                        'ITM_HTEMS_EVT_FLOW');
        else
            -- evt already existed
            update alerts.status set Severity = new_sev , Summary = 'ITM event flow has stopped - NO events in ' + to_char((30+difft)/60) + ' minutes' where Identifier = 'ESM_HB:ITM_HTEMS_EVT_FLOW';

        end if;
    else
        if (old_sev > 0) then
            -- evt already existed
            update alerts.status set Severity = new_sev , Summary = 'ITM event flow has resumed - events received ' + to_char(difft) + ' seconds ago' where Identifier = 'ESM_HB:ITM_HTEMS_EVT_FLOW';

        end if;
    end if;

end;
go

_______________________________________________
TME10 mailing list
[email protected]
Unsubscribe:[email protected]