Kintana Script : Unused and Underused Licenses

Purpose

Shows,for enabled users,when they last logged on, the number of days since they last logged on, and the types of licences they are using.

If you have Sharepoint I suggest you set up a reminder to run this audit periodically.

First identify users who have left and set the end date on their user records.

Next identify the 'occasional' users. I strongly suggest that you decide what constitutes an 'occasional' user' (for example 3 months or 6 months without using the system) and set the end dates on those user accounts.

When and if they need access again then you can remove the end date to re-enable their accounts.

By pro-actively managing the system this way you should be able to run the system without paying for more licenses than you really need

Multiply your per-seat license cost by the number of underused seats, and i believe that you will find that there is a very large payback for the small amount of time it takes to turn 'occasional' user accounts on or off.

To make this easy to manage going forward, i suggest you mark one of the user or contact record fields as 'occasional'. This makes it easy to run a Toad query for users with this string who are currently enabled or disabled.

Tested

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

Usage

Paste the script into Toad or SQL Runner

Author

Joe Bossalini (Jul 26, 2005).

select max(nla.creation_date)                    LAST_LOGIN,
       round(sysdate - max(nla.creation_date))   DAYS_FROM_LOGIN,
       np.license_name                           LICENSE_NAME,
       (nu.first_name || ' ' || nu.last_name)    FULL_NAME,
       nu.username                               USERNAME,
       nu.user_id                                USER_ID,
       nu.creation_date                          CREATION_DATE
from KNTA_USERS nu,
     KNTA_LOGON_ATTEMPTS nla,
     KNTA_USER_PRODUCTS nup,
     KNTA_PRODUCTS np
where nup.user_id = nu.user_id
  and (( nu.end_date is NULL) OR ( nu.end_date > sysdate ) )
  and np.product_id = nup.product_id
  and nu.user_id = nla.user_id (+)
  and (np.license_name = 'Change Management Power'
       or
       np.license_name like '%Demand Management%')
group by nu.user_id, nu.username, nu.first_name, nu.last_name,
         np.license_name, nu.creation_date
order by 2 desc

Download

KintanaScripts.zip (64 Kb)