Concurrent License Usage

As part of an effort to monitor maximum concurrent license usage, a colleague of mine wrote a SQL script to query ACTIVE_SESSION_USER joined to T_LICENCEDETAILS. He then created a batch script to run the the SQL script with SQLplus and another batch script to concatenate the results. The results were dumped to a .csv file every five minutes. Once the sample data had been gathered, he pulled it into Excel for analysis and created this nifty chart:


The SQL used to gather the data behind the chart is:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDDHH24MISS') dcol from dual;
set echo off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ''
set trimspool on
spool c:\temp\concurrency\&mydate._output.csv
select to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')||','||b.username||','||b.peoplefullname||','||c.userlicence
from tridata9.active_session_user a, tridata9.t_myprofile b, tridata9.ibs_spec_assignments isa, tridata9.t_licencedetails c
where a.user_id = b.spec_id
and b.spec_id = isa.spec_id
and isa.ass_spec_id = c.spec_id;
spool off
exit

Thanks Chris for your contribution!