IBM i User Information is easy with SQL USER_INFO
If you’re working with IBM i and need to retrieve information about user profiles, you can use the USER_INFO view. This view provides details about various aspects of user profiles.
Here are some key columns from the USER_INFO view:
- AUTHORIZATION_NAME: The user profile name.
- PREVIOUS_SIGNON: The date and time the user last signed on (or null if the profile has never been used for sign-on).
- SIGN_ON_ATTEMPTS_NOT_VALID: The number of invalid sign-on attempts since the last successful sign-on.
- STATUS: The status of the user profile (either DISABLED or ENABLED).
- NETSERVER_DISABLED: Indicates whether the user profile is disabled for IBM i NetServer use (YES or NO).
- PASSWORD_CHANGE_DATE: The date the user’s password was last changed.
- NO_PASSWORD_INDICATOR: Indicates whether the password in the user profile is set to NONE.
- PASSWORD_LEVEL_0_1: Indicates whether the user profile has a password that can be used for a system at QPWDLVL 0 or 1.
- PASSWORD_LEVEL_2_3: Indicates whether the user profile has a password that can be used for a system at QPWDLVL 2 or 3.
- PASSWORD_EXPIRATION_INTERVAL: The number of days the user’s password can remain active before it must be changed.
- DATE_PASSWORD_EXPIRES: The date the user’s password expires (null if the password won’t expire).
- DAYS_UNTIL_PASSWORD_EXPIRES: The number of days until the password expires (null if it won’t expire within the warning period).
- SET_PASSWORD_TO_EXPIRE: Indicates whether the user’s password is set to expire (YES or NO).
- USER_CLASS_NAME: The user’s class name (e.g., PGMR, SECADM, or SECOFR)¹.
You can query this view using SQL to retrieve the desired information.
Read user_info to get IBM i Profile Information Easily
One of my clients recently asked me to create a spreadsheet of users with passwords that will expire within 30 days.
We can do this easily using SQL to pull the information.
Let’s write a SQL statement that says: “show me all users information for users where the expiry date is set to hit within the next 30 days. Show me all results in user profile alphabetic sequence.”
In SQL it’s easy, and it looks like this:
select *
from qsys2.user_info
where pwdexpdat between current date and current date + 30 days
order by user_nameHere it is in IBM i ACS, showing a single result for a test user profile (un-imaginatively called TEST) which I created with a password set to expire in a few days:
Now, you can easily download this as a spreadsheet by clicking the little download button in the bottom right corner.
Which made me think “How can we automate this to run weekly… and email the results automatically?”
Next blog, to answer that question:
To download to Excel:
– Enable Save Results
– run the SQL statement
– right-click the data and select Save Results
The down-arrow you are referring to is to retrieve all of the data instead of just the first 100 rows.