Google BigQuery

This document provides guidance on how to integrate BigQuery with the SOAR module of Google Security Operations.

Integration version: 12.0

This integration uses one or more open source components. You can download a zipped copy of the source code of this integration from a Cloud Storage bucket.

Use cases

In the Google SecOps SOAR platform, the Google BigQuery integration solves the following use cases:

  • Threat intelligence enrichment: automatically enrich security alerts with threat intelligence data stored in BigQuery to assess the severity and credibility of potential threats.

    For example, you can configure Google SecOps to query a BigQuery table containing known malicious IP addresses every time when a suspicious IP address triggers an alert. Google SecOps checks for a match in the BigQuery table and retrieves additional context like associated malware families.

  • User behavior analysis: identify anomalous user activity by analyzing historical user activity logs stored in BigQuery to detect insider threats and compromised accounts.

    For example, you can configure Google SecOps to query BigQuery for login attempts outside of a user usual geolocation and trigger an automated incident response for a detected suspicious activity.

  • Security data lake analysis: use the BigQuery querying capabilities to analyze large volumes of security data from various sources aggregated in a data lake to improve threat hunting and incident investigation.

    For example, you can configure Google SecOps to query BigQuery for all events related to a specific file hash across multiple security tools logs that are stored in the data lake.

  • Long-term security data retention: use the BigQuery storage to retain large volumes of security logs for extended periods to support historical analysis, compliance reporting, and forensic investigations.

    For example, you can configure Google SecOps to automatically archive resolved incident data into BigQuery for long-term storage and help ensure data availability for future audits or investigations.

Before you begin

To use the integration, you need a Google Cloud service account. You can use an existing service account or create a new one.

Create a service account

For guidance on creating a service account, see Create service accounts.

If you use a service account to authenticate to Google Cloud, create a service account key in JSON and provide the content of the downloaded JSON file when configuring the integration parameters.

For security reasons, we recommend using Workload Identity Federation for GKE email addresses instead of a service account key. For more information about the workload identities, see Identities for workloads.

Integrate BigQuery with Google SecOps

The integration requires the following parameters:

Parameters Description
Account Type Optional

The type of BigQuery account.

Provide the value that is set in the type parameter of the service account key JSON file.

The default value is service_account.
Project ID Optional

The project ID of the BigQuery account.

Provide the value that is set in the project_id parameter of the authentication JSON file.
Private Key ID Optional

The private key ID of the BigQuery account.

Provide the value that is set in the private_key_id parameter of the authentication JSON file.
Private Key Optional

The private key of the BigQuery account.

Provide the value that is set in the private_key parameter of the authentication JSON file.
Client Email Optional

The client email address of the BigQuery account.

Provide the value that is set in the client_email parameter of the authentication JSON file.
Client ID Optional

The client ID of the BigQuery account.

Provide the value that is set in the client_id parameter of the authentication JSON file.
Auth URI Optional

The authentication URI of the BigQuery account.

Provide the value that is set in the auth_uri parameter of the authentication JSON file.

The default value is https://accounts.google.com/o/oauth2/auth.
Token URI Optional

The token URI of the BigQuery account.

Provide the value that is set in the token_uri parameter of the authentication JSON file.

The default value is https://oauth2.googleapis.com/token.
Auth Provider X509 URL Optional

The authentication provider X.509 URL of the BigQuery account.

Provide the value that is set in the auth_provider_x509_cert_url parameter of the authentication JSON file.

The default value is https://www.googleapis.com/oauth2/v1/certs.
Client X509 URL Optional

The client X.509 URL of the BigQuery account.

Provide the value that is set in the client_x509_cert_url parameter of the authentication JSON file.

The default value is https://www.googleapis.com/oauth2/v1/certs.
Service Account Json File Content Optional

The content of the service account key JSON file.

You can configure either this parameter, or the Workload Identity Email parameter, or set all the preceding integration parameters.

To configure this parameter, provide the full content of the service account key JSON file that you have downloaded when creating a service account.

If you configure this parameter, the integration ignores other connection parameters.
Workload Identity Email Optional

The client email address of your service account.

You can configure either this parameter or the Service Account Json File Content parameter.

If you set this parameter, configure the Quota Project ID parameter.

To impersonate service accounts with the Workload Identity Federation for GKE email address, grant the Service Account Token Creator role to your service account. For more details about workload identities and how to work with them, see Identities for workloads.
Quota Project ID Optional

The Google Cloud project ID that you use for Google Cloud APIs and billing. This parameter requires you to grant the Service Usage Consumer role to your service account.

If you set no value for this parameter, the integration retrieves the project ID from your Google Cloud service account.
Verify SSL Optional

If selected, the integration verifies that the SSL certificate for the connection to the Google Cloud service is valid.

Selected by default.

For detailed instructions about configuring an integration in Google SecOps, see Configure integrations.

You can make changes at a later stage if needed. After you configure an integration instance, you can use it in playbooks. For more information about configuring and supporting multiple instances, see Supporting multiple instances.

Actions

The Google BigQuery integration includes the following actions:

Ping

Use the Ping action to test connectivity to the BigQuery.

This action doesn't run on entities.

Action inputs

None.

Action outputs

The action provides the following outputs:

Action output type Availability
Case wall attachment Not available
Case wall link Not available
Case wall table Not available
Enrichment table Not available
JSON result Not available
Output messages Available
Script result Available
Output messages

On a Case Wall, the Ping action provides the following output messages:

Output message Message description
Successfully connected to the Google BigQuery server with the provided connection parameters! Action succeeded.
Failed to connect to the Google BigQuery server! Error is ERROR_REASON

Action failed.

Check the connection to the server, input parameters, or credentials.
Script result

The following table describes the values for the script result output when using the Ping action:

Script result name Value
is_success True or False

Run SQL Query

Use the Run SQL Query action to execute queries in BigQuery.

This action doesn't run on entities.

Action inputs

The Run SQL Query action requires the following parameters:

Parameters Description
Dataset Name Required

The name of the dataset to use when executing queries.
Query Required

The SQL query to execute.
Max Results To Return Optional

The number of results to return for every response.

The default value is 50 results.

Action outputs

The Run SQL Query action provides the following outputs:

Action output type Availability
Case wall attachment Not available
Case wall link Not available
Case wall table Not available
Enrichment table Not available
JSON result Not available
Output messages Available
Script result Available
JSON result

The following example describes the JSON result output received when using the Run SQL Query action:

{
    "Airport_Code": "CODE",
    "Airport_Name": "NAME",
    "Time_Label": "2015/05",
    "Time_Month": 5,
    "Time_Month_Name": "May",
    "Time_Year": 2015,
    "Statistics___of_Delays_Carrier": 351,
    "Statistics___of_Delays_Late_Aircraft": 546,
    "Statistics___of_Delays_National_Aviation_System": 292,
    "Statistics___of_Delays_Security": 2,
    "Statistics___of_Delays_Weather": 100,
    "Statistics_Carriers_Names": "Example Airlines Inc.,Example Airlines Co.",
    "Statistics_Carriers_Total": 3,
    "Statistics_Flights_Cancelled": 88,
    "Statistics_Flights_Delayed": 1289,
    "Statistics_Flights_Diverted": 32,
    "Statistics_Flights_On_Time": 6182,
    "Statistics_Flights_Total": 7591,
    "Statistics_Minutes_Delayed_Carrier": 19332,
    "Statistics_Minutes_Delayed_Late_Aircraft": 34376,
    "Statistics_Minutes_Delayed_National_Aviation_System": 12346,
    "Statistics_Minutes_Delayed_Security": 48,
    "Statistics_Minutes_Delayed_Total": 76163,
    "Statistics_Minutes_Delayed_Weather": 100061
}
Output messages

On a Case Wall, the Run SQL Query action provides the following output messages:

Output message Message description
Successfully executed query in the BigQuery dataset DATASET_NAME. Action succeeded.
Error executing action "Run SQL Query". Reason: ERROR_REASON

Action failed.

Check the connection to the server, input parameters, or credentials.
Script result

The following table describes the values for the script result output when using the Run SQL Query action:

Script result name Value
is_success True or False