Notification Template

Question

Hi, 
 I need some help to build a new notification template. 
 The gooal is to receive notification that show if a Task run succesfully or on error. 
 What i need to write on the SQL Query field on the Notification Template? 
 Thank for help me 
 Faye

Igor.Ilyin · Answer

Hi CSI_Faye,

I believe this template already exists in the Quest InTrust Manager | Configuration | Advanced | Notification Templates | Task Completed. This template resides in the Configuration database. You just create the job at the end of the jobs chain inside your task and it sends you the overall result of the task in the email subject, and also the result of each job in the message body. The SQL query and couple of examples goes below. Do you mean you want to change something in this template?

Email title example 1: Quest InTrust activity report. Task "Gather 4771" completion status is Success.

Email title example 2: Quest InTrust activity report. Task "Gather 4771" completion status is Error.

SQL query:

if
not exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[DatabaseVersion]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
raiserror ('Notification template query doesn''t match the database format.', 16, 1)
end
else if
not exists (select * from dbo.DatabaseVersion where ProductName = 'ADC')
begin
raiserror ('Notification template query doesn''t match the database format.', 16, 1)
end
else
begin
select G.StartTime As StartTime, G.StartTimeLocal As StartTimeLocal, ADCTask.Name As TaskName,
Status = CASE COALESCE(MAX(t.NormalResultType), MAX(t.NotificationResultType))
when 0 then 'Success'
when 1 then 'Warning'
when 2 then 'Error'
when 3 then 'Success'
else 'Unknown'
END,
COALESCE(MAX(ADCTaskGenericJobLog2.EndTime), MAX(ADCTaskGenericJobLog2.StartTime)) As EndTime,
COALESCE(MAX(ADCTaskGenericJobLog2.EndTimeLocal), MAX(ADCTaskGenericJobLog2.StartTimeLocal)) As EndTimeLocal,
JobName = NULL, JobStartTime = NULL, JobEndTime = NULL, JobStartTimeLocal = NULL, JobEndTimeLocal = NULL, JobStatus = NULL, JobResult = NULL, JobType = NULL
from (

select W.ResultType as NormalResultType, NULL as NotificationResultType, W.[$Owner$] as Ow
from ADCTaskWorkflowLogEntry2 as W
where W.[$Owner$] = '%SESSION_ID%'

union
select J.ResultType as NormalResultType, 0 as NotificationResultType, J.[$Owner$] as Ow
from ADCTaskGenericJobLog2 as J
where J.JobId <> '%JOB_ID%' AND
J.[$Owner$] = '%SESSION_ID%'

union
select 0 as NormalResultType, J.ResultType as NotificationResultType, J.[$Owner$] as Ow
from ADCTaskGenericJobLog2 as J
where J.JobId = '%JOB_ID%' AND
J.[$Owner$] = '%SESSION_ID%'
) as t

, ADCTaskGlobalSession2 As G
inner join ADCTask on ADCTask.Guid = G.TaskId
inner join ADCTaskGenericJobLog2 on ADCTaskGenericJobLog2.[$Owner$] = G.Guid
where G.Guid = '%SESSION_ID%' and
(ADCTaskGenericJobLog2.ResultType <= 2 OR (ADCTaskGenericJobLog2.ResultType = 3 AND ADCTaskGenericJobLog2.JobId = '{AA4D1358-A444-4C92-9B70-1F57C39CA85F}') )
group by ADCTask.Name, G.StartTime, G.StartTimeLocal

union
select distinct StartTime = NULL, StartTimeLocal = NULL, TaskName = NULL, Status = NULL, EndTime = NULL,
EndTimeLocal = NULL, l.JobName AS JobName, l.StartTime As JobStartTime,
l.EndTime As JobEndTime,
l.StartTimeLocal As JobStartTimeLocal, l.EndTimeLocal As JobEndTimeLocal,
JobStatus =
CASE l.ResultType
when 0 then 'Success'
when 1 then 'Warning'
when 2 then 'Error'
when 3 then 'Running'
else 'Unknown'
END,
l.Result As JobResult,
JobType = CASE WHEN i.ServerID is null THEN l.JobType ELSE i.AttributeValue END

from ADCTaskGenericJobLog2 l
left join ADCTaskGlobalSession2 s on l.[$Owner$] = s.[$Owner$]
left join ADCClassInventory i on i.ServerID = l.ServerId and i.ComponentID = l.JobClass and i.AttributeName = 'DisplayName'
where l.JobId <> '%JOB_ID%' and
l.[$Owner$] = '%SESSION_ID%'

union
select distinct StartTime = NULL, StartTimeLocal = NULL, TaskName = NULL, Status = NULL, EndTime = NULL, EndTimeLocal = NULL,
ADCTaskWorkflowLogEntry2.JobName AS JobName, ADCTaskWorkflowLogEntry2.Time As JobStartTime,
ADCTaskWorkflowLogEntry2.Time As JobEndTime,
ADCTaskWorkflowLogEntry2.TimeLocal As JobStartTimeLocal, ADCTaskWorkflowLogEntry2.TimeLocal As JobEndTimeLocal,
JobStatus =
CASE ADCTaskWorkflowLogEntry2.ResultType
when 0 then 'Success'
when 1 then 'Warning'
when 2 then 'Error'
else 'Unknown'
END,
ADCTaskWorkflowLogEntry2.Result As JobResult, JobType = NULL
from ADCTaskWorkflowLogEntry2,
ADCTaskGlobalSession2
where ADCTaskGlobalSession2.Guid = '%SESSION_ID%' AND
ADCTaskWorkflowLogEntry2.[$Owner$] = '%SESSION_ID%'
order by StartTime desc, JobStartTime

end