2 Replies Latest reply on Jul 12, 2016 7:03 AM by sotherls

    Does SWQL support nested queries?

    sotherls

      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?

        • Re: Does SWQL support nested queries?
          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 OTHER
          
          FROM 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