List email notification recipients for Jobs #adtempus #sql #version4

readme.md

This SQL script lists the names and addresses of all Notification Recipients used by Notification Actions in Responses defined for Jobs and Job Steps and for Notification Tasks within jobs.

sample.sql

select j.name as JobName,0 as stepNumber,'Response' as [Type], nr.name,addr.address

from job j 
	join response r on j.oid=r.JobOID 
		join ResponseAction  a on a.ResponseOID=r.oid 
			join notificationAction na on na.oid=a.oid
				join NotificationActionRecipient nar on nar.NotificationActionOID=na.oid
				join NotificationRecipient nr on nr.oid=nar.NotificationRecipientOID
				join notificationAddress addr on addr.ownerOID=nr.oid


union 

select j.name as JobName,js.stepNumber as stepNumber,'Response' as [Type], nr.name,addr.address

from job j 
join jobStep js on j.oid=js.JobOID 
	join response r on js.oid=r.JobStepOID 
		join ResponseAction  a on a.ResponseOID=r.oid 
			join notificationAction na on na.oid=a.oid
				join NotificationActionRecipient nar on nar.NotificationActionOID=na.oid
				join NotificationRecipient nr on nr.oid=nar.NotificationRecipientOID
				join notificationAddress addr on addr.ownerOID=nr.oid

union

select j.name as JobName,js.stepNumber as stepNumber,'Notification Task' as [Type], nr.name,addr.address

from job j 
join jobStep js on j.oid=js.JobOID 
join task t on js.oid=t.JobStepOID 
join notificationTask nt on nt.oid=t.oid
join NotificationTaskRecipient ntr on ntr.NotificationTaskOID=t.oid
				join NotificationRecipient nr on nr.oid=ntr.NotificationRecipientOID
				join notificationAddress addr on addr.ownerOID=nr.oid

order by j.name,stepNumber