Using Splunk's ODBC Driver - Look at Splunk Data via Excel
Long-term administrators of Splunk have heard the following two requests from users many, many times I’m sure. Luckily, there’s now an easy answer to both.
1) “Can I search Splunk using a SQL query?”
2) “Is there a way I can just have the data in Excel?”
Splunk has finally relented to the pressure and quietly released an ODBC driver for Windows. The driver and documentation are available here:
It’s a great extension to the Splunk platform and certainly a welcome one. The primary use case I can foresee is placing this driver on executive’s computers so that they can access up-to-date information and reports from a tool they’re already familiar with: Excel.
The single limitation, which actually works in your favour, is that you have to pre-prepare a saved search in Splunk for the data your users might want. When using the ODBC driver, the results will come from the available fields at the end of your saved search, whether that be raw fields straight from the event or processed information such as transaction durations. This allows Splunk administrators to limit the data available to their users whilst also ensuring that their searches won’t use copious amounts of resources.
Setting it up is straightforward – simply follow the steps in the documentation and you’ll be underway in less than 5 minutes. Once complete, create a saved search for the information you’re after. You don’t need to format the results, since all the data in Excel will come from the fields returned. Opening the query wizard within Excel and selecting the Splunk ODBC source allows me to see the saved search, where I can pick and choose fields to import.
Note: In the examples below I’m using false positive data from our Enguard service.
Clicking next through the rest of the prompts brings the data I want into excel where I can do some more digging, such as finding out which checks generate the most false positives, or see which false positives need to be rechecked based on the date they were added.
Whilst this can be accomplished in Splunk, you can see how this can easily be extended to graphs and charts presented in a familiar Excel context: “Will not make executives run away, guaranteed!”
The benefit of using a data source is that you can simply go to the data tab and hit ‘Refresh All’ and all the data will update from Splunk. This means you only need to create the report in Excel once, then you, or an executive, are able to refresh it forever – no more stale report data!
For those of you who are SQL inclined, you can manually edit the SQL search string once the initial data input has been created. Go to the Data tab and click on properties, then select your input and view the connection properties. Voila, SQL, albeit not super powerful. A reference for which commands are available will probably come out as the driver matures. If you need to know, submit a feature request to Splunk and they might publish it sooner.
Afterword: An interesting issue I ran into was saved searches with underscores in the name don’t work correctly (see the error below). If you encounter this, clone the saved search in Splunk and save it with a name without underscores, for example change "transaction_statistics" to "Transaction Statistics".
Thanks for reading, please let me know if you have any comments or questions! You can always reach us from the Contact Us page.