Need Help With SWQL Modern Dashboard Returning Higher Count Than Expected

I am attempting to build Modern Dashboards and using SWQL for the first time. So far I have been able to use SWQL successfully to organize very simple tables. The issue I am currently getting is higher than usual counts when I am using the SELECT COUNT(1) syntax. I only want the total count of devices based on a Custom Property (Owner) and that count to be organized by Status. So, basically how many devices are Up/Down/Critical etc. in a select owner group. Below is the script I am using and the results, which are in the millions, but there are only a few hundred devices for each group.

SELECT COUNT(1) AS Item_Count
, [Nodes] .Status
, [CustomProperties] .Owner

FROM Orion.Nodes AS [Nodes]
, Orion.NodesCustomProperties AS [CustomProperties]

WHERE Owner = 'High Schools'

GROUP BY Status, Owner

OUTPUT:

Parents
  • So I would write it as shown below. Most of my changes are cosmetic, some use the features of SWQL (which if you don't have SWQL studio installed from GitHub - solarwinds/OrionSDK: SDK for the SolarWinds Orion platform, including tools, documentation, and samples in PowerShell, C#, Go, Perl, and Java., you should ). I don't have owner as a property to test this, but using my properties it worked. I also added a case statement if you wanted to replace the numeric status value with text label, you can remove it if you don't need it. Anything with -- is commented out. 

    Edit: I updated to fix my copy/paste error so the case statement is better. Hopefully this version works.

    SELECT 
        Count(N.NodeID) as Item_Count
    ,   N.Status
    ,   Case 
            When N.Status = 0 Then 'Unknown'
            When N.Status = 1 Then 'Up'
            When N.Status = 2 Then 'Down'
            When N.Status = 3 Then 'Warning'
    --        When N.Status = 4 Then 'Shutdown'
    --        When N.Status = 5 Then 'Then 'Testing'
    --        When N.Status = 6 Then 'Dormant'
    --        When N.Status = 7 Then 'NotPresent'
    --        When N.Status = 8 Then 'LowerLayerDown'
            When N.Status = 9 Then 'Unmanaged'
    --        When N.Status = 10 Then 'Unplugged'
    --        When N.Status = 11 Then 'External'
            When N.Status = 12 Then 'Unreachable'
            When N.Status = 14 Then 'Critical'
    --        When N.Status = 15 Then 'PartlyAvailable'
    --        When N.Status = 16 Then 'Misconfigured'
    --        When N.Status = 17 Then 'Undefined'
    --        When N.Status = 19 Then 'Unconfirmed'
    --        When N.Status = 22 Then 'Active'
    --        When N.Status = 24 Then 'Inactive'
    --        When N.Status = 25 Then 'Expired'
    --        When N.Status = 26 Then 'MonitoringDisabled'
    --        When N.Status = 27 Then 'Disabled'
    --        When N.Status = 28 Then 'NotLicensed'
    --        When N.Status = 29 Then 'OtherCategory'
    --        When N.Status = 30 Then 'NotRunning'
    --        When N.Status = 31 Then 'Online'
    --        When N.Status = 32 Then 'OnBattery'
    --        When N.Status = 33 Then 'OnSmartBoost'
    --        When N.Status = 34 Then 'TimedSleeping'
    --        When N.Status = 35 Then 'SoftwareBypass'
    --        When N.Status = 36 Then 'Off'
    --        When N.Status = 37 Then 'Rebooting'
    --        When N.Status = 38 Then 'SwitchedBypass'
    --        When N.Status = 39 Then 'HardwareFailureBypass'
    --        When N.Status = 40 Then 'SleepingUntilPowerReturn'
    --        When N.Status = 41 Then 'OnSmartTrim'
    --        When N.Status = 42 Then 'EcoMode'
    --        When N.Status = 43 Then 'HotStandBy'
    --        When N.Status = 44 Then 'OnBatteryTest'
    Else 'Other status'
    End as [Readable Status]
    
    ,   N.CustomProperties.Support_Group 
    FROM Orion.Nodes N
    WHERE N.CustomProperties.Support_Group like '%'
    Group by N.Status, N.CustomProperties.Support_Group

  • I copy pasta into my Modern Dashboard and got an error.

    Tried to fix it and it kept erroring on other debug issues.

    Thank you for your reply!

  • My bad, I copied the wrong block, the case statement was unfinished. Try this:


    SELECT 
        Count(N.NodeID) as Item_Count
    ,   N.Status
    ,   Case 
            When N.Status = 0 Then 'Unknown'
            When N.Status = 1 Then 'Up'
            When N.Status = 2 Then 'Down'
            When N.Status = 3 Then 'Warning'
    --        When N.Status = 4 Then 'Shutdown'
    --        When N.Status = 5 Then 'Then 'Testing'
    --        When N.Status = 6 Then 'Dormant'
    --        When N.Status = 7 Then 'NotPresent'
    --        When N.Status = 8 Then 'LowerLayerDown'
            When N.Status = 9 Then 'Unmanaged'
    --        When N.Status = 10 Then 'Unplugged'
    --        When N.Status = 11 Then 'External'
            When N.Status = 12 Then 'Unreachable'
            When N.Status = 14 Then 'Critical'
    --        When N.Status = 15 Then 'PartlyAvailable'
    --        When N.Status = 16 Then 'Misconfigured'
    --        When N.Status = 17 Then 'Undefined'
    --        When N.Status = 19 Then 'Unconfirmed'
    --        When N.Status = 22 Then 'Active'
    --        When N.Status = 24 Then 'Inactive'
    --        When N.Status = 25 Then 'Expired'
    --        When N.Status = 26 Then 'MonitoringDisabled'
    --        When N.Status = 27 Then 'Disabled'
    --        When N.Status = 28 Then 'NotLicensed'
    --        When N.Status = 29 Then 'OtherCategory'
    --        When N.Status = 30 Then 'NotRunning'
    --        When N.Status = 31 Then 'Online'
    --        When N.Status = 32 Then 'OnBattery'
    --        When N.Status = 33 Then 'OnSmartBoost'
    --        When N.Status = 34 Then 'TimedSleeping'
    --        When N.Status = 35 Then 'SoftwareBypass'
    --        When N.Status = 36 Then 'Off'
    --        When N.Status = 37 Then 'Rebooting'
    --        When N.Status = 38 Then 'SwitchedBypass'
    --        When N.Status = 39 Then 'HardwareFailureBypass'
    --        When N.Status = 40 Then 'SleepingUntilPowerReturn'
    --        When N.Status = 41 Then 'OnSmartTrim'
    --        When N.Status = 42 Then 'EcoMode'
    --        When N.Status = 43 Then 'HotStandBy'
    --        When N.Status = 44 Then 'OnBatteryTest'
    Else 'Other status'
    End as [Readable Status]
    ,   N.CustomProperties.Owner 
    FROM Orion.Nodes N
    WHERE N.CustomProperties.Owner like 'High Schools'
    Group by N.Status, N.CustomProperties.Owner


  • This worked beautifully! Thank you so much!!

Reply Children
No Data