Building for the Splunk Platform

How to map a multivalued JSON Field Value(X) to its respective Field(Y) while writing datamodel Searches?

bhargavnariyani
Path Finder

Hi,
I Have a Sample Event as Follows:

     A: [
        {   [-]
         a1: xxx
         b1:xxxx    
        }   
        {   [-]
              a1: yyy
              b1:yyyy
        }   
    ]   

Am Mapping the fields in Datamodel and Getting Values a1:xxx,yyy
b1:xxxx,yyyy in the field.
When The Query is written For a table What should be done to map the value xxx of a1 to xxxx of b1 and yyy of a1 to yyyy of b1 respectively from a datamodel.

Tags (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust
A:
    [
    {    [-]
            a1: xxx
            b1:xxxx    
         }    
         {    [-]
               a1: yyy
               b1:yyyy
         }    
       ]

As I understand your question, you need below result from above event using DataModel.

a1    b1
--------------
xxx    xxxx
yyy    yyyy

I would like to share my views with Data model and Non- Data model approach.

With DataModel Approach

With DataModel approach, we have to use tstats search and for the getting a1 and b1 columns we can write search like below

| tstats values(datamodelName.node.a1) as a1,values(datamodelName.node.b1) as b1 
from datamodel=datamodelName 
where nodename=datamodelName.node 
by _time,YOUR_UNIQUE_ID_IF_ANY

But this will also return multivalued in a1 and b1, with loosing mapping.

I have tried to store node "A" (full JSON string ) in data model by using spath configuration in props.conf and tried with below search and I got expected result.

| tstats count
from datamodel=datamodelName 
where nodename=datamodelName.node 
by _time,YOUR_UNIQUE_ID_IF_ANY,datamodelName.node.A
| eval _raw=datamodelName.node.A 
| extract pairdelim="\"{,}" kvdelim=":" 
| table a1 b1

But it is very difficult and complex approach and I'm NOT SUGGESTING. Because DataModel is not for storing JSON object.
It is good to keep data model as simple as possible.

Non-Data model Approach

I'm suggesting Non-Data model Approach:

The Non-data model approach, we have to write a plain search and extract multiple rows and columns from a single event by using mvzip, mvindex and split.

See below search.

YOUR SEARCH 
| eval tempField=mvzip(a1,b1) 
| stats count by _time,YOUR_UNIQUE_ID_IF_ANY,tempField 
| eval a1=mvindex(split(tempField,","),0), b1=mvindex(split(tempField,","),1) 

As per the JSON, node "A" contains multiple JSON objects this search will extract column a1 and b1. You can add more columns by using mvzip, splits

Well, now we are replacing Data model to plain search so performance issue can be raised. So I suggest to put this search in savedsearch.conf and accelerate it. It will give you expected output and performance both.

I hope this will help you.

Thanks
Kamlesh

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust
A:
    [
    {    [-]
            a1: xxx
            b1:xxxx    
         }    
         {    [-]
               a1: yyy
               b1:yyyy
         }    
       ]

As I understand your question, you need below result from above event using DataModel.

a1    b1
--------------
xxx    xxxx
yyy    yyyy

I would like to share my views with Data model and Non- Data model approach.

With DataModel Approach

With DataModel approach, we have to use tstats search and for the getting a1 and b1 columns we can write search like below

| tstats values(datamodelName.node.a1) as a1,values(datamodelName.node.b1) as b1 
from datamodel=datamodelName 
where nodename=datamodelName.node 
by _time,YOUR_UNIQUE_ID_IF_ANY

But this will also return multivalued in a1 and b1, with loosing mapping.

I have tried to store node "A" (full JSON string ) in data model by using spath configuration in props.conf and tried with below search and I got expected result.

| tstats count
from datamodel=datamodelName 
where nodename=datamodelName.node 
by _time,YOUR_UNIQUE_ID_IF_ANY,datamodelName.node.A
| eval _raw=datamodelName.node.A 
| extract pairdelim="\"{,}" kvdelim=":" 
| table a1 b1

But it is very difficult and complex approach and I'm NOT SUGGESTING. Because DataModel is not for storing JSON object.
It is good to keep data model as simple as possible.

Non-Data model Approach

I'm suggesting Non-Data model Approach:

The Non-data model approach, we have to write a plain search and extract multiple rows and columns from a single event by using mvzip, mvindex and split.

See below search.

YOUR SEARCH 
| eval tempField=mvzip(a1,b1) 
| stats count by _time,YOUR_UNIQUE_ID_IF_ANY,tempField 
| eval a1=mvindex(split(tempField,","),0), b1=mvindex(split(tempField,","),1) 

As per the JSON, node "A" contains multiple JSON objects this search will extract column a1 and b1. You can add more columns by using mvzip, splits

Well, now we are replacing Data model to plain search so performance issue can be raised. So I suggest to put this search in savedsearch.conf and accelerate it. It will give you expected output and performance both.

I hope this will help you.

Thanks
Kamlesh

0 Karma

bhargavnariyani
Path Finder

Used the Non DataModel Approach, It Worked Well...

0 Karma

woodcock
Esteemed Legend

I am sure that I do not understand the full context here but very generally you need mvzip, mvindex, and possibly mvfilter and mvjoin..

0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...