If you would like to know who has logged into your TRIRIGA system within the past 30 days and when, here is a script you can run against the tririga schema. You can update this script to filter for specific users or filter specific users out (such as data integration users).
SELECT DISTINCT
user_credentials.user_account AS USERNAME,
session_history.start_time AS LOGIN
FROM
user_credentials, session_history
WHERE
user_credentials.user_id = session_history.user_id AND
session_history.start_time > (sysdate - 30)
UNION ALL
SELECT DISTINCT
user_credentials.user_account AS USERNAME,
active_session_user.start_time AS LOGIN
FROM
active_session_user, user_credentials
WHERE
user_credentials.user_id = active_session_user.user_id
ORDER BY 1, 2;
Subscribe to:
Post Comments (Atom)
4 comments:
Hello Rob,
is there a way to show concurrency through the session history table? Here are two queries that I'm using to look for this information:
select distinct (count(T3.usergroup)) as Total_Logins, T3.USERGROUP
from session_history as T1, T_MYPROFILE as T2, T_GROUPDETAILS as T3
where T1.USER_ID = T2.SPEC_ID
and T2.SPEC_ID = T3.PAR_SPEC_ID
and T1.USER_ID != '10943449'
and T3.USERGROUP not like 'Portfolio 2%'
and T1.START_TIME > '2010-07-01 00:00:00' and T1.START_TIME < '2010-07-21 00:00:00'
group by T3.usergroup
order by Total_Logins asc
OR
select T3.USERGROUP, T1.START_TIME, T1.END_TIME, T1.USER_ID, T2.trinametx
from session_history as T1, T_MYPROFILE as T2, T_GROUPDETAILS as T3
where T1.USER_ID = T2.SPEC_ID
and T2.SPEC_ID = T3.PAR_SPEC_ID
and T1.USER_ID != '10943449'
and T3.USERGROUP not like 'Portfolio 2%'
and T1.START_TIME > '2010-04-01 00:00:00'
order by T1.START_TIME asc
The userid 10943449 is our integration user and I filter out all Porfolio groups because of duplication.
thanks
Warren
Warren,
A while back, a coworker of mine was asked to do a similar task. He wrote a script to go out and query ACTIVE_SESSION_USER joined to T_LICENCEDETAILS every five minutes and dump it to a csv file. He used a batch script that runs the the sql script with sqlplus and another batch script to concatenate the results. He then dumped the output into Excel and slapped a pivot table on it to produce a chart of concurrent license usage.
The script he used 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
How is table cleaned up? How do you delete unnecessary data from the table? Is it an update script for cleanup agent or just delete from the table?
jennyB:
To delete data from a table, the record needs to be transitioned to the NULL state. The safest way to do this is through the app, but there is a way to do it via SQL. Once a record is in a NULL state, the cleanup agent will remove it. You do not want to directly delete data from tables.
Post a Comment