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
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.
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>.+)$"
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.
🙂 . . . .
great approach
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
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.
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
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
This works for my requirement. Thank you!
why you didn't accept answer if it works?
If this really works, then you should accept this answer.