Shawn_Moore

CA Clarity Tuesday Tip: How to determine inactive users with bonus NSQL!

Discussion created by Shawn_Moore Employee on Aug 15, 2011
Latest reply on Mar 3, 2013 by another_martink
Title: CA Clarity Tuesday Tip: How to determine inactive users with bonus NSQL!

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 08/16/2011

Today we will look at a simple query for determining what users are not actively using Clarity. This method works for 12.0.6 gen 05 and above, as well as 12.1.0 and above.

The cmn_sec_users table has a field called, last_logged_in_date, which represents the date in which the user had last logged in. This field was consistently populated in the versions mentioned above.

To determine the set of users who haven't logged in for n number of days you can compare the last_logged_in_date to the current date of the server.

i.e. In Oracle you could run the following to find users who haven't logged into the system within the last 180 days, using the following:

select user_name, last_logged_in_date
from cmn_sec_users where last_logged_in_date < sysdate - 180

For MS SQL Server, you would use the getDate() function.

i.e.

select user_name, last_logged_in_date
from cmn_sec_users where last_logged_in_date < getDate() - 180

Lastly, to put this into a portlet, you could create an NSQL query that takes in an incoming parameter (used on your filter section).

Here are the NSQL's:

MS SQL:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:USER:user_name:USER_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:USER:last_logged_in_date:LAST_LOGGED_IN_DATE@
FROM cmn_sec_users
WHERE last_logged_in_date < getdate() - @WHERE:PARAM:USER_DEF:INTEGER:DAYS_PARAM@
AND @FILTER@

Oracle:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:USER:user_name:USER_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:USER:last_logged_in_date:LAST_LOGGED_IN_DATE@
FROM cmn_sec_users
WHERE last_logged_in_date < sysdate - @WHERE:PARAM:USER_DEF:INTEGER:DAYS_PARAM@
AND @FILTER@


Enjoy!

-shawn

Outcomes