Summary
This article provides a database query that can be used to get a list of all jobs that send notification messages and the recipients that each job notifies.
More Information
See article K00000385 for instructions on how to execute a database query.
The following SQL query returns a list of all jobs that contain Notification Tasks or Notification Actions in Responses, and lists all of the notification recipients who receive notification for the job. The query reports on job-level and step-level Responses.
select j.name as JobName, 0 as StepNumber, cast(event as varchar(50)) as Event, 'Individual' as RecipientType, nr.name as RecipientName from job j join response r on j.oid=r.owner join actions a on r.oid=a.owner join notificationaction na on a.actionoid=na.oid join notificationrecipients nrs on nrs.owner=na.oid join notificationindividual nr on nr.oid=nrs.recipientoid join events es on es.owner=r.oid join jobeventfilter e on es.eventoid=e.oid where j.instanceparent is null union select j.name as JobName, 0 as StepNumber, cast(event as varchar(50)) as Event, 'Group' as RecipientType, nr.name as RecipientName from job j join response r on j.oid=r.owner join actions a on r.oid=a.owner join notificationaction na on a.actionoid=na.oid join notificationrecipients nrs on nrs.owner=na.oid join notificationgroup nr on nr.oid=nrs.recipientoid join events es on es.owner=r.oid join jobeventfilter e on es.eventoid=e.oid where j.instanceparent is null union select j.name as JobName, StepNumber, cast(event as varchar(50)) as Event, 'Individual' as RecipientType, nr.name as RecipientName from job j join jobstep js on j.oid=js.owner join response r on js.oid=r.owner join actions a on r.oid=a.owner join notificationaction na on a.actionoid=na.oid join notificationrecipients nrs on nrs.owner=na.oid join notificationindividual nr on nr.oid=nrs.recipientoid join events es on es.owner=r.oid join jobeventfilter e on es.eventoid=e.oid where j.instanceparent is null union select j.name as JobName, StepNumber, cast(event as varchar(50)) as Event, 'Group' as RecipientType, nr.name as RecipientName from job j join jobstep js on j.oid=js.owner join response r on js.oid=r.owner join actions a on r.oid=a.owner join notificationaction na on a.actionoid=na.oid join notificationrecipients nrs on nrs.owner=na.oid join notificationgroup nr on nr.oid=nrs.recipientoid join events es on es.owner=r.oid join jobeventfilter e on es.eventoid=e.oid where j.instanceparent is null union select j.name as JobName,StepNumber,'Notification Task' as Event, 'Individual' as RecipientType, nr.name as RecipientName from job j join jobstep js on j.oid=js.owner join notificationtask nt on js.task=nt.oid join notificationrecipients nrs on nrs.owner=nt.oid join notificationindividual nr on nr.oid=nrs.recipientoid where j.instanceparent is null union select j.name as JobName,StepNumber,'Notification Task' as Event, 'Group' as RecipientType, nr.name as RecipientName from job j join jobstep js on j.oid=js.owner join notificationtask nt on js.task=nt.oid join notificationrecipients nrs on nrs.owner=nt.oid join notificationgroup nr on nr.oid=nrs.recipientoid where j.instanceparent is null order by jobname,stepnumber,event,recipientname
