Search code examples
splunksplunk-query

Splunk : Spath searching the JSON array

I have below two JSON events where under "appliedConditionalAccessPolicies", in one event policy1 has results =failure and policy2 has results=notApplied. In the other event the values are reversed.

Now I'm trying to get the event where the policy1 has the status="failure", it gives both the events

index=test
| spath path="appliedConditionalAccessPolicies{}" | search "appliedConditionalAccessPolicies{}.displayName"="policy1" "appliedConditionalAccessPolicies{}.result"="failure"

It looks like Its searching within all the elements in the array. How can I ensure It searches both the conditions on each element of the array and return the event which has the element satisfying both the conditions.

Events :

 appDisplayName: App1
   appId: aaaa-1111-111aeff-aad222221111
   appliedConditionalAccessPolicies: [ 
     { 
       displayName: policy1
       enforcedGrantControls: [
         Block
       ]
       enforcedSessionControls: [
         SignInFrequency
         ContinuousAccessEvaluation
       ]
       id: f111113-111-400c-a251-2123bbe4233e1
       result: failure
     }
     { [-]
       displayName: policy2
       enforcedGrantControls: [ [-]
         Block
       ]
       enforcedSessionControls: [ [-]
       ]
       id: sdsds-8c92-45ef-sdsds-c0b2e006d39b
       result: notApplied
     }
   ]
   
   appDisplayName: App1
   appId: aaaa-1111-111aeff-aad222221111
   appliedConditionalAccessPolicies: [ 
     { 
       displayName: policy1
       enforcedGrantControls: [
         Block
       ]
       enforcedSessionControls: [
         SignInFrequency
         ContinuousAccessEvaluation
       ]
       id: f111113-111-400c-a251-2123bbe4233e1
       result: notApplied
     }
     { [-]
       displayName: policy2
       enforcedGrantControls: [ [-]
         Block
       ]
       enforcedSessionControls: [ [-]
       ]
       id: sdsds-8c92-45ef-sdsds-c0b2e006d39b
       result: failure
     }
   ]
Solution

  • The problem is that appliedConditionalAccessPolicies{}.displayName and appliedConditionalAccessPolicies{}.result are multi-value fields so you need to do something that determines if the search matches the same index of both multi-value fields.

    Here is a way using mvfind:

    And mvfind gives you the multi-value field index so you can compare them, but from my testing mvfind hates field names like appliedConditionalAccessPolicies{}.displayName and appliedConditionalAccessPolicies{}.result so you need to rename them before you can use them with mvfind. This works for me:

    | rename "appliedConditionalAccessPolicies{}.displayName" as displayName
| rename "appliedConditionalAccessPolicies{}.result" as result
| where mvfind(displayName,"policy1")=mvfind(result,"failure")

    Here is a full example that you can play with:

    | makeresults
| eval data="
{\"appDisplayName\":\"App1\",\"appId\":\"aaaa-1111-111aeff-aad222221111\",\"appliedConditionalAccessPolicies\":[{\"displayName\":\"policy1\",\"enforcedGrantControls\":[\"Block1\"],\"enforcedSessionControls\":[\"SignInFrequency\",\"ContinuousAccessEvaluation\"],\"id\":\"f111113-111-400c-a251-2123bbe4233e1\",\"result\":\"failure\"},{\"displayName\":\"policy2\",\"enforcedGrantControls\":[\"Block2\"],\"enforcedSessionControls\":[],\"id\":\"sdsds-8c92-45ef-sdsds-c0b2e006d39b\",\"result\":\"notApplied\"}]}
###
{\"appDisplayName\":\"App2\",\"appId\":\"aaaa-1111-111aeff-aad222221112\",\"appliedConditionalAccessPolicies\":[{\"displayName\":\"policy1\",\"enforcedGrantControls\":[\"Block1\"],\"enforcedSessionControls\":[\"SignInFrequency\",\"ContinuousAccessEvaluation\"],\"id\":\"f111113-111-400c-a251-2123bbe4233e1\",\"result\":\"notApplied\"},{\"displayName\":\"policy2\",\"enforcedGrantControls\":[\"Block2\"],\"enforcedSessionControls\":[],\"id\":\"sdsds-8c92-45ef-sdsds-c0b2e006d39b\",\"result\":\"failure\"}]}
"
| makemv data delim="###"
| mvexpand data
| spath input=data
| fields - data 

| rename "appliedConditionalAccessPolicies{}.displayName" as displayName
| rename "appliedConditionalAccessPolicies{}.result" as result
| where mvfind(displayName,"policy1")=mvfind(result,"failure")

    Here is a way using mvzip: (thanks to @warren)

    You can join the multi-value fields together nad then just search for the string that contains both values. It looks like mvzip also hates field names like appliedConditionalAccessPolicies{}.displayName and appliedConditionalAccessPolicies{}.result so you need to rename them before you can use them with mvzip. This works for me:

    | rename "appliedConditionalAccessPolicies{}.displayName" as displayName
| rename "appliedConditionalAccessPolicies{}.result" as result
| where mvzip(displayName,result)="policy1,failure"

    Here is a full example that you can play with:

    | makeresults
| eval data="
{\"appDisplayName\":\"App1\",\"appId\":\"aaaa-1111-111aeff-aad222221111\",\"appliedConditionalAccessPolicies\":[{\"displayName\":\"policy1\",\"enforcedGrantControls\":[\"Block1\"],\"enforcedSessionControls\":[\"SignInFrequency\",\"ContinuousAccessEvaluation\"],\"id\":\"f111113-111-400c-a251-2123bbe4233e1\",\"result\":\"failure\"},{\"displayName\":\"policy2\",\"enforcedGrantControls\":[\"Block2\"],\"enforcedSessionControls\":[],\"id\":\"sdsds-8c92-45ef-sdsds-c0b2e006d39b\",\"result\":\"notApplied\"}]}
###
{\"appDisplayName\":\"App2\",\"appId\":\"aaaa-1111-111aeff-aad222221112\",\"appliedConditionalAccessPolicies\":[{\"displayName\":\"policy1\",\"enforcedGrantControls\":[\"Block1\"],\"enforcedSessionControls\":[\"SignInFrequency\",\"ContinuousAccessEvaluation\"],\"id\":\"f111113-111-400c-a251-2123bbe4233e1\",\"result\":\"notApplied\"},{\"displayName\":\"policy2\",\"enforcedGrantControls\":[\"Block2\"],\"enforcedSessionControls\":[],\"id\":\"sdsds-8c92-45ef-sdsds-c0b2e006d39b\",\"result\":\"failure\"}]}
"
| makemv data delim="###"
| mvexpand data
| spath input=data
| fields - data 

| rename "appliedConditionalAccessPolicies{}.displayName" as displayName
| rename "appliedConditionalAccessPolicies{}.result" as result
| where mvzip(displayName,result)="policy1,failure"