My team has found this rather useful so I figured I'd pass it along. This is a striped down version of an existing Alerting widget, so I can't take credit for the entire thing. Regardless, I hope you find it useful.
This can in theory be used for any app template with a component that has a statistic value that you want to report on. In this case, we use a the SSL Certificate Expiration Date template to validate how many days until the cert expires.
This will provide an output of the node name, app name, and the days until the cert expires, assuming the application is in warning or critical. You can modify the name to fit your needs.
Widget: Custom Query
First Box:
SELECT
n.Caption as [Node]
,n.detailsurl as [_linkfor_Node]
,'/Orion/images/StatusIcons/Small-' + n.Statusicon AS [_IconFor_Node]
,a.Name as Application
,a.detailsurl as [_linkfor_Application]
,'/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Application]
,aps.ComponentStatisticData as [Days to Expire]
from orion.apm.application a
join orion.nodes n on n.nodeid=a.nodeid
join Orion.APM.CurrentStatistics aps on aps.ApplicationID=a.ApplicationID
where a.status in (3,14)
--and (n.caption like '%{SEARCH_STRING}%' or a.name like '%{SEARCH_STRING}%' )
and a.name = 'SSL Certificate Expiration Date' --Change value to your template name
order by aps.[Days to Expire] asc
Second box (if you want to be able to search):
SELECT
n.Caption as [Node]
,n.detailsurl as [_linkfor_Node]
,'/Orion/images/StatusIcons/Small-' + n.Statusicon AS [_IconFor_Node]
,a.Name as Application
,a.detailsurl as [_linkfor_Application]
,'/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Application]
,aps.ComponentStatisticData as [Days to Expire]
from orion.apm.application a
join orion.nodes n on n.nodeid=a.nodeid
join Orion.APM.CurrentStatistics aps on aps.ApplicationID=a.ApplicationID
where a.status in (3,14)
and (n.caption like '%{SEARCH_STRING}%' or a.name like '%{SEARCH_STRING}%' )
and a.name = 'SSL Certificate Expiration Date' --Change value to your template name
order by aps.[Days to Expire] asc
