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

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,