A significant part of the monthly grind for patches, also know as Software Updates, for an Enterprise company is managing the release schedule, sending out consistent communications, and capturing compliance data for the executive leadership team (ELT).
To avoid busy work, I have written automation to accomplish the goal of sending out consistent communications both for users, leadership, and administrators for operational insights. Enabling the ConfigMgr administrator to keep a pulse on the progression of the rollout each month.
The PatchSendComms.ps1 PowerShell script I am sharing with you can be used for any communication, not just patching. It is designed to pull information from a SQL Database, enabling you to target a list of contacts based on the returned query results and include tables within the email communication.
It does however, offer very specific functionality tailored to patching by enabling the scheduling communications on a specific number of days offset from Patch Tuesday (in combination with a daily scheduled task to run the script in scheduled mode).
What are some things that you may want to send an automated communication about?
- Release Schedule – Your field technicians or IT team may want to receive a summary of the patch release schedule for the month. Also helpful is sending the schedule the day before the official release schedule email. This can be critical if you are relying on maintenance windows set on the collections.
- Deployment Review – Send a summary of your scheduled deployments for the month. Ensure the deployment details are correct.
- Compliance– Send current compliance summary to field technicians to help drive compliance adoption. Send and official compliance score card at a specific interval (30 day, 60 day, etc.). Attach the list of workstations and/or servers out of compliance.
- User Communications – Send a pilot and/or production notification to your users, providing them with key information about monthly patching.
The script solution can be downloaded from PatchSendComms_v1.0.zip.
Generates a communication either by specifying a specific message ID or in schedule mode. Communication templates and the schedule are loaded from the \PatchConfig.xml.
-Communication (Required – unless Schedule mode). Specifies which message template ID to use from PatchConfig.xml.
-DumpTemplate (Optional) – Generates HTML files to preview templates. For each message ID, an HTML file is generated in the \Templates sub-folder.
-DumpExpand (Optional) – Used in combination with DumpTemplate, specifies to expand PowerShell variables when generating the preview HTML templates.
-Resend (Optional). By default, the same message template will not be sent more than once per day. In the case of a contact list, the message is always processed, but individual contact emails not sent more than once a day. This setting overrides and always resends the message. This is useful for testing, or to reissue a corrected communication.
-ListSchedule (Optional) – Provides output of all scheduled communications.
-Schedule (Optional) – In schedule mode, communications are sent based on their number of days from Patch Tuesday, instead of sending an individual communication. If there are no matching scheduled communications, no communications are sent.
-PatchTuesday (Optional) – Overrides the Patch Tuesday date. Used for testing changes.
-To (Optional) – Overrides the recipient for the communication (does not affect contact list message templates). Used for testing changes.
Configuration file for the communication script.
connection – Specify ConfigMgr, SMTP and database environment.
- id (required) – Identifier (sccm & smtp required)
- server (required) – Server name used for the connection
- sql (optional) – When a database connection, must specific the SQL server name
- database (optional) – When a database connection, must specific the SQL database name
- sitecode – Required for sccm
- port – Required for smtp
- sendusing – Required for smtp
For example, to add a connection to your CMDB SQL database you would use:
<connection id= "cmdb" server="sqlservername" database="cmdb"/>
Later referencing the connection to add table data, you would specify the identifier in the connection attribute:
<table id="myassets" connection="cmdb"><![CDATA[
Select Name, Owner From MyAssets
notifications/notice – Specify a specific communication that should be sent a specific number of days after Patch Tuesday. Supports going back two months from the current date/time the script is run.
- name (required) – Identifier that maps to the message id.
- days (required) – Number of days from Patch Tuesday to trigger communication.
- hour (optional) – Specific hour which communication should be triggered (24-hour time).
For example, you may want most communications to go out at 10 a.m. and your overall compliance report at the end of the day at 5 p.m. Set a scheduled task to trigger the script with the following parameters every day at 10 a.m. and 5 p.m.
For communications you want to send at 5 p.m. you would specify the hours in 24-hour format:
<notice name="OverallCompliance" days="60" hour="17"/>
header – HTML header used in email communications. This is leveraged to inject HTML Styles in the message. Must be encapsulated in <![CDATA[ ]]>to prevent XML parser from barfing. Yes “barfing” is my technical description.
Formatting in Outlook can be very different from how it is presented in a web browser. I will use the -DumpTemplates to generate the HTML templates, and then I will edit them to get the look and feel how I like it, before incorporating those changes back into PatchConfig.xml and generating and email to confirm the formatting works.
message – Specify a specific communication that should be sent a specific number of days after Patch Tuesday. Supports going back two months from the current date/time the script is run.
- type (required) – Identifier for the message template.
- notify (optional) – Applies to message templates with contact list only.
- individual – if multiple users specified in a contact list, send email to each contact.
- group (default) – if not specified, default value. If multiple users specified in a contact list, sends email to all contacts at once.
When using a contact list, you must specify %ContactList% for group, and %ContactEmail% for individual as a placeholder in one of the recipient fields (to/cc/bcc).
from (required) – Send from used in email.
to (required) – Recipients for message.
cc (optional) – Recipients copied on message.
bcc (optional) – Recipients blind copied on message.
subject (optional/parsed) – Subject of message. Supports PowerShell and Environment variable parsing.
image (optional) – Image to attach to email. Specify the file name.
Images can be referenced within the email communication using by specifying content identifier within the body.
Example adding logo email to message:
<image id="logo" type="image/png">logo.png</image>
Reference added image within the body:
<img src="cid:logo" alt="www.contoso.com"/>
attachment (optional) – File generated from SQL query to be attached to the message. The node text should be wrapped in <![CDATA[ ]]> tags to ensure it safely parses.
- id (required) – Identifier for attachment.
- filename (required) – File name for attachment.
- connection (required) – Identifier for connection used to perform the SQL data query.
table (optional) – Generates a data set with tables that can be used in body of message. The node text should be wrapped in <![CDATA[ ]]> tags to ensure it safely parses.
- id (required) – Identifier for table. Placeholder %id_table% can be used in message body, or %id_table#% for data sets.
- connection (required) – Identifier for connection used to perform the SQL data query.
- type (optional) – Optionally a type of dataset can be specified if a query returns multiple tables. Otherwise only the first table in a query is returned.
To reference table in message body, specify the place holder value, for example for a table with the id of summary:
body (required/parsed) – HTML body used in message. Supports PowerShell and Environment variable parsing. It should be encapsulated in the <![CDATA[ ]]> tags to ensure it safely parses. It must contain the following, at a minimum, if you want the header style to apply to the message:
HTML body goes here.
The solution comes with several canned examples for notifications.
ScheduleCheck – Summary of schedule sent day before Patch Tuesday. Especially helpful if maintenance windows are used.
DeploymentReview– Review of scheduled patch deployments, sent after cut off time for administrator to setup all deployments. Enables peer review/sign off and sanity check for administrator.
WorkstationPilot – Communication to users in the patch pilot group.
WorkstationProduction – Communication to all users.
WorkstationCompliance– Compliance status for workstations, for field techs, or personnel responsible for working with users to get all devices patched.
PatchSchedule – Sample communication to be send to service desk or field technicians informing them of the patch schedule for the month.
OverallCompliance – Overall patch compliance for workstations and servers with attached list of out of compliance systems.