3 Replies Latest reply on Aug 20, 2018 5:32 AM by rajasekar

    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?

        • Re: Custom Dashboard For Reboot pending
          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