topic Re: How Extract Fields and Values on a multivalue field in search time in Splunk Search

How Extract Fields and Values on a multivalue field in search time

nravichandran — Tue, 29 Sep 2020 13:47:04 GMT

I want to extract the fields and values from the following event:

1997-11-14 12:11:56 schedule ERROR a.b.c.d.e SomeProcess::ERROR::Alert::FAILURE::{"NAME=FAILURE":[["Name=somename","p_name=abcd","type=F","status=B"],["Name=somename1","p_name=abcde","type=T","status=C"],
["Name=somename3","p_name=abde","type=T","status=C"]]}

The search results should look like:
Name p_name, Type status
somename abcd F B

somename1 abcde T C

somename3 abde T C

I tried with mvexpan and makemv but could get the desired result.
search | rex ":[[*(?result[^}]+)" | mvexpand result | makemv delim="," result | table result

Re: How Extract Fields and Values on a multivalue field in search time

cpetterborg — Fri, 21 Apr 2017 22:25:34 GMT

Does this do what you want?:

YOUR_SEARCH_HERE | rex max_match=100 "Name=(?P<Name>[^\"]+)\",\"p_name=(?P<p_name>[^\"]+)\",\"type=(?P<type>\w+)\",\"status=(?P<status>\w+)\"" | eval temp=mvzip(mvzip(mvzip(Name,p_name,"#"),type,"#"),status,"#") | mvexpand temp | rex field=temp "(?<Name>.+)#(?<p_name>.+)#(?<type>.+)#(?<status>.+)" | table Name, p_name, type, status

Re: How Extract Fields and Values on a multivalue field in search time

woodcock — Fri, 21 Apr 2017 23:01:49 GMT

Like this:

Your Base Search Here
| rex max_match=0 "\"(?<kvp>[^=\"]+=[^=\"]+)\""
| table _time host kvp*
| streamstats count AS serial
| mvexpand kvp
| rex field=kvp "^(?<kvp_key>[^=\"]+)=(?<kvp_value>[^=\"]+)$"
| eval {kvp_key} = kvp_value
| rename COMMENT AS "If you need to reconstitute original events, then add in the next line"
| rename COMMENT AS "| fields - kvp* | stats values(_time) AS _time values(*) AS * BY serial"
| table Name p_name Type status

Note that this is a generic approach that works for all KVPs.

Re: How Extract Fields and Values on a multivalue field in search time

koshyk — Sat, 22 Apr 2017 09:07:35 GMT

great approach

Re: How Extract Fields and Values on a multivalue field in search time

nravichandran — Tue, 29 Sep 2020 13:47:17 GMT

When i run the following query i get the results but the last row get truncated. The values for status and type for the last row is not shown.

| rex max_match=0 "\"(?[^=\"]+=[^=\"]+)\""
| table _time host kvp* | streamstats count AS serial
| mvexpand kvp | rex field=kvp "^(?[^=\"]+)=(?[^=\"]+)$"
| eval {kvp_key} = kvp_value
| fields - kvp* | stats values(_time) AS _time values(*) AS * BY serial

Result:

Name p_name status type
somename abcd B F
somename1 abcde C T
somename3 abde

Re: How Extract Fields and Values on a multivalue field in search time

nravichandran — Sun, 23 Apr 2017 00:43:25 GMT

When I changed the stats values to stats list it works!
The values shows the unique and list shows all.

Thank you for your help.

Re: How Extract Fields and Values on a multivalue field in search time

nravichandran — Sun, 23 Apr 2017 00:57:55 GMT

This works for my requirement. Thank you!

Re: How Extract Fields and Values on a multivalue field in search time

lakromani — Sun, 23 Apr 2017 10:20:16 GMT

Why "\"(?<kvp>[^=\"]+=[^=\"]+)\"" and not just "\"(?<kvp>[^\"]+)\""
Also here: "^(?<kvp_key>[^=\"]+)=(?<kvp_value>[^=\"]+)$" you do not need the " since its already removed, so this should do: "^(?<kvp_key>[^=]+)=(?<kvp_value>.+)$"

Re: How Extract Fields and Values on a multivalue field in search time

woodcock — Sun, 23 Apr 2017 13:30:12 GMT

Because these answers evolve as I write them and I do not always do a final cleanup once it works but you are correct on both points.

Re: How Extract Fields and Values on a multivalue field in search time

lakromani — Sun, 23 Apr 2017 14:29:44 GMT

🙂 . . . .

Re: How Extract Fields and Values on a multivalue field in search time

nravichandran — Tue, 29 Sep 2020 14:00:20 GMT

Sorry I have to unaccept this answer. There is a problem with this approach as the stats list(*) hits the limit. Moreover it should be a table format - whereas the stats list displaying it a one group.
_time Name p_name status type
xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx

Re: How Extract Fields and Values on a multivalue field in search time

woodcock — Fri, 12 May 2017 04:42:03 GMT

If this really works, then you should accept this answer.

Re: How Extract Fields and Values on a multivalue field in search time

askhat_pernebek — Fri, 12 Apr 2019 06:35:14 GMT

why you didn't accept answer if it works?