Using System Limits and Audit Journals to Investigate IFS Usage

IFSAuditAndSystemLimits.sql

/*************************************************************/
/* Use system limits to find users abusing IFS space         */
/*************************************************************/

-- Look at all users who have triggered system limits in the last 24 hours...
SELECT *
    FROM QSYS2.SYSLIMITS
    WHERE LAST_CHANGE_TIMESTAMP > CURRENT TIMESTAMP - 1 DAY
    AND USER_NAME != 'QSYS'
    ORDER BY LAST_CHANGE_TIMESTAMP DESC;

-- SQL can help identify who has been creating these files!
SELECT USER_NAME, 
       COUNT(*) AS NUM_FILES, 
       MAX(ENTRY_TIMESTAMP) AS LATEST_UPDATE
    FROM TABLE (
            SYSTOOLS.AUDIT_JOURNAL_CO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 14 DAY)
        )
WHERE USER_NAME != 'QSYS'
GROUP BY USER_NAME
ORDER BY NUM_FILES DESC;

-- If a user profile has triggered a system limit, use audit journal entries to track what they are doing...
SELECT SYS.USER_NAME,
       COUNT(*) AS NUM_FILES,
       MAX(ENTRY_TIMESTAMP) AS LATEST_UPDATE
    FROM QSYS2.SYSLIMTBL SYS
         JOIN TABLE (
                 SYSTOOLS.AUDIT_JOURNAL_CO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 14 DAY)
             ) AUDIT
             ON SYS.USER_NAME = AUDIT.USER_NAME
    WHERE SYS.USER_NAME NOT LIKE 'Q%'
        AND LIMIT_CATEGORY = 5 -- Category 5 is "File system", see https://www.ibm.com/docs/en/i/7.5?topic=services-syslimtbl-table
       
    GROUP BY SYS.USER_NAME
    ORDER BY NUM_FILES DESC;