Hi Stefan,
I believe the SQL below is what you're looking for.
To give a brief explanation around what I needed to do to get it:
I got the latest State from the Job (multiple versions are stored for each Job, it increases the version when the Job is modified)
I then used the State and extracted the Tasks from it, in the correct order.
I then joined the Tasks and Job to the Item table to return the Job Name and Task Names in order.
I've tested this with two jobs, however please test this in your environment and let us know the results.
Thanks!
DECLARE @JobGuid as UniqueIdentifier = '<InsertJobGuidHere>'
DECLARE @XML XML =
(
SELECT IVD.State
FROM ItemVersions IV
INNER JOIN ItemVersionData IVD ON IV.VersionGuid = IVD.VersionGuid
WHERE IV.ItemGuid = @JobGuid
AND IV.Version =
(
SELECT MAX(IV2.Version)
FROM ItemVersions IV2
WHERE IV2.ItemGuid = @JobGuid
)
)
SELECT I2.[Name] AS JobName, I2.[Guid] AS JobGuid, I.[Name] AS TaskName, T.N.value('@taskVersionGuid[1]', 'UNIQUEIDENTIFIER') AS TaskGuid
FROM @XML.nodes('/vItem/jobScript/node/subNodes/node/execute') AS T(N)
INNER JOIN Item I ON I.[Guid] = T.N.value('@taskVersionGuid[1]', 'UNIQUEIDENTIFIER')
INNER JOIN Item I2 ON I2.[Guid] = @JobGuid