ITMS Administrator Group

 View Only
  • 1.  SQL showing Job with task ordered as showed in the console

    Posted Sep 05, 2017 08:40 AM

    Hi,

    I am looking for an SQL Query which shows me all the tasks belonging to a job and in the order which I have it in the console. So this is an example:

    I have found the following query which lets me query a job and shows me all tasks included in the job.

    SELECT i1.Name AS Job,i2.Name AS Task
    FROM Item i1
    JOIN ItemReference ir ON ir.ParentItemGuid = i1.[Guid]
    JOIN Item i2 ON i2.[Guid] = ir.ChildItemGuid
    WHERE ir.Hint = 'jobusestask' ir.ParentItemGuid = '<jobguidhere>'


    For example I get the following result:

    My Job    Install - Java Runtime x86
    My Job    Install - Adobe Reader
    My Job    Install - Google Chrome

    However I do not see in which order they are. I guess somewhere in the database this should be found but I haven't found it. Does anyone have a query to get this info from the database?

    Thanks

    Stefan



  • 2.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 05, 2017 09:21 AM

    Two ways to find this

    • Run a sql trace when you load the page
    • Run the Altiris Profiler - The 'SQL Trace' and 'Code Trace' are very handy here.
      • E:\Program Files\Altiris\Diagnostics\Profiler.exe


  • 3.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 05, 2017 09:24 AM

    Need SQL to list all Tasks that are part of a Job
    https://www.symantec.com/connect/forums/need-sql-list-all-tasks-are-part-job



  • 4.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 06, 2017 03:14 AM

    I did try the profiler however I was not able to find the sql query I am looking for. I know this is weird it should be there.

    I also saw the thread which you are pointing to. There I found the query mentioned earlier. However, this does not give me ayn ordering.



  • 5.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 06, 2017 06:59 AM

    I'd need to look into it but the XML of the Task/Job itself, usually the [State] column in vItem shows the XML, might have the ordering.



  • 6.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 06, 2017 11:25 AM

    Looking at the XML.

    DECLARE @JobGuid as UniqueIdentifier
    SET @JobGuid = 'REPLACE WITH GUID'
    
    
    SELECT 
      IV.*, 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 
      )
    

     

    Then in the 'State' check for the SubNodes where the Task Guids will be shown, you can then get the information about those Tasks to see their name etc.

    <vItem>
     <jobScript>
      <node ...>
       <subNodes>
        <node ...>
         <execute taskVersionGuid="GUID" />
        </node>
        <node ...>
         <execute taskVersionGuid="GUID" />
        </node>
       </subNodes>
      </node>
     </jobScript>
    </vItem>

    I still need to work out the SQL to grab just the Guids with a similar process to this post.



  • 7.  RE: SQL showing Job with task ordered as showed in the console
    Best Answer

    Trusted Advisor
    Posted Sep 12, 2017 06:58 AM

    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
    

     



  • 8.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 12, 2017 09:20 AM

    Nice job!

    I just tested it quickly and it seems to work. I will do some furhter tests tomorrow and let you know.

    Thanks.

    Stefan



  • 9.  RE: SQL showing Job with task ordered as showed in the console

    Trusted Advisor
    Posted Sep 14, 2017 08:26 AM

    Hi Stefan,

    How did the testing go?



  • 10.  RE: SQL showing Job with task ordered as showed in the console

    Posted Sep 15, 2017 04:41 AM

    Yes. It is working fine...