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