-- changeward extract v1. One SELECT statement; no routines that mutate data.
-- Run using an EXISTING security officer with *AUDIT authority and journal access.
-- Do not add special authority to a journal-reader profile to run this script.
-- Edit the half-open window [start, end) and production library VALUES below.
-- Export the single result set as UTF-8 CSV with column headings and empty NULLs.
-- Check SQL warnings: incomplete object visibility is not a complete inventory.
WITH
params(window_start, window_end, observed_at) AS (
  VALUES (TIMESTAMP('2026-01-01 00:00:00'), TIMESTAMP('2026-04-01 00:00:00'), CURRENT_TIMESTAMP)
),
production_libraries(library_name) AS (VALUES (VARCHAR('PRODLIB', 10)), ('PRODDTA')),
all_events(entry_type, entry_timestamp, sequence_number, user_name,
  qualified_job_name, job_user, program_library, program_name, remote_address,
  system_name, object_library, object_name, object_type, receiver_library,
  receiver_name, previous_library, previous_name) AS (
  SELECT 'CO', a.ENTRY_TIMESTAMP, VARCHAR(a.SEQUENCE_NUMBER), a.USER_NAME,
    a.QUALIFIED_JOB_NAME, a.JOB_USER, a.PROGRAM_LIBRARY, a.PROGRAM_NAME,
    a.REMOTE_ADDRESS, a.SYSTEM_NAME, a.OBJECT_LIBRARY, a.OBJECT_NAME,
    a.OBJECT_TYPE, a.RECEIVER_LIBRARY, a.RECEIVER_NAME,
    CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(10))
  FROM params p, TABLE(SYSTOOLS.AUDIT_JOURNAL_CO(STARTING_TIMESTAMP => p.window_start, ENDING_TIMESTAMP => p.window_end)) a
  UNION ALL
  SELECT 'DO', a.ENTRY_TIMESTAMP, VARCHAR(a.SEQUENCE_NUMBER), a.USER_NAME,
    a.QUALIFIED_JOB_NAME, a.JOB_USER, a.PROGRAM_LIBRARY, a.PROGRAM_NAME,
    a.REMOTE_ADDRESS, a.SYSTEM_NAME, a.OBJECT_LIBRARY, a.OBJECT_NAME,
    a.OBJECT_TYPE, a.RECEIVER_LIBRARY, a.RECEIVER_NAME, NULL, NULL
  FROM params p, TABLE(SYSTOOLS.AUDIT_JOURNAL_DO(STARTING_TIMESTAMP => p.window_start, ENDING_TIMESTAMP => p.window_end)) a
  UNION ALL
  SELECT 'ZC', a.ENTRY_TIMESTAMP, VARCHAR(a.SEQUENCE_NUMBER), a.USER_NAME,
    a.QUALIFIED_JOB_NAME, a.JOB_USER, a.PROGRAM_LIBRARY, a.PROGRAM_NAME,
    a.REMOTE_ADDRESS, a.SYSTEM_NAME, a.LIBRARY_NAME, a.OBJECT_NAME,
    a.OBJECT_TYPE, a.RECEIVER_LIBRARY, a.RECEIVER_NAME, NULL, NULL
  FROM params p, TABLE(SYSTOOLS.AUDIT_JOURNAL_ZC(STARTING_TIMESTAMP => p.window_start, ENDING_TIMESTAMP => p.window_end)) a
  UNION ALL
  SELECT 'OR', a.ENTRY_TIMESTAMP, VARCHAR(a.SEQUENCE_NUMBER), a.USER_NAME,
    a.QUALIFIED_JOB_NAME, a.JOB_USER, a.PROGRAM_LIBRARY, a.PROGRAM_NAME,
    a.REMOTE_ADDRESS, a.SYSTEM_NAME, a.OBJECT_LIBRARY, a.OBJECT_NAME,
    a.OBJECT_TYPE, a.RECEIVER_LIBRARY, a.RECEIVER_NAME, NULL, NULL
  FROM params p, TABLE(SYSTOOLS.AUDIT_JOURNAL_OR(STARTING_TIMESTAMP => p.window_start, ENDING_TIMESTAMP => p.window_end)) a
  UNION ALL
  SELECT 'OM', a.ENTRY_TIMESTAMP, VARCHAR(a.SEQUENCE_NUMBER), a.USER_NAME,
    a.QUALIFIED_JOB_NAME, a.JOB_USER, a.PROGRAM_LIBRARY, a.PROGRAM_NAME,
    a.REMOTE_ADDRESS, a.SYSTEM_NAME, a.LIBRARY_NAME, a.OBJECT_NAME,
    a.OBJECT_TYPE, a.RECEIVER_LIBRARY, a.RECEIVER_NAME,
    a.PREV_LIBRARY_NAME, a.PREV_OBJECT_NAME
  FROM params p, TABLE(SYSTOOLS.AUDIT_JOURNAL_OM(STARTING_TIMESTAMP => p.window_start, ENDING_TIMESTAMP => p.window_end)) a
),
events AS (
  SELECT e.* FROM all_events e, params p
  WHERE e.entry_timestamp >= p.window_start AND e.entry_timestamp < p.window_end
    AND (e.object_library IN (SELECT library_name FROM production_libraries)
      OR e.previous_library IN (SELECT library_name FROM production_libraries))
),
objects AS (
  SELECT o.OBJLIB, o.OBJNAME, o.OBJTYPE, o.OBJECT_AUDIT
  FROM production_libraries l, TABLE(QSYS2.OBJECT_STATISTICS(l.library_name, '*ALL')) o
),
other_rows(record_kind, object_library, object_name, object_type,
  receiver_library, receiver_name, attach_ts, detach_ts, "key", "value") AS (
  SELECT 'receiver', CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(10)),
    CAST(NULL AS VARCHAR(8)), r.JOURNAL_RECEIVER_LIBRARY, r.JOURNAL_RECEIVER_NAME,
    VARCHAR(r.ATTACH_TIMESTAMP), VARCHAR(r.DETACH_TIMESTAMP),
    VARCHAR('status', 64), VARCHAR(r.STATUS, 1024)
  FROM QSYS2.JOURNAL_RECEIVER_INFO r, params p
  WHERE r.JOURNAL_LIBRARY = 'QSYS' AND r.JOURNAL_NAME = 'QAUDJRN'
    AND r.ATTACH_TIMESTAMP < p.window_end
    AND (r.DETACH_TIMESTAMP >= p.window_start OR r.DETACH_TIMESTAMP IS NULL)
  UNION ALL
  SELECT 'sysval', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'QAUDCTL', AUDITING_CONTROL FROM QSYS2.SECURITY_INFO
  UNION ALL
  SELECT 'sysval', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'QAUDLVL', AUDITING_LEVEL FROM QSYS2.SECURITY_INFO
  UNION ALL
  SELECT 'sysval', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'QAUDLVL2', AUDITING_LEVEL_EXTENSION FROM QSYS2.SECURITY_INFO
  UNION ALL
  SELECT 'objaudit', OBJLIB, OBJNAME, OBJTYPE, NULL, NULL, NULL, NULL, 'OBJAUDIT', OBJECT_AUDIT FROM objects
  UNION ALL
  SELECT 'meta', l.library_name, NULL, NULL, NULL, NULL, NULL, NULL, 'inventory_count', VARCHAR(COUNT(o.OBJNAME))
    FROM production_libraries l LEFT JOIN objects o ON l.library_name = o.OBJLIB GROUP BY l.library_name
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'script_version', '1' FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'window_start', VARCHAR(window_start) FROM params
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'window_end', VARCHAR(window_end) FROM params
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'observed_at', VARCHAR(observed_at) FROM params
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'system_name', COALESCE(MAX(system_name), CURRENT_SERVER) FROM events
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'event_count', VARCHAR(COUNT(*)) FROM events
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'release',
    CASE PTF_GROUP_NAME WHEN 'SF99704' THEN '7.4' WHEN 'SF99950' THEN '7.5' ELSE '7.6' END
    FROM QSYS2.GROUP_PTF_INFO WHERE PTF_GROUP_NAME IN ('SF99704', 'SF99950', 'SF99960')
      AND PTF_GROUP_STATUS = 'INSTALLED' GROUP BY PTF_GROUP_NAME
  UNION ALL
  SELECT 'meta', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ptf_level',
    PTF_GROUP_NAME CONCAT ':' CONCAT VARCHAR(MAX(PTF_GROUP_LEVEL))
    FROM QSYS2.GROUP_PTF_INFO WHERE PTF_GROUP_NAME IN ('SF99704', 'SF99950', 'SF99960')
      AND PTF_GROUP_STATUS = 'INSTALLED' GROUP BY PTF_GROUP_NAME
)
SELECT 'event' AS record_kind, entry_type, VARCHAR(entry_timestamp) AS entry_timestamp,
  sequence_number, user_name, qualified_job_name, job_user, program_library,
  program_name, remote_address, system_name, object_library, object_name, object_type,
  receiver_library, receiver_name, CAST(NULL AS VARCHAR(32)) AS attach_ts,
  CAST(NULL AS VARCHAR(32)) AS detach_ts, CAST(NULL AS VARCHAR(64)) AS "key",
  CAST(NULL AS VARCHAR(1024)) AS "value", previous_library, previous_name
FROM events
UNION ALL
SELECT record_kind, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
  object_library, object_name, object_type, receiver_library, receiver_name,
  attach_ts, detach_ts, "key", "value", NULL, NULL
FROM other_rows
ORDER BY record_kind, entry_timestamp, receiver_name, sequence_number, object_library, object_name, "key";