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