March 18

2 comments

How do I know if my IBM i Users are Expiring?

By NickLitten

March 18, 2024

IBM i, CLLE, SQL

Not dying users, you big silly!

Are your IBM i Users Passwords Expiring?

Every IBM i User Profile can be created with an automatic expiration date set against it. This forces regular password changes. Pretty standard stuff right?

This is rarely a problem for interactive users – who will see the change password screen:

How do i know if my ibm i users are expiring? 1

But, for service accounts (aka service profiles) suddenly receiving a password expired message can be problematic!

What is a service account? This is a term used in most IBM i Departments meaning those accounts that are only ever used by automated background services. Think of the user accounts as those that are used for webservices, or ftp transfers, background email jobs, etc

How do I know which IBM i Service Accounts are due to expire?

How do we find out how many IBM i User Profiles with passwords set to expire are rapidly approaching a reset?

Will that reset lock them out in the next 30 days?

Let’s build on the SQL we used in our previous blog to:

  • save the list of expiring user names
  • email them to someone to address them

eMail a list of IBM i User with password expiring in the next 30 days

This little SQL snippet will send the output of our SELECT statement and write it to an IFS File:

begin

    call qsys2.ifs_write(
        path_name => '/tmp/expiring_users.txt',
        line => '',
        overwrite => 'REPLACE',
        end_of_line => 'NONE'
    );

    for select user_name as expiring_user_name
        from qsys2.user_info
        where pwdexpdat between current date and current date + 30 days
        do
            call qsys2.ifs_write(
                path_name => '/tmp/expiring_users.txt',
                line => expiring_user_name
            );
    end for;

end;

This will give us a simple list of all user profiles that have passwords set to expire in the next 30 days:

How do i know if my ibm i users are expiring? 2

How about we flesh this out a little and add some extra information:

-- this will write a list of user accounts to the IFS location in path_name
begin
-- clear the file
    call qsys2.ifs_write(
        path_name => '/tmp/expiring_users.txt',
        line => '',
        overwrite => 'REPLACE',
        end_of_line => 'NONE'
    );
-- write each expiring user to the output file
    for select
        user_name || ' - ' || coalesce(text_description, 'Blank') || ' - ' || + date_password_expires as expiring_user_information
        from qsys2.user_info
        where date_password_expires between current date and current date + 30 days
        do
            call qsys2.ifs_write(
                path_name => '/tmp/expiring_users.txt',
                line => expiring_user_information
            );
    end for;
end;

This adds some extra information like a user description and expiry date:

How do i know if my ibm i users are expiring? 3

Note: the coalesce condition will check for *NULL (ie: blank) values in the user description.

What is the easiest way to automate this?

Copy this little SQL script into an IFS file (or you can use a QSQLSRC member if that is your old-skool preference):

-- this will write a list of user accounts to the IFS location in path_name
begin
-- clear the file
    call qsys2.ifs_write(
        path_name => '/tmp/expiring_users.txt',
        line => '',
        overwrite => 'REPLACE',
        end_of_line => 'NONE'
    );
-- write each expiring user to the output file
    for select
        user_name || ' - ' || coalesce(text_description, 'Blank') || ' - ' || + date_password_expires as expiring_user_information
        from qsys2.user_info
        where date_password_expires between current date and current date + 30 days
        do
            call qsys2.ifs_write(
                path_name => '/tmp/expiring_users.txt',
                line => expiring_user_information
            );
    end for;
end;

-- email and cleanup the file from the IFS location
CL: SNDSMTPEMM RCP((myname@somewhere.com)) SUBJECT('Expiring IBM i Users')
               ATTACH(('/tmp/expiring_users.txt' *PLAIN *TXT))        

CL: RMVLNK OBJLNK('/tmp/expiring_users.txt')

This IBM i SQL script will generate the list of users into a file at “/tmp/expiring_users.txt’ containing the username, description and timestamp that this profile is set to expire.

Then we can use the SNDSMTPEMM command to email that text file to the address of your choice, followed by a little RMVLNK cleanup.

List_expiring_users_and_email_using_runsqlstm. Sql

Run this using:

RUNSQLSTM SRCSTMF('/home/nicklitten/list_expiring_users.sql') COMMIT(NONE) NAMING(SQL)

And there you have it. It’s quick and dirty but it does the trick 😉

  • Ha … I read the headline and thought this was about IBM i staff aging out, or ‘expiring’ not actual IBM i user profiles expiring!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >