topic Re: Impossible number of occurrences being returned in Splunk Search

Impossible number of occurrences being returned

Ara — Thu, 18 Jan 2024 22:22:06 GMT

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

Re: Impossible number of occurrences being returned

bowesmana — Thu, 18 Jan 2024 23:27:33 GMT

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

Re: Impossible number of occurrences being returned

Ara — Tue, 23 Jan 2024 16:09:31 GMT

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?

Re: Impossible number of occurrences being returned

yuanliu — Wed, 24 Jan 2024 07:42:55 GMT

(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\" } ]"