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.
For more information about audit logs, see Audit logs overview. For a list of services integrated with audit logs, see Google Cloud services with audit logs.
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 to projects, folders, and organizations.
You might also be able to get the required permissions through custom roles or other predefined roles.
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 Google Cloud console, go to the Log Analytics page:
If you use the search bar to find this page, then select the result whose subheading is Logging.
Identify the table name for the log view that you want to query.
To identify this name, go to the Log views list, locate the log view, and then select Query. The Query pane is populated with a default query, which includes the table name of the log view that is queried. The table name has the format
project_ID.region.bucket_ID.view_ID
.For more information about how to access the default query, see Query a log view.
Replace TABLE_NAME_OF_LOG_VIEW with the table name for the log view that you want to query, then copy the query.
Paste the query in the Query pane, then click Run query.
-
Sample queries
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_NAME_OF_LOG_VIEW`
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_NAME_OF_LOG_VIEW`
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 method_name
,
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_NAME_OF_LOG_VIEW`,
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 (%@example.com
).
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_NAME_OF_LOG_VIEW`
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
information:
- A counter of the actions performed.
- The calculated average actions performed per day.
- The specific actions that were performed.
What's next
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.