Suppose one fine day, you feel like checking the user login history of your Clarity application. Where will you go to get the list of users who had logged into Clarity, along with their login dates and the number of times each user had logged-in? CA PPM, traditionally stores session information in two database tables but each has its own disadvantages.
Traditional Session Tables
- CMN_SESSIONS --- stores information of ACTIVE authenticated user sessions only. Moreover the entries get automatically removed either when the user logs out or when the session expires and is cleared by the session cleanup job.
- CMN_SESSION_AUDITS --- keeps a record of when all authenticated user sessions begin and end. But there is no way to differentiate between a session created by a real user login and a session created by the Clarity background (BG) service on behalf of the user who runs or schedules a job/process.
LOG_SESSIONS: The New Session Table from CA PPM v14.1
This new LOG_SESSIONS table contains the session created date, the session token and the logged-in user id. The same Clarity 14.1 version also introduced other LOG_XXXXX tables too. We only need to set them up properly which is given in detail under "Setting Up The Log Tables" in the article Most Viewed Portlets In Clarity PPM.
NOTE: The only additional step you need to do here is to make sure the parameter 'Session token retention in days' in Delete Log Analysis Data job is set appropriately. As is evident from the parameter name, the Clarity login history details you get when you use the below query is only for the number of days you set for this parameter in this scheduled job. Set both the parameters (Log retention in days and Session token retention in days) in Delete Log Analysis Data job to a higher value if you desire longer login history. This will result in adequate data to be present in the LOG_DETAILS and LOG_SESSIONS tables which are used by the following SQL query.
Advantages of LOG_SESSIONS table
- Entries remain in the table even after the user logs out of Clarity PPM
- By coupling it with the LOG_DETAILS table, we can differentiate whether it's a BG session or a real user session
Clarity User Login History Query
csu.first_name||' '||csu.last_name Full_Name,
csu.id = ls.user_id
AND ls.token IN (SELECT distinct session_cookie FROM log_details) ----- To exclude all the BG sessions
GROUP BY csu.user_name, csu.first_name||' '||csu.last_name, TRUNC(ls.created_date)
ORDER BY TRUNC(ls.created_date) DESC, COUNT(*) DESC
Feel free to edit the above Oracle query as per your needs. A grid portlet with the above result fields can be easily made by changing this SQL query to NSQL.
NB: The most important task here is to setup the log tables by properly scheduling both the jobs i.e. Tomcat access log import/analyze job and Delete Log Analysis Data job. Their job schedule and parameter values determine how much login history is available to us.