topic Re: need to consider special format in csv as same field value in Getting Data In

need to consider special format in csv as same field value

anwar114 — Wed, 30 Sep 2020 04:38:30 GMT

Is there a way to let splunk know when ever the format like "32770": ALL_REQ:2 | CT_FLAG(32768) keep it as a single field value in csv .
Data:
"123","EMPTY","1766 Bytes","32770": ALL_REQ:2 | CT_FLAG(32768),"131680": 20(32) | CT_FLAG |MODIFIED:20000(131072),"44d5","200 bytes"

using normal csv extraction splunk extracts fields to :
field1 :123
field2: EMPTY
field3: 1766 Bytes
field4: "32770": ALL_REQ:2 | CT_FLAG(32768),"131680": 20(32) | CT_FLAG |MODIFIED:20000(131072), 44d5
field5: "200 bytes"

splunk combines field4 & field5 into a single field. thereafter all other field values gets pre jumped .

Result required after field extraction:

field1 :123
field2: EMPTY
field3: 1766 Bytes
field4: "32770": ALL_REQ:2 | CT_FLAG(32768)
field5: "131680": 20(32) | CT_FLAG |MODIFIED:20000(131072)
field6: 44d5
field7: 200 bytes

Re: need to consider special format in csv as same field value

anmolpatel — Wed, 30 Sep 2020 04:34:26 GMT

@anwar114 the issue is with your csv data.
What you want to do is escape the quotations or remove them. So the data should be

as such:
field1, field2, field3, field4, field5, field6, field7
"123","EMPTY","1766 Bytes",32770: ALL_REQ:2 | CT_FLAG(32768),131680: 20(32) | CT_FLAG |MODIFIED:20000(131072),"44d5","200 bytes"
OR:
field1, field2, field3, field4, field5, field6, field7
"123","EMPTY","1766 Bytes",\"32770\": ALL_REQ:2 | CT_FLAG(32768),\"131680\": 20(32) | CT_FLAG |MODIFIED:20000(131072),"44d5","200 bytes"
OR:
field1, field2, field3, field4, field5, field6, field7
123,EMPTY,1766 Bytes,32770: ALL_REQ:2 | CT_FLAG(32768),131680: 20(32) | CT_FLAG |MODIFIED:20000(131072),44d5,200 bytes

and the csv sourcetype will work as expected

Re: need to consider special format in csv as same field value

anwar114 — Mon, 16 Mar 2020 04:16:58 GMT

is there a way to do it (escape the quotations or remove them ) from splunk before or while indexing. as the csv is an output from another system commandline there is no much we can do from that side. thx

Re: need to consider special format in csv as same field value

manjunathmeti — Mon, 16 Mar 2020 07:25:09 GMT

hi @anwar114,

Add these configurations in forwarder. This will extract the fields before indexing.

props.conf

[sourcetype_name]
TRANSFORM-fields = extract_fields

transforms.conf

[extract_fields]
SOURCE_KEY = field4
REGEX = (?<field4>[^\,]+),(?<field5>[^\,]+),\s*(?<field6>\w+)

If this is not possible you can also extract these during search time. Add the same configurations on search heads. This will extract fields whenever sourcetype is searched.

Re: need to consider special format in csv as same field value

anmolpatel — Wed, 30 Sep 2020 04:34:37 GMT

@anwar114 yes that can be done at index timee. You would want to update the props.conf and create a new csv sourcetype. Copy the current stanza key pair as data is being extracted correctly. The key pair you want to modify is this
FIELD_QUOTE = "
Update to
FIELD_QUOTE = '

if you're using inputs.conf and any other .conf file or searches, you would want to update the sourcetype reference to this new sourcetype.