Create report of SSL certificate expiration dates

Hi,

I'm very new to Solarwinds and I have never created any reports before.

I'm using the Application Monitor Template called "SSL Certificate Expiration Dates" I have assigned this template to some nodes so now when I select a node in the component section it shows me the Component Name then Statistic=226 for example which is telling me an SSL cert will expire in 226 days. I want to generate a summary report for all notes that lists when the SSL cert will expire. First of all is there any built in report or dashboard that will provide a clean summary of all nodes and just tell me when the cert will expire? I don't think this is an option so I'm looking at creating a custom report. Just 2 columns - node name, days until expiry. I thought this would be an easy report to create.

I'm in the new report section> Custom Table.

-I add first column = Node->Caption

-I try and add a second column, I'm now looking for data source that will give me a value in days until the cert expires like I can see on the node page. the problem is I can't seem to locate this value anywhere. What category would it be under?

The search only brings up "SSL Days Remaining" which is under the App Insights for IIS. I'm not using this app Insights template, so this does not return any useful info.

Find more posts tagged with

Accepted answers

jm_sysadmin

I hope to post a dashboard, alerts and scripts and a how-to write up in the near future, but nobody likes to wait, try out this SWQL for a report or dash. You can remove the filters for expiring in the next 90 days or already expired.

SELECT 
  [Comp].[Application].[Node].Caption as [Server Name]
, [Comp].[CurrentStatistics].ComponentStatisticData as [Days Until Expiration]
FROM Orion.APM.Component AS Comp
WHERE
(
(([Comp].[CurrentStatistics].[ComponentType]) = (48)) -- all SSL Cert monitors
AND (([Comp].[CurrentStatistics].[ComponentStatisticData]) <= (90)) -- that expire in the next 90 days
AND (([Comp].[CurrentStatistics].[ComponentStatisticData]) != (0)) -- that aren't expired already
)
or ( [Comp].[CurrentStatistics].[ComponentType] = 48 and [Comp].StatusDescription like 'Down') -- or are not getting data
WITH NOLOCK</pre>

All comments

jm_sysadmin

SELECT 
  [Comp].[Application].[Node].Caption as [Server Name]
, [Comp].[CurrentStatistics].ComponentStatisticData as [Days Until Expiration]
FROM Orion.APM.Component AS Comp
WHERE
(
(([Comp].[CurrentStatistics].[ComponentType]) = (48)) -- all SSL Cert monitors
AND (([Comp].[CurrentStatistics].[ComponentStatisticData]) <= (90)) -- that expire in the next 90 days
AND (([Comp].[CurrentStatistics].[ComponentStatisticData]) != (0)) -- that aren't expired already
)
or ( [Comp].[CurrentStatistics].[ComponentType] = 48 and [Comp].StatusDescription like 'Down') -- or are not getting data
WITH NOLOCK</pre>

Ryan3432

Awesome! thanks for that query. worked perfectly!

trilobite_rex

Could you modify this to look for a specific cn of a certificate? if so, how would you do it?