Impossible number of occurrences being returned

[Ara]

Given the sample event below representing a user sign-in, I am trying to create a table that shows each combination of a 'policy' and 'result' and the number of occurrences for that combination. There are only three possible result values for any given policy (success, failure, or notApplied). In essence, I need this table to find out how which policies are not being used by looking at the number of times it was not applied.

i.e.:

Input:

Desired Output:

displayName	result	count
Policy1	success	1
Policy2	failure	1
Policy3	notApplied	1

However, the query I currently have is returning a sum that isn't possible because the sum is exceeding the number of sign-in events. What is wrong with my query?

<my_search> | stats count by Policies{}.displayName, ConditionalAccessPolicies{}.result

[yuanliu]

(First off, please post sample data as text block, not screenshot.)  You should first convert JSON array Policy{} into multivalue of its JSON element before applying mvexpand.  spath is very useful here.  In 9.0, Splunk added a new command fromjson which is more convenient for your case.  The following uses fromjson:

| fromjson _raw
| mvexpand Policies
| fromjson Policies
| stats count by displayName result

Your mock data gives

displayName	result	count
Policy1	success	1
Policy2	failure	1
Policy3	notApplied	1

This is an emulation of your mock data you can play with and compare with real data

| makeresults
| eval _raw = "{\"SigninId\": \"some-id\",
\"Policies\": [
{
\"id\": \"1234\",
\"displayName\": \"Policy1\",
\"result\": \"success\"
},
{
\"id\": \"4353\",
\"displayName\": \"Policy2\",
\"result\": \"failure\"
},
{
\"id\": \"0093\",
\"displayName\": \"Policy3\",
\"result\": \"notApplied\"
}
]"

[bowesmana]

Because each of those 2 fields has 3 values, you are effectively doing a 3x3 matrics stats, hence the results.

You will need to do something like this to join each of the policy bits of info together

| eval Policy=mvzip('Policies{}.displayName','Policies{}.result', "##")
| stats count by Policy

 and then finish it with

| eval Policy=split(Policy, "##")
| eval displayName=mvindex(Policy, 0), result=mvindex(Policy, 1)
| table displayName result count

[Ara]

Thanks! This looks to be returning the desired info and format. Though I noticed some Policies were missing counts for certain results. The number of different values possible for 'displayName' is showing less than is actually present in the event log. I think this may be an issue with Splunk itself and not the query though. 
Would you happen to know if it's possible for the number of values to have a max or limit in Splunk?