Exporting data from Splunk to Tableau over ODBC, is there a way to clean up the data (remove quotation marks) before the export?

[bearman]

Hi guys!

We’re trying to export data from Splunk over to Tableau over ODBC.
We’ve successfully managed to export/import data from two platforms (CallManager/Linux and TMS/Windows), but on 3 other platforms (NetBSD) we have hit some rubble.

What seems to be causing us some issues is that in the _raw column that we see in Tableau or for that matter Excel, some of the data are enclosed with quotation marks such as below:

2015-08-07T08:16:25+00:00 vcs-aer-202 UTCTime="2015-08-07 06:16:25,678" Module="network.tcp" Level="ERROR":  Src-ip="173.38.197.xx" Src-port="33872" Dst-ip="10.160.86.xxx" Dst-port="56960" Detail="TCP Connection Failed"

On the successful platforms (the CallManagers and the TMS), we do not see these quotation marks and the import into Tableau functions 100%.

On the NetBSD platforms the coders have decided to use double quotation marks around some events, and that's seems to be the only difference as far as we can see (yeah, I know it's not much to go on but it's still the only difference open to the eye...).

Is there any way to clean up the data before I export to Tableau in my Splunk search that gets sent over to Tableau, as in getting rid of these Quotation marks? I have seen various techniques in the export itself (be it Excel or other csv reader) but that option isn't open to us in Tableau. On the unsuccessful Tableau imports from the NetBSD platform we get the following:

"Unable to create extract".
"StarExtractTupleSource has wrong number of bindings for number of inputs column"  

Does anyone have some good tips on this one?

Thanks!

[gcato]

Hi Bearman,

To simply remove the quotation marks in the _raw data using Splunk search, then I suggest using the rex command. For example,

search ... |rex mode=sed "s/\"//g"  | table _raw ... 

Not sure how this works with Tableau over ODBC, however.

[bearman]

Well, reinstalling the client helped with the Splunk->Tableau extract and this time it even worked with the double quotes (for about a pair of hours...). Now the client is back to it's normal "I don't wanna do anything today" mode.

Thanks anyways for the double quotes regex above!

[bearman]

Hi gcato!

Thanks!
That actually works part of the way.

I still get the double qoutes for the "INFO" level as below:

2015-08-16T11:49:59+00:00 vcs-aer-2xx UTCTime=2015-08-16 09:49:59,784 Module=network.http.trafficserver Level=INFO: Detail=Receive Request Txn-id=4199474 Src-ip=127.0.0.1 Src-port=31184 Last-via-addr=173.38.2xx.xx Msg=POST http://vcs_control.edge-emea.cisco.com:8443/ZWRnZS1lbWVhLmNpc2NvLmNvbHRwL3VjeC1lbTEtZ3NzLmNpc2NvLmNv... HTTP/1.1

date_zone = 0
host = vcs-aer-2xx
process = Level="INFO"

source = /apps/data/ucv/raw/logs/user.log
sourcetype = syslog

The process = Level="INFO" seems to screw up the Tableau column import.

Do you know anyway to get rid of the dbl. quotes here?

Thanks so far!!!