SQL Query

Hello msarkar​,

I recommend using SWQL for a number of reasons, primarily as it is more protected from schema changes than SQL, hence greater sustainability for your query to survive in the wild so to speak. I do still use SQL when absolutely necessary, and using swql definitely has challenges in cases like this as it doesn't have the same exact features as SQL.

In any case, here is a sample of how you could accomplish this using SWQL. You would only need to download SWQL studio on your local machine, connect using solarwinds credentials, and use the below info:

1) Query to get full list of values from your table:

SELECT DISTINCT MachineType

FROM Orion.Nodes

2) Select values and add to WHERE / IN statement (or use whatever logic you like, this was just demonstrating static values)

3) This:

SELECT  n.MachineType as [Type], COUNT(n.NodeID) as [Value]

FROM Orion.Nodes n

WHERE N.MachineType IN ('Windows 2008 Server','Windows 2008 R2 Server')

GROUP BY n.MachineType

UNION ALL(

SELECT 'PostWindows2008 Total' as [Type],  COUNT(n.NodeID) as  [Value]

FROM Orion.Nodes n

WHERE N.MachineType IN ('Windows 2008 Server','Windows 2008 R2 Server')

)

UNION ALL(

SELECT  n.MachineType as [Type], COUNT(n.NodeID) as [Value]

FROM Orion.Nodes n

WHERE N.MachineType IN ('Windows 2012 R2 Server','Windows 2016 Server')

GROUP BY n.MachineType

)

UNION ALL(

SELECT  'PostWindows2008 Total' as [Type] , COUNT(n.NodeID) as [Value]

FROM Orion.Nodes n

WHERE N.MachineType IN ('Windows 2012 R2 Server','Windows 2016 Server')

)

Thank you,

Justin Valdez

Systems Engineer, Tobias International

justin.valdez@tobiassystems.com