We wanted a way to see the same template across multiple servers and the status of the servers services. eg
you will need to edit few things to what you might want to display.
Select Component,
'' as [Srv1], [_IconFor_Srv1],
'' as [ ],
'' as [Srv2], [_IconFor_Srv2],
'' as [Srv3], [_IconFor_Srv3],
'' as [Srv4], [_IconFor_Srv4]
From (
SELECT
c.name as Component,
max(Case
When n.Caption = 'ServerName1'
Then ('/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif')
Else ('/Orion/images/StatusIcons/Small-' + 'shutdown'+ '.gif')
End) as [_IconFor_Srv1],
max(Case
When n.Caption = 'ServerName2'
Then ('/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif')
Else ('/Orion/images/StatusIcons/Small-' + 'shutdown'+ '.gif')
End) as [_IconFor_Srv2],
max(Case
When n.Caption = 'ServerName3'
Then ('/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif')
Else ('/Orion/images/StatusIcons/Small-' + 'shutdown'+ '.gif')
End) as [_IconFor_Srv3],
max(Case
When n.Caption = 'ServerName4'
Then ('/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif')
Else ('/Orion/images/StatusIcons/Small-' + 'shutdown'+ '.gif')
End) as [_IconFor_Srv4]
FROM Orion.APM.Component c
JOIN ORION.APM.CurrentStatistics cs ON c.ComponentID = cs.ComponentID
JOIN Orion.APM.Application a ON c.ApplicationID = a.ApplicationID
JOIN Orion.Nodes n ON a.NodeID = n.NodeID
WHERE
c.name IN ( 'WSUS Check-in Monitor',
'Service: Windows Update',
'Available hidden updates',
'Available critical updates',
'Available optional updates',
'Installed updates',
'Days passed from last Windows Update',
'Days passed from last Windows Update.New',
'Windows Updates activity status',
'Machine restart status before installing Windows Updates',
'Machine restart status after installing Windows Updates',
'Last Patch Install Monitor'
)
Group by c.name
) Tbl
gives