Kintana Script : Audit Deleted Packages

Purpose

One of the many surprising things disclosed by the Deployment Metrics package is just how many packages are being deleted.

It need hardly be said that this is very bad practice!

As of PPM version 6.0 the good news is that the system can maintain an audit trail to show who has deleted packages and when. The bad news is that using this feature is not particularly well documented.

The first thing you need to do is set auditing on for all your workflows.

workflow auditing can be turned on by opening a workflow, selecting the 'Deployment Management Settings' tab, then selecting the ' Modify Settings For: Auditing' radiobutton.

Check the 'Turn on Package Auditing' checkbox.

The dialog states that the purpose of this checkbox is to audit changes in package notes. Although it doesn't say so it also has the effect of auditing package deletions.

It would have been better to add an explicit checkbox to the dialog for this purpose, but perhaps the developer was pressed for time.

PPM stores the resulting Y/N AUDIT_FLAG field not in KDLV_WORKFLOWS which is where you might think it should logically belong, but instead it stores it in the KDLV_WORKFLOW_EXTN table for use by the SOX accelerator.

Tested

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

Usage

Paste the script into Toad or SQL Runner

Omit the comment, spool, and linesize statements if you're using SQL RUNNER.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.

If using PL-SQL change 'yyyymmdd' in the output file name to today's date.

Periodically verify that the audit is set on for all your workflows.

This sql will help you ensure that setting the audit flag on is not missed for new workflows. You can use it as-is or build it into a custom PPM report.

spool c:\deleted_packages_yyyymmdd.txt
set linesize 1000

select dwe.audit_flag,
       ww.enabled_flag,
       ww.workflow_name
from KDLV_WORKFLOW_EXTN dwe,
     KWFL_WORKFLOWS ww
where dwe.workflow_id = ww.workflow_id
  and ww.workflow_name not like '%(REFERENCE)%'
  -- and dwe.audit_flag != 'Y'
  -- and ww.enabled_flag = 'Y'
order by dwe.audit_flag, ww.workflow_name

spool off

AUDIT_FLAG  ENABLED_FLAG  WORKFLOW_NAME
    Y           Y         Inventory Main v02
    Y           Y         Inventory Re-Stocking v01
...

To extract the output, click on the [Open as Text] button.

This opens a text window from which you can copy and paste.

Periodically verify who deleted packages, when, from what workflow, and what status the package was in when it was deep-sixed.

This sql will allow you to track down the malfeasers. You can use it as-is or build it into a custom PPM report.

spool c:\deleted_packages_yyyymmdd.txt
set linesize 1000

select dwe.audit_flag,
       ww.enabled_flag,
       ww.workflow_name
from KDLV_WORKFLOW_EXTN dwe,
     KWFL_WORKFLOWS ww
where dwe.workflow_id = ww.workflow_id
  and ww.workflow_name not like '%(REFERENCE)%'
  -- and dwe.audit_flag != 'Y'
  -- and ww.enabled_flag = 'Y'
order by dwe.audit_flag, ww.workflow_name

-- If the deleted package was copied from another package the source_package_id field contains the original package number.
-- If you are using projects you might want to add ddp.project_code to get the project that the package belonged to.

spool off

PACKAGE_NUMBER Copied From Package  DELETION_DATE         Deleted By     DESCRIPTION                  WORKFLOW_NAME  STATUS_CODE
30009                        30001  9/26/2008 9:29:55 AM  David Goodall  Test audit deleted packages  Inventory      NEW
30303                               10/2/2008 9:50:39 AM  Peter Stewart                               Patches        IN_PROGRESS
...

To extract the output, click on the [Open as Text] button.

This opens a text window from which you can copy and paste.

Download

kintanascripts.zip (64 Kb)