I needed to create custom SWQL alert for checking group members availability and it turns out to be a bit more complicated, so I decided to share it.
You can also modify "cm.Status = 2" to include any other status codes (such as Unreachable etc).
SELECT Groups.Uri, Groups.DisplayName FROM Orion.Groups AS Groups
--,cm.[counter] AS [All Members], cmDOWN.[counter] AS [Members with Down status], cmDOWN.[counter]*1.00/cm.[counter] AS [PercDown]
-- option to use Group Custom Property
JOIN Orion.GroupCustomProperties cp ON Groups.ContainerID = cp.ContainerID
--counting all members within a group
JOIN (
SELECT cm.ContainerID, count(cm.MemberPrimaryID) AS [counter] FROM Orion.ContainerMembers cm GROUP BY cm.ContainerID
) cm ON cm.ContainerID = Groups.ContainerID;
--counting all members with Down status
JOIN (
SELECT cm.ContainerID, count(cm.MemberPrimaryID) AS [counter] FROM Orion.ContainerMembers cm WHERE cm.Status = 2 GROUP BY cm.ContainerID
) cmDOWN ON cmDOWN.ContainerID = Groups.ContainerID;
WHERE
-- Percent of Members with Down status
cmDOWN.[counter]*1.00/cm.[counter] >= 0.80
-- Optional filter for number of members within a group
-- and cm.[counter] > 10