Useful group custom SWQL query: Events on group members

I recently added a custom SWQL resource to our group page that other might find useful:

SELECT EventTime, '' as Type, Message , Concat('/Orion/ImageHandler.ashx?Server=1&Image=%2FNetPerfMon%2Fimages%2FEvent-', EventType, '.gif') as [_IconFor_Type]FROM Orion.Eventsinner joinOrion.ContainerMemberSnapshots CMS on CMS.EntityID = Events.NetworkNode inner join Orion.groups C on C.ContainerID=CMS.ContainerIDand C.Name =  '${Caption}'and CMS.Entitytype='Orion.Nodes'order by EventTime Desc

Line07 is the key one -- the group page provides the group/container name as ${Caption} so you can find the members of the group (container) currently being displayed.

This returns the events a node members in the container has experienced -- useful for getting some idea what has been happening in a location rather then somewhat poor events the container has experienced resource.

[one could extend this to report the interface events and other tings, but for my purpose node-related events are good enough]

NPM:12

Find more posts tagged with

custom swql resource

Group

npm12.0

Accepted answers

All comments

mesverrum

Adding on to this, I have some similar examples of custom groups pages queries for all kinds of objects that might be useful.

This one is current CPU/Mem/Loss/Latency for all nodes in the group, with threshold sensitive icons

select n.Caption, n.IP_Address, c.detailsurl as [_linkfor_Caption],'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Caption],CASE when cpuload < 0 then 'Not Polled'else concat(cpuload,'%')end as [CPU Load],concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=HostAvgCPULoad&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_CPU Load],CASE WHEN cpuload>= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'WHEN cpuload< n.CpuLoadThreshold.Level2Value and cpuload >= n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'WHEN cpuload< n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'END AS [_IconFor_CPU Load],case when percentmemoryused < 0 then 'Not Polled'else concat(percentmemoryused,'%')end as [Memory Used],concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=HostAvgPercentMemoryUsed&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_Memory Used],CASE WHEN percentmemoryused>= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'WHEN percentmemoryused< n.percentmemoryusedThreshold.Level2Value and percentmemoryused >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif' WHEN percentmemoryused< n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'END AS [_IconFor_Memory Used],concat(responsetime,'ms') as [Latency],concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=AvgRt&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_Latency],CASE WHEN responsetime>= n.responsetimeThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'WHEN responsetime< n.responsetimeThreshold.Level2Value and responsetime >= n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'WHEN responsetime< n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'END AS [_IconFor_Latency],concat(percentloss,'%') as [Packet Loss],concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=PercentLoss&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_Packet Loss],CASE WHEN percentloss>= n.percentlossThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'WHEN percentloss< n.percentlossThreshold.Level2Value and percentloss >= n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'WHEN percentloss< n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'END AS [_IconFor_Packet Loss]from orion.nodes njoin orion.nodescustomproperties cp on cp.nodeid=n.nodeidjoin Orion.ContainerMembers c on c.memberprimaryid=n.nodeidwhere MemberEntityType like 'orion.nodes'and c.containerid like ${id}--and n.caption like '%${SEARCH_STRING}%'order by n.caption

yerffej07

Is there a way to show this info for just one group? I made a group for my Exchange servers, and I want to show this info in my Applications > Exchange page.

yerffej07

I figured it out, I ran this query to find the container ID of my group:

SELECT TOP 1000 ContainerID, DisplayName

FROM Orion.Groups

Once I had the ID, I changed the line below to from {$id} to the container id number.

and c.containerid like {$id}

and c.containerid like XX