topic Re: CSV report not showing data correctly in Getting Data In

CSV report not showing data correctly

Shashank_87 — Thu, 13 Feb 2020 17:05:19 GMT

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.

Re: CSV report not showing data correctly

vikramyadav — Thu, 13 Feb 2020 17:32:16 GMT

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

Re: CSV report not showing data correctly

Shashank_87 — Wed, 30 Sep 2020 04:06:34 GMT

@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

Re: CSV report not showing data correctly

to4kawa — Sat, 15 Feb 2020 11:34:25 GMT

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?

Re: CSV report not showing data correctly

Shashank_87 — Tue, 18 Feb 2020 15:01:30 GMT

@to4kawa It worked like charm. Thanks. 🙂

Re: CSV report not showing data correctly

Shashank_87 — Tue, 25 Feb 2020 17:23:48 GMT

@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.

Re: CSV report not showing data correctly

to4kawa — Tue, 25 Feb 2020 19:54:47 GMT

add at last:

....
| fields - _*