Azure Query: 'where' operator: Failed to resolve column or scalar expression named 'displayName'

%3CLINGO-SUB%20id%3D%22lingo-sub-2276513%22%20slang%3D%22en-US%22%3EAzure%20Query%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20column%20or%20scalar%20expression%20named%20'displayName'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276513%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrying%20to%20enable%20a%20rule%20in%20Azure%20Sentinel%20that%20contains%20the%20following%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-git%22%3E%3CCODE%3Elet%20OperationList%20%3D%20dynamic(%5B%22Add%20member%20to%20role%22%2C%20%22Add%20member%20to%20role%20in%20PIM%20requested%20(permanent)%22%2C%20%22AzureDiagnostics%22%5D)%3B%0Alet%20PrivilegedGroups%20%3D%20dynamic(%5B%22UserAccountAdmins%22%2C%20%22PrivilegedRoleAdmins%22%2C%20%22TenantAdmins%22%5D)%3B%0AAuditLogs%0A%7C%20where%20LoggedByService%20%3D~%20%22Core%20Directory%22%0A%7C%20where%20Category%20%3D~%20%22RoleManagement%22%0A%7C%20where%20OperationName%20in~%20(OperationList)%0A%7C%20mv-expand%20TargetResources%0A%7C%20extend%20modProps%20%3D%20parse_json(TargetResources).modifiedProperties%0A%7C%20mv-expand%20bagexpansion%3Darray%20modProps%0A%7C%20evaluate%20bag_unpack(modProps)%0A%7C%20where%20displayName%20%3D~%20%22Role.WellKnownObjectName%22%0A%7C%20extend%20DisplayName%20%3D%20displayName%2C%20GroupName%20%3D%20replace('%22'%2C%20''%2C%20newValue)%0A%7C%20extend%20initByApp%20%3D%20parse_json(InitiatedBy).app%2C%20initByUser%20%3D%20parse_json(InitiatedBy).user%0A%7C%20extend%20AppId%20%3D%20initByApp.appId%2C%20%0A%20%20%20%20InitiatedByDisplayName%20%3D%20case(isnotempty(initByApp.displayName)%2C%20initByApp.displayName%2C%20isnotempty(initByUser.displayName)%2C%20initByUser.displayName%2C%20%22not%20available%22)%2C%0A%20%20%20%20ServicePrincipalId%20%3D%20initByApp.servicePrincipalId%2C%0A%20%20%20%20ServicePrincipalName%20%3D%20initByApp.servicePrincipalName%2C%0A%20%20%20%20UserId%20%3D%20initByUser.id%2C%0A%20%20%20%20UserIPAddress%20%3D%20initByUser.ipAddress%2C%0A%20%20%20%20UserRoles%20%3D%20initByUser.roles%2C%0A%20%20%20%20UserPrincipalName%20%3D%20initByUser.userPrincipalName%0A%2F%2F%7C%20where%20GroupName%20in~%20(PrivilegedGroups)%0A%2F%2F%20If%20you%20want%20to%20still%20alert%20for%20operations%20from%20PIM%2C%20remove%20below%20filtering%20for%20MS-PIM.%0A%2F%2F%7C%20where%20InitiatedByDisplayName%20!%3D%20%22MS-PIM%22%0A%7C%20project%20TimeGenerated%2C%20AADOperationType%2C%20Category%2C%20OperationName%2C%20AADTenantId%2C%20AppId%2C%20InitiatedByDisplayName%2C%20ServicePrincipalId%2C%20ServicePrincipalName%2C%20DisplayName%2C%20GroupName%2C%20UserId%2C%20UserIPAddress%2C%20UserRoles%2C%20UserPrincipalName%0A%7C%20extend%20timestamp%20%3D%20TimeGenerated%2C%20AccountCustomEntity%20%3D%20case(isnotempty(ServicePrincipalName)%2C%20ServicePrincipalName%2C%20isnotempty(ServicePrincipalId)%2C%20ServicePrincipalId%2C%20isnotempty(UserPrincipalName)%2C%20UserPrincipalName%2C%20%22not%20available%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESend%20the%20following%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22notification-wrapper%20ng-star-inserted%22%3E%3CDIV%20class%3D%22notification-container%20error%22%3E%3CDIV%20class%3D%22notification-content%22%3E%3CDIV%20class%3D%22notification-message%20ng-star-inserted%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E'where'%20operator%3A%20Failed%20to%20resolve%20column%20or%20scalar%20expression%20named%20'displayName'%20If%20issue%20persists%2C%20please%20open%20a%20support%20ticket.%20Request%20id%3A%203e7b7ded-8631-4118-b133-d0501c20eba2%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22container-end%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3EDo%20you%20have%20any%20idea%20if%20this%20expression%20stopped%20working%20in%20Azure%20or%20what%20setting%20could%20help%20me%20so%20that%20the%20query%20returns%20information%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20regards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278497%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Query%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20column%20or%20scalar%20expression%20named%20'displayName'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278497%22%20slang%3D%22en-US%22%3EI%20too%20am%20experiencing%20the%20same%20issue.%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello.

 

Trying to enable a rule in Azure Sentinel that contains the following query:

 

 

let OperationList = dynamic(["Add member to role", "Add member to role in PIM requested (permanent)", "AzureDiagnostics"]);
let PrivilegedGroups = dynamic(["UserAccountAdmins", "PrivilegedRoleAdmins", "TenantAdmins"]);
AuditLogs
| where LoggedByService =~ "Core Directory"
| where Category =~ "RoleManagement"
| where OperationName in~ (OperationList)
| mv-expand TargetResources
| extend modProps = parse_json(TargetResources).modifiedProperties
| mv-expand bagexpansion=array modProps
| evaluate bag_unpack(modProps)
| where displayName =~ "Role.WellKnownObjectName"
| extend DisplayName = displayName, GroupName = replace('"', '', newValue)
| extend initByApp = parse_json(InitiatedBy).app, initByUser = parse_json(InitiatedBy).user
| extend AppId = initByApp.appId, 
    InitiatedByDisplayName = case(isnotempty(initByApp.displayName), initByApp.displayName, isnotempty(initByUser.displayName), initByUser.displayName, "not available"),
    ServicePrincipalId = initByApp.servicePrincipalId,
    ServicePrincipalName = initByApp.servicePrincipalName,
    UserId = initByUser.id,
    UserIPAddress = initByUser.ipAddress,
    UserRoles = initByUser.roles,
    UserPrincipalName = initByUser.userPrincipalName
//| where GroupName in~ (PrivilegedGroups)
// If you want to still alert for operations from PIM, remove below filtering for MS-PIM.
//| where InitiatedByDisplayName != "MS-PIM"
| project TimeGenerated, AADOperationType, Category, OperationName, AADTenantId, AppId, InitiatedByDisplayName, ServicePrincipalId, ServicePrincipalName, DisplayName, GroupName, UserId, UserIPAddress, UserRoles, UserPrincipalName
| extend timestamp = TimeGenerated, AccountCustomEntity = case(isnotempty(ServicePrincipalName), ServicePrincipalName, isnotempty(ServicePrincipalId), ServicePrincipalId, isnotempty(UserPrincipalName), UserPrincipalName, "not available")

 


Send the following error:

 

'where' operator: Failed to resolve column or scalar expression named 'displayName' If issue persists, please open a support ticket. Request id: 3e7b7ded-8631-4118-b133-d0501c20eba2
 

Do you have any idea if this expression stopped working in Azure or what setting could help me so that the query returns information?

 

Thanks, regards.

1 Reply