P6 Session Audit Basics

Auditing in P6 is primarily an opt-in only function--you need to turn it on first. But one type of auditing is on-by-default: Session Auditing. Rows in the usession table are deleted by the application when the user logs off. The Usession Audit trigger captures an image of the usession table row during delete. The resulting rows in the usessaud table provide valuable perspective into application usage.

Managing usessaud

Although this audit is on-by-default, the regular clean-up routine will only keep the last five (5) days of data in the table. I recommend increasing this value to at least a month. Ideally, set this to be 90 days.

Update the setting database.cleanup.Usessaud.KeepInterval:

sql> exec settings_write_string('90d','database.cleanup.Usessaud','KeepInterval')
column setting format a50
select namespace ||'.'|| setting_name || '=' || setting_value setting 
from setting 
where namespace = 'database.cleanup.Usessaud'

SETTING
--------------------------------------------------
database.cleanup.Usessaud.KeepInterval=90d

Contents

The rows and columns in usessaud mirror those in usession, so it is important to understand exactly what is in usession. When you logon to any P6 application, one or more rows are inserted into the usession table. The primary purpose of the usession table is to track module/license usage (via the db_engine_type column). A row is inserted for each module the user could use during the session. A user logging into P6 EPPM, with module access to Project Management and Resource Management, will get two records in usession: WEB_PM and WEB_RM. While we would consider this a "single login", the usession contains two records.

When this user disconnects from the application, both records from usession are inserted into usessaud table. This is important to keep in mind for any queries on the usessaud table. It may be necessary to include a predicate for db_engine_type in your usessaud queries.

Examples

1. How many (unique) users connected on 9/23/2012?

select count(distinct user_name)
from usessaud
where trunc(login_date) = DATE'2012-09-23'
/

COUNT(DISTINCTUSER_NAME)
------------------------
                     113 

2. How many users connect to Professional Client on 9/25/2012?

select  count(distinct user_name)
from usessaud
where trunc(login_date) = DATE'2012-09-25'
and db_engine_type = 'PM'
/

COUNT(DISTINCTUSER_NAME)
------------------------
                      92
 

3. What was the longest login time?

select max(round((logout_date-login_date)*24,2)) hours
from usessaud
where login_date between DATE'2012-09-22' and DATE'2012-10-22'
/

     HOURS
----------
      13.5

4. Did the user "user257" connect to the application in September 2012?

select db_engine_type, login_date, logout_date
from usessaud
where user_name = 'user257'
and login_date between DATE'2012-09-01' and DATE'2012-09-30'
order by 1
/

DB_ENGINE_TYPE       LOGIN_DATE          LOGOUT_DATE
-------------------- ------------------- -------------------
PM                   2012-09-28 03:51:27 2012-09-28 15:43:02
PM                   2012-09-27 03:02:44 2012-09-27 11:30:23

These are just a few examples of the types of queries possible with usessaud. If you have your own usessaud query, or questions about the table contents, go ahead and post a comment.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

An insider view of the technology behind the Primavera product suite.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today