[END OF "SUPPORT" NOTICE]
Hello everyone, after close to 5 years maintaining various tools around Symantec Connect this legacy is turning to be more of a burden than anything else.
It's still is a great set of tool and they all have their use, but as such I'm not going to maintain them anymore.
The source code for this tool may still change over time, and can be found on Github: https://github.com/somewhatsomewhere?tab=repositories
[/END OF "SUPPORT" NOTICE]
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).