I was trying to come up with a longer name ;-)
I have a need to drive alerts filtered to only nodes of a certain group. The generally agreed approach for this is a custom property with the groups in it. To avoid manually updating such a property,I have built the following.
Procedure - this runs every 4 hours for an SQL Agent Job
create proc sp_Refresh_Node_GroupList
as
BEGIN
with p1 as (
select c.EntityID NodeID, cn.Name GroupName
from solarwindsOrion.dbo.ContainerMemberSnapshots c
inner join solarwindsOrion.dbo.Containers cn
on cn.ContainerID = c.ContainerID
where EntityType = 'Orion.Nodes'
--the following line is optional - filter to specific group types
--and cn.Name like '(App)%'
)
,p2 as (
SELECT p1.NodeID,
stuff( (SELECT ','+ GroupName s
FROM p1 p2
WHERE p2.NodeID = p1.NodeID
ORDER BY GroupName
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
AS Groups_List
FROM p1
GROUP BY NodeID
)
update dst
set dst.Group_List = src.Groups_List
from solarwindsOrion.dbo.[NodesCustomProperties] dst
inner join p2 AS src
on dst.NodeID = src.NodeID
and coalesce(dst.Group_List,'') <> coalesce(src.Groups_List,'')
END;