Trying to understand how to remove joins...

keoki

So had some great discussions at the SWUG covering a lot of SWQL ground.  And one of my take-aways was that joins should be a rarity.

Background:  The team handling alerts was suffering from alert fatigue when I joined the team, and I only made it worse initially as I increased coverage by 300%.  95% of the alerts were coming from low priority things like cpu's, memory, disk hitting short term peaks,  and 150 other other low priority thresholds per node (everything here, fans, power supplies, temperatures, etc, we monitor EVERYTHING except temporary filesystems.  We migrated all of these alerts into an Orion Maps structure, so that dashboards could hold the low-priority events to be processed by the second and third shifts.  Worked great for a while, but over time it was not being effectively managed.  So I'm developing a tabular report instead, which will split up the nodes by vendor, and send out a daily report that covers the same ground as Orion Maps.  So we get our dashboard for the managers to look at, and we get reports for the engineers to process, everyone is covered.

SELECT N.Caption AS NodeName, N.IPAddress, SME.Description AS Type, SME.StatusDescription AS Status, SME.AncestorDisplayNames AS Object_details --, N.NodeStatusRootCause
FROM Orion.Nodes AS N
JOIN System.ManagedEntity AS SME
ON SME.AncestorDisplayNames LIKE CONCAT('%', N.Caption ,'%')
JOIN Orion.NodesCustomProperties AS CP
ON N.NodeID = CP.NodeID
WHERE CP.Team = 'CyberOps'
AND N.Vendor NOT IN ('Zanar Networks, Inc.', 'Yabbadabba Networks', 'Somevendor Inc.', 'OtherVendor')
AND SME.Description NOT LIKE ''
AND SME.StatusDescription NOT IN ('Up', 'Node status is Up.', 'Unknown', 'Shutdown', 'Unmanaged')
AND N.Caption NOT IN(
SELECT N.Caption
FROM Orion.AlertSuppression AS A
JOIN Orion.nodes AS N
ON N.uri = A.EntityURI
)
ORDER BY N.Caption

This report supports alert suppression, so nodes under maintenance do not clog this report.  It actually works better than orion maps, but orion maps is an important presentation layer for this data.

I expose one custom property "Team" here, which is one of the main filters as we are in a specific team silo.  The main lift of this report is it shows every minor alert condition, no matter how small, and since we monitor literally everything SolarWinds offers on these nodes except mounted upgrade folders.  Before I filtered on vendor, the report timed out 30% of the time, it was on the edge of running too long.

This version of the report is the catch-all for nodes not matching our major vendors, and I have a lot of filters to eliminate irrelevent data points, so the report is 100% actionable.  But with 3 joins and a nested select I ask myself if I really did this efficiently.  This is a long running query I intend to run once per day, and I did a LOT of tuning to reduce how long it runs, so I was quite happy with it until I was schooled on not using joins.  I'm not an sql expert, but I have been making sql do useful things for 20 years (in the most hackish way possible I'm sure).

KMSigma.SWI

I took a quick stab at this to see what I can see.  Does this do it for you?

SELECT [Nodes].Caption AS [Node Name]
     , [Nodes].IPAddress AS [IP]
     , [Nodes].Description AS [Type]
     , [Nodes].StatusDescription AS [Status]
     , [Nodes].AncestorDisplayNames AS Object_details --, N.NodeStatusRootCause
--   , [Nodes].Uri
--   , [Nodes].CustomProperties.Team
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor NOT IN (
          'Zanar Networks, Inc.'
          , 'Yabbadabba Networks'
          , 'Somevendor Inc.'
          , 'OtherVendor'
          )
     AND [Nodes].Description NOT LIKE ''
     AND [Nodes].StatusDescription NOT IN (
          'Up'
          , 'Node status is Up.'
          , 'Unknown'
          , 'Shutdown'
          , 'Unmanaged'
          )
--   AND [Nodes].CustomProperties.Team = 'Team Name Here'
     AND [Nodes].Uri NOT IN (
          SELECT [Suppression].EntityUri
          FROM Orion.AlertSuppression AS [Suppression]
          WHERE GETUTCDATE() BETWEEN [Suppression].SuppressFrom
                    AND [Suppression].SuppressUntil
          )

I had to comment out the "Team" filter because I don't have it populated, but in my environment, it executes in under 1 second - but that might be because it's sourcing from the nodes table.

I'm not sure what else you are gaining by bringing in the System.ManagedEntities entity, but that dataset is huge and should be avoided if at all possible.  If you really need to query it, then I could probably help get that part together, but figure out a better way to handle the logic.