2 Replies Latest reply on Mar 22, 2018 11:50 AM by bartley

    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
          tony.johnson

          HI Bartley,

          Interesting question - I must take a look.

          This may be a helpful start

          [SOLVED] SWQL Query: How Do I Output "Statistic" and "Message" Values For The Component?

          • 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