Stream findings to BigQuery for analysis

This page describes how to stream new and updated findings to a BigQuery dataset by using the Security Command Center export function for BigQuery. Existing findings are not sent to BigQuery unless they are updated.

BigQuery is Google Cloud's fully managed, petabyte-scale, and cost-effective analytics data warehouse that lets you run analytics over vast amounts of data in near real time. You can use BigQuery to run queries against new and updated findings, filter data to find what you need, and generate custom reports. To learn more about BigQuery, see the BigQuery documentation.

Overview

When you enable this feature, new findings that are written to Security Command Center are exported to a BigQuery table in near real time. You can then integrate the data into existing workflows and create custom analyses. You can enable this feature at the organization, folder, and project levels to export findings based on your requirements.

This feature is the recommended way to export Security Command Center findings to BigQuery, because it's fully managed and doesn't require performing manual operations or writing custom code.

Dataset structure

This feature adds each new finding and its subsequent updates as new rows in the findings table, which is clustered by source_id, finding_id, and event_time.

When a finding is updated, this feature creates multiple finding records with the same source_id and finding_id values, but with different event_time values. This dataset structure lets you view how each finding's state changes over time.

Note that duplicate entries might exist in your dataset. To parse them out, you can use the DISTINCT clause, as shown in the first example query.

Each dataset contains a findings table, which has the following fields:

Field Description
source_id

A unique identifier that Security Command Center assigns to the source of a finding. For example, all findings from the Cloud Anomaly Detection source have the same source_id value.

Example: 1234567890
finding_id Unique identifier that represents the finding. It is unique within a source for an organization. It is alphanumeric and has less than or equal to 32 characters.
event_time

The time that the event took place or the time that an update to the finding occurred. For example, if the finding represents an open firewall, then event_time captures the time the detector believes the firewall was opened. If the finding is resolved afterward, then this time reflects when that finding was resolved.

Example: 2019-09-26 12:48:00.985000 UTC
finding

A record of assessment data like security, risk, health, or privacy, that is ingested into Security Command Center for presentation, notification, analysis, policy testing, and enforcement. For example, a cross-site scripting (XSS) vulnerability in an App Engine application is a finding.

For more information about the nested fields, see the API reference for the Finding object.

resource

Information related to the Google Cloud resource that is associated with this finding.

For more information about the nested fields, see the API reference for the Resource object.

Cost

You incur BigQuery charges related to this feature. For more information, see BigQuery pricing.

Before you begin

You must complete these steps before you enable this feature.

Set up permissions

To complete this guide, you must have the following Identity and Access Management (IAM) roles:

Create a BigQuery dataset

Create a BigQuery dataset. For more information, see Creating datasets.

Plan for data residency

If data residency is enabled for Security Command Center, the configurations that define streaming exports to BigQuery—BigQueryExport resources—are subject to data residency control and are stored in a Security Command Center location that you select.

To export findings in a Security Command Center location to BigQuery, you must configure the BigQuery export in the same Security Command Center location as the findings.

Because the filters that are used in BigQuery exports can contain data that is subject to residency controls, make sure you specify the correct location before you create them. Security Command Center does not restrict which location you create exports in.

BigQuery exports are stored only in the location in which they are created and cannot be viewed or edited in other locations.

After you create a BigQuery export, you can't change its location. To change the location, you need to delete the BigQuery export and recreate it in the new location.

To retrieve a BigQuery export by using API calls, you need to specify the location in the full resource name of the bigQueryExport. For example:

GET https://securitycenter.googleapis.com/v2/organizations/123/locations/eu/bigQueryExports/my-export-01

Similarly, to retrieve a BigQuery export by using the gcloud CLI, you need to specify the location by using the --location flag. For example:

gcloud scc bqexports get myBigQueryExport --organization=123 \
    --location=us

Export findings from Security Command Center to BigQuery

To export findings, first enable the Security Command Center API.

Enabling the Security Command Center API

To enable the Security Command Center API:

  1. Go to the API Library page in the Google Cloud console.

    Go to API Library

  2. Select the project for which you want to enable the Security Command Center API.

  3. In the Search box, enter Security Command Center, and then click Security Command Center in the search results.

  4. On the API page that appears, click Enable.

The Security Command Center API is enabled for your project. Next, you use gcloud CLI to create a new export configuration to BigQuery.

Granting perimeter access in VPC Service Controls

If you use VPC Service Controls and your BigQuery dataset is part of a project inside a service perimeter, you must grant access to projects in order to export findings.

To grant access to projects, create ingress and egress rules for the principals and projects that you are exporting findings from. The rules allow access to protected resources and let BigQuery verify that users have the setIamPolicy permission on the BigQuery dataset.

Before setting up a new export to BigQuery

  1. Go to the VPC Service Controls page in the Google Cloud console.

    Go to VPC Service Controls

  2. If necessary, select your organization.

  3. Click the name of the service perimeter you want to change.

    To find the service perimeter you need to modify, you can check your logs for entries that show RESOURCES_NOT_IN_SAME_SERVICE_PERIMETER violations. In those entries, check the servicePerimeterName field: accessPolicies/ACCESS_POLICY_ID/servicePerimeters/SERVICE_PERIMETER_NAME.

  4. Click Edit Perimeter.

  5. In the navigation menu, click Ingress Policy.

  6. To configure ingress rules for users or service accounts, use the following parameters:

    • FROM attributes of the API client:
      • In the Identities menu, choose Selected identities.
      • In the Source menu, select All Sources.
      • Click Select, and then enter the principal that is used to call the Security Command Center API.
    • TO attributes of Google Cloud services/resources:
      • In the Project menu, choose Selected projects.
      • Click Select, and then enter the project that contains the BigQuery dataset.
      • In the Services menu, choose Selected services, and then select BigQuery API.
      • In the Methods menu, choose All actions.

  7. Click Save.

  8. In the navigation menu, click Egress Policy.

  9. Click Add Rule.

  10. To configure egress rules for user or service accounts, enter the following parameters:

    • FROM attributes of the API client:
      • In the Identities menu, choose Selected identities.
      • Click Select, and then enter the principal that is used to call the Security Command Center API.
    • TO attributes of Google Cloud services/resources:
      • In the Project menu, choose All projects.
      • In the Services menu, choose Selected services, and then select BigQuery API.
      • In the Methods menu, choose All actions.

  11. Click Save.

Set up a new export to BigQuery

In this step, you create an export configuration to export findings to a BigQuery instance. You can create export configurations at the project, folder, or organization level. For example, if you want to export findings from a project to a BigQuery dataset, you create an export configuration at the project level to export only the findings related to that project. Optionally, you can specify filters to export certain findings only.

Be sure to create your export configurations at the appropriate level. For example, if you create an export configuration in Project B to export findings from Project A and you define filters such as resource.project_display_name: project-a-id, the configuration does not export any findings.

You can create a maximum of 500 export configurations to BigQuery for your organization. You can use the same dataset for multiple export configurations. If you use the same dataset, all updates will be made to the same findings table.

When you create your first export configuration, a service account is automatically created for you. This service account is required to create or update the findings table within a dataset and to export findings to the table. It has the form service-org-ORGANIZATION_ID@gcp-sa-scc-notification.iam.gservicaccount.com and is granted the BigQuery Data Editor (roles/bigquery.dataEditor) role at the BigQuery dataset level.

In the Google Cloud console, some BigQueryExport resources might have a Legacy label, which indicates that they were created with the v1 Security Command Center API. You can manage these BigQueryExport resources with the Google Cloud console; the gcloud CLI; the v1 Security Command Center API; or the v1 client libraries for Security Command Center.

To manage these BigQueryExport resources with the gcloud CLI, you must not specify a location when you run the gcloud CLI command.

gcloud

  1. Go to the Google Cloud console.

    Go to the Google Cloud console

  2. Select the project for which you enabled the Security Command Center API.

  3. Click Activate Cloud Shell.

  4. To create a new export configuration, run the following command:

    gcloud scc bqexports create BIGQUERY_EXPORT \
    --dataset=DATASET_NAME \
    --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
    --location=LOCATION \
    [--description=DESCRIPTION] \
    [--filter=FILTER]

    Replace the following:

    • BIGQUERY_EXPORT with a name for this export configuration.
    • DATASET_NAME with the name of the BigQuery dataset—for example, projects/PROJECT_ID/datasets/DATASET_ID.
    • FOLDER_ID, ORGANIZATION_ID, or PROJECT_ID with the name of your folder, organization, or project. You must set one of these options. For folders and organizations, the name is the folder ID or the organization ID. For projects, the name is the project number or the project ID.
    • LOCATION: if data residency is enabled, the Security Command Center location in which to create an export configuration; if data residency is not enabled, use the value global.
    • DESCRIPTION with a human-readable description of the export configuration. This variable is optional.
    • FILTER with an expression that defines what findings to include in the export. For example, if you want to filter on the XSS_SCRIPTING category, type "category=\"XSS_SCRIPTING\". This variable is optional.

Java

To authenticate to Security Command Center, set up Application Default Credentials. For more information, see Set up authentication for a local development environment. 


import com.google.cloud.securitycenter.v2.BigQueryExport;
import com.google.cloud.securitycenter.v2.CreateBigQueryExportRequest;
import com.google.cloud.securitycenter.v2.OrganizationLocationName;
import com.google.cloud.securitycenter.v2.SecurityCenterClient;
import java.io.IOException;
import java.util.UUID;

public class CreateBigQueryExport {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.
    // organizationId: Google Cloud Organization id.
    String organizationId = "{google-cloud-organization-id}";

    // projectId: Google Cloud Project id.
    String projectId = "{your-project}";

    // Specify the location.
    String location = "global";

    // filter: Expression that defines the filter to apply across create/update events of findings.
    String filter = "severity=\"LOW\" OR severity=\"MEDIUM\"";

    // bigQueryDatasetId: The BigQuery dataset to write findings' updates to.
    String bigQueryDatasetId = "{bigquery-dataset-id}";

    // bigQueryExportId: Unique identifier provided by the client.
    // For more info, see:
    // https://cloud.google.com/security-command-center/docs/how-to-analyze-findings-in-big-query#export_findings_from_to
    String bigQueryExportId = "default-" + UUID.randomUUID().toString().split("-")[0];

    createBigQueryExport(organizationId, location, projectId, filter, bigQueryDatasetId,
        bigQueryExportId);
  }

  // Create export configuration to export findings from a project to a BigQuery dataset.
  // Optionally specify filter to export certain findings only.
  public static BigQueryExport createBigQueryExport(String organizationId, String location,
      String projectId, String filter, String bigQueryDatasetId, String bigQueryExportId)
      throws IOException {
    // Initialize client that will be used to send requests. This client only needs to be created
    // once, and can be reused for multiple requests.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {
      OrganizationLocationName organizationName = OrganizationLocationName.of(organizationId,
          location);
      // Create the BigQuery export configuration.
      BigQueryExport bigQueryExport =
          BigQueryExport.newBuilder()
              .setDescription(
                  "Export low and medium findings if the compute resource "
                      + "has an IAM anomalous grant")
              .setFilter(filter)
              .setDataset(String.format("projects/%s/datasets/%s", projectId, bigQueryDatasetId))
              .build();

      CreateBigQueryExportRequest bigQueryExportRequest =
          CreateBigQueryExportRequest.newBuilder()
              .setParent(organizationName.toString())
              .setBigQueryExport(bigQueryExport)
              .setBigQueryExportId(bigQueryExportId)
              .build();

      // Create the export request.
      BigQueryExport response = client.createBigQueryExport(bigQueryExportRequest);

      System.out.printf("BigQuery export request created successfully: %s\n", response.getName());
      return response;
    }
  }
}

Python

To authenticate to Security Command Center, set up Application Default Credentials. For more information, see Set up authentication for a local development environment. 



def create_bigquery_export(
    parent: str, export_filter: str, bigquery_dataset_id: str, bigquery_export_id: str
):
    from google.cloud import securitycenter_v2

    """
    Create export configuration to export findings from a project to a BigQuery dataset.
    Optionally specify filter to export certain findings only.

    Args:
        parent: Use any one of the following resource paths:
             - organizations/{organization_id}/locations/{location_id}
             - folders/{folder_id}/locations/{location_id}
             - projects/{project_id}/locations/{location_id}
        export_filter: Expression that defines the filter to apply across create/update events of findings.
        bigquery_dataset_id: The BigQuery dataset to write findings' updates to.
             - projects/{PROJECT_ID}/datasets/{BIGQUERY_DATASET_ID}
        bigquery_export_id: Unique identifier provided by the client.
             - example id: f"default-{str(uuid.uuid4()).split('-')[0]}"
        For more info, see:
        https://cloud.google.com/security-command-center/docs/how-to-analyze-findings-in-big-query#export_findings_from_to
    """
    client = securitycenter_v2.SecurityCenterClient()

    # Create the BigQuery export configuration.
    bigquery_export = securitycenter_v2.BigQueryExport()
    bigquery_export.description = "Export low and medium findings if the compute resource has an IAM anomalous grant"
    bigquery_export.filter = export_filter
    bigquery_export.dataset = bigquery_dataset_id

    request = securitycenter_v2.CreateBigQueryExportRequest()
    request.parent = parent
    request.big_query_export = bigquery_export
    request.big_query_export_id = bigquery_export_id

    # Create the export request.
    response = client.create_big_query_export(request)

    print(f"BigQuery export request created successfully: {response.name}\n")
    return response

You should see findings in your BigQuery dataset within about 15 minutes after you create the export configuration. After the BigQuery table is created, any new and updated findings that match your filter and scope will appear in the table in near real time.

To review your findings, see Review findings.

Create an ingress rule for the new export to BigQuery

If you use VPC Service Controls and your BigQuery dataset is part of a project inside a service perimeter, you must create an ingress rule for a new export to BigQuery.

  1. Re-open the service perimeter from Set up a new export to BigQuery.

    Go to VPC Service Controls

  2. Click Ingress Policy.

  3. Click Add Rule.

  4. To configure the ingress rule for the export configurations, enter the following parameters:

    • FROM attributes of the API client:
      • In the Source drop-down menu, select All Sources.
      • In the Identities drop-down menu, choose Selected identities.
      • Click Select, and then enter the name of the BigQuery export configuration service account: service-org-ORGANIZATION_ID@gcp-sa-scc-notification.iam.gserviceaccount.com
    • TO attributes of GCP services/resources:
      • In the Project drop-down menu, choose Selected projects.
      • Click Select, and then select the project that contains the BigQuery dataset.
      • In the Services drop-down menu, choose Selected services, and then select BigQuery API.
      • In the Methods drop-down menu, choose All actions.

  5. In the navigation menu, click Save.

The selected projects, users, and service accounts can now access the protected resources and export findings.

If you followed all of the steps in this guide, and exports are working properly, you can now delete the following:

  • The ingress rule for the principal
  • The egress rule for the principal

Those rules were only needed to configure the export configuration. However, for export configurations to continue working, you must keep the ingress rule that you created previously, which lets Security Command Center export findings to your BigQuery dataset behind the service perimeter.

View the details of an export configuration

gcloud

  1. Go to the Google Cloud console.

    Go to the Google Cloud console

  2. Select the project for which you enabled the Security Command Center API.

  3. Click Activate Cloud Shell.

  4. To verify the details of the export configuration, run the following command:

    gcloud scc bqexports get BIGQUERY_EXPORT \
    --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
    --location=LOCATION

    Replace the following:

    • BIGQUERY_EXPORT with the name for this export configuration.
    • FOLDER_ID, ORGANIZATION_ID, or PROJECT_ID with the name of your folder, organization, or project. You must set one of these options. For folders and organizations, the name is the folder ID or the organization ID. For projects, the name is the project number or the project ID.

    • LOCATION: if data residency is enabled, the Security Command Center location in which to create an export configuration; if data residency is not enabled, use the value global.

      For example, to get an export configuration named my-bq-export from an organization with an organization ID set to 123, run:

      gcloud scc bqexports get my-bq-export \
    --organization=123 \
    --location=global

Update an export configuration

When necessary, you can modify the filter, dataset, and description of an existing export configuration. You cannot change the name of the export configuration.

gcloud

  1. Go to the Google Cloud console.

    Go to the Google Cloud console

  2. Select the project for which you enabled the Security Command Center API.

  3. Click Activate Cloud Shell.

  4. To update an export configuration, run the following command:

    gcloud scc bqexports update BIGQUERY_EXPORT \
    --dataset=DATASET_NAME \
    --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
    --location=LOCATION \
    [--description=DESCRIPTION] \
    [--filter=FILTER]

    Replace the following:

    • BIGQUERY_EXPORT with the name for the export configuration that you want to update.
    • DATASET_NAME with the name of the BigQuery dataset—for example, projects/PROJECT_ID/datasets/DATASET_ID.
    • FOLDER_ID, ORGANIZATION_ID, or PROJECT_ID with the name of your folder, organization, or project. You must set one of these options. For folders and organizations, the name is the folder ID or the organization ID. For projects, the name is the project number or the project ID.
    • LOCATION: if data residency is enabled, the Security Command Center location in which to update the export configuration; if data residency is not enabled, use the value global.
    • DESCRIPTION with a human-readable description of the export configuration. This variable is optional.
    • FILTER with an expression that defines what findings to include in the export. For example, if you want to filter on the XSS_SCRIPTING category, type "category=\"XSS_SCRIPTING\". This variable is optional.

View all export configurations

You can view all the export configurations within your organization, folder, or project.

gcloud

  1. Go to the Google Cloud console.

    Go to the Google Cloud console

  2. Select the project for which you enabled the Security Command Center API.

  3. Click Activate Cloud Shell.

  4. To list the export configurations, run the following command:

    gcloud scc bqexports list \
    --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
    --location=LOCATION \
    [--limit=LIMIT] \
    [--page-size=PAGE_SIZE]

    Replace the following:

    • FOLDER_ID, ORGANIZATION_ID, or PROJECT_ID with the name of your folder, organization, or project. You must set one of these options. For folders and organizations, the name is the folder ID or the organization ID. For projects, the name is the project number or the project ID.

      If you specify an organization ID, the list includes all export configurations defined in that organization, including those at the folder and project levels. If you specify a folder ID, the list includes all export configurations defined at the folder level and in the projects within that folder. If you specify a project number or project ID, the list includes all export configurations for that project only.

    • LOCATION: if data residency is enabled, the Security Command Center location in which to list export configurations; if data residency is not enabled, use the value global.

    • LIMIT with the number of export configurations that you want to see. This variable is optional.

    • PAGE_SIZE with a page size value. This variable is optional.

Java

To authenticate to Security Command Center, set up Application Default Credentials. For more information, see Set up authentication for a local development environment. 


import com.google.cloud.securitycenter.v2.BigQueryExport;
import com.google.cloud.securitycenter.v2.ListBigQueryExportsRequest;
import com.google.cloud.securitycenter.v2.OrganizationLocationName;
import com.google.cloud.securitycenter.v2.SecurityCenterClient;
import com.google.cloud.securitycenter.v2.SecurityCenterClient.ListBigQueryExportsPagedResponse;
import java.io.IOException;

public class ListBigQueryExports {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.
    // organizationId: Google Cloud Organization id.
    String organizationId = "{google-cloud-organization-id}";

    // Specify the location to list the findings.
    String location = "global";

    listBigQueryExports(organizationId, location);
  }

  // List BigQuery exports in the given parent.
  public static ListBigQueryExportsPagedResponse listBigQueryExports(String organizationId,
      String location) throws IOException {
    // Initialize client that will be used to send requests. This client only needs to be created
    // once, and can be reused for multiple requests.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {
      OrganizationLocationName organizationName = OrganizationLocationName.of(organizationId,
          location);

      ListBigQueryExportsRequest request = ListBigQueryExportsRequest.newBuilder()
          .setParent(organizationName.toString())
          .build();

      ListBigQueryExportsPagedResponse response = client.listBigQueryExports(request);

      System.out.println("Listing BigQuery exports:");
      for (BigQueryExport bigQueryExport : response.iterateAll()) {
        System.out.println(bigQueryExport.getName());
      }
      return response;
    }
  }
}

Python

To authenticate to Security Command Center, set up Application Default Credentials. For more information, see Set up authentication for a local development environment. 

def list_bigquery_exports(parent: str):
    from google.cloud import securitycenter_v2

    """
    List BigQuery exports in the given parent.
    Args:
         parent: The parent which owns the collection of BigQuery exports.
             Use any one of the following resource paths:
                 - organizations/{organization_id}/locations/{location_id}
                 - folders/{folder_id}/locations/{location_id}
                 - projects/{project_id}/locations/{location_id}
    """

    client = securitycenter_v2.SecurityCenterClient()

    request = securitycenter_v2.ListBigQueryExportsRequest()
    request.parent = parent

    response = client.list_big_query_exports(request)

    print("Listing BigQuery exports:")
    for bigquery_export in response:
        print(bigquery_export.name)
    return response

Delete an export configuration

If you no longer require an export configuration, you can delete it.

gcloud

  1. Go to the Google Cloud console.

    Go to the Google Cloud console

  2. Select the project for which you enabled the Security Command Center API.

  3. Click Activate Cloud Shell.

  4. To delete an export configuration, run the following command:

    gcloud scc bqexports delete BIGQUERY_EXPORT \
    --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
    --location=LOCATION

    Replace the following:

    • BIGQUERY_EXPORT with a name for the export configuration that you want to delete.
    • FOLDER_ID, ORGANIZATION_ID, or PROJECT_ID with the name of your folder, organization, or project. You must set one of these options. For folders and organizations, the name is the folder ID or the organization ID. For projects, the name is the project number or the project ID.

    • LOCATION: if data residency is enabled, the Security Command Center location in which to delete the export configuration; if data residency is not enabled, use the value global.

      For example, to delete an export configuration named my-bq-export from an organization with an organization ID set to 123, run:

      gcloud scc bqexports delete my-bq-export \
    --organization=123 \
    --location=global

Java

To authenticate to Security Command Center, set up Application Default Credentials. For more information, see Set up authentication for a local development environment. 


import com.google.cloud.securitycenter.v2.BigQueryExportName;
import com.google.cloud.securitycenter.v2.DeleteBigQueryExportRequest;
import com.google.cloud.securitycenter.v2.SecurityCenterClient;
import java.io.IOException;

public class DeleteBigQueryExport {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.
    // organizationId: Google Cloud Organization id.
    String organizationId = "{google-cloud-organization-id}";

    // Specify the location to list the findings.
    String location = "global";

    // bigQueryExportId: Unique identifier that is used to identify the export.
    String bigQueryExportId = "{bigquery-export-id}";

    deleteBigQueryExport(organizationId, location, bigQueryExportId);
  }

  // Delete an existing BigQuery export.
  public static void deleteBigQueryExport(String organizationId, String location,
      String bigQueryExportId)
      throws IOException {
    // Initialize client that will be used to send requests. This client only needs to be created
    // once, and can be reused for multiple requests.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {
      // Optionally BigQueryExportName or String can be used
      // String bigQueryExportName = String.format("organizations/%s/locations/%s
      // /bigQueryExports/%s",organizationId,location, bigQueryExportId);
      BigQueryExportName bigQueryExportName = BigQueryExportName.of(organizationId, location,
          bigQueryExportId);

      DeleteBigQueryExportRequest bigQueryExportRequest =
          DeleteBigQueryExportRequest.newBuilder()
              .setName(bigQueryExportName.toString())
              .build();

      client.deleteBigQueryExport(bigQueryExportRequest);
      System.out.printf("BigQuery export request deleted successfully: %s", bigQueryExportId);
    }
  }
}

Python

To authenticate to Security Command Center, set up Application Default Credentials. For more information, see Set up authentication for a local development environment. 

def delete_bigquery_export(parent: str, bigquery_export_id: str):
    """
    Delete an existing BigQuery export.
    Args:
        parent: Use any one of the following resource paths:
                 - organizations/{organization_id}/locations/{location_id}
                 - folders/{folder_id}/locations/{location_id}
                 - projects/{project_id}/locations/{location_id}
        bigquery_export_id: Unique identifier that is used to identify the export.
    """
    from google.cloud import securitycenter_v2

    client = securitycenter_v2.SecurityCenterClient()

    request = securitycenter_v2.DeleteBigQueryExportRequest()
    request.name = f"{parent}/bigQueryExports/{bigquery_export_id}"

    client.delete_big_query_export(request)
    print(f"BigQuery export request deleted successfully: {bigquery_export_id}")

After you delete the export configuration, you can remove the data from Looker Studio. For more information, see Remove, delete, and restore a data source.

Review findings in BigQuery

After you create an export configuration, new findings are exported to the BigQuery dataset in the project that you specified.

To review findings in BigQuery, do the following:

  1. Go to the project in BigQuery.

    Go to BigQuery

  2. Select a project.

  3. In the Explorer pane, expand the node for your project.

  4. Expand your dataset.

  5. Click the findings table.

  6. On the tab that opens, click Preview. A sample set of data is displayed.

Useful queries

This section provides example queries for analyzing findings data. In the following examples, replace DATASET with the name assigned to your dataset and PROJECT_ID with the project name for your dataset.

To troubleshoot any errors you encounter, see Error messages.

The number of new findings created and updated every day

SELECT
    FORMAT_DATETIME("%Y-%m-%d", event_time) AS date,
    count(DISTINCT finding_id)
FROM `PROJECT_ID.DATASET.findings`
GROUP BY date
ORDER BY date DESC

The latest finding record for each finding

SELECT
    * EXCEPT(row)
FROM (
    SELECT *, ROW_NUMBER() OVER(
        PARTITION BY finding_id
        ORDER BY event_time DESC, finding.mute_update_time DESC
    ) AS row
    FROM `PROJECT_ID.DATASET.findings`
)
WHERE row = 1

Current findings that are active, ordered by time

WITH latestFindings AS (
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT finding_id, event_time, finding
FROM latestFindings
WHERE finding.state = "ACTIVE"
ORDER BY event_time DESC

Current findings that are in a project

WITH latestFindings AS (
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT finding_id, event_time, finding, resource
FROM latestFindings
WHERE resource.project_display_name = 'PROJECT'

Replace PROJECT with the project name.

Current findings that are in a folder

WITH latestFindings AS(
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT finding_id, event_time, finding, resource
FROM latestFindings
CROSS JOIN UNNEST(resource.folders) AS folder
WHERE folder.resource_folder_display_name = 'FOLDER'

Replace FOLDER with the folder name.

Current findings from scanner Logging Scanner

WITH latestFindings AS (
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT finding_id, event_time, finding
FROM latestFindings
CROSS JOIN UNNEST(finding.source_properties) AS source_property
WHERE source_property.key = "ScannerName"
  AND source_property.value = "LOGGING_SCANNER"

Current active findings of type Persistence: IAM Anomalous Grant

WITH latestFindings AS(
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT finding_id, event_time, finding
FROM latestFindings
WHERE finding.state = "ACTIVE"
  AND finding.category = "Persistence: IAM Anomalous Grant"

Correlate active findings of a given type with Cloud Audit Logs

This example query helps investigate anomalous IAM grant findings from Event Threat Detection using Cloud Audit Logs by displaying the grantor's sequence of Admin Activity actions during the time window preceding and succeeding the anomalous IAM grant action. This following query correlates Admin Activity logs between 1 hour before and 1 hour after the finding's timestamp.

WITH latestFindings AS(
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT
  finding_id,
  ANY_VALUE(event_time) as event_time,
  ANY_VALUE(finding.access.principal_email) as grantor,
  JSON_VALUE_ARRAY(ANY_VALUE(finding.source_properties_json), '$.properties.sensitiveRoleGrant.members') as grantees,
  ARRAY_AGG(
    STRUCT(
      timestamp,
      IF(timestamp < event_time, 'before', 'after') as timeline,
      protopayload_auditlog.methodName,
      protopayload_auditlog.resourceName,
      protopayload_auditlog.serviceName
    )
    ORDER BY timestamp ASC
  ) AS recent_activity
FROM (
  SELECT
    f.*,
    a.*,
  FROM latestFindings AS f
  LEFT JOIN `PROJECT_ID.DATASET.cloudaudit_googleapis_com_activity` AS a
  ON a.protopayload_auditlog.authenticationInfo.principalEmail = f.finding.access.principal_email
  WHERE f.finding.state = "ACTIVE"
    AND f.finding.category = "Persistence: IAM Anomalous Grant"
    AND a.timestamp >= TIMESTAMP_SUB(f.event_time, INTERVAL 1 HOUR)
    AND a.timestamp <= TIMESTAMP_ADD(f.event_time, INTERVAL 1 HOUR)
  )
GROUP BY
  finding_id
ORDER BY
  event_time DESC

The output is similar to the following:

Screenshot of query results showing findings with correlated audit logs

Create charts in Looker Studio

Looker Studio lets you create interactive reports and dashboards.

In general, you incur BigQuery usage costs when accessing BigQuery through Looker Studio. For more information, see Visualizing BigQuery data using Looker Studio.

To create a chart that visualizes findings data by severity and category, do the following:

  1. Open Looker Studio and sign in.
  2. If prompted, provide additional information and set up other preferences. Read the terms of service and, if you're satisfied, continue.
  3. Click Blank Report.
  4. On the Connect to data tab, click the BigQuery card.
  5. If prompted, authorize Looker Studio to access BigQuery projects.

  6. Connect to your findings data:

    1. For Project, select that project for your dataset. Or, in the My projects tab, enter your project ID to search for it.
    2. For Dataset, click the name of your dataset.
    3. For Table, click findings.
    4. Click Add.
    5. In the dialog, click Add to report.

  7. After the report is added, click Add a chart.

  8. Click Stacked column chart, and then click the area where you want to place it.

    Screenshot of the chart selection

  9. In the Chart > Bar pane, on the Data tab, set the following fields:

    1. In the Dimension field, select finding.severity.
    2. In the Breakdown Dimension field, select finding.category.
    Screenshot of a chart of findings categorized by severity and subcategorized by category

The report is updated to show multiple columns with findings split by severity and category.

What's next

Learn how to run a query in BigQuery.