Can I create Advansed Alert with count?

Gob

Hello.

Some nodes goes down more than four times per hour.
Can I create Alert with this request?

select caption,count(*) as total
from events inner join nodes on (networknode=nodeid)
where eventtime >dateadd(hour,-1,getdate()) and eventtype=1
group by caption
having count(*)>=4

 

Thanks.

Gob

Hi. At one's own risk.
I made thus.

1# Add new Alert
2# Fill General Tab (For example. Name of Alert will be "Node went down")
3# Trigger Condition Tab:
 Type of Property to Monitor:Node
 Don't edit design query!
 Setup your delay.
4# Reset Condition Tab:
 Reset this alert when the following conditions are met
 Don't edit design query!
 Setup your delay.
5# Alert Suppression Tab
 (I do not use this)
6#,7#,8# TimeOfDay/TriggerActions/ResetActions
Press Ok
Now we must insert our queries into AlertDefinitions table

Open SW Database Manager.
Exec this query on AlertDefinitions table
 Select  *
 From AlertDefinitions
 where alertname ='Node went down'

Add our queries into "TriggerQuery" and "ResetQuery"
Triggerquery
select Nodes.NodeID AS NetObjectID, Nodes.Caption AS Name,count(*) as total
from events inner join nodes on (networknode=nodeid)
where eventtime >dateadd(hour,-2,getdate()) and eventtype=1
group by NodeID,Caption
having count(*)>=3

Resetquery
select Nodes.NodeID AS NetObjectID, Nodes.Caption AS Name,count(*) as total
from events inner join nodes on (networknode=nodeid)
where eventtime >dateadd(hour,-3,getdate()) and eventtype=1
group by NodeID,Caption
having count(*)=1

The columns NetObjectID and Name must exist in our querires!

Atention!
If you want to change any of Alert parameters you'll get this message

After this you must add your queries into "TriggerQuery" and "ResetQuery" again!