Clarity

Guidance required to write a query that shows the history for the past 30 days with approximate number of times or the approximate minutes each user has logged on.

cmn_sessions

cmn_session_audits

would be the main, as I know of, but still want to check if anyone has already created one

NJ

Ok, got this (thanks to TomConnery) in one of the threads (https://communities.ca.com/message/15791778#15791778)-

SELECT

LAST_NAME,

FIRST_NAME,

USER_NAME,

LAST_LOGGED_IN_DATE

FROM niku.CMN_SEC_USERS

where LAST_LOGGED_IN_DATE >= DATEADD(day, -60, CURRENT_TIMESTAMP)

How to get this ? - "approximate number of times or the approximate minutes each user has logged on"

NJ

"number of times" - from the access logs on the server only I think (or implement an unsupported trigger on the user table to log updates to last_logged_in_date)

"minutes" - no idea, don't think you can get that from the log/access files even (you could spot actual manual logouts, but not expired sessions)

When you say

"number of times or the approximate minutes each user has logged on"

in Clarity that relates to sessions. You could query sessions.

Depending on  how often you clean your sessions you are likely to get the results for less than 30 days. So you could need to store the results and query the results from the storage.

I've posted before something like

-- MS SQL

SELECT

U_ID,

Start_time,

Last_updated_time,

hrs,

mins,

secs,

Name,

user_name

FROM

(select  top 100

convert(varchar,cmn_sessions.id)  U_ID,

convert(varchar,cmn_session_audits.session_start_date, 108) Start_time,

cmn_session_audits.session_start_date starttime,

convert(varchar,cmn_sessions.last_updated_date ,108) Last_updated_time,

convert(varchar,(datepart(hour,(getdate()-cmn_sessions.created_date)))) hrs,

convert(varchar,(datepart(minute,(getdate()-cmn_sessions.created_date)))) mins,

convert(varchar,(datepart(second,(getdate()-cmn_sessions.created_date)))) secs,

(cmn_sec_users.last_name+',  '+cmn_sec_users.first_name) Name,

cmn_sec_users.user_name,

--convert(varchar,cmn_sessions.user_id) user_id ,

CASE

WHEN starter_user.id = 1 THEN 3

WHEN starter_user.id = 8 THEN 5

ELSE 0

END

SF

from

cmn_sessions,

cmn_sec_users,

cmn_sec_users starter_user,

cmn_session_audits

where

cmn_sec_users.id = cmn_sessions.user_id

and cmn_session_audits.session_id = cmn_sessions.id

and cmn_session_audits.user_id = starter_user.id

--and (starter_user.id <> 1 and  starter_user.id<> 8)

Order by SF, cmn_session_audits.session_start_date desc

UNION ALL

SELECT

'-----' U_ID,

'-----' Start_time,

getdate() starttime,

'-----' Last_updated_time,

'-----' hrs,

'-----' mins,

'-----' secs,

'-----' Name,

'-----' user_name,

2 SF

UNION ALL

SELECT

'-----' U_ID,

'-----' Start_time,

getdate() starttime,

'-----' Last_updated_time,

'-----' hrs,

'-----' mins,

'-----' secs,

'-----' Name,

'-----' user_name,

4 SF) AS RAW

Order by SF, starttime desc

for the sessions and something like

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:SESSIONS:S.Name:Name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:SESSIONS:S.Userid:Userid@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:SESSIONS:S.Number_sessions:Number_sessions@                          

FROM

(select

cmn_sec_users.last_name +','+ cmn_sec_users.first_name Name,

cmn_sec_users.user_name Userid,

Count(user_id) Number_sessions

from

cmn_sessions,

cmn_sec_users

where

cmn_sec_users.id = cmn_sessions.user_id

Group by(cmn_sec_users.last_name +','+ cmn_sec_users.first_name), cmn_sec_users.user_name)s

WHERE      @FILTER@

for the session count and chrisfol has posted

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:S.id:SessionID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.id:UserID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.user_name:UserName@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.last_name:LastName@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.first_name:FirstName@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.created_date:LoggedInSince@

FROM CMN_SESSIONS S,CMN_SEC_USERS U

WHERE U.ID=S.USER_ID

AND S.ID= (SELECT MAX(ID) FROM CMN_SESSIONS WHERE USER_ID = S.USER_ID)

AND @FILTER@

Running the below query -

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:SESSIONS:S.Name:Name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:SESSIONS:S.Userid:Userid@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:SESSIONS:S.Number_sessions:Number_sessions@                          

FROM

(select

cmn_sec_users.last_name +','+ cmn_sec_users.first_name Name,

cmn_sec_users.user_name Userid,

Count(user_id) Number_sessions

from

cmn_sessions,

cmn_sec_users

where

cmn_sec_users.id = cmn_sessions.user_id

Group by(cmn_sec_users.last_name +','+ cmn_sec_users.first_name), cmn_sec_users.user_name)s

WHERE      @FILTER@

results in this -

NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00923: FROM keyword not found where expected SQL Text: SELECT S.Name Name, S.Userid Userid, S.Number_sessions Number_sessions FROM (select cmn_sec_users.last_name ',' cmn_sec_users.first_name Name, cmn_sec_users.user_name Userid, Count(user_id) Number_sessions from cmn_sessions, cmn_sec_users where cmn_sec_users.id = cmn_sessions.user_id Group by(cmn_sec_users.last_name ',' cmn_sec_users.first_name), cmn_sec_users.user_name)s WHERE 1=? and 1=1 and 2 = 2

Maybe my eyes are giving me off

NJ

'+' for string concatenation is MSSQL syntax, you are clearly on Oracle, use '||'.

Got that to work

NJ

"Depending on  how often you clean your sessions you are likely to get the results for less than 30 days. So you could need to store the results and query the results from the storage." - It runs everyday @ 0400 ET

NJ

Hi Navdeep,

If you are on Clarity v14.1 or higher, you can also make use of the new LOG_SESSIONS and LOG_DETAILS tables to retrieve this information.

You can find the query at Clarity PPM Login History

Regards,

Georgy