Getting data for my auditors is simple using SQL

It is that time of the year when the auditors start asking me for system information about the IBM i I am responsible for. The reports they want from me today are:

1. Users who have not signed onto the IBM i for over a year.
2. Users who have a user profile, but have never signed onto the IBM i.

It is also a good opportunity to show how some of the things I have written about in the past come together to make my job a lot easier.

A colleague, who is responsible for a different IBM i partition, asked me if there was an easy way to get this information?

Because at present he...

1. Uses the Print User Profile command, PRTUSRPRF, to generate a spool file of the user profiles' information.
2. Copies the spool file to a physical file.
3. Calls a RPG program that reads the file of the spool file. This program finds the records relevant to what he wants and writes that data to an output file.
4. Transfers the output to his PC using Client Access upload, and converts it into an Excel as part of the transfer process.

I was shocked to find he does this manually one step at a time!

Did I have a "better" approach?

Yes! I have a program that extracts the relevant user profile information into two output files, and then emails them to me.

So let me dive into my CL program, by starting with the part that extracts the user profile information.

01 PGM 02 DLTF FILE(QTEMP/@*) 03 MONMSG MSGID(CPF0000) 04 RUNSQL SQL('CREATE TABLE QTEMP.@LASTYEAR AS + 05 (SELECT CAST(USER_NAME AS CHAR(10)) AS USER,+ 06 STATUS,+ 07 CAST(LASTUSED AS DATE) AS LAST_USED,+ 08 USRCLS,TEXT,+ 09 CAST(TIMESTAMP AS DATE) AS CREATED + 10 FROM QSYS2.USER_INFO + 11 WHERE STATUS = ''*ENABLED'' + 12 AND LASTUSED < CURRENT_TIMESTAMP - 1 YEAR) + 13 WITH DATA') + 14 COMMIT(*NC) 15 RUNSQL SQL('CREATE TABLE QTEMP.@NEVERUSED AS + 16 (SELECT CAST(USER_NAME AS CHAR(10)) AS USER,+ 17 STATUS,USRCLS,TEXT,+ 18 CAST(TIMESTAMP AS DATE) AS CREATED + 19 FROM QSYS2.USER_INFO + 20 WHERE STATUS = ''*ENABLED'' + 21 AND LASTUSED IS NULL) + 22 WITH DATA') + 23 COMMIT(*NC)

Line 2: I always put my temporary output files in QTEMP, and I start them with an at sign ( @ ) so that I can delete all of them with one delete file command.

Lines 4 – 14: I am using the Run SQL command, RUNSQL, to create a table/file using the Db2 for i View USER_INFO.

Line 5: I have converted the USER_NAME column to be ten characters, and used the AS to give it column name USER.

Line 6: STATUS contains with the profile is *ENABLED or *DISABLED.

Line 7: LASTUSED is a timestamp. I only need a date, therefore, I am using CAST to reformat it as a date column.

Line 8: I am including the users' class, USRCLS, and description, TEXT.

Line 9: I need the date the profile was created. TIMESTAMP is a timestamp of when the profile was created. I need to reformat it as a date using CAST.

Line 10: The user profile data is contained within the Db2 for i USER_INFO View.

Line 11: I only want profiles that are enabled...

Line 12: And were last used less than year ago.

Line 13: If I forget WITH DATA I get an empty table.

Lines 15 – 23: This SQL statement is just about the same as the previous one. I don't bother to include the last used date as it would be null for all the rows/records. And on line 21 I only select rows/records where the last used is null, i.e. the profile has never been used to signon.

When this part of my program runs I have my two outfiles. The first, @LASTUSED, contains the list of profiles that have not signed on in over a year:

USER STATUS LAST_USED USRCLS TEXT AA******** *ENABLED 2016-08-17 *PGMR ******************** AB******** *ENABLED 2015-07-31 *USER ******************** AC******** *ENABLED 2013-06-18 *USER ******************** AD******** *ENABLED 2014-04-04 *USER - AE******** *ENABLED 2012-09-05 *USER ******************** CREATED 2016-08-17 2005-02-07 1997-04-15 2002-08-25 2012-09-05

The second file contains those profiles that have never been used:

USER STATUS USRCLS TEXT CREATED AF******** *ENABLED *USER ******************** 1999-10-14 AG******** *ENABLED *USER ******************** 2001-08-14 AH******** *ENABLED *USER ******************** 2005-11-04 AI******** *ENABLED *USER ******************** 2006-05-05 AJ******** *ENABLED *USER ******************** 2006-05-05

Note:  I have deliberately obscured the user profile and the text.

It takes two steps to send these files via email.

1. Copy the files to the IFS
2. Email the IFS files

24 RMVLNK OBJLNK('/MyFolder/last_year.csv') 25 MONMSG MSGID(CPF0000) 26 CPYTOIMPF FROMFILE(QTEMP/@LASTYEAR) + 27 TOSTMF('/MyFolder/last_year.csv') + 28 MBROPT(*REPLACE) STMFCCSID(*PCASCII) + 29 RCDDLM(*CRLF) + 30 ADDCOLNAM(*SQL) 31 RMVLNK OBJLNK('/MyFolder/never_used.csv') 32 MONMSG MSGID(CPF0000) 33 CPYTOIMPF FROMFILE(QTEMP/@NEVERUSED) + 34 TOSTMF('/MyFolder/never_used.csv') + 35 MBROPT(*REPLACE) STMFCCSID(*PCASCII) + 36 RCDDLM(*CRLF) + 37 ADDCOLNAM(*SQL) 38 DLTF FILE(QTEMP/@*)

Line 24: Before I copy the file I am going to delete it, if it exists, from the IFS folder of my choice, MyFolder.

Lines 26 – 30: I am using the Copy to Import File command, CPYTOIMPF, to copy the file from QTEMP to the IFS.

Line 27: This is the folder and file name I will be using for this file in the IFS. Notice that this starts with a forward slash ( / ).

Line 28: The Stream file CCSID parameter, STMFCCSID, must be *PCASCII so that the contents of the file is translated from the IBM i's CCSID to a PC compatible version.

Line 29: If I am creating an import file like this I need to use the carriage return and line feed at the end of every record.

Line 30: This parameter, ADDCOLNAME(*SQL), will copy the SQL column headings to the IFS file to be column headings in the CSV file.

Lines 31 – 37: Are the same as the previously described section, just for the other file.

Line 38: I no longer need the files in QTEMP, therefore, I am going to delete them.

Now to send the files from the IFS via email:

39 SNDSMTPEMM RCP(('simon@rpgpgm.com')) + 40 SUBJECT('User profile info for auditors') + 41 NOTE('User profile not used in year and never used') + 42 ATTACH(('MyFolder/never_used.csv' *PLAIN *TXT) + 43 ('MyFolder/last_year.csv' *PLAIN *TXT)) 44 RMVLNK OBJLNK('/MyFolder/last_year.csv') 45 RMVLNK OBJLNK('/MyFolder/never_used.csv') 46 ENDPGM

Lines 39 – 43: This is the command to send emails from your IBM i. And before you all think I have given you my email address, that is not my address.

Lines 42 and 43: This is where I attach the two files. Notice that here there is not a forward slash before the path name. I am sending my files as plain, *PLAIN, text, *TXT, so that they will be received as CSV (Comma Separated Variable length fields) files. I use CSVs a lot on emails as they are a lot smaller than Microsoft Excel files, and use up a lot less space on my email server.

Lines 44 and 45: These are just for clean up to delete the files I created in the IFS, as once I have sent the email I do not need them anymore.

I am not going to bother to show the CSV files attached to an email message, but I will show how the files look when I opened them in Excel.

last_used.csv

never_used.csv

Click on image to see larger version.

As you can see it is very simple to gather the data for the auditors using modern programming. This may take a few minutes to generate and send the email, but it is many time faster than my colleague's methods.