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="thwack.solarwinds.com/.../ContainerDetails.aspx 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="thwack.solarwinds.com/.../ContainerDetails.aspx as varchar),'">',tmp.Name+'</>')

else

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

'<a href="thwack.solarwinds.com/.../ContainerDetails.aspx 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