Excelling with Excel in Splunk

Hey all,

if you didn’t already know that you can heavily customize Splunk through our open developer framework you should check it out. You can even develop and introduce new search commands. This particular blog illustrates this with an example where business people wanted to have excel files with reports mailed to them from Splunk.
You might already know that Splunk enables you to connect Excel directly to Splunk with the ODBC Connector for Windows, as well as enabling you to export with outputcsv a csv file. Dominique from Helvetia Insurance has developed a Splunk TA that is freely available on Splunkbase which allows you to import, export and e-mail data in XLS format.

Dominique notes that, “If tabular reports go to a business user it’s more convenient to provide them with an Excel formatted file rather than a csv“. This app directly set’s the correct cell formatting for numbers, dates and strings to display them nicely in Microsoft Excel. The app also performs the conversion of the normalized _time field from epoch time to human readable date syntax. The new search commands that come with the app are called “outputxls” and “sendfile“, and can directly mail reports according to a schedule. The app also brings a xls2csv command which can convert an Excel sheet into a csv that can then be loaded directly into Splunk. The collect command can store it longterm into an index.

outputxls

| outputxls <filename.xls> “<sender>” “<receiver>” “<subject>” “<bodyText>” “<smtpHost>”

Description: This command will take the search results and write them into an excelsheet where the name is specified by the parameter. This command wraps also the sendfile command if you add the parameters optionally

sendfile

| sendfile “<sender>” “<receiver>” “<subject>” “<bodyText>” “<attachment>” “<smtpHost>”

Description: This command will take the file “<attachement>” which can only reside in /var/run/splunk and sends it via email using the parameters.

csv2xls

| csv2xls <commaseparated.csv> <filename.xls>

Description: This command will take a filename to a csv file residing in /var/run/splunk and write it into a Excel file

xls2csv

| xls2csv <filename.xls> <numOfWorksheets> <commaseparated.csv>

Description: This command will take a excel file that is located in /var/run/splunk, selects the proper Worksheet (Worksheet 0 is the first one) and writes it into a splunk-readable csv file.

The creation of the Excel Worksheet is based on the Python XLWT Module. The extraction capabilities use the xlrd Python Module.

If you have any questions related to the app, you can ask directly via Splunk Answers.

Thanks Dominique for developing such a great app and sharing it with the community.

Happy Splunking.