Integrate BigQuery with Google SecOps

This document explains how to integrate BigQuery with Google Security Operations (Google SecOps).

Integration version: 15.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 platform, the BigQuery integration can help you solve 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.

Create and configure a service account

To integrate BigQuery with Google SecOps, you can use an existing service account or create a new one. For guidance on creating a service account, see Create service accounts.

If you don't use a workload identity email to configure the integration, create a service account key in JSON after you create a service account. You need to provide the full content of the downloaded JSON key file when configuring the integration parameters.

For security reasons, we recommend you to use workload identity email addresses instead of service account JSON keys. For more information about the workload identities, see Identities for workloads.

Integration parameters

The BigQuery 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 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 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, 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 don't set a value for this parameter, the integration retrieves the project ID from your Google Cloud service account.
`Verify SSL`	Optional. If selected, the integration validates the SSL certificate when connecting to Google Cloud. Selected by default.

For instructions about how to configure 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 how to configure and support multiple instances, see Supporting multiple instances.

Actions

For more information about actions, see Respond to pending actions from Your Workdesk and Perform a manual action.

Ping

Use the Ping action to test connectivity to the BigQuery.

This action doesn't run on Google SecOps entities.

Action inputs

None.

Action outputs

The Ping 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

The Ping action can return the following output messages:

Output message Message description

Successfully connected to the Google BigQuery server with the provided connection parameters! Action succeeded.

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.

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 lists the value for the script result output when using the Ping action:

Script result name	Value
`is_success`	`True` or `False`

Run Custom Query

Use the Run Custom Query action to execute custom queries in BigQuery.

This action doesn't run on Google SecOps entities.

Action inputs

The Run Custom Query action requires the following parameters:

Parameters Description

Parameters	Description
`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`.

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.

Action outputs

The Run Custom 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	Available
Output messages	Available
Script result	Available

JSON result

The following example shows the JSON result output received when using the Run Custom 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 Air Lines 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

The Run Custom Query action can return the following output messages:

Output message Message description

Successfully executed query in Google BigQuery! Action succeeded.

Output message	Message description
`Successfully executed query in Google BigQuery!`	Action succeeded.
`Error executing action "Run Custom Query". Reason: ERROR_REASON`	Action failed. Check the connection to the server, input parameters, or credentials.

Error executing action "Run Custom Query". Reason:
      ERROR_REASON

Action failed.

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

Script result

The following table lists the value for the script result output when using the Run Custom Query 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 Google SecOps entities.

Action inputs

The Run SQL Query action requires the following parameters:

Parameters Description

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`.

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.

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

The Run SQL Query action can return the following output messages:

Output message Message description

Successfully executed query in the BigQuery dataset DATASET_NAME. Action succeeded.

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.

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 lists the value for the script result output when using the Run SQL Query action:

Script result name	Value
`is_success`	`True` or `False`

Need more help? Get answers from Community members and Google SecOps professionals.