Video Screencast Help

{CWoC} Patch Trending: Adding Patch Compliance Trending Capacity to SMP is as Simple as Running a Report Daily

Created: 14 Aug 2013 • Updated: 25 Sep 2013 | 15 comments
Language Translations
Ludovic Ferre's picture
+2 2 Votes
Login to vote

Update: A site builder is now available to generate a web-site where you can see global compliance graphs and compliance by bulletin and updates as well! You can check it out here: Connect downloads: {CWoC} Patch Trending Sitebuilder.

Update 2: You can also add Compliance by Computer trending (optional). This will allow you to see how the estate is doing with compliance and how changes in compliance are trending (up or down). The report SQL is here: Adding Compliance by Computer Trending to Your SMP.

Patch Management Solution is a great product that comes with a large number of reports, however it is missing what I would consider a key feature: trending. The ability to keep track of compliance over time.

Now from a Product Management stand-point I can understand why it would be very difficult to put in place (too many options - diverging needs etc). But from an "outsider" it's very easy to put in place.

Today we will look at the first enabler to such feature: a patch trending report.

Patch trending report sources:

-- #########################################################################################################
-- PART I: Make sure underlying infrastructure exists and is ready to use
if (exists(select 1 from sys.objects where name = 'PM_TRENDS_TEMP' and type = 'U'))
begin
	truncate table PM_TRENDS_TEMP
end
else
begin
CREATE TABLE [dbo].[PM_TRENDS_TEMP](
	[_SWUGuid] [uniqueidentifier] NOT NULL,
	[Bulletin] [varchar](250) NOT NULL,
	[Update] [varchar](250) NOT NULL,
	[Severity] [varchar](250) NOT NULL,
	[Custom Severity] [nvarchar](100) NULL,
	[Release Date] [datetime] NOT NULL,
	[Compliance] [numeric](6, 2) NULL,
	[Applicable (Count)] [int] NULL,
	[Installed (Count)] [int] NULL,
	[Not Installed (Count)] [int] NULL,
	[_SWBGuid] [uniqueidentifier] NOT NULL,
	[_ScopeCollection] [uniqueidentifier] NULL,
	[_Collection] [uniqueidentifier] NULL,
	[_StartDate] [datetime] NULL,
	[_EndDate] [datetime] NULL,
	[_DistributionStatus] [nvarchar](16) NULL,
	[_OperatingSystem] [nvarchar](128) NULL,
	[_VendorGuid] [uniqueidentifier] NULL,
	[_CategoryGuid] [uniqueidentifier] NULL
) ON [PRIMARY]
end

if (not exists(select 1 from sys.objects where type = 'U' and name = 'TREND_WindowsCompliance_ByUpdate'))
begin
	CREATE TABLE [dbo].[TREND_WindowsCompliance_ByUpdate](
		[_Exec_id] [int] NOT NULL,
		[_Exec_time] [datetime] NOT NULL,
		[Bulletin] [varchar](250) NOT NULL,
		[UPDATE] [varchar](250) NOT NULL,
		[Severity] [varchar](250) NOT NULL,
		[Installed] [int] NULL,
		[Applicable] [int] NULL,
		[DistributionStatus] [nvarchar](16) NULL
	) ON [PRIMARY]

	CREATE UNIQUE CLUSTERED INDEX [IX_TREND_WindowsCompliance_ByUpdate] ON [dbo].[TREND_WindowsCompliance_ByUpdate] 
	(
		[Bulletin] ASC,
		[Update] ASC,
		[_exec_id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = 
OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [IX_TREND_WindowsCompliance_ByUpdate_OrderbyUpdate] ON [dbo].[TREND_WindowsCompliance_ByUpdate] 
	(
		[UPDATE] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

end

-- PART II: Get data into the trending table if no data was captured in the last 24 hours
if (select MAX(_exec_time) from TREND_WindowsCompliance_ByUpdate) <  dateadd(hour, -23, getdate()) or (select COUNT(*) from TREND_WindowsCompliance_ByUpdate) = 0
begin

-- Get the compliance by update to a "temp" table
insert into PM_TRENDS_TEMP
  exec spPMWindows_ComplianceByUpdate
			@OperatingSystem = '%',
			@DistributionStatus = 'Active',
			@FilterCollection = '01024956-1000-4cdb-b452-7db0cff541b6',
			@StartDate = '1900-06-29T00:00:00',
			@EndDate = '2020-06-29T00:00:00',
			@pCulture = 'en-GB',
			@ScopeCollectionGuid = '91c68fcb-1822-e793-b59c-2684e99a64cd',
			@TrusteeScope = '{2e1f478a-4986-4223-9d1e-b5920a63ab41}',
			@VendorGuid = '00000000-0000-0000-0000-000000000000',
			@CategoryGuid = '00000000-0000-0000-0000-000000000000',
			@DisplayMode = 'all' 

declare @id as int
	set @id = (select MAX(_exec_id) from TREND_WindowsCompliance_ByUpdate)
		insert into TREND_WindowsCompliance_ByUpdate
		select (ISNULL(@id + 1, 1)), GETDATE() as '_Exec_time', Bulletin, [UPDATE], Severity, [Installed (Count)] as 'Installed', [Applicable (Count)] as 'Applicable', _DistributionStatus as 'DistributionStatus'
		  from PM_TRENDS_TEMP
end

-- Return the latest results
select *, applicable - installed as 'Vulnerable',  cast(cast(installed as float) / cast(applicable as float) * 100 as money) as 'Compliance %'
  from TREND_WindowsCompliance_ByUpdate
 where _exec_id = (select MAX(_exec_id) from TREND_WindowsCompliance_ByUpdate)
--   and cast(cast(installed as float) / cast(applicable as float) * 100 as money) < %ComplianceThreshold%
--   and applicable > %ApplicableThreshold%

union

select max(_exec_id), max(_exec_time), Bulletin, '-- ALL --' as [update], '' as severity, sum(installed) as 'Installed', sum(applicable) as 'Applicable', '' as DistributionStatus,  sum(applicable) - sum(installed) as 'Vulnerable',  cast(cast(sum(installed) as float) / cast(sum(applicable) as float) * 100 as money) as 'Compliance %'
  from TREND_WindowsCompliance_ByUpdate
 where _exec_id = (select MAX(_exec_id) from TREND_WindowsCompliance_ByUpdate)
 group by Bulletin
--having sum(applicable) >%ApplicableThreshold%
--   and cast(cast(sum(installed) as float) / cast(sum(applicable) as float) * 100 as money) < %ComplianceThreshold%
 order by Bulletin,[update]

A Quick look at the report proceedings:

The report takes care of a temp table used to store the compliance results, of the Patch trending table (to create it on the first execution with all the required indexes to keep it fast under load) and of course of inserting the results from the temp table to the trending table (only if the last insert was done more than 23 hours prior).

Finally we return the latest result set, whether it was just inserted or already cached.

I will not detail the above SQL but I need to explain the decisions taken in the compliance report execution, shown here:

  exec spPMWindows_ComplianceByUpdate
			@OperatingSystem = '%',
			@DistributionStatus = 'Active',
			@FilterCollection = '01024956-1000-4cdb-b452-7db0cff541b6',
			@StartDate = '1900-06-29T00:00:00',
			@EndDate = '2020-06-29T00:00:00',
			@pCulture = 'en-GB',
			@ScopeCollectionGuid = '91c68fcb-1822-e793-b59c-2684e99a64cd',
			@TrusteeScope = '{2e1f478a-4986-4223-9d1e-b5920a63ab41}',
			@VendorGuid = '00000000-0000-0000-0000-000000000000',
			@CategoryGuid = '00000000-0000-0000-0000-000000000000',
			@DisplayMode = 'all' 

As you can see we are not writing our own compliance report, but rather we leverage the built-in procedure that return the compliance by update. The parameters are hard set to ensure we do not have any limits (collection = all computer, trustee scope = symantec admin) but we limit the result set to only show Active updates.

One important note on what "Active" means in this case. An active update is an update that is enabled and ready for distribution. So you can have a mix of updates with enabled policy, disabled policies or not policies returned in this report.

I have some report that picks up data from the trending table and filters out updates that do not have an enabled policy - but this will be another article subject.

A quick Howto implement

Implementing this feature, as promised in the article title is simple: create a report and set it to the SQL type. Paste the full code above and save the report. Once it has run you should be able to see the current compliance status for your environment.

Next you need to create an Automation Policy that will run the report daily.

Voila.

Next in the serie

We will look at some reports to get some graphical views of the patch trending, as well as report on the meta-data and global compliance. There'll also be some report for the compliance by enabled update, as mentioned above.

And as a final teaser, I will boldly state here that I am considering creating a small web-application to provide a nice graphical view of the patch compliance trending using the gorgeous Google graphic API's (very much like what we have in aila-web).

Comments 15 CommentsJump to latest comment

Ludovic Ferre's picture

I have just added an index to this report to improve performance, here is the code:

GO
CREATE NONCLUSTERED INDEX [IX_TREND_WindowsCompliance_ByUpdate_OrderbyUpdate] ON [dbo].[TREND_WindowsCompliance_ByUpdate] 
(
	[UPDATE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

This is keeping a list of items order by update name, which is usefull when one wants to massively hit the table for status by update.

More on this subject later :D.

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Ludovic Ferre's picture

For anyone interested, you can now build a site to display pretty charts from the Patch trending data: Connect downloads: {CWoC} Patch Trending Sitebuilder

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
natemg's picture

Thanks a bunch, I just got this working.  I really like that you can visually see what's going on, ensure patches are still being rolled out vs. running reports to get numbers and manually graphing out in Excel.  

+1
Login to vote
Papa-Gio's picture

Backin November of 2013, I installed the Patch Trending utility on our Client Facing NS and everythign worked great!  WE got just what we wanted and expected over time.  However, last week, we noticed that the compliance numbers stopped updating on April 25, 2015.  So now I'm being asked to prepar the typical report (including hte compliance graphs and daily summary), but cannot since the numbers stopped updating.

The initial Start date was November 13, 2013 and ;ast updated date is April 25, 2015 -- exactly 528 days difference between the two dates (per Excel). 

What I am wondering is, is there a way to change the "starting date" so I can use a 1 year range of compliance numbers?  Surely the application works up to 528 days (data points), but If i can just hav ea rolling 1 year chart that would be awesome. 

I don't see anywehre in the code where there is a defined start date, but everytime proceses run, the data is reset to that first initial date (11/13/2013).

We have come to love the applicaiton, however this dead-stop in calculation is making the tracking a bit more difficult.

Anyone have experience with Google Analytics to change the way the charts are plotted out?  Can I minimise the Horizontal axis with a start and end data point and have all the other date adjust?

Looking for ideas....

Capture3.JPG

John A.

0
Login to vote
Ludovic Ferre's picture

Hello Papa-gio,

I'm happy to say that you qualify as an early adopter of the patch trending tool, and I'm also happy that it worked without problems to this day.

Now, there's no limit on how much data can be gathered, and my first customer of this tool ran the trending SQL from July 2013 (on 3 different client facing servers) and it's still working fine to this day (in fact I monitor the system daily).

So, there must be something that's gone off in your environement recently. Can you tell me which version of SMP you are running? Did you install a hotfix recently or nothing at all?

The Patch Trending SQL should be running daily, so first you should verify that the data collection takes place normally. You could for example run the stored procedure in SQL Management Studio to ensure it runs without fail and in a timely manner.

If you need a hand to look at this send me an email and I should be able to help you with Webex later on today (we can start up iuntil 1700 CEST).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Ludovic Ferre's picture

PS: You could prune the trending table to only keep up to 365 days of trending, but I think it's not really worth it.

Having more data means for example that you can show the inflation in vulnerability for the last 2 years (my customer for example went from 3.44 million vulnerabilities reported to 5.36 million - and we're close to our all time high compliance level - which speaks a lot to management).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Papa-Gio's picture

Thank you for your reply.  We are currently at SMP 7.1 SP2 MP1, rollup 9.  We have made no changes to the environment since rollup 9 was installed. 

It appears that we have also had some strange occurrneces with the total environment as well.  Our AppID account seems to be locking up.

I've checked the logs from the SMP side as well as the SQL datbase server side and see no messages that signify incorrect operation.  In the Task Schedule Library, I can see that all four Patch Trending tasks are inded running successfully each day.

John A.

0
Login to vote
Ludovic Ferre's picture

Can you run the following query to check that the data collection works fine. Because if it does, you only have an issue with the SiteBuilder:

select [_Exec_id], [_Exec_time], count(distinct([Bulletin])) as 'Bulletin #', count([update]) as 'Update #'
  from [TREND_WindowsCompliance_ByUpdate
 group by [_Exec_id], [_Exec_time]
 order by [_Exec_id] desc, [_Exec_time]

I asked about the update changes ebcause the SiteBuilder needs to run with an Altiris Admin account, and as you pointed out you have authenticat5ion issue.

So if the query above comes clean (i.e. you have recent data) then you should just run the SiteBuilder.exe (in your patch trending folder) manually to make sure all is up-to-date.

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Papa-Gio's picture

Well, once I put a close square bracket after the TREND_WindowsCompliance_ByUpdate line, it worked fine and returned 516 rows of data.-- the most current date in the list is: 2015-04-25 22:28:41.833.  That is the last date the system worked correctly.  Something is stopping the process from running beyond the 516 days.....

By the way, we have solved our authentication issues by resetting the password toe the AppID account and resetting all the parameters using "AexConfig.exe /svcid ......

John A.

0
Login to vote
Papa-Gio's picture

By the way, I am running sitebuilder.exe V15, which I beleive is the latest one.

John A.

0
Login to vote
Ludovic Ferre's picture

Okay, so time to go back to SQL.

Can you simply run the SP from SQL Management studio?

This should collect data immediately.

Then you'll have to go back to your schedule task (should be a SQL Task on the SMP) and find out why it is not running, or running with an error.

PS: I'm currently working on version 16 - which is good for a beta really - that will allow users to collect data for multiple collections, to generate multiple sites (one per collection guid) and to collect the data (run the stopred procedures) as well.

This should allow you to have everything under one task, which make it much easier to control and troubleshoot.

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Papa-Gio's picture

I will anxiously be awaiting version 16 as it sounds like a solution to our issue. 

I get the same results regardless of where I run the query.  Highest number is 516, and the most recent date/time is 2015-04-25 22:28:41.833

I am having no luck getting past the last run date of April 25th..... Hoping Version 16 will be the answer for us.

John A.

0
Login to vote
Ludovic Ferre's picture

To keep on the teasing, I have version 17 running for a couple of customer now.

Version 17 adds a drop-down on the default page which allows you to select the update sevrity for the compliance charts. It shows some very interesting patterns and allows a quick check of the status for the most important updates versus lesser important ones.

PS: I have not published the version 16 or 17 yet because it requires an overall of the SQL infrastructure (the sp and tables) and it's quite tricky to migrate the data.

I have an automated upgrade working in a test environment however it's never made the cut in production environment (the procedure requires to rename the existing table, install the new stored proecure, create updated tables and then restore the data with the added collectionguid back into the standard table).

Anyhow, I do intend to push all of the work out here on Connect with documentation, but it requires time I don't have right now. Hopefully it should make it here on Connect before end of June!

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote
Papa-Gio's picture

I think I have found the culprit to our processing failures.  It appears that there are two Patch Bulletins with the same name and update file, released on April 21, 2015 from Adobe.  There is an Adobe Shockwave 12.1.8.158 update with bulletin number SW12-1218158.  However, somehow this bulletin was imported twice in our environment.  both are identical updates, however the applicable counts differe greatly.  While one bulletin applies to 8044 targets, the duplicte bulletin only applies to 2 targets.

I want to delete the second version of that bulletin but am having trouble trying to do so.  I've watched the Site builder program execute and turned on the verbose output and found the diagnostic message pointing to the Bulletin SW12-18158, sw_lic_fill_installer_1219158.exe,517).  This corresponds to the fact tho tthe SQL results from the trouble shooting query abot only returns 516 rows -- due to the error on row 517.

SiteBuilder would be better if a diagnostic was thrown for duplicate entries and continued to process rahter than just aborting -- just a thought.

I've inclueded a screenshot of my diagnostic message that led to the discovery below.

patch trend.JPG

John A.

0
Login to vote
Ludovic Ferre's picture

Hi John,

Thanks for the feedback. It looks like a nasty problem, but I think we can solve it at the SQL level by modifying the index. Currently it is based on the Bulletin + Update columns.

You should be okay with this duplicate issue if you had a column that help differentiate the updates from one another. And Severity is the only option.

That will allow the SQL trending to work again - but the data for this update will mix both results or could cause more issue in the SiteBuilder...

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

0
Login to vote