Splunk Search

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

nravichandran
Communicator

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

0 Karma

woodcock
Esteemed Legend

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
Builder

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
Esteemed Legend

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.

0 Karma

lakromani
Builder

🙂 . . . .

0 Karma

koshyk
Super Champion

great approach

0 Karma

nravichandran
Communicator

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

0 Karma

nravichandran
Communicator

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.

0 Karma

nravichandran
Communicator

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

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

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
Communicator

This works for my requirement. Thank you!

0 Karma

askhat_pernebek
Path Finder

why you didn't accept answer if it works?

0 Karma

woodcock
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...