Dashboard / SWQL Question

martian_monster

I am working on learning new dashboards along side figuring out SWQL queries.  I followed along in the lab and created this dashboard to for the basics of dashboards. 

I am doing some small changes to the SWQL queries so this page only shows Windows nodes.  Which was easy -

I would like to have the rest of the widgets on the page only grabbing data from nodes that are Windows and that is where I am having an issue.  Can someone point me in the direction of how to sort these so it is they are just Windows Devices?  I think I may need to do a JOIN clause but not entirely sure.   I was reading another THWACK article that described how to use custom properties to filter by but I think I am missing something. 

SELECT COUNT(1) AS Count_Items, Status
FROM ORION.Nodes
JOIN Orion.NodesCustomProperties ON nodes.nodeid = NodesCustomProperties.nodeid
WHERE Business_Region = 'EMEA'
AND
Client = 'Client A'
GROUP BY Status

Thanks and there will be probably more questions coming.  Thanks - Dave

jm_sysadmin

I stole liberally from an existing dash widget I had below, so if it doesn't flow the same, you weren't doing it wrong, I just didn't start from what you had. @ me if you have questions or I forgot something @martian_monster

SELECT 
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 '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 'Unknown'
End as [Device Status]

, Count(N.NodeID) as [Amount]
FROM Orion.Nodes N 
Where 
        N.CustomProperties.Business_Region = 'EMEA'
    AND N.CustomProperties.Client = 'Client A'
    and N.Vendor LIKE 'Windows'
Group By N.Status

So first I don't want to use the new 'statusdescription' but I wanted what that used to be, so I did my best with that status CASE statement. Grouping by status is absolutely the way to go here like you had. 

Then instead of JOINs I used the SWQL native links. Nodes has a ton of links, we used custom properties here. You can see them in the SWQL Studio from the SDK with the little chain icons below.