Version 1

    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