Video Screencast Help
Symantec Secure Login will be live on Connect starting February 25. Get the details here.

{CWoC} Patch Trending Stored Procedures

Created: 17 Sep 2013 • Updated: 03 Oct 2013 | 2 comments
Ludovic Ferre's picture
+2 2 Votes
Login to vote



The ability to track information over time, aka trending, is not yet a big part of the SMP and solution, and for many good reasons (just consider the count of data points you may want to trend - add the possibility to filter by OU or collections and multiple this by 365 to store data just for a year and you have in your hand something that could be considered as data explosion).

However it is a key part of monitoring and tracking Patch Management compliance over time.

To this avail I have already published a set of articles [1][2][3], and another download [4] to create reports that basically run the trending logic and an executable that generates a semi-static web-site (i.e. all content is generated onto static files on the server, but we still use Javascript to load data dynamically on the client browser).

Today I am grouping the report SQL into self-contained stored procedures, so your reports won't need to be so long and will be much easier to understand. I have also added common feature that make them easier to use for our specific trending needs.

File list:

  • spTrendInactiveComputers.txt
  • spTrendPatchComplianceByComputer.txt
  • spTrendPatchComplianceByUpdate.txt


  • @CollectionGuid = 'uniqueidentifier'
  • @Force = 0 | 1

Back to top

Custom collection guid (@CollectionGuid):

This parameter allows you to specify which CollectionGuid will be used when executing the Patch Management stored procedures. 

If nothing is specified we will use the default '01024956-1000-4cdb-b452-7db0cff541b6', which is the 'Windows Computers with Software Update Plug-in Installed'.

This is useful if the monitored scope is different from the default collection, for example if your Server patching is done differently and you want to cater only for workstations in your patch trending, or if you have a custom filter used in the Software Update policies.


exec spTrendPatchComplianceByComputer @collectionguid = '29bafad0-1a60-4796-b260-4e6f3633afae'

exec spTrendPatchComplianceByUpdate @collectionguid = '29bafad0-1a60-4796-b260-4e6f3633afae'

Caution! If you are using a custom collection guid make sure you do not call the procedure without the parameter - as this would change the base on which the trending is done. Actually, if you are in this situation, the best is to change the default value to match your filter guid. This will avoid any potential mishaps if you need to run the trending procedure out of the normal operation.

Back to top

Force recording (@Force)

By default each of the procedure will record a new dataset if the last dataset was records more 23 hours prior. This is to make sure we capture the data once per day (and accommodates for tiny variations in the report run time that could cause recording not to take place if we had selected 24 hours).

However sometime you will need the report to run within the 23 hours period after the last execution. For example if for x reasons a nightly execution failed to run on schedule, and you ran the report manually in the morning.

Without the ability to force the recording to take place the coming night you could be missing a daily snapshot, which over months of data recording is not a huge deal, however when running a report for the past week for example having the latest data can be quite important.


exec spTrendPatchComplianceByUpdate @force = 1

exec spTrendInactiveComputers @force = 1

Back to top

Features Availability:

Not all features are available on all procedure, so here's a table listing capabilities:

Procedure @CollectionGuid @Force
spTrendInactiveComputers No Yes
spTrendPatchComplianceByComputer Yes Yes
spTrendPatchComplianceByUpdate Yes Yes

So you could run the following:

exec spTrendPatchComplianceByComputer @collectionguid = '29bafad0-1a60-4796-b260-4e6f3633afae', @force = 1

exec spTrendPatchComplianceByUpdate @collectionguid = '29bafad0-1a60-4796-b260-4e6f3633afae', @force = 1

exec spTrendInactiveComputers @force = 1

But not:

exec spTrendInactiveComputers @force = 1, @collectionguid = '29bafad0-1a60-4796-b260-4e6f3633afae'

as it would cause an SQL error.

Back to top


[1] Adding Patch Compliance Trending Capacity to SMP is as Simple as Running a Report Daily :D 
[2] Adding Compliance by Computer Trending to Your SMP 
[3] {CWoC} Patch trending: Inactive computer trending report
[4] {CWoc} Patch Trending SiteBuilder

Back to top

Comments 2 CommentsJump to latest comment

cparkITSPartners's picture

Hi Ludovic,

I keep getting an error on @force = 1

but no error on @force=1

but I don't think that's working...


Login to vote
Ludovic Ferre's picture

So you think it's not creating a new set when the @force variable is set to 1?

I can see it working well on my 7.5 server (the sp should be the same - or I may be at a different version).

Run it in SQL and you should see the _Exec_ID increment in between each execution.

Ludovic FERRÉ
Principal Remote Product Specialist

Login to vote