Kintana Script : List Environments NOT In Use By Workflows

Purpose

This script will list all the Environment_Names (Source and Target Servers) NOT in use by workflows.

Run it periodically to help you keep the environment table trimmed down by removing obsolete entries

Tested

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

Usage

Paste the following script into SQL Runner

Omit the comment unless you're using a PL-SQL editor.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.

-- List Environments NOT In Use By Workflows

select ww.workflow_id, ww.workflow_name,
       wws.workflow_step_id, wws.step_name,
       ee.environment_id, ee.environment_name, ee.description, ee.enabled_flag
from   KENV_ENVIRONMENTS ee, KWFL_WORKFLOWS ww, KWFL_WORKFLOW_STEPS wws
where  ee.environment_id not in
       ( select source_environment_id from KWFL_WORKFLOW_STEPS )
       and
       ee.environment_id not in
       ( select dest_environment_id from KWFL_WORKFLOW_STEPS )
order by ee.environment_name

The script may take some time to run as it is (we hope) computing an empty result set. Be patient.

WL_ID WORKFLOW_NAME  WF_SP  STEP_NAME        ENV_ID ENV_NAME    DESCRIPTION            E_FLG
----- -------------- -----  ---------------- ------ ----------- ----------------------------
30123 ParseGen       30341  Unzip            20043  DEV DEV_PR4 DEV Application server Y
30644 Dataplot_Old   31392  XSLT translation 20043  DEV DEV_DP3 DEV Application server Y

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 (79 Kb)