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:

• On the organization, folder, or project where you want to export findings from, one of the following:

  • Security Center BigQuery Exports Editor (roles/securitycenter.bigQueryExportsEditor).
  • Security Center Admin (roles/securitycenter.admin).

  To learn more about Security Command Center roles, see Access control.

• On the BigQuery dataset, BigQuery Data Owner (roles/bigquery.dataOwner).

Create a BigQuery dataset

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

Plan for data residency

Preview — Data residency

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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/{name=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 either in the full resource name of the configuration or by using the --locations flag. For example:

gcloud scc scc bqexports get myBigQueryExport organizations/123 \
    --location=locations/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 [e-mail] and is granted the BigQuery Data Editor (roles/bigquery.dataEditor) role at the BigQuery dataset level.

Security Command Center API v2 (Preview)

If you create a BigQuery export by using the v2 API the resulting BigQueryExport resource is handled separately from a BigQueryExport resource that is created by the v1 API.

To work with BigQuery exports that are created by the v2 API, you must use the v2 API or a gcloud CLI command with the --location flag specified.

Similarly, you cannot use the v2 API to work with BigQuery exports that are created with the v1 API.

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 BIG_QUERY_EXPORT \
       --dataset=DATASET_NAME \
       --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
       --location=LOCATION \
       [--description=DESCRIPTION] \
       [--filter=FILTER]
   

   Replace the following:

   • BIG_QUERY_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, specify the Security Command Center location in which to create the BigQuery export. The BigQuery export configuration is stored in this location. Only findings from this location are included in the export.

     If data residency is not enabled, specifying the --location flag creates the BigQuery export by using Security Command Center API v2 and the only valid value for the flag is 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.

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

import com.google.cloud.securitycenter.v1.BigQueryExport;
import com.google.cloud.securitycenter.v1.CreateBigQueryExportRequest;
import com.google.cloud.securitycenter.v1.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.

    // parent: Use any one of the following resource paths:
    //              - organizations/{organization_id}
    //              - folders/{folder_id}
    //              - projects/{project_id}
    String parent = String.format("projects/%s", "your-google-cloud-project-id");

    // filter: Expression that defines the filter to apply across create/update events of findings.
    String filter =
        "severity=\"LOW\" OR severity=\"MEDIUM\" AND "
            + "category=\"Persistence: IAM Anomalous Grant\" AND "
            + "-resource.type:\"compute\"";

    // bigQueryDatasetId: The BigQuery dataset to write findings' updates to.
    String bigQueryDatasetId = "your-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(parent, 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 void createBigQueryExport(
      String parent, 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. After completing all of your requests, call
    // the "close" method on the client to safely clean up any remaining background resources.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {

      // 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("%s/datasets/%s", parent, bigQueryDatasetId))
              .build();

      CreateBigQueryExportRequest bigQueryExportRequest =
          CreateBigQueryExportRequest.newBuilder()
              .setParent(parent)
              .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());
    }
  }
}

Python

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

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

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

    """
    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}
             - folders/{folder_id}
             - projects/{project_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.
        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.SecurityCenterClient()

    # Create the BigQuery export configuration.
    bigquery_export = securitycenter.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 = f"{parent}/datasets/{bigquery_dataset_id}"

    request = securitycenter.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")

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: [e-mail]
   • 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 BIG_QUERY_EXPORT \
       --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
       --location=LOCATION
   

   Replace the following:

   • BIG_QUERY_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: required if either data residency is enabled or the BigQueryExport resource was created by using the v2 API.

     If data residency is enabled, specify the Security Command Center location in which the export is stored.

     If data residency is not enabled, include /locations/LOCATION only if the BigQueryExport resource was created by using the Security Command Center API v2, in which case, the only valid location is 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
     

Java

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

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

import com.google.cloud.securitycenter.v1.BigQueryExport;
import com.google.cloud.securitycenter.v1.GetBigQueryExportRequest;
import com.google.cloud.securitycenter.v1.SecurityCenterClient;
import java.io.IOException;

public class GetBigQueryExport {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.

    // parent: Use any one of the following resource paths:
    //              - organizations/{organization_id}
    //              - folders/{folder_id}
    //              - projects/{project_id}
    String parent = String.format("projects/%s", "your-google-cloud-project-id");

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

    getBigQueryExport(parent, bigQueryExportId);
  }

  // Retrieve an existing BigQuery export.
  public static void getBigQueryExport(String parent, 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. After completing all of your requests, call
    // the "close" method on the client to safely clean up any remaining background resources.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {

      GetBigQueryExportRequest bigQueryExportRequest =
          GetBigQueryExportRequest.newBuilder()
              .setName(String.format("%s/bigQueryExports/%s", parent, bigQueryExportId))
              .build();

      BigQueryExport response = client.getBigQueryExport(bigQueryExportRequest);
      System.out.printf("Retrieved the BigQuery export: %s", response.getName());
    }
  }
}

Python

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

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

def get_bigquery_export(parent: str, bigquery_export_id: str):
    from google.cloud import securitycenter

    """
    Retrieve an existing BigQuery export.
    Args:
        parent: Use any one of the following resource paths:
                 - organizations/{organization_id}
                 - folders/{folder_id}
                 - projects/{project_id}
        bigquery_export_id: Unique identifier that is used to identify the export.
    """

    client = securitycenter.SecurityCenterClient()

    request = securitycenter.GetBigQueryExportRequest()
    request.name = f"{parent}/bigQueryExports/{bigquery_export_id}"

    response = client.get_big_query_export(request)
    print(f"Retrieved the BigQuery export: {response.name}")

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 BIG_QUERY_EXPORT \
       --dataset=DATASET_NAME \
       --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
       --location=LOCATION \
       [--description=DESCRIPTION] \
       [--filter=FILTER]
   

   Replace the following:

   • BIG_QUERY_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: required if either data residency is enabled or the BigQueryExport resource was created by using the v2 API.

     If data residency is enabled, specify the Security Command Center location in which the export is stored.

     If data residency is not enabled, include /locations/LOCATION in the full name or specify the --location flag only if the BigQueryExport resource was created by using the Security Command Center API v2, in which case, the only valid location is 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.

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

import com.google.cloud.securitycenter.v1.BigQueryExport;
import com.google.cloud.securitycenter.v1.SecurityCenterClient;
import com.google.cloud.securitycenter.v1.UpdateBigQueryExportRequest;
import com.google.protobuf.FieldMask;
import java.io.IOException;

public class UpdateBigQueryExport {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.

    // parent: Use any one of the following resource paths:
    //              - organizations/{organization_id}
    //              - folders/{folder_id}
    //              - projects/{project_id}
    String parent = String.format("projects/%s", "your-google-cloud-project-id");

    // filter: Expression that defines the filter to apply across create/update events of findings.
    String filter =
        "severity=\"LOW\" OR severity=\"MEDIUM\" AND "
            + "category=\"Persistence: IAM Anomalous Grant\" AND "
            + "-resource.type:\"compute\"";

    // 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 = "big-query-export-id";

    updateBigQueryExport(parent, filter, bigQueryExportId);
  }

  // Updates an existing BigQuery export.
  public static void updateBigQueryExport(String parent, String filter, 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. After completing all of your requests, call
    // the "close" method on the client to safely clean up any remaining background resources.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {

      //  Set the new values for export configuration.
      BigQueryExport bigQueryExport =
          BigQueryExport.newBuilder()
              .setName(String.format("%s/bigQueryExports/%s", parent, bigQueryExportId))
              .setFilter(filter)
              .build();

      UpdateBigQueryExportRequest request =
          UpdateBigQueryExportRequest.newBuilder()
              .setBigQueryExport(bigQueryExport)
              // Set the update mask to specify which properties should be updated.
              // If empty, all mutable fields will be updated.
              // For more info on constructing field mask path, see the proto or:
              // https://cloud.google.com/java/docs/reference/protobuf/latest/com.google.protobuf.FieldMask
              .setUpdateMask(FieldMask.newBuilder().addPaths("filter").build())
              .build();

      BigQueryExport response = client.updateBigQueryExport(request);
      if (!response.getFilter().equalsIgnoreCase(filter)) {
        System.out.println("Failed to update BigQueryExport!");
        return;
      }
      System.out.println("BigQueryExport updated successfully!");
    }
  }
}

Python

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

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

def update_bigquery_export(parent: str, export_filter: str, bigquery_export_id: str):
    """
    Updates an existing BigQuery export.
    Args:
        parent: Use any one of the following resource paths:
                 - organizations/{organization_id}
                 - folders/{folder_id}
                 - projects/{project_id}
        export_filter: Expression that defines the filter to apply across create/update events of findings.
        bigquery_export_id: 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
    """
    from google.cloud import securitycenter
    from google.protobuf import field_mask_pb2

    client = securitycenter.SecurityCenterClient()

    # Set the new values for export configuration.
    bigquery_export = securitycenter.BigQueryExport()
    bigquery_export.name = f"{parent}/bigQueryExports/{bigquery_export_id}"
    bigquery_export.filter = export_filter

    # Field mask to only update the export filter.
    # Set the update mask to specify which properties should be updated.
    # If empty, all mutable fields will be updated.
    # For more info on constructing field mask path, see the proto or:
    # https://googleapis.dev/python/protobuf/latest/google/protobuf/field_mask_pb2.html
    field_mask = field_mask_pb2.FieldMask(paths=["filter"])

    request = securitycenter.UpdateBigQueryExportRequest()
    request.big_query_export = bigquery_export
    request.update_mask = field_mask

    response = client.update_big_query_export(request)

    if response.filter != export_filter:
        print("Failed to update BigQueryExport!")
        return
    print("BigQueryExport updated successfully!")

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: required if either data residency is enabled or the BigQueryExport resources were created by using the v2 API.

     If data residency is enabled, specify the Security Command Center location in which the exports are stored.

     If data residency is not enabled, including the --location flag lists only the BigQueryExport resources that were created by using the Security Command Center API v2 and the only valid location is 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.

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

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

public class ListBigQueryExports {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.

    // parent: The parent, which owns the collection of BigQuery exports.
    //         Use any one of the following resource paths:
    //              - organizations/{organization_id}
    //              - folders/{folder_id}
    //              - projects/{project_id}
    String parent = String.format("projects/%s", "your-google-cloud-project-id");

    listBigQueryExports(parent);
  }

  // List BigQuery exports in the given parent.
  public static void listBigQueryExports(String parent) 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. After completing all of your requests, call
    // the "close" method on the client to safely clean up any remaining background resources.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {

      ListBigQueryExportsRequest request =
          ListBigQueryExportsRequest.newBuilder().setParent(parent).build();

      ListBigQueryExportsPagedResponse response = client.listBigQueryExports(request);

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

Python

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

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

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

    """
    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}
                 - folders/{folder_id}
                 - projects/{project_id}
    """

    client = securitycenter.SecurityCenterClient()

    request = securitycenter.ListBigQueryExportsRequest()
    request.parent = parent

    response = client.list_big_query_exports(request)

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

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 BIG_QUERY_EXPORT \
       --folder=FOLDER_ID | --organization=ORGANIZATION_ID | --project=PROJECT_ID \
       --location=LOCATION
   

   Replace the following:

   • BIG_QUERY_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: required if either data residency is enabled or the BigQueryExport resource was created by using the v2 API.

     If data residency is enabled, specify the Security Command Center location in which the export is stored.

     If data residency is not enabled, include /locations/LOCATION only if the BigQueryExport resource was created by using the Security Command Center API v2, in which case, the only valid location is 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
     

Java

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

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

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

public class DeleteBigQueryExport {

  public static void main(String[] args) throws IOException {
    // TODO(Developer): Modify the following variable values.

    // parent: Use any one of the following resource paths:
    //              - organizations/{organization_id}
    //              - folders/{folder_id}
    //              - projects/{project_id}
    String parent = String.format("projects/%s", "your-google-cloud-project-id");

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

    deleteBigQueryExport(parent, bigQueryExportId);
  }

  // Delete an existing BigQuery export.
  public static void deleteBigQueryExport(String parent, 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. After completing all of your requests, call
    // the "close" method on the client to safely clean up any remaining background resources.
    try (SecurityCenterClient client = SecurityCenterClient.create()) {

      DeleteBigQueryExportRequest bigQueryExportRequest =
          DeleteBigQueryExportRequest.newBuilder()
              .setName(String.format("%s/bigQueryExports/%s", parent, bigQueryExportId))
              .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.

The following sample uses the v1 API. To modify the sample for v2, replace v1 with v2 and add /locations/LOCATION to the resource name.

For most resources, add /locations/LOCATION to the resource name after /PARENT/PARENT_ID, where PARENT is organizations, folders, or projects.

For findings, add /locations/LOCATION to the resource name after /sources/SOURCE_ID, where SOURCE_ID is the ID of the Security Command Center service that issued the finding.

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}
                 - folders/{folder_id}
                 - projects/{project_id}
        bigquery_export_id: Unique identifier that is used to identify the export.
    """
    from google.cloud import securitycenter

    client = securitycenter.SecurityCenterClient()

    request = securitycenter.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. If you're not in the correct project, follow these steps:

   1. On the toolbar, click the project selector.
   2. Next to Select from, select your organization.
   3. In the list of projects, select your 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:

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.

   

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.

   

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.