Splunk Search

Extraction of value from nested multivalue field

Poojitha
Path Finder

Hi All,

I have a multivalue field that contains nested key value pair with key named as "Key" and Value named as "Value".

Example snippet :

 tags: [ [-]
     { [-]
       Key: Contact
       Value: abc@gmail.com
     }
     { [-]
       Key: Name
       Value: abc
     }


I want to extract only the Contact value from here i.e abc@gmail.com. I am trying with multivalue functions and spath. Still stuck here. Please help me.

Regards,
PNV

Labels (1)
Tags (1)
0 Karma
1 Solution

dtburrows3
Builder

I dont know the complete path to the nested tags array but you can do something like this to target the value contained within the Contact Key in the MV json fields.

Something like this.

 

 

<base_search>
    | eval
        tags_json=spath(_raw, "tags{}"),
        contact=case(
            mvcount(tags_json)==1, if(spath(tags_json, "Key")=="Contact", spath(tags_json, "Value"), null()),
            mvcount(tags_json)>1, mvmap(tags_json, if(spath(tags_json, "Key")=="Contact", spath(tags_json, "Value"), null()))
            )
    | fields + _time, _raw, tags_json, contact

 

 


Below is a screenshot of an example on my local instance.

dtburrows3_0-1704171143729.png

First we extract all json objects from the tags array as a multivalued field named "tags_json". From there you can use the mvmap() function to loop through the multivalue field and check each entry to see if the Key field value of the json object is equal to "Contact". If it is, then we know this is the json object we want to target the extraction of the "Value" key from. So we do an Spath specificly on that object and store the returned value as a field named "contact".

Option 2:
Another route to take (depending on the structure of your event and if it make sense to do i this way).
We can loop through each json object in the tags array and stuff the key/values into a temporary json object that we can then do a full spath against. This is a more exhaustive approach as apposed to the targeted one in the previous example.

SPL to do this would look something like this.

<base_search>
    | eval
        ``` extract array of json objects a multivalued field ```
        tags_json=spath(_raw, "tags{}"),
        ``` initialize the temporary json object that will hold all the key/value pairs contained within the tags array ```
        final_tag_json=json_object()
    ``` use the mode=multivalue foreach loop to loop through each entry in the multivalued field ```
    | foreach mode=multivalue tags_json
        [
            | eval
                ``` json_set() function will set up each Key/Value as a new key/value pair in the temporary json "final_tag_json" ```
                final_tag_json=json_set(final_tag_json, spath('<<ITEM>>', "Key"), spath('<<ITEM>>', "Value"))
            ]
    | fields - tags_json
    ``` full spath against the final_tag_json field ```
    | spath input=final_tag_json
    | fields - final_tag_json
    | fields + _time, _raw, Contact, Name


You can see in this screenshot that not only is the "Contact" field extracted but "Name" value is extracted as well, this method would loop through each json array and extract a new Key/Value pair for each entry.

dtburrows3_0-1704174836107.png

Below is a screenshot showing what the temporary final_tag_json object looks like that we did the full spath against for context.

dtburrows3_1-1704174963016.png

 

View solution in original post

Poojitha
Path Finder

@inventsekar @dtburrows3   Thank you both for your reply. I was trying to use the spath command but was failing in extraction.

@dtburrows3 : The second method using for loop worked well. I am running this query against large set of query. Does using for loop and json functions has any limitation in that case ? Like results getting truncated and so ?

0 Karma

inventsekar
SplunkTrust
SplunkTrust

>>> I am running this query against large set of query. Does using for loop and json functions has any limitation in that case ? Like results getting truncated and so ?

1) may we know how large is the data set, so that we can suggest you better. 

2) foreach does not have any limitation, whereas the spath got 5000 characters limitation. but the docs has given this overriding method:

By default, the spath command extracts all the fields from the first 5,000 characters in the input field. If your events are longer than 5,000 characters and you want to extract all of the fields, you can override the extraction character limit for all searches that use the spath command. To change this character limit for all spath searches, change the extraction_cutoff setting in the limits.conf file to a larger value.

https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Spath

https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Foreach

 

PS - Upvotes / like / karma points are appreciated, thanks. 

0 Karma

inventsekar
SplunkTrust
SplunkTrust

Hi @Poojitha 

The Splunk command "spath" enables you to extract information from the structured data formats XML and JSON. The command Ref doc link is:

https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Spath

 

Pls let us know if you are able to use the spath command (as seen in the previous reply)

or you could use direct "rex" command extract field values and do the stats.

 

but, the spath is the simplest option i think. pls let us know if you are ok with spath or not, thanks. 

0 Karma

dtburrows3
Builder

I dont know the complete path to the nested tags array but you can do something like this to target the value contained within the Contact Key in the MV json fields.

Something like this.

 

 

<base_search>
    | eval
        tags_json=spath(_raw, "tags{}"),
        contact=case(
            mvcount(tags_json)==1, if(spath(tags_json, "Key")=="Contact", spath(tags_json, "Value"), null()),
            mvcount(tags_json)>1, mvmap(tags_json, if(spath(tags_json, "Key")=="Contact", spath(tags_json, "Value"), null()))
            )
    | fields + _time, _raw, tags_json, contact

 

 


Below is a screenshot of an example on my local instance.

dtburrows3_0-1704171143729.png

First we extract all json objects from the tags array as a multivalued field named "tags_json". From there you can use the mvmap() function to loop through the multivalue field and check each entry to see if the Key field value of the json object is equal to "Contact". If it is, then we know this is the json object we want to target the extraction of the "Value" key from. So we do an Spath specificly on that object and store the returned value as a field named "contact".

Option 2:
Another route to take (depending on the structure of your event and if it make sense to do i this way).
We can loop through each json object in the tags array and stuff the key/values into a temporary json object that we can then do a full spath against. This is a more exhaustive approach as apposed to the targeted one in the previous example.

SPL to do this would look something like this.

<base_search>
    | eval
        ``` extract array of json objects a multivalued field ```
        tags_json=spath(_raw, "tags{}"),
        ``` initialize the temporary json object that will hold all the key/value pairs contained within the tags array ```
        final_tag_json=json_object()
    ``` use the mode=multivalue foreach loop to loop through each entry in the multivalued field ```
    | foreach mode=multivalue tags_json
        [
            | eval
                ``` json_set() function will set up each Key/Value as a new key/value pair in the temporary json "final_tag_json" ```
                final_tag_json=json_set(final_tag_json, spath('<<ITEM>>', "Key"), spath('<<ITEM>>', "Value"))
            ]
    | fields - tags_json
    ``` full spath against the final_tag_json field ```
    | spath input=final_tag_json
    | fields - final_tag_json
    | fields + _time, _raw, Contact, Name


You can see in this screenshot that not only is the "Contact" field extracted but "Name" value is extracted as well, this method would loop through each json array and extract a new Key/Value pair for each entry.

dtburrows3_0-1704174836107.png

Below is a screenshot showing what the temporary final_tag_json object looks like that we did the full spath against for context.

dtburrows3_1-1704174963016.png

 

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...