cancel
Showing results for 
Search instead for 
Did you mean: 

SSL Certificate Experiation Date Monitor (SWQL)

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.

pastedImage_0.png

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

Tags (2)
Comments

Amit - Looks good!  Unfortunately, I'm getting an error message saying Function CharIndex not found.  I'm using SWQL Studio version 2.2.54.0.  Appreciate any assistance.  Thanks!

Version history
Revision #:
1 of 1
Last update:
‎01-16-2019 07:53 PM
Updated by: