All nodes report with each nodes thresholds

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

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

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

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

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?

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

WHERE n.Vendor = 'Windows'

That should return only results having Windows as the vendor.

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

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!