Getting Data In

CSV report not showing data correctly

Explorer

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.alt text

0 Karma

SplunkTrust
SplunkTrust
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?

0 Karma

Explorer

@to4kawa It worked like charm. Thanks. 🙂

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

add at last:

....
| fields - _*
0 Karma

Contributor

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

0 Karma

Explorer

@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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!