IBM Support

Finding the Top Audited Objects for Change and Use Operations.

Question & Answer


Question

How do you create a report for the top Audited Objects for Change and Use Operations?

Answer

Using the SQL Service QSYS2.DISPLAY_JOURNAL() to create a report that lists the top audited objects for Change and Use Operations. 

Authorization:

  • You must have *USE authority to the journal and to all requested journal receivers.
  • *OBJEXIST authority is required to the journal if object-name is omitted or if object-name specifies an object that no longer exists.
  • If object-name is *ALL, you must be authorized to every object associated with a journal entry.
Note:   This method is only available on R720, R730, R740 and R750 of the Operating System. 
 
Before you start, verify that the QAUDJRN journal exists and that you are tracking for Object Auditing operations.
Use the DSPSECAUD command and make sure the following settings are configured on your system:
 
Security journal QAUDJRN exists . . . . . :  YES    
Current QAUDCTL system value  . . . . . . : *AUDLVL *OBJAUD
 
If not there, use the CHGSECAUD command to add *OBJAUD to the QAUTCTL system value on your system.  
 
The Object Auditing value also has to be turned on for the objects to show up on this report.  Refer to the CHGOBJAUD and CHGAUD commands on the links:
 
 
Change Operations for IFS Objects: 
 
Using the RUN SQL Scripts tool, run the following SQL Statement:
 
SELECT Path_name AS IFS_Object_name,
       COUNT(*) AS zc_count
    FROM TABLE (
            QSYS2.DISPLAY_JOURNAL(
                JOURNAL_LIBRARY => 'QSYS',
                JOURNAL_NAME => 'QAUDJRN',
                STARTING_RECEIVER_NAME => '*CURCHAIN', JOURNAL_ENTRY_TYPES => 'ZC', STARTING_TIMESTAMP => '2022-08-01 06:00:00',
                ENDING_TIMESTAMP => '2022-08-04 08:39:00')
        ) AS X
    WHERE path_name IS NOT null
    GROUP BY path_name
    ORDER BY zc_count DESC
image-20220804074225-1
 
Notes:
  • Modify the time and date on the SQL, 
  • If Hexadecimal data is displayed on the report, you need to change the JDBC connection properties to translate the CCSID to 65535. On the Run SQL tool, select the Connection Menu > Connected > Edit JDBC Connection > Translation and click on Translate CCSID 65535. Save the connection and reconnect to run the SQL.
 
Change Operations for Library type Objects: 
 
Using the RUN SQL Scripts tool, run the following SQL Statement:
 
SELECT object as object_name_library_member, COUNT(*) as count 
       FROM TABLE (
            QSYS2.DISPLAY_JOURNAL(
                JOURNAL_LIBRARY => 'QSYS',
                JOURNAL_NAME => 'QAUDJRN',
                STARTING_RECEIVER_NAME => '*CURCHAIN', JOURNAL_ENTRY_TYPES => 'ZC', STARTING_TIMESTAMP => '2022-08-03 06:00:00',
                ENDING_TIMESTAMP => '2022-08-04 08:39:00')
        ) AS X
        where path_name is null
           group by object 
           order by count desc
image-20220804075147-3
 
Notes:
  • Modify the time and date on the SQL.
  • If Hexadecimal data is displayed on the report, you need to change the JDBC connection properties to translate the CCSID to 65535. On the Run SQL tool, select the Connection Menu > Connected > Edit JDBC Connection > Translation and click on Translate CCSID 65535. Save the connection and reconnect to run the SQL.
 
Use Operations for IFS Objects: 
 
Using the RUN SQL Scripts tool, run the following SQL Statement:
 
SELECT Path_name AS IFS_Object_name,
       COUNT(*) AS zr_count
    FROM TABLE (
            QSYS2.DISPLAY_JOURNAL(
                JOURNAL_LIBRARY => 'QSYS',
                JOURNAL_NAME => 'QAUDJRN',
                STARTING_RECEIVER_NAME => '*CURCHAIN', JOURNAL_ENTRY_TYPES => 'ZR', STARTING_TIMESTAMP => '2022-08-01 06:00:00',
                ENDING_TIMESTAMP => '2022-08-04 08:39:00')
        ) AS X
    WHERE path_name IS NOT null
    GROUP BY path_name
    ORDER BY zr_count DESC
image-20220804074337-2
 
Notes:
  • Modify the time and date on the SQL
  • If Hexadecimal data is displayed on the report, you need to change the JDBC connection properties to translate the CCSID to 65535. On the Run SQL tool, select the Connection Menu > Connected > Edit JDBC Connection > Translation and click on Translate CCSID 65535. Save the connection and reconnect to run the SQL.
 
Use Operations for Library type Objects: 
Using the RUN SQL Scripts tool, run the following SQL Statement:
 
SELECT object as object_name_library_member, COUNT(*) as count 
       FROM TABLE (
            QSYS2.DISPLAY_JOURNAL(
                JOURNAL_LIBRARY => 'QSYS',
                JOURNAL_NAME => 'QAUDJRN',
                STARTING_RECEIVER_NAME => '*CURCHAIN', JOURNAL_ENTRY_TYPES => 'ZR', STARTING_TIMESTAMP => '2022-08-03 06:00:00',
                ENDING_TIMESTAMP => '2022-08-04 08:39:00')
        ) AS X
        where path_name is null
           group by object 
           order by count desc
image-20220804075821-1
 
Notes
  • Modify the time and date on the SQL.
  • If Hexadecimal data is displayed on the report, you need to change the JDBC connection properties to translate the CCSID to 65535. On the Run SQL tool, select the Connection Menu > Connected > Edit JDBC Connection > Translation and click on Translate CCSID 65535. Save the connection and reconnect to run the SQL.
     
More information on the DISPLAY_JOURNAL table function can be found on the link:

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHyAAM","label":"Security"}],"ARM Case Number":"TS010167295","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"and future releases;7.2.0;7.3.0;7.4.0;7.5.0"}]

Document Information

Modified date:
26 November 2025

UID

ibm16610275

Page Feedback