SQL queries for security insights

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.

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.
To use the queries in this document in the Log Analytics page, do the following:
1. In the navigation panel of the Google Cloud console, select Logging, and then select Log Analytics:
  Go to Log Analytics
2. 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 project_ID.region.bucket_ID.view_ID.
  
  For more information about how to access the default query, see Query a log view.
3. Replace TABLE with the name of the table that corresponds to the view that you want to query, then copy the query.
4. 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`
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 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`,
  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`
    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.