topic Re: Extracting field name containing as table in Splunk Search

How to extract field name containing as table?

msg4sunil — Fri, 12 Aug 2022 15:07:36 GMT

Hello All,

I have data like below. How do I extract the field names like prefix:field1, prefix:field2, prefix:field3 in tablular fashion. Extract all those fields containing the word, "prefix:" in it.

"prefix:field1":"value1","prefix:field2":value2,"prefix:field3":value3,

Expect result

prefix:field1

prefix:field2

prefix:field3

Thank you

Re: Extracting field name containing as table

yuanliu — Fri, 12 Aug 2022 01:58:32 GMT

Use extract, (aka kv) e.g.,

| kv pairdelim=",", kvdelim=":"

You get

_raw	prefix_field1	prefix_field2	prefix_field3
"prefix:field1":"value1","prefix:field2":value2,"prefix:field3":"value3"	value1	value2	value3

Is this what you need?

Re: Extracting field name containing as table

msg4sunil — Fri, 12 Aug 2022 02:04:01 GMT

Its not always in this pattern("prefix:field1":"value1","prefix:field2":value2,"prefix:field3":value3,) and rather be more complex structure as well(could be "prefix:field1":"ABC","TxnMsg":{"prefix:field2":XYZ,"prefix:field3":123},). Is there any other way?

thank you

Re: Extracting field name containing as table

yuanliu — Fri, 12 Aug 2022 03:04:57 GMT

The example you give looks like a fragment of a JSON object. Is your raw data or a part of that data conformant JSON? If so, use spath instead. For example, if _raw is

{
    "prefix:field1": "ABC",
    "TxnMsg": {
        "prefix:field2": "XYZ",
        "prefix:field3": 123
    }
}

spath gives

TxnMsg.prefix:field2	TxnMsg.prefix:field3	_raw	prefix:field1
XYZ	123	{"prefix:field1":"ABC","TxnMsg":{"prefix:field2":"XYZ","prefix:field3":123}}	ABC

Re: Extracting field name containing as table

msg4sunil — Fri, 12 Aug 2022 03:12:46 GMT

Yes, part of the data is JSON and not the entire _raw. Isn't there a way to look for String matching "prefix:.*" criteria and extract the complete matched string? thank you

Re: Extracting field name containing as table

yuanliu — Fri, 12 Aug 2022 03:21:41 GMT

You should focus on extracting that conformant part into its own field. Suppose you have a field data that contains conformant JSON, you can do

| spath input=data

Trying to manipulate structured data as text is labored and unreliable.

Re: Extracting field name containing as table

msg4sunil — Fri, 12 Aug 2022 03:38:46 GMT

I was trying the below, but its not helping much as in its not extracting all the data. 😞

*| rex field=_raw "prefix:(?<from>\w+)" | dedup from | table from

Re: Extracting field name containing as table

yuanliu — Fri, 12 Aug 2022 03:54:28 GMT

It's not clear what you're trying to achieve with that rex. If you need help to put the conformant JSON part of your log into a field that spath can operate on, here are some suggestions.

Go back to your developers and ask them to do something helpful, namely, place the JSON part in a key-value pair that Splunk can easily handle, e.g.,

2021-11-25 00:48:02 something unimportant conformant='{"prefix:field1":"ABC","TxnMsg":{"prefix:field2":"XYZ","prefix:field3":123}}' something else unimportant

Then, you can use spath input=conformant. This is the best option.

Post some sample full log (anonymized) for others to help analyze and determine how to get the conformant part.

Re: Extracting field name containing as table

msg4sunil — Fri, 12 Aug 2022 04:35:00 GMT

Thank you. In the above case, what would be the exact command to extract prefix:field1, prefix:field2, prefix:field3 in tabular fashion . What needs to be added to the below?

spath input=conformant

thank you

Re: Extracting field name containing as table

yuanliu — Fri, 12 Aug 2022 05:10:34 GMT

In that path, you need to go back to the developers who produced the logs, ask them to place the JSON part in a key-pair structure as exemplified in my comment. (Or maybe they already did and your data already contained that JSON field?)