Does SWQL support nested queries?

FormerMember

I am trying to produce the following in SWQL. I can do this in SQL (report writer) but I want to do it in SWQL.

TOTAL  UP    DOWN   UNMANAGED   OTHER

89          79         0              10                       0

My SQL query looks like this:

SELECT Count(*) as Total,

(SELECT Count(*) FROM Nodes Where Status = 1 ) as 'UP',

(SELECT Count(*) FROM Nodes Where Status = 2 ) as 'DOWN',

(SELECT Count(*) FROM Nodes Where Status = 9 ) as 'UNMANAGED',

(SELECT Count(*) FROM Nodes Where Status not in (1,2,9) ) as 'OTHER'

FROM Nodes

I know I have to convert to using SWQL nomenclature so this works:

SELECT Count(*) AS Total FROM Orion.Nodes

or

SELECT Count(*) AS Up FROM Orion.Nodes Where Status = 1

etc.

But I want to nest these together, using SWQL, like this:

SELECT Count(*) as TOTAL FROM Orion.Nodes

(SELECT Count(*) as UP FROM Nodes Where Status = 1),

(SELECT Count(*) as DOWN FROM Nodes Where Status = 2),

(SELECT Count(*) as UNMANAGED FROM Nodes Where Status = 9),

(SELECT Count(*) as OTHER FROM Nodes Where Status not in (1,2,9)

Any ideas?

mesverrum

They work, there were just some minor syntax problems, Here you go

SELECT Count(*) as TOTAL,(SELECT Count(*) as UP FROM Orion.Nodes Where Status = 1) as UP,(SELECT Count(*) as DOWN FROM Orion.Nodes Where Status = 2) as DOWN,(SELECT Count(*) as UNMANAGED FROM Orion.Nodes Where Status = 9) as UNMANAGED,(SELECT Count(*) as OTHER FROM Orion.Nodes Where Status not in (1,2,9)) as OTHERFROM Orion.Nodes

I went ahead and got a little fancy with it for you

SELECT Count(*) as TOTAL,  '/Orion/images/StatusIcons/ContainerMembers/DefaultIcon.gif' as [_iconfor_TOTAL],(SELECT Count(*) as UP FROM Orion.Nodes Where Status = 1) as UP, '/Orion/images/StatusIcons/Small-Up.gif' as [_iconfor_UP],(SELECT Count(*) as DOWN FROM Orion.Nodes Where Status = 2) as DOWN, '/Orion/images/StatusIcons/Small-Down.gif' as [_iconfor_DOWN],(SELECT Count(*) as UNMANAGED FROM Orion.Nodes Where Status = 9) as UNMANAGED, '/Orion/images/StatusIcons/Small-Unmanaged.gif' as [_iconfor_UNMANAGED],(SELECT Count(*) as OTHER FROM Orion.Nodes Where Status not in (1,2,9)) as OTHER, '/Orion/images/StatusIcons/Small-NotRunning.gif' as [_iconfor_OTHER]FROM Orion.Nodes

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems