Skip to main content

Sample Queries of Audit Trail

Zuora

  • 日本語のコンテンツは機械翻訳されており、補助的な参照を目的としています。機械翻訳の精度は保証できません。英語版が正となります。また、現時点では検索機能は日本語での検索をサポートしていません。翻訳に関するフィードバックについては、docs@zuora.comに送信してください。

Sample Queries of Audit Trail

  1. Last updated
    Jan 23, 2025
  2. Save as PDF

The Audit Trail records are stored in the following tables:

  • auditloginevent
  • auditsettingchangeevent
  • auditobjectchangeevent

You must retrieve Audit Trail records through Data Query. See the following samples on how to generate Audit Trail reports.

Retrieve the login history

The following use case retrieves the records in the login history during a date range.

  1. Submit a data query job through UI or API with the following SQL query:
    view sourceprint?
    01SELECT username       AS Username,
    02       logintype      AS Type,
    03       status         AS Status,
    04       timestamp      AS Timestamp,
    05       browsertype    AS Browser,
    06       browserversion AS BrowserVersion,
    07       ipaddress      AS IPAddress
    08FROM   auditloginevent
    09WHERE  year = 2022
    10       AND month >= 10
    11       AND month < 11
    12ORDER  BY timestamp DESC
    13LIMIT  100000
  2. Check the status of the query job through UI or Get data query job API operation.
  3. Download the query result when the job is completed. See the following snippet of the query result. 
    Username,Type,Status,Timestamp,Browser,BrowserVersion,IPAddress
audit-trail@example.com,PASSWORD,Success,2022-10-30T04:29:06.571Z,Chrome,77.0,67.180.238.89
audit-trail@example.com,PASSWORD,Success,2022-10-29T16:34:09.092Z,Chrome,77.0,216.129.127.1
audit-trail@example.com,PASSWORD,Success,2022-10-29T16:34:09.042Z,Chrome,77.0,216.129.127.1
audit-trail@example.com,PASSWORD,Success,2022-10-29T01:15:38.693Z,Chrome,77.0,103.104.131.160
zconnect.api.9439@example.com,PASSWORD,Success,2022-10-17T18:50:16.763Z,RestBiz,0.0,54.187.61.166
audit-trail@example.com,PASSWORD,Success,2022-10-17T18:49:13.393Z,Chrome,77.0,67.180.238.89
audit-trail@example.com,PASSWORD,Success,2022-10-17T18:38:24.001Z,Chrome,77.0,67.180.238.89
audit-trail@example.com,CLIENT_CREDENTIALS,Success,2022-10-17T03:43:05.423Z,RestBiz,0.0,103.104.131.160
audit-trail@example.com,PASSWORD,Success,2022-10-17T03:33:34.209Z,Chrome,77.0,103.104.131.160
audit-trail@example.com,PASSWORD,PasswordExpired,2022-10-17T03:29:43.207Z,Chrome,77.0,103.104.131.160

Retrieve the setting changes

The following use case retrieves the setting change records.

  1. Submit a data query job through UI or API with the following SQL query:
    view sourceprint?
    01SELECT username      AS UpdatedBy,
    02       action        AS Action,
    03       settingtype   AS SettingType,
    04       attributename AS Attribute,
    05       oldvalue      AS OldValue,
    06       newvalue      AS NewValue,
    07       timestamp     AS Timestamp
    08FROM   auditsettingchangeevent
    09WHERE  namespace <> 'UserManagement'
    10       AND year = 2022
    11       AND month = 11
    12ORDER  BY timestamp DESC
    13LIMIT  100000
  2. Check the status of the query job through UI or Get data query job API operation.
  3. Download the query result when the job is completed. See the following snippet of the query result. 
    UpdatedBy,Action,SettingType,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,UPDATED,TenantProperty,PROPERTY_DISALLOW_BLANK_ACCOUNTING_CODE,false,true,2022-11-10T09:30:52Z
audit-trail@zuora.com,UPDATED,TenantProperty,PROPERTY_DISALLOW_BLANK_ACCOUNTING_CODE,true,false,2022-11-10T09:29:47Z
audit-trail@zuora.com,UPDATED,BillingRules,Enable credit back for removing or canceling one time charges?,false,true,2022-11-05T17:08:53Z
audit-trail@zuora.com,UPDATED,BillingRules,"When prorating a month, assume 30 days in a month or use actual days?",Assume30Days,UseActualDays,2022-11-05T17:08:53Z
audit-trail@zuora.com,UPDATED,BillingRules,Support bill run auto-post?,true,false,2022-11-05T17:08:53Z
audit-trail@zuora.com,UPDATED,BillingRules,"When prorating periods greater than a month, prorate by month first, or by day?",ProrateByDay,ProrateByMonthFirst,2022-11-05T16:56:36Z
audit-trail@zuora.com,UPDATED,BillingRules,"When prorating a month, assume 30 days in a month or use actual days?",UseActualDays,Assume30Days,2022-11-05T16:56:36Z
audit-trail@zuora.com,UPDATED,BillingRules,includeNegativeInvoice,false,true,2022-11-05T16:56:36Z
audit-trail@zuora.com,UPDATED,BillingRules,Enable credit back for removing or canceling one time charges?,true,false,2022-11-05T16:56:36Z

Retrieve the user management setting changes

The following use case retrieves the user management setting change records.

  1. Submit a data query job through UI or API with the following SQL query:
    view sourceprint?
    01SELECT username          AS UpdatedBy,
    02       timestamp         AS Timestamp,
    03       settingtype       AS Type,
    04       settingobjectname AS ObjectName,
    05       newvalue          AS NewValue,
    06       oldvalue          AS OldValue,
    07       namespace         AS Namespace,
    08       action            AS Action,
    09       attributename     AS AttributeName
    10FROM   auditsettingchangeevent
    11WHERE  namespace = 'UserManagement'
    12       AND year = 2022
    13       AND month = 11
    14ORDER  BY timestamp DESC
    15LIMIT  100000
  2. Check the status of the query job through UI or Get data query job API operation.
  3. Download the query result when the job is completed. See the following snippet of the query result. 
    UpdatedBy,Timestamp,Type,ObjectName,NewValue,OldValue,Namespace,Action,AttributeName
audittraile2e@example.com,2022-11-18T07:00:10.286Z,User,34084+user@zuora.com,,Administrator,UserManagement,REMOVED_FROM_COLLECTION,roles
audittraile2e@example.com,2022-11-18T07:00:10.286Z,User,34084+user@zuora.com,Standard User,,UserManagement,ADDED_TO_COLLECTION,roles
audittraile2e@example.com,2022-11-18T06:59:08.413Z,Role,audit_1574060164607_update,,,UserManagement,DELETED,
audittraile2e@example.com,2022-11-18T06:58:08.157Z,Role,audit_1574060164607_update,apiWrite,,UserManagement,ADDED_TO_COLLECTION,permissions
audittraile2e@example.com,2022-11-18T06:58:08.157Z,Role,audit_1574060164607_update,,ui,UserManagement,REMOVED_FROM_COLLECTION,permissions
audittraile2e@example.com,2022-11-18T06:57:06.187Z,Role,audit_1574060164607,desc_update,desc,UserManagement,UPDATED,description
audittraile2e@example.com,2022-11-18T06:57:06.187Z,Role,audit_1574060164607,audit_1574060164607_update,audit_1574060164607,UserManagement,UPDATED,name
audittraile2e@example.com,2022-11-18T06:56:05.777Z,Role,audit_1574060164607,ui,,UserManagement,ADDED_TO_COLLECTION,permissions
audittraile2e@example.com,2022-11-18T06:56:05.418Z,Role,audit_1574060164607,,,UserManagement,CREATED,
audittraile2e@example.com,2022-11-18T06:54:03.701Z,OAuth Client,0fecbd03-9445-4b72-8145-7f813e6b63f5,,,UserManagement,DELETED,

Currently, the UI does not support retrieving permissions for each role. To obtain entitlements for each role, please contact the Zuora Support team at support@zuora.com to request an offline report for auditing purposes. 

Retrieve the business object changes

Administrators can enable or disable auditing objects through Manage Audit Trail settings. Ensure the object to audit is selected in the setting list.

Retrieve Account changes

The following use case retrieves the auditing records of Account changes. If BillToId or SoldToId value is updated or added, the value change is retrieved as additional_info in the query result.

  1. Submit a data query job through UI or API with the following SQL query:
    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       objecttype  AS ObjectType,
    04       objectname  AS ObjectName,
    05       attributeid AS Attribute,
    06       oldvalue    AS OldValue,
    07       newvalue    AS NewValue,
    08       timestamp   AS Timestamp,
    09       ( CASE
    10           WHEN ( ( attributeid = 'BillToId' OR attributeid = 'SoldToId' )
    11                  AND oldvalue <> ''
    12                  AND newvalue <> '' ) THEN (SELECT Concat(c1.firstname, ' ', c1.lastname, ' -> ', c2.firstname, ' ', c2.lastname)
    13                                             FROM   contact c1, contact c2
    14                                             WHERE  c1.id = oldvalue AND c2.id = newvalue)
    15           WHEN ( ( attributeid = 'BillToId' OR attributeid = 'SoldToId' )
    16                  AND oldvalue IS NULL ) THEN (SELECT Concat(firstname, ' ', lastname)
    17                                               FROM   contact
    18                                               WHERE  id = newvalue)
    19           ELSE ''
    20         END )     AS additional_info
    21FROM   auditobjectchangeevent
    22WHERE  objecttype = 'Account'
    23       AND year = 2022
    24       AND month = 11
    25ORDER  BY timestamp DESC, transactionid
    26LIMIT  100000
  2. Check the status of the query job through UI or Get data query job API operation.
  3. Download the query result when the job is completed. See the following snippet of the query result. 
    Username,Action,ObjectType,ObjectName,Attribute,OldValue,NewValue,Timestamp,additional_info
audit-trail@zuora.com,DELETED,Account,testJoy002,,,,2022-11-09T09:04:39.046Z,
audit-trail@zuora.com,UPDATED,Account,testJoy002,Status,Active,Canceled,2022-11-09T09:04:35.386Z,
audit-trail@zuora.com,UPDATED,Account,gary test staging2,TotalDebitMemoBalance,,0.000000000,2022-11-06T16:16:36.303Z,
audit-trail@zuora.com,UPDATED,Account,gary test staging2,Mrr,,12.00,2022-11-06T16:16:36.303Z,
audit-trail@zuora.com,UPDATED,Account,gary test staging2,UnappliedCreditMemoAmount,,0.000000000,2022-11-06T16:16:36.303Z,
audit-trail@zuora.com,UPDATED,Account,gary test staging2,CustomerServiceRepName,,gary test staging2,2022-11-06T16:10:19.557Z,
audit-trail@zuora.com,CREATED,Account,Xi Test,BillToId,,2c92c8fe7569068c01756ede2ee4383a,2022-11-04T11:00:56.173Z,John Doe
audit-trail@zuora.com,CREATED,Account,Xi Test,SoldToId,,2c92c8fe7569068c01756ede2ee4383a,2022-11-04T11:00:56.173Z,Jane Doe
audit-trail@zuora.com,UPDATED,Account,Oscorp123,BillToId,2c92c8fb756388ec017564e530151b3a,2c92c8fb6af31db7016af7dd77dd1c36,2022-11-03T23:12:35.685Z,Tony Stark -> Nick Fury
audit-trail@zuora.com,UPDATED,Account,Oscorp123,SoldToId,2c92c8fb6af31db7016af7dd77dd1c36,2c92c8fb756388ec017564e530151b3a,2022-11-03T23:12:35.685Z,Nick Fury -> Tony Stark

Note that deleting an Account object triggers two sequential object change events. Consequently Audit Trail inserts two records to the auditobjectchangeevent table. The first one records an UPDATED action that changes the status of the Account from Active to Canceled. The second one records a DELETED action that removes that Account. See the first two rows in the preceding query result for an example.

Retrieve Product Catalog changes

The following use case retrieves the auditing records of changes to Product Catalog object hierarchy.

  1. Submit a data query job through UI or API with the following SQL query:
    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       objecttype  AS ObjectType,
    04       objectname  AS ObjectName,
    05       attributeid AS Attribute,
    06       oldvalue    AS OldValue,
    07       newvalue    AS NewValue,
    08       timestamp   AS Timestamp,
    09       ( CASE
    10           WHEN ( objecttype = 'ProductRatePlan' ) THEN (SELECT p.name
    11                                                         FROM   product p,
    12                                                                productrateplan prp
    13                                                         WHERE  prp.id = objectid
    14                                                                AND p.id = prp.productid)
    15           WHEN ( objecttype = 'ProductRatePlanCharge' ) THEN (SELECT CONCAT(p.name, '.', prp.name)
    16                                                               FROM   product p,
    17                                                                      productrateplan prp,
    18                                                                      productrateplancharge prpc
    19                                                               WHERE  prpc.id = objectid
    20                                                                      AND prp.id = prpc.productrateplanid
    21                                                                      AND p.id = prp.productid)
    22           WHEN ( objecttype = 'ProductRatePlanChargeTier' ) THEN (SELECT CONCAT(p.name, '.', prp.name, '.', prpc.name)
    23                                                                   FROM   product p,
    24                                                                          productrateplan prp,
    25                                                                          productrateplancharge prpc,
    26                                                                          productrateplanchargetier prpct
    27                                                                   WHERE  prpct.id = objectid
    28                                                                          AND prpc.id = prpct.productrateplanchargeid
    29                                                                          AND prp.id = prpc.productrateplanid
    30                                                                          AND p.id = prp.productid)
    31           ELSE ''
    32         END ) AS product_catalog_info
    33FROM   auditobjectchangeevent
    34WHERE  (objecttype = 'ProductRatePlan'
    35       OR objecttype = 'ProductRatePlanCharge'
    36       OR objecttype = 'ProductRatePlanChargeTier')
    37       AND year = 2022
    38       AND month = 11
    39ORDER  BY timestamp DESC,
    40          transactionid
    41LIMIT  100000
  2. Check the status of the query job through UI or Get data query job API operation.
  3. Download the query result when the job is completed. See the following snippet of the query result. 
    Username,Action,ObjectType,ObjectName,Attribute,OldValue,NewValue,Timestamp,product_catalog_info
audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,ProductRatePlanChargeId,,2c92c8fe75dff0620175e00ad3b101f7,2022-11-19T10:26:47.385Z,Test.New Rate Plan.One-time charge
audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,IsOveragePrice,,false,2022-11-19T10:26:47.385Z,Test.New Rate Plan.One-time charge
audit-trail@zuora.com,CREATED,ProductRatePlanCharge,One-time charge,ChargeModel,,Flat Fee Pricing,2022-11-19T10:26:47.384Z,Test.New Rate Plan
audit-trail@zuora.com,CREATED,ProductRatePlanCharge,One-time charge,LegacyRevenueReporting,,false,2022-11-19T10:26:47.384Z,Test.New Rate Plan
audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,StartingUnit,,0.000000000,2022-11-19T10:26:47.384Z,Test.New Rate Plan.One-time charge
audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,IsOveragePrice,,false,2022-11-19T10:26:47.384Z,Test.New Rate Plan.One-time charge
audit-trail@zuora.com,UPDATED,ProductRatePlan,New Rate Plan,ActiveCurrencies,,USD,2022-11-19T10:26:47.383Z,Test
audit-trail@zuora.com,CREATED,ProductRatePlan,New Rate Plan,EffectiveEndDate,,2023-11-19,2022-11-19T10:13:29.764Z,Test
audit-trail@zuora.com,DELETED,ProductRatePlanChargeTier,1,,,,2022-11-18T18:36:50.423Z,
audit-trail@zuora.com,DELETED,ProductRatePlanCharge,New Component,,,,2022-11-18T18:36:50.423Z,

Retrieve Chase Orbital Payment Gateway changes 

The following use case retrieves the auditing records of changes to the Chase Orbital payment gateway configuration.

  1. Submit a data query job through UI or API with the following SQL query:

    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       Objectid    AS ObjectID,
    04       objecttype  AS ObjectType,
    05       objectname  AS ObjectName,
    06       attributeid AS Attribute,
    07       oldvalue    AS OldValue,
    08       newvalue    AS NewValue,
    09       timestamp   AS Timestamp
    10FROM   auditobjectchangeevent
    11WHERE  objecttype = 'PaymentGateway'
    12       AND year = 2022
    13       AND month = 11
    14ORDER  BY timestamp DESC
    15LIMIT  100000

  2. Check the status of the query job through UI or Get data query job API operation.

  3. Download the query result when the job is completed. See the following example of the query result.

    Username,Action,ObjectID,ObjectType,ObjectName,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptCarteBancaire,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptCabal,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptDiners,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,EnableSafetechFraudService,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,IsDefaultGateway,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptVisa,,true,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptMir,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptHipercard,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,DisableCardTypeIndicators,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,GatewayName,,Test Chase Latest Version Audit Trail,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,VerifyNewPaymentMethod,,true,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,CREATED,8xx08c0f7f76c096017f863b914635xx,PaymentGateway,Chase Latest Version,AcceptElectron,,false,2022-11-12T02:22:05.16Z
audit-trail@zuora.com,UPDATED,2xx2c0f87881b8340178a4511eff09xx,PaymentGateway,Chase 7.0.1,VerifyUpdatedPaymentMethod,false,true,2022-11-11T01:20:58.943Z
audit-trail@zuora.com,UPDATED,8xx0824e7f6e7f15017f71ba0b4236xx,PaymentGateway,Chase Latest Version Deprecated,IsActive,true,false,2022-11-10T01:19:22.597Z

Retrieve Email Template changes 

The following use case retrieves the auditing records of changes to the Email Template object.

  1. Submit a data query job through UI or API with the following SQL query:

    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       objectid    AS ObjectID,
    04       objecttype  AS ObjectType,
    05       objectname  AS ObjectName,
    06       attributeid AS Attribute,
    07       oldvalue    AS OldValue,
    08       newvalue    AS NewValue,
    09       timestamp   AS Timestamp
    10FROM   auditobjectchangeevent
    11WHERE  objecttype = 'EmailTemplate'
    12       AND year = 2023
    13       AND month = 4
    14ORDER  BY timestamp DESC
    15LIMIT  100000

  2. Check the status of the query job through UI or Get data query job API operation.

  3. Download the query result when the job is completed. See the following example of the query result.

    Username,Action,ObjectID,ObjectType,ObjectName,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,DELETED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,,,,2023-04-06T10:35:10.913Z
audit-trail@zuora.com,UPDATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,ReplyToEmailAddress,test@zuora.com,admin@zuora.com,2023-04-06T10:34:56.473Z
audit-trail@zuora.com,UPDATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,ReplyToEmailType,TenantEmail,SpecificEmail,2023-04-06T10:34:56.473Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,ReplyToEmailAddress,,test@zuora.com,2023-04-06T10:34:05.891Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,IsHtml,,true,2023-04-06T10:34:05.891Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,EmailSubject,,Invoice 10 Days Past Due,2023-04-06T10:34:05.891Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,ToEmailType,,BillToContact,2023-04-06T10:34:05.891Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562174852d2d,EmailTemplate,Invoice Due 10 Days,FromEmailType,,TenantEmail,2023-04-06T10:34:05.891Z

Retrieve Notification Definition changes 

The following use case retrieves the auditing records of changes to the Notification Definition object.

  1. Submit a data query job through UI or API with the following SQL query:

    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       objectid    AS ObjectID,
    04       objecttype  AS ObjectType,
    05       objectname  AS ObjectName,
    06       attributeid AS Attribute,
    07       oldvalue    AS OldValue,
    08       newvalue    AS NewValue,
    09       timestamp   AS Timestamp
    10FROM   auditobjectchangeevent
    11WHERE  objecttype = 'NotificationDefinition'
    12       AND year = 2023
    13       AND month = 4
    14ORDER  BY timestamp DESC
    15LIMIT  100000

  2. Check the status of the query job through UI or Get data query job API operation.

  3. Download the query result when the job is completed. See the following example of the query result.

    Username,Action,ObjectID,ObjectType,ObjectName,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,DELETED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,,,,2023-04-06T10:40:08.087Z
audit-trail@zuora.com,UPDATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,CalloutParams,"{""AccountName"":""<DataSource.Account.Name>""}","{""AccountName"":""<DataSource.Account.Name>"",""InvoiceDueDate"":""<DataSource.Invoice.DueDate>""}",2023-04-06T10:39:50.314Z
audit-trail@zuora.com,UPDATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,CalloutBaseUrl,https://api.mycompany.com,https://api.mycompany.com/callout,2023-04-06T10:39:50.314Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,Name,,Invoice Due 10 days,2023-04-06T10:38:27.828Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,CalloutOauth2,,true,2023-04-06T10:38:27.828Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,ProfileId,,2c92c8fe7208d2e501720f65887d2b55,2023-04-06T10:38:27.828Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,EventId,,2c92c8fe7208d2e501720f65885c2b35,2023-04-06T10:38:27.828Z
audit-trail@zuora.com,CREATED,8a90f50887558f990187562573ef3c00,NotificationDefinition,Invoice Due 10 days,IsSmsActive,,false,2023-04-06T10:38:27.828Z

Retrieve Workflow changes 

The following use case retrieves the auditing records of changes to Workflow definition object hierarchy.

  1. Submit a data query job through UI or API with the following SQL query:

    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       Objectid    AS ObjectID,
    04       objecttype  AS ObjectType,
    05       objectname  AS ObjectName,
    06       attributeid AS Attribute,
    07       oldvalue    AS OldValue,
    08       newvalue    AS NewValue,
    09       timestamp   AS Timestamp
    10FROM   auditobjectchangeevent
    11WHERE  namespace = 'Workflow'
    12       AND objecttype = 'WorkflowVersion'
    13       AND year = 2022
    14       AND month = 11
    15ORDER  BY timestamp DESC
    16LIMIT  100000

  2. Check the status of the query job through UI or Get data query job API operation.

  3. Download the query result when the job is completed. See the following example of the query result.

    Username,Action,ObjectID,ObjectType,ObjectName,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,name,,Sample Workflow,2022-11-10 16:54:14Z
audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,priority,,Medium,2022-11-10 16:54:14Z
audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,call_type,,ASYNC,2022-11-10 16:54:14Z
audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,callout_trigger,,true,2022-11-10 16:54:14Z
audit-trail@zuora.com,UPDATED,233,WorkflowVersion,Workflow Name,callout_trigger,true,false,2022-11-10 17:11:30Z

Retrieve custom field definition changes 

The following use case retrieves the auditing records of changes to custom field definitions.

  1. Submit a data query job through UI or API with the following SQL query:

    view sourceprint?
    01SELECT username    AS Username,
    02       action      AS Action,
    03       objectname  AS ObjectName,
    04       attributeid AS Attribute,
    05       oldvalue    AS OldValue,
    06       newvalue    AS NewValue,
    07       timestamp   AS Timestamp
    08FROM   auditobjectchangeevent
    09WHERE  objecttype = 'CustomFieldDefinition'
    10       AND year = 2022
    11       AND month = 11
    12       AND day > 1
    13       AND day < 31
    14ORDER  BY timestamp DESC
    15LIMIT  100000

  2. Check the status of the query job through UI or Get data query job API operation.

  3. Download the query result when the job is completed. See the following example of the query result.

    Username,Action,ObjectName,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,UPDATED,Account.LastReviewDate__c,Required,true,false,2022-11-13T07:50:44.683Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,Description,,Date of the last business review with the customer,2022-11-13T07:50:22.915Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,ApiName,,LastReviewDate__c,2022-11-13T07:50:22.915Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,Required,,true,2022-11-13T07:50:22.915Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,ReadOnlyOnUI,,false,2022-11-13T07:50:22.915Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,Type,,Date,2022-11-13T07:50:22.915Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,Label,,LastReviewDate,2022-11-13T07:50:22.915Z
audit-trail@zuora.com,CREATED,Account.LastReviewDate__c,MaxLength,,255,2022-11-13T07:50:22.915Z

Retrieve custom object changes 

The following use case retrieves the auditing records of changes to custom objects, including custom object definitions and records.

  1. Submit a data query job through UI or API with the following SQL query:

    view sourceprint?
    1SELECT *
    2FROM   auditobjectchangeevent
    3WHERE  (objecttype = 'CustomObjectDefinition'
    4        OR objecttype = 'CustomObjectRecord')
    5       AND year = 2022
    6       AND month = 11
    7ORDER  BY timestamp DESC
    8LIMIT  100000

  2. Check the status of the query job through UI or Get data query job API operation.

  3. Download the query result when the job is completed. See the following example of the query result.

    Username,Action,ObjectName,Attribute,OldValue,NewValue,Timestamp
audit-trail@zuora.com,CREATED,ATC,title,,ATC,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,label,,ATC,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,Field1__c.maxLength,,100,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,Field1__c.default,,volume,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,filterable,,"[""Field1__c""]",2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,Field1__c.description,, ,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,Field1__c.label,,Field1,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,Field1__c.type,,STRING,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,required,,"[""Field1__c""]",2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,Field1__c.origin,,custom,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,unique,,"[""Field1__c""]",2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,type,,object,2022-11-04T21:56:19.025Z
audit-trail@zuora.com,CREATED,ATC,object,,ATC,2022-11-04T21:56:19.025Z

Retrieve jobs performed through the Data Loader

The following use cases retrieve auditing records of changes to a job performed through the Data Loader.

Example 1:

view sourceprint?
1SELECT *
2FROM   auditobjectchangeevent
3WHERE  namespace = 'BulkData'
4ORDER  BY timestamp DESC
5LIMIT  100

Example 2: 

view sourceprint?
1SELECT *
2FROM   auditobjectchangeevent
3WHERE  namespace = 'BulkData'
4        AND YEAR = 2023
5        AND MONTH = 8
6        AND DAY = 7
7ORDER  BY timestamp DESC
8LIMIT  100

Retrieve jobs performed through the Deployment Manager

The following query retrieves the details of the job performed through Deployment Manager

view sourceprint?
1SELECT *
2FROM auditobjectchangeevent
3WHERE Month=10 and Year=2024

Note

To get high-performance queries, always use the year, month, or day fields in the WHERE clause of a SQL query when exporting Audit Trail records through Data Query.