Thursday, November 27, 2014

MS Bulletin Quick Compliance Check

Ok ,it's been a while since my last post so i thought I'd share something useful and relevant.
Microsoft released a couple of out-of-band patches a few days ago that you should deploy asap.

To keep track of the deployment of patches belonging to a specific bulletin ID i created this quick and dirty report. Still some work left on this report ,but might still be useful 

The report gives a quick overview on how compliment your environment is to a bulletin-ID.

Update: Have made some changes to the report ,Updated the colors(last one looked like a traffic light) and added "Last Status Message" to give it some more details.
Grab the new version  ,link below the picture.



The reports defaults to MS14-068 and clients that have been active in the last 30 days ,both values can be changed.

As always you have to change the datasource to suit your environment ,check my earlier post on details on how to do this

This is just a quick draft ,going to add a few fetures to it when i have time

  •  the Report don't consider clients with status "Not Applicable".
    For now it's just required vs installed

Hope you find it useful.

Here's the Query ,note it's a simple query ,but with quite a few joins :

SELECT B.name0, 
       E.caption0, 
       CASE 
         WHEN E.producttype0 = 2 THEN 'Server' 
         WHEN E.producttype0 = 3 THEN 'Server' 
         WHEN E.producttype0 = 1 THEN 'Client' 
       END AS ClientType, 
       C.bulletinid, 
       C.articleid, 
       C.title, 
       D.statename, 
       H.statename, 
       F.lastactivetime 
FROM   v_updatecompliancestatus A 
       INNER JOIN v_r_system B 
               ON A.resourceid = B.resourceid --To get Computername 
       INNER JOIN v_updateinfo C 
               ON A.ci_id = C.ci_id --To get Details about the Update 
       INNER JOIN v_statenames D 
               ON A.status = D.stateid 
                  AND D.topictype = 500 --To get the installstatus 
       INNER JOIN v_gs_operating_system E 
               ON A.resourceid = E.resourceid --To get OS info 
       INNER JOIN v_ch_clientsummary F 
               ON A.resourceid = F.resourceid --To get Client last active time 
       INNER JOIN v_statenames H 
               ON A.lastenforcementmessageid = H.stateid 
                  AND H.topictype = 402 --To get State Messages 
WHERE  C.bulletinid = @BulletinID