Clarity user session login activity detail for last month

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Need a report to show currently active (or non active) user login session activity for the last month (minimum).

Environment

Clarity 16.0

Resolution

Sample SQL query logic to query CMN_SEC_USERS and CMN_SESSION_AUDITS in order to achieve use case. 

SELECT a.user_id,
 a.session_start_date,
 a.session_end_date,
 u.user_name
FROM cmn_session_audits a,
 cmn_sec_users u
WHERE   a.user_id = u.id AND
  a.session_start_date >= getDate() - 30

The same can be converted to NSQL portlet using the following equivalent NSQL query;

select
@select:dim:user_def:implied:d0:q.id:id@,
@select:dim_prop:user_def:implied:d0:q.user_id:user_id@,
@select:dim_prop:user_def:implied:d0:q.session_start_date:start_date@,
@select:dim_prop:user_def:implied:d0:q.session_end_date:end_date@,
@select:dim_prop:user_def:implied:d0:q.user_name:user_name@
from(
SELECT u.id,
a.user_id,
a.session_start_date,
a.session_end_date,
u.user_name
FROM cmn_session_audits a,
cmn_sec_users u
WHERE a.user_id = u.id AND
a.session_start_date >= getDate() - 30
)q
where @filter@