Skip to main content

Sample Queries of Audit Trail

Zuora

Sample Queries of Audit Trail

  1. Last updated
  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: 
    SELECT username       AS Username, 
       logintype      AS Type, 
       status         AS Status, 
       timestamp      AS Timestamp, 
       browsertype    AS Browser, 
       browserversion AS BrowserVersion, 
       ipaddress      AS IPAddress 
FROM   auditloginevent 
WHERE  year = 2022
       AND month >= 10
       AND month < 11 
ORDER  BY timestamp DESC 
LIMIT  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: 
    SELECT username      AS UpdatedBy, 
       action        AS Action, 
       settingtype   AS SettingType, 
       attributename AS Attribute, 
       oldvalue      AS OldValue, 
       newvalue      AS NewValue, 
       timestamp     AS Timestamp 
FROM   auditsettingchangeevent 
WHERE  namespace <> 'UserManagement' 
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC 
LIMIT  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: 
    SELECT username          AS UpdatedBy, 
       timestamp         AS Timestamp, 
       settingtype       AS Type, 
       settingobjectname AS ObjectName, 
       newvalue          AS NewValue, 
       oldvalue          AS OldValue, 
       namespace         AS Namespace, 
       action            AS Action, 
       attributename     AS AttributeName 
FROM   auditsettingchangeevent 
WHERE  namespace = 'UserManagement' 
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC 
LIMIT  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: 
    SELECT username    AS Username,
       action      AS Action,
       objecttype  AS ObjectType,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp,
       ( CASE
           WHEN ( ( attributeid = 'BillToId' OR attributeid = 'SoldToId' )
                  AND oldvalue <> ''
                  AND newvalue <> '' ) THEN (SELECT Concat(c1.firstname, ' ', c1.lastname, ' -> ', c2.firstname, ' ', c2.lastname)
                                             FROM   contact c1, contact c2
                                             WHERE  c1.id = oldvalue AND c2.id = newvalue)
           WHEN ( ( attributeid = 'BillToId' OR attributeid = 'SoldToId' )
                  AND oldvalue IS NULL ) THEN (SELECT Concat(firstname, ' ', lastname)
                                               FROM   contact
                                               WHERE  id = newvalue)
           ELSE ''
         END )     AS additional_info
FROM   auditobjectchangeevent
WHERE  objecttype = 'Account'
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC, transactionid
LIMIT  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: 
    SELECT username    AS Username,
       action      AS Action,
       objecttype  AS ObjectType,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp,
       ( CASE
           WHEN ( objecttype = 'ProductRatePlan' ) THEN (SELECT p.name
                                                         FROM   product p,
                                                                productrateplan prp
                                                         WHERE  prp.id = objectid
                                                                AND p.id = prp.productid)
           WHEN ( objecttype = 'ProductRatePlanCharge' ) THEN (SELECT CONCAT(p.name, '.', prp.name)
                                                               FROM   product p,
                                                                      productrateplan prp,
                                                                      productrateplancharge prpc
                                                               WHERE  prpc.id = objectid
                                                                      AND prp.id = prpc.productrateplanid
                                                                      AND p.id = prp.productid)
           WHEN ( objecttype = 'ProductRatePlanChargeTier' ) THEN (SELECT CONCAT(p.name, '.', prp.name, '.', prpc.name)
                                                                   FROM   product p,
                                                                          productrateplan prp,
                                                                          productrateplancharge prpc,
                                                                          productrateplanchargetier prpct
                                                                   WHERE  prpct.id = objectid
                                                                          AND prpc.id = prpct.productrateplanchargeid
                                                                          AND prp.id = prpc.productrateplanid
                                                                          AND p.id = prp.productid)
           ELSE ''
         END ) AS product_catalog_info
FROM   auditobjectchangeevent
WHERE  (objecttype = 'ProductRatePlan'
       OR objecttype = 'ProductRatePlanCharge'
       OR objecttype = 'ProductRatePlanChargeTier')
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC,
          transactionid
LIMIT  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:

    SELECT username    AS Username,
       action      AS Action,
       Objectid    AS ObjectID,
       objecttype  AS ObjectType,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp
FROM   auditobjectchangeevent
WHERE  objecttype = 'PaymentGateway'
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC 
LIMIT  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:

    SELECT username    AS Username,
       action      AS Action,
       objectid    AS ObjectID,
       objecttype  AS ObjectType,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp
FROM   auditobjectchangeevent
WHERE  objecttype = 'EmailTemplate'
       AND year = 2023
       AND month = 4
ORDER  BY timestamp DESC 
LIMIT  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:

    SELECT username    AS Username,
       action      AS Action,
       objectid    AS ObjectID,
       objecttype  AS ObjectType,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp
FROM   auditobjectchangeevent
WHERE  objecttype = 'NotificationDefinition'
       AND year = 2023
       AND month = 4
ORDER  BY timestamp DESC 
LIMIT  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:

    SELECT username    AS Username,
       action      AS Action,
       Objectid    AS ObjectID,
       objecttype  AS ObjectType,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp
FROM   auditobjectchangeevent
WHERE  namespace = 'Workflow'
       AND objecttype = 'WorkflowVersion'
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC 
LIMIT  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:

    SELECT username    AS Username,
       action      AS Action,
       objectname  AS ObjectName,
       attributeid AS Attribute,
       oldvalue    AS OldValue,
       newvalue    AS NewValue,
       timestamp   AS Timestamp
FROM   auditobjectchangeevent
WHERE  objecttype = 'CustomFieldDefinition'
       AND year = 2022
       AND month = 11
       AND day > 1
       AND day < 31
ORDER  BY timestamp DESC 
LIMIT  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:

    SELECT *
FROM   auditobjectchangeevent
WHERE  (objecttype = 'CustomObjectDefinition'
        OR objecttype = 'CustomObjectRecord')
       AND year = 2022
       AND month = 11
ORDER  BY timestamp DESC 
LIMIT  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:

SELECT *
FROM   auditobjectchangeevent
WHERE  namespace = 'BulkData'
ORDER  BY timestamp DESC 
LIMIT  100

Example 2: 

SELECT *
FROM   auditobjectchangeevent
WHERE  namespace = 'BulkData'
        AND YEAR = 2023
        AND MONTH = 8
        AND DAY = 7
ORDER  BY timestamp DESC 
LIMIT  100

Retrieve jobs performed through the Deployment Manager

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

SELECT * 
FROM auditobjectchangeevent 
WHERE 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.