Splunk + Excel. What happens?
In previous articles we talked a lot about Splunk . This is a product that allows you to collect and analyze data from any IT systems in real time, for example, it can be the results of a web server, mail server , Windows, various applications, and so on. Does this mean that if all employees interested in analytics obtained in Splunk need to understand the work of the program, make changes in their business processes and do all analytics only in Splunk? Of course not!
In this article, we will show how to make it so that in a few clicks the sales department could receive in MS Excel a table with current data on the sale of goods through the online store.
At company X, data from different IT systems is indexed in Splunk, including data from an online store's web server.
The sales department of company X needs to receive information on the volume of sales of goods through the online store for the previous week for further analytics in MS Excel.
')
Thus, by installing and configuring the ODBC driver, in a few clicks you can upload to Excel the results of any queries saved in Splunk. If necessary, conduct their filtering and sorting and get useful data for further analytics in Excel.
We are happy to answer all your questions and comments on this topic. Also, if you are interested in something specifically in this area, or in the field of machine data analysis in general, we are ready to refine the existing solutions for you, for your specific task. To do this, you can write about it in the comments or simply send us a request through the form on our website .
On June 28, 2018, “ Splunk Getting Started ” will be taught in Moscow , where in 6 hours the participants will receive a theoretical base and practical skills for working in Splunk. Learn more about learning and register at this link .
In this article, we will show how to make it so that in a few clicks the sales department could receive in MS Excel a table with current data on the sale of goods through the online store.
Task
At company X, data from different IT systems is indexed in Splunk, including data from an online store's web server.
The sales department of company X needs to receive information on the volume of sales of goods through the online store for the previous week for further analytics in MS Excel.
')
Implementation
Create a request in Splunk
Which will give us the necessary table with the results. And save the table as a report: “Save As” - “Report” .
Download ODBC driver
It can be downloaded from the SplunkBase website at the following link .
Installation
- There are two files in the archive: for MSExcel 32-bit, or 64-bit.
MSExcel version can be viewed on the tab "File" - "Account" - "About Excel" . Select the required version and run SplunkODBC64.msi or SplunkODBC32.msi. - Start the installation and create the data source.
Enter user login. You can leave the password blank and enter it every time, or check the box that we enter the password now and save it. Enter the URL of the Splunk Enterprise server, and specify the port number 8089. - We finish the installation.
- There are two files in the archive: for MSExcel 32-bit, or 64-bit.
Check the installation.
Open the ODBC Data Sources program (Path: C: \ ProgramData \ Microsoft \ Windows \ Start Menu \ Programs \ Administrative Tools )
In the "System DSN" section there should be a Splunk ODBC driver. By clicking on the name of the driver, you can change its parameters: login, password, source address.
Using the driver in Excel
- Open the required .xls document. This may be a blank document or already have preliminary information. In our document there is such a table that we fill in using information from Splunk.
- In the section “Data” - “Get data” - “From other sources” - “From Microsoft Query” .
- Select the “Splunk ODBC *” data source, select the “Use Query Wizard” checkbox.
- In the query creation window we will be presented with a list of available tables from Splunk.
- We select the table saved at the first step and columns from it.
- Next, you can set a data selection rule, for example, count> 1000. But since we have a ready table, we leave the conditions empty and click “Next”.
- Specify the sorting conditions.
- We set a condition that it is necessary to return the data to Excel.
- Choose a way to present data.
- Data is loaded, and we can update it at any time by clicking the "Update All" button.
- Open the required .xls document. This may be a blank document or already have preliminary information. In our document there is such a table that we fill in using information from Splunk.
Add information to the table template
With the help of the formula: = ESD (CDF (A: A; Table_Request_is_Splunk_ODBC_1; 3; FALSE); 0)
We get the final result
Conclusion
Thus, by installing and configuring the ODBC driver, in a few clicks you can upload to Excel the results of any queries saved in Splunk. If necessary, conduct their filtering and sorting and get useful data for further analytics in Excel.
We are happy to answer all your questions and comments on this topic. Also, if you are interested in something specifically in this area, or in the field of machine data analysis in general, we are ready to refine the existing solutions for you, for your specific task. To do this, you can write about it in the comments or simply send us a request through the form on our website .
PS
On June 28, 2018, “ Splunk Getting Started ” will be taught in Moscow , where in 6 hours the participants will receive a theoretical base and practical skills for working in Splunk. Learn more about learning and register at this link .
Source: https://habr.com/ru/post/350276/
All Articles