Google BigQuery
Integration for Google BigQuery, a data warehouse for querying and analyzing large databases. In all commands, for any argument not specified, the BigQuery default value for that argument will be applied.
Database · Google BigQuery
Details
| ID | Google BigQuery |
|---|---|
| Provider | |
| Category | Database |
| From Version | 5.0.0 |
| Docker Image | demisto/bigquery:1.0.0.10408886 |
| Supported Modules | Agentix XSIAM |
README
Google BigQuery is a data warehouse for querying and analyzing large databases.
Configure Google BigQuery on Cortex XSOAR
- Navigate to Settings > Integrations > Servers & Services.
- Search for Google BigQuery.
- Click Add instance to create and configure a new integration instance.
- Name: a textual name for the integration instance.
- Google service account JSON (a credentials JSON generated from Google API Manager or from GCP console)
- Region: The geographic location where the connectivity test should run.
- Click Test to validate the URLs, token, and connection.
Commands
You can execute these commands from the Cortex XSOAR CLI, as part of an automation, or in a playbook.
After you successfully execute a command, a DBot message appears in the War Room with the command details.
1. Perform a query in BigQuery
Performs a query on BigQuery.
Base Command
bigquery-query
Input
| Argument Name | Description | Required |
|---|---|---|
| query | A query string (in BigQuery query syntax). For more information about the standard syntax, see the BigQuery documentation. | Required |
| location | The geographic location where the job should run. Required for locations other than US and EU. | Optional |
| allow_large_results | Allow query results tables larger than 128 MB compressed (legacy SQL only) | Optional |
| default_dataset | A string of the fully-qualified dataset ID in standard SQL format. The value must include a project ID and dataset ID, separated by periods. | Optional |
| destination_table | The table to which the results are written. Default value is “None”. | Optional |
| kms_key_name | Custom encryption configuration for the destination table. | Optional |
| dry_run | If “true,” BigQuery doesn’t run the job. Instead, if the query is valid, BigQuery returns statistics about the job, such as how many bytes would be processed. If the query is invalid, an error is returned. The default value is “false”. | Optional |
| priority | Priority of the query (“INTERACTIVE” or “BATCH”). A query set as INTERACTIVE will be run on-demand, at the next possible time. A query set as BATCH will start as soon as idle resources are available, and changed to INTERACTIVE priority if it wasn’t started within 24 hours. The default value is “INTERACTIVE”. | Optional |
| use_query_cache | Whether to look for the query results in the cache. | Optional |
| use_legacy_sql | Whether to use legacy SQL syntax. | Optional |
| job_id | The ID of the job. The ID must contain only letters (a-z, A-Z), numbers (0-9), underscores (_), or dashes (-). The maximum length is 1,024 characters. | Optional |
| write_disposition | Specifies the action that occurs if the destination table already exists. | Optional |
Context Output
| Path | Type | Description |
|---|---|---|
| BigQuery.Query | String | The query performed. |
| BigQuery.Row | Unknown | The table rows the given query returned. |
Command Example
!bigquery-query query="SELECT * FROMbigquery-public-data.usa_names.usa_1910_2013WHERE state='TX' LIMIT 100"
Context Example
{
"BigQuery": {
"Query": "SELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013` WHERE state='TX' LIMIT 100",
"Row": [
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Frances",
"Number": 197
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Alice",
"Number": 149
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Beatrice",
"Number": 123
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Ella",
"Number": 102
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Gertrude",
"Number": 97
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Josephine",
"Number": 86
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Lula",
"Number": 77
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Blanche",
"Number": 50
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Marjorie",
"Number": 40
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Christine",
"Number": 34
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Margarita",
"Number": 31
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Alta",
"Number": 29
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Sara",
"Number": 28
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Naomi",
"Number": 24
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Sofia",
"Number": 23
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Andrea",
"Number": 16
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Delfina",
"Number": 16
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Dominga",
"Number": 16
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Elnora",
"Number": 16
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Adele",
"Number": 15
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Rafaela",
"Number": 12
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Dixie",
"Number": 11
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Luisa",
"Number": 11
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Bess",
"Number": 10
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Ernestine",
"Number": 10
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Lorine",
"Number": 9
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Patsy",
"Number": 9
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Cecelia",
"Number": 8
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Enriqueta",
"Number": 8
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Lucinda",
"Number": 8
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Alyce",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Oneta",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Apolonia",
"Number": 6
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Gloria",
"Number": 6
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Rhoda",
"Number": 6
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Buna",
"Number": 5
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Maye",
"Number": 5
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Pansy",
"Number": 5
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Gladys",
"Number": 240
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Maria",
"Number": 223
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Helen",
"Number": 189
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Velma",
"Number": 133
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Clara",
"Number": 129
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Anna",
"Number": 117
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Evelyn",
"Number": 106
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Rosa",
"Number": 88
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Mae",
"Number": 83
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Bernice",
"Number": 77
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Stella",
"Number": 69
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Vivian",
"Number": 63
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Mable",
"Number": 62
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Maggie",
"Number": 54
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Betty",
"Number": 50
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Guadalupe",
"Number": 50
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Lorene",
"Number": 46
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Susie",
"Number": 44
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Sadie",
"Number": 42
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Erma",
"Number": 38
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Verna",
"Number": 37
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Amelia",
"Number": 33
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Estelle",
"Number": 31
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Nell",
"Number": 31
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Margie",
"Number": 28
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Celia",
"Number": 26
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Lessie",
"Number": 24
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Eloise",
"Number": 22
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Millie",
"Number": 22
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Olga",
"Number": 21
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Letha",
"Number": 20
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Rachel",
"Number": 19
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Luz",
"Number": 16
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Gussie",
"Number": 15
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Sylvia",
"Number": 14
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Aline",
"Number": 13
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Barbara",
"Number": 11
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Iris",
"Number": 11
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Leila",
"Number": 10
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Mozelle",
"Number": 10
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Simona",
"Number": 9
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Catalina",
"Number": 8
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Hester",
"Number": 8
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Willia",
"Number": 8
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Allene",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Avis",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "George",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Isabella",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Polly",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Syble",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Teodora",
"Number": 7
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Lennie",
"Number": 5
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Ricarda",
"Number": 5
},
{
"Gender": "F",
"State": "TX",
"Year": 1910,
"Name": "Valerie",
"Number": 5
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Robert",
"Number": 276
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Willie",
"Number": 199
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Fred",
"Number": 78
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Raymond",
"Number": 78
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Richard",
"Number": 75
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Sam",
"Number": 56
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Ernest",
"Number": 55
},
{
"Gender": "M",
"State": "TX",
"Year": 1910,
"Name": "Leroy",
"Number": 29
}
]
}
}
Human Readable Output
BigQuery Query Results
| State | Gender | Year | Name | Number |
|---|---|---|---|---|
| TX | F | 1910 | Frances | 197 |
| TX | F | 1910 | Alice | 149 |
| TX | F | 1910 | Beatrice | 123 |
| TX | F | 1910 | Ella | 102 |
| TX | F | 1910 | Gertrude | 97 |
| TX | F | 1910 | Josephine | 86 |
| TX | F | 1910 | Lula | 77 |
| TX | F | 1910 | Blanche | 50 |
| TX | F | 1910 | Marjorie | 40 |
| TX | F | 1910 | Christine | 34 |
| TX | F | 1910 | Margarita | 31 |
| TX | F | 1910 | Alta | 29 |
| TX | F | 1910 | Sara | 28 |
| TX | F | 1910 | Naomi | 24 |
| TX | F | 1910 | Sofia | 23 |
| TX | F | 1910 | Andrea | 16 |
| TX | F | 1910 | Delfina | 16 |
| TX | F | 1910 | Dominga | 16 |
| TX | F | 1910 | Elnora | 16 |
| TX | F | 1910 | Adele | 15 |
| TX | F | 1910 | Rafaela | 12 |
| TX | F | 1910 | Dixie | 11 |
| TX | F | 1910 | Luisa | 11 |
| TX | F | 1910 | Bess | 10 |
| TX | F | 1910 | Ernestine | 10 |
| TX | F | 1910 | Lorine | 9 |
| TX | F | 1910 | Patsy | 9 |
| TX | F | 1910 | Cecelia | 8 |
| TX | F | 1910 | Enriqueta | 8 |
| TX | F | 1910 | Lucinda | 8 |
| TX | F | 1910 | Alyce | 7 |
| TX | F | 1910 | Oneta | 7 |
| TX | F | 1910 | Apolonia | 6 |
| TX | F | 1910 | Gloria | 6 |
| TX | F | 1910 | Rhoda | 6 |
| TX | F | 1910 | Buna | 5 |
| TX | F | 1910 | Maye | 5 |
| TX | F | 1910 | Pansy | 5 |
| TX | F | 1910 | Gladys | 240 |
| TX | F | 1910 | Maria | 223 |
| TX | F | 1910 | Helen | 189 |
| TX | F | 1910 | Velma | 133 |
| TX | F | 1910 | Clara | 129 |
| TX | F | 1910 | Anna | 117 |
| TX | F | 1910 | Evelyn | 106 |
| TX | F | 1910 | Rosa | 88 |
| TX | F | 1910 | Mae | 83 |
| TX | F | 1910 | Bernice | 77 |
| TX | F | 1910 | Stella | 69 |
| TX | F | 1910 | Vivian | 63 |
| TX | F | 1910 | Mable | 62 |
| TX | F | 1910 | Maggie | 54 |
| TX | F | 1910 | Betty | 50 |
| TX | F | 1910 | Guadalupe | 50 |
| TX | F | 1910 | Lorene | 46 |
| TX | F | 1910 | Susie | 44 |
| TX | F | 1910 | Sadie | 42 |
| TX | F | 1910 | Erma | 38 |
| TX | F | 1910 | Verna | 37 |
| TX | F | 1910 | Amelia | 33 |
| TX | F | 1910 | Estelle | 31 |
| TX | F | 1910 | Nell | 31 |
| TX | F | 1910 | Margie | 28 |
| TX | F | 1910 | Celia | 26 |
| TX | F | 1910 | Lessie | 24 |
| TX | F | 1910 | Eloise | 22 |
| TX | F | 1910 | Millie | 22 |
| TX | F | 1910 | Olga | 21 |
| TX | F | 1910 | Letha | 20 |
| TX | F | 1910 | Rachel | 19 |
| TX | F | 1910 | Luz | 16 |
| TX | F | 1910 | Gussie | 15 |
| TX | F | 1910 | Sylvia | 14 |
| TX | F | 1910 | Aline | 13 |
| TX | F | 1910 | Barbara | 11 |
| TX | F | 1910 | Iris | 11 |
| TX | F | 1910 | Leila | 10 |
| TX | F | 1910 | Mozelle | 10 |
| TX | F | 1910 | Simona | 9 |
| TX | F | 1910 | Catalina | 8 |
| TX | F | 1910 | Hester | 8 |
| TX | F | 1910 | Willia | 8 |
| TX | F | 1910 | Allene | 7 |
| TX | F | 1910 | Avis | 7 |
| TX | F | 1910 | George | 7 |
| TX | F | 1910 | Isabella | 7 |
| TX | F | 1910 | Polly | 7 |
| TX | F | 1910 | Syble | 7 |
| TX | F | 1910 | Teodora | 7 |
| TX | F | 1910 | Lennie | 5 |
| TX | F | 1910 | Ricarda | 5 |
| TX | F | 1910 | Valerie | 5 |
| TX | M | 1910 | Robert | 276 |
| TX | M | 1910 | Willie | 199 |
| TX | M | 1910 | Fred | 78 |
| TX | M | 1910 | Raymond | 78 |
| TX | M | 1910 | Richard | 75 |
| TX | M | 1910 | Sam | 56 |
| TX | M | 1910 | Ernest | 55 |
| TX | M | 1910 | Leroy | 29 |
Configuration parameters
google_service_creds— Google service account JSON (a credentials JSON generated from Google API Manager or from GCP console)credentials_google_service—region— Regionfirst_fetch— First fetch timestamp (<number> <time unit>. For example, 12 hours, 7 days)incidentType— Incident typeincidentFetchInterval— Incidents Fetch Intervalmax_fetch— Fetch Limit (Max.- 200, Recommended less than 50)isFetch— Fetch incidentsfetch_query— Fetch queryfetch_time_field— Fetch time field
Commands (1)
-
bigquery-queryPerforms a query on BigQuery.
import demistomock as demisto from CommonServerPython import * from CommonServerUserPython import * """ IMPORTS """ import hashlib import json import os from datetime import date import urllib3 from google.cloud import bigquery # Disable insecure warnings urllib3.disable_warnings() """ GLOBALS/PARAMS """ TEST_QUERY = "SELECT 1" """ HELPER FUNCTIONS """ def represents_bool(string_var): return string_var.lower() == "false" or string_var.lower() == "true" def str_to_bool(str_representing_bool): return str_representing_bool.lower() == "true" def bool_arg_set_to_true(arg): return arg and str_to_bool(arg) def start_and_return_bigquery_client(google_service_creds_json_string): cur_directory_path = os.getcwd() creds_file_name = f"{demisto.uniqueFile()}.json" path_to_save_creds_file = os.path.join(cur_directory_path, creds_file_name) with open(path_to_save_creds_file, "w") as creds_file: json.dump(json.loads(google_service_creds_json_string), creds_file) os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = path_to_save_creds_file creds_file.close() bigquery_client = bigquery.Client() return bigquery_client def validate_args_for_query_job_config( allow_large_results, priority, use_query_cache, use_legacy_sql, dry_run, destination_table, write_disposition ): if allow_large_results and not represents_bool(allow_large_results): return_error("Error: allow_large_results must have a boolean value.") if bool_arg_set_to_true(allow_large_results) and not destination_table: return_error("Error: allow_large_results could only be set to True if a destination table is provided as well.") if bool_arg_set_to_true(allow_large_results) and not bool_arg_set_to_true(use_legacy_sql): return_error("Error: allow_large_results could be set to True only if use_legacy_sql is set to True.") if use_query_cache and not represents_bool(use_query_cache): return_error("Error: use_query_cache must have a boolean value.") if bool_arg_set_to_true(use_query_cache) and destination_table: return_error("Error: use_query_cache cannot be set to True if a destination_table is set") if use_legacy_sql and not represents_bool(use_legacy_sql): return_error("Error: use_legacy_sql must have a boolean value.") if dry_run and not represents_bool(dry_run): return_error("Error: dry_run must have a boolean value.") if priority and not (priority == "INTERACTIVE" or priority == "BATCH"): return_error("Error: priority must have a value of INTERACTIVE or BATCH.") if write_disposition and not ( write_disposition == "WRITE_TRUNCATE" or write_disposition == "WRITE_APPEND" or write_disposition == "WRITE_EMPTY" ): return_error("Error: write_disposition must have a value of WRITE_TRUNCATE, WRITE_APPEND or WRITE_EMPTY.") def build_query_job_config( allow_large_results, default_dataset_string, destination_table, dry_run, priority, use_query_cache, use_legacy_sql, kms_key_name, write_disposition, ): validate_args_for_query_job_config( allow_large_results, priority, use_query_cache, use_legacy_sql, dry_run, destination_table, write_disposition ) query_job_config = bigquery.QueryJobConfig() if allow_large_results: query_job_config.allow_large_results = str_to_bool(allow_large_results) if default_dataset_string: query_job_config.default_dataset = default_dataset_string if destination_table: query_job_config.destination = destination_table if kms_key_name: query_job_config.destination_encryption_configuration = bigquery.table.EncryptionConfiguration(kms_key_name) if dry_run: query_job_config.dry_run = str_to_bool(dry_run) if use_legacy_sql: query_job_config.use_legacy_sql = str_to_bool(use_legacy_sql) if use_query_cache: query_job_config.use_query_cache = str_to_bool(use_query_cache) if priority: query_job_config.priority = priority if write_disposition: query_job_config.write_disposition = write_disposition return query_job_config def convert_to_string(field_value, datetime_format: str = "%m/%d/%Y %H:%M:%S", date_only_format: str = "%m/%d/%Y"): if isinstance(field_value, datetime): return field_value.strftime(datetime_format) if isinstance(field_value, date): return field_value.strftime(date_only_format) if isinstance(field_value, bytes): return field_value.decode("utf-8") return field_value """ COMMANDS + REQUESTS FUNCTIONS """ def query( query_string, project_id, location, allow_large_results, default_dataset, destination, kms_key_name, dry_run, priority, use_query_cache, use_legacy_sql, google_service_creds, job_id, write_disposition, ): bigquery_client = start_and_return_bigquery_client(google_service_creds) job_config = build_query_job_config( allow_large_results, default_dataset, destination, dry_run, priority, use_query_cache, use_legacy_sql, kms_key_name, write_disposition, ) query_job = bigquery_client.query( query=query_string, job_config=job_config, location=location, job_id=job_id, project=project_id ) if not (dry_run and str_to_bool(dry_run)): query_results = query_job.result() return query_results else: # if dry run is activated, the results (number of bytes the query will process) are returned in the job itself return query_job def get_query_results(query_to_run=None): args = demisto.args() query_to_run = query_to_run or args["query"] project_id = args.get("project_id", None) location = args.get("location", None) allow_large_results = args.get("allow_large_results", None) default_dataset = args.get("default_dataset", None) destination_table = args.get("destination_table", None) kms_key_name = args.get("kms_key_name", None) dry_run = args.get("dry_run", None) priority = args.get("priority", None) use_query_cache = args.get("use_query_cache", None) use_legacy_sql = args.get("use_legacy_sql", None) google_service_creds = ( demisto.params().get("credentials_google_service", {}).get("password") or demisto.params()["google_service_creds"] ) job_id = args.get("job_id", None) if not google_service_creds: raise DemistoException("Google service account JSON must be provided.") write_disposition = args.get("write_disposition", None) query_results = query( query_to_run, project_id, location, allow_large_results, default_dataset, destination_table, kms_key_name, dry_run, priority, use_query_cache, use_legacy_sql, google_service_creds, job_id, write_disposition, ) return query_results def query_command(query_to_run=None): query_results = get_query_results(query_to_run) args = demisto.args() dry_run = args.get("dry_run", None) context = {} rows_contexts = [] human_readable = "No results found." if dry_run and str_to_bool(dry_run): human_readable = f"### Dry run results: \n This query will process {query_results.total_bytes_processed} bytes" else: context_key_format = args.get("context_key_format", "") datetime_format = args.get("datetime_format", "%m/%d/%Y %H:%M:%S") date_only_format = args.get("date_only_format", "%m/%d/%Y") demisto.debug(f"{context_key_format=}, {datetime_format=}, {date_only_format=}") for row in query_results: if context_key_format == "underscore": row_context = {k: convert_to_string(v, datetime_format, date_only_format) for k, v in row.items()} else: row_context = { underscoreToCamelCase(k): convert_to_string(v, datetime_format, date_only_format) for k, v in row.items() } rows_contexts.append(row_context) if rows_contexts: context["BigQuery(val.Query && val.Query == obj.Query)"] = {"Query": args["query"], "Row": rows_contexts} title = "BigQuery Query Results" human_readable = tableToMarkdown(title, rows_contexts, removeNull=True) return_outputs(readable_output=human_readable, outputs=context, raw_response=rows_contexts) def get_incident_id(row): """ In BigQuery, each row is a separate incident. To enable the deduplication of incidents, we would like to generate a unique ID for each row. We achieve that goal by using several common fields, that are, combined, a unique identifier. """ additional_fields = row.get("additional_fields") generated = row.get("generatedTime") or row.get("generated_time") or row.get("GeneratedTime") event_id = row.get("event_id") or row.get("EventId") or row.get("eventId") instance_id = row.get("instance_id") or row.get("InstanceId") or row.get("instanceId") agent_id = row.get("agent_id") or row.get("AgentId") or row.get("agentId") data = [additional_fields, generated, event_id, instance_id, agent_id] row_data_string = "" for data_field in data: row_data_string += f"{data_field}_" row_id = hashlib.md5(row_data_string.encode("utf-8")).hexdigest() # nosec return row_id def get_last_run_date(): """ Calculate the time from which to start fetching incidents. """ last_date = demisto.getLastRun().get("last_date") demisto.debug(f"[BigQuery Debug] last_date is: {last_date}") if last_date is None: first_fetch_time = demisto.params().get("first_fetch", "1 days") first_fetch, _ = parse_date_range(first_fetch_time, date_format="%Y-%m-%d %H:%M:%S.%f") last_date = first_fetch demisto.debug(f"[BigQuery Debug] FIRST RUN - last_date is: {last_date}") return last_date def build_fetch_query(last_date): """ Build the fetch query, given the user's input query. """ fixed_query = demisto.params()["fetch_query"] if "WHERE" in fixed_query: fixed_query += " AND" else: fixed_query += " WHERE" fetch_time_field = demisto.params().get("fetch_time_field", "CreationTime") fetch_query = f'{fixed_query} `{fetch_time_field}` > "{last_date}"' return fetch_query def row_to_incident(row): """ Transform a Google BigQuery row to an incident's format. """ incident = {} raw = {underscoreToCamelCase(k): convert_to_string(v) for k, v in row.items()} incident["rawJSON"] = json.dumps(raw) incident_name_field = demisto.params().get("incident_name_field") if incident_name_field and incident_name_field in raw: incident["name"] = raw[incident_name_field] return incident def get_incident_time(incident): incident_row = json.loads(incident["rawJSON"]) return get_row_date_string(incident_row) def get_row_date_string(row): """ Given a row, retrieve the date representing the time in which it was created. According to our testing, on some cases the creation time is spelled 'creation_time', and on other cases 'CreationTime'. Moreover, it could be something else entirely, specified by the user. On each case, the format is different as well. """ row_date_field = demisto.params().get("fetch_time_field", "creation_time") row_date = row.get(row_date_field) if row_date is None: demisto.debug(f"[BigQuery Debug] missing creation_time, trying CreationTime: {row}") row_date_str = row.get("CreationTime") if row_date_str is not None: row_date = datetime.strptime(row_date_str, "%Y-%m-%d %H:%M:%S") row_date_str = row_date.strftime("%Y-%m-%d %H:%M:%S.%f") else: row_date_str = row_date.strftime("%Y-%m-%d %H:%M:%S.%f") if row_date_str is None: demisto.debug(f"[BigQuery Debug] missing creation time completely: {row}") return_error(f"[BigQuery Debug] missing creation time completely: {row}") return row_date_str def get_max_incident_time(new_incidents): """ Given the newly fetched incidents, return the time of the most recent one. """ def incident_to_timestamp(incident): incident_time = get_incident_time(incident) return datetime.strptime(incident_time, "%Y-%m-%d %H:%M:%S.%f") incident_with_latest_timestamp = max(new_incidents, key=lambda inc: incident_to_timestamp(inc)) return get_incident_time(incident_with_latest_timestamp) def remove_outdated_incident_ids(found_incidents_ids, latest_incident_time_str): """ To avoid a continuously growing context size, we must delete outdated incident IDs. To do that, we delete any ID that dates before the start time of the current fetch. """ new_found_ids = {} latest_incident_time = datetime.strptime(latest_incident_time_str, "%Y-%m-%d %H:%M:%S.%f") for incident_id, date_str in found_incidents_ids.items(): incident_time = datetime.strptime(date_str, "%Y-%m-%d %H:%M:%S.%f") if incident_time >= latest_incident_time: new_found_ids[incident_id] = date_str return new_found_ids def verify_params(): params = demisto.params() if not params.get("first_fetch"): return_error("Error: First fetch timestamp parameter must be supplied.") if not params.get("fetch_query"): return_error("Error: fetch query must be supplied.") if not params.get("fetch_time_field"): return_error("Error: the time field you want us to sort incidents by must be supplied.") def fetch_incidents(): verify_params() latest_incident_time_str = get_last_run_date() fetch_query = build_fetch_query(latest_incident_time_str) demisto.debug(f"[BigQuery Debug] fetch query with date is: {fetch_query}") fetch_limit = arg_to_number(demisto.params().get("max_fetch") or 50) bigquery_rows = list(get_query_results(fetch_query)) demisto.debug(f"[BigQuery Debug] number of results is: {len(bigquery_rows)}") if len(bigquery_rows) > 0: demisto.debug(f"[BigQuery Debug] first row is: {bigquery_rows[0]}") demisto.debug(f"[BigQuery Debug] last row is: {bigquery_rows[-1]}") new_incidents = [] # type: ignore found_incidents_ids = demisto.getLastRun().get("found_ids", {}) for i in range(len(bigquery_rows) - 1, -1, -1): # We iterate backwards since the incidents' time is in increasing order if len(new_incidents) == fetch_limit: break row = bigquery_rows[i] row_incident_id = get_incident_id(row) row_date = get_row_date_string(row) if row_incident_id in found_incidents_ids: continue found_incidents_ids[row_incident_id] = row_date demisto.debug(f"[BigQuery Debug] cur row: {row}") incident = row_to_incident(row) new_incidents.append(incident) demisto.debug(f"[BigQuery Debug] new_incidents is: {new_incidents}\nbigquery_rows is: {len(bigquery_rows)}") if 0 < len(new_incidents) < fetch_limit: # type: ignore demisto.debug("[BigQuery Debug] Less than limit") latest_incident_time_str = get_max_incident_time(new_incidents) found_incidents_ids = remove_outdated_incident_ids(found_incidents_ids, latest_incident_time_str) next_run = {"last_date": latest_incident_time_str, "found_ids": found_incidents_ids} demisto.debug(f"[BigQuery Debug] next run is: {next_run}") demisto.setLastRun(next_run) demisto.incidents(new_incidents) def test_module(): """ Perform basic get request to get item samples """ try: params = demisto.params() google_service_creds = ( params.get("credentials_google_service", {}).get("password") or demisto.params()["google_service_creds"] ) region = params.get("region") bigquery_client = start_and_return_bigquery_client(google_service_creds) demisto.debug(f"[BigQuery Debug] test-module with {region=}") query_job = bigquery_client.query(TEST_QUERY, location=region) query_results = query_job.result() results_rows_iterator = iter(query_results) next(results_rows_iterator) demisto.results("ok") except Exception as ex: return_error(f"Authentication error. Exception received:{ex}") """ COMMANDS MANAGER / SWITCH PANEL """ LOG(f"Command being called is {demisto.command()}") try: if demisto.command() == "test-module": test_module() elif demisto.command() == "bigquery-query": search_query = demisto.args().get("query") query_command(search_query) elif demisto.command() == "fetch-incidents": fetch_incidents() except Exception as e: LOG(str(e)) LOG.print_log() raise