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:

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

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 learn how to use Security Command Center when data residency is enabled, see Security Command Center regional endpoints .

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 Searchbox, 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

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.

  resource 
  
 "google_bigquery_dataset" 
  
 "default" 
  
 { 
  
 dataset_id 
  
 = 
  
 "my-dataset" 
  
 friendly_name 
  
 = 
  
 "test" 
  
 description 
  
 = 
  
 "This is a test description" 
  
 location 
  
 = 
  
 "US" 
  
 default_table_expiration_ms 
  
 = 
  
 3600000 
  
 default_partition_expiration_ms 
  
 = 
  
 null 
  
 labels 
  
 = 
  
 { 
  
 env 
  
 = 
  
 "default" 
  
 } 
  
 lifecycle 
  
 { 
  
 ignore_changes 
  
 = 
  
 [ 
 default_partition_expiration_ms 
 ] 
  
 } 
 } 
 resource 
  
 "google_scc_v2_organization_scc_big_query_export" 
  
 "custom_big_query_export_config" 
  
 { 
  
 name 
  
 = 
  
 "my-export" 
  
 big_query_export_id 
  
 = 
  
 "my-export" 
  
 organization 
  
 = 
  
 "123456789" 
  
 dataset 
  
 = 
  
 google_bigquery_dataset.default.id 
  
 location 
  
 = 
  
 "global" 
  
 description 
  
 = 
  
 "Cloud Security Command Center Findings Big Query Export Config" 
  
 filter 
  
 = 
  
 "state=\"ACTIVE\" AND NOT mute=\"MUTED\"" 
 } 
 

  resource 
  
 "google_folder" 
  
 "folder" 
  
 { 
  
 parent 
  
 = 
  
 "organizations/123456789" 
  
 display_name 
  
 = 
  
 "folder-name" 
  
 deletion_protection 
  
 = 
  
 false 
 } 
 resource 
  
 "google_bigquery_dataset" 
  
 "default" 
  
 { 
  
 dataset_id 
  
 = 
  
 "my_dataset_id" 
  
 friendly_name 
  
 = 
  
 "test" 
  
 description 
  
 = 
  
 "This is a test description" 
  
 location 
  
 = 
  
 "US" 
  
 default_table_expiration_ms 
  
 = 
  
 3600000 
  
 default_partition_expiration_ms 
  
 = 
  
 null 
  
 labels 
  
 = 
  
 { 
  
 env 
  
 = 
  
 "default" 
  
 } 
  
 lifecycle 
  
 { 
  
 ignore_changes 
  
 = 
  
 [ 
 default_partition_expiration_ms 
 ] 
  
 } 
 } 
 resource 
  
 "google_scc_v2_folder_scc_big_query_export" 
  
 "custom_big_query_export_config" 
  
 { 
  
 big_query_export_id 
  
 = 
  
 "my-export" 
  
 folder 
  
 = 
  
 google_folder.folder.folder_id 
  
 dataset 
  
 = 
  
 google_bigquery_dataset.default.id 
  
 location 
  
 = 
  
 "global" 
  
 description 
  
 = 
  
 "Cloud Security Command Center Findings Big Query Export Config" 
  
 filter 
  
 = 
  
 "state=\"ACTIVE\" AND NOT mute=\"MUTED\"" 
 } 
 

  resource 
  
 "google_bigquery_dataset" 
  
 "default" 
  
 { 
  
 dataset_id 
  
 = 
  
 "my_dataset_id" 
  
 friendly_name 
  
 = 
  
 "test" 
  
 description 
  
 = 
  
 "This is a test description" 
  
 location 
  
 = 
  
 "US" 
  
 default_table_expiration_ms 
  
 = 
  
 3600000 
  
 default_partition_expiration_ms 
  
 = 
  
 null 
  
 labels 
  
 = 
  
 { 
  
 env 
  
 = 
  
 "default" 
  
 } 
  
 lifecycle 
  
 { 
  
 ignore_changes 
  
 = 
  
 [ 
 default_partition_expiration_ms 
 ] 
  
 } 
 } 
 resource 
  
 "google_scc_v2_project_scc_big_query_export" 
  
 "custom_big_query_export_config" 
  
 { 
  
 name 
  
 = 
  
 "my-export" 
  
 big_query_export_id 
  
 = 
  
 "my-export" 
  
 project 
  
 = 
  
 "my-project-name" 
  
 dataset 
  
 = 
  
 google_bigquery_dataset.default.id 
  
 location 
  
 = 
  
 "global" 
  
 description 
  
 = 
  
 "Cloud Security Command Center Findings Big Query Export Config" 
  
 filter 
  
 = 
  
 "state=\"ACTIVE\" AND NOT mute=\"MUTED\"" 
 } 
 

  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 
 ; 
  
 } 
  
 } 
 } 
 

  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.

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

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.

View all export configurations

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

  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 
 ; 
  
 } 
  
 } 
 } 
 

  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.

  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 
 ); 
  
 } 
  
 } 
 } 
 

  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 Explorerpane, expand the node for your project.

4. Expand your dataset.

5. Click the findingstable.

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 datatab, click the BigQuerycard.
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 projectstab, 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 > Barpane, on the Datatab, set the following fields:

   1. In the Dimensionfield, select finding.severity.
   2. In the Breakdown Dimensionfield, select finding.category.

   

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

What's next

• Learn how to create a bulk export to BigQuery ( Preview ).
• Learn how to run a query in BigQuery .