cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

All nodes report with each nodes thresholds

Jump to solution

Hi All

Audit have asked for a report that shows all nodes monitored and what their specific alerting thresholds are and we cannot find any built-in report that shows this .

We need the report to contain the thresholds for cpu, memory and disk.

Any suggestions/comments are greatly appreciated.

Cheers

Labels (1)
0 Kudos
1 Solution

I fused together 3-4 reports that I had laying around and came up with this one.

pastedImage_0.png

select

n.caption as [Server Name]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Server Name]

,n.detailsurl as [_linkfor_Server Name]

,IP_address

,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 n.cpuload >= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

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

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

END AS [_IconFor_CPU Load]

,case when n.CpuLoadThreshold.Level1Value = n.CpuLoadThreshold.GlobalWarningValue then ('Default (' + tostring(n.CpuLoadThreshold.Level1Value) + '%)')

else ('Custom ('+tostring(n.CpuLoadThreshold.Level1Value)+'%)')

end as [CPU Warning]

,case when n.CpuLoadThreshold.Level2Value = n.CpuLoadThreshold.GlobalCriticalValue then ('Default (' + tostring(n.CpuLoadThreshold.Level2Value) + '%)')

else ('Custom ('+tostring(n.CpuLoadThreshold.Level2Value)+'%)')

end as [CPU Critical]

,case when n.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 n.percentmemoryused >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

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

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

END AS [_IconFor_Memory Used]

,case when n.percentmemoryusedThreshold.Level1Value = n.percentmemoryusedThreshold.GlobalWarningValue then ('Default (' + tostring(n.percentmemoryusedThreshold.Level1Value) + '%)')

else ('Custom ('+tostring(n.percentmemoryusedThreshold.Level1Value)+'%)')

end as [Mem Warning]

,case when n.percentmemoryusedThreshold.Level2Value = n.percentmemoryusedThreshold.GlobalCriticalValue then ('Default (' + tostring(n.percentmemoryusedThreshold.Level2Value) + '%)')

else ('Custom ('+tostring(n.percentmemoryusedThreshold.Level2Value)+'%)')

end as [Mem Critical]

,CASE

WHEN n.responsetime<0 then 'No Response'

ELSE concat(n.responsetime,' ms')

END AS [Latency]

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

,CASE

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

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

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

END AS [_IconFor_Latency]

,case when n.responsetimeThreshold.Level1Value = n.responsetimeThreshold.GlobalWarningValue then ('Default (' + tostring(n.responsetimeThreshold.Level1Value) + ' ms)')

else ('Custom ('+tostring(n.responsetimeThreshold.Level1Value)+' ms)')

end as [Latency Warning]

,case when n.responsetimeThreshold.Level2Value = n.responsetimeThreshold.GlobalCriticalValue then ('Default (' + tostring(n.responsetimeThreshold.Level2Value) + ' ms)')

else ('Custom ('+tostring(n.responsetimeThreshold.Level2Value)+' ms)')

end as [Latency Critical]

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

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

,CASE

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

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

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

END AS [_IconFor_Packet Loss]

,case when n.percentlossThreshold.Level1Value = n.percentlossThreshold.GlobalWarningValue then ('Default (' + tostring(n.percentlossThreshold.Level1Value) + '%)')

else ('Custom ('+tostring(n.percentlossThreshold.Level1Value)+'%)')

end as [Loss Warning]

,case when n.percentlossThreshold.Level2Value = n.percentlossThreshold.GlobalCriticalValue then ('Default (' + tostring(n.percentlossThreshold.Level2Value) + '%)')

else ('Custom ('+tostring(n.percentlossThreshold.Level2Value)+'%)')

end as [Loss Critical]

,v.caption as [Volume]

,tostring(round(v.VolumePercentUsed,0)) + '%' as [Percent Used]

,case when v.caption is null then ''

when v.forecastcapacity.WarningThreshold is null then ('Default (' + (Select tostring(CurrentValue) AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') + '%)')

else ('Custom ('+tostring(v.forecastcapacity.WarningThreshold)+'%)')

end as [Volume Warning]

,case when v.caption is null then ''

when v.forecastcapacity.CriticalThreshold is null then ('Default (' + (Select tostring(CurrentValue) AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') + '%)')

else ('Custom ('+tostring(v.forecastcapacity.CriticalThreshold)+'%)')

end as [Volume Critical]

FROM orion.nodes n

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

FROM Orion.CPUMultiLoadCurrent

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

left join orion.volumes v on v.nodeid=n.nodeid

order by n.caption

- Marc Netterfield, Github

View solution in original post

7 Replies

I fused together 3-4 reports that I had laying around and came up with this one.

pastedImage_0.png

select

n.caption as [Server Name]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Server Name]

,n.detailsurl as [_linkfor_Server Name]

,IP_address

,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 n.cpuload >= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

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

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

END AS [_IconFor_CPU Load]

,case when n.CpuLoadThreshold.Level1Value = n.CpuLoadThreshold.GlobalWarningValue then ('Default (' + tostring(n.CpuLoadThreshold.Level1Value) + '%)')

else ('Custom ('+tostring(n.CpuLoadThreshold.Level1Value)+'%)')

end as [CPU Warning]

,case when n.CpuLoadThreshold.Level2Value = n.CpuLoadThreshold.GlobalCriticalValue then ('Default (' + tostring(n.CpuLoadThreshold.Level2Value) + '%)')

else ('Custom ('+tostring(n.CpuLoadThreshold.Level2Value)+'%)')

end as [CPU Critical]

,case when n.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 n.percentmemoryused >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

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

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

END AS [_IconFor_Memory Used]

,case when n.percentmemoryusedThreshold.Level1Value = n.percentmemoryusedThreshold.GlobalWarningValue then ('Default (' + tostring(n.percentmemoryusedThreshold.Level1Value) + '%)')

else ('Custom ('+tostring(n.percentmemoryusedThreshold.Level1Value)+'%)')

end as [Mem Warning]

,case when n.percentmemoryusedThreshold.Level2Value = n.percentmemoryusedThreshold.GlobalCriticalValue then ('Default (' + tostring(n.percentmemoryusedThreshold.Level2Value) + '%)')

else ('Custom ('+tostring(n.percentmemoryusedThreshold.Level2Value)+'%)')

end as [Mem Critical]

,CASE

WHEN n.responsetime<0 then 'No Response'

ELSE concat(n.responsetime,' ms')

END AS [Latency]

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

,CASE

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

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

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

END AS [_IconFor_Latency]

,case when n.responsetimeThreshold.Level1Value = n.responsetimeThreshold.GlobalWarningValue then ('Default (' + tostring(n.responsetimeThreshold.Level1Value) + ' ms)')

else ('Custom ('+tostring(n.responsetimeThreshold.Level1Value)+' ms)')

end as [Latency Warning]

,case when n.responsetimeThreshold.Level2Value = n.responsetimeThreshold.GlobalCriticalValue then ('Default (' + tostring(n.responsetimeThreshold.Level2Value) + ' ms)')

else ('Custom ('+tostring(n.responsetimeThreshold.Level2Value)+' ms)')

end as [Latency Critical]

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

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

,CASE

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

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

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

END AS [_IconFor_Packet Loss]

,case when n.percentlossThreshold.Level1Value = n.percentlossThreshold.GlobalWarningValue then ('Default (' + tostring(n.percentlossThreshold.Level1Value) + '%)')

else ('Custom ('+tostring(n.percentlossThreshold.Level1Value)+'%)')

end as [Loss Warning]

,case when n.percentlossThreshold.Level2Value = n.percentlossThreshold.GlobalCriticalValue then ('Default (' + tostring(n.percentlossThreshold.Level2Value) + '%)')

else ('Custom ('+tostring(n.percentlossThreshold.Level2Value)+'%)')

end as [Loss Critical]

,v.caption as [Volume]

,tostring(round(v.VolumePercentUsed,0)) + '%' as [Percent Used]

,case when v.caption is null then ''

when v.forecastcapacity.WarningThreshold is null then ('Default (' + (Select tostring(CurrentValue) AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') + '%)')

else ('Custom ('+tostring(v.forecastcapacity.WarningThreshold)+'%)')

end as [Volume Warning]

,case when v.caption is null then ''

when v.forecastcapacity.CriticalThreshold is null then ('Default (' + (Select tostring(CurrentValue) AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') + '%)')

else ('Custom ('+tostring(v.forecastcapacity.CriticalThreshold)+'%)')

end as [Volume Critical]

FROM orion.nodes n

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

FROM Orion.CPUMultiLoadCurrent

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

left join orion.volumes v on v.nodeid=n.nodeid

order by n.caption

- Marc Netterfield, Github

View solution in original post

Good work there pal, I have had a very similar request and this report done the trick!

0 Kudos

Hi @sauders,

Can you share the report template that you use?

We are on NPM12.4 2019.2HF3 but we are not able to generate the report using the custom query.

Thanks in advance!

0 Kudos

How do you use this report?  Is this imported into reports or is this a SQL Query?




0 Kudos

You add the custom query widget to a dashboard and paste it in there

- Marc Netterfield, Github

Thanks!  That was the answer I needed.  I got it setup and it appears to be working. 

Another question, how can I filter the output so that it only returns Windows machines, Vendor=Windows.  What would the syntax need to be and where would it need to go?


0 Kudos

lordmonk​ You should be able to add the following line directly above the last (order by) line.

WHERE n.Vendor = 'Windows'

pastedImage_0.png

That should return only results having Windows as the vendor.