Blank Query Method (Recommended)
This method employs a Power Query M script to connect to Splunk using service account credentials, enabling the extraction of data from a Splunk search to create tables in Power BI on the web (dataflow) for use in reports.
Important Note About Securing Credentials
The Power Query connection utilized in this method includes the credentials of your Splunk service account. These credentials are encoded with base-64; however, this can be easily decoded. Due to the way Power BI permissions are structured, only users with edit access to the dataflow can view these credentials. When a report using the dataflow is shared, recipients cannot see the credentials – only the dataflow ID. Only users with admin or member permissions to the workspace will have access to the dataflow. Please be aware of this before creating a dataflow to Splunk.
It is advisable not to use this Power Query code directly in the desktop version of Power BI, as anyone with access to the PBIX file will be able to view the credentials.
Prerequisites:
- A Power BI Pro license
- A Splunk service account with permission to the necessary resources
Resources:
Steps:
Encode your Splunk credentials
Before setting up the dataflow, you will need to encode your Splunk credentials in base-64 format. More information is available here.
Here is an example Python script that will encode your username:password string into base-64.
| import base64 def encode_basic_auth(username, password): # Combine username and password with a colon credentials = f”{username}:{password}” # Encode to Base64 encoded_credentials = base64.b64encode(credentials.encode(‘utf-8’)).decode(‘utf-8’) # Format for HTTP Authorization header return f”Basic {encoded_credentials}” # Example usage username = “YOUR SPLUNK SA USERNAME” password = “YOUR SPLUNK SA PASSWORD” auth_header = encode_basic_auth(username, password) print(“Authorization Header:”, auth_header) |
Create the dataflow
- Navigate to your desired Power BI workspace and create a new dataflow (gen 1)
- Click “Add new tables”
- Choose the blank query connector
- Enter the following code, making sure to insert your encoded Splunk credentials and search in the highlighted sections. Then, click Next.
| let url = “https://illinois.splunkcloud.com:8089/services/search/jobs/export“, headers = [#”Authorization”=”Basic YOUR ENCODED SPLUNK CREDENTIALS”, #”Content-Type”=”application/x-www-form-urlencoded”, #”Accept”=”*/*” ], postData = [ preview=”false”, output_mode=”csv”, search=”YOUR SPLUNK SEARCH” ], response = Csv.Document(Web.Contents(url, [ Content=Text.ToBinary(Uri.BuildQueryString(postData)), Headers=headers ])), source = Table.FromRows(response), Custom = response in Custom |
- If needed, add transformations using the Power Query window and then click Save and close. When prompted, refresh the dataflow.
ODBC Method (Using Dataflows)
Prerequisites:
- A VM with the Power BI on-premises gateway software installed
- A Splunk service account with permission to the necessary resources
- The Splunk data you would like to use must be in the form of a saved search
Resources:
- Splunk ODBC Driver Documentation
- Splunk ODBC Driver Download (requires login with a Splunkbase account)
- To choose the correct version (likely Windows), click the “Version History” tab
- Power BI On-Prem Gateway Documentation
- Microsoft Introduction to Dataflows Documentation
Steps:
Set up the ODBC connection on the gateway
- On the gateway, install the Splunk ODBC driver.
- During the setup, you will need to configure the connection options.
Enter the following:
- Data Source Name: Splunk ODBC
- Login ID: <your local Splunk account username>
- Password: <your local Splunk account password>
- Server URL: https://illinois.splunkcloud.com:8089
- Verify Server SSL Certificate: leave unchecked
- Continue through the setup process. To confirm that it was set up, you can open the ODBC Data Source Administrator 64-bit (Windows). You should see the connection under the “System DSN” tab.
Create a dataflow in Power BI
- Navigate to your desired Power BI workspace and create a new dataflow (gen 1)
- Click “Add new tables”
- Search for the ODBC connection and select it
- Use the following settings:
- ODBC Connection String:
Driver={Splunk ODBC Driver}; Host=https://illinois.splunkcloud.com; Port=8089; Database=Default; UID=<Splunk account username>; PWD=<Splunk account password>; URL=https://illinois.splunkcloud.com:8089; - Connection:
Create new connection - Connection Name:
Whatever you’d like - Data gateway
The name of your on-prem gateway - Authentication kind
Basic – enter your Splunk account credentials - Privacy Level:
None
- ODBC Connection String:
- Next, you can choose the saved search(es) you would like to add to the dataflow, perform any transformations in the Power Query editor, and save.