cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Status of Statues (swql)

A pretty good overall summary of the modules.  It can be expanded by putting in more unions.

 cscoengineer_0-1590700105294.png

 

SELECT
concat('NPM - ',count(*),' Nodes') as [Category]
,sum(case when n.Status =1 then 1 else 0 end) AS [Up]
,'/Orion/Images/StatusIcons/Small-Up.gif' as [_IconFor_Up]
,'/orion/nodes/default.aspx' as [_Linkfor_Up]
,sum(case when n.Status=3 then 1 else 0 end) AS [Warning]
,'/Orion/Images/StatusIcons/Small-Warning.gif' as [_IconFor_Warning]
,'/orion/nodes/default.aspx' as [_Linkfor_Warning]
,sum(case when n.Status=6 then 1 else 0 end) AS [Critical]
,'/Orion/Images/StatusIcons/Small-Critical.gif' as [_IconFor_Critical]
,'/orion/nodes/default.aspx' as [_Linkfor_Critcial]
,sum(case when n.Status=2 then 1 else 0 end) AS [Down]
,'/Orion/Images/StatusIcons/Small-Down.gif' as [_IconFor_Down]
,'/orion/nodes/default.aspx' as [_Linkfor_Down]
,sum(case when n.Status=0 then 1 else 0 end) AS [Unknown]
,'/Orion/Images/StatusIcons/Small-Unknown.gif' as [_IconFor_Unknown]
,'/orion/nodes/default.aspx' as [_Linkfor_Unknown]
,sum(case when n.Status=9 then 1 else 0 end) AS [UnManaged]
,'/Orion/Images/StatusIcons/Small-Unmanaged.gif' as [_IconFor_UnManaged]
,'/orion/nodes/default.aspx' as [_Linkfor_UnManaged]
FROM Orion.Nodes n

Union All

(SELECT
concat('SAM - ',count(*),' Applications') as [Category]
,sum(case when a.Availability=1 then 1 else 0 end) AS [Up]
,'/Orion/APM/Images/StatusIcons/Small-App-Up.gif' as [_IconFor_Up]
,'/Orion/APM/Resources/ApplicationHealthDetails.aspx?SelectedStatus=Up' as [_Linkfor_Up]
,sum(case when a.Availability=3 then 1 else 0 end) AS [Warning]
,'/Orion/APM/Images/StatusIcons/Small-App-Warning.gif' as [_IconFor_Warning]
,'/Orion/APM/Resources/ApplicationHealthDetails.aspx?SelectedStatus=Warning' as [_Linkfor_Warning]
,sum(case when a.Availability=6 then 1 else 0 end) AS [Critical]
,'/Orion/APM/Images/StatusIcons/Small-App-Up-Critical.gif' as [_IconFor_Critical]
,'/Orion/APM/Resources/ApplicationHealthDetails.aspx?SelectedStatus=Critical' as [_Linkfor_Critcial]
,sum(case when a.Availability=2 then 1 else 0 end) AS [Down]
,'/Orion/APM/Images/StatusIcons/Small-App-Down.gif' as [_IconFor_Down]
,'/Orion/APM/Resources/ApplicationHealthDetails.aspx?SelectedStatus=Down' as [_Linkfor_Down]
,sum(case when a.Availability=0 then 1 else 0 end) AS [Unknown]
,'/Orion/APM/Images/StatusIcons/Small-App-Unknown.gif' as [_IconFor_Unknown]
,'/Orion/APM/Resources/ApplicationHealthDetails.aspx?SelectedStatus=Unknown' as [_Linkfor_Unknown]
,sum(case when a.Availability=9 then 1 else 0 end) AS [UnManaged]
,'/Orion/APM/Images/StatusIcons/Small-App-Unmanaged.gif' as [_IconFor_UnManaged]
,'/Orion/APM/Resources/ApplicationHealthDetails.aspx?SelectedStatus=Unknown' as [_Linkfor_UnManaged]
FROM Orion.APM.CurrentApplicationStatus a
)

union ALL

(
SELECT
concat('SAM - ',count(*), ' Components') as [Category]
,sum(case when c.Availability=1 then 1 else 0 end)AS [Up]
,'/Orion/APM/Images/StatusIcons/Components/Small-Up.gif' as [_IconFor_Up]
,'' as [_Linkfor_Up]
,sum(case when c.Availability=3 then 1 else 0 end) AS [Warning]
,'/Orion/APM/Images/StatusIcons/Components/Small-Warning.gif' as [_IconFor_Warning]
,'' as [_Linkfor_Warning]
,sum(case when c.Availability=6 then 1 else 0 end) AS [Critical]
,'/Orion/APM/Images/StatusIcons/Components/Small-Up-Critical.gif' as [_IconFor_Critical]
,'' as [_Linkfor_Crtitical]
,sum(case when c.Availability=2 then 1 else 0 end) AS [Down]
,'/Orion/APM/Images/StatusIcons/Components/Small-Down.gif' as [_IconFor_Down]
,'' as [_Linkfor_Down]
,sum(case when c.Availability=0 then 1 else 0 end) AS [Unknown]
,'/Orion/APM/Images/StatusIcons/Components/Small-Unknown.gif' as [_IconFor_Unknown]
,'' as [_Linkfor_Unknown]
,sum(case when c.Availability=9 then 1 else 0 end) AS [UnManaged]
,'/Orion/APM/Images/StatusIcons/Components/Small-Unmanaged.gif' as [_IconFor_UnManaged]
,'' as [_Linkfor_UnManaged]

FROM Orion.APM.CurrentComponentStatus c
)

union ALL


(select
concat('Hardware - ',count(*),' Nodes') as [Category]

,sum(case when hhi.Status=1 then 1 else 0 end) as [Up]
,'/Orion/HardWareHealth/Images/Server_Small_Up.png' as [_IconFor_Up]
,'/Orion/HardwareHealth/Resources/HardwareHealthDetails.aspx?SelectedStatus=Up' as [_Linkfor_Up]

,sum(case when hhi.Status=3 then 1 else 0 end) as [Warning]
,'/Orion/HardWareHealth/Images/Server_Small_Warning.png' as [_IconFor_Warning]
,'/Orion/HardwareHealth/Resources/HardwareHealthDetails.aspx?SelectedStatus=Warning' as [_Linkfor_Warning]

,sum(case when hhi.Status=14 then 1 else 0 end) as [Critical]
,'/Orion/HardWareHealth/Images/Server_Small_Critical.png' as [_IconFor_Critical]
,'/Orion/HardwareHealth/Resources/HardwareHealthDetails.aspx?SelectedStatus=Critical' as [_Linkfor_Critical]

,sum(case when hhi.Status=2 then 1 else 0 end) as [Down]
,'/Orion/HardWareHealth/Images/Server_Small_Down.png' as [_IconFor_Down]
,'/Orion/HardwareHealth/Resources/HardwareHealthDetails.aspx?SelectedStatus=Critical' as [_Linkfor_Down]

,sum(case when hhi.Status=17 then 1 else 0 end) as [Unknown]
,'/Orion/HardWareHealth/Images/Server_Small_Unknown.png' as [_IconFor_Unknown]
,'/Orion/HardwareHealth/Resources/HardwareHealthDetails.aspx?SelectedStatus=Other' as [_Linkfor_Unknown]

,null as [Unmanaged]
,'' as [_IconFor_Unmanaged]
,'' as [_LinkFor_Unmanaged]
from Orion.HardwareHealth.HardwareInfo hhi)

union ALL

(SELECT
concat('WPM - ',count(t.DisplayName), ' Tranactions') as [Category]
,sum(case when t.status=1 then 1 else 0 end) as [Up]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Up.gif' as [_IconFor_Up]
,'' as [_Linkfor_Up]
,sum(case when t.status=3 then 1 else 0 end) as [Warning]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Warning.gif' as [_IconFor_Warning]
,'' as [_Linkfor_Warning]
,sum(case when t.status=14 then 1 else 0 end) as [Critical]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Critical.gif' as [_IconFor_Critical]
,'' as [_Linkfor_Critical]
,sum(case when t.status=2 then 1 else 0 end) as [Down]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Down.gif' as [_IconFor_Down]
,'' as [_LinkFor_Down]
,sum(case when t.status=0 then 1 else 0 end) as [Unknown]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Unknown.gif' as [_IconFor_Unknown]
,'' as [_Linkfor_Unknown]
,sum(case when t.status=9 then 1 else 0 end) as [Unmanage]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Unmanaged.png' as [_IconFor_Unmanage]
,'' as [_Linkfor_unmanage]
FROM Orion.SEUM.Transactions t)


union all
(
SELECT
concat('WPM - ',count(*), ' Steps') as [Category]
,sum(case when ts.status=1 then 1 else 0 end) as [Up]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Step-Up.gif' as [_IconFor_Up]
,'' as [_Linkfor_Up]
,sum(case when ts.status=3 then 1 else 0 end) as [Warning]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Step-Warning.gif' as [_IconFor_Warning]
,'' as [_Linkfor_Warning]
,sum(case when ts.status=14 then 1 else 0 end) as [Critical]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Step-Critical.gif' as [_IconFor_Critical]
,'' as [_Linkfor_Critical]
,sum(case when ts.status=2 then 1 else 0 end) as [Down]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Step-Down.gif' as [_IconFor_Down]
,'' as [_LinkFor_Down]
,sum(case when ts.status=0 then 1 else 0 end) as [Unknown]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Step-Unknown.gif' as [_IconFor_Unknown]
,'' as [_Linkfor_Unknown]
,sum(case when ts.status=9 then 1 else 0 end) as [Unmanage]
,'/Orion/SEUM/Images/StatusIcons/Small-Transaction-Step-Unmanaged.png' as [_IconFor_Unmanage]
,'' as [_Linkfor_unmanage]
FROM Orion.SEUM.TransactionSteps ts
)

Order by [Category]

 

 

Thank you
Amit

email: ashah@loop1.com

Loop1 Systems - SolarWinds Training and Professional Services

 

Labels (3)
Comments

This is a great view of the objects/modules

Version history
Revision #:
1 of 1
Last update:
‎05-28-2020 04:11 PM
Updated by: