My data has a tables{}.values{} containing a list of lists. Within each list there is data. Sample data below. When I try to extract this search to csv via job id, it's not containing tables{}.values{} data within a single cell and instead treating it as it's own field as it's comma delimited. How can I keep all the data within a singular field when exporting to CSV?
Sample data:
test@email.com
The following was found, on website: google.com, by test user, with id:testuser, extracted from test.txt, on date testdate,another test field.
OK. Where are you exporting this from? Splunk should enclose the values in double quotes (and use double double quotes for the double quotes within a field value) when exporting search results.
So where are you exporting from/to?
I'm using the splunk search API to convert search job to csv and downloading it. Unfortunately in this case splunk does not enclose values in double quotes.
/api/search/jobs/$alert_data$/results?isDownload=true&timeFormat=%FT%T.%Q%3Az&maxLines=0&count=0&filename=alert_data&outputMode=csv
Something got converted here 😉 &->&
But to the point - if the csv export works badly, it's a material for support case.
the & is HTML encoding to escape the & character. Exporting to CSV works via the API but when field values are multi-value and within each multi-value there is a comma as part of the data, exporting to CSV doesn't work.
Yes. I know that & is a HTML entity. Hence the smiley.
Anyway. If exporting from WebUI works OK and the REST-initiated export does not there are two things you can do:
1) As I mentioned - raise a case with support. It seems like a bug. A proper CSV should be properly quoted/escaped/whatever.
2) You can use developer tools to check which REST endpoint the WebUI uses
Oh, apologies hahaha 😅
Yeah, I tried using dev tools to see which REST endpoint WebUI uses. Not for exporting to CSV but for exporting to pdf (uses pdfgen endpoint). But can't find Splunk documentation on it anywhere and other forums don't seem to have a working solution.
Anyways thanks for recommendation. Will raise a case with support for now.
Have you tried using double quotes around the field values with commas in e.g.
field 1,"field2, with commas", field3
How would you do this? I've seen solutions that replace the commas with null but I'm not sure how to encapsulate field values with commas in a double quote, especially with field values that have multiple commas inside.