Kintana Script : List Active Licenses

Purpose

The query dialog will give you an immediate on-screen list of users with active licences
for each product.

There doesn't seem to be a standard report to get these lists in hard copy form.

This little script fills in a gap.

Tested

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

Usage

Paste the following script into SQL Runner

Uncomment and change 'nup.product_id = (n,n,...) to restrict the list to specific products.

Omit the comment, spool, and linesize statements unless you're using a PL-SQL editor.
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.

-- List Users with Active Licenses
spool c:\active_deliver_users_yyyymmdd.txt
set linesize 1000

select nu.first_name "First Name", nu.last_name "Last Name",
       np.product_name "Product Name", np.license_name "License Name", np.product_key "License Id"
from KNTA_USERS nu, KNTA_USER_PRODUCTS nup, KNTA_PRODUCTS np
where nu.end_date is null

-- and nup.product_id in ( 1,6 )    /* 1=Deliver Power 6=Deliver Standard */

and nu.user_id = nup.user_id
and np.product_id = nup.product_id
order by np.product_name, nu.first_name

spool off

First Name  Last Name  Product Name               License Name      License_Id
----------  ---------  -------------------------  ------------      ----------
gandalf     grayhame   Kintana Create Workbench   Create Power            2
meriadoc    brandybuck Kintana Dashboard          Dashboard Standard      9
frodo       baggins    Kintana Deliver Workbench  Deliver Power           1
sam         gamgee     Kintana Deliver            Deliver Standard        6
bilbo       baggins    Kintana Drive Workbench    Drive Power             4

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)