ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Service Desk Manager Activity Notification and Notification Rule Relationship

book

Article ID: 237375

calendar_today

Updated On:

Products

CA Service Management - Service Desk Manager CA Service Desk Manager

Issue/Introduction

How to extract all Service Desk Manager (SDM) Notification Rules attached to an Activity Notification that is sent to the Affected End User?

Environment

Service Desk Manager 17.x

All Supported Database Management Systems (DBMS)

Resolution

The following documentation on Notification Rules Relational Tables provided additional information:

https://techdocs.broadcom.com/us/en/ca-enterprise-software/business-management/ca-service-management/17-3/reference/ca-service-desk-manager-reference-commands/data-element-dictionary/usp-relational-table-notification-rule.html

Specifically, the 'usp_lrel_ntfr_macrol_att_ntfrl' MDB table contains the required information.

The following MDB query can be used as a template:

SELECT aty.sym 'Activity_Notification', ntfr.id, ntfr.sym 'Notification_Rule'
FROM act_type aty
LEFT JOIN usp_lrel_ntfr_macrol_att_ntfrl lrelntfr ON
lrelntfr.macro = aty.cr_notify_info
-- aty.iss_notify_info
-- aty.chg_notify_info
-- aty.mgs_notify_info
-- aty.kd_notify_info
-- aty.kd_comment_notify_info
-- aty.krc_notify_info
-- aty.sa_notify_info
-- aty.cnt_notify_info
-- aty.ci_notify_info
LEFT JOIN ntfr ON lrelntfr.ntfr = ntfr.id
LEFT JOIN ntfm ON
ntfm.persid =  ntfr.cr_notify_info
-- ntfr.iss_notify_info
-- ntfr.chg_notify_info
-- ntfr.mgs_notify_info
-- ntfr.kd_notify_info
-- ntfr.kd_comment_notify_info
-- ntfr.krc_notify_info
-- ntfr.sa_notify_info
-- ntfr.cnt_notify_info
-- ntfr.ci_notify_info
LEFT JOIN usp_lrel_ntfr_ntfl_att_ntfrl objcnt ON ntfr.id = objcnt.ntfr
LEFT JOIN ntfl ON objcnt.ntfl = ntfl.id
WHERE aty.del = 0 AND ntfr.del = 0 AND ntfm.notify_flag = 1 AND
ntfl.object_type =  'cr'
-- 'iss'
-- 'chg'
-- 'mgs'
-- 'KD'
-- 'O_COMMENTS'
-- 'KT_REPORT_CARD'
-- 'sa_notif'
-- 'cnt'
-- 'nr'
AND ntfl.object_attr='customer'

From the query above, you can adjust to uncomment the objects based on your requirements.  For example, 'cr' for Requests, 'chg' for Change Orders, etc.