topic Extracting key and value from substring in Getting Data In

Extracting key and value from substring

brdr — Tue, 14 Nov 2023 17:46:40 GMT

I'm trying to corral a string into new field and value and having trouble. I've used eval / split / mvexpand....

The string looks like this. Its actually a field in an event:

field_id=/key1/value1/key2/value2/key3/value3/key4/value4

The end goal is to have new fields. Like:

field_key1=value1

filed_key2=value2

So i can now search, for example, if field_key1='the value of something"

Re: Extracting key and value from substring

ITWhisperer — Tue, 14 Nov 2023 18:19:25 GMT

| rex field=field_id max_match=0 "/(?<key>[^/]+)/(?<value>[^/]+)" | eval row=mvrange(0,mvcount(key)) | streamstats count as _row | mvexpand row | eval name="field_".mvindex(key,row) | eval {name}=mvindex(value,row) | fields - key value name row | stats values(*) as * by _row

Re: Extracting key and value from substring

bowesmana — Wed, 15 Nov 2023 03:07:42 GMT

If you have a known max limit of keys, then you can do it without the mvexpand, which if you have a large dataset, can hit memory issues.

| makeresults | eval field_id="/key1/value1/key2/value2/key3/value3/key4/value4" | rex field=field_id max_match=0 "/(?<k>[^/]*)/(?<v>[^/]*)" | foreach 0 1 2 3 4 5 6 7 8 9 10[ eval _k=mvindex(k, <<FIELD>>), {_k}=mvindex(v, <<FIELD>>) ]

Just put in the foreach statement the maximum number of possible key/value pairs you have.

Re: Extracting key and value from substring

brdr — Wed, 15 Nov 2023 23:35:34 GMT

Thank you both ITWhisperer and bowesmana!!! 🙂 Will try these out

Re: Extracting key and value from substring

inventsekar — Wed, 15 Nov 2023 23:56:05 GMT

Hi @brdr ...the above 2 SPL are working fine as you can see on the screenshots below.
the easiest one is the split command:

Re: Extracting key and value from substring

inventsekar — Thu, 16 Nov 2023 21:24:01 GMT

| makeresults | eval field_id="/key1/value1/key2/value2/key3/value3/key4/value4" | rex field=field_id max_match=0 "/(?<key>[^/]*)/(?<value>[^/]*)" | foreach 0 1 2 3 4 5 6 7 8 9 10[ eval _key=mvindex(key, <<FIELD>>), {_key}=mvindex(value, <<FIELD>>) ]

Hi @bowesmana .. instead of k and v, i used key and value, it works fine as well.

could you pls explain how the last eval works (why do you use "eval _k")

Re: Extracting key and value from substring

bowesmana — Thu, 16 Nov 2023 23:47:15 GMT

@inventsekar You can use whatever two variables you like, a/b, k/v, key/value

In the foreach using a var name with _ prefix means that it will not be generally visible as a field, so in case you forget to remove the field _key, it will not be seen as part of the data. I often use that just to make sure temporary fields are hidden and don't become part of the working dataset.

The syntax {_key}=mvindex(value,<<FIELD>>) uses Splunk's encoding to create a new field (left hand side) that has the name of the VALUE of _key and it takes the n'th multivalue element from the value MV based on <<FIELD>> which is effectively a loop of the values of the foreach statement 0 1 2 3 4...

It's the same as doing this

| makeresults | fields - _time | eval key="NAME", value="ANTONY" | eval {key}=value

where you will end up with a new field called NAME with the value of ANTONY

There should really be cleanup to remove the temporary field names k,v,_k, so a | fields statement would be a good idea at the end.

Re: Extracting key and value from substring

inventsekar — Fri, 17 Nov 2023 00:13:06 GMT

Great, thanks a lot @bowesmana ,..much appreciated !