Home » Business Topics » Business Analytics

Business Analytics from Application Logs and Database using Splunk

  • Roopesh Uniyal 
System Log on Screen
White System Log on Black Screen Closeup

Introduction

Splunk is a well-known log management tool. Splunk mines log from different machines in real-time and can be used to monitor, search, and analyze gathered data. It is a Big Data log management tool that can give insight from the unstructured data stored in the Splunk indexes. Splunk analytics helps turn unstructured log data into knowledge that enterprises can use to derive business value. In addition, it’s a great tool to monitor and report for making knowledgeable decisions and help in alerting so that corrective actions can be taken in case the system is underperforming.

Splunk is not also a great tool for log analytics but also being used heavily as a SEIM tool also. As a result, Splunk is placed in the leader quadrant of the Gartner Magic Quadrant. This article is to show how easy it is to use Splunk to gain insight into IT systems. How can we create reports, how can we create a dashboard, and how can we set alerts can be set? To create Splunk reports, we need to know the intermediate level of Regex. This article aims not to teach but to show how to use Splunk to get analytics out of application log files.

Tool & Methods

Data Analytics can be Descriptive, Diagnostic, Predictive, and Prescriptive. Using Splunk, a business can create reports and alerts that can help in getting these types of analytical insights. Splunk can not only gather insights from application logs but can also connect to the database and can either pull the data from it or push data to it

image-71

In this article we will be looking into 3 aspects of Business Analytics using Splunk:

  1. Connect to application logs, generate reports, and create a dashboard
  2. Connect to MS SQL Server database, mine the data, and present it in a report in the dashboard
  3. Set up an alert so that the IT team or business can be notified of a specific event

Connect to application logs, generate reports, and create a dashboard

For this article, in this section, we will first import a log file that has application records. Once it’s in Splunk, I will be using Regex to create reports and a dashboard. When creating a dashboard, we can create a report as an event, table, chart, single value. Event is the single transaction/ event stored in the log file. I will show single value, table, comparison, event, and charts reports. Data that this log file consists of is of a fictitious company called “Awesome Insurance Company”.

Connect to an Application and mine logfiles

First step is to setup application so that log file can be mined. Configure Splunk forwarder on app server and configure inputs.conf to make sure application log can be mined and pushed to Splunk Index. For this test though, I used the Data Inputs to import a log file.

image-30

Once clicked on “Data inputs”, Data Inputs page will present mutiple option to select any of the local inputs, click on “Files & Directories”:

Business Analytics from Application Logs and Database using Splunk

When in “Files & directories”, click on “New Local File & Directory”. Select the logfile that needs to be mined and mention if it needs to be continuously monitored or index only once.

Business Analytics from Application Logs and Database using Splunk

After clicking on Next, we can use event breaks if required and then provide the name of Source Type name. Clicking next will lead to Input Settings like Index, App context, etc. Once setup is complete, we get details of the index.

Index Details:
Host:AppServer01
Source Path:C:\Awesome\awesome_log.txt
Source Type:Awesome_SourceType
Index:awesome

Create Splunk reports

We can use regex to create Splunk reports. Reports can be generated from the data stored in a Splunk Index. Below are the Report Name, Report Type, and Script that we can use to create these Splunk reports. Splunk reports can be more complex, but this article aims to show how to create different reports and present them in the Splunk dashboard.

Report
Name
Report
Type
Splunk Script
Daily
Approved Policies
Table index=awesome_index "Policy Transaction completed successfully" AND "status=approved"
| rex "transactionDate=(?<Date>[^,]+)."
| rex "policyType=(?<PolicyType>[^,]+)."
| rex "policyAmt=(?<Amount>[^,]+)."
| eval sum(Amount)=round(sum(Amount),2)
| table Date PolicyType sum(Amount)
Policy Type Comparisons – Pie index=awesome_index "Claims Transaction completed successfully"
| stats count by policyType
Claim Type Comparisons – Pie index=awesome_index "Policy Transaction completed successfully"
| stats count by policyType
Approved Claims Table index=awesome_index "Claims Transaction completed successfully" AND "claimStatus=Approved"
| rex "transactionDate=(?<Date>[^,]+)."
| rex "policyType=(?<PolicyType>[^,]+)."
| rex "policyAmt=(?<Amount>[^,]+)."
| eval sum(Amount)=round(sum(Amount),2)
| table Date PolicyType sum(Amount)
Policy Transaction Failure Table index=awesome_index Policy Account creation failed
| rex "policyType=(?<PolicyType>[^,]+)."
| rex "accountNumber=(?<Account>[^,]+)."
| rex "reason=(?<Reason>[^,]+)."
| rex "createdDate=(?<Date>[^,]+)."
| rex "transactionDate=(?<Date>[^,]+)."
| kv
| table Date PolicyType Account Reason
Host wise
Error
Trends index=awesome_index ERROR |timechart count by host
Application Database
Data & Reconciliation
Table index="awesome_index"
| rex "ClaimNumber="\""(?<ClaimNumber>[^"\"",]+)."
| rex "claimNumber=(?<ClaimNumber>[^,]+)."
| rex "PolicyNumber="\""(?<PolicyNumber>[^"\"",]+)."
| rex "policyNumber=(?<PolicyNumber>[^,]+)." 
| rex "LOB="\""(?<PolicyType>[^"\"",]+)."
| rex "policyType=(?<PolicyType>[^,]+)."
| rex "Amount="\""(?<Amount>[^"\"",]+)."
| rex "Status="\""(?<Status>[^"\"",]+)."
| rex "claimStatus=(?<Status>[^,]+)."
| rex "TransactionDate="\""(?<Date>[^"\"",]+)."
| eval Date=strftime(strptime(Date, "%Y-%m-%d"),"%m-%d-%Y")
| rex "transactionDate=(?<Date>[^,]+)."
| rex "Reason=(?<Reason>[^,]+)."
| rex "Reason=(?<Reason>[^}]+)."
| rex "Reason="\""(?<Reason>[^"\"",]+)."
| where isnotnull(Reason)
| kv
| table  Date PolicyNumber PolicyType ClaimNumber Amount Status Reason source
| sort – Date
Transaction Performance Table index=awesome_index
| rex "in (?<respTime>\d+\.\d+) seconds.*app=(?<Application>[^,}]+)"
| stats count as "Total Transactions" min(respTime) as "Min Resp Time (Sec)" max(respTime) as "Max Resp Time (Sec)" avg(respTime) as "Avg Resp Time (Sec)" by Application
Transaction takings more than 1 Sec Table index=awesome_index "Policy Transaction completed" OR "Claims Transaction completed"
| rex "in (?<ResponseTime>\d+\.\d+) seconds."
| where ResponseTime>.01
| rex "app=(?<Application>[^,]+)."
| kv
| table  Application ResponseTime
Errors Event –
Table
index=awesome_index "java.net.SocketException: Connection reset" Save as an Event - AI_Java_ConnectionReset index=awesome_index "ERROR user login failed" Save as an Event - AI_LoginFailed index=awesome_index "DB Connection timeout" Save as an Event - AI_DB_ConnectionTimeout index=awesome_index "OutOfMemory" Save as an Event - AI_OutOfMemory   eventtype="AI_Java_ConnectionReset" OR eventtype="AI_LoginFailed" OR eventtype="AI_DB_ConnectionTimeout" OR eventtype="AI_OutOfMemory"
| stats count as Total by eventtype
| rename eventtype AS "Error Type"
| sort-Total

Create Splunk dashboard to present reports

Splunk provides a way to present these reports in a dashboard. Presenting reports in the dashboard helps stakeholders to know the state of business/ application in real-time. Business or Technical teams can take business decisions or corrective actions based on these real-time operational Splunk reports. Based on our requirements and scripts mentioned above, here is one quick dashboard that we can build.

Log on to Splunk and click on Dashboards. Once on this page, click on the “Create New Dashboard” button:

Business Analytics from Application Logs and Database using Splunk

Provide details for this new Dashboard and click on “Create”:

Business Analytics from Application Logs and Database using Splunk

A black dashboard “Awesome Insurance Company” has been created. O top ribbon, there are different options to create and deploy reports:

Business Analytics from Application Logs and Database using Splunk

To create a report, click on the “Add Chart” button and select what type of chart you want to build. In our case, I click on “Table”:

image-39

A new Table will be created on the dashboard and on the right side, we can add the Splunk report script in there, and as we add script and click on “Run & Save”, data will be populated:

Business Analytics from Application Logs and Database using Splunk

As Below is a dashboard “Awesome Insurance Company” that has these reports aligned with the time picker so that all these reports get the result for the same period.

Business Analytics from Application Logs and Database using Splunk

Connect to MS SQL Server database, mine the data, and present it in a report in dashboard

If you look at the report above “Application Database Data & Reconciliation”, it is showing data not only from the application logs but from database too. Mining data from database can be very helpful in getting a wholistic idea of the system but mining data from database needs additional steps. This is achieved by using “Splunk DB Connect” to pull database data and merge it to the main index. This article is not to show how exactly Splunk DB Connect is being configured as it warrants an article in itself but will provide high level steps in creating one.

Application Database

Consider there is an application called “Awesome Insurance Core System” and it has a database called “Awesome”. There are four tables in it containing Policyholder, Policy, Claim, and Billing information. Idea here is not to show how to set up a SQL Server database or create tables correctly but to give reader an idea on how a table data that is residing in a SQL Server can be extracted.

Create SQL Server tables and insert some records to mimic a policy & claim transaction:

image-44

Mine the data using Splunk

Once the data has been populated, now we need to set up Splunk so that it can mine the database.

First, we need to install “Splunk DB Connect”. Splunk DB connect will help connecting to the database and either fetching or pulling data. First, download the latest version or the version that is compatible with your Splunk version. Although installation is quire easy, this article is not going to show how to install this connector.

Business Analytics from Application Logs and Database using Splunk

Once Splunk DB Connect is installed as per the installation instructions, it will be presented in Splunk app:

image-48

Click on the “Splunk DB Connect” and “Splunk DB Connect” app will open. Following needs to happen: Go to “Configurations” and create “Identities”. Identities is nothing but a user who is going to connect to the database.

image-49

Next install appropriate SQL Server driver so that Splunk can communicate to the MS SQL Database. I installed JTDS driver for this test.

Business Analytics from Application Logs and Database using Splunk

Once Identities have been created, then click on “Connections” and provide connection settings. User will select which user will be used for this connection, using which driver, the host where the DB resides, Port used by DB, and the database name. We can also provide specific permission if required. A new connection will be created once you click on “Save”.

Business Analytics from Application Logs and Database using Splunk

With above three steps completed, we are ready to make connection with the DB and fetch records from the underlying tables. Click on “Data Lab” and you will see four sub-sections – Inputs, Outputs, Lookups, and SQL Explorer. Since for this test we want to fetch data from MS SQL Server database to Splunk, we will create Inputs. Now click on “New Input”:

Business Analytics from Application Logs and Database using Splunk

One can set up input, use which connection to use, which catalog/ Database to connect, use which schema, and can select the tables populated underneath. Once we click on the table, SQL Editor will generate a select * query. We can update it as per our requirements. But since we want this table to be updated as we receive data, we must choose a column that our input will track. We can do this by setting values on the right pane. We can choose “Rising” for “Input Type”. The rising column will be “TransactionID” as it is a unique column and always increment by 1. We can set a value that we want Input to track from under “checkpoint value”. We can also set Query Timeout so that system can cancel fetch query if it takes x number of seconds.

Business Analytics from Application Logs and Database using Splunk
SELECT * FROM AWESOME.DBO.CLAIM WHERE TRANSACTIONID >? ORDER BY TRANSACTIONID ASC

Click on “Next” and you will be presented with the “Set Properties” section. Some of the information is optional but we need to provide info like “Name”, “Execution Frequency”, “Source Type”, and “Index”. Here we direct Splunk to store all the data mined by that particular Input to this specified Index.

Business Analytics from Application Logs and Database using Splunk

Once a user connects to “Finish”, the user will get an Input completion message.

image-77

Like the input creation for the first table, we can set up Inputs for all other 3 tables also and Data will now start flowing into the assigned Index (In our case “awesome_index”).

Business Analytics from Application Logs and Database using Splunk

Since Application log data is already there in the index and now, we also received data from Database, we can now run the Splunk script for “Application Database Data & Reconciliation”. We can see the source type as 5 different source types, Inputs for the database tables as well as the log file.

Business Analytics from Application Logs and Database using Splunk

Set up an alert so that IT team or business can be notified on a specific event

Like a report, we can set up an alert for a critical event if it occurs. We can schedule alerts for the business use case where the business would like to be alerted if there is any Claim approved for more than x amount or for a technical use case where the technical team would like to be alerted in case the system gets an error like Out of Memory, Service Unavailable, Connection Timeout, Failed login (if lots in short span), etc. The following section will show how we can set up an alert in Splunk.

Run a script for which you want to search

For this example, we are trying to look at any Claim that got approved for more than $50000. Once we get the result set then we can “Save As” Alert.

Business Analytics from Application Logs and Database using Splunk

Set up Alert

Once you click on same as alert, we will be presented with the form that we need to fill out so that Alert can be scheduled. We can give a name and then schedule it as per the cron. We can also set trigger conditions.

Business Analytics from Application Logs and Database using Splunk

Set up Trigger Actions

After we put mandatory parameters like above, we can configure “Trigger Actions”. For this test, we will “Add Actions” like “Send email”. We can provide To emailID, message, and can add “inline table” if required. Settings like these will provide an HTML formatted email body along with the inline table having the result set from the above query.

Business Analytics from Application Logs and Database using Splunk

Conclusion

Splunk provides a bigdata enabled log analytics solution. Splunk provides operational intelligence and helps enterprises and stakeholders to see the state of their systems in real-time. Although this article has presented some basic reporting, Splunk reports can also be more complex and derive more business value. Splunk can also connect to a database to create a report based on a structured database. Splunk can push log data to outside databases or copy data from outside databases to the Splunk Index. Once the data is in one place, creating reports is very easy and provides a holistic view of the application transactions. Splunk is also a great tool to alert business or IT teams so that an issue can be resolved before an issue becomes a problem. Apart from the Log Analytics solution that Splunk provides to IT, there are many other use cases like Security and DevOps where Splunk can shine. Splunk also provides a lot of connectors to other applications for ease of use. Gathering business and technology analytics from application logs and application databases using Splunk can be of great help to companies.