Jeremy Young at myITforum.com : Patch Management

PowerShell Module For ConfigMgr OnDemand Patch Management

JeremyYoung — Wed, 29 Aug 2012 16:15:00 GMT

I developed a PowerShell module that will scan for update compliance or launch a task sequence to install required security updates (or any advertised task sequence) on either a single machine, or multiple machines using multithreading.

The OnDemand module is a supplemental tool for our automated patching service. Our automated patching solution of enrolling a server in a monthly maintenance window and having ConfigMgr patch it works for most of our servers but there are times when a server owner needs more control over when a server is patched. For example, a critical business process may require a server to be patched outside of the scheduled maintenance window. Likewise, there are servers that can’t us a routine monthly maintenance window because they require downtime approvals from their customers. The OnDemand Patch Management module fills this gap.

I have blogged about individual components that are the foundation of module in previous posts:

PowerShell Script to Check ConfigMgr Agent Software Update Compliance

PowerShell Script to Run ConfigMgr Task Sequence On-Demand

I’ve wrapped updated versions of the code in these posts (and some new code) into a pipeline-friendly module with a function that controls the processing options.

To get started you’ll need to us the PowerShell command [Import-Module ConfigMgr-OnDemandPatchManagement.psm1].

The function [Invoke-OnDemandPatchManagement] is the main method of interacting with the module; it has parameters and switches available to process an input list of machines (ComputerName), launch selected actions (Scan | Patch | Test) and switch options for enabling multithreading (Fast) , which uses a slightly modified version of Split-Job. There are also filters (Compliant | NonCompliant) available to further limit the processing. Additionally, the module can be used to launch any advertised task sequence by name via the (CustomTaskSequenceName) parameter.

Use the Get-Help function on the module to view options, parameters and syntax examples

Things you’ll need to configure in Configuration Manager:

Task Sequence
- Inside the ConfigMgr console you will need to create a task sequence to handle the security update patching. The task sequence names I chose in the module are “OnDemand Security Update Patching” for the patch action and “OnDemand Test Package” for the test action. The download link below contains xml exports of example task sequences that can be imported. Else you can update the package name specified in the global variables to match what you already have deployed or use the CustomTaskSequence parameter.
Advertisements
- In order for client’s to access the task sequence they will need to have an advertisement targeted at them; the least impactful way to accomplish this is to create an advertisement that has a mandatory date that client’s will never hit, like some random date in the year 2020.
Software Update Deployment Policy
- Client’s will need to be targeted with one or more software update deployment policies that contain required security updates (assume you already have this in place…).

A couple of notes about the actions performed by this module:

The module uses a slightly modified version of Get-WMICustom to interact with WMI.
The Patch action will always perform the Scan action first and only initiate the patching task sequence if a machine is found NonCompliant.
If no ComputerName value is passed in the selected action will happen on the local machine running the module.
If the ComputerName input only contains a single machine name Split-Job is skipped; likewise, multithreading is not used by default so the list of machines is processed serially. Split-Job will be used if the count of machines is greater than one but it will be limited to a single thread. The Fast switch will enable multithreading and uses ten threads by default.
By default the module will monitor the task sequence execution until it finishes, even if the machine being monitored reboots; will not work on local machine executions cause the process will be terminated on reboot (future design change). The NoWaitForFinish switch will override this behavior and the module will only wait for the task sequence to start before returning; useful to blast out task sequence executions on a list of machines as fast as possible.
If the Patch action launches the task sequence after the execution finishes another Scan action is performed and the object will return a state message of “Patched” if the machine is found compliant after patching.

OnDemand Patch Management Module v1.95 - Link

I hope you find this module useful; happy patching OnDemand!

Q&A from SCCM GURU Event - Datacenter Configuration Management

JeremyYoung — Fri, 22 Jun 2012 23:38:00 GMT

Thanks to everyone who attended the SCCM GURU event I presented, if you missed it you can catch it on the replay – link

Here are my answers to questions raised during the session.

Q: Is the patch management service on ConfigMgr for only Windows/IE patches, for all Microsoft products (e.g. Office), or for all products Microsoft and non-Microsoft?

A: The Software Update feature in ConfigMgr is capable of installing patches for any product supported by the Windows Update catalog. You can also extend the software update catalog using the System Center Custom Updates Publisher tool to deploy 3rd party update catalogs (like Adobe, Dell, etc.) or even manually defined updates.

Q: On the SUM Deployment policy, what mechanism did you use to cause the updates to download or was it only to get the advertisements?

And

Q: If you have a Software Update Deployment become mandatory before your maintenance window begins, patches will be cached outside of the maintenance window? Is that correct?

A: When a SUM Deployment policy is past deadline the ConfigMgr agent will automatically background download the applicable updates.

We want servers to background download the updates all the time so our SUM Deployment policy is always set past the deadline and an unreachable (20+ years in the future) collection level maintenance window on the ‘All Systems’ collection keeps machines from installing updates before the advertisements start the patching task sequence.

Read more details in one of my previous blog posts:

SCCM Patch Management Using The Task Sequence Engine

Q: If Bit Locker has been implemented in the environment, does this add to the complexity of rolling out patches? Can some patches corrupt Bit Locker?

A: I don’t think Bit Locker adds any complexity to patch deployments and I’m not aware of any corruption with patches.

Q: Is the only way to do multi-pass patching to use Task Sequences?

A: Outside of creating a custom script, the task sequence engine presents the easiest and most predictable way to do multi-pass patching.

The main reason we use multi-pass patching is to install patches that result from a baseline upgrade, like a service pack. For example, if a machine is running Office 2010 RTM and we authorize SP1 – the first patching pass would detect and install SP1 and the second pass would detect and install any post-SP1 security patches that now are required on the machine as a result of the service pack upgrade.

Q: Where would I find the "Patch Now" PowerShell stuff?

And

Q: Would you suggest using your "patch now" PowerShell stuff for a zero-day exploit or an out of band patch?

A: I've written two previous blog entries on the subject of initiating task sequences (which we use for patching – link) on-demand:

PowerShell Script to Run ConfigMgr Task Sequence On-Demand

PowerShell Script to Check ConfigMgr Agent Software Update Assignment Compliance

I am finishing up a blog post with an updated version of this code that is wrapped in a PowerShell module; check back with my blog soon!

As far as use in a zero-day exploit the on-demand scripts prove very useful for our server owners as they can determine their compliance or patch their machines as soon as the ConfigMgr packages are ready to go.

Q: Where is this health check script?

A: The health check script isn’t something I’ve blogged about yet; the script itself run a pretty basic set of checks against a list of machines that are not reached (no agent installed) or not healthy (hardware inventory or update scan). The real “magic” of the check script is that it logs to our SQL database allowing us to collect data on machines where we have no agent, e.g., is the machine responding to a ping request? Are there free space issues on the OS drive? Can we connect to WMI? Any errors in the ccmsetup log? And so on…

The check script is also constantly updated as we identify fixes for common issues they get integrated into the check script to either auto-fix or at the very least report on them.

I’ll consider this topic for a future blog post.

Q: Seems we overcomplicate. Why not just run on auto updates on systems?

A: In the datacenter space we need to be predictable about downtimes for servers, e.g. maintenance windows. We also need the ability to in-line software deployments alongside security updates, run multiple security update install passes, dynamic targeting, handle any reboots, pre/post patching scripts and the list goes on…

If the main goal is just patching and rebooting machines then automatic updates or Windows Server Update Services (WSUS) is a decent tool for the job. ConfigMgr is much more than just a patching tool, and while the method may seem overly complicated ConfigMgr allows us to deliver a much more robust service offering.

Q: Can you talk about the disaster recovery plans for your ConfigMgr Infrastructure?

A: Our current disaster recovery plan includes warm-standby virtual servers shadowing each of our Site Servers. The standby servers are running a blank OS install (no SQL or ConfigMgr installed) with the same drive letter/size configuration as the server they shadow; each morning they replicate a copy of the drives used for ConfigMgr backups (which contains a backup of the Site and SQL databases) as well as the live ConfigMgr folder (which contains all the inbox folders, package files, and other data).

If we were to lose a primary site server for example we could pretty quickly rename the standby server to the name of the lost primary site server, install SQL, restore the databases from backup and then install ConfigMgr and restore the Site database and be up and running again.

We have high-availability for the ConfigMgr roles like Management Points and Distribution Points and most of the other ConfigMgr roles require minimal effort to install on another server in the hierarchy in cases of disaster.

Q: How big is the team you work on that runs the ConfigMgr datacenter infrastructure?

A: My immediate team that supports a ConfigMgr for ~25-30k datacenter servers consists of three service engineers (myself included), one service manager and a small (2-3 persons per shift) tiered support team in India.

Q: What are your plans for upgrading to ConfigMgr 2012?

A: We plan to be upgraded to ConfigMgr 2012 within the next three to four months; currently we are deploying a parallel infrastructure of virtual machines running a ConfigMgr 2012 hierarchy. Once that’s complete we will finalize service continuity testing (making sure automation and reporting functions still work) and then begin migrating clients in a phased approach.

Again, thanks for attending the webcast – if you have additional questions please leave them as comments and I’ll get them answered.

Filtering ConfigMGR Software Updates IN SQL

JeremyYoung — Thu, 31 May 2012 06:54:32 GMT

The Datacenter environment that I manage with ConfigMgr for security updates consists of only Windows server platforms, running English builds, and no Itanium architecture. So when it came time to build automation for authorizing security updates I wanted to ensure that only updates applicable to my environment were approved for deployment.

Creating this filtered list in SQL turned out to be much more complicated than I had anticipated but this query was the result.

It’s expensive to authorize unnecessary updates because they have associated resource costs:

Agents use processing time on the unnecessary updates to see if they apply to them
ConfigMgr infrastructure servers waste disk space storing unnecessary update files
Replication of ConfigMgr update packages takes longer because of unnecessary files

ConfigMgr relies on the Windows Server Update Services (WSUS) catalog and agent to handle security update patching; WSUS natively allows you to filter the update catalog and remove unneeded languages and some of the client/desktop platform updates but currently there isn’t any way to filter updates by architecture, 32bit or 64bit or specifically exclude just the 64bit Itanium architecture.

I was able to overcome this hurdle by filtering in SQL (using PATINDEX()) against the title of the update or the content web URL (Itanium patches have a pattern of %ia64%). Of course there are a few update publishers who don’t have consistent publishing titles or they bundle updates for all platforms/architectures into a single update (.Net/SQL updates are the worst offenders); to solve this I have additional PATINDEX() checks to make sure the title/URL of the update being filtered doesn’t also contain the string pattern “server”.

The next problem was finding a distinct list of updates in the database; ConfigMgr normalizes the update metadata across a different tables and each security update gets broken into many rows. For example, each security update for the Windows Server platforms will show up as at least 8 unique rows:

Security Update for Windows Server 2003 x64 Edition (KBxxxxxxx)
Security Update for Windows Server 2003 for Itanium-based Systems (KBxxxxxxx)
Security Update for Windows Server 2003 (KBxxxxxxx)
Security Update for Windows Server 2008 x64 Edition (KBxxxxxxx)
Security Update for Windows Server 2008 R2 for Itanium-based Systems (KBxxxxxxx)
Security Update for Windows Server 2008 (KBxxxxxxx)
Security Update for Windows Server 2008 R2 x64 Edition (KBxxxxxxx)
Security Update for Windows Server 2008 for Itanium-based Systems (KBxxxxxxx)

Another interesting tidbit I learned about ConfigMgr software updates data storage is that there are three CI (configuration items) fields that make up a distinct software update:

CI_UniqueID (you’d think we could stop here since this field is named unique…)
- Sourced from the [v_UpdateInfo] view
CI_ID
- Also sourced from the [v_UpdateInfo] view
- Joined to the [v_UpdateContents] view to get the Content_ID
Content_ID
- Sourced from the [CI_ContentFiles] view joined using the Content_ID

The information returned from this query is key to our automated patch authorization tools because it provides ConfigMgr’s Software Update feature with all the information it needs to programmatically authorize and download the necessary files for the security updates.

Update Assignments authorize security updates using the CI_ID field

Update file download URLs come from the Content_ID field and get downloaded to folders with a name equal to the CI_UniqueID

The downloaded update file path is used as a parameter, along with the Content_ID, by the AddUpdateContent() method from the WMI object [SMS_SoftwareUpdatesPackage] when adding security updates to a deployment package

Filtering updates in SQL prior to authorization also helps cut down on the number of CIs we need to process, download and replicate around the network.

Here are the results from a recent run of the filtering query:

So, out of the 4,214 update CI rows spanning 700 security bulletins in the database we only require 731 CIs to deploy the 92 required security updates applicable to our Datacenter.

The ConfigMgr console abstracts the translation of security bulletins to their many CIs; as mentioned in a previous blog post (link) I used the SMS_Provider.log to discover the data queries being used and then replicate the console functionality in our automation tools.

Enjoy.

Download link.

PowerShell Script to Check ConfigMgr Agent Software Update Assignment Compliance

JeremyYoung — Thu, 29 Mar 2012 14:32:48 GMT

Here is a PowerShell script to check a ConfigMgr agent’s compliance for all required/assigned security updates. It is another utility tool in the kit for on-demand patch management, like this script to run a ConfigMgr Task Sequence On-Demand, which I blogged about previously. Before you launch an on-demand patching session against a machine you should make sure it actually needs some updates, likewise, after patching you want to make sure the machine is compliant; this script addresses both scenarios.

The script first checks if there is a software update assignment targeted at the machine using the WMI class CCM_AssignmentCompliance from the namespace root\ccm\SoftwareUpdates\DeploymentAgent. If an assignment is found the script queries the WMI class CCM_TargetedUpdateEX1 under the root\ccm\SoftwareUpdates\DeploymentAgent namespace, which contains the mandatory updates assigned to the machine. If any missing required updates are found they will be returned as output.

Running this script on a machine before patching you would see something similar to this:

And after patching you would expect to see:

See the script comments for the exact syntax:

Enjoy.

Download Link

Powershell Script to Run ConfigMgr Task Sequence On-Demand

JeremyYoung — Wed, 29 Feb 2012 06:42:00 GMT

I wrote a script to handle a cluster patching situation where I needed to give a server owner the ability to run the ConfigMgr monthly patch management task sequence on-demand (immediately) via PowerShell. The server owner would use another script to make their machine, in this case a cluster joined virtual server host, ready for patching (resources drained, removed from the cluster, etc.) and then initiate the patch task sequence to install any required security updates. At the end of the task sequence a post-patching script will make the server ready for production (rejoin the cluster) so they can continue onto the next host programatically, rinse, repeat, through the rest of the clustered hosts.

The script takes a parameter for the machine name (else it will default to the local machine) and also the name of the ConfigMgr task sequence to be run. The task sequence must already be advertised to the machine for this script to work because it searches the local WMI repository for the task sequence name and then works backwards to determine a policy ID to trigger. Once the policy ID is found it uses the SMS_Client::TriggerSchedule method to execute the task sequence immediately.

If you need to target a machine but only make the task sequence available on-demand you can set up an advertisement with a mandatory schedule way out in the future, basically something that will never run but the necessary policy objects still get created in the local WMI repository to run on-demand.

See the script comments for the syntax.

Hope you enjoy!

Download Link

SCCM Patch Management Using The Task Sequence Engine

JeremyYoung — Thu, 24 Nov 2011 02:04:00 GMT

As I mentioned in a previous blog post, we recently switched our monthly patch management function via SCCM away from the built-in Software Update Management (SUM) feature to the Task Sequence Engine.

One of the primary drivers behind making the technology shift was the increasing frequency at which we were deploying software (applications, non-security updates, etc.) inside the same maintenance windows used for software updates.

Without the task sequence engine there is no easy way to coordinate when the software installs and patching would happen; we had started scheduling software during the second half of the two hour maintenance windows but as the distinct software distributions started stacking up keeping up with the install schedules became increasingly difficult.

The other benefits of this change should become clear after I explain the steps/groups inside of the task sequence.

Here is a simplified example of what our monthly patch management task sequence looks like:

Inject Random Sleep: A simple random sleep timer to spread out the execution of the task sequence.

Pre-Patch Custom Actions: This step runs any custom actions to make a server ready for patching, stopping services, failing over resources, etc.. These custom scripts are provided by the machine owners and assigned to the machine via collection variables. This is one of the only steps where we do not set the “Continue on Error” option so that if a machine fails to be made ready for patching we have an opportunity to stop processing further steps.

Install Required Software Updates: The heart of the task sequence; this step installs all of the mandatory security updates targeted at a machine. Prior to patching the machine will also be rebooted if any pending reboot operations are detected. Following the patch installs we run our Update Install Source script to gather data about “who” installed the security updates (see this previous blog post for more information).

Install Required Non-Security Updates: This step allows us to install any QFE’s/Hotfixes that are required. Since we would not want to reboot after each update we declare a variable to detect if a reboot was required.

Install Required Software Distributions: Install or modify any required software distributions that are relevant to the machine.

Agent Maintenance: This step allows us to perform upgrades or maintenance on our core agents, SCCM, WindowsUpdate, OpsMgr, FEP.

Post-Patch Custom Actions: The clean-up phase of the task sequence.; this step runs any server owner provided custom action scripts to make a server ready for production again post patching, starting services, reclaiming resources, monthly maintenance steps (log archiving for example). We also handle any pending reboot operations that may have been caused by agent maintenance, software distributions, or non-security updates installed during the task sequence execution.

Switching to patching via the task sequence is not without its challenges; it requires some major changes in the way that you target machines, configure update policies and maintenance windows.

Here are some key lessons learned from our implementation (so far):

SCCM Advertisements:

If you use the SCCM native SUM feature you can get by on collection level maintenance windows alone to control when updates are installed. I found out in testing that you can’t control the execution order of generic task sequences and SUM deployments; only OSD task sequence types get a special maintenance window override flag. When we tried to use collection maintenance windows to advertise the task sequence the SUM feature would start separately at the same time and then reboot the machine causing the task sequence to fail. The SCCM advertisements will deliver the task sequence and also provide the bounds for the the maintenance window, using a combination of Mandatory Time and Expire Time, instead of the collection level maintenance windows.

Past Deadline SUM Deployment Policy

We need agents to install, not just be offered, any mandatory updates during the maintenance window. Also, machines will only pre-download the required update files before the maintenance window if the policy is past deadline. The policy is configured to only run during maintenance windows and prevent system reboots outside of maintenance windows.

Prevention Maintenance Window

A “dummy” maintenance window for all agents set to some date in the future which will prevent machines from running the past deadline SUM Deployment Policy outside of their actual maintenance window. Caution: this maintenance window will prevent all machines from running any software distribution, unless override maintenance windows option is selected on the advertisement. *Important Safety Tip* set this prevention date far enough in the future that you will not likely ever reach it; or at least until you expect to leave your role and it’s someone else’s problem ;)

Automation

We manage a large and dynamic population of servers and we offer choices of ~42 different maintenance windows (6 per day x 7 days). Servers can move maintenance windows at will and some machines receive patching extensions that have to be kept out of all targeted collections; maintaining these manually is not an option. I wrote some PowerShell tools that handle the translation of selected maintenance windows in a CMDB –> SCCM collections/advertisements; maybe a future blog topic.

Variables (Collection/Task Sequence) for Orchestration Control

Use collection variables and task sequence variables as program flow gateways for steps/groups inside the task sequence. Variables offer a simple way to assign programs to machines or even multiple programs using base variables. Variables can also collect execution return codes to postpone reboots or to send machines down a different set of steps/groups.

Looking for a good way to manage collection variables? Refer to my PowerShell library blog post about it.

Program Exit Code / Reboot Control

There aren’t many options available for controlling the task sequence reboot behavior at each step; any program that returns the universal 3010 code will cause the Task Sequence Engine to reboot the machine immediately after the step completes. I learned this lesson the hard way when we lined up five QFE/Hotfix installs and machines rebooted after each one.

It’s best to use a simple wrapper script to call the software installer and then manage the return code to mark a variable for reboot required and then pass back a 0 (success) return code. Also beware of native program return codes that map to irrelevant SCCM status messages – after a bit of pointless troubleshooting I learned that a QFE returned a WSUS exit code that equated to “Update Not Required” but the error message in ExecMgr.log said something like “File Not Found”.

Status Message Increase

Prepare for an increase in status messages during patching windows, especially as you add steps/groups into the task sequence. Agents will send status messages (sometimes multiple) for each step/group they process. While this yields a new level of detail for your reporting it also increases the load on the infrastructure/network during patching windows.

Hopefully I’ve sparked your interest in switching to the Task Sequence Engine for patch management; while it definitely adds an extra layer (or two!) of complication the benefits gained in orchestration, reporting and general extensibility (to name a few) make it worth the investment...

Update Install Source

JeremyYoung — Fri, 30 Sep 2011 19:57:00 GMT

Inside the Microsoft IT Datacenter environment SCCM is the main tool used for automated patch management. Some servers are still manually patched by their server owners using many different tools (scripts, WSUS, AutomaticUpdates, download/install the exe’s manually, etc.). Since our primary focus is on the security of the environment it doesn’t really matter how servers get secured each month for the required updates.

However, in order to measure the usage of our automated patching service we wanted to see how many of these servers are actually getting updates installed by SCCM versus those being manually patched.

You would think this data would exist in the SCCM database…

SCCM does have a column that looks like it contains the install source information; the column is EnforcementSource in the view v_UpdateComplianceStatus (and a few other views too).

The column is an integer type and the WMI class definition shows the possible values as follows:

SMS_CI_CurrentComplianceStatus Server WMI Class

After comparing the data for machines I manually updated and machines where I let SCCM install the updates I found that this view did not provide the results I was expecting; both sets of machines showed EnforcementSource = 2 == USER. After more research, testing and chats with colleagues, it appears these values are tied to the mandatory enforcement messages and not necessarily the mechanism that installed the update; else it’s bad data being returned to SCCM…

I talked with another Microsoft colleague, Satish Petwe, who works on the SCCM team managing a large population of desktop clients, and he had found that update install source data does exist on the machine in the local Windows Update agent repository.

Executing the QueryHistory method inside the Microsoft.Update.Searcher object will return a list of updates applicable to the machine. The list can be filtered to show only the installed updates and most importantly the result object contains a property called ClientApplicationID which stores the application that processed the update.

As you can see in the output generated by the vbscript (code at the bottom of the page) this is exactly the information I was looking for:

You could also use this data to see the different non-SCCM applications that are being used to install updates in your environment, but as I stated earlier true install counts by SCCM was our main goal.

There are a number of options on how to get this data back to a central location; I decided on funneling the relevant information into a local WMI class and then extending SCCM inventory to bring it back to the central site database. I haven’t done further investigation to see if this information is available in WSUS natively; I assume we would have to enable WSUS reporting events on our Software Update Points.

Here’s some quick vbscript code just to give you an idea how to find the data.

Set loc = CreateObject("WbemScripting.SWbemLocator")

Set WbemServices = loc.ConnectServer(, "root\cimv2")

On Error Resume Next

Set oWU = CreateObject("Microsoft.Update.Searcher")

iTHCount = oWU.GetTotalHistoryCount

'make sure there is something returned from the WU searcher

If iTHCount > 0 Then

   'query the WU history to get all the updates on this machine

   Set colUpdate = oWU.QueryHistory(0, iTHCount)

   For Each oUpdate In colUpdate

    'only look for successfully installed updates ResultCode=2

   If  oUpdate.ResultCode = 2 then

     UpdateID =oUpdate.UpdateIdentity.UpdateID

     Title=oUpdate.Title

     DateGMT=oUpdate.Date

     InstallAgent=oUpdate.ClientApplicationID

    wscript.echo Title

    wscript.echo "     UpdateGUID: [" & UpdateID & "]"

    wscript.echo "     Installed by: " & InstallAgent

    wscript.echo "     Installed on: [" & DateGMT & "]"

End if

    Next

End If

Finding the Patch Cycle Location Using SQL

JeremyYoung — Tue, 23 Aug 2011 23:11:00 GMT

Inside Microsoft IT the monthly security patch cycles for our Datacenter servers are static; over the years the deadline lengths have changed and some phases have shifted around but it always remains highly predictable.

At a high level each patch cycles consists of the following phases:

Patch Tuesday Release :: Start of a new patching cycle.

Patch Deployment Preparation :: Authorizing updates, downloading and sending bits to SCCM distribution points, WSUS, preparing collections for deployments, etc.

Pre-Production Patching :: Smoke test of the patch content and deployment setup to pre-production machines.

Production Patching :: Main patching deployment to all production machines.

Have you ever looked at a compliance report and wondered which part of the security patch cycle the results belonged to? Or thought to yourself, “is this a patching weekend?” Given that each patch cycle is predictable I wrote a bit of SQL code to take a date, apply the patch cycle business logic and return the patch cycle location.

The first step is to calculate the first day of the month of the date provided; in this case @Date is set to the current date.

declare @Date datetime = getdate()

declare @FirstDayofCurrentMonth date = DATEADD(dd,-(DAY(@Date)-1),@Date)

Next comes the most important bit of code which will find the date for Patch Tuesday. Patch Tuesday occurs on the 2nd Tuesday of each month. Initially I was taking the long route to calculate this date, but luckily I stumbled upon this article which showed a much cleaner formula for finding the second Tuesday of the month (read the article for the details):

set @PatchTues = DATEADD(day,((10 -

        DATEPART(dw,@FirstDayofCurrentMonth)) % 7)

        + 7, @FirstDayofCurrentMonth)

Patch cycles span from Patch Tuesday to the next Patch Tuesday, so after a date for Patch Tuesday is found, an additional check must be done to see if the @Date variable is less than the calculated Patch Tuesday date; for example, if @Date equals the first day of a month you are still within the previous patch cycle; in this case recalculate Patch Tuesday based on the first day of the previous month:

if(@Date < @PatchTues)

begin

    -- get the first day of last month

    declare @FirstDayofLastMonth date = DATEADD(dd,

    -(DAY(DATEADD(mm,1,@Date))-1),DATEADD(mm,-1,@Date))

    -- calculate previous patch tues from first day last month

    set @PatchTues = DATEADD(day,((10 -

    DATEPART(dw,@FirstDayofLastMonth)) % 7)

     + 7, @FirstDayofLastMonth)

end

Now that the correct Patch Tuesday date has been found the rest of the code calculates the number of days from Patch Tuesday and applies the logic to determine the phase of the patch cycle:

-- find out how many days from current patch tues

declare @DaysFromCurrentPatchTues int = DATEDIFF(day,@PatchTues,@Date)

And then use a case statement to assign the Patch Cycle location to a variable; our current patching cycle is 19 days long:

set @idPatchCycleLocation =

case

    -- patch tuesday

    when @DaysFromCurrentPatchTues = 0 then 'Patch Tuesday'

    -- days 1 and 2 are for patch prep

    when @DaysFromCurrentPatchTues between 1 and 2 then 'DeploymentPrep'

    -- days 3-5 are for smoke test patching (dev/test systems)

    when @DaysFromCurrentPatchTues between 3 and 5 then 'PreProductionPatching'

    -- days 6 thru 12 are maintenance window patching

    when @DaysFromCurrentPatchTues between 6 and 19 then 'ProductionPatching'

    -- anything over 19 is inbetween cycles

    when @DaysFromCurrentPatchTues > 19 then 'InBetweenCycles'

end

The last step is to return the Patch Cycle location with key data about the patch month/cycle:

-- return the patch cycle location

select

    -- use the calculated patch tuesday's month/year

    DATENAME(month,@PatchTues) as PatchMonth

    ,YEAR(@PatchTues) as PatchYear

    ,Convert(varchar(25),DATENAME(month,@PatchTues))

        + ' ' + Convert(varchar(25),YEAR(@PatchTues)) as PatchCycle

    ,@idPatchCycleLocation as PatchCycleLocation

The output will look like this:

PatchMonth	PatchYear	PatchCycle	PatchCycleLocation
August	2011	August 2011	ProductionPatching

In our production environment, this code has been wrapped into a SQL user-defined function with a parameter for the date so that it can be easily joined it to other SQL data.

Using SQL to find the Patch Cycle location adds extra value to reports, especially patch compliance reports where data can now be broken down by patch cycle phases. I also built an SRS report object that uses this function to build a dynamic monthly patching calendar to share with our server owners.

In addition to reporting integration this function is also used to drive automation into the monthly SCCM patch management administration tasks. Our patching process allows server owners to select a maintenance window for SCCM to patch their servers; the maintenance window choices are generic, i.e. “Saturday 12:00 AM – 2:00 AM” but as you saw from our business logic there are only specific weeks of the month where patching might be active. Using this patch cycle location code makes it possible to map a generic date to an actual date during the right patch cycle phase and drive the automation scripts to place the servers into the right collections for SCCM patching.

Here is the full SQL code; I hope you enjoy it!

Please share any other uses you find for this code!

Thanks for reading my first blog post.

declare @Date datetime = getdate()declare @PatchTues datedeclare @idPatchCycleLocation varchar(25)     -- using the date provided calculate the first day of the monthdeclare @FirstDayofCurrentMonth date = DATEADD(dd,-(DAY(@Date)-1),@Date) /*    patch tuesday is 2nd tues of each month; use the first day of the month to calculate it        based on formula found at:    http://www.devarticles.com/c/a/SQL/Generating-Reports-with-SQL-Date-Handling/3/    */set @PatchTues = DATEADD(day,((10 - DATEPART(dw,@FirstDayofCurrentMonth)) % 7) + 7, @FirstDayofCurrentMonth) /*    check if the current date is less patch tues date     if so recalculate based on last month, i.e. the month rolled over*/if(@Date < @PatchTues)begin        -- get the first day of last month    declare @FirstDayofLastMonth date = DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))-1),DATEADD(mm,-1,@Date))            -- calculate previous patch tues from first day last month    set @PatchTues = DATEADD(day,((10 - DATEPART(dw,@FirstDayofLastMonth)) % 7) + 7, @FirstDayofLastMonth)           end -- find out how many days from current patch tuesdeclare @DaysFromCurrentPatchTues int = DATEDIFF(day,@PatchTues,@Date) set @idPatchCycleLocation = case    -- patch tuesday    when @DaysFromCurrentPatchTues = 0 then 'Patch Tuesday'    -- days 1 and 2 are for patch prep    when @DaysFromCurrentPatchTues between 1 and 2 then 'DeploymentPrep'    -- days 3-5 are for smoke test patching (dev/test systems)                when @DaysFromCurrentPatchTues between 3 and 5 then 'PreProductionPatching'    -- days 6 thru 12 are maintenance window patching    when @DaysFromCurrentPatchTues between 6 and 19 then 'ProductionPatching'    -- anything over 19 is inbetween cycles    when @DaysFromCurrentPatchTues > 19 then 'InBetweenCycles'end     -- return the patch cycle locationselect     -- use the calculated patch tuesday's month/year    DATENAME(month,@PatchTues) as PatchMonth      ,YEAR(@PatchTues) as PatchYear    ,Convert(varchar(25),DATENAME(month,@PatchTues))         + ' ' + Convert(varchar(25),YEAR(@PatchTues)) as PatchCycle    ,@idPatchCycleLocation as PatchCycleLocation