Home > PT, Security > Record Audit

Record Audit

PeopleTools ver 8.45 

To audit an already exisiting PeopleSoft record, we need to create a record definition and SQL table in which we store audit information. When creating the audit record, remove any attributes such as
PARENT records, Query Security Records, and PeopleCode. The easiest way to create an audit table is to open the record definition of the base record that you wish to audit. Save it as a new record, prefaced with AUDIT_.

Remove the all edit and key attributes from the newly saved record. Also remove any attributes such as
PARENT records, Query Security Records, and PeopleCode. Add to the top of the audit record the following three special audit-specific fields in the same order given below.

  • AUDIT_OPRID
  • AUDIT_STAMP
  • AUDIT_ACTN
  • AUDIT_RECNAME

In most cases you should include AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN. The AUDIT_STAMP must be given the attribute AUTOUPDATE. You might also add AUDIT_RECNAME if you are creating an audit table to audit more than one record definition.

Make these fields required and keys. Then build the table. Make sure you can query this table using sql editor.

The purpose of each audit specific field is explained below.

AUDIT_OPRID – Identifies the user who caused the system to trigger the audits—either by performing an add, change, or delete to an audited field.
AUDIT_STAMP – Identifies the date and time the audit was triggered.
AUDIT_ACTN  – Indicates the type of action that the system audited. Possible actions include:

  • A: Row inserted.
  • D: Row deleted.
  • C: Row changed (updated), but no key fields changed. The system writes old values to the audit table.
  • K: Row changed (updated), and at least one key field changed. The system writes old values to the audit table.
  • N: Row changed (updated), and at least one key field changed. The system writes new values to the audit table.

AUDIT_RECNAME –  Identifies the name of the record definition that was audited.

The audit table does not have to include all the columns of the base table. In fact, for performance reasons, it’s best to only include those fields in your audit record that are deemed

Open the record properties for the record you want to audit, Under the Record Audit, we have the following options

Record Name – Specify the user-defined audit record.

Audit Options – following are the audit options to choose for auditing the record.

  • Add –  Inserts an audit table row whenever a new row is added to the table underlying this record definition.
  • Change –  Inserts one or two audit table rows whenever a row is changed on the table underlying this record definition.
  • Selective –  Inserts one or two audit table rows whenever a field that is also included in the record definition for the audit table is changed.
  • Delete –  Inserts an audit table row whenever a row is deleted from the table underlying this record definition.

Now perform online transactions on the audited table, query the audit table to know what is changed and who changed it at what time.

Advertisements
Categories: PT, Security Tags:
  1. Jason
    March 24, 2009 at 8:41 am

    Hi,

    How do I find out how to identify all the fileds that have been flagged for auditing in PS?

    Thanks in advance.

  2. balaglobal
    March 24, 2009 at 12:49 pm

    Two ways to find the fields being flagged for Audit:

    1. When a field is being audited a row will be inserted into PSAUDIT table. So indirect way of finding the fields that have been flagged for auditing is by using the following sql. Obvioulsy, this statement will not give the fields which are flagged for audit, but never functionally used.

    SELECT
    RECNAME
    ,FIELDNAME
    FROM PSAUDIT
    GROUP BY RECNAME, FIELDNAME

    2. To find all fields being flagged for auditing, you may use the following SQL
    SELECT
    RECNAME
    ,FIELDNAME
    FROM PSRECFIELD
    WHERE USEEDIT IN (‘8388617′,’8388737′,’8389633′,’8389769′,’8389641′,’8389761’)

  3. Anoop
    June 13, 2009 at 7:50 am

    Enabling Audit on tables will only audit changes done thru API/on-line pages. How to Audit changes done thru SQLEXEC function?

  4. November 19, 2009 at 11:52 pm

    Should you key your audit records by AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN and if relevant AUDIT_RECORD? I realise there may be cases where you get key violations, but assuming that won’t happen (as the AUDIT_STAMP is granular enough to uniquely identify each row), is this a good idea? I figure it would help with performance as they AUDIT* fields would be indexed.

  5. balaglobal
    November 20, 2009 at 1:49 am

    I have not tried it, I would definitely try, if the auditing is hampering the online performance. But without indexes it may be hard for searching and reporting.

  6. Raj Manickam
    January 15, 2010 at 4:25 pm

    Hello Bala,

    Thank you for the blog – I find it very useful!

    After going through these steps, I set up audit of ‘Add’ and ‘Delete’ of JOB. I notice, however, that this process captures only the fact that something was added / deleted, without any further information on *what* was added / deleted (i.e. the primary key values are not available). I would consider this a fatal flaw, and misses the point of the audit. Am I missing something, or could you suggest additional action to capture the primary key values, as well?

    Thanks in advance!

  7. balaglobal
    January 15, 2010 at 10:32 pm

    Raj,
    you need to keep all the fields you want to audit in the audit record.

  8. Tom
    January 20, 2010 at 11:40 pm

    I believe I have followed the procedure to enable auditing of my records but nothing shows up in the audit tables. There isn’t even anything in PSAUDIT. Is there some global setting to switch on the auditing capability? Do I need to restart something?

  9. Sheri
    February 16, 2010 at 9:28 pm

    We have an application engine. We would like the data that is updated to be inserted into the audit record. Does audit records work with that? If how does that work.

  10. balaglobal
    February 16, 2010 at 11:27 pm

    No, it does not work with application engine.

    You may write a stored procedure at the database level, to audit any SQL statement issued against the database.

  11. dhananjay
    May 6, 2010 at 3:11 am

    Hello friends,

    We are using PeopleSoft9.0 and I have research task to audit user activity for any changes to roles, permissions or user profiles he/she may make.
    I’M very new to peoplesoft hencce wanted practical steps to leanr:

    1) How to identify which tables?

    2) How to create Audit Records using peopelsoft,

    3) how to define triggers and test.

    Kind Regards
    Dhananjay

  12. balaglobal
    May 6, 2010 at 2:52 pm

    1. Basically the table where the data is getting inserted is the table you need to audit. (Ex: To audit role changes the table need to be audited is PSROLEUSER_VW)

    2. Using Application Desginer. (Ex: Create a new record AUDIT_ROLEUSER with the following fields AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, OPRID, ROLENAME, DYNAMIC_SW. Build the table after creating it)

    3. Add the newly created audit record to the table need to audited in record property. (Open PSROLEUSER_VW record, open its record property and add AUDIT_ROLEUSER as Audit record and choose proper audit options, add,change,selective and/or delete) .Change the data (like try adding or deleting roles to a user profile). Query PS_AUDIT_ROLEUSER table to see the changed data.

  13. September 20, 2010 at 12:49 pm

    Thank you Bala… for a complete article on Audit records.

  14. Arden
    October 19, 2010 at 8:43 pm

    I have created audit records for the Address, Name and Job tables. I created queries to return audit actions for a given data range using prompts on the audit_stamp field. I was able to do this with no problem for the Name and Job table, but I keep getting an error when I try to prompt on the Date field in the Audit_Address record query. The query works just fine if I prompt on a different field, but audit_stamp is giving me problems. Changing the prompt to a Date field instead of a DateTime field doesn’t help. Any suggestions?

  15. Marci
    March 4, 2011 at 6:28 pm

    HI Gang…

    I have successfully created an audit table that is logging Adds and Deletes made to the PSROLECLASS table. My problem is when I try do make any changes on an existing row. I get a ‘Data being added conflicts with existing data’ error. I have Add, Change, Selective & Delete checked off for my audit options [and have tried varying these options].

    Any suggestions?

    Thanks!!!!
    Marci

  16. Marci
    March 4, 2011 at 6:29 pm

    HI Gang…

    I have successfully created an audit table that is logging Adds and Deletes made to the PSROLECLASS table. My problem is when I try do make any changes on an existing row. I get a ‘Data being added conflicts with existing data’ error. I have Add, Change, Selective & Delete checked off for my audit options [and have tried varying these options].

    Any suggestions?

    Thanks!!!!
    Marci

  17. Bala
    March 6, 2011 at 4:50 am

    Marci,
    please check the key structure of audit record and make sure that it is as mentioned in the above blog.

    • Marci
      March 7, 2011 at 4:53 pm

      I am auditing the PSROLECLASS table. The Audit Table I have created is set as follows:
      Audit_Oprid Key – Required
      Audit_Stamp Key – Required
      Audit_Actn Key – Required
      Rolename
      Classid

      If the audit table is brand new, void of any rows, it will accept the ‘K’ and ‘N’ action during a change. But once I do an Add or Delete I can no longer log any Changes….I get the ‘data integrity’ error.

      Thanks again!
      Marci

  18. Chris
    August 23, 2012 at 9:55 am

    I have created the audit log record, and it can insert the log value into the table, but I found that the audit_stamp value is null. May I know which part did I missing ?

  19. Aman
    July 10, 2017 at 8:54 am

    I have grid on the page I have tagged audit record for this , it works fine for the add delete and changes but the moment i perform these action on more than one row it gives error of in consistent data base error

    • Bala
      July 11, 2017 at 4:19 am

      Try ‘commit’ after each action

  1. September 20, 2008 at 8:07 am
  2. November 7, 2008 at 8:37 am
  3. October 14, 2013 at 2:00 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: