Console
    Contact Us Start free

    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:

      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.

    • To use the queries in this document in the Log Analytics page, do the following:

      1. In the Google Cloud console, go to the Log Analytics page:

        Go to Log Analytics

        If you use the search bar to find this page, then select the result whose subheading is Logging .

      2. Identify the table name for the log view that you want to query.

        To identify this name, go to the Log viewslist, locate the log view, and then select Query. The Querypane 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 .

      3. Replace TABLE_NAME_OF_LOG_VIEW with the table name for the log view that you want to query, then copy the query.

      4. Paste the query in the Querypane, 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
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: