$theTitle=wp_title(" - ", false); if($theTitle != "") { ?>
About Virtualization, VDI, SBC, Application Compatibility and anything else I feel like
12 Dec // php the_time('Y') ?>
Last time I showed a User Defined Function to the Full Path of an Altiris Job given it’s id (event_id). Note that Altiris calls a Job an Event so the terms Event and Jobs are interchangeable here.
To complete it we first need to prepend the server and share name to the path.
I looked into the Altiris database tables to find the best place to get the servername and it seems that the hostname column of the mmsettings table is a good way.
In my database there was only one row in the table but I restrict the results by adding top 1:
1 | SELECT TOP 1 hostname FROM mmsettings |
Then I looked into the available tokens for one that returns a job id but we can only return a job name or a computer id. Since a job name is not unique I decided to use the computername and find the active job for this computer.
When a Job is scheduled an entry is added to the event_schedule table. If you look into this table you will notice a column status_code which is NULL initially and when the job start it will get a value of -1 which indicates the job is active.
When the job has finished the status will always be 0 or higher.So we can determine the active job by querying the event_schedule table for the computer_id and status_code = -1.
1 2 3 4 5 | DECLARE @EventId int SELECT @EventId = event_id FROM event_schedule WHERE computer_id = @ComputerId AND status_code = -1 |
But I found out that during the phase where the Custom Tokens are evaluated the status_code column is still NULL.
However if we query for status_code is NULL we may get back more than one row if there a several jobs scheduled for this computer. So again I used the TOP 1 statement to limit the results:
1 2 3 4 5 | DECLARE @EventId int SELECT TOP 1 @EventId = event_id FROM event_schedule WHERE computer_id = @ComputerId AND status_code is null |
This is the final UDF:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | CREATE FUNCTION [dbo].[GetActiveFullPath] ( @ComputerId int) RETURNS nvarchar(4000) AS BEGIN DECLARE @EventId int SELECT TOP 1 @EventId = event_id FROM event_schedule WHERE computer_id = @ComputerId AND status_code is null DECLARE @str nvarchar(4000); WITH t1 (folder_id, name, parent_id, level) AS ( -- Anchor member definition SELECT folder_id, name, parent_id, 0 AS Level FROM event_folder WHERE folder_id in ( SELECT folder_id FROM event WHERE event_id = @EventId) UNION ALL -- Recursive member definition SELECT t2.folder_id,t2.name, t2.parent_id, Level+1 FROM event_folder AS t2, t1 WHERE t2.folder_id = t1.parent_id ) -- Statement that executes the CTE -- STUFF and FOR XML are used to Concatenate the Values and seperate them by \ character SELECT @str = STUFF((SELECT '\' + name from t1 order by level desc FOR XML PATH('')),1,1,'') -- Prepend Servername and Share SET @str = '\\' + (SELECT TOP 1 hostname FROM mmsettings) + '\eXpress\' + @str RETURN @str; END |
And we use it like this in the Job:
1 2 | set JOBFOLDER=%#*"SELECT dbo.GetActiveFullPath(%ID%)"% if not exist %JOBFOLDER% MD %JOBFOLDER% |
I didn’t find the share name in any of the tables so I hardcoded it as eXpress. If there’s a better way please let me know!
I would be very interested in your comments, do you find this approach usefull or do you have suggestions? Please leave a comment!
One Response for "SQL Query to get the Full Path of an Altiris Job #3"
[…] full function is on my blog in case everyone is […]
Leave a reply