IBM i - retrieve details about invalid login attempts

Invalid Login Attempts.sql

--
-- Description: Using the AUDIT_JOURNAL_PW view, extract details from the 
--              system audit journal (QAUDJRN) about invalid login attempts 
--              (i.e. wrong password, invalid user) that have occurred in 
--              the past 10 minutes.
--              

--Description: the number of invalid login attempts in the past 10 minutes
SELECT COUNT(1) AS INVALID_LOGIN_ATTEMPTS
    FROM TABLE (
            SYSTOOLS.AUDIT_JOURNAL_PW(
                STARTING_RECEIVER_NAME => '*CURCHAIN', 
                STARTING_TIMESTAMP => CURRENT TIMESTAMP - 10 MINUTES)
        );


--Description: details of invalid login attempts i the past 10 minutes
SELECT ENTRY_TIMESTAMP, QUALIFIED_JOB_NAME, REMOTE_ADDRESS, VIOLATION_TYPE_DETAIL, AUDIT_USER_NAME, DEVICE_NAME
    FROM TABLE (
            SYSTOOLS.AUDIT_JOURNAL_PW(
                STARTING_RECEIVER_NAME => '*CURCHAIN', 
                STARTING_TIMESTAMP => CURRENT TIMESTAMP - 10 MINUTES)
        );