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.

MS Bulletin Compliance.RDL

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 

Keep Track of your OS Deployments Part 2 (Drill-down Report)

 As promised ,here's the drill-down report for the OSD - Overview Report i posted last week. This OSD Details Report shows the run time and status of each step in a task sequence.

This report can either be run standalone or as a drilldown report for my OSD - Overview report that i posted last week.

This report is brand new so there might still be some kinks that needs to be sorted out.

OSD-Details.rdl

Features

If you run it standalone you get two dropdown list ,first you select the Task Sequence and Advertised collection ,then you get a list of only the computers that have ran that deployment(not every computer in your site as in the default reports)

The top part of the report tries to resolve some basic information about the computer to help troubleshoot. The information available will vary ,an unknown client won't have any information in the database an will be blank.

It will try to get Make and Model ,Active Directory DN and Chassis Type.(The picture will change based on chassis type)

Stuff you might want to change

This report might need some work to display correctly ,therefore I've included an unfiltered view on page two of the report that you can use to fine tune your report.
The problem is that some steps have a StepID 0 or some other problem that's going to mess with the run time calculation.
For instance "Install Collection Software" will show a total run time for the action ,but each software will have it's own step with StepID 0 with it's own run time ,so if you include both it will look like the step took twice as long to complete.
Another Problem is that some steps like "Restart Computer" and "Setup Windows and Configuration Manger" show up twice ,it seems like the first line shows run time without the reboot and the second line shows time with the reboot. I haven't figured out how to fix this yet so for now you have to live with for now.(let me know if you figure out how to fix this....)

To get the report to work import it into your enviroment and change "datasource1"

To finetune the report you can edit the last line in Dataset1.

the "Not IN" clause is essentially a filter ,the 11xxx numbers are the LastMessageIDs you don't want to include.

AND tse.laststatusmessageid NOT IN 

( 11138, 11139, 11140, 11141,11142,11143,11902,11904,11905 )

you might want to remove the 11902, 11904 and 11905 ,they are for the Install based on task sequence variable step.

Link the Reports

To link this report to the OSD -Overview report open the OSD-Overview in Report Builder and right click [Computername] in the Tablix and select properties.

Under Actions ,change the settings to point to this report.

Do the same for the databar in the tablix...

Hope these reports can help you shed some light on your OS Deployments ,let me know how it works out:-)

Here is the complete Query:

SELECT tse.step                                                           AS 
       TSStep, 
       tse.laststatusmessageid, 
       CASE 
         WHEN ( tse.laststatusmessageid >= 11120 
                AND tse.laststatusmessageid <= 11127 ) THEN tse.groupname 
         ELSE tse.actionname 
       END                                                                AS 
       NAME, 
       CASE 
         WHEN ws.timezoneoffset IS NULL THEN 
         Dateadd(minute, 120, tseprev.executiontime) 
         ELSE Dateadd(minute, ws.timezoneoffset, tseprev.executiontime) 
       END                                                                AS 
       StartTime, 
       CASE 
         WHEN ws.timezoneoffset IS NULL THEN 
         Dateadd(minute, 120, tse.executiontime) 
         ELSE Dateadd(minute, ws.timezoneoffset, tse.executiontime) 
       END                                                                AS 
       EndTime, 
       --dateadd(minute,ws.TimezoneOffset,tseprev.ExecutionTime) as StartTime,  
       --dateadd(minute,ws.TimezoneOffset,tse.ExecutionTime) as EndTime,  
       CASE WHEN Datediff(dd, tseprev.executiontime, tse.executiontime) > 0 THEN 
       Cast( 
       Datediff(dd, tseprev.executiontime, tse.executiontime) AS VARCHAR(2)) + 
       'd ' 
       ELSE '' END 
       + CONVERT(CHAR(8), tse.executiontime - tseprev.executiontime, 108) AS 
       TotalTime, 
       tse.exitcode, 
       tse.actionoutput 
FROM   v_taskexecutionstatus tse 
       INNER JOIN (SELECT Max(executiontime) AS Time, 
                          advertisementid, 
                          resourceid 
                   FROM   v_taskexecutionstatus 
                   WHERE  laststatusmessageid = 11140 
                          AND step = 0 
                   GROUP  BY advertisementid, 
                             resourceid) tsstart 
               ON tse.advertisementid = tsstart.advertisementid 
                  AND tse.resourceid = tsstart.resourceid 
                  AND tse.executiontime > tsstart.time 
       LEFT OUTER JOIN v_taskexecutionstatus tseprev 
                    ON tse.advertisementid = tseprev.advertisementid 
                       AND tse.resourceid = tseprev.resourceid 
                       AND tseprev.executiontime >= tsstart.time 
                       AND tseprev.executiontime < tse.executiontime 
                       AND ( ( tse.step = 0 
                               AND tseprev.step = 0 
                               AND tseprev.laststatusmessageid = 11140 ) 
                              OR ( tse.step > 0 
                                   AND tseprev.step = tse.step - 1 
                                   AND tseprev.laststatusmessageid NOT IN ( 
                                       11138, 
                                       11139, 11140, 11141, 
                                       11142, 
                                       11143 ) ) ) 
       JOIN v_r_system sys 
         ON tse.resourceid = sys.resourceid 
       LEFT OUTER JOIN (SELECT resourceid, 
                               timezoneoffset 
                        FROM   v_gs_workstation_status) ws 
                    ON ws.resourceid = sys.resourceid 
WHERE  tse.advertisementid = @AdvertID 
       AND sys.netbios_name0 = @ComputerName 
       AND tse.laststatusmessageid NOT IN ( 11138, 11139, 11140, 11141, 
                                            11142, 11143, 11902, 11904, 11905 )