Kintana Script : Audit Deleted Packages

Purpose

One of the many surprising things disclosed by the Deploy Metrics tool 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_audit_set_for_workflows_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_audit_trail_yyyymmdd.txt
set linesize 1000

select ddp.package_number,
       ddp.source_package_id "Copied From Package",
       ddp.deletion_date,
       nu.first_name || ' ' || nu.last_name "Deleted By",
       ddp.description,
       ww.workflow_name,
       ddp.status_code
from KDLV_DELETED_PACKAGES ddp,
     KNTA_USERS nu,
     KWFL_WORKFLOWS ww
where ddp.deleted_by = nu.user_id
  and ddp.workflow_id = ww.workflow_id
order by ddp.deletion_date, ddp.package_number

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.

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. I'm not quite sure why you would want to track back to the original line but it could be done.

Locating the workflow the deleted package-line was associated with is not as easy as it might bave been, as HP omitted to put a WORKFLOW_ID field in the KDLV_DELETED_PKG_LINES table.

If the package still exists, then the workflow can be obtained by making an association via the KDLV_PACKAGES table. If the package has been deleted the association must be made using the KDLV_DELETED_PACKAGES table.

Refining the sql to do this, is, as they say, left as an exercise for the reader!

Thanks to Andrea Balluff who pointed out that the Script #2 sql was just a copy 'n paste of Script #1's sql - an error now fixed!

Download

kintanascripts.zip (64 Kb)