I make an alert to email me if any group member status goes down (even when there were other nodes in the group that went down before), I write my solution down here and wish it can help you.
1. Make a custom property for groups as text. (e.g. chng_date)
2. Make an alert for groups:
- Select I want to alert on to Custom SWQL Alert (Advanced)
- Set up your SWQL condition to Groups
- And write the following code:
INNER JOIN Orion.ContainerMembers cm ON Groups.ContainerID = cm.ContainerID
INNER JOIN ORION.Nodes n ON n.NodeID = cm.MemberPrimaryID
INNER JOIN ORION.Events e ON n.NodeID = e.NetworkNode
WHERE NOT Groups.Status = 1 AND Groups.name LIKE '%-SITE%' AND n.Status = 2 AND e.EventType = 1 AND (datetime(Groups.customproperties.chng_date) < tolocal(e.EventTime) OR datetime(Groups.customproperties.chng_date) IS NULL)
-- I made groups with "-SITE" identifier so I add Groups.name LIKE '%-SITE%’ to recognize them.
-- datetime(Groups.customproperties.chng_date) IS NULL) is made the alert trigger for first time that chng_date field is empty
- In reset condition select Create a special reset condition for this alert ➟ Copy condition from trigger (It should be the same as setting to No reset condition – Trigger this alert each time the trigger condition is met but I had some problems with that and I don't know why):
INNER JOIN Orion.ContainerMembers cm ON Groups.ContainerID = cm.ContainerID
INNER JOIN ORION.Nodes n ON n.NodeID = cm.MemberPrimaryID
INNER JOIN ORION.Events e ON n.NodeID = e.NetworkNode
WHERE NOT Groups.Status = 1 AND Groups.name LIKE '%-SITE%' AND n.Status = 2 AND e.EventType = 1 AND (datetime(Groups.customproperties.chng_date) < tolocal(e.EventTime))
- In Trigger Actions add an action to change the chng_date value to Sortable Date (${N=Generic;M=S}).
- And add Send an Email/Page action with the subect:
${N=SwisEntity;M=Name} Status is ${N=SwisEntity;M=StatusDescription} at ${N=Alerting;M=AlertTriggerTime;F=DateTime}
And the Message:
${N=Generic;M=DateTime;F=DateTime}
NODES:
${SQL:SELECT cast(cast([GroupMemberFullName]as varchar(max)) + '' + ' ' + '-' + ' ' as XML), cast(cast([GroupMemberStatusName]as varchar(max)) + '' + CHAR(10) + CHAR(13) as XML) FROM [SolarWindsOrion].[dbo].[Containers_AlertsAndReportsData] where GroupID=${GroupID} FOR XML PATH('')}
3. Make reset alert for groups (to reset the groups which went up before generating jira) (I preferred to set Evaluate the trigger condition every 59 seconds)
- Select I want to alert on to Custom SWQL Alert (Advanced)
- Set up your SWQL condition to Groups
- And write the following code:
INNER JOIN Orion.ContainerMembers cm ON Groups.ContainerID = cm.ContainerID
INNER JOIN ORION.Nodes n ON n.NodeID = cm.MemberPrimaryID
INNER JOIN ORION.Events e ON n.NodeID = e.NetworkNode
WHERE Groups.Status = 1 AND Groups.name LIKE '%-SITE%' AND n.Status = 1 AND e.EventType = 5 AND (datetime(Groups.customproperties.chng_date) < tolocal(e.EventTime))
- In reset condition select No reset condition – Trigger this alert each time the trigger condition is met:
- In Trigger Actions add an action to change the chng_date value to Sortable Date (${N=Generic;M=S}).
And leave other fields as default.
