SWQL query to display Top 10 nodes with alerts OS Wise for widget

Question

Currently this is the query I am using, but I need to filter the nodes OS wise ( windows and Linux together)

SELECT TOP 10 COUNT (AlertActiveID) AS Quantity , [Alerts].AlertObjects.RelatedNodeCaption AS [Node] , [Alerts].AlertObjects.RelatedNodeDetailsUrl AS DetailsUrl , [Nodes].Status FROM Orion.AlertActive AS [Alerts] INNER JOIN Orion.Nodes AS [Nodes] ON [Alerts].AlertObjects.RelatedNodeId = [Nodes].NodeID WHERE [Alerts].AlertObjects.RelatedNodeCaption IS NOT NULL GROUP BY [Alerts].AlertObjects.RelatedNodeCaption , [Alerts].AlertObjects.RelatedNodeDetailsUrl ORDER BY COUNT(AlertActiveID) Desc

Appu_Nov · Answer

Thanks @m-milligan , worked like a charm

m-milligan · Answer

Try this: SELECT TOP 10 COUNT (AlertActiveID) AS Quantity , [Alerts].AlertObjects.RelatedNodeCaption AS [Node] , [Alerts].AlertObjects.RelatedNodeDetailsUrl AS DetailsUrl , [Nodes].Status , [Nodes].AssetInventory.ServerInformation.OperatingSystem as [OS] FROM Orion.AlertActive AS [Alerts] INNER JOIN Orion.Nodes AS [Nodes] ON [Alerts].AlertObjects.RelatedNodeId = [Nodes].NodeID WHERE [Alerts].AlertObjects.RelatedNodeCaption IS NOT NULL AND ([Nodes].AssetInventory.ServerInformation.OperatingSystem like '%windows%' OR [Nodes].AssetInventory.ServerInformation.OperatingSystem like '%linux%') GROUP BY [Alerts].AlertObjects.RelatedNodeCaption , [Alerts].AlertObjects.RelatedNodeDetailsUrl ORDER BY COUNT(AlertActiveID) Desc

Appu_Nov · Answer

I want a report on top 10 Nodes limited to windows and Linux