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

[nravichandran]

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

[woodcock]

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.

[lakromani]

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>.+)$"

[woodcock]

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.

[lakromani]

🙂 . . . .

[koshyk]

great approach

[nravichandran]

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

[nravichandran]

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.

[nravichandran]

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

[cpetterborg]

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

[nravichandran]

This works for my requirement. Thank you!

[askhat_pernebek]

why you didn't accept answer if it works?

[woodcock]

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