Kintana Script : Detect notifications to explicit User Names, UserIDs or email addresses

Purpose

This script catches three bad practices for the price of one. It lists workflow steps sending notifications to explicit userids, usernames or email addresses.

This is very bad practice! Use Security Groups or a custom token!

imho PPM should make the user confirm that he wants to send a notification to anything other than a security group or a token, and provide appropriate context sensitive help explaining why sending emails to explicit userids, usernames, or email addresses is very poor practice.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

Paste the script into Toad or SQL Runner

The KNTA_NOTIFICATIONS table condition_value field maps to
the KWFL_WORKFLOW_STEPS workflow_step_id field for requests and packages.

select ww.workflow_name,
       wws.sort_order "Step#",
       wws.step_name,
       nnr.email_address
       nnr.recipient_type_code,
       -- nnr.notification_recipient_id,
       nnr.user_id,
       nu.username,
       nu.first_name || ' ' || nu.last_name "Full Name"

from KWFL_WORKFLOWS ww,
     KWFL_WORKFLOW_STEPS wws,
     KNTA_NOTIFICATIONS nn,
     KNTA_NOTIFICATION_RECIPIENTS nnr,
     KNTA_USERS nu

where  wws.workflow_id = ww.workflow_id
   and wws.workflow_step_id = nn.condition_value
   and nn.notification_id = nnr.notification_id
   and ( ( nnr.recipient_type_code = 'USER'
           and nnr.user_id is not NULL
           and nu.user_id = nnr.user_id)
         or
         ( nnr.recipient_type_code = 'USER_ID'
           and nnr.token is NULL
           and nnr.user_id is not NULL
           and nu.user_id = nnr.user_id)
         or
         ( nnr.recipient_type_code = 'EMAIL_ADDRESS'
           and nnr.email_address is not NULL
           and  nu.email_address = nnr.email_address)
       )

   -- Limit to Enabled Workflows

   and ww.enabled_flag = 'Y'

   -- Limit to one workflow

   -- and ww.workflow_name = 'My Workflow'

   -- Limit to one user

   -- and nu.last_name = 'Houdini'

  order by ww.workflow_name, wws.sort_order

Download

KintanaScripts.zip (79 Kb)