Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrades.
Please accept our apologies in advance for any inconvenience this might cause.

{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 | 3 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 3 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.

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

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

 

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

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