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 ) 

Keep track of your OS Deployments

Update: I've created a new and improved version of this report. you can find it here:

OSD Dashboard

Do you have full control over all OS Deployments in your organisation? Do you know how long each machine took to complete the task sequence? Do you know how many OS Deployments are running right now?
If other people is running your task sequences you probably heard stuff like "Task Sequences are so slow","Ghost/WDS was much faster" and "The task sequence never works".
So to keep on top of things I created this report:

OSD-Overview.rdl
Update: I accidentally uploaded an older version with some Odd filters for the barchart.
I've updated the link to point to the new version. 

This report allows me to keep track of witch computer ran what task sequence and how long it took.
It also shows how many times each task sequence has been run the last 3 months and how many times they have failed.
I uses colored databars to visualize how long each machine took to complete the task sequence. It's not exact science since some task sequence takes longer than others to complete and some locations have faster connections then others ,but i know it should not take much longer than 50 minutes to complete a task sequence.

The report is based of a query i found on myITForum written by Jeremy Young.
I've modified the query a bit to show clients that haven't registered fully with Configmgr yet. I've replaced the joins with left outer joins ,and added some stuff to show running clients and clients that have timed out.

The report gives you a quick overview of OS Deployments running in your environment It show witch computer ran what task sequence at what time and how long it took.
It uses a bar graph in each column to indicate the run time ,allowing you to easily spot problems.
The bar is green if the task sequence completed in under 50 minutes ,over 50 minutes the bar becomes more and more yellow. This value is controlled by a parameter so you can easily change it to suit your environment.
The Bar graph at the top shows which task sequence have been ran in the last 90 days.

There's also a sub report for this so you can click on a computer to get the run time for each step ,so you know what took so long.
I will add this in my next blog post....

  

To get this report up and running in your environment you just need to download the .RDL file and import it into your environment. Then you need to edit it in Report Builder and change the "Datasource1" to point to your Configmgr SQL.

The barchart at the top have a category group filter to filter out task sequences you don't want to include in the report. Default it don't include task sequences with "LAB" in the name.

To change the filter click the bar chart to get the chart data window. then right click the [PackageName] and select Properties.
Navigate to Filters and remove or edit the filter.
(Add the same filter to the Tablix if you don't want to see them at all)

Query:

/* Configuration Manager Task Sequence Execution Times
  Original Query By: Jeremy Young (jeremy.young@microsoft.com)
  http://myitforum.com/cs2/blogs/jeremyyoung/
Modified with looser joins and some other stuff to allow for clients that havent registeret with confimgr yet.
*/ 
DECLARE @DefaultTimezone INTEGER 

SET @DefaultTimezone = 120 
--build a CTE with the TS execution details 
; 

WITH tsx (computername, packagename, advertisment, executiontime, step, exitcode 
     , 
     position, executionstatus, executionid) 
     AS (SELECT cm.name0                            AS ComputerName, 
                pkg.NAME                            AS PackageName, 
                ad.advertisementid                  AS Advertisment 
--execution time has been converted to GMT; use offset to convert it to machine local time 
                , 
                CASE 
                  WHEN ws.timezoneoffset IS NULL THEN 
                  Dateadd(minute, @DefaultTimezone, ts.executiontime) 
                  ELSE Dateadd(minute, ws.timezoneoffset, ts.executiontime) 
                END                                 AS ExecutionTime, 
                ts.step, 
                ts.exitcode 
                --determine if it was a start or end 
                , 
                CASE 
                  WHEN ( ts.laststatusmessageidname = 
'The task sequence execution engine started execution of a task sequence' 
AND step = 0 ) THEN 'Start' 
ELSE 'End' 
END                                 AS Position 
--consolidate the other TS messages into short status messages 
, 
CASE 
WHEN ts.laststatusmessageidname = 
'The task sequence execution engine aborted execution for a failure of an action' 
THEN 'Aborted' 
WHEN ts.laststatusmessageidname = 
'The task sequence execution engine successfully completed a task sequence' THEN 
'Completed' 
WHEN ts.laststatusmessageidname = 
'The task sequence execution engine failed execution of a task sequence' 
THEN 'Failed' 
WHEN ts.laststatusmessageidname = 
'The task sequence execution engine performed a system reboot initiated by an action' 
THEN 'Timed Out' 
ELSE 'Unknown' 
END                                 AS ExecutionStatus 
--partition the data into task sequence start times per package name 
, 
Dense_rank() 
OVER ( 
partition BY name0, pkg.NAME 
--only get starts at step 0 versus when the task sequence restarts after a reboot for example 
, CASE WHEN (ts.laststatusmessageidname = 
'The task sequence execution engine started execution of a task sequence' AND 
step=0) THEN 1 ELSE 0 END 
ORDER BY ts.executiontime DESC) AS ExecutionID 
FROM 
--systems  
(SELECT name0, 
     resourceid 
FROM   v_r_system) cm 
--timezone data 
LEFT OUTER JOIN (SELECT resourceid, 
                     timezoneoffset 
              FROM   v_gs_workstation_status) ws 
          ON ws.resourceid = cm.resourceid 
--task sequence execution data 
JOIN (SELECT resourceid, 
          advertisementid, 
          step, 
          exitcode, 
          laststatusmessageidname, 
          executiontime 
   FROM   v_taskexecutionstatus ts 
   WHERE 
    --get TS starts or Ends of a task sequence execution 
    ( 
    --Ends 
    laststatusmessageidname IN ( 
'The task sequence execution engine successfully completed a task sequence', 
'The task sequence execution engine aborted execution for a failure of an action' 
                        , 
'The task sequence execution engine failed execution of a task sequence' ) 
OR 
--Starts 
( laststatusmessageidname = 
'The task sequence execution engine started execution of a task sequence' 
AND step = 0 ) 
OR 
--Timeouts 
( laststatusmessageidname = 
'The task sequence execution engine performed a system reboot initiated by an action' 
AND ts.executiontime < Dateadd(day, -1, Getdate()) ) )) AS ts 
ON ts.resourceid = cm.resourceid 
--advertisement info 
JOIN (SELECT advertisementid, 
          packageid 
   FROM   v_advertisement) AS ad 
ON ad.advertisementid = ts.advertisementid 
--package info 
JOIN (SELECT packageid, 
          NAME 
   FROM   v_package) AS pkg 
ON pkg.packageid = ad.packageid) 
--select out a consolidated row 
SELECT tsStart.computername, 
       tsStart.packagename, 
       tsStart.executionid, 
       tsStart.advertisment, 
       tsStart.executiontime                                                 AS 
       StartTime, 
       tsStart.step                                                          AS 
       StartStep, 
       tsEnd.executiontime                                                   AS 
       EndTime, 
       tsEnd.step                                                            AS 
       EndStep, 
       tsEnd.executionstatus                                                 AS 
       ExecutionStatus, 
       CONVERT(VARCHAR(8), tsEnd.executiontime - tsStart.executiontime, 108) AS 
       ExecutionDuration, 
       Datediff(day, tsStart.executiontime, Getdate())                       AS 
       ExecutedDaysAgo 
FROM   (SELECT * 
        FROM   tsx 
        WHERE  position = 'Start') AS tsStart 
       LEFT OUTER JOIN (SELECT * 
                        FROM   tsx 
                        WHERE  position = 'End') AS tsEnd 
                    ON tsStart.computername = tsEnd.computername 
                       AND tsStart.packagename = tsEnd.packagename 
                       AND tsStart.executionid = tsEnd.executionid 
ORDER  BY starttime DESC