This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Need Help With SWQL Modern Dashboard Returning Higher Count Than Expected

zharrison1989 over 3 years ago

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:

Top Replies

Parents

0 jm_sysadmin over 3 years ago

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

0 zharrison1989 over 3 years ago in reply to jm_sysadmin

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!
Cancel
Vote Up 0 Vote Down

Cancel

0 jm_sysadmin over 3 years ago in reply to zharrison1989

zharrison1989 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

Reply

0 jm_sysadmin over 3 years ago in reply to zharrison1989

zharrison1989 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

Children

0 zharrison1989 over 2 years ago in reply to jm_sysadmin

This worked beautifully! Thank you so much!!
Cancel
Vote Up +1 Vote Down

Cancel