Device Backup Status Count

Could anyone help me build an SWSQL query to count the number of nodes backed up vs not backed up?

I'm hoping to build a new modern dashboard proportional widget, much like the current pie-chart one on the NCM Status page.

I've had a play using the SQL from the report, listing the devices backup status, but I'm not sure how to adapt this to just show the count, rather than a list of each node.

Something like

from

node custom property 'site=siteA'

count device status='backedup'

count device status='not backed up'

I've tried to find the appropriate schema but i'm a bit of a noob when it comes to SQL, as you can probably tell

Thanks!

Find more posts tagged with

Widgets

sql

Modern Dashboard

ncm

Accepted answers

christopher.t.jones123

@george.subnet, i noticed some syntax errors in the one above. The below one should work a little bit better.

SELECT
COUNT (CASE LoginStatus WHEN '' THEN 1 else NULL end) AS [Count], 'Not Backed Up' as [Status]
FROM NCM.Nodes
WHERE site = 'Site1'
UNION ALL (
SELECT
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS [Count], 'Backed Up' as [Status]
FROM NCM.Nodes
WHERE site = 'Site1')
UNION ALL (
SELECT
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS [Count], 'Total' as [Status]
FROM NCM.Nodes
WHERE site = 'Site1')

All comments

george.subnet

I've managed to create this, which although has the data I need, does not fit in with the proportional widget due to how the data is arranged..

I think I need it arranged differently? Any ideas?

SELECT
COUNT (CASE LoginStatus WHEN '' THEN 1 else NULL end) AS NotBackedUp,
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS Backedup,
COUNT (Status) as Total
FROM NCM.Nodes
WHERE site = 'Site1'

christopher.t.jones123

@george.subnet, the proportional widget requires a single column with multiple rows, something like below should get you what you're looking for.

SELECT
COUNT (CASE LoginStatus WHEN '' THEN 1 else NULL end) AS NotBackedUp,
FROM NCM.Nodes
WHERE site = 'Site1'
UNION ALL (
SELECT
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS Backedup,
FROM NCM.Nodes
WHERE site = 'Site1')
UNION ALL (
SELECT
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS Backedup,
FROM NCM.Nodes
WHERE site = 'Site1')

christopher.t.jones123

@george.subnet, i noticed some syntax errors in the one above. The below one should work a little bit better.

SELECT
COUNT (CASE LoginStatus WHEN '' THEN 1 else NULL end) AS [Count], 'Not Backed Up' as [Status]
FROM NCM.Nodes
WHERE site = 'Site1'
UNION ALL (
SELECT
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS [Count], 'Backed Up' as [Status]
FROM NCM.Nodes
WHERE site = 'Site1')
UNION ALL (
SELECT
COUNT (CASE LoginStatus WHEN 'Login OK' THEN 1 else NULL end) AS [Count], 'Total' as [Status]
FROM NCM.Nodes
WHERE site = 'Site1')

george.subnet

Thanks so much - this is exactly what was required and allowed me to get the proportional widget up and running!

I really appreciate you taking the time to help with this