Skip to main content

Sample Queries of Audit Trail

Zuora

Sample Queries of Audit Trail

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 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  createddate > Date('2019-04-01') 
           AND createddate < Date('2019-07-01') 
    ORDER  BY timestamp DESC 
    LIMIT  100000
  2. Check the status of the query job through UI or Get data query job call.
  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,2019-06-30T04:29:06.571Z,Chrome,77.0,67.180.238.89
    audit-trail@example.com,PASSWORD,Success,2019-06-29T16:34:09.092Z,Chrome,77.0,216.129.127.1
    audit-trail@example.com,PASSWORD,Success,2019-06-29T16:34:09.042Z,Chrome,77.0,216.129.127.1
    audit-trail@example.com,PASSWORD,Success,2019-06-29T01:15:38.693Z,Chrome,77.0,103.104.131.160
    zconnect.api.9439@example.com,PASSWORD,Success,2019-06-17T18:50:16.763Z,RestBiz,0.0,54.187.61.166
    audit-trail@example.com,PASSWORD,Success,2019-06-17T18:49:13.393Z,Chrome,77.0,67.180.238.89
    audit-trail@example.com,PASSWORD,Success,2019-06-17T18:38:24.001Z,Chrome,77.0,67.180.238.89
    audit-trail@example.com,CLIENT_CREDENTIALS,Success,2019-06-17T03:43:05.423Z,RestBiz,0.0,103.104.131.160
    audit-trail@example.com,PASSWORD,Success,2019-06-17T03:33:34.209Z,Chrome,77.0,103.104.131.160
    audit-trail@example.com,PASSWORD,PasswordExpired,2019-06-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 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' 
    ORDER  BY timestamp DESC 
    LIMIT  100000 
  2. Check the status of the query job through UI or Get data query job call.
  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,2019-12-10T09:30:52Z
    audit-trail@zuora.com,UPDATED,TenantProperty,PROPERTY_DISALLOW_BLANK_ACCOUNTING_CODE,true,false,2019-12-10T09:29:47Z
    audit-trail@zuora.com,UPDATED,BillingRules,Enable credit back for removing or canceling one time charges?,false,true,2019-12-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,2019-12-05T17:08:53Z
    audit-trail@zuora.com,UPDATED,BillingRules,Support bill run auto-post?,true,false,2019-12-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,2019-12-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,2019-12-05T16:56:36Z
    audit-trail@zuora.com,UPDATED,BillingRules,includeNegativeInvoice,false,true,2019-12-05T16:56:36Z
    audit-trail@zuora.com,UPDATED,BillingRules,Enable credit back for removing or canceling one time charges?,true,false,2019-12-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 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' 
    ORDER  BY timestamp DESC 
    LIMIT  100000 
    
  2. Check the status of the query job through UI or Get data query job call.
  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,2019-11-18T07:00:10.286Z,User,34084+user@zuora.com,,Administrator,UserManagement,REMOVED_FROM_COLLECTION,roles
    audittraile2e@example.com,2019-11-18T07:00:10.286Z,User,34084+user@zuora.com,Standard User,,UserManagement,ADDED_TO_COLLECTION,roles
    audittraile2e@example.com,2019-11-18T06:59:08.413Z,Role,audit_1574060164607_update,,,UserManagement,DELETED,
    audittraile2e@example.com,2019-11-18T06:58:08.157Z,Role,audit_1574060164607_update,apiWrite,,UserManagement,ADDED_TO_COLLECTION,permissions
    audittraile2e@example.com,2019-11-18T06:58:08.157Z,Role,audit_1574060164607_update,,ui,UserManagement,REMOVED_FROM_COLLECTION,permissions
    audittraile2e@example.com,2019-11-18T06:57:06.187Z,Role,audit_1574060164607,desc_update,desc,UserManagement,UPDATED,description
    audittraile2e@example.com,2019-11-18T06:57:06.187Z,Role,audit_1574060164607,audit_1574060164607_update,audit_1574060164607,UserManagement,UPDATED,name
    audittraile2e@example.com,2019-11-18T06:56:05.777Z,Role,audit_1574060164607,ui,,UserManagement,ADDED_TO_COLLECTION,permissions
    audittraile2e@example.com,2019-11-18T06:56:05.418Z,Role,audit_1574060164607,,,UserManagement,CREATED,
    audittraile2e@example.com,2019-11-18T06:54:03.701Z,OAuth Client,0fecbd03-9445-4b72-8145-7f813e6b63f5,,,UserManagement,DELETED,

Retrieve the business object changes

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 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'
    ORDER  BY timestamp DESC, transactionid
    LIMIT  100000
    
  2. Check the status of the query job through UI or Get data query job call.
  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,,,,2020-11-09T09:04:39.046Z,
    audit-trail@zuora.com,UPDATED,Account,testJoy002,Status,Active,Canceled,2020-11-09T09:04:35.386Z,
    audit-trail@zuora.com,UPDATED,Account,gary test staging2,TotalDebitMemoBalance,,0.000000000,2020-11-06T16:16:36.303Z,
    audit-trail@zuora.com,UPDATED,Account,gary test staging2,Mrr,,12.00,2020-11-06T16:16:36.303Z,
    audit-trail@zuora.com,UPDATED,Account,gary test staging2,UnappliedCreditMemoAmount,,0.000000000,2020-11-06T16:16:36.303Z,
    audit-trail@zuora.com,UPDATED,Account,gary test staging2,CustomerServiceRepName,,gary test staging2,2020-11-06T16:10:19.557Z,
    audit-trail@zuora.com,CREATED,Account,Xi Test,BillToId,,2c92c8fe7569068c01756ede2ee4383a,2020-10-28T11:00:56.173Z,John Doe
    audit-trail@zuora.com,CREATED,Account,Xi Test,SoldToId,,2c92c8fe7569068c01756ede2ee4383a,2020-10-28T11:00:56.173Z,Jane Doe
    audit-trail@zuora.com,UPDATED,Account,Oscorp123,BillToId,2c92c8fb756388ec017564e530151b3a,2c92c8fb6af31db7016af7dd77dd1c36,2020-10-27T23:12:35.685Z,Tony Stark -> Nick Fury
    audit-trail@zuora.com,UPDATED,Account,Oscorp123,SoldToId,2c92c8fb6af31db7016af7dd77dd1c36,2c92c8fb756388ec017564e530151b3a,2020-10-27T23: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 above query result for an example.

Retrieve Product Catalog changes

The following use case retrieves the audit trail of Product Catalog object hierarchy.

  1. Submit a data query 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'
    ORDER  BY timestamp DESC,
              transactionid
    LIMIT  100000
    
  2. Check the status of the query job through UI or Get data query job call.
  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,2020-11-19T10:26:47.385Z,Test.New Rate Plan.One-time charge
    audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,IsOveragePrice,,false,2020-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,2020-11-19T10:26:47.384Z,Test.New Rate Plan
    audit-trail@zuora.com,CREATED,ProductRatePlanCharge,One-time charge,LegacyRevenueReporting,,false,2020-11-19T10:26:47.384Z,Test.New Rate Plan
    audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,StartingUnit,,0.000000000,2020-11-19T10:26:47.384Z,Test.New Rate Plan.One-time charge
    audit-trail@zuora.com,CREATED,ProductRatePlanChargeTier,1,IsOveragePrice,,false,2020-11-19T10:26:47.384Z,Test.New Rate Plan.One-time charge
    audit-trail@zuora.com,UPDATED,ProductRatePlan,New Rate Plan,ActiveCurrencies,,USD,2020-11-19T10:26:47.383Z,Test
    audit-trail@zuora.com,CREATED,ProductRatePlan,New Rate Plan,EffectiveEndDate,,2021-11-19,2020-11-19T10:13:29.764Z,Test
    audit-trail@zuora.com,DELETED,ProductRatePlanChargeTier,1,,,,2020-11-18T18:36:50.423Z,
    audit-trail@zuora.com,DELETED,ProductRatePlanCharge,New Component,,,,2020-11-18T18:36:50.423Z,
    

Retrieve Workflow changes 

The following use case retrieves the audit trail of Workflow definition object hierarchy.

  1. Submit a data query 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'
    ORDER  BY timestamp DESC 
    LIMIT  100000 
    
  2. Check the status of the query job through UI or Get data query job call.

  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,2020-11-10 16:54:14Z
    audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,priority,,Medium,2020-11-10 16:54:14Z
    audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,call_type,,ASYNC,2020-11-10 16:54:14Z
    audit-trail@zuora.com,CREATED,233,WorkflowVersion,Workflow Name,callout_trigger,,true,2020-11-10 16:54:14Z
    audit-trail@zuora.com,UPDATED,233,WorkflowVersion,Workflow Name,callout_trigger,true,false,2020-11-10 17:11:30Z