API calls required to collect an audit trail

This article looks at the API calls required to collect an audit trail detailing the SQL commands executed against a datasource.

The dataSource API call required to build up a list of dataSources you may be interested in.

https://<immuta-url>/dataSource?determinePolicyConflicts=false&collectionExclude=false

This produces a JSON record or records containing the datasource ID('s). In this case the target data source is 212 and auditing of SQL executed against this is required.

{ "hits": [ 
{ "name": "Tags Plb Fake People",
 "id": 212, "recordFormat": "Not Provided",
 "deleted": false,
 "description": null,
 "createdAt": "2024-05-02T03:15:38.941Z",
 "subscriptionPolicy": null,
 "schemaEvolutionId": 48,
 "recordCount": 0,
 "blobHandlerType": "Databricks",
 "subscriptionType": "manual",
 "sqlSchemaName": "plb_dbx_tags",
 "status": "passed",
 "statusPriority": 6,
 "subscriptionStatus": "owner",
 "connectionString": ":443/plb_data2",
 "remoteTable": "plb_fake_people",
 "remoteSchema": "tags" } 
],
 "facets": { "statuses": [ { "name": "passed", "count": 0 } ] }, "count": 1 }

Collecting the audit information required around SQL executed against a datasource requires two API calls.

• The following API call will allow you pull out all audit records for datasource 212 generated between two dates. This provides as its output a record in the format shown below. Each record will need to be examined to identify those of a recordType "nativeQuery".

  https://<immuta-url>/audit?offset=0&dataSourceId=212&minDate=2024-04-25T14%3A54%3A09%2B10%3A00&maxDate=2024-05-03T14%3A54%3A09%2B10%3A00&sortField=dateTime&sortOrder=desc&size=50

{
             "dateTime": "1714619742377",
             "dataSourceName": "Tags Plb Fake People",
             "projectName": null,
             "recordType": "nativeQuery",
             "blobId": null,
             "userId": null,
             "profileId": -1,
             "purposeIds": null,
             "success": true,
             "failureReason": null,
             "warning": null,
             "id": "1abe7cd6-1340-3867-9741-59cf7f1ba50c_212",
             "email": null
         },

The "id" found for each audit record can then be used to pull out more detail for the specific audit record.

• A second API call can be made using the "id" from the record above to obtain details for the specific audit record itself.

https://<immuta-url>/audit/1abe7cd6-1340-3867-9741-59cf7f1ba50c_212b

This will provide details around who executed SQL against the datasource, what the sql was and when it was executed.

{   "id": "1abe7cd6-1340-3867-9741-59cf7f1ba50c_212",
   "dateTime": "1714619742377",
   "month": 1492,   "profileId": -1,
   "dataSourceId": 212,
   "dataSourceName": "Tags Plb Fake People",
   "count": 1,
   "recordType": "nativeQuery",
   "success": true,
   "component": "nativeSql",
   "accessType": "query",
   "query": "SELECT `ssn`, `first_name`, `last_name`, `gender`, `date_of_birth` FROM `tags`.`plb_fake_people` WHERE (((pmod(cast(conv(substr(md5(CAST((`ssn`) AS string)), 1, 16), 16, 10) AS DECIMAL(38)), 100) < 100))) LIMIT 30000 ",
   "queryId": "1abe7cd6-1340-3867-9741-59cf7f1ba50c",
   "extra": { "handler": "Databricks",
              "startTime": "2024-05-02T03:15:42.377Z",
              "endTime": "2024-05-02T03:15:43.056Z",
              "duration": 679,
              "nativeObject": "plb_fake_people",
              "nativeObjectFullName": "plb_data2.tags.plb_fake_people",
              "host": "",
              "actionStatusReason": null,
              "errorCode": null,
              "actionName": "commandSubmit",
              "accountId": "xxxxxxxx39db7a3ff76a",
              "notebookId": null,
              "clusterId": null,
              "requestId": "09d5941f-2db0-4eab-973f-52d74b5482bb",
              "sessionId": "01ef0832-422d-12bf-920f-85e05e3e1f9b",
              "warehouseId": "c719799eaf5e7f47",
              "actorIp": "0.0.0.0",
              "actorEmail": "",
              "userAgent": "",
              "workspaceId": "xxxxxxxx3599671",
              "queryLanguage": "sql",
              "actionStatus": "SUCCESS",
              "service": "WAREHOUSE"   },
   "createdAt": "2024-05-02T04:04:59.524Z",
   "updatedAt": "2024-05-02T04:04:59.524Z"
 }