I'm currently using the SSL: Certificate Expiration template provided by @KMSigma.SWI and it's working great. Now we want to see the data in an easier to digest format. Problem is that the only place I'm able to find all the data pulled by this single component is in the DynamicEvidence table and there's no easy way for me to get just the latest polled data from here. It's looking like it keeps multiple polls for charting purposes. Is there something else I'm missing to get just the latest outputs for a single component that has more than one?
The thought process is this:
SELECT TOP 1 <Thing>FROM <Entity>WHERE <Filters>ORDER BY <Timestamp> DESC
SELECT TOP 1 <Thing>
FROM <Entity>
WHERE <Filters>
ORDER BY <Timestamp> DESC
That'll return only the most recent.
Give me your starting query (the one with all the historical information).
That would work if I'm looking for only the top row but this is for over 200 nodes. Also there's no time stamp on the DynamicEvidence table in SWQL that I'm noticing. I'd probably try and cheat and do WHERE DateTime > MINUTEADD(GetDate(), -7) so I can catch the 5 minute polls with a bit of play in the poll time and data insert times.
Is there some other tables that holds all the outputs for a script other than DynamicEvidence? I'd love to expand on your script and add Thumbprint and maybe SAN entries but starting small first.
SELECT Caption,ComponentID,ColumnName,T1.StringData AS [Issuer],T2.AvgNumericData AS [Days Remaining],T3.StringData AS [Subject]FROM(SELECTDistinct de.ComponentStatus.Component.Application.ApplicationID,DE.ComponentStatus.ComponentID,De.ComponentStatus.Component.Application.Node.Caption,ColumnName,ColumnLabel,AvgNumericData,StringDataFROM Orion.APM.DynamicEvidence DEWHERE ColumnName = 'Issuer' AND AvgNumericData IS NULL ) AS T1INNER JOIN(SELECTDistinct de.ComponentStatus.Component.Application.ApplicationID,DE.ComponentStatus.ComponentID,De.ComponentStatus.Component.Application.Node.Caption,ColumnName,ColumnLabel,AvgNumericData,StringDataFROM Orion.APM.DynamicEvidence DEWHERE ColumnName = 'Expiration' AND AvgNumericData IS NOT NULL ) AS T2 ON T1.ApplicationID = T2.ApplicationID AND T1.Caption = T2.CaptionINNER JOIN(SELECTDistinct de.ComponentStatus.Component.Application.ApplicationID,DE.ComponentStatus.ComponentID,De.ComponentStatus.Component.Application.Node.Caption,ColumnName,ColumnLabel,AvgNumericData,StringDataFROM Orion.APM.DynamicEvidence DEWHERE ColumnName = 'Subject' AND AvgNumericData IS NULL ) AS T3 ON T2.ApplicationID = T3.ApplicationID and T2.Caption = T3.CaptionOrder by Caption, ComponentID
This is what I came up with. I noticed the output had repeated rows with unique days remain counts that were decrementing by one. Only really need the last polled data and it'd be decent.
dkeyser could you put up a link to the template as well? I'm currently assigning a similar template to several thousand nodes and would love to see if the one you are referencing is any better than the one I'm using. Thanks in advance!
I was using the script found here: (+) SSL Certificate Expiration - CI, CN, and Expiry Date - Application Monitor Templates - Server & Application Monitor - THWACK (solarwinds.com)
You are all welcome.
Thanks @dkeyser and @KMSigma loading it up in my test system now
# Shamelessly stolen from https://blog.kmsigma.com/2017/09/08/sam-powershell-scripting-template/
Love it!
