Splunk comes as a web-style interface that captures real-time data from which it can generate graphs, reports, alerts, dashboards and visualisations. It can be used to automate complex queries and reports across multiple data sources, deal with any data type and provide real-time alerting abilities. Thus, it can be a simple and quick way to help you get an insight into your business metrics, error logs and so on.
This article will focus on building a simple metrics dashboard for a user management platform which involves 3 major steps:
- Installing and setting up Splunk
- Setting up a database connection using the Splunk DB Connect
- Turn your DB queries into a simple metrics dashboard
Set Up DB Connection — Install Splunk DB Connect on a Single Server Deployment
Splunk DB Connect comes as a Splunk app to easily help you integrate structured data sources with Splunk’s real-time data collection.
- Splunk Enterprise 6.4.0 or later
- Java Runtime Environment (JRE) version 8 from Oracle. (DB Connect uses a JDBC to connect to the DB.)
- A supported relational database running locally or elsewhere on your network. (for this tutorial, I will be using a MySQL instance. )
- Install database drivers and follow the instructions for your own DB and OS type.
Install Spunk DB Connect:
- Log in to Splunk Web and go to Apps > Find More Apps.
- Use the search box to find db connect.
- Click the Install button next to Splunk DB Connect.
- Restart Splunk.
The app has been successfully installed if it can be found in the left hand side in the Application Tab. Next, let’s set up DB Connect. Open the DB Connect App and click on the Setup button.
Go to DB Connect/Settings/General and set it to your JRE path and your Task Server Port (default is 9998) or set up your custom one.
Go to DB Connect/Settings/Drivers/ to check if your drivers have been installed properly.
After having all the prerequisites installed along with Splunk DB Connect, we’ll need to go through the following steps to set up a connection to MySQL:
As a first step, a database identity will be set up to define a database user though which Splunk will connect to your database(s) by using your username and password that you use to access your databases(s). Next, we’ll set up a JDBC Connection along with permissions to read, read-write or no-access.
- Create a database identity.
Go to DB Connect/Databases/Identities/ and click on New Identity. Complete the required fields and check the permission tab to grant access to users. Save it.
- Set up a database connection.
Go to DB Connect/Databases/Connections/ and create a New Connection. Complete the fields to your JDBC connection and check the permission tab. Save it.
For a MySQL instance, the JDBC url has the format: jdbc:mysql://hostname/ databaseName
Creating a Dashboard
- Create a new app. Navigate to Home page and in the left tab called Apps click on the Settings icon.
- Navigate to the app you have just created.
- Create new Dashboard from the Dashboard tab.
Splunk provides several visualisation types: line chart, column chart, bar chart, pie chart, scatter chart, single value, radial/filer/marker guage, map, heatmap, sparkline. We’ll go through a couple of them.
- Single Value Panel
And now let’s start by creating a panel that shows the number of active users. From Add Panel, select Single Value and use the following query where connection is the name you have set earlier for your db connection.
Set a title to the panel and save the dashboard.
2. Pie Chart Panel
Pie charts are single data series and can be used to show how different values combine over an entire data set.
Suppose we have a table transactions with the following fields (created_on, modality, amount, status). Let’s add a pie chart for the status of transactions (which can be either success, fail, pending)
We have used a transforming command in the search to generate the single series, such as the counting events in each status field category.
Let’s add a new pie chart for the type of transactions (oct, fundus)
3. Column Chart Panel
Column charts are one or more data series that get the x-axis values from the first column in the statistics table.
Let’s add a column chart with the following query to show the amount spent over each modality type. The over operator indicates that modality is the first table column and the modality values are used for the x-axis. The y-axis in the column chart is sum(amount).
4. Line Chart Panel
Line charts are used to track value trends over time and can represent one or more data series.
Let’s add a line chart to show the amount spent by type (oct, fundus) over time (per month). Add the following query:
The above search compares the amount spent over each month, the over operator indicating that month is the first table column. In the line chart month values are going to be used for the x-axis and first(amount) values representing the y-axis. The search would produce the following search table:
To transform the single to multiple line data series, we’ll add a split-by field (in our case this is going to be the modality). Each modality is a data series with its own first(amount) values for each month.
Our dashboard should now look similar to:
5. Cluster Map
Cluster maps are used to plot aggregated values on the map. We’ll use the geostats command to generate events that include latitude and longitude coordinates for markers.
Now let’s add a Cluster Map to show the popularity of our platform over the world. In order to achieve this, we are going to use another DB table, called audit_trail which is used to track the request traffic of the platform (the field we are going to use is the user and the ip). In order to map the user to a specific location on a map, we’ll have to turn the ip into longitude and latitude coordinates. Fortunately, Splunk provides the iplocation command that adds the City, Country, lat, lon and Region fields to the result.
Use the following query:
And so we obtain the following plot:
6. Panels with Dropdowns
Let’s say we want to modify the line chart from above that shows the amount spent over time to show the amount spent for a specific site or organization. In order to achieve this, we are going to use a drop-down to allow the user to pick the desired site/org.
- Let’s add a line chart panel to show the amount spent per day (over the current month)
- And another line chart panel to show the amount spent per month.
which are not going to show anything for the moment because $site_day$, $org_day$, $org_month$, $org_day$ are tokens linking the dropdowns. So let’s add 2 dropdowns for each panel of the 2 line charts we have just added. Go to Add Input, select Dropdown and place them inside the panel.
Each dropdown will contain a list with the organizations and sites and thus, let’s edit them by going navigating to the source page.
As an example, we’ll use the site dropdown for the amount per month line chart.
Here token is the name of the dropdown that we will use in the query in line chart. fieldForLabel is the column field from the query which is going to show in the dropdown. In our case, we’d like to show the name of the site. fieldForValue is the column field which is going to be used in the line chart query. In the query tag, we just use a simple select query to return a list of available sites.
Thus, we obtain the following line charts with dropdowns:
The following is the complete XML code used for building the dashboard.