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

Group Status (swql, sql)

Groups are useful in pulling together similar information.   For example for an application we can put in the nodes, interfaces, volumes, luns, and other aspects of a application.

pastedImage_0.png

The bad part is that we can’t display this in a clean way.   The All groups widget has no filter.  A way around this is to use a group custom property and tag the groups.   For example, all application groups have been tagged with isApp=1.

pastedImage_1.png

Now it’s a simple matter of displaying the status of the apps (swql).

pastedImage_2.png

SELECT

g.name as [Application]

,concat('/Orion/images/StatusIcons/Small-', g.StatusDescription,'.gif') AS [_IconFor_Application]

,concat('/Orion/NetPerfMon/ContainerDetails.aspx?NetObject=C:',g.ContainerID) as [_linkfor_Application]

,gs.total as [Total]

,gs.up as [Up]

,case when gs.up>0 then '/Orion/images/StatusIcons/Small-up.gif' end as [_Iconfor_Up]

,gs.warning as [Warning]

,case when gs.warning>0 then '/Orion/images/StatusIcons/Small-warning.gif' end as [_Iconfor_Warning]

,gs.critical as [Critical]

,case when gs.critical>0 then '/Orion/images/StatusIcons/Small-critical.gif' end as [_Iconfor_Critical]

,gs.down as [Down]

,case when gs.down>0 then '/Orion/images/StatusIcons/Small-down.gif' end as [_Iconfor_Down]

FROM Orion.Groups g

join (

SELECT

cm.ContainerID

,count(*) as [total]

,sum(case when cm.Status=1 then 1 else 0 end) as [up]

,sum(case when cm.Status=3 then 1 else 0 end) as [warning]

,sum(case when cm.Status=14 then 1 else 0 end) as [critical]

,sum(case when cm.Status=2 then 1 else 0 end) as [down]

FROM Orion.ContainerMembers cm

group by cm.ContainerID

order by cm.ContainerID asc

) gs on gs.containerid=g.ContainerID

where g.CustomProperties.isApp=1

order by g.Name

This is good, but still takes up too much screen real estate.  I wanted something concise and to the point – without the need for any scrolling.  Unfortunately, swql does not have the proper constructs – we’ll have to resort to sql.

pastedImage_3.png

select 

max(column1) as C1,

max(column2) as C2,

max(column3) as C3,

max(column4) as C4

from (

select linenumber

,case colnumber when 1 then node else NULL end as column1,

case colnumber when 2 then node else NULL end as column2,

case colnumber when 3 then node else NULL end as column3,

case colnumber when 4 then node else NULL end as column4

from

(select concat('<img src="/orion/images/statusicons/Small-',si.StatusName,'.gif"/>',

'<a href="https://thwack.solarwinds.com/Orion/NetPerfMon/ContainerDetails.aspx?NetObject=C:',cast(tmp.ContainerID as varchar),'">',tmp.Name+'</>') [Node]

,((rownumber-1) / 4) + 1 as linenumber

,((rownumber-1) % 4)+1 as colnumber

from (select c.name, c.ContainerID, c.status,rownumber = ROW_NUMBER() over (order by c.name asc) from Containers c join ContainerCustomProperties cp on cp.ContainerID=c.ContainerID where cp.isApp=1) tmp 

join StatusInfo si on si.StatusId=tmp.Status) tmp1

group by linenumber, tmp1.colnumber, tmp1.node) tmp2

group by tmp2.linenumber

For a NOC view, it's going to be simplified.  They don't need to know the memory or other issues.  Just is the group up or not.

pastedImage_4.png

select 

max(column1) as C1,

max(column2) as C2,

max(column3) as C3,

max(column4) as C4

from (

select linenumber

,case colnumber when 1 then node else NULL end as column1,

case colnumber when 2 then node else NULL end as column2,

case colnumber when 3 then node else NULL end as column3,

case colnumber when 4 then node else NULL end as column4

from

(select

case when tmp.Status=1 then

concat('<img src="/orion/images/statusicons/Small-Up.gif"/>',

'<a href="https://thwack.solarwinds.com/Orion/NetPerfMon/ContainerDetails.aspx?NetObject=C:',cast(tmp.ContainerID as varchar),'">',tmp.Name+'</>')

else

concat('<img src="/orion/images/statusicons/Small-Down.gif"/>',

'<a href="https://thwack.solarwinds.com/Orion/NetPerfMon/ContainerDetails.aspx?NetObject=C:',cast(tmp.ContainerID as varchar),'">',tmp.Name+'</>')

end as [Node]

,((rownumber-1) / 4) + 1 as linenumber

,((rownumber-1) % 4)+1 as colnumber

from (select c.name, c.ContainerID, c.status,rownumber = ROW_NUMBER() over (order by c.name asc) from Containers c join ContainerCustomProperties cp on cp.ContainerID=c.ContainerID where cp.isApp=1) tmp 

join StatusInfo si on si.StatusId=tmp.Status) tmp1

group by linenumber, tmp1.colnumber, tmp1.node) tmp2

group by tmp2.linenumber

Thank you,

amit

Labels (1)
Tags (4)
Version history
Revision #:
1 of 1
Last update:
‎11-09-2019 12:47 PM
Updated by: