Most of the charts on the NCM summary runs after the NCM job completes. Sometimes we want a real time update of it.
Here are two resources which are already there, but needed them them in swql.
SELECT
(SELECT count(*) as [Total Nodes] FROM Cirrus.Nodes cn left join Cirrus.LatestComparisonResults cr on cr.nodeid=cn.NodeID where cr.ComparisonType=1 or cr.diffflag is null) as [Total Nodes]
,concat(sum (case when cr.diffflag=0 and cr.ComparisonType=1 then 1 else 0 end),'') as [No Conflict]
,'/Orion/images/StatusIcons/Small-up.gif' AS [_IconFor_No Conflict]
,sum (case when cr.diffflag=1 and cr.ComparisonType=1 then 1 else 0 end) as [Conflict]
,'/Orion/images/StatusIcons/Small-Critical.gif' AS [_IconFor_Conflict]
,sum (case when cr.diffflag is null then 1 else 0 end) as [Unknown]
,'/Orion/images/StatusIcons/Small-unknown.gif' AS [_IconFor_Unknown]
FROM Cirrus.Nodes cn
left join Cirrus.LatestComparisonResults cr on cr.nodeid=cn.NodeID
order by 1
SELECT
pr.name as [Policy Name]
,concat('/Orion/NCM/ComplianceReportResult.aspx?ReportID=',pr.PolicyReportID) as [_LinkFor_Policy Name]
,tmp.info as [Info]
,case when tmp.info > 0 then '/Orion/NCM/Resources/images/PolicyIcons/Policy.Info.gif'
else '' end as [_IconFor_Info]
,tmp.Warning as [Warning]
,case when tmp.warning > 0 then '/Orion/NCM/Resources/images/PolicyIcons/Policy.Warning.gif'
else '' end as [_IconFor_Warning]
, tmp.error as [Critical]
,case when tmp.error > 0 then '/Orion/NCM/Resources/images/PolicyIcons/Policy.Critical.gif'
else '' end as [_IconFor_Critical]
FROM Cirrus.PolicyReports pr
join (select
rv.reportid, rv.error, rv.Warning, rv.TimeStamp, rv.Info
from Cirrus.PolicyReportViolations rv
where rv.TimeStamp = (select max(TimeStamp) as maxt from Cirrus.PolicyReportViolations)
group by rv.ReportID, rv.error, rv.warning, rv.TimeStamp, rv.Info) as tmp on tmp.ReportID=pr.PolicyReportID
where pr.reportstatus=1
group by pr.name, tmp.error, tmp.Warning, tmp.timestamp, tmp.info, tmp.ReportID, pr.PolicyReportID
order by pr.name
Thanks
Amit