Managing user profiles on IBM i can be an onerous task. For those with larger systems user profiles can run into the thousands. As the person responsible for managing this crucial element of the system doing this through the green screen and reports sees you reading through the report looking for the exceptions to whatever it is you are looking for.

For example, you need to know when people leave so you can disable their profile so an easy method of checking who hasn’t signed on for the past xxx days could highlight user profiles which haven’t been used for xxx days. Armed with this information you could ask HR if these users are still on the payroll and if not disable the relevant user profiles.

These days, remote access is much more common than it used to be so timely knowledge of your user profiles is more important than ever. Using the magic given to us in the IBM Access Client Solutions Services you can obtain focused information in a fraction of the time it used to take just by running an SQL script.

For example, which user profiles have not signed onto the system for the last xx days?

Here’s the SQL to query the system

ProTip, there is a copy of the SQL statement in a format you can copy at the bottom of this article, I’ve used an image here because it’s easier to read.

What this SQL statement requests is where the previous sign on is less than 120 days ago and is enabled and isn’t an IBM user profile starting with a Q. Note the current_date – xxx days element of the SQL, this makes date calculations really easy.

When the results are displayed it is then easy to click on File and Save results as an Excel spreadsheet and change the headers to suit. Change the file name to save it to the location and name you would like.

Followed by

Clicking Yes opens up Excel where you can amend the document. I like to give the columns more meaningful names and hide any columns which are not relevant.

This can then be saved as part of your system documentation and can be very useful in impressing IT auditors who come asking questions about how you manage your user access to the system.

My second example turns things around and this SQL statement asks the question Who has signed onto the system in the last month.

So in this example the results will include users whose previous sign on date is greater than the current date – 1 month.

Now you might be thinking why would I ever want to do that. Well, I can think of two examples when this might be useful to you. The first is on January 4^th 2021 when I might like to know who has been on the system during the Christmas holiday (change 1 month to 12 days) and the second might be if you have an HA system and want to know who has logged onto that Server. Having got you thinking I’m sure you can easily come up with other examples of your own.

You don’t need to know SQL to get started using it. In the IBM ACS Client there are literally 100’s of free examples available to you to use, modify and save in your own directory. These are making the lives of IBM i admins so much easier. If you haven’t yet ventured into this are of ACS I recommend you make a New Year’s resolution to do so. Happy New Year!

i-UG goes Hybrid

Our last User Group Meeting was in December at the Mount Hotel, Wolverhampton and was a Hybrid event with a combination of Zoom meeting for remote attendees and a live audience at the Mount Hotel in Wolverhampton involving cameras, sound equipment and technical wizardry to send the vision and audio feed the right way at the right time! We had good feedback doing it this way, thank you to those who commented. Sadly, our usual spring venues of Rochdale, closely followed by London are not available to us this year so we will be doing it all again on Thursday 18^th March with a live audience and remote attendance via Zoom at the Mount Hotel Wolverhampton. We hope to see you there. For more details check out  www.i-ug.co.uk

Sample SQL

Examples of the SQL used in this article are below. Feel free to copy and use as you with. If you have any suggestions on how to improve it please add it back as a comment to this article:

SELECT AUTHORIZATION_NAME, TEXT_DESCRIPTION, PREVIOUS_SIGNON, SIGN_ON_ATTEMPTS_NOT_VALID, STATUS, PASSWORD_CHANGE_DATE, USER_CLASS_NAME
FROM QSYS2.USER_INFO
WHERE Previous_signon <  (CURRENT_DATE – 120 DAYS) and STATUS = ‘*ENABLED’ and NO_PASSWORD_INDICATOR = ‘NO’ and AUTHORIZATION_NAME NOT LIKE ‘Q%’
ORDER BY PREVIOUS_SIGNON ;

SELECT AUTHORIZATION_NAME, TEXT_DESCRIPTION, PREVIOUS_SIGNON,
SIGN_ON_ATTEMPTS_NOT_VALID, STATUS, PASSWORD_CHANGE_DATE, USER_CLASS_NAME
FROM QSYS2.USER_INFO
WHERE Previous_signon > (CURRENT_DATE – 1 MONTH) and STATUS = ‘*ENABLED’
and NO_PASSWORD_INDICATOR = ‘NO’ and AUTHORIZATION_NAME NOT LIKE ‘Q%’
ORDER BY PREVIOUS_SIGNON ;