Video Screencast Help
Endpoint Management Community Blog

Documenting DS6.x Jobs

Created: 21 Nov 2011 • Updated: 21 Nov 2011 • 1 comment
ianatkin's picture
0 0 Votes
Login to vote

In preparation for our DS6.x to 7.x upgrade path, I'm looking at how to document DS6.x jobs. Ideally, I'd like to be able to point a SQL Script at a DS6.x Job and have the entire job nicely documented as an HTML report.

Looking at our job trees, I need to document first and foremost,

  1. 'Copy File' tasks
  2. 'Run Script' tasks

These make up the bulk of our DS6.x jobs. Our imaging tasks are typically done with automation 'Run Scripts'.  After a couple of hours I've been able to come up with some SQL which documents a Copy Task as two tables. The first table gives the job details (copy src, dst etc) and the second documents the return code behaviour.

 

Chucking this up to connect so I don't lose it. ;-)
 
 
-- T-SQL to export a Copy task (task type 12) as readble tables 

-- The ultimate aim idea is to be able to export jobs in a nice format so that  

-- DS6.x Jobs can be fully documented.  

--  

-- Uses: DS7 upgrades, and perhaps automating much of image build and software delivery documentation

DECLARE @EventName VARCHAR(100) 

DECLARE @EventID INT 

DECLARE @TaskIndex INT 

DECLARE @ConditionIndex INT 



SET @EventName='Copy_Sample' 

SET @EventID=(SELECT TOP 1 event_id 

              FROM   event 

              WHERE  name LIKE 'Copy_Sample') 

-- First let's just look at the first task in the first condition 

SET @TaskIndex=0 

SET @ConditionIndex=0 



--handler=0  means on success stop 

--handler=1 means on success continue 

--handler=xxx means run job xxx 

SELECT local_file  'Source', 

       remote_file 'Destination', 

       CASE download_directory 

         WHEN 0 THEN 'File' 

         ELSE 'Directory' 

       END         'Copy Type', 

       CASE 

         WHEN download_directory = 0 THEN 'N/A' 

         WHEN recurse_dir = 0 THEN 'No' 

         ELSE 'Yes' 

       END         'Copy Subfolders', 

       CASE aclient_to_download 

         WHEN 0 THEN 'Use DS' 

         ELSE 'Direct' 

       END         'Copy Method', 

       CASE 

         WHEN username IS NULL THEN 'N/A' 

         ELSE username 

       END         'Copy Credential', 

       CASE allow_automation 

         WHEN 0 THEN 'No' 

         ELSE 'Yes' 

       END         'Allow Automation' 

FROM   copyfile_task 

WHERE  event_id = @EventID 

       AND task_seq = @TaskIndex 

       AND cond_seq = @ConditionIndex 



SELECT CAST(ret_code AS VARCHAR(100)) AS [Code], 

       CASE handler 

         WHEN 0 THEN 'Stop' 

         WHEN 1 THEN 'Continue' 

         ELSE 'Execute: ' + (SELECT name 

                             FROM   event 

                             WHERE  event_id = handler) 

       END                            AS [Response], 

       CASE 

         WHEN ret_code = 0 THEN 'Success' 

         WHEN result = 1 THEN 'Success' 

         ELSE 'Failure' 

       END                            AS [Result], 

       CASE 

         WHEN ( Len(status) > 0 ) THEN status 

         ELSE '' 

       END                            AS [Return Code Text (Status)] 

FROM   task_return_handlers 

WHERE  event_id = @EventID 

       AND task_seq = @TaskIndex 

       AND cond_seq = @ConditionIndex 

UNION 

SELECT 'Other', 

       (SELECT CASE on_fail 

                 WHEN 0 THEN 'Stop' 

                 WHEN 1 THEN 'Continue' 

                 ELSE 'Execute: ' + (SELECT name 

                                     FROM   event 

                                     WHERE  event_id = on_fail) 

               END 

        FROM   task 

        WHERE  event_id = @EventID 

               AND task_seq = @TaskIndex 

               AND cond_seq = @ConditionIndex), 

       'Failure', 

       '' 
 
 
I'll probably convert this into a function to export a copy task as HTML. I'll keep chucking progress into this blog posting, and throw out any working 'task documenters' as articles.
 
I'll see what I can do in future updates to remove the extra line breaks that appear in the SQL above...
 
Kind Regards,
Ian./

Comments 1 CommentJump to latest comment

ianatkin's picture

Now.... have the basis of some SQL which will iterate through the tasks and conditions which make up a job. Abstracted out the SQL which documents copy tasks into an SP.

So, now just need to create the SPs for other task types to insert into this wrapper....

 

 DECLARE @EventName varchar(100)
DECLARE @EventID int
DECLARE @TaskIndex int
DECLARE @ConditionIndex int
DECLARE @TaskType int

DECLARE @MaxTask int
DECLARE @MaxCondition int


SET @EventName='Copy_Sample'
SET @EventID=(select top 1 event_id from event where name like 'Copy_Sample')


SET @MaxCondition =(select max(cond_seq) from task where event_id=@EventID)

-- For each condition sequence we have a number of tasks.
-- so we need to look at each condition, and find the number of tasks within each one

DECLARE @i int -- iterates through tasks
DECLARE @j int -- iterates through conditions

SET @j=0 

WHILE @j<=@MaxCondition
BEGIN
  --find the number of tasks for this specific condition
  SET @i=0 
  SET @MaxTask =(select max(task_seq) from task where event_id=@EventID and cond_seq=@j)
  WHILE @i<=@MaxTask
    BEGIN
	SET @Tasktype=(SELECT task_type from task where event_id=@EventID and cond_seq=@j and task_seq=@i)
    SELECT 'Job: ' + @EventName + '  Condition: ' + (Select name from event_condition where event_id=@EventID and cond_seq=@j) + '  Task: ' + cast((@i+1) as varchar) + ' of ' + cast((@maxtask+1) as varchar ) 
    IF @TaskType=12 EXEC [Custom_Doc_CopyTask] @EventID,@i,@j
    SET @i=@i+1
  END
SET @j=@j+1
END


Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads

-2
Login to vote