Auditing Queries
Use the audit logs to find out which users have run or tried to run queries and the tables that those queries have accessed or tried to access.
Each analytical query against a cluster is logged. An analytical query is a query against a published project, that is issued from a Thrift endpoint, or that is issued from an XMLA endpoint. Queries from the Design Center for data previews and queries for setting up or testing connections are not audited.
How log files are managed
The log file, audit.log, is located in the path
/opt/atscale/log/engine/ on the AtScale server. It is rotated daily.
Old log files are compressed and given the name
audit.yyyy-mm-dd.log.gz. Ninety days of old log files are retained.
Content of log entries
Each entry in the audit log contains the following key/value pairs.
Note: Due to the removal of environments in AtScale 7.4.0, the Environment ID field is no longer written to the query audit logs.
| Key | Value |
|---|---|
| queryID | A string that identifies the query. |
| allowed | A Boolean value that indicates whether the user had permission to execute the query. Values: true | false |
| isCanary | A Boolean value that indicates whether the query was a canary query. Values: true | false |
| service or user | The name of the service or the user ID that executed or attempted to execute the query. |
| ip | The IP address of the client that executed or attempted to execute the query.This name/value pair does not appear for queries that are issued by services. |
| org_id | The name of the organization under which the query was executed or attempted to be executed. |
| project_id | The name of the project against which the query was executed or attempted to be executed.This name/value pair might be missing for some queries. |
| tables_read | The names of the tables that the query accessed or attempted to access. If the query is against a query dataset, the value is the text of the query. |
Example log entries
These examples are formatted for better readability, with each key/value pair on a separate line. Actual log entries separate key/value pairs with spaces.
Figure 1. Raw query
2016-07-29T21:55:28.373Z atscale-query-audit: queryId=e06d6077-a422-4e1e-83f7-ccdb9b9fb9ab allowed=true isCanary=true user=user_ID ip=/192.168.5.115 orgId=default projectId=1f8ef67a-b237-4ed9-7958-b17ff09e0755 tables_read=database_a.factinternetsales
Figure 2. Canary query
2016-07-29T21:42:19.949Z atscale-query-audit: queryId=52b5ac09-6d3c-4499-b6ef-a6abca677ff0 allowed=true isCanary=true user=user_ID ip=/192.168.5.115 orgId=default projectId=1f8ef67a-b237-4ed9-7958-b17ff09e0755 tables_read=database_a.dimgender,database_a.dimcustomer,database_a.factinternetsales
Figure 3. Use of aggregates
Use of a system aggregate
2016-08-01T03:27:26.874Z atscale-query-audit: queryId=6b4fbe97-9d06-46a1-a64c-5d803a88b100 allowed=true isCanary=false user=user_ID ip=/192.168.99.1 orgId=default projectId=demo tables_read=as_adventure.as_agg_37b34995_none
Use of a user-defined aggregate
2016-07-29T21:42:21.201Z atscale-query-audit: queryId=52b5ac09-6d3c-4499-b6ef-a6abca677ff0 allowed=true isCanary=false user=user_ID ip=/192.168.5.115 orgId=default projectId=1f8ef67a-b237-4ed9-7958-b17ff09e0755 tables_read=as_adventure.as_agg_2c479178_uda_hdp2sec
Figure 4. Drill-through
2016-08-01T03:28:02.380Z atscale-query-audit: queryId=e7870e98-3608-4789-88a5-9091c97e5cb1 allowed=true isCanary=false user=user_ID ip=/192.168.99.1 orgId=default projectId=demo tables_read=as_adventure.dim_geo_state,as_adventure.dim_geo_city,as_adventure.dimdate, as_adventure.as_agg_ff188f43_clr,as_adventure.dim_geo_postalcode,as_adventure.dimcustomer 2016-08-01T03:28:17.433Z atscale-query-audit: queryId=181561db-f53f-44dd-ac1b-7b63ba4c69c3 allowed=true isCanary=false user=auser_ID ip=/192.168.99.1 orgId=default projectId=demo tables_read=as_adventure.as_agg_06dfc994_clr_sz_stl,as_adventure.dimdate,as_adventure.dimproduct, as_adventure.dimcustomer
Figure 5. Query dataset (two fact datasets and one dimension)
2016-07-30T22:42:28.043Z atscale-query-audit: queryId=a20b5eac-23cb-4392-bb7b-1b6642c57045 allowed=true isCanary=false user=user_ID ip=/192.168.99.1 orgId=default projectId=975b5a31-acef-40a9-4466-7e3fbd32beb9 tables_read="select \* from as_adventure.sales_log",as_adventure.factinternetsales,as_adventure.customer_file
Figure 6. Use of query datasets
Query dataset with delegated authorization and impersonation
2016-07-31T22:57:01.726Z atscale-query-audit: queryId=72128767-8e67-4f5b-90e9-7c60bdd75472 allowed=true isCanary=true user=user_ID ip=/192.168.5.5 orgId=default projectId=02f8c203-27c2-4449-7e68-bc04a5cb35d8 tables_read="select \* from as_adventure.factinternetsales"
Canary query, then use of an aggregate table with delegated authorization and impersonation
2016-07-29T21:52:31.470Z atscale-query-audit: queryId=506c35e1-85b5-4507-9f82-fd15d22bf8cd allowed=true isCanary=true user=user_ID ip=/192.168.5.115 orgId=default projectId=1f8ef67a-b237-4ed9-7958-b17ff09e0755 tables_read=database_a.factinternetsales 2016-07-29T21:52:32.411Z atscale-query-audit: queryId=506c35e1-85b5-4507-9f82-fd15d22bf8cd allowed=true isCanary=false user=user_ID ip=/192.168.5.115 orgId=default projectId=1f8ef67a-b237-4ed9-7958-b17ff09e0755 tables_read=as_adventure.as_agg_06ddb2d1_none
Figure 7. Query from the AggregationService
2016-08-01T03:34:03.450Z atscale-query-audit: queryId=a7a20dd7-1527-493c-974c-29e7fc3c738a allowed=true isCanary=false service=AggregationService orgId=default projectId=demo tables_read=as_adventure.dimproduct
Figure 8. Query from the StatsService
2016-08-01T03:33:59.801Z atscale-query-audit: queryId=e562b649-d479-47ce-be55-913cda1974ae allowed=true isCanary=false service=StatsService orgId=default projectId=demo tables_read=as_adventure.dimproduct