WSUS, How to Email Reports

 
Report.sql > contents follow <

/* note- javascript ignores whitespace, transact sql does not.
This allows the following raiserror trick to work. It inserts a custom CSS header.
Transact will try to interpret the message according to pseudo-printf substitution,
%% is required rather than % to print a % character, $_(name) is required rather than $(name)
where the underscore is an ordinary space.
*/

RAISERROR ('
<script language="JavaScript" type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
<style type="text/css" media="screen">table { width: 100%%; border: 1px solid #cef; text-align: left; } th { font-weight: bold; background-color: #acf; border-bottom: 1px solid #cef;} td,th {padding: 4px 5px;}.odd {background-color: #def;} .odd td{border-bottom:1px solid#cef;}></style>
<script language="JavaScript" type="text/javascript">$ (document).ready(function(){$ ("tr:odd").addClass("odd");});</script>
</script>
', 0, 1) WITH NOWAIT


/*
Approved security updates compliance report
Find computers within a specific target group that need security updates
that have been approved to this group (or a parent group) for at least N days
Optionally, only consider updates of a given MSRC severity rating
*/


SET NOCOUNT ON
DECLARE @TargetGroup nvarchar(30)
DECLARE @Days int

-- Configure these values as needed
-- Example: SELECT @TargetGroup = 'Unassigned Computers'
-- Example: Microsoft update is older than 1 days
SELECT @TargetGroup = 'SUS Reporting Group'
SELECT @Days = 1

-- Find the target group and all it's parent groups
DECLARE @groups AS TABLE (Id uniqueidentifier NOT NULL)
DECLARE @groupId uniqueidentifier
-- retrieve the singular @groupID that corresponds to the TargetGroup
SET @groupId = (
    SELECT ComputerTargetGroupId
    FROM PUBLIC_VIEWS.vComputerTargetGroup
    WHERE vComputerTargetGroup.Name = @TargetGroup
)
IF @groupId is NULL
    RAISERROR ('Invalid Target Group Name', 16, 1)
-- create a table @groups loaded with all the groupId's where the TargetGroup is a member
WHILE @groupId IS NOT NULL
BEGIN
    INSERT INTO @groups SELECT @groupId
    SET @groupId = (
        SELECT ParentTargetGroupId
        FROM PUBLIC_VIEWS.vComputerTargetGroup
        WHERE vComputerTargetGroup.ComputerTargetGroupId = @groupId
    )
END

-- Find all security updates which have been approved for install for at least
-- @Days to the specified target group (or one of it's parent groups)
--
-- create a table @updates loaded with UpdateId's where the TargetGroupId belongs to the @groups table
-- and the Action is 'Install'
-- and the MsrcSeverity is 'Not Null'
-- and the difference between the Microsoft creation data and Today is greater than @Days
--
DECLARE @updates AS TABLE (Id uniqueidentifier NOT NULL)
INSERT INTO @updates
SELECT vUpdate.UpdateId
FROM
    PUBLIC_VIEWS.vUpdate
    INNER JOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateId = vUpdate.UpdateId
WHERE
    DATEDIFF (day, vUpdateApproval.CreationDate, GETUTCDATE()) > @Days
    AND vUpdate.MsrcSeverity is NOT NULL
    AND vUpdateApproval.Action = 'Install'
    AND vUpdateApproval.ComputerTargetGroupId IN (SELECT * FROM @groups)
    -- Can retrieve updates with important/critical MSRC ratings by replacing MsrcSeverity clause with this instead:
    -- AND vUpdate.MsrcSeverity in (’Critical’, ’Important’)
    -- values for MsrcSeverity include Unspecified, Moderate, Low, Critical and Important




IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SET NOCOUNT ON
SET ANSI_WARNINGS OFF


-- List of computers not in compliance for at least one updates
--
-- retrieve the computer name, kb article, and description
-- from several inner joins where
-- the ComputerTarget is an inner set of
-- a ComputerGroup which is an inner set of
-- an UpdateState which is an inner set of
-- an Update which is an inner set of
-- an Updates Group (from the previous query)
-- where the ComputerTarget has a State
-- and is in the 'definition' of 'Failed or Needed'
-- and ComputerTargetGroup Name is the original TargetGroup
--
--
SELECT

vComputerTargetGroup.Name AS Target_Group,
vComputerTarget.Name AS 'Computer_Name',
vComputerTarget.LastReportedStatusTime AS 'LastContactTime',
--CreationDate AS 'Microsoft_Released',
KnowledgebaseArticle AS 'KB_Article',
StateMap.Name AS 'Status',
SecurityBulletin AS 'Security_Bulletin',
MsrcSeverity AS 'Severity_Rating',
DefaultTitle AS Description


INTO #tmp

-- vComputerTarget.Name as 'Computer Name', vUpdate.KnowledgebaseArticle as 'KB Article', vUpdate.DefaultDescription as 'Update Title'
FROM PUBLIC_VIEWS.vComputerGroupMembership
    INNER JOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId
    INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId
    INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId
    INNER JOIN PUBLIC_VIEWS.fnUpdateInstallationStateMap() AS StateMap ON vUpdateInstallationInfoBasic.State = StateMap.Id
    INNER JOIN PUBLIC_VIEWS.vUpdate on vUpdate.UpdateId = vUpdateInstallationInfoBasic.UpdateId
    INNER JOIN @updates GROUPS on vUpdateInstallationInfoBasic.UpdateId = GROUPS.Id
WHERE vComputerTarget.ComputerTargetId = vUpdateInstallationInfoBasic.ComputerTargetId
    AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)
    -- 0=Unknown, 1=NotApplicable, 2=NotInstalled, 3=Downloaded, 4=Installed, 5=Failed, 6=Installed Pending Reboot
    -- 2=NotInstalled, 3=Downloaded, 5=Failed, 6=Installed Pending Reboot (Definition of "Failed or Needed")
    AND vComputerTargetGroup.Name = @TargetGroup
-- would normally consolidate duplicate column values (but multiple Updates can have the same information)    
-- GROUP BY vComputerTarget.Name, vUpdate.KnowledgebaseArticle, vUpdate.DefaultDescription
-- ORDER BY 'Computer_Name' ASC, 'Microsoft_Released'
ORDER BY 'Computer_Name' ASC, 'Security_Bulletin'


EXECUTE SaveTableAsHTML @DBFetch = #tmp, @Header = 1, @CSS =
'table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
 th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
 td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
 tr:hover td{background:#d0dafd;color:#339;}'