Here is a widget that can be used for a NOC view which list the SSL certs. Nice little view which can also be used for a summary view.
select
n.Caption as [Node]
,n.DetailsURL as [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
,cts.value as [Port]
,CONCAT(round(floor(tmp.data/30),0),' Months ',(tmp.data)-(FLOOR(tmp.data/30)*30),'Days') AS [Expiration Duration]
,concat(substring(tmp.errormessage,CharIndex(':',tmp.errormessage)+1,99),' (',tmp.data,' days)') as [Expiration Date]
,c.DetailsUrl as [_linkfor_Expiration Date]
,case when tmp.data<tmp.ThresholdStatisticCritical then '/Orion/images/StatusIcons/Small-Critical.gif'
when tmp.data<tmp.ThresholdStatisticWarning then '/Orion/images/StatusIcons/Small-Warning.gif'
else '/Orion/images/StatusIcons/Small-up.gif' end as [_Iconfor_Expiration Date]
from Orion.nodes n
join Orion.APM.Application a on a.NodeID=n.NodeID
join orion.apm.Component c on c.ApplicationID=a.ApplicationID
left join Orion.APM.componenttemplate ct on ct.id = c.templateid
left join Orion.APM.ComponentTemplateSetting cts on cts.componenttemplateid = ct.id and cts.[key] = 'portnumber'
left join
(SELECT DISTINCT n.caption, n.nodeid, n.Applications.Name, c.Name, c.ComponentID, c.ApplicationID ,c.CurrentStatistics.ComponentStatisticData as [data], c.CurrentStatistics.ErrorMessage, c.ShortName
,c.DetailsUrl,ct.ThresholdStatisticWarning, ct.ThresholdStatisticCritical
from Orion.Nodes n join Orion.APM.Component c on c.ApplicationID=n.Applications.ApplicationID
join orion.APM.ComponentAlertThresholds ct on ct.ComponentID=c.ComponentID
where c.name like 'SSL%') tmp on tmp.applicationid=a.ApplicationID and tmp.nodeid=n.nodeid
where a.ApplicationTemplateID in (SELECT distinct a.ApplicationTemplateID from Orion.APM.Application a
left join Orion.APM.ApplicationTemplate t on t.ApplicationTemplateID=a.ApplicationTemplateID where t.name like '%SSL%')
group by n.Caption, n.nodeid,n.DetailsURL, n.Statusicon, tmp.data, tmp.shortname,tmp.errormessage, cts.Value, c.DetailsUrl, tmp.ThresholdStatisticCritical, tmp.ThresholdStatisticWarning
order by tmp.data asc
THanks
Amit
