This document describes how to analyze Cloud Logging audit logs by using BigQuery-standard SQL queries in the Log Analytics page. SQL queries let you aggregate and analyze your audit logs, which provide information about administrative activities and accesses in your Google Cloud resources.
About audit logs
There are four types of audit logs that can be written by Google Cloud services:
Admin Activity audit logs: Admin Activity audit logs record API calls or other actions that modify the configuration or metadata of resources. These logs are always written; you can't configure, exclude, or disable them.
Data Access audit logs: Data Access audit logs record API calls that read the configuration or metadata of resources, as well as user-driven API calls that create, modify, or read user-provided resource data. Because accessing data is a frequent API operation, these logs are disabled by default (except for BigQuery).
System Event audit logs: System Event audit logs contain log entries for Google Cloud actions that modify the configuration of resources. These logs are generated by Google systems; these logs aren't generated by user actions. You can't configure, exclude, or disable System Event audit logs.
Policy Denied audit logs: Policy Denied audit logs are recorded when a Google Cloud service denies access to a user or a service account due to a security policy violation. These logs can't be disabled, but you can use exclusion filters to prevent these logs from being stored in Logging.
Use audit logs to identify policy violations or suspicious activity
You can use audit logs to identify policy violations or suspicious activity:
To identify potential privilege escalation by using Identity and Access Management (IAM), or defense evasion by disabling Logging, use Admin Activity audit logs. For a sample query that identifies this scenario, see Changes made to Logging settings.
To identify potential misuse of APIs, or data hosted in services like Cloud Storage or BigQuery, use Data Access audit logs. For a sample query that identifies this scenario, see Identify high API usage by a principal.
To identify how frequently data is accessed and by which users, query for all the audit logs. For a sample query that identifies this scenario, see Determine most common actions performed in the past month.
Before you begin
Ensure you have a Google Cloud project, folder, or organization that generates audit logs.
Ensure that you have access to a view on the log bucket that the audit logs are routed to. The log bucket must be upgraded to use Log Analytics. For information about how to create a log bucket that is upgraded to use Log Analytics, see Configure log buckets.
To get the permissions that you need to create sinks and view logs, ask your administrator to grant you the following IAM roles:
Logs Configuration Writer (
roles/logging.configWriter) on your project
Logs Viewer (
roles/logging.viewer) on your project
For more information about granting roles, see Manage access.
Depending on which audit logs you want to view, you might need separate roles or permissions. For information about setting IAM roles, the see Logging Access control with IAM documentation.
- Logs Configuration Writer (
To use the queries in this document in the Log Analytics page, do the following:
In the navigation panel of the Google Cloud console, select Logging, and then select Log Analytics:
Identify the table name for a log view by running the default query:
In the Log views list, locate the log view, and then select Query. The Query pane is populated with a default query, which includes the name of the table that is queried. The table name has the format
For more information about how to access the default query, see Query a log view.
Replace TABLE with the name of the table that corresponds to the view that you want to query, then copy the query.
Paste the query in the Query pane, then click Run query.
This section provides sample SQL queries for querying audit logs.
Changes made to Logging settings
To identify when audit logs are disabled or when changes are made to the default Logging settings, query the Admin Activity audit logs:
SELECT receive_timestamp, timestamp AS eventTimestamp, proto_payload.audit_log.request_metadata.caller_ip, proto_payload.audit_log.authentication_info.principal_email, proto_payload.audit_log.resource_name, proto_payload.audit_log.method_name FROM `TABLE` WHERE proto_payload.audit_log.service_name = "logging.googleapis.com" AND log_id = "cloudaudit.googleapis.com/activity"
Determine most common actions performed in the past month
To identify which actions are the most commonly performed in the past 30 days, query all audit logs:
SELECT proto_payload.audit_log.method_name, proto_payload.audit_log.service_name, resource.type, COUNT(*) AS counter FROM `TABLE` WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND log_id="cloudaudit.googleapis.com/data_access" GROUP BY proto_payload.audit_log.method_name, proto_payload.audit_log.service_name, resource.type ORDER BY counter DESC LIMIT 100
The previous query searches all audit logs in the past 30 days, and returns
the 100 most performed actions with information about the
service_name, resource type, and a counter of the actions performed.
Detect roles granted on a service account
To identify service account impersonation, or roles granted on service accounts, query the Admin Activity audit logs:
SELECT timestamp, proto_payload.audit_log.authentication_info.principal_email as grantor, JSON_VALUE(bindingDelta.member) as grantee, JSON_VALUE(bindingDelta.role) as role, proto_payload.audit_log.resource_name, proto_payload.audit_log.method_name FROM `TABLE`, UNNEST(JSON_QUERY_ARRAY(proto_payload.audit_log.service_data.policyDelta.bindingDeltas)) AS bindingDelta WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND log_id = "cloudaudit.googleapis.com/activity" AND ( (resource.type = "service_account" AND proto_payload.audit_log.method_name LIKE "google.iam.admin.%.SetIAMPolicy") OR (resource.type IN ("project", "folder", "organization") AND proto_payload.audit_log.method_name = "SetIamPolicy" AND JSON_VALUE(bindingDelta.role) LIKE "roles/iam.serviceAccount%") ) AND JSON_VALUE(bindingDelta.action) = "ADD" -- Principal (grantee) exclusions AND JSON_VALUE(bindingDelta.member) NOT LIKE "%@example.com" ORDER BY timestamp DESC
The previous query searches for audit logs that capture roles being granted to a
principal on a service account. The Service Account Token creator role lets the
principal impersonate the service account. The query also specifies a time
range of the last seven days and excludes approved grantees (
Identify high API usage by a principal
To identify unusually high API usage by a principal, query all audit logs:
SELECT * FROM ( SELECT *, AVG(counter) OVER ( PARTITION BY principal_email ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS avg, STDDEV(counter) OVER ( PARTITION BY principal_email ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS stddev, COUNT(*) OVER ( PARTITION BY principal_email RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS numSamples FROM ( SELECT proto_payload.audit_log.authentication_info.principal_email, EXTRACT(DATE FROM timestamp) AS day, ARRAY_AGG(DISTINCT proto_payload.audit_log.method_name IGNORE NULLS) AS actions, COUNT(*) AS counter FROM `TABLE` WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 DAY) AND proto_payload.audit_log.authentication_info.principal_email IS NOT NULL AND proto_payload.audit_log.method_name NOT LIKE "storage.%.get" AND proto_payload.audit_log.method_name NOT LIKE "v1.compute.%.list" AND proto_payload.audit_log.method_name NOT LIKE "beta.compute.%.list" GROUP BY proto_payload.audit_log.authentication_info.principal_email, day ) ) WHERE counter > avg + 3 * stddev AND day >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) ORDER BY counter DESC
For the specified principal,
principal_email, the query computes the average
number of API calls per day, and the standard deviation of those API calls.
When the average number of API calls is larger than the running average plus
three times the standard deviation, then the query displays the following
- A counter of the actions performed.
- The calculated average actions performed per day.
- The specific actions that were performed.
For an overview of Log Analytics, see Log Analytics.
For more sample queries, see Sample SQL queries.
For more sample queries used to generate security insights from your logs, see the Community Security Analytics repository.
To learn how to enable, aggregate, and analyze your logs by using Log Analytics, see Security log analytics in Google Cloud.