Been a little while since I posted one of these, but yesterday I noticed some users on my team struggling to understand how to see what they needed on the OOTB Application details view, so I glued together a few queries I had used in the past to make this improved version of the application details widget. We actually use tags, so I wanted to make it easier for them to be seen, as well as using another script I had laying around to give an explanation for why any particular component is down or critical.
I've attached the resource export that you can use with my powershell tool, https://thwack.solarwinds.com/t5/NPM-Documents/ResourceImporter-ps1/ta-p/510757
Or you can do it by hand by pasting in the below SQL into a Custom Table widget using a SQL data source
Examples of my version
Out of the box version
------------------------SQL to below
select 'Application Status' as [c], concat('<img src="/Orion/APM/images/StatusIcons/Small-App-',case when a.Availability in ('critical') then 'up-' else '' end,a.availability,'.gif"></> '
,'<a href="/Orion/APM/ApplicationDetails.aspx?netobject=AA:'${applicationid}'">'
,a.Availability
,case when sup.SuppressFrom < GETUTCDATE() and sup.SuppressUntil > GETUTCDATE() then ('<br>Application is Muted from '+cast(sup.SuppressFrom as varchar)+' until '+cast(sup.suppressuntil as varchar)+'. ')
when sup.SuppressFrom < GETUTCDATE() and sup.suppressuntil is null then ('<br>Application is Muted from '+cast(sup.SuppressFrom as varchar)+' until Forever. ')
else ''
end
,case when app.unmanaged = 1 and unmanagefrom is not null and unmanageuntil is not null then ('<br>Application is Unmanaged from '+cast(app.unmanagefrom as varchar)+' until '+cast(app.unmanageuntil as varchar)+'. ')
when app.unmanaged = 1 and app.unmanageuntil is null then ('<br>Application is Unmanaged from '+cast(app.unmanagefrom as varchar)+' until Forever. ')
else ''
end
,'</a>') AS [v]
from APM_ApplicationAlertsData a
join apm_application app on app.id=a.applicationid
left join AlertSuppression2 sup on sup.EntityUri like concat('%ApplicationID=',${applicationid})
where a.applicationid=${applicationid}
union all (
select 'Last Up' as [c]
, case when latest.t1 is not null then cast(latest.t1 as nvarchar)
else 'Unknown - has not been Up in the previous 90 days'
end AS [v]
from solarwindsorion.dbo.apm_application a
left join (
select max(timestamp) as t1
, st.ApplicationID
from [SolarWindsOrion].dbo.[APM_ApplicationStatus] st
where st.availability = 1
group by st.applicationid
) latest on a.ID=latest.ApplicationID
where a.ID = ${applicationid}
)
union all (
select replace(ats.[key],'__','Polling ') as [c]
, case when s.value is not null then concat(s.value,' sec (Custom)')
else concat(ats.value,' sec (Default)')
end AS [v]
from apm_application a
join apm_applicationtemplate at on a.templateid=at.id and a.id=${applicationid}
join APM_ApplicationTemplateSetting ats on ats.ApplicationTemplateID=at.id and ats.[key] in ('__Frequency','__Timeout')
left join APM_ApplicationSetting s on a.id=s.ApplicationID and s.[key] in ('__Frequency','__Timeout') and s.[key]=ats.[key]
)
union all (
select top 1 'Template Tags' as [c]
, '' AS [v]
from APM_Application a
join APM_Tag t on t.TemplateID=a.TemplateID and a.id = ${applicationid}
union all (
select ' - <img src="/Orion/images/TopologyItems/RArrow2.png"> ' as [c]
, t.Name AS [v]
from APM_Application a
join APM_Tag t on t.TemplateID=a.TemplateID and a.id = ${applicationid}
)
)
union all (
select 'Total Components' as [c]
, cast(count(*) as nvarchar) AS [v]
from APM_Component c
left join APM_ComponentTemplateSetting cts on cts.ComponentTemplateID = c.TemplateID and cts.[key] = '__Disabled' and cts.value ='false'
left join APM_ComponentSetting cs on cs.ComponentID = c.ID and cs.[key] = '__Disabled' and cs.value ='false'
where c.applicationid=${applicationid}
and isnull(c.isdisabled,'') <> 1
and (cts.value is not null or cs.value is not null)
)
union all (
select 'Down or Critical Components' as [c]
, cast(count(*) as nvarchar) AS [v]
from APM_AlertsData d
where d.ComponentStatus in ('Down','Critical') and d.ApplicationID=${applicationid}
union all (
select concat(' - <img src="/Orion/images/TopologyItems/RArrow2.png"> ',c.Name) as [c]
,concat('<img src="/Orion/images/StatusIcons/Small-',st.DisplayName,'.gif"> Reason: ',case
when cst.ComponentType in (1,8) and cs.Availability=2 then concat(cs.InstanceCount, ' instances of ',serv.value, ' found')
when cst.ComponentType in (2,37,38,39,55,63) and cs.Availability=2 then cst.ErrorMessage
when cst.ComponentType in (9) and cs.Availability=2 then concat(serv.value, ' ',cst.ErrorMessage)
when cst.ComponentType in (6,14) and cs.Availability in (2,12) then concat(replace(replace(replace(url.value,'${IP}',n.IP_Address),'${port}',port.value),'${Node.Caption}',n.caption), ' returned: ',cst.ErrorMessage)
when cst.ComponentType in (42) and cs.Availability in (2,5,6) then concat(cs.StatisticData, ' events matched the rule, see component view for details')
when cst.ComponentType in (21) and cs.Availability=2 then concat(cav.multivaluestatistics, ' - ',replace(cav.multivaluemessages,'Statistic:',''))
when cst.ComponentType in (1,6,8,9,14,20,21,45,32,37) and cs.Availability = 6 then concat(
case when multivaluestatistics is not null then concat(cav.multivaluestatistics, ' - ',replace(cav.multivaluemessages,'Statistic:',''))
else '' end
, case when tbc.ThresholdName is not null then concat(tbc.thresholdname ,' is '
, case when tbc.thresholdname = 'CPU' then cst.ComponentPercentCPU
when tbc.thresholdname = 'PMem' then cst.ComponentPercentMemory
when tbc.thresholdname = 'VMem' then cst.ComponentPercentVirtualMemory
when tbc.thresholdname = 'IOReadOperationsPerSec' then cst.ComponentIOReadOperationsPerSec
when tbc.thresholdname = 'IOWriteOperationsPerSec' then cst.ComponentIOWriteOperationsPerSec
when tbc.thresholdname = 'IOTotalOperationsPerSec' then cst.ComponentIOTotalOperationsPerSec
when tbc.thresholdname = 'Response' then cst.ComponentResponceTime
when tbc.thresholdname = 'StatisticData' then cst.ComponentStatisticData
end
, ', threshold is ' , case
when tbc.thresholdoperator = 0 then 'greater than '
when tbc.thresholdoperator = 1 then 'greater than or equal to '
when tbc.thresholdoperator = 2 then 'equal to '
when tbc.thresholdoperator = 3 then 'less than or equal to '
when tbc.thresholdoperator = 4 then 'less than '
when tbc.thresholdoperator = 5 then 'not equal to '
end
, cast(tbc.critical as varchar)
, ' for ', isnull(isnull(ovr.criticalpolls,t.criticalpolls),1)
, case when isnull(ovr.criticalpolls,t.criticalpolls) != isnull(ovr.criticalpollsinterval,t.criticalpollsinterval) then concat(' of ',isnull(ovr.criticalpollsinterval,t.criticalpollsinterval)) else '' end
, case when isnull(ovr.criticalpolls,t.criticalpolls) > 1 then ' polls' else ' poll' end, CHAR(13)
) else '' end)
when cs.Availability=2 then '...'
else 'To be categorized...'
end, CHAR(10)) AS [v]
from nodes n
join APM_Application a on a.nodeid=n.nodeid
join APM_Component c on c.applicationid=a.id
join APM_CurrentComponentStatus cs on c.id=cs.componentID
join APM_CurrentStatistics cst on cst.componentid=c.id
join APM_StatusMetadata st on st.ApmStatusValue=cs.Availability
left join APM_ThresholdsByComponent tbc on tbc.ComponentID=c.id and tbc.Critical < 1.797E+308
and (
(tbc.thresholdname = 'StatisticData' and (
(tbc.thresholdoperator = 0 and (cst.componentstatisticdata > statisticcritical)) or
(tbc.thresholdoperator = 1 and (cst.componentstatisticdata >= statisticcritical)) or
(tbc.thresholdoperator = 2 and (cst.componentstatisticdata = statisticcritical)) or
(tbc.thresholdoperator = 3 and (cst.componentstatisticdata <= statisticcritical)) or
(tbc.thresholdoperator = 4 and (cst.componentstatisticdata < statisticcritical)) or
(tbc.thresholdoperator = 5 and (cst.componentstatisticdata != statisticcritical))))
or
(tbc.thresholdname = 'Response' and (
(tbc.thresholdoperator = 0 and (cst.componentresponcetime > responsetimecritical)) or
(tbc.thresholdoperator = 1 and (cst.componentresponcetime >= responsetimecritical)) or
(tbc.thresholdoperator = 2 and (cst.componentresponcetime = responsetimecritical)) or
(tbc.thresholdoperator = 3 and (cst.componentresponcetime <= responsetimecritical)) or
(tbc.thresholdoperator = 4 and (cst.componentresponcetime < responsetimecritical)) or
(tbc.thresholdoperator = 5 and (cst.componentresponcetime != responsetimecritical))))
or
(tbc.thresholdname = 'CPU' and (
(tbc.thresholdoperator = 0 and (cst.componentpercentcpu > cpucritical)) or
(tbc.thresholdoperator = 1 and (cst.componentpercentcpu >= cpucritical)) or
(tbc.thresholdoperator = 2 and (cst.componentpercentcpu = cpucritical)) or
(tbc.thresholdoperator = 3 and (cst.componentpercentcpu <= cpucritical)) or
(tbc.thresholdoperator = 4 and (cst.componentpercentcpu < cpucritical)) or
(tbc.thresholdoperator = 5 and (cst.componentpercentcpu != cpucritical))))
or
(tbc.thresholdname = 'PMem' and (
(tbc.thresholdoperator = 0 and (cst.componentpercentmemory > physicalmemorycritical)) or
(tbc.thresholdoperator = 1 and (cst.componentpercentmemory >= physicalmemorycritical)) or
(tbc.thresholdoperator = 2 and (cst.componentpercentmemory = physicalmemorycritical)) or
(tbc.thresholdoperator = 3 and (cst.componentpercentmemory <= physicalmemorycritical)) or
(tbc.thresholdoperator = 4 and (cst.componentpercentmemory < physicalmemorycritical)) or
(tbc.thresholdoperator = 5 and (cst.componentpercentmemory != physicalmemorycritical))))
or
(tbc.thresholdname = 'VMem' and (
(tbc.thresholdoperator = 0 and (cst.componentpercentvirtualmemory > virtualmemorycritical)) or
(tbc.thresholdoperator = 1 and (cst.componentpercentvirtualmemory >= virtualmemorycritical)) or
(tbc.thresholdoperator = 2 and (cst.componentpercentvirtualmemory = virtualmemorycritical)) or
(tbc.thresholdoperator = 3 and (cst.componentpercentvirtualmemory <= virtualmemorycritical)) or
(tbc.thresholdoperator = 4 and (cst.componentpercentvirtualmemory < virtualmemorycritical)) or
(tbc.thresholdoperator = 5 and (cst.componentpercentvirtualmemory != virtualmemorycritical))))
or
(tbc.thresholdname = 'IOReadOperationsPerSec' and (
(tbc.thresholdoperator = 0 and (cst.componentioreadoperationspersec > ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 1 and (cst.componentioreadoperationspersec >= ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 2 and (cst.componentioreadoperationspersec = ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 3 and (cst.componentioreadoperationspersec <= ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 4 and (cst.componentioreadoperationspersec < ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 5 and (cst.componentioreadoperationspersec != ioreadoperationsperseccritical))))
or
(tbc.thresholdname = 'IOWriteOperationsPerSec' and (
(tbc.thresholdoperator = 0 and (cst.componentiowriteoperationspersec > iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 1 and (cst.componentiowriteoperationspersec >= iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 2 and (cst.componentiowriteoperationspersec = iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 3 and (cst.componentiowriteoperationspersec <= iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 4 and (cst.componentiowriteoperationspersec < iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 5 and (cst.componentiowriteoperationspersec != iowriteoperationsperseccritical))))
or
(tbc.thresholdname = 'IOTotalOperationsPerSec' and (
(tbc.thresholdoperator = 0 and (cst.componentiototaloperationspersec > iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 1 and (cst.componentiototaloperationspersec >= iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 2 and (cst.componentiototaloperationspersec = iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 3 and (cst.componentiototaloperationspersec <= iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 4 and (cst.componentiototaloperationspersec < iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 5 and (cst.componentiototaloperationspersec != iototaloperationsperseccritical))))
)
left join APM_Threshold t on c.id=t.id and t.thresholdname=tbc.thresholdname and t.istemplate=1
left join APM_Threshold ovr on c.id=ovr.id and ovr.thresholdname=tbc.thresholdname and ovr.istemplate=0
left join apm_componentalertvariable cav on cav.componentid=c.id
left join APM_ComponentDetails url on c.id=url.ID and url.[Key] = 'URL'
left join APM_ComponentDetails port on c.id=port.ID and port.[Key] = 'portnumber'
left join APM_ComponentDetails serv on c.id=serv.ID and serv.[Key] in ('ServiceName','ProcessName')
where c.ApplicationID=${applicationid}
and st.displayname in ('down','critical')
)
)
