AUDIT_JOURNAL_CP table function

The AUDIT_JOURNAL_CP table function returns rows from the audit journal that contain information from the CP (User Profile Changes) journal entries.

Every audit journal table function shares a common authorization requirement and a common set of parameters. These are described in AUDIT JOURNAL table function common information.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.

Table 1. AUDIT_JOURNAL_CP table function
Column Name	Data Type	Description
The first columns returned by this table function are from the common audit journal entry header. See Common columns returned from the audit journal entry header for the column definitions. After the common columns are the following columns that describe the entry specific data for the CP audit journal entry.
ENTRY_TYPE	CHAR(1)	The type of entry. A Change to a user profile
ENTRY_TYPE_DETAIL	VARCHAR(200)	Descriptive text that corresponds to the entry type.
USER_PROFILE	VARCHAR(10)	The user profile that was changed.
COMMAND_TYPE	CHAR(3)	The type of command used. CHG Change User Profile (CHGUSRPRF) or Change Expiration Scd Entry (CHGEXPSCDE) commands CRT Create User Profile (CRTUSRPRF) command DST QSECOFR password reset using DST RPA Reset Profile Attributes (QSYRESPA) API RST Restore User Profile (RSTUSRPRF) command SQL QSYS2/SET_SERVER_SBS_ROUTING() Db2®® for i procedure
STATUS	VARCHAR(9)	User profile status. DISABLED The user profile cannot be used ENABLED The user profile is valid Contains the null value if the value was not changed.
PASSWORD_CHANGED	VARCHAR(3)	Whether the password changed. YES Password changed Contains the null value if the value was not changed.
NO_PASSWORD_INDICATOR	VARCHAR(3)	Whether the password is NONE. YES Password is NONE Contains the null value if the value was not changed.
PASSWORD_EXPIRED	VARCHAR(4)	Whether the password is expired. NO Password is not expired YES Password is expired Contains the null value if the value was not changed.
LOCAL_PASSWORD_MANAGEMENT	VARCHAR(4)	Specifies whether the user profile password should be managed locally. NO The password is not managed on the local system. YES The password is managed on the local system. Contains the null value if the value was not changed.
PASSWORD_CONFORMANCE	VARCHAR(8)	Indicates whether the new password conforms to the password composition rules. EXITPGM Checked but does not conform because of an exit program response. NOCHECK Not checked; password was changed. NONE Not checked; NONE was specified for the new password. PASSED Checked and conforms. SYSVAL Checked but does not conform because of a system value based rule. Contains the null value if PASSWORD_CHANGED is not YES.
BLOCK_PASSWORD_CHANGE	VARCHAR(7)	Specifies the value that the block password change has been changed to. 1-99 Blocked hours. NONE No block period. SYSVAL The system value QPWDCHGBLK is used. Contains the null value if the value was not changed.
PASSWORD_EXPIRATION_INTERVAL	VARCHAR(7)	Specifies the value that the password expiration interval has been changed to. 1-366 The size of the expiration interval in days. NOMAX No expiration interval. SYSVAL The system value QPWDEXPITV is used. Contains the null value if the value was not changed.
USER_EXPIRATION_DATE	VARCHAR(10)	Specifies the date when the user profile expires. The user profile is automatically disabled or deleted on this date. Can contain the special value *NONE to indicate the user profile does not expire. Contains the null value if the value was not changed.
USER_EXPIRATION_ACTION	CHAR(3)	The action performed on the profile when it expires. This value is always DSB when using the CRTUSRPRF and CHGUSRPRF commands. When using the CHGEXPSCDE command, this value is one of the following values. DLT The profile is deleted when it expires. DSB The profile is disabled when it expires. Contains the null value if the value was not changed.
OWNED_OBJECT_OPTION	VARCHAR(7)	The type of operation performed on the objects owned by the expiring profile. The owned object option value is specified on the OWNOBJOPT parameter of the CHGEXPSCDE ACTION(DELETE) command. CHGOWN The owned objects for the user profile have ownership transferred to the new owner user profile. The user profile is deleted if the transfer of all owned objects is successful. DLT The owned objects for the user profile are deleted. The user profile is deleted if the deletion of all owned objects is successful. NODLT The owned objects for the user profile are not changed, and the user profile is not deleted if the user owns any objects. Contains the null value if USER_EXPIRATION_ACTION is not DLT or if the value was not changed.
OWNED_OBJECT_OPTION_NEW_OWNER	VARCHAR(10)	The profile that will own all of the objects owned by the expiring profile. Contains the null value if OWNED_OBJECT_OPTION is not *CHGOWN or if the value was not changed.
PRIMARY_GROUP_OPTION	VARCHAR(7)	The type of operation performed on the objects that have the expiring user profile as their primary group. The primary group option value is specified on the PGPOPT parameter of the CHGEXPSCDE ACTION(DELETE) command. CHGPGP The objects the user profile is the primary group for are transferred to the new primary group user profile. The user profile is deleted if the transfer of all objects is successful. *NOCHG The objects the user profile is the primary group for do not change, and the user profile is not deleted if the user is the primary group for any objects. Contains the null value if USER_EXPIRATION_ACTION is not DLT or if the value was not changed.
NEW_PRIMARY_GROUP	VARCHAR(10)	The profile that will become the new primary group of the objects for which the expiring profile is the primary group. Can contain the following special value: NONE All of the objects for which the expiring user is the primary group will no longer have a primary group. Contains the null value when PRIMARY_GROUP_OPTION is not CHGPGP or if the value was not changed.
NEW_PRIMARY_GROUP_AUTHORITY	VARCHAR(8)	The authority the new primary group has to the object. ALL The new primary group has ALL authority to the object. CHANGE The new primary group has CHANGE authority to the object. EXCLUDE The new primary group has EXCLUDE authority to the object. OLDPGP The new primary group has the same authority to the object as the old primary group. PRIVATE The new primary group has the same authority to the object as its private authority to the object was. USE The new primary group has USE authority to the object. Contains the null value when PRIMARY_GROUP_OPTION is not CHGPGP or NEW_PRIMARY_GROUP is NONE or if the value was not changed.
USER_CLASS_NAME	VARCHAR(7)	The user class of the user. PGMR Programmer SECADM Security administrator SECOFR Security officer SYSOPR System operator *USER User Contains the null value if the value was not changed.
SPECIAL_AUTHORITIES	VARCHAR(72)	Current® list of special authorities. This is a single string containing the list of special authorities separated by one blank. Contains the null value if the value was not changed.
ALLOBJ	VARCHAR(3)	Current ALLOBJ special authority YES Profile has ALLOBJ special authority Contains the null value if the value was not changed.
JOBCTL	VARCHAR(3)	Current JOBCTL special authority YES Profile has JOBCTL special authority Contains the null value if the value was not changed.
SAVSYS	VARCHAR(3)	Current SAVSYS special authority YES Profile has SAVSYS special authority Contains the null value if the value was not changed.
SECADM	VARCHAR(3)	Current SECADM special authority YES Profile has SECADM special authority Contains the null value if the value was not changed.
SPLCTL	VARCHAR(3)	Current SPLCTL special authority YES Profile has SPLCTL special authority Contains the null value if the value was not changed.
SERVICE	VARCHAR(3)	Current SERVICE special authority YES Profile has SERVICE special authority Contains the null value if the value was not changed.
AUDIT	VARCHAR(3)	Current AUDIT special authority YES Profile has AUDIT special authority Contains the null value if the value was not changed.
IOSYSCFG	VARCHAR(3)	Current IOSYSCFG special authority YES Profile has IOSYSCFG special authority Contains the null value if the value was not changed.
PREVIOUS_SPECIAL_AUTHORITIES	VARCHAR(72)	Previous list of special authorities. This is a single string containing the list of special authorities separated by one blank. Contains the null value if the value was not changed.
PREVIOUS_ALLOBJ	VARCHAR(3)	Previous ALLOBJ special authority YES Profile had ALLOBJ special authority Contains the null value if the value was not changed.
PREVIOUS_JOBCTL	VARCHAR(3)	Previous JOBCTL special authority YES Profile had JOBCTL special authority Contains the null value if the value was not changed.
PREVIOUS_SAVSYS	VARCHAR(3)	Previous SAVSYS special authority YES Profile had SAVSYS special authority Contains the null value if the value was not changed.
PREVIOUS_SECADM	VARCHAR(3)	Previous SECADM special authority YES Profile had SECADM special authority Contains the null value if the value was not changed.
PREVIOUS_SPLCTL	VARCHAR(3)	Previous SPLCTL special authority YES Profile had SPLCTL special authority Contains the null value if the value was not changed.
PREVIOUS_SERVICE	VARCHAR(3)	Previous SERVICE special authority YES Profile had SERVICE special authority Contains the null value if the value was not changed.
PREVIOUS_AUDIT	VARCHAR(3)	Previous AUDIT special authority YES Profile had AUDIT special authority Contains the null value if the value was not changed.
PREVIOUS_IOSYSCFG	VARCHAR(3)	Previous IOSYSCFG special authority YES Profile had IOSYSCFG special authority Contains the null value if the value was not changed.
GROUP_PROFILE_NAME	VARCHAR(10)	The user's group profile. Can contain the special value *NONE to indicate the user is not a member of any group profiles. Contains the null value if the value was not changed.
GROUP_OWNER	VARCHAR(7)	Owner of objects created as a member of a group profile. GRPPRF The user's group profile becomes the owner USRPRF The user's user profile becomes the owner Contains the null value if the value was not changed.
GROUP_AUTHORITY	VARCHAR(8)	The authority the user's group profile has to objects the user creates. ALL ALL authority CHANGE CHANGE authority EXCLUDE The group is denied access NONE No authority is granted to the group profile *USE USE authority Contains the null value if the value was not changed.
GROUP_AUTHORITY_TYPE	VARCHAR(8)	The value of the group authority type parameter. PGP The group becomes the object's primary group and is given the authority specified in GROUP_AUTHORITY. PRIVATE The authority defined in the GROUP_AUTHORITY is assigned as private authority to the group profile. Contains the null value if the value was not changed.
SUPPLEMENTAL_GROUP_LIST	VARCHAR(150)	The names of up to 15 supplemental group profiles for the user. Can contain the special value *NONE to indicate the user has no supplemental groups. Each entry except for the last one is padded with blanks to fill 10 characters. Contains the null value if the value was not changed.
INITIAL_PROGRAM_LIBRARY	VARCHAR(10)	The library where the initial program is found. Can contain the special value LIBL. Contains the null value if INITIAL_PROGRAM is NONE or if the value was not changed.
INITIAL_PROGRAM	VARCHAR(10)	The user's initial program. Can contain the special value *NONE to indicate the user has no initial program. Contains the null value if the value was not changed.
INITIAL_MENU_LIBRARY	VARCHAR(10)	The library where the initial menu is found. Can contain the special value LIBL. Contains the null value if INITIAL_MENU is SIGNOFF or if the value was not changed.
INITIAL_MENU	VARCHAR(10)	The user's initial menu. Can contain the special value *SIGNOFF to indicate the user is limited to running the initial program specified for this profile. Contains the null value if the value was not changed.
CURRENT_LIBRARY_NAME	VARCHAR(10)	The user's current library. Can contain the special value *CRTDFT to indicate the user has no current library. Contains the null value if the value was not changed.
HOME_DIRECTORY	VARGRAPHIC(5000) CCSID 1200	Path name of the home directory or the following special value: *USRPRF The home directory assigned to the user will be /home/USRPRF, where USRPRF is the name of the user profile. Contains the null value if the value was not changed.
LOCALE_PATH_NAME	VARGRAPHIC(5000) CCSID 1200	Path name of the locale or one of the following special values: C The C locale path name is assigned to this user. NONE No locale path name is assigned to this user. POSIX The POSIX locale path name is assigned to this user. SYSVAL The system value, QLOCALE, is used to determine the locale path name to be assigned to this user. Contains the null value if the value was not changed.
LIMIT_CAPABILITIES	VARCHAR(8)	The value of limited capabilities parameter. NO No limitations. PARTIAL The user can change the initial menu, but cannot change the initial program, current library, or attention key handling program. The user can run commands from command lines. *YES The user cannot change the initial program, initial menu, current library, and attention key handling programs. The user cannot run commands from command lines. Contains the null value if the value was not changed.
ASSISTANCE_LEVEL	VARCHAR(9)	The user interface that will be used. ADVANCED The expert system interface is used. BASIC The Operational Assistant user interface is used. INTERMED The system interface is used. SYSVAL The system value, QASTLVL, is used to determine the user interface that will be used. Contains the null value if the value was not changed.
USER_OPTIONS	VARCHAR(70)	The level of help information detail to be shown and the default function of the Page Up and Page Down keys. This column can contain up to seven values, each ten characters long. CLKWD Parameter keywords are shown instead of the possible parameter values when a control language (CL) command is prompted. EXPERT More detailed information is shown when the user is performing display and edit options to define or change the system. HLPFULL Help text is shown on a full display rather than in a window. NONE Detailed information is not shown. NOSTSMSG Status messages are not displayed when sent to the user. PRTMSG A message is sent to this user's message queue when a spooled file for this user is printed or held by the printer writer. ROLLKEY The actions of the Page Up and Page Down keys are reversed. STSMSG Status messages are displayed when sent to the user. Contains the null value if the value was not changed.
SPECIAL_ENVIRONMENT	VARCHAR(7)	The special environment in which the user operates after signing on. NONE The user operates in the IBM® i system environment after signing on the system. S36 The user operates in the System/36 environment after signing on the system. *SYSVAL The system value, QSPCENV, is used to determine the system environment in which the user operates after signing on the system. Contains the null value if the value was not changed.
DISPLAY_SIGNON_INFORMATION	VARCHAR(7)	Indicates if the sign-on information display is shown. NO The sign-on information display is not shown. SYSVAL The system value, QDSPSGNINF, is used to determine whether the sign-on information display is shown. *YES The sign-on information display is shown. Contains the null value if the value was not changed.
LIMIT_DEVICE_SESSIONS	VARCHAR(7)	The number of device sessions allowed for a user is limited. 0 The user is not limited to a specific number of device sessions. This value has the same meaning as NO. 1 The user is limited to a single device session. This value has the same meaning as YES. 2-9 The user is limited to the specified number of device sessions. NO The user is not limited to a specific number of device sessions. SYSVAL The system value, QLMTDEVSSN, is used to determine whether the user is limited to a specific number of device sessions. *YES The user is limited to a single device session. Contains the null value if the value was not changed.
KEYBOARD_BUFFERING	VARCHAR(10)	The keyboard buffering value to be used when a job is initialized for this user profile. NO The type-ahead feature and attention key buffering option are not active. SYSVAL The system value, QKBDBUF, is used to determine the keyboard buffering value. TYPEAHEAD The type-ahead feature is active, but the attention key buffering option is not. YES The type-ahead feature and attention key buffering option are active. Contains the null value if the value was not changed.
MAXIMUM_ALLOWED_STORAGE	BIGINT	The amount of auxiliary storage (in kilobytes) assigned to store permanent objects owned by this user profile in the system auxiliary storage pool (ASP) and on all the basic ASPs combined. In addition, this value also controls the maximum amount of auxiliary storage that can be used to store permanent objects owned by this user profile on each Independent ASP (IASP). A value of -1 indicates *NOMAX. Contains the null value if the value was not changed.
PRIORITY_LIMIT	INTEGER	The value of the priority limit parameter. Values are 0 to 9. Contains the null value if the value was not changed.
JOB_DESCRIPTION_LIBRARY	VARCHAR(10)	The library where the job description is found. Can contain the special value *LIBL. Contains the null value if the value was not changed.
JOB_DESCRIPTION	VARCHAR(10)	The job description used for jobs that start through subsystem work station entries whose job description parameter values indicate the user JOBD(*USRPRF). Contains the null value if the value was not changed.
ALTERNATE_SUBSYSTEM_NAME	VARCHAR(10)	The alternative subsystem that will be used for this user, instead of the default subsystem, whenever a connection is initiated to the server job specified in ALTERNATE_SERVER_JOB_NAME. Contains the null value when COMMAND_TYPE is not SQL or if the value was not changed.
ALTERNATE_SERVER_JOB_NAME	VARCHAR(10)	When a connection to this server is initiated for this user it will be routed to the subsystem specified in the ALTERNATIVE_SUBSYSTEM_NAME column. Can contain the special value *ALL. To understand the Server Job Name mapping to server names and the default subsystem use, see Server table. Contains the null value when COMMAND_TYPE is not SQL or if the value was not changed.
ACCOUNTING_CODE	VARCHAR(15)	The accounting code that is associated with this user profile or the following special value: *BLANK An accounting code of 15 blanks is assigned to this user profile. Contains the null value if the value was not changed.
MESSAGE_QUEUE_LIBRARY	VARCHAR(10)	The library where the message queue is found. Can contain the special value *LIBL. Contains the null value if the value was not changed.
MESSAGE_QUEUE	VARCHAR(10)	The message queue to which messages are sent or the following special value: *USRPRF A message queue with the same name as the user profile is used as the message queue for this user. The message queue is located in the QUSRSYS library. Contains the null value if the value was not changed.
MESSAGE_QUEUE_DELIVERY_METHOD	VARCHAR(7)	How messages sent to the message queue for this user are to be delivered. BREAK The job to which the message queue is assigned is interrupted when a message arrives at the message queue. DFT The default reply to the inquiry message is sent. HOLD The messages are held in the message queue until they are requested by the user or program. NOTIFY The job to which the message queue is assigned is notified when a message arrives at the message queue. Contains the null value if the value was not changed.
MESSAGE_QUEUE_SEVERITY	INTEGER	The lowest severity code that a message can have and still be delivered to a user in break or notify mode. This is a value from 0 to 99. Contains the null value if the value was not changed.
PRINT_DEVICE	VARCHAR(10)	The default printer device for this user or one of the following special values: SYSVAL The system value, QPRTDEV, is used to determine the printer device. WRKSTN The printer assigned to the user's work station is used. Contains the null value if the value was not changed.
OUTPUT_QUEUE_LIBRARY	VARCHAR(10)	The library where the output queue is found. Can contain the special value *LIBL. Contains the null value if the value was not changed.
OUTPUT_QUEUE	VARCHAR(10)	The output queue to be used by this user profile or one of the following special values: DEV The output queue associated with the printer specified for the Printer Device is used. WRKSTN The output queue assigned to the user's work station is used. Contains the null value if the value was not changed.
ATTENTION_KEY_HANDLING_ PROGRAM_LIBRARY	VARCHAR(10)	The library where the ATTN program is found. Can contain the special value *LIBL. Contains the null value if the value was not changed.
ATTENTION_KEY_HANDLING_PROGRAM	VARCHAR(10)	The program to be used as the Attention (ATTN) key handling program for this user or one of the following special values: ASSIST The Operational Assistant ATTN key handling program, QEZMAIN, is used. NONE No ATTN key handling program is used by this user. *SYSVAL The system value, QATNPGM, is used to determine the ATTN key handling program. Contains the null value if the value was not changed.
SORT_SEQUENCE_TABLE_LIBRARY	VARCHAR(10)	The the library where the sort sequence table is found. Can contain the special value *LIBL. Contains the null value if the value was not changed.
SORT_SEQUENCE_TABLE	VARCHAR(10)	The sort sequence table to be used for string comparisons for this user profile or one of the following special values: HEX A sort sequence table is not used. The hexadecimal values of the characters are used to determine the sort sequence. LANGIDSHR A shared-weight sort table is used. LANGIDUNQ A unique-weight sort table is used. SYSVAL The system value, QSRTSEQ, is used to determine the sort sequence table. Contains the null value if the value was not changed.
LANGUAGE_ID	VARCHAR(7)	The language identifier to be used for this user profile or the following special value: *SYSVAL The system value, QLANGID, is used to determine the language identifier. Contains the null value if the value was not changed.
COUNTRY_OR_REGION_ID	VARCHAR(7)	The country or region identifier to be used for this user profile or the following special value: *SYSVAL The system value, QCNTRYID, is used to determine the country or region ID. Contains the null value if the value was not changed.
CCSID	VARCHAR(7)	The coded character set identifier (CCSID) to be used for this user profile. *SYSVAL The system value, QCCSID, is used to determine the CCSID. Contains the null value if the value was not changed.
CHARACTER_IDENTIFIER_CONTROL	VARCHAR(9)	The character identifier control (CHRIDCTL) for the job. DEVD Performs the same function as it does on the CHRID parameter for display files, printer files, and panel groups. JOBCCSID Performs the same function as it does on the CHRID parameter for display files, printer files, and panel groups. *SYSVAL The system value, QCHRIDCTL, is used to determine the CHRIDCTL for the job. Contains the null value if the value was not changed.
LOCALE_JOB_ATTRIBUTES	VARCHAR(60)	The job attributes that are to be taken from the locale when the job is initiated. This column can contain up to six values, each ten characters long. CCSID The coded character set identifier from the locale is used. DATFMT The date format from the locale is used. DATSEP The date separator from the locale is used. DECFMT The decimal format from the locale is used. NONE No job attributes are taken from the locale. SRTSEQ The sort sequence from the locale is used. SYSVAL The system value, QSETJOBATR, is used to determine which job attributes are taken from the locale. TIMSEP The time separator from the locale is used. Contains the null value if the value was not changed.
DOCUMENT_PASSWORD_CHANGED	VARCHAR(3)	Indicates if the document password has been changed. YES Document password changed. Contains the null value if the value was not changed.
DOCUMENT_PASSWORD_NONE	VARCHAR(3)	Indicates if the document password is NONE. YES Document password is NONE. Contains the null value if the value was not changed.
EIM_ID	VARCHAR(128)	Enterprise Identity Mapping (EIM) identifier name or the following special value: *USRPRF The EIM identifier is the same name as the user profile. Contains the null value if no EIM values were changed.
EIM_ASSOCIATION_TYPE	VARCHAR(7)	EIM association type. ADMIN Administrative association. ALL All association types. SOURCE Source association. TARGET Target association. *TGTSRC Target and source associations. Contains the null value if no EIM values were changed.
EIM_ASSOCIATION_ACTION	VARCHAR(8)	EIM association action. ADD Add an association. REMOVE Remove an association. *REPLACE Associations of the specified type will be removed from all EIM identifiers that have an association for this user profile and local EIM registry. A new association will be added to the specified EIM identifier. Contains the null value if no EIM values were changed.
CREATE_EIM_ID	VARCHAR(11)	Indicates whether the EIM identifier should be created if it does not exist. CRTEIMID EIM identifier gets created if it does not exist. NOCRTEIMID EIM identifier does not get created. Contains the null value if no EIM values were changed.
USER_ID_NUMBER	VARCHAR(10)	The UID for the user. Can contain the special value *GEN to indicate the uid number is generated for the user. Contains the null value if the value was not changed.
GROUP_ID_NUMBER	VARCHAR(10)	The GID for the user. Can contain the special values GEN to indicate the gid number is generated for the user or NONE to indicate the user profile does not have a group profile. Contains the null value if the value was not changed.

Example

List any user profiles that were created in the last 6 months.


SELECT USER_PROFILE FROM TABLE(
  SYSTOOLS.AUDIT_JOURNAL_CP(  
      STARTING_TIMESTAMP => CURRENT TIMESTAMP - 6 MONTHS
  )
)
WHERE COMMAND_TYPE = 'CRT';