SQL Query to get the Full Path of an Altiris Job #3

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:

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.

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:

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:

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:

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!