CSV report not showing data correctly

[Shashank_87]

alt textHi, I have a daily scheduled report which goes to sftp server in a csv format. I am getting complaints that the data is not coming properly. I investigated and suspect that it may be because of the multi valued fields in the table but I am not sure. In Splunk it shows something like I have attached and in the CSV which is delivered on the server it is seen something like this very weird with column name deviceDescription

app,"serviceName","2020-02-12 23:34:01","2020-02-12 23:34:01",34567,ANA,C,,51228586,"HD BOX (CISCO),,,,,,,,,,,,
TIVO 500GB BOX (CISCO),,,,,,,,,,,,,,,,,,,,,,
TIVO 1TB BOX (ARRIS),,,,,,,,,,,,,,,,,,,,,,
TIVO 1TB BOX (ARRIS)",456,Agent,,,,5678997,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Total columns in the table is 23 but it seems in CSV they are more than 23 commas coming.

Any help is appreciated.

[to4kawa]

index=temp_prd serviceName source=tomcat.txt 
| rex field=_raw "deviceDescription\>(?<deviceDescription>.*?)\<\/" max_match=20 
| rex field=_raw "requestTimestamp\>(?<requestTimestamp>.*?)\<\/" 
| dedup requestID
| fields - _*
| table originatorURI, activityname requestTimestamp,responseTimestamp,AccountNumber,ref,Channel,Code,accountDiagnosticsSummary,diagnosticIdentifier,deviceDescription 
| eval deviceDescription = mvjoin(deviceDescription,"|")

hi, @Shashank_87
how about this?

[Shashank_87]

@to4kawa It worked like charm. Thanks. 🙂

[Shashank_87]

@to4kawa Hi, it is still not working. So the issue is when sending this file to server using SFTP, it's not in the proper format. There are extra commas coming in multiple rows don't know for what reason.

Even if i export that from Splunk and open the file in Notepad++, it shows incorrectly. Attached are the 2 files - one from server and one directly from Splunk.

The data in both of these attached file should be coming as one row but they are going to next line and the server file have extra commas for no reason.

Please help.

[to4kawa]

add at last:

....
| fields - _*

[vikramyadav]

Is it possible for you to share a query for a better understanding?

[Shashank_87]

@vikramyadav There is nothing much in the search. There are few fields which I am extracting are multi valued like deviceDescription. I am trying to combine it's values by using mv command but not sure if it's working. Not sure what is causing that issue on csv

index=temp_prd serviceName source=tomcat.txt
| rex field=_raw "deviceDescription>(?[^<\/]+)" max_match=20
| rex field=_raw "requestTimestamp>(?[^<\/]+)"
| eval requestTimestam=strptime('requestTimestamp',"%Y-%m-%dT%H:%M:%S")
| eval requestTimestamp=strftime('requestTimestam',"%Y-%m-%d %H:%M:%S")
| dedup requestID
| table originatorURI, activityname requestTimestamp,responseTimestamp,AccountNumber,ref,Channel,Code,accountDiagnosticsSummary,diagnosticIdentifier,deviceDescription
| makemv delim="|" deviceDescription