Is there a way to download the users and their last login date/time into a file similar to what you see on the security screen (WebFocus 9.1.1)

[Erin Trotter]

We need to be able to check a user's last login into the system and disable any user accounts that have not logged in for the past 90 days, but we need to automate this process, so we need a way to get the list of users and their last logon date/time.  The resource analyzer does not seem to have a way to schedule something like this and seems to only be on demand.

[John Lewis]

If you have a database connection to your WebFOCUS repository, there is a table named UOA_USERS that you can create a synonym for and query to get user information including their last login date and time.  It also holds the status of the user (active/inactive) that you should be able to change to disable an account.

[David Beagan]

Thanks John. I just tried it -- had to create the metadata first -- and it worked great. 

[Erin Trotter]

Thanks John!  I was also able to connect and get the data.  This helps a bunch!

[Twanette Jurd]

Hi Erin,

Your initial request was to "check a user's last login into the system and disable any user accounts that have not logged in for the past 90 days".

A question: have you found the solution to disable any user accounts ?

[Clayton Peacock]

Hi @Erin Trotter

Why not use the WebFOCUS client adapter instead of going directly to the database? 

The adapter will generate some sample queries for you to get up and running very quickly and you can add/update/delete users/content etc and interact with the client repository - this is the solution you are requesting.

We automated this process, ReportCaster job runs daily to get all the users that haven't logged in for 90 days(GET_USERS) we then use this list to loop through and update the users status'(ADD_USER).

Below should get you started:

DEFINE FILE GET_USERS
-* GMT+2 = 7200000
BOT_START/D14 = EDIT(LASTSIGNIN) + 7200000;
BASE_DATE/YYMD = 19700101;
HBASE_DATE/HYYMDIA = HDTTM(BASE_DATE,8,'HYYMDIA');
LAST_SIGNIN/HYYMDS MISSING ON = IF LASTSIGNIN EQ '' THEN MISSING ELSE HADD(HBASE_DATE,'MILLISECONDS',BOT_START,8,'HYYMDS');
LAST_SIGNIN_YYMD/YYMD = HDATE(LAST_SIGNIN,'YYMD');
DAYS_SINCE_LOGIN/I9 MISSING ON = IF LAST_SIGNIN_YYMD EQ '' THEN MISSING ELSE DATEDIF(LAST_SIGNIN_YYMD, '&YYMD', 'D')
END

TABLE FILE GET_USERS                                       
PRINT GET_USERS.ITEM.NAME3        AS 'Userid'                                           
      GET_USERS.ITEM.DESCRIPTION1 AS 'User Description'                          
      GET_USERS.ITEM.EMAIL        AS 'Email Address'                                    
      GET_USERS.ITEM.NAME2        AS 'Status'
      LAST_SIGNIN                 AS 'Last Signin(DT)'
      LAST_SIGNIN_YYMD            AS 'Last Signin'
      DAYS_SINCE_LOGIN            AS 'Days since login'
BY HIGHEST DAYS_SINCE_LOGIN NOPRINT 

WHERE DAYS_SINCE_LOGIN GE 90;

END
-RUN

[Twanette Jurd]

That's what I was thinking too. Thanks @Clayton Peacock

[Clayton Peacock]

Think should do a tips and tricks post 🙂 ibi adapter is very powerful. 

[Clayton Peacock]

@Patrick Huebgen thank you - here is a how to connect to the WebFOCUS client repository