Need help writing a search command to determine if a value is in a json array more than once

[lyndac]

I am indexing json objects into splunk. An example of the json is:

 {
      id: "24234563",
      systems: [ "host1", "host2", "host3", "host1"]
      otherfield: "otherfieldvalue"
    }
    {
      id: "1255434",
      systems: [ "host3", "host2", "host7", "host1"]
      otherfield: "otherfieldvalue"
    }

I need to write a search to return all events where a given value of "systems" occurs in the same event more than once. So, if the data is the two events above, my search would only return the event with id=24234563.

I can figure out if there are any duplicates in the event using the search below, but that doesn't get me what I need and I'm not sure where to go from here.

 index=foo systems{}=host1 | rename systems{} as systems |eval numsystems= mvcount(systems) | eval sysdedup=mvdedup(systems) | eval numdedup=mvcount(sysdedup) | where numdedup != numsystems

[alacercogitatus · Accepted solution]

This should help you. mvexpand will build numerous events for each occurance of systems, which you can then build a search against to find the total counts. I've tested this on one of my systems, and the logic is sound and should return what you want.

index=foo| rename systems{} as systems | eventstats count as total_count | mvexpand systems | stats count values(total_count) as total_count by systems id | eval count  = count / (2*total_count) | where count > 1 | fields systems count id

This, for me returns this table:

systems count   id
host1       2   24234563

[somesoni2]

I don't see anything wrong with your query, So what is exactly your need? Are you looking for a specific Items?