topic Re: Split values from each fields from output table in Splunk Search

Split values from each fields from output table

KundanNagare23 — Tue, 12 Dec 2023 13:54:58 GMT

We got output in table but all values are in one column for each fields of output table. We want to split values in row. Below is the output table for reference. Please help to split it.

Re: Split values from each fields from output table

richgalloway — Tue, 12 Dec 2023 14:19:24 GMT

It's probably better to split the data before the table is created. Please share the current SPL.

Re: Split values from each fields from output table

KundanNagare23 — Tue, 12 Dec 2023 15:06:09 GMT

@richgalloway Below is SPL used,

index="*****" host="sclp*" source="*****" "BOLT_ARIBA_ERROR_DETAILS:" "1-57d28402-9058-11ee-83b7-021a6f9d1f1c" "5bda7ec9" | rex "(?ms)BOLT_ARIBA_ERROR_DETAILS: (?<details>\[.*\])" | spath input=details output=ERROR_MESSAGE path={}.ERROR_MESSAGE | spath input=details output=PO_NUMBER path={}.PO_NUMBER | spath input=details output=MW_ERROR_CODE path={}.MW_ERROR_CODE | spath input=details output=INVOICE_ID path={}.INVOICE_ID | spath input=details output=MSG_GUID path={}.MSG_GUID | spath input=details output=INVOICE_NUMBER path={}.INVOICE_NUMBER | spath input=details output=UUID path={}.UUID | spath input=details output=DB_TIMESTAMP path={}.DB_TIMESTAMP | table ERROR_MESSAGE PO_NUMBER MW_ERROR_CODE INVOICE_ID MSG_GUID INVOICE_NUMBER UUID DB_TIMESTAMP

Re: Split values from each fields from output table

richgalloway — Tue, 12 Dec 2023 19:52:57 GMT

That's not what I was expecting. I expected a stats values command that was globbing field values together.

Can you share a sample event? How many events are in the sample output?

Re: Split values from each fields from output table

dtburrows3 — Tue, 12 Dec 2023 23:06:25 GMT

If I am understanding your question correctly I usually parse out an array of json objects as a mutlivalued field first and then use an mvexpand against that MV field. After this you can SPATH each json_object individually so its contents will be on its own row.

This will also prevent situation where there are some json objects whose key's may have null values and them not properly aligning in the final output.

Here is an example:

| makeresults | eval event_id=sha256(tostring(random())), json_object="[{\"field1\": \"value_a\", \"field2\": \"value_b\", \"field3\": \"value_c\"},{\"field1\": \"value_x\", \"field2\": \"value_y\", \"field3\": \"value_z\"},{\"field1\": \"value_q\", \"field2\": \"value_r\", \"field3\": \"value_s\"},{\"field1\": \"value_a\", \"field2\": \"value_r\", \"field3\": \"value_c\", \"field4\": \"value_w\"},{\"field2\": \"value_a\", \"field3\": \"value_b\", \"field4\": \"value_s\"}]" | eval mv_json_object=spath(json_object, "{}") | fields - json_object | mvexpand mv_json_object | spath input=mv_json_object | fields - mv_json_object