What task sequences have run (containing an image file)

-- What task sequences have run (containing an image file)

select

Start.ResourceID

, s.Name0

, s.Resource_Domain_OR_Workgr0

, a.AdvertisementID

, tsp.Name as TaskSequence

, tsp.Description

, CStep.ActionName

, CStep.ExitCode

, case

when CStep.ExitCode = 0 then ''

else master.dbo.fn_varbintohexstr(CStep.ExitCode) end

as HexExitCode

, DATEADD(HH, DATEDIFF (hh, GETUTCDATE(),GETDATE()), Start.LatestStartTime) as StartTime

, DATEADD(HH, DATEDIFF (hh, GETUTCDATE(),GETDATE()), CStep.ExecutionTime) as LastStep

, DATEADD(HH, DATEDIFF (hh, GETUTCDATE(),GETDATE()), Ended.LatestEndTime) as EndTime

, CONVERT (VARCHAR, Ended.LatestEndTime - Start.LatestStartTime, 108) as TotalTime

from

(

select ResourceID, MAX (ExecutionTime) as LatestStartTime from v_TaskExecutionStatus

where

LastStatusMessageID = 11140 and Step = 0

group by ResourceID

) as Start

inner join

(

-- Find the details of the very last step

select tes.ExitCode, tes.ActionName, tes.ResourceID, tes.ExecutionTime, tes.AdvertisementID from v_TaskExecutionStatus tes

inner join

(

-- Find the very last step time

select ResourceID, MAX (ExecutionTime) LastStep from v_TaskExecutionStatus

group by ResourceID

) as CStepInner on CStepInner.ResourceID = tes.ResourceID and CStepInner.LastStep = tes.ExecutionTime

) as CStep on CStep.ResourceID = Start.ResourceID

inner join v_R_System s on s.ResourceID = Start.ResourceID

inner join v_Advertisement a on a.AdvertisementID = CStep.AdvertisementID

inner join v_TaskSequencePackage tsp on a.PackageID = tsp.PackageID

inner join v_TaskSequenceReferencesInfo tsri on tsp.packageid = tsri.PackageID and tsri.ReferencePackageType = 257

left join

(

select ResourceID, MAX (ExecutionTime) as LatestEndTime from v_TaskExecutionStatus

where

LastStatusMessageID = 11143

group by ResourceID

) as Ended on Ended.ResourceID = Start.ResourceID and Ended.LatestEndTime >= Start.LatestStartTime

order by Start.LatestStartTime desc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s