Service Desk Practitioners Forum
cancel

How to report user's license usage

SOLVED
Go to solution
Highlighted
Jan Pavelka
Outstanding Contributor.

How to report user's license usage

Hi,

I need to report usage of our named and concurrent licenses in a period of time. I know that current sessions are stored in rep_sessions table and I can write a short script periodically querying this table.
On the other hand we use Enhanced Audit for user's Log in and Log out so I was assuming to query ifc_auditlogs.
Both ways are definitely possible. Some of you had to face the same issue so I do not want to re-invent the wheel.

What are your best practices to report this type of information?

I appreciate any hints or advice.
Thank you
Jan
8 REPLIES
Andrey Deryugin
Outstanding Contributor.
Solution

Re: How to report user's license usage

Hi Jan,

One of our customers ask for that too and I make a trigger+extra_table solution for that.

CREATE TABLE "rep_sessions_history"
( sh_ses_oid NUMBER(18),
sh_login_date DATE,
sh_logout_date DATE NULL,
sh_login_name VARCHAR2(32),
sh_server_ip VARCHAR2(64),
sh_client_ip VARCHAR2(64),
sh_session_count NUMBER(3));

#-------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "modify_sessions_history"
AFTER INSERT OR DELETE ON "rep_sessions"
FOR EACH ROW

BEGIN

IF INSERTING THEN
INSERT INTO rep_sessions_history
VALUES(:new.ses_oid, SYSDATE, NULL,
(SELECT acc_loginname FROM rep_accounts WHERE acc_oid = :new.ses_acc_oid),
:new.ses_server_ipaddress, :new.ses_client_ipaddress, NULL);
ELSIF DELETING THEN
UPDATE rep_sessions_history
SET sh_logout_date = SYSDATE
WHERE sh_ses_oid = :old.ses_oid;
END IF;

END;

After you implement this solution you can make any historical reports you want using "rep_sessions_history" table.

Regards,
Andrey
Jan Pavelka
Outstanding Contributor.

Re: How to report user's license usage

Hi Andrey,

it looks really good and saves me a lot of time.

Thanks
Jan
Eric P_2
Outstanding Contributor.

Re: How to report user's license usage

i'm interested with this script.

but where how do i have to put the trigger ? any guide to do this customization?

(I'm new for scripting in SD)

Thanks,
EP
Andrey Deryugin
Outstanding Contributor.

Re: How to report user's license usage

Hi Jan,

It's not a SD script, but Oracle trigger.

Look this URL for Oracle Trigger's HOWTO - http://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html#basic trigger syntax

Regards,
Andrey
Andrey Deryugin
Outstanding Contributor.

Re: How to report user's license usage

Sorry, meant Eric :)

Andrey
Jan Pavelka
Outstanding Contributor.

Re: How to report user's license usage

Hi guys,

you should consider the threat of unsupported SD database. HP denies to support a DB with any customer's intervention (including such easy thing as trigger). Unfortunately :o(

BR
Jan
Guenther Schere
Honored Contributor.

Re: How to report user's license usage

Hello license reporter,

what a dilemma.
To forecast the needed amount of user licenses I have to know the usage of SD.

As I know the analysed Data can't give this information and I'm not lucky to observe the usage of licenses every minute within the admin-console.
So the database trigger would be a hopeful tool but on the other hand I have a bad feeling to change the DB (like Jan already mentioned).

I've tried to translate the trigger into SQL.
I'm still testing this (more simple) trigger:
CREATE TRIGGER modify_sessions_history ON rep_sessions AFTER INSERT, DELETE
AS
INSERT INTO rep_sessions_history SELECT getdate(), (select count(*) from rep_sessions)


@Jan: Do you know why the trigger can't detect deleted rows because of "session timeout" ?


Thank you
Guenther


Christian Nilss
Super Contributor.

Re: How to report user's license usage

Here you have a correct formatted SQL trigger for enable licenses usage...

CREATE TRIGGER "modify_sessions_history" ON "REP_SESSIONS"
AFTER INSERT, DELETE
AS

declare @_ses_oid numeric,
@_login_date datetime,
@_logout_date datetime,
@_server_ip nvarchar(64),
@_client_ip nvarchar(64),
@_acc_oid nvarchar(32),
@_logon_time numeric,
@_is_con char(1),
@_acc_type nvarchar(16)



IF EXISTS(select ses_oid from deleted)
begin
update rep_sessions_history
set sh_logout_date = getdate()
from deleted
where rep_sessions_history.sh_ses_oid = deleted.ses_oid

update rep_sessions_history
set sh_logon_time = datediff(minute, sh_login_date, sh_logout_date)
from deleted
where rep_sessions_history.sh_ses_oid = deleted.ses_oid

end
IF EXISTS(select ses_oid from inserted)
begin
select @_ses_oid = ses_oid, @_client_ip = ses_client_ipaddress, @_server_ip = ses_server_ipaddress, @_acc_oid = ses_acc_oid, @_is_con = ses_isconcurrent
from inserted
if exists(select @_acc_oid where @_acc_oid != '1')
begin

SELECT @_acc_type = rep_accounts.acc_subtype
FROM ITSM_PERSONS inner join rep_accounts ON ITSM_PERSONS.PER_ACC_OID = rep_accounts.acc_oid
where rep_accounts.acc_oid = @_acc_oid

insert into rep_sessions_history(sh_ses_oid, sh_login_date, sh_client_ip, sh_server_ip, sh_acc_oid, sh_logon_time, sh_is_con, sh_acc_type)
values(@_ses_oid, getdate(), @_client_ip, @_server_ip, @_acc_oid, 0, @_is_con, @_acc_type)
end
end

hope this helps!!!

regards
Christian