NPM Alert based on percentage of group members status?

Easy to do if you understand sql/swql

Create an alert and change the first box on the trigger page to Custom SWQL Alert

Second box should be Group

Lower box that you can type into should say

join (select count(Name) as [Total], containerid FROM Orion.ContainerMembers group by containerid) t on t.containerid=Groups.containerid

join (select count(Name) as [Down], containerid FROM Orion.ContainerMembers where status=2 group by containerid) d on d.containerid=Groups.containerid

where (d.Down/t.Total)*100>50 -- change 50 to whatever percent of the nodes you want to trigger on

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

Hi Mesverrum,

That looks like exactly what I was looking for - Could you also help me figure out where in the SQL statement I specify the actual group I want to apply this for?

Thanks,

Lasse

That one is written for any group, but if you want to specify a single group then just add to the WHERE section a line saying

and groups.name='your group here'

Hi Mesverum,

Thank you for your kind assistance - I am really trying to learn here.

You mean like this(?)

join (select count(Name) as [Total], containerid FROM Orion.ContainerMembers group by containerid) t on t.containerid=Groups.containerid

join (select count(Name) as [Down], containerid FROM Orion.ContainerMembers where status=2 group by containerid) d on d.containerid=Groups.containerid where (d.Down/t.Total)*100>50 and groups.name='DUMMYGROUP'

When I use the statement above, the statement validates, but it does not trigger even though the group DUMMYGROUP has more than 50% downed members. If I omit the "and groups.name='DUMMYGROUP'" from the statement, it triggers, but on a completely different group (and only that group), even though more groups exist with the condition more than 50% members down exist.

Regards Lasse

Lasse, Looking at the query in my lab i see that i did the math wrong.

try changing the where line from

where (d.Down/t.Total)*100>50

to

where (d.Down*100/t.Total)>50

Since I didn't specify the numbers to be floats SQL was rounding things to the nearest whole number and then multiplying that, which gave unexpected results. 

Lasse, Looking at the query in my lab i see that i did the math wrong.

try changing the where line from

where (d.Down/t.Total)*100>50

to

where (d.Down*100/t.Total)>50

Since I didn't specify the numbers to be floats SQL was rounding things to the nearest whole number and then multiplying that, which gave unexpected results.