We all know about the NCM - Startup vs Running chart. It doesn't give us detailed information on vendor..
I wanted something compact and broken down by vendor. I came with with this and it worked out nicely. There a small issue with the percentage rounding error and it probably has to do with the way SWQL does arithmetic, as it works perfectly in SQL when I cast as float.
SELECT
'Cisco' as [Vendor]
,(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Cisco%') as [Total Nodes]
,concat(isnull(sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Cisco%'))+0.5,1),'%)') as [No Conflict]
,'/orion/images/statusicons/Small-up.gif' as [_iconfor_No Conflict]
,concat(isnull(sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Cisco%'))+0.5,1),'%)') as [Conflict]
,'/orion/images/statusicons/Small-critical.gif' as [_iconfor_Conflict]
,concat(isnull((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Cisco%')-sum(case when cr.ComparisonType=1 then 1 else 0 end),0),' (',round((((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Cisco%') - sum(case when cr.ComparisonType=1 then 1 else 0 end))*100)/(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Cisco%')+0.5,1),'%)') as [Unknown]
,'/orion/images/statusicons/Small-Unknown.gif' as [_iconfor_Unknown]
FROM NCM.NodeProperties cn
left join Cirrus.LatestComparisonResults cr on cr.nodeid=cn.NodeID
where cn.Nodes.Vendor like 'Cisco%'
group by cn.Nodes.Vendor
UNION
(
SELECT
'F5 Network' as [Vendor]
,(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'F5%') as [Total Nodes]
,concat(isnull(sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties where cn.Nodes.Vendor like 'F5%'))+0.0,0),'%)') as [No Conflict]
,'/orion/images/statusicons/Small-up.gif' as [_iconfor_No Conflict]
,concat(isnull(sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties where cn.Nodes.Vendor like 'F5%'))+0.0,1),'%)') as [Conflict]
,'/orion/images/statusicons/Small-critical.gif' as [_iconfor_Conflict]
,concat(isnull((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'F5%')-sum(case when cr.ComparisonType=1 then 1 else 0 end),0),' (',round((((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'F5%') - sum(case when cr.ComparisonType=1 then 1 else 0 end))*100)/(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'F5%')+0.0,1),'%)') as [Unknown]
,'/orion/images/statusicons/Small-Unknown.gif' as [_iconfor_Unknown]
FROM NCM.NodeProperties cn
left join Cirrus.LatestComparisonResults cr on cr.nodeid=cn.NodeID
where cn.Nodes.Vendor like 'F5%'
group by cn.Nodes.Vendor
)
UNION
(
SELECT
'Gigamon Systems' as [Vendor]
,(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Gigamon%') as [Total Nodes]
,concat(isnull(sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties where cn.Nodes.Vendor like 'Gigamon%'))+0.0,0),'%)') as [No Conflict]
,'/orion/images/statusicons/Small-up.gif' as [_iconfor_No Conflict]
,concat(isnull(sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties where cn.Nodes.Vendor like 'Gigamon%'))+0.0,1),'%)') as [Conflict]
,'/orion/images/statusicons/Small-critical.gif' as [_iconfor_Conflict]
,concat(isnull((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Gigamon%')-sum(case when cr.ComparisonType=1 then 1 else 0 end),0),' (',round((((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Gigamon%') - sum(case when cr.ComparisonType=1 then 1 else 0 end))*100)/(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Gigamon%')+0.0,1),'%)') as [Unknown]
,'/orion/images/statusicons/Small-Unknown.gif' as [_iconfor_Unknown]
FROM NCM.NodeProperties cn
left join Cirrus.LatestComparisonResults cr on cr.nodeid=cn.NodeID
where cn.Nodes.Vendor like 'Gigamon%'
group by cn.Nodes.Vendor
)
UNION
(
SELECT
'Juniper Networks, Inc.' as [Vendor]
,(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Juniper%') as [Total Nodes]
,concat(isnull(sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Juniper%'))+0.0,0),'%)') as [No Conflict]
,'/orion/images/statusicons/Small-up.gif' as [_iconfor_No Conflict]
,concat(isnull(sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end),0),' (',round((sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end)*100)/((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Juniper%'))+0.0,1),'%)') as [Conflict]
,'/orion/images/statusicons/Small-critical.gif' as [_iconfor_Conflict]
,concat(isnull((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Juniper%')-sum(case when cr.ComparisonType=1 then 1 else 0 end),0),' (',round((((SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Juniper%') - sum(case when cr.ComparisonType=1 then 1 else 0 end))*100)/(SELECT count(*) as [Total Nodes] FROM ncm.NodeProperties cn where cn.Nodes.Vendor like 'Juniper%')+0.0,1),'%)') as [Unknown]
,'/orion/images/statusicons/Small-Unknown.gif' as [_iconfor_Unknown]
FROM NCM.NodeProperties cn
left join Cirrus.LatestComparisonResults cr on cr.nodeid=cn.NodeID
where cn.Nodes.Vendor like 'Juniper%'
group by cn.Nodes.Vendor
)
