Evening all,
I have been working on a report which combines two that utilise SWQL queries as the datasources.
Unfortunately, the link between the two had to be found elsewhere and incorporated in to the current report.
Can anyone take a look at the query below to try and assist?
The report combines a list of alerts with their trigger points and email recipients, along side a list of nodes and the node thresholds.
What I need to have is a list of nodes with the associated alerts showing their trigger points and thresholds and the recipients (although the majority of this will be done in formatting).
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
left join (select AlertID, RelatedNodeID FROM AlertObjects) ao ON ao.RelatedNodeID = cpu.nodeid
LEFT JOIN (SELECT ac.AlertID, ac.Name, ac.Description, ac.Enabled,
ac.Severity,ac.CreatedBy,ac.AlertMessage, FROM Orion.AlertConfigurations) ac ON ac.AlertID = ao.AlertID
LEFT JOIN orion.ActionsAssignments aa on aa.ParentID = ac.AlertID
LEFT JOIN orion.ActionsProperties aa ON ap.ActionID = aa.ActionID
LEFT JOIN orion.Actions a on a.ActionID = aa.ActionID
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
left join (select AlertID, RelatedNodeID FROM AlertObjects) ao ON ao.RelatedNodeID = cpu.nodeid
LEFT JOIN (SELECT ac.AlertID, ac.Name, ac.Description, ac.Enabled,
ac.Severity,ac.CreatedBy,ac.AlertMessage, FROM Orion.AlertConfigurations) ac ON ac.AlertID = ao.AlertID
LEFT JOIN orion.ActionsAssignments aa on aa.ParentID = ac.AlertID
LEFT JOIN orion.ActionsProperties aa ON ap.ActionID = aa.ActionID
LEFT JOIN orion.Actions a on a.ActionID = aa.ActionID
order by n.caption
There is also a where clause before the order, however this has been removed for security purposes and in no way impacts the links to the tables...
Any help would be very much appreciated as I have been looking at this for two days solid and think I am seeing things
Many thanks in advance
J