Certificate Audit Solution

Purpose

The purpose of this solution is to pull meaningful certificate information into Automate to be monitored, reported on, or stored for periodic checkups in a dataview.

Update Notice 19-Sept-2024

New Content:

• Internal Monitor: ProVal - Production - Local Machine Certificate Audit
• Alert Template: △ Custom - Local Machine Certificate Audit

Modified Content:

• Script: Windows - Certificates (My) - Local Machine - Audit
• Dataview: SSL Certificate Audit [Script]
• Internal Monitor: ProVal - Production - Certificate Expiration < 30 Days

Modifications:

• The Windows - Certificates (My) - Local Machine - Audit script has been modified to use the Shell function instead of the Execute PowerShell function, as Threatlocker was flagging it.
• With the introduction of the ProVal - Production - Local Machine Certificate Audit internal monitor, the Windows - Certificates (My) - Local Machine - Audit script no longer needs to be scheduled against the group.

Note:

• It is suggested to use the Local Machine Certificate Audit internal monitor to execute the script instead of scheduling it against groups.

Associated Content

Auditing

Content	Type	Function
Certificates (My) - Local Machine - Audit	Script	Gathers, sorts, and imports SSL certificate information into a custom table in the database.
plugin_proval_certs	Custom Table	Custom table created to hold SSL certificate information.
Local Machine Certificate Audit	Internal Monitor	Detects computers where Certificates (My) - Local Machine - Audit has not been executed in the past 7 days.
△ Custom - Local Machine Certificate Audit	Alert Template	Executes the Certificates (My) - Local Machine - Audit script against the computers detected by the Local Machine Certificate Audit internal monitor.
SSL Certificate Audit	Dataview	Displays a comprehensive list of all certificates found with expiration status.

Alerting

Content	Type	Function
Certificate Expiration < 30 Days	Internal Monitor	This monitor checks the plugin_proval_certs table for any SSL certificates that have an expiration date of less than 30 days.
Ticket Creation - Computer	Script	Creates a ticket using the alerting information from the monitor and adds more detailed information to the ticket.
△ Custom - Ticket Creation - Computer	Alert Template	Executes the Ticket Creation - Computer script.

Implementation

1. Import/Update the following content using the Prosync plugin:
   For Auditing Only:

   • Script - Certificates (My) - Local Machine - Audit
   • Internal Monitor - Local Machine Certificate Audit
   • Dataview - SSL Certificate Audit
   • Alert Template - △ Custom - Local Machine Certificate Audit

   For Alerting:

   • Internal Monitor - Certificate Expiration < 30 Days
   • Script - Ticket Creation - Computer
   • Alert Template - △ Custom - Ticket Creation - Computer

2. Reload the system cache:
   

3. Execute the Certificates (My) - Local Machine - Audit script against any online Windows computer to create the plugin_proval_certs custom table.
   

4. Skip this step for new implementations.
   Run this SQL query from a RAWSQL monitor set to remove the script's schedule:

   DELETE FROM groupscripts WHERE scriptid = (SELECT scriptid FROM lt_scripts WHERE scriptGUID = '4f7fd3ff-3732-11e9-b7e5-005056a614c6')

5. Configure the auditing solution as outlined below:
   Navigate to Automation → Monitors within the CWA Control Center and set up the following:

   • Internal Monitor - Local Machine Certificate Audit
     • Alert Template: △ Custom - Local Machine Certificate Audit
     • The monitor should target the following groups:
       • Service Plans.Windows Servers.Server Roles.Windows Messaging Servers
       • Service Plans.Windows Servers.Server Roles.Windows Remote Access Servers
       • Service Plans.Windows Servers.Server Roles.Windows Web/Proxy Servers
       • Service Plans.Windows Servers.Server Roles.Windows Database Servers
       • Service Plans.Windows Servers.Server Roles.MSP Specific Servers
         
     • Right-click and select "Run Now" to start the monitor.

If Requested

6. Please ensure that the alerting solution is implemented only after the consultant has confirmed it.
   Configure the alerting solution as outlined below:
   Navigate to Automation → Monitors within the CWA Control Center and set up the following:
   • Internal Monitor - Certificate Expiration < 30 Days
     • Alert Template: △ Custom - Ticket Creation - Computer
     • Right-click and select "Run Now" to start the monitor.

Changelog

2025-04-10

• Initial version of the document