I've recently been working on some Window update compliance reports which dynamically retrieve compliance data for the latest SUG (Software Update Group) created on Patch Tuesday. This makes it easy for administrators and management to keep track of compliance without the need to find and select the latest SUG.
The SUG is deployed to two collections on the evening of patch Tuesday, one containing servers and the other workstations. We have a multi-tenant environment so the report splits these systems up by customer.
The compliance data is presented using pie charts on an SSRS report. I'll update this post at a later date with how I formatted these, for now I'll just provide the SQL we use to report on our server estate.
Prerequisites:
- Make sure your ADR is configured to create a new SUG each time it's triggered.
- Our ADR triggers on patch Tuesday (2nd Tuesday of the month).
- Create a separate database (Mine is called 'CM_Runtime') to store SQL functions. I wasn't too keen on defining functions on the CM database.
Create Functions:
To scope the report to a SUG created on the most recent patch Tuesday, we need to save some stored procedures ('PCC_SEC_TUESDAY' and 'PCC_LAST_SEC_TUESDAY') in a runtime database which calculate the most recent patch Tuesday date based on the date the report is run using the following query: SELECT [dbo].[PCC_LAST_SEC_TUESDAY](getdate())
Run the SQL statements below on the database created in prerequisite steps.
PCC_SEC_TUESDAY returns the 2nd Tuesday of the month that any date is in (@date would be the parameter.
================================================
CREATE FUNCTION PCC_SEC_TUESDAY
(
@p_date datetime
)
RETURNS DATETIME
AS
BEGIN
DECLARE @firstday AS DATETIME;
DECLARE @lastDay AS DATETIME;
DECLARE @sectue AS DATETIME;
--
DECLARE @today AS DATETIME -- for testing
SET @today = @p_date;
SET @firstday = DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0)
SET @lastday = DATEADD(DAY, -1, DATEADD(MONTH, 1, @firstday));
--SELECT @today AS [Today], @firstday AS FirstDayOfMonth, @lastday AS LastDayOfMonth
WITH MyDates AS
(
SELECT @firstday AS MyDate, DATENAME(DW, @firstday) AS NameOfDay
UNION ALL
SELECT DATEADD(DAY, 1, MyDate) AS MyDate, DATENAME(DW, DATEADD(DAY, 1, MyDate)) AS NameOfDay
FROM MyDates
WHERE DATEADD(DAY, 1, MyDate)<@lastDay
)
SELECT @sectue = Mydate
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY NameOfDay ORDER BY MyDate) AS RowNo, *
FROM MyDates
) AS T
WHERE (RowNo=2 ) AND NameOfDay = 'Tuesday';
RETURN @sectue;
END
================================================
PCC_LAST_SEC_TUESDAY
a) Works out the 2nd Tuesday of the month for the current date
b) If this is before or after the current date then return the 2nd Tuesday of the previous month
================================================
CREATE FUNCTION PCC_LAST_SEC_TUESDAY
(
@p_date datetime
)
RETURNS DATETIME
BEGIN
DECLARE @lastsectue DATETIME;
-- Get the 2nd Tuesday for the month of the date given
DECLARE @thissectue DATETIME;
SET @thissectue = dbo.PCC_SEC_TUESDAY(@p_date);
-- is this date after the imput date
IF @thissectue <= @p_date
BEGIN
SET @lastsectue = @thissectue;
END;
ELSE
BEGIN
-- previous months
DECLARE @monthago DATETIME
SET @monthago = DATEADD(MONTH, -1, @p_date);
SET @lastsectue = dbo.PCC_SEC_TUESDAY(@monthago);
END;
--
RETURN @lastsectue;
END;
Report SQL Query
The query to use in the SSRS report is below, the following strings will need editing to reflect your SCCM set up.
- Customer A Device Collection ,'Customer B Device Collection'. - Used to split servers up by customer.
- Server Software Updates -- Name of the SUG deployment.
- SUM | Server Software Updates - Name of collection the SUG is deployed to.
- ADR | Server Software Updates - Name of the ADR used to create the SUG deployment.
declare @collname table(name varchar(100));
declare @title varchar(max);
declare @collid table(id varchar(100));
declare @assignmentid table(id varchar(100));
insert into @collname values( 'Customer A Device Collection'),('Customer B Device Collection')
insert into @assignmentid SELECT AssignmentID FROM v_CIAssignment WHERE
CreationTime >= SELECT [CM_Runtime].[dbo].[PCC_LAST_SEC_TUESDAY](getdate())
and
CollectionName like 'SUM | Server Software Updates%'
and
AssignmentName like '%Server Software Updates%'
insert into @collid SELECT CollectionID from dbo.v_Collection where name in (select name from @collname) -- Get Collection ID's from Collection names --
set @title = (SELECT Title
FROM v_AuthListInfo WHERE
CI_UniqueID = (SELECT TOP 1 CI_UniqueID FROM v_AuthListInfo WHERE Title like 'ADR | Server Software Updates%' ORDER BY DateCreated DESC));
SELECT
@title AS SUG,
Count(StateID) AS Total,
Count(CASE When StateID = '1' OR StateID = '4' THEN 'OK' END) AS OK,
Count(CASE When StateID = '0' THEN 'Unknown' END) AS Unknown,
Count(CASE When StateID = 5 or StateID = 7 or StateID = 8 or StateID = 10 THEN 'Warning' END) AS Warning,
Count(CASE When StateID = 2 or StateID = 6 or StateID = 9 THEn 'Critical' END) AS Critical,
(SELECT name from dbo.v_Collection where CollectionID = v_fullcollectionmembership.collectionID) AS Collection
FROM v_AssignmentState_Combined
INNER JOIN
v_R_System ON v_AssignmentState_Combined.ResourceID = v_R_System.ResourceID INNER JOIN
v_fullCollectionMembership on v_R_System.ResourceID = V_FullCollectionMembership.ResourceID
WHERE
v_R_System.ResourceID = v_AssignmentState_Combined.ResourceID AND
v_AssignmentState_Combined.AssignmentID in (SELECT id from @assignmentid)
AND
v_fullcollectionmembership.collectionID in (select id from @collid)
GROUP BY v_fullcollectionmembership.collectionID
