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

nikitha15 — Wed, 17 Mar 2021 12:20:10 GMT

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.

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

ITWhisperer — Mon, 15 Mar 2021 12:33:28 GMT

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-limitations/m-p/541523#M5051

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

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

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