IBM i User ID Usage Tracking - Comprehensive SQL Queries

Steps

1. Active Job Usage (Real-time)

Description: Find all active jobs using the specific user ID

-- Find all active jobs using the specific user ID
SELECT JOB_NAME,
       AUTHORIZATION_NAME,
       JOB_TYPE,
       JOB_STATUS,
       SUBSYSTEM,
       FUNCTION,
       FUNCTION_TYPE,
       CLIENT_IP_ADDRESS,
       SERVER_TYPE,
       CPU_TIME,
       THREAD_COUNT
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
    JOB_NAME_FILTER => '*ALL',
    CURRENT_USER_LIST_FILTER => 'YOURUSERID'  -- Replace with actual user ID
))
ORDER BY JOB_STATUS, SUBSYSTEM;

Risk Level: INFORMATIONAL
Purpose: Identifies current system usage by the user ID

2. Job History (Past Usage)

Description: Find historical job usage from history log (last 30 days)

-- Find historical job usage from HISTORY_LOG_INFO table function
-- Shows jobs that used the profile in the last 30 days
SELECT MESSAGE_TIMESTAMP,
       JOB_NAME,
       FROM_USER,
       MESSAGE_TEXT,
       MESSAGE_ID,
       MESSAGE_SECOND_LEVEL_TEXT
FROM TABLE(QSYS2.HISTORY_LOG_INFO(
    START_TIME => CURRENT_TIMESTAMP - 30 DAYS
)) AS H
WHERE FROM_USER = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
ORDER BY MESSAGE_TIMESTAMP DESC;

Alternative: Filter for Job Start/End Messages

-- Focus on job start and completion messages
SELECT MESSAGE_TIMESTAMP,
       JOB_NAME,
       FROM_USER,
       MESSAGE_ID,
       MESSAGE_TEXT
FROM TABLE(QSYS2.HISTORY_LOG_INFO(
    START_TIME => CURRENT_TIMESTAMP - 30 DAYS
)) AS H
WHERE FROM_USER = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
  AND MESSAGE_ID IN ('CPF1124', 'CPF1164', 'CPC1221', 'CPC1224')  -- Job start/end messages
ORDER BY MESSAGE_TIMESTAMP DESC;

Alternative: Last 7 Days Only (Better Performance)

-- Query last 7 days for better performance
SELECT MESSAGE_TIMESTAMP,
       JOB_NAME,
       FROM_USER,
       MESSAGE_ID,
       MESSAGE_TEXT
FROM TABLE(QSYS2.HISTORY_LOG_INFO(
    START_TIME => CURRENT_TIMESTAMP - 7 DAYS,
    END_TIME => CURRENT_TIMESTAMP
)) AS H
WHERE FROM_USER = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
ORDER BY MESSAGE_TIMESTAMP DESC;

Risk Level: INFORMATIONAL
Purpose: Historical usage patterns and job execution history from system history log

Important Notes:

• HISTORY_LOG_INFO is a table function (requires TABLE() wrapper)
• Contains system history log entries (QHST)
• Uses FROM_USER column (not JOB_USER) to identify the user profile
• Default retention is typically 30 days (configurable via QHSTLOGSIZ system value and the CLEANUP Options)
• User ID should be in UPPERCASE
• Use START_TIME parameter to limit the time range for better performance
• For comprehensive job tracking, use Query #25 (SYSTOOLS.AUDIT_JOURNAL_JS with *JOBDTA auditing)
• Common message IDs:
  • CPF1124: Job started
  • CPF1164: Job ended
  • CPC1221: Job completed normally
  • CPC1224: Job completed abnormally

Available Columns in HISTORY_LOG_INFO:

• MESSAGE_TIMESTAMP
• JOB_NAME
• FROM_USER (user profile that sent the message)
• FROM_JOB
• FROM_PROGRAM
• MESSAGE_ID
• MESSAGE_TYPE
• MESSAGE_TEXT
• MESSAGE_SECOND_LEVEL_TEXT

3. Object Ownership (Programs, Files, etc.)

Description: Find all objects owned by the user ID using OBJECT_OWNERSHIP view

-- Find all objects owned by the user ID
-- Uses QSYS2.OBJECT_OWNERSHIP view for comprehensive ownership tracking
SELECT *
FROM QSYS2.OBJECT_OWNERSHIP
WHERE AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
ORDER BY OBJECT_LIBRARY,
         OBJECT_NAME,
         PATH_NAME;

Alternative: Filter by Object Type

-- Find only specific object types (e.g., programs and files)
SELECT OBJECT_LIBRARY,
       OBJECT_NAME,
       OBJECT_TYPE,
       AUTHORIZATION_NAME
FROM QSYS2.OBJECT_OWNERSHIP
WHERE AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
  AND OBJECT_TYPE IN ('*PGM', '*FILE', '*SRVPGM', '*DTAARA')
ORDER BY OBJECT_TYPE,
         OBJECT_LIBRARY,
         OBJECT_NAME;

Risk Level: HIGH
Impact: Identifies all objects that could be affected if user is removed

Important Notes:

• OBJECT_OWNERSHIP view provides comprehensive ownership and authority information
• Includes both traditional objects (libraries, programs, files) and IFS objects (PATH_NAME)
• AUTHORIZATION_NAME is the user profile being queried
• User ID should be in UPPERCASE
• Returns objects where user has explicit authority or ownership
• More comprehensive than OBJECT_STATISTICS as it includes authority details

Valid Columns in OBJECT_OWNERSHIP (IBM i 7.3+):

• OBJECT_LIBRARY (library name)
• OBJECT_NAME (object name)
• OBJECT_TYPE (object type)
• PATH_NAME (for IFS objects)
• AUTHORIZATION_NAME (user profile with authority)
• OWNER (object owner)
• PRIMARY_GROUP (primary group)
• IS_COLUMN (indicates if this is a column-level authority)
• COLUMN_NAME (column name if IS_COLUMN = 'YES')

4. Object Authority Grants

Description: Find all objects where the user has explicit authority

-- Find all objects where the user has explicit authority
SELECT SYSTEM_OBJECT_SCHEMA AS library_name,
       OBJECT_NAME,
       OBJECT_TYPE,
       AUTHORIZATION_NAME,
       OBJECT_OPERATIONAL,
       OBJECT_MANAGEMENT,
       OBJECT_EXISTENCE,
       OBJECT_ALTER,
       OBJECT_REFERENCE,
       DATA_READ,
       DATA_ADD,
       DATA_UPDATE,
       DATA_DELETE,
       DATA_EXECUTE
FROM QSYS2.OBJECT_PRIVILEGES
WHERE AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID
ORDER BY SYSTEM_OBJECT_SCHEMA, OBJECT_NAME;

Risk Level: MEDIUM
Impact: Shows explicit authority grants that may need review

5. Group Profile Membership

Description: Find if user is member of any groups or has supplemental groups

-- Find if user is member of any groups or has supplemental groups
SELECT AUTHORIZATION_NAME,
       GROUP_PROFILE_NAME,
       SUPPLEMENTAL_GROUP_COUNT,
       SUPPLEMENTAL_GROUP_LIST,
       SPECIAL_AUTHORITIES,
       STATUS
FROM QSYS2.USER_INFO
WHERE AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID
   OR GROUP_PROFILE_NAME = 'YOURUSERID'
   OR SUPPLEMENTAL_GROUP_LIST LIKE '%YOURUSERID%';

Risk Level: MEDIUM
Impact: Group memberships grant additional authorities

6. Java Program References (IFS Files)

Description: Search for user ID in Java properties, config files, and scripts

-- Search for Java config files in IFS
-- This lists potential configuration files that may contain user IDs
SELECT PATH_NAME,
       OBJECT_TYPE,
       DATA_SIZE,
       CREATE_TIMESTAMP
FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
    START_PATH_NAME => '/QIBM/UserData/Java400',  -- Java installation directory
    SUBTREE_DIRECTORIES => 'YES'
)) AS IFS
WHERE OBJECT_TYPE = '*STMF'
  AND (PATH_NAME LIKE '%.properties'
    OR PATH_NAME LIKE '%.xml'
    OR PATH_NAME LIKE '%.config'
    OR PATH_NAME LIKE '%.sh'
    OR PATH_NAME LIKE '%.bat')
ORDER BY PATH_NAME;


-- Note: You'll need to manually search file contents using IFS commands
-- or use QSHELL: grep -r "YOURUSERID" /path/to/search

Risk Level: HIGH
Impact: Java applications may have hardcoded user IDs in configuration files

Important Notes:

• IFS_OBJECT_STATISTICS requires table alias (AS IFS)
• This query lists files; you must manually search file contents
• Use grep commands below to search for actual user ID references
• OWNER and MODIFY_TIMESTAMP columns not available in this IBM i version
• Valid columns: PATH_NAME, OBJECT_TYPE, DATA_SIZE, CREATE_TIMESTAMP

Additional Commands:

# From QSHELL (CALL QP2TERM):
grep -r "YOURUSERID" /QIBM/UserData/Java400
grep -r "YOURUSERID" /QIBM/ProdData
grep -r "YOURUSERID" /home

7. Subsystem Job Descriptions

Description: Find job descriptions that might use the user ID

-- Find job descriptions that might use the user ID
SELECT JOB_DESCRIPTION_LIBRARY,
       JOB_DESCRIPTION,
       AUTHORIZATION_NAME,
       TEXT_DESCRIPTION
FROM QSYS2.JOB_DESCRIPTION_INFO
WHERE AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
ORDER BY JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION;

Risk Level: HIGH
Impact: Job descriptions control how jobs execute and under which user profile

Important Notes:

• Column names: JOB_DESCRIPTION (not JOB_DESCRIPTION_NAME), AUTHORIZATION_NAME (not USER_PROFILE)
• User ID should be in UPPERCASE
• Valid columns: JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION, AUTHORIZATION_NAME, TEXT_DESCRIPTION

8. Scheduled Jobs (Job Scheduler)

Description: Find scheduled jobs using the user ID

-- Find scheduled jobs using the user ID
SELECT SCHEDULED_JOB_NAME,
       SCHEDULED_BY,
       SCHEDULED_DATE,
       SCHEDULED_TIME,
       FREQUENCY,
       STATUS,
       USER_PROFILE_FOR_SUBMITTED_JOB,
       COMMAND_STRING
FROM QSYS2.SCHEDULED_JOB_INFO
WHERE SCHEDULED_BY = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
   OR USER_PROFILE_FOR_SUBMITTED_JOB = 'YOURUSERID'
ORDER BY SCHEDULED_DATE, SCHEDULED_TIME;

Risk Level: HIGH
Impact: Scheduled jobs will fail if user is disabled or removed

Important Notes:

• SCHEDULED_BY: User who scheduled the job
• USER_PROFILE_FOR_SUBMITTED_JOB: User profile under which the job runs
• COMMAND_STRING: Command to be executed
• User ID should be in UPPERCASE
• Valid columns: SCHEDULED_JOB_NAME, SCHEDULED_BY, SCHEDULED_DATE, SCHEDULED_TIME, FREQUENCY, STATUS, USER_PROFILE_FOR_SUBMITTED_JOB, COMMAND_STRING

10. SQL Procedures and Functions

Description: Find SQL routines created by or referencing the user

-- Find SQL routines created by or referencing the user
SELECT ROUTINE_SCHEMA,
       ROUTINE_NAME,
       ROUTINE_TYPE,
       ROUTINE_DEFINER,
       ROUTINE_BODY,
       EXTERNAL_LANGUAGE,
       ROUTINE_DEFINITION
FROM QSYS2.SYSROUTINES
WHERE ROUTINE_DEFINER = 'YOURUSERID'  -- Replace with actual user ID
   OR ROUTINE_DEFINITION LIKE '%YOURUSERID%'
ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;

Risk Level: MEDIUM
Impact: SQL routines may have embedded user references or run with definer's authority

11. Database Triggers

Description: Find triggers that might reference the user ID

-- Find triggers that might reference the user ID
SELECT TRIGGER_SCHEMA,
       TRIGGER_NAME,
       EVENT_OBJECT_SCHEMA,
       EVENT_OBJECT_TABLE,
       ACTION_STATEMENT,
       DEFINER
FROM QSYS2.SYSTRIGGERS
WHERE DEFINER = 'YOURUSERID'  -- Replace with actual user ID
   OR ACTION_STATEMENT LIKE '%YOURUSERID%'
ORDER BY TRIGGER_SCHEMA, TRIGGER_NAME;

Risk Level: MEDIUM
Impact: Triggers may fail if user is removed or authorities change

12. Exit Programs (Security Hooks)

Description: Find exit programs that might use the user ID

-- Find exit programs that might use the user ID
SELECT EXIT_POINT_NAME,
       EXIT_PROGRAM_LIBRARY,
       EXIT_PROGRAM,
       EXIT_PROGRAM_NUMBER,
       EXIT_POINT_FORMAT
FROM QSYS2.EXIT_PROGRAM_INFO
WHERE EXIT_PROGRAM_LIBRARY IN (
    SELECT OBJLIB 
    FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL', '*ALL'))
    WHERE OBJOWNER = 'YOURUSERID'  -- Replace with actual user ID
)
ORDER BY EXIT_POINT_NAME, EXIT_PROGRAM_NUMBER;

Risk Level: CRITICAL
Impact: Exit programs can intercept and modify system behavior

14. IFS File Ownership

Description: Find IFS files owned by the user

-- Find IFS files owned by the user
SELECT PATH_NAME,
       OBJECT_TYPE,
       DATA_SIZE,
       CREATE_TIMESTAMP
FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
    START_PATH_NAME => '/',
    SUBTREE_DIRECTORIES => 'YES'
))
WHERE OBJECT_OWNER = 'YOURUSERID'  -- Replace with actual user ID
ORDER BY PATH_NAME;

Risk Level: MEDIUM
Impact: IFS files may become inaccessible if owner is removed

Note: This query can be slow. Consider limiting to specific directories:

-- Limit to specific directories for better performance
START_PATH_NAME => '/home'
START_PATH_NAME => '/tmp'
START_PATH_NAME => '/QIBM/UserData'

13. Audit Journal Entries (Historical Activity)

Description: Find audit journal entries for the user (requires audit journal)

-- Find audit journal entries for the user (requires audit journal)
-- This shows what the user has been doing
SELECT ENTRY_TIMESTAMP,
       JOB_NAME,
       USER_NAME,
       JOURNAL_ENTRY_TYPE,
       OBJECT,
       OBJECT_TYPE
FROM TABLE(QSYS2.DISPLAY_JOURNAL(
    'QSYS', 'QAUDJRN',
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
))
WHERE USER_NAME = 'YOURUSERID'  -- Replace with actual user ID
ORDER BY ENTRY_TIMESTAMP DESC
FETCH FIRST 1000 ROWS ONLY;

Risk Level: INFORMATIONAL
Purpose: Historical activity tracking and compliance

Authority Required: *AUDIT special authority

14. Search Source Code for User ID References

Description: Search source code for hardcoded user ID references

Command (must be run from within PDM - WRKMBRPDM):

FNDSTRPDM STRING('YOURUSERID') FILE(SRCFILENAME) MBR(*ALL)

Example usage:

WRKMBRPDM FILE(MYLIB/QCLSRC)
  Then use option 25 (Find String) or F16 (Find String)
  Or type: FNDSTRPDM STRING('YOURUSERID') FILE(QCLSRC) MBR(*ALL)

To search multiple source files, repeat for each:

WRKMBRPDM FILE(MYLIB/QCLSRC)    - Search CL source
WRKMBRPDM FILE(MYLIB/QRPGLESRC) - Search RPG source
WRKMBRPDM FILE(MYLIB/QCMDSRC)   - Search CMD source

Risk Level: MEDIUM
Impact: Source code may have hardcoded user IDs

15. Active Jobs Using Specific Job Description

Description: Find active jobs that are using job descriptions with the user ID

-- Find active jobs that are using job descriptions with the user ID
-- This shows current connections
SELECT j.JOB_NAME,
       j.AUTHORIZATION_NAME,
       j.JOB_TYPE,
       j.JOB_STATUS,
       j.SUBSYSTEM,
       j.FUNCTION,
       j.CLIENT_IP_ADDRESS,
       j.SERVER_TYPE,
       j.JOB_DESCRIPTION_LIBRARY,
       j.JOB_DESCRIPTION,
       jd.USER_NAME AS JOBD_USER,
       j.JOB_ENTERED_SYSTEM_TIME,
       j.CPU_TIME
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) j
LEFT JOIN QSYS2.JOB_DESCRIPTION_INFO jd
    ON j.JOB_DESCRIPTION_LIBRARY = jd.JOB_DESCRIPTION_LIBRARY
   AND j.JOB_DESCRIPTION = jd.JOB_DESCRIPTION
WHERE j.AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID
   OR jd.USER_NAME = 'YOURUSERID'
ORDER BY j.JOB_ENTERED_SYSTEM_TIME DESC;

Risk Level: INFORMATIONAL
Purpose: Shows current active connections using the user ID

16. Historical Job Connections (Job Log Analysis)

Description: Track historical connections using job log

-- Track historical connections using job log
-- Shows past connections and their details
SELECT
       JOB_NAME,
       JOB_USER AS USER_NAME,
       JOB_TYPE,
       JOB_STATUS,
       JOB_SUBSYSTEM,
       JOB_ENTERED_SYSTEM_TIME,
       JOB_END_TIME,
       COMPLETION_STATUS
FROM TABLE(
     QSYS2.JOB_INFO(
         JOB_USER_FILTER   => 'YOURUSERID',
         JOB_STATUS_FILTER => '*ALL'
     )
)
ORDER BY JOB_ENTERED_SYSTEM_TIME DESC
FETCH FIRST 5000 ROWS ONLY;

Risk Level: INFORMATIONAL
Purpose: Historical connection patterns and usage analysis

17. Prestart Job Entries Using User ID

Description: Find prestart job entries that use the user ID

-- Find prestart job entries that use the user ID
-- These are server jobs that stay active waiting for work
SELECT SUBSYSTEM_DESCRIPTION_LIBRARY,
       SUBSYSTEM_DESCRIPTION,
       PRESTART_JOB_PROGRAM_LIBRARY,
       PRESTART_JOB_PROGRAM,
       JOB_NAME,
       USER_PROFILE,
       INITIAL_JOBS,
       THRESHOLD,
       ADDITIONAL_JOBS,
       MAXIMUM_JOBS,
       POOL_ID
FROM QSYS2.PRESTART_JOB_INFO
WHERE USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID
ORDER BY SUBSYSTEM_DESCRIPTION, PRESTART_JOB_PROGRAM;

Risk Level: HIGH
Impact: Prestart jobs provide server functionality; failures affect applications

18. Comprehensive Connection Summary

Description: Comprehensive view of all connection methods for the user

-- Comprehensive view of all connection methods for the user
WITH ActiveConnections AS (
    SELECT
        'ACTIVE JOB' AS connection_type,
        JOB_NAME AS identifier,
        AUTHORIZATION_NAME AS user_name,
        JOB_TYPE,
        SUBSYSTEM,
        CLIENT_IP_ADDRESS,
        JOB_ENTERED_SYSTEM_TIME AS timestamp
    FROM TABLE(QSYS2.ACTIVE_JOB_INFO())
    WHERE AUTHORIZATION_NAME = 'YOURUSERID'  -- Replace with actual user ID
),
PrestartEntries AS (
    SELECT
        'PRESTART ENTRY' AS connection_type,
        SUBSYSTEM_DESCRIPTION || '/' || PRESTART_JOB_PROGRAM AS identifier,
        USER_PROFILE AS user_name,
        CAST(NULL AS VARCHAR(10)) AS JOB_TYPE,
        SUBSYSTEM_DESCRIPTION AS SUBSYSTEM,
        CAST(NULL AS VARCHAR(45)) AS CLIENT_IP_ADDRESS,
        CAST(NULL AS TIMESTAMP) AS timestamp
    FROM QSYS2.PRESTART_JOB_INFO
    WHERE USER_PROFILE = 'YOURUSERID'
)
SELECT *
FROM (
    SELECT * FROM ActiveConnections
    UNION ALL
    SELECT * FROM PrestartEntries
) A
ORDER BY
    connection_type,
    CASE WHEN timestamp IS NULL THEN 1 ELSE 0 END,
    timestamp DESC;

Risk Level: INFORMATIONAL
Purpose: Single query to see all connection methods

19. *JOBDTA Audit Journal Tracking (RECOMMENDED)

Description: Track user profile usage through *JOBDTA audit journal entries using SYSTOOLS.AUDIT_JOURNAL_JS() - the most comprehensive method

Reference: https://www.ibm.com/support/pages/node/7269032

This method uses the *JOBDTA auditing value to track all job-related activities for a specific user profile. This is the IBM-recommended approach for comprehensive user ID tracking.

Prerequisites

1. *Enable JOBDTA Auditing:

   CHGSECAUD

   Press F4 to prompt, then add *JOBDTA to the QAUDLVL parameter

2. Set User Profile to be Audited:

   CHGUSRAUD USRPRF(YOURUSERID) AUDLVL(*JOBBAS)

Query 1: Comprehensive Job Event Tracking with Connection Types (IBM Recommended)

-- Track all job events with connection type identification
-- Based on IBM Support Document 7269032
SELECT ENTRY_TIMESTAMP AS JOB_EVENT_TIME,
       USER_NAME,
       JOB_NAME,
       JOB_TYPE,
       REAL_USER_PROFILE,
       ENTRY_TYPE,
       CASE
           WHEN ENTRY_TYPE = 'S' THEN 'Start'
           WHEN ENTRY_TYPE = 'E' THEN 'End'
           WHEN ENTRY_TYPE = 'M' THEN 'Change profile or group profile'
           WHEN ENTRY_TYPE = 'B' THEN 'Begin'
           ELSE 'Other'
       END AS JOB_EVENT_TYPE,
       CASE
           WHEN JOB_TYPE = 'INT' THEN '5250 / Telnet'
           WHEN JOB_NAME LIKE 'QZDASOINIT%' THEN 'ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QZDASSINIT%' THEN 'Secured ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QTFTP%' THEN 'FTP'
           WHEN JOB_NAME LIKE 'QSSHD%' THEN 'SFTP'
           WHEN JOB_NAME LIKE 'QRWTSRVR%' THEN 'Remote SQL'
           WHEN JOB_NAME LIKE 'QZRCSRVS%' THEN 'Web /Remote Command'
           WHEN JOB_NAME LIKE 'QP0ZSPWP%' THEN 'SSH/Spawned Process'
           WHEN JOB_NAME LIKE 'QHTTPSVR%' THEN 'Web (HTTP/HTTPS)'
           WHEN JOB_NAME LIKE 'QZLSFILE%' THEN 'Mapped Drive'
           WHEN JOB_NAME LIKE 'QPWFSERVSO%' THEN 'File Server Job'
           ELSE 'Other Host Server'
       END AS CONNECTION_TYPE,
       REMOTE_ADDRESS
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_JS(STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS)
) AJ
WHERE REAL_USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
      AND ENTRY_TYPE IN ('S', 'E', 'B', 'M')
      AND REAL_USER_PROFILE NOT IN ('QCTP', 'QUSER')  -- Exclude system profiles
ORDER BY JOB_EVENT_TIME DESC;

Query 2: Job Start Events Only

-- Track only job start events
-- Useful for identifying when and how user connects
SELECT ENTRY_TIMESTAMP AS JOB_START_TIME,
       USER_NAME,
       JOB_NAME,
       JOB_TYPE,
       REAL_USER_PROFILE,
       CASE
           WHEN JOB_TYPE = 'INT' THEN '5250 / Telnet'
           WHEN JOB_NAME LIKE 'QZDASOINIT%' THEN 'ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QZDASSINIT%' THEN 'Secured ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QTFTP%' THEN 'FTP'
           WHEN JOB_NAME LIKE 'QSSHD%' THEN 'SFTP'
           WHEN JOB_NAME LIKE 'QRWTSRVR%' THEN 'Remote SQL'
           WHEN JOB_NAME LIKE 'QZRCSRVS%' THEN 'Web /Remote Command'
           WHEN JOB_NAME LIKE 'QP0ZSPWP%' THEN 'SSH/Spawned Process'
           WHEN JOB_NAME LIKE 'QHTTPSVR%' THEN 'Web (HTTP/HTTPS)'
           WHEN JOB_NAME LIKE 'QZLSFILE%' THEN 'Mapped Drive'
           WHEN JOB_NAME LIKE 'QPWFSERVSO%' THEN 'File Server Job'
           ELSE 'Other Host Server'
       END AS CONNECTION_TYPE,
       REMOTE_ADDRESS,
       QUALIFIED_JOB_NAME
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_JS(STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS)
) AJ
WHERE REAL_USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
      AND ENTRY_TYPE = 'S'  -- Start events only
      AND REAL_USER_PROFILE NOT IN ('QCTP', 'QUSER')
ORDER BY JOB_START_TIME DESC;

Query 3: Summary Statistics

-- Get summary statistics of user profile usage
-- Shows connection type distribution
SELECT CASE
           WHEN JOB_TYPE = 'INT' THEN '5250 / Telnet'
           WHEN JOB_NAME LIKE 'QZDASOINIT%' THEN 'ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QZDASSINIT%' THEN 'Secured ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QTFTP%' THEN 'FTP'
           WHEN JOB_NAME LIKE 'QSSHD%' THEN 'SFTP'
           WHEN JOB_NAME LIKE 'QRWTSRVR%' THEN 'Remote SQL'
           WHEN JOB_NAME LIKE 'QZRCSRVS%' THEN 'Web /Remote Command'
           WHEN JOB_NAME LIKE 'QP0ZSPWP%' THEN 'SSH/Spawned Process'
           WHEN JOB_NAME LIKE 'QHTTPSVR%' THEN 'Web (HTTP/HTTPS)'
           WHEN JOB_NAME LIKE 'QZLSFILE%' THEN 'Mapped Drive'
           WHEN JOB_NAME LIKE 'QPWFSERVSO%' THEN 'File Server Job'
           ELSE 'Other Host Server'
       END AS CONNECTION_TYPE,
       COUNT(*) AS event_count,
       COUNT(DISTINCT DATE(ENTRY_TIMESTAMP)) AS days_active,
       MIN(ENTRY_TIMESTAMP) AS first_event,
       MAX(ENTRY_TIMESTAMP) AS last_event,
       COUNT(DISTINCT REMOTE_ADDRESS) AS unique_ip_addresses
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_JS(STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS)
) AJ
WHERE REAL_USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
      AND ENTRY_TYPE IN ('S', 'E', 'B', 'M')
      AND REAL_USER_PROFILE NOT IN ('QCTP', 'QUSER')
GROUP BY CASE
           WHEN JOB_TYPE = 'INT' THEN '5250 / Telnet'
           WHEN JOB_NAME LIKE 'QZDASOINIT%' THEN 'ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QZDASSINIT%' THEN 'Secured ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QTFTP%' THEN 'FTP'
           WHEN JOB_NAME LIKE 'QSSHD%' THEN 'SFTP'
           WHEN JOB_NAME LIKE 'QRWTSRVR%' THEN 'Remote SQL'
           WHEN JOB_NAME LIKE 'QZRCSRVS%' THEN 'Web /Remote Command'
           WHEN JOB_NAME LIKE 'QP0ZSPWP%' THEN 'SSH/Spawned Process'
           WHEN JOB_NAME LIKE 'QHTTPSVR%' THEN 'Web (HTTP/HTTPS)'
           WHEN JOB_NAME LIKE 'QZLSFILE%' THEN 'Mapped Drive'
           WHEN JOB_NAME LIKE 'QPWFSERVSO%' THEN 'File Server Job'
           ELSE 'Other Host Server'
       END
ORDER BY event_count DESC;

Query 4: Remote Access Tracking (IP Addresses)

-- Track remote access with IP addresses
-- Identifies where connections are coming from
SELECT ENTRY_TIMESTAMP AS ACCESS_TIME,
       USER_NAME,
       JOB_NAME,
       JOB_TYPE,
       REAL_USER_PROFILE,
       REMOTE_ADDRESS,
       CASE
           WHEN JOB_TYPE = 'INT' THEN '5250 / Telnet'
           WHEN JOB_NAME LIKE 'QZDASOINIT%' THEN 'ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QZDASSINIT%' THEN 'Secured ODBC / JDBC'
           WHEN JOB_NAME LIKE 'QTFTP%' THEN 'FTP'
           WHEN JOB_NAME LIKE 'QSSHD%' THEN 'SFTP'
           WHEN JOB_NAME LIKE 'QRWTSRVR%' THEN 'Remote SQL'
           WHEN JOB_NAME LIKE 'QZRCSRVS%' THEN 'Web /Remote Command'
           WHEN JOB_NAME LIKE 'QP0ZSPWP%' THEN 'SSH/Spawned Process'
           WHEN JOB_NAME LIKE 'QHTTPSVR%' THEN 'Web (HTTP/HTTPS)'
           WHEN JOB_NAME LIKE 'QZLSFILE%' THEN 'Mapped Drive'
           WHEN JOB_NAME LIKE 'QPWFSERVSO%' THEN 'File Server Job'
           ELSE 'Other Host Server'
       END AS CONNECTION_TYPE,
       ENTRY_TYPE,
       CASE
           WHEN ENTRY_TYPE = 'S' THEN 'Start'
           WHEN ENTRY_TYPE = 'E' THEN 'End'
           WHEN ENTRY_TYPE = 'M' THEN 'Change profile or group profile'
           ELSE 'Other'
       END AS EVENT_TYPE
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_JS(STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS)
) AJ
WHERE REAL_USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
      AND ENTRY_TYPE IN ('S', 'E', 'B', 'M')
      AND REMOTE_ADDRESS IS NOT NULL
      AND REMOTE_ADDRESS != ''
      AND REAL_USER_PROFILE NOT IN ('QCTP', 'QUSER')
ORDER BY ACCESS_TIME DESC;

Query 5: Profile Change Events (Swap User)

-- Track profile change events (swap user, change profile)
-- Shows when the user profile is swapped or changed during job execution
SELECT ENTRY_TIMESTAMP AS CHANGE_TIME,
       USER_NAME,
       JOB_NAME,
       JOB_TYPE,
       REAL_USER_PROFILE,
       ENTRY_TYPE,
       'Change profile or group profile' AS EVENT_DESCRIPTION,
       REMOTE_ADDRESS,
       QUALIFIED_JOB_NAME
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_JS(STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS)
) AJ
WHERE REAL_USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
      AND ENTRY_TYPE = 'M'  -- Profile change events
      AND REAL_USER_PROFILE NOT IN ('QCTP', 'QUSER')
ORDER BY CHANGE_TIME DESC;

Query 6: Daily Usage Pattern Analysis

-- Analyze daily usage patterns for the user profile
-- Shows usage trends over time
SELECT DATE(ENTRY_TIMESTAMP) AS usage_date,
       COUNT(*) AS total_events,
       COUNT(DISTINCT JOB_NAME) AS unique_jobs,
       COUNT(DISTINCT REMOTE_ADDRESS) AS unique_ip_addresses,
       SUM(CASE WHEN ENTRY_TYPE = 'S' THEN 1 ELSE 0 END) AS job_starts,
       SUM(CASE WHEN ENTRY_TYPE = 'E' THEN 1 ELSE 0 END) AS job_ends,
       SUM(CASE WHEN ENTRY_TYPE = 'M' THEN 1 ELSE 0 END) AS profile_changes
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_JS(STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS)
) AJ
WHERE REAL_USER_PROFILE = 'YOURUSERID'  -- Replace with actual user ID (UPPERCASE)
      AND ENTRY_TYPE IN ('S', 'E', 'B', 'M')
      AND REAL_USER_PROFILE NOT IN ('QCTP', 'QUSER')
GROUP BY DATE(ENTRY_TIMESTAMP)
ORDER BY usage_date DESC;

Setup Commands

-- 1. Enable *JOBDTA auditing at system level
--    Run: CHGSECAUD
--    Press F4 to prompt, then add *JOBDTA to the QAUDLVL parameter

-- 2. Set specific user profile to be audited
CHGUSRAUD USRPRF(YOURUSERID) AUDLVL(*JOBBAS)

-- 3. Verify auditing is enabled
DSPUSRPRF USRPRF(YOURUSERID)

-- 4. Check system audit level
DSPSYSVAL SYSVAL(QAUDLVL)

-- 5. Verify audit journal exists and is active
WRKJRN JRN(QSYS/QAUDJRN)

Important Notes

1. SYSTOOLS.AUDIT_JOURNAL_JS() Function:
   • Specifically designed for job start/end/change auditing
   • More efficient than generic DISPLAY_JOURNAL for job tracking
   • Automatically filters for job-related audit entries
2. Entry Types:
   • S: Job Start
   • E: Job End
   • M: Profile change or group profile change (swap user)
   • B: Job Begin (less common)
3. Connection Type Identification:
   • Based on job name patterns
   • Identifies common IBM i connection methods
   • Helps understand how the user profile is being accessed
4. User ID Format:
   • MUST be UPPERCASE for REAL_USER_PROFILE comparison
   • IBM i user profiles are stored in uppercase
5. Performance Impact:
   • *JOBDTA auditing can generate significant journal entries
   • Monitor journal size and performance
   • Use appropriate date ranges to limit result sets
6. Journal Management:
   • Regularly save and clear audit journal
   • Consider journal receivers management
   • Set appropriate journal receiver threshold
7. Authority Required:
   • *AUDIT special authority required to query QAUDJRN
   • *SECADM required to change audit settings
8. Retention Period:
   • Adjust STARTING_TIMESTAMP based on your retention requirements
   • Consider regulatory compliance requirements

Connection Types Identified

The queries identify these common connection types:

• 5250 / Telnet: Traditional green screen terminal
• ODBC / JDBC: Database connections (QZDASOINIT)
• Secured ODBC / JDBC: Encrypted database connections (QZDASSINIT)
• FTP: File Transfer Protocol (QTFTP)
• SFTP: Secure FTP via SSH (QSSHD)
• Remote SQL: Remote SQL execution (QRWTSRVR)
• Web / Remote Command: Web-based commands (QZRCSRVS)
• SSH / Spawned Process: SSH sessions (QP0ZSPWP)
• Web (HTTP/HTTPS): Web server jobs (QHTTPSVR)
• Mapped Drive: Network drive mapping (QZLSFILE)
• File Server Job: File server operations (QPWFSERVSO)
• Other Host Server: Other IBM i host server jobs

Advantages of *JOBDTA Auditing with SYSTOOLS.AUDIT_JOURNAL_JS()

✓ Most Comprehensive: Captures ALL job-related activities
✓ IBM Recommended: Official IBM method for user tracking
✓ Connection Type Identification: Automatically identifies how user connects
✓ Historical Data: Provides complete audit trail
✓ Compliance Ready: Meets most regulatory requirements
✓ Detailed Information: Includes IP addresses, job names, timestamps
✓ Real-time: Captures events as they happen
✓ Efficient: SYSTOOLS function optimized for job auditing

Disadvantages

✗ Performance Impact: Can generate large volumes of journal entries
✗ Storage Requirements: Requires significant disk space
✗ Requires Setup: Must be configured before tracking begins
✗ Authority Required: Needs *AUDIT special authority
✗ Uppercase Requirement: User IDs must be in uppercase

Best Practice Workflow

1. Enable auditing for the user profile using setup commands
2. Wait 24-48 hours to collect meaningful data
3. Run Query 3 (Summary Statistics) first to get overview
4. Run Query 1 (Comprehensive) to see detailed events
5. Run Query 4 (Remote Access) to identify IP addresses
6. Run Query 6 (Daily Pattern) to analyze trends
7. Document results for compliance/security review
8. Disable auditing if no longer needed (to reduce overhead)

Troubleshooting

No Results Returned:

• Verify user ID is in UPPERCASE
• Check if auditing is enabled: DSPUSRPRF USRPRF(YOURUSERID)
• Verify audit journal has entries: WRKJRN JRN(QSYS/QAUDJRN)
• Ensure sufficient time has passed since enabling auditing

Performance Issues:

• Reduce date range in STARTING_TIMESTAMP
• Run queries during off-peak hours
• Consider creating indexes on audit journal receivers

Risk Level: INFORMATIONAL
Purpose: Most comprehensive and accurate method for tracking user profile usage
Authority Required: *AUDIT special authority

Usage Instructions

Step-by-Step Process

1. Replace 'YOURUSERID' with the actual user ID you're searching for in ALL queries
2. Run queries in recommended order:
   • Query #19 (*JOBDTA Audit Journal) - Most comprehensive tracking method (RECOMMENDED)
   • Query #1 (Active Jobs) - Current connections
   • Query #3 (Object Ownership) - Objects owned
   • Query #7 (Job Descriptions) - Configuration
   • Query #18 (Comprehensive Summary) - All connection methods
   • Remaining queries as needed for specific areas
3. For Java/Email configs:
   • Use Query #6 (IFS queries)

   • Then manually inspect files using:

     CALL QP2TERM
     grep -r "YOURUSERID" /QIBM/UserData/Java400
     grep -r "YOURUSERID" /QIBM/ProdData
     grep -r "YOURUSERID" /home

4. Check application-specific locations:
   • Many Java applications store configs in custom IFS directories
   • Review application documentation for config file locations
5. Review audit journal:
   • Query #19 (*JOBDTA) shows comprehensive job tracking with connection types
   • Requires *AUDIT special authority
   • IBM-recommended method for user profile usage tracking

Performance Considerations

• Add date filters to historical queries to improve performance
• Limit IFS searches to specific directories rather than root (/)
• Use FETCH FIRST clauses to limit result sets during testing
• Run during off-peak hours for queries that scan large tables
• Consider creating indexes on frequently queried columns

Authority Requirements

Some queries require specific authorities:

• Audit Journal queries: *AUDIT special authority
• Object queries: Authority to objects being queried
• IFS queries: Authority to IFS directories
• Job queries: *JOBCTL or *SPLCTL may be needed

Additional Manual Checks

Beyond SQL queries, also check:

1. WRKJOB command for active jobs
2. DSPOBJAUT for specific object authorities
3. WRKSPLF YOURUSERID for spooled files
4. WRKOUTQ to check output queues
5. Application-specific configuration files in IFS
6. Custom application databases for embedded user IDs
7. Third-party software configurations
8. Network authentication systems (LDAP, Active Directory)

Connection Types Tracked

This document covers all major connection and usage types:

1. Interactive sign-ons (workstation entries)
2. Batch job submissions (job queue entries)
3. Server jobs (prestart entries)
4. Remote connections (communication entries)
5. Auto-start jobs (subsystem entries)
6. Scheduled jobs (job scheduler)
7. Object ownership (programs, files, etc.)
8. Authority grants (explicit permissions)
9. Java applications (IFS configuration files)
10. Email systems (SMTP/POP configurations)
11. SQL routines (procedures, functions, triggers)
12. Exit programs (security hooks)

Best Practices

Before Disabling or Removing a User ID

1. Run ALL queries in this document
2. Document all findings
3. Identify dependencies and impacts
4. Create remediation plan
5. Test in development environment first
6. Have rollback plan ready
7. Schedule during maintenance window
8. Monitor system after changes

Regular Auditing

• Daily: Active jobs (Query #1)
• Weekly: New objects owned (Query #3)
• Monthly: Complete audit using all queries
• Quarterly: Review and update documentation

Documentation

• Save query results with timestamps
• Document all user ID usages found
• Track remediation actions
• Maintain change history

Troubleshooting

Query Returns No Results

• Verify user ID spelling (case-sensitive)
• Check if user profile exists: SELECT * FROM QSYS2.USER_INFO WHERE AUTHORIZATION_NAME = 'YOURUSERID'
• Verify you have authority to run the query
• Check if required IBM i Services are installed

Query Runs Slowly

• Add more specific WHERE clauses
• Limit date ranges for historical queries
• Use FETCH FIRST to limit results during testing
• Run during off-peak hours
• Consider creating indexes

Access Denied Errors

• Verify you have required special authorities
• Check object-level authorities
• Contact security administrator if needed

Related Documents

• IBM_i_Security_Attack_Vectors.md
• IBM_i_Additional_Security_Queries.md
• IBM_i_Services_SQL_Reference.md

Notes

• All queries have been designed to minimize performance impact
• Results should be reviewed in context of your organization's security policies
• Some queries may require additional IBM i Services to be installed
• Consider data privacy regulations when storing query results
• Regular execution of these queries helps maintain security posture
• Test all queries in development before running in production