Splunk Search

compare two fields in json data and display data in the third field for the matched data

nikitha15
Explorer

Hi all,

I have only started working on splunk recently and i am stuck at one query. So, I have JSON data like below:

 

catDevices: [ {
model: A1_1234
Name: ZASNJHCDNA
}
 {

model: A1_5678
Name: JNDIHUEDHNJ
}]
Devices : [ 
JNDIHUEDHNJ
NVBBVUYVBHI
]

 

 

I want to compare "Devices" with caDevices{}.Name and if it matches i want to display Devices and model list.

I tried this query

 

index=main sourcetype=device |rename Devices{} as success | mvexpand success |dedup success |rename catDevices{}. model as Model ,rename catDevices{}.Name as device_name |eval zip = mvzip(Model, device_name) |fields - _raw |mvexpand zip | rex field = zip "(?<MODEL>.*),(?<DEVICE>.*)" | fields - zip | eval Status = if(match(MODEL,"A1*"), if(success == DEVICE, success, "NO MATCH"), "NO MATCH") | table success, MODEL, Status | where Status != "NO MATCH" | stats count(success) 

 

It worked but as the data increases , due to mvexpand threshold the result is not accurate. Can you please tell me how i can correct my query or if you can provide a different solution for my question, any help would be appreciated. thanks in advance.

 

Labels (1)
0 Karma

ITWhisperer
Ultra Champion

There appear to be a couple of typos(?) in your query (order of fields in mvzip don't match rex extraction, table field name should be success not suc, device is not listed in table so count should possibly be MODEL), but aside from this, if the issue is to do with mvexpand limitations, have a look at this solution to see if it helps https://community.splunk.com/t5/Splunk-Enterprise/Alternatives-to-using-MVExpand-running-into-limita... 

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!