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:

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 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:

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.

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 😉