topic How to select the value of a field where another field equals a specific value? in Splunk Search

How to select the value of a field where another field equals a specific value?

TravellingGuy — Mon, 22 May 2023 12:39:19 GMT

Hi!

I have a search query problem that's wrecking my newbie brain.

I have log events that look like this:

{ "operationName": "Add app role assignment to group", "properties": { "targetResources": [ { "administrativeUnits": [], "displayName": "MyAwesomeDisplayName", "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "modifiedProperties": [ { "displayName": "AppRole.Id", "newValue": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "oldValue": null }, { "displayName": "AppRole.Value", "newValue": null, "oldValue": null }, { "displayName": "Group.ObjectID", "newValue": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "oldValue": null }, { "displayName": "Group.DisplayName", "newValue": "myAwesomeGroupName", "oldValue": null }, { "displayName": "Group.WellKnownObjectName", "newValue": null, "oldValue": null } ], "type": "ServicePrincipal" } ], "userAgent": null } }

What I'm trying to do is get the corresponding value for newValue where displayName is Group.DisplayName. i.e. when displayName=Group.DisplayName, the corresponding newValue for that would be (in this example) myAwesomeGroupName.

Not every log event will have a displayName=Group.DisplayName event in it, so that's why I'm looking to capture when it's there.

I hope that makes sense.

Re: How to select the value of a field where another field equals a specific value

yuanliu — Sat, 20 May 2023 02:39:06 GMT

Something like

| spath path=properties.targetResources{} output=targetResources | mvexpand targetResources | fields - data | spath input=targetResources | rename displayName as displayName_orig | spath input=targetResources path=modifiedProperties{} | mvexpand modifiedProperties{} | spath input=modifiedProperties{} | rename displayName as displayName_mod | where displayName_mod == displayName_orig

Re: How to select the value of a field where another field equals a specific value

TravellingGuy — Tue, 23 May 2023 20:03:52 GMT

Hi yuanliu.

Thanks for trying, but I realize by question wasn't phrased very well and my example JSON wasn't a very good example.

I think I have a solution, but I'll try explaining it differently. Here's a sample JSON log.

{ "operationName": "Add app role assignment to group", "properties": { "targetResources": [ { "administrativeUnits": [], "displayName": "MyAwesomeDisplayName", "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "modifiedProperties": [ { "displayName": "Group.DisplayName", "newValue": "myAwesomeGroupName", "oldValue": null } ], "type": "ServicePrincipal" } ], "userAgent": null }, "operationName": "Add app role assignment grant to user", "properties": { "targetResources": [ { "administrativeUnits": [], "displayName": "MyAwesomeDisplayNameTwo", "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "modifiedProperties": [ { "displayName": "User.UPN", "newValue": "myemail@onetrust.com", "oldValue": null } ], "type": "ServicePrincipal" } ], "userAgent": null } }

What I want to do is, if properties.targetResources.modifiedProperties.displayName = "Group.DisplayName" then I want to retrieve the corresponding value in properties.targetResources.modifiedProperties.newValue (in this case "myAwesomeGroupName") and put it into a new field.

Correspondingly, if properties.targetResources.modifiedProperties.displayName = "User.UPN" then I want to get the corresponding newValue for that as a separate field.

Although, as I pondered it over the weekend, I think it's better if I make two separate alerts with two separate queries where my search only includes records where operationName = "Add app role assignment to group", then I know properties.targetResources.modifiedProperties.displayName = "Group.DisplayName" will always be there (and then a separate one for the User.UPN query/alert). I was trying to do it as a single query/alert, but there are two different things, really, so breaking them up makes more sense and should work for me.

Re: How to select the value of a field where another field equals a specific value

yuanliu — Wed, 24 May 2023 06:13:34 GMT

It sounds like you want to preserve all events but selectively populate a new field only when properties.targetResources{}.modifiedProperties{}.displayName is in a select group. Is this correct? Something like

| spath path=properties.targetResources{} | mvexpand properties.targetResources{} | spath input=properties.targetResources{} path=modifiedProperties{} | mvexpand modifiedProperties{} | spath input=modifiedProperties{} | eval newField = if(displayName IN ("Group.DisplayName", "User.UPN"), newValue, null())

But if this is concerning alerting, you would want to discard data outside that select group, like

| spath input=data path=properties.targetResources{} | mvexpand properties.targetResources{} | spath input=properties.targetResources{} path=modifiedProperties{} | mvexpand modifiedProperties{} | spath input=modifiedProperties{} | where displayName IN ("Group.DisplayName", "User.UPN") | rename newValue as newField

Re: How to select the value of a field where another field equals a specific value

TravellingGuy — Wed, 24 May 2023 21:36:47 GMT

Yes! That's it, exactly what I was looking to do. Thank you.