Cacher is the code snippet organizer for pro developers

--build a CTE with the TS execution details
WITH TSX (ComputerName,PackageName,ExecutionTime,Step,ExitCode,POSITION,ExecutionStatus,ExecutionID) AS
  ( SELECT cm.Name0 AS ComputerName ,
           pkg.Name AS PackageName --execution time has been converted to GMT; use offset to convert it to machine local time

                                 ,
                                 dateadd(MINUTE,ws.TimezoneOffset,ts.ExecutionTime) 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'
                                                             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
      
      -- ########################################
	  -- Beschränken auf Computer
      WHERE Name0 LIKE 'D-w-d804437'
      -- ########################################
 ) cm

   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) ) ) AS ts ON ts.ResourceID = cm.ResourceID --advertisement info

   JOIN
     ( SELECT AdvertisementID,
              PackageID
      FROM v_Advertisement
      
      -- ########################################
	  -- Beschränken auf Advertisement
      --where AdvertisementID LIKE 'D012B0C9'
      -- ########################################
      
 ) 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.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
JOIN
  ( SELECT *
   FROM TSX
   WHERE POSITION = 'End' ) AS tsEnd ON tsStart.ComputerName = tsEnd.ComputerName
AND tsStart.PackageName = tsEnd.PackageName
AND tsStart.ExecutionID = tsEnd.ExecutionID