Custom Dashboard For Reboot pending

bartley

I want to create a dashboard like this.

the only thing that i want to add is a filter that only shows systems that have the SAM template Windows Update Monitoring.

And the only systems that needs to be in the dashboard are the systems That have available critical updates or if a Reboot is required.

How would my query look?

bartley

In the meantime i have the solution.

For everybody that's interested in the query. Here i go.

The dashboard looks like this.

select n.caption as [Node], n.detailsurl as [_Linkfor_Node], n.ip_address as [IP],

case when n.cpuload < 0 then 'Not Polled'

when n.host.nodeid is not null and n.host.cpucorecount is not null then concat(round(n.host.cpuload,0),'% of ',n.host.CpuCoreCount,' CPU')

when cpu.[cpu count] is not null then concat(cpuload,'% of ',cpu.[cpu count],' CPU')

else 'Polling Error'

end as [CPU Load]

,'/Orion/NetPerfMon/CustomChart.aspx?chartName=HostAvgCPULoad&NetObject=N:'+tostring(n.nodeid)+'&Period=Today' as [_linkfor_CPU Load]

,CASE

WHEN cpuload >= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN cpuload >= n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN cpuload <  n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_CPU Load]

,case when percentmemoryused < 0 then 'Not Polled'

else concat(percentmemoryused,'% of ',(round(n.totalmemory/1073741824,0)),' GB')

end as [Memory Used]

,'/Orion/NetPerfMon/CustomChart.aspx?chartName=HostAvgPercentMemoryUsed&NetObject=N:'+tostring(n.nodeid)+'&Period=Today' as [_linkfor_Memory Used]

,CASE

WHEN percentmemoryused >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN percentmemoryused >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN percentmemoryused <  n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Memory Used]

,CASE

WHEN responsetime<0 then 'No Response'

ELSE concat(responsetime,' ms')

END AS [Latency]

,'/Orion/NetPerfMon/CustomChart.aspx?chartName=AvgRt&NetObject=N:'+tostring(n.nodeid)+'&Period=Today' as [_linkfor_Latency]

,CASE

WHEN responsetime >= n.responsetimeThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN responsetime >= n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN responsetime <  n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Latency]

,concat(percentloss,'%') as [Packet Loss]

,'/Orion/NetPerfMon/CustomChart.aspx?chartName=PercentLoss&NetObject=N:'+tostring(n.nodeid)+'&Period=Today' as [_linkfor_Packet Loss]

,CASE

WHEN percentloss >= n.percentlossThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN percentloss >= n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN percentloss <  n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Packet Loss]

--,case

--when vol.high is null then 'None Monitored'

--else tostring(vol.[# of Volumes])

--end as [# of Volumes]

,CASE

When n.SystemUpTime < 0 Then 'N/A'

Else DAYDIFF('1970-1-1',AddSecond(n.SystemUpTime, '1970-1-1'))

end AS [UpTime In Days]

,case

when vol.high is null then 'N/A'

when voldown.down is not null then (tostring(voldown.down)+' Down')

else (tostring(vol.high) + ' %')

end as [Fullest Disk]

,CASE

WHEN voldown.down is not null then '/Orion/images/StatusIcons/Small-Down.gif'

WHEN vol.[High] >= 95 then '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN vol.[High] >= 80 THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN vol.[High] >=  0 THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Fullest Disk]

,'/Orion/DetachResource.aspx?ResourceID=21218&NetObject=N:' + tostring(n.nodeid) as [_linkfor_Fullest Disk] -- Need to change resourceid to match your environment

,CASE

When CritUp >= 1 then 'Update'

Else 'No'

End As [Critical Update]

,CASE

When Reboot >= 1 then 'Reboot'

Else 'No'

End As [Reboot Required]

from orion.nodes n

left join (SELECT NodeID AS Node, ComponentStatisticData as critup, nodeid

FROM Orion.APM.CurrentStatistics

Where ComponentName Like 'Available Critical Updates') Node on Node.nodeid=n.nodeid

left join (SELECT NodeID AS NAPM, ComponentStatisticData as Reboot, nodeid

FROM Orion.APM.CurrentStatistics

Where ComponentName Like 'Machine restart status after installing Windows Updates') NAPM on NAPM.nodeid=n.nodeid

left join (SELECT count(NodeID) as [CPU Count], nodeid

FROM Orion.CPUMultiLoadCurrent

group by nodeid) cpu on cpu.nodeid=n.nodeid

left join (SELECT nodeid, count(volumeid) as [# of Volumes], round(max(volumepercentused),0) as [High]

FROM Orion.Volumes where volumetype='Fixed Disk'

group by nodeid) vol on vol.nodeid=n.nodeid

left join (SELECT nodeid, count(volumeid) as [down]

FROM Orion.Volumes where status=2 and volumetype='Fixed Disk'

group by nodeid) voldown on voldown.nodeid=n.nodeid

where

critup >=1 Or

Reboot >=1

ORDER by n.Caption