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

[TravellingGuy]

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.

[yuanliu]

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

[TravellingGuy]

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.

[yuanliu · Accepted solution]

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

[TravellingGuy]

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