Modern Dashboard - Node Count by Custom Property.

dclick

Hello everyone! Been a minute since have had need to post, but I am recently finding myself wanting to do some custom dashboards now.  I watched Lab #93, and a few others, and it seems easy enough, once I get a better handled on SWQL 

I am attempting to create a simple KPI dashboard that will show the total number of Managed nodes based on the custom field "Team".  Alot of gear is sperated betwen the NetOps and SrvOps teams, and i wanted to show that split in Orion. 

Below is my basic query - thanks to General_Apathy for the jump start.   What I need to do is do a count of nodes when the team = netops and different count on team = srvops, etc. 

I can get the count here - its not a single number but its the right amount totoal.  I am just not sure how to pull in the right custom fileds to get the team portion written.  I have muddule my way through enough that i understand what a join does, just not sure how to write one properly.  

SELECT COUNT(1) AS Count_Items, Status
FROM ORION.Nodes
JOIN Orion.NodesCustomProperties ON nodes.nodeid = NodesCustomProperties.nodeid
GROUP BY Nodes.Status

jm_sysadmin

I needed a very similar SWQL statement, I think I got the custom properties correct. I like to group based on Status not status description to keep the groupings smaller. 

SELECT Count( NodeID) as [Count]
, N.CustomProperties.Team

, 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]
FROM Orion.Nodes N
Where N.CustomProperties.Team Is not NULL and N.CustomProperties.Team not like ''
Group By N.CustomProperties.Team, N.Status