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!

Unlock New Opportunities with Splunk Education: Explore Our Latest Courses!

At Splunk Education, we’re dedicated to providing top-tier learning experiences that cater to every skill ...

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...