BigQuery: Using Audit logs to assess your consumption

Often Google Cloud Platforms billing dashboard suffices to capture and manage how much you are spending on the platform each month. However, this often will not allow you to optimise your spending and won’t show those queries running at the beginning of the month which can be hidden in the free tier or by data being processed by external tools such as Data Studio.

So how do you ensure you can break down the true usage of your BigQuery instance and where spend is generated from…. in comes the great functionality of audit logs. The audit logs track every transaction which takes place on the Google Cloud Platform, are highly accessible and can be exported into many other tools to generate triggers and alerts.

In this case, I will be taking you through how to assess the expense a query could generate should it not have appeared in your free tier, meaning those sneaky beginning of the month queries can be assessed. We will be using the Data Access logs for this analysis.

How to stream the audit logs to BigQuery?

• Navigate to BigQuery and create a dataset in the correct region for your project, otherwise, the BigQuery dataset created automatically by the sync will be in the US region. I recommend naming the dataset audit_logs_type.
• Navigate to the audit log page. As always ensure you are on the correct project.
• On the interface, you are able to build a query that will pull back the logs you wish to review, in this case, we will be looking solely at BigQuery, however, this approach can be used for any tool generating logging on GCP.

resource.type="bigquery_project" OR resource.type="bigquery_dataset"

• This query will now return all logging results which relate to your project or datasets. A handy tip is that by clicking elements of current logs the query will build itself.
• Select actions and create sink, follow the step by step instructions and output to your BigQuery dataset.
• One important note, the audit logs won’t backdate, so you will have to manually output the historic results to join the data sources. You should also be careful about what audit logs you export as these datasets can get incredibly large especially if you are using GCP app features.

Now for the fun stuff

After a few hours, and as long as logs fitting your criteria have been generated, you will start to see your audit log dataset populate. There are multiple levels of audit logs streamed. I will not be going through this detail in this blog, simply as audit logs could be a book in themselves.

The tables are nested and can be fairly intimidating due to nesting, JSON strings and the sheer number of columns, luckily the BigQuery documentation provides some great sample queries to get you started.

Extract

This code will extract and format the JSON strings, alongside converting bytes to GB. This ultimately provides data to populate multiple summaries which allows you to assess how spending is being generated on BigQuery. In this case, we will be looking at the manual and scheduled codes for BigQuery. One thing you’ll notice is that service accounts used by tools such as DataFlow, Cloud Functions and Python, will also be reported in this table with the service account name as the principal email.

(
  SELECT
   DATE( timestamp) AS date,  
    protopayload_auditlog.authenticationInfo.principalEmail AS principalEmail, 
    JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
      "$.jobChange.job.jobConfig.queryConfig.priority") AS querytype,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64)/ POWER(2, 40) AS totalBilledGB,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.totalProcessedBytes") AS INT64)/ POWER(2, 40) AS totalProcessedGB,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.outputRowCount") AS INT64) AS totalproducedrows, 
    JSON_EXTRACT(protopayload_auditlog.metadataJson,
      "$.jobChange.job.jobConfig.queryConfig.query") AS metadataquery,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.endTime") AS TIMESTAMP) AS endtime,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.createTime") AS TIMESTAMP) AS createTime
  FROM
    `project.audit_logs.cloudaudit_googleapis_com_data_access`
    WHERE JSON_EXTRACT(protopayload_auditlog.metadataJson,
      "$.jobChange.job.jobConfig.queryConfig.priority") IS NOT NULL)

You’ll notice we use the JSON_EXTRACT_SCALAR which in this case removes the quotation marks and JSON_EXTRACT. Additionally, we convert the Bytes into GigaBytes using the POWER function.

So what’s next? In this case, I want to see how much processing is taking place per person or service account, split into scheduled and manual queries. You are able to customise your queries, for example if you have groups of users who fall into a team. I’ve kept things simple here and just split out service accounts, however you can use other fields to identify access via tools like DataStudio.

(SELECT   
     (CASE WHEN principalEmail LIKE '%.gserviceaccount.%' THEN 'service_account' ELSE 'team_access' END) AS IAM_type,
     principalEmail ,
     (CASE WHEN querytype = 'QUERY_BATCH' AND  principalEmail NOT LIKE '%.gserviceaccount.%' THEN 'scheduled_code' ELSE lower(querytype) END) AS query_type , 
     ROUND(SUM(totalBilledGB),3) as gb_billed,
     ROUND(SUM(totalProcessedGB),3) as gb_processed,
     ROUND(SUM(totalBilledGB),3) *0.5 AS gb_cost_cents, 
     FROM BASE 
     WHERE date >= DATE_SUB(current_date(), INTERVAL 60 DAY) 
     GROUP BY 1,2,3
     ORDER BY 6 desc)

What about the cost of scheduled codes?

There is a wealth of data available in the audit logs, including the query itself and the number of rows populated. These can be used for processes such as triggering error reports, table usage reports, Assertions (as mentioned in my previous post), warehouse weather reports and even reverting back when your scheduled code has new bugs. In this case, I wanted to simply show you extracting the scheduled code and how much they have cost historically per month, who owns the schedule and the GB processed.

In this case we use almost the same base code but will use the query syntax to define the scheduled code. I will be chatting about scheduled query good practice in a future blog which means you can track code changes and whether a report ran correctly.

WITH
  BASE AS (
  SELECT
    DATE( timestamp) AS date,
    protopayload_auditlog.authenticationInfo.principalEmail AS principalEmail,
    JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
      "$.jobChange.job.jobConfig.queryConfig.priority") AS querytype,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64)/ POWER(2, 40) AS totalBilledGB,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.totalProcessedBytes") AS INT64)/ POWER(2, 40) AS totalProcessedGB,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.outputRowCount") AS INT64) AS totalproducedrows,
    JSON_EXTRACT(protopayload_auditlog.metadataJson,
      "$.jobChange.job.jobConfig.queryConfig.query") AS metadataquery,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.endTime") AS TIMESTAMP) AS endtime,
    CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.createTime") AS TIMESTAMP) AS createTime
  FROM
    `audit_logs.cloudaudit_googleapis_com_data_access`
  WHERE
    JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
      "$.jobChange.job.jobConfig.queryConfig.priority") = 'QUERY_BATCH'
    AND protopayload_auditlog.authenticationInfo.principalEmail NOT LIKE '%.gserviceaccount.%' ) (
  SELECT
    FORMAT_DATE('%Y-%m', date) AS month,
    principalEmail,
    metadataquery,
    COUNT(DISTINCT(date)) AS run_count,
    ROUND(SUM(totalBilledGB),3) AS gb_billed,
    ROUND(SUM(totalProcessedGB),3) AS gb_processed,
    ROUND(SUM(totalBilledGB),3) *0.5 AS gb_cost_cents,
    MIN(createTime) AS creation_time,
    MAX(endtime) AS end_time
  FROM
    BASE
  GROUP BY
    1,
    2,
    3
  ORDER BY
    5 DESC)

There is so much more you can do with audit logs to automate, understand and optimise your warehouse usage and these codes simply scratch the service. I will be sharing how to build a automated weather check and table usage reviews with Python and BigQuery in the near future using these logs alongside how to track access management effectively. for the meantime, be sure to share how you use these logs.