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?
#------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER "modify_sessions_history" AFTER INSERT OR DELETE ON "rep_sessions" FOR EACH ROW
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;
After you implement this solution you can make any historical reports you want using "rep_sessions_history" table.
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" ?
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