Query Google BigQuery with Splunk DB Connect

This post explains on how to setup BigQuery with Splunk DB Connect, to be able to run queries against BigQuery from within Splunk. This is particularly useful when you have SIEM logs stored in BigQuery and you would like to perform adhoc queries from Splunk without bringing the logs in and indexing them first.

Environment :
GCP VM with Debian 12
Splunk Enterprise 9.2.0.1
Splunk DB Connect 3.15.0
Simba BigQuery JDBC Driver 1.5.2.1005

1) Download the Splunk enterprise version for Linux in the VM. You have to register account for downloading trial version.
https://www.splunk.com/en_us/download/splunk-enterprise.htm

The .deb downloaded in my case is “splunk-9.2.0.1-d8ae995bf219-linux-2.6-amd64.deb”

wget -O splunk-9.2.0.1-d8ae995bf219-linux-2.6-amd64.deb "https://download.splunk.com/products/splunk/releases/9.2.0.1/linux/splunk-9.2.0.1-d8ae995bf219-linux-2.6-amd64.deb"

2) Install Splunk using downloaded .deb file

# Install Splunk
sudo ./splunk-9.2.0.1-d8ae995bf219-linux-2.6-amd64.deb

# Enable startup at boot time
sudo /opt/splunk/bin/splunk enable boot-start

# Start splunk service
sudo service splunk start

3) Login to Splunk Web and confirm it is working.

If you haven’t changed any settings, then you can access the Splunk Web at <Your VM external IP>:8000.

Splunk Web Login

4) Install Splunk DB Connect app (3.1.5 version at time of writing) — https://splunkbase.splunk.com/app/2686/#/overview. There are few ways to do so, we will install the app from within Splunk Web.

1. Log in to Splunk Web and go to Apps > Find More Apps.

2. Use the search box to find db connect

3. Click the green Install button next to Splunk DB Connect.

4. Click Restart Splunk.

Install DB Connect

5) Configure DB Connect

If you now open Apps -> Splunk DB Connect, you may see errors like — “Cannot communicate with task server, please check your settings” or “Failed to start Task Server”.

We would need to configure settings for Task Server and Query Server to work properly.

First step is to check if you have JRE installed on the VM. If not install using:

# Install JRE
sudo apt install default-jre

# Find Java Home
readlink -f /usr/bin/java | sed "s:bin/java::"

/usr/lib/jvm/java-17-openjdk-amd64/

Next open Splunk Web -> Apps -> Splunk DB Connect -> Configuration -> Settings page and set the values like below.

Configure DB Connect settings

Then click on green Save button, which will restart Task Server with these settings. If the Task Server still fails to start, then restart Splunk from VM:

sudo service splunk restart

6) Next we will install Simba Google BigQuery JDBC driver.

Open Splunk Web -> Apps -> Splunk DB Connect -> Configuration -> Settings -> Drivers page. At the time of writing it will show 26 supported drivers, and BigQuery is not one of them.

We will setup BigQuery driver using custom driver install approach.

Download the JDBC driver for BigQuery from below link to the VM.

https://storage.googleapis.com/simba-bq-release/jdbc/SimbaJDBCDriverforGoogleBigQuery42_1.5.2.1005.zip

Unzip the zip file and you will get a set of .jar files.

Copy the GoogleBigQueryJDBC42.jar and EULA.txt file to the /opt/splunk/etc/apps/splunk_app_db_connect/drivers directory. Create a directory /opt/splunk/etc/apps/splunk_app_db_connect/drivers/GoogleBigQueryJDBC42-libs and copy rest of the dependent jars under it.

$ sudo ls -lrt /opt/splunk/etc/apps/splunk_app_db_connect/drivers
total 1552
-rw-r--r-- 1 root   root   1579618 Feb 28 11:38 GoogleBigQueryJDBC42.jar
drwxr-xr-x 2 root   root      4096 Feb 28 15:32 GoogleBigQueryJDBC42-libs
-rw-r--r-- 1 root   root       230 Feb 28 16:45 EULA.txt

7) Next we will add the custom database (BigQuery) to db_connection_types.conf file. When you add a custom database connection that Splunk DB Connect does not support by default, you must create a stanza to define the database connection in a copy of db_connection_types.conf under /opt/splunk/etc/apps/splunk_app_db_connect/local.

$ sudo cat /opt/splunk/etc/apps/splunk_app_db_connect/local/db_connection_types.conf
[GoogleBigQuery]
displayName = GoogleBigQuery
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = com.simba.googlebigquery.jdbc42.Driver
jdbcUrlFormat = jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<GCP Project ID>;OAuthType=0;OAuthServiceAcctEmail=splunk@<GCP Project ID>.iam.gserviceaccount.com;OAuthPvtKeyPath=<path to aservice account JSON file>/<json file name>;

Check in Splunk Web if the new database appears under the Drivers page, if not restart Splunk.

8) Connect to Splunk Web and create identities and BigQuery connection with connection URL specific to environment.

For identity, we will be using service account based authentication which is specified in the db_connection_types.conf file above. Therefore we will create a dummy identity which won’t be used but to satisfy Splunk Web UI which requires it as mandatory.

For connection, set Connection type as “GoogleBigQuery”, Identity as “BQ” from above. Host, Port & Database you can set to any dummy value as they are not relevant for BigQuery. JDBC URL will be automatically populated from db_connection_types.conf file, but you can edit it to update any of the values.

9) Validate the connection with sample queries in SQL explorer.

10) Open it in Splunk Search

Hopefully this will help you get going with the integration.