Forum Discussion
Solved
Perform a lookup on each value in array
I have been experimenting with exporting conditional access policies to LA for historical reporting purposes. Each policy has a number of attributes which have arrays of zero or more members containi...
It seems like I have an answer to my own question, but it only lets me pack up one field. If I wanted to keep the GUIDs and the UserName I don't have a solution for this.
let FactTable=datatable(Rule:string,IncludeUsers:string) [ "Allow Rule", '["b152239e-e443-4fdd-b989-caae6a46b34f","0366ea79-ca58-4207-9d01-945d9b36c0ca"]', "Deny Rule", '["f569275d-a42c-4282-b41f-fff90eb960cb"]' ]; let DimTable=datatable(userGuid:string,UserName:string) [ "b152239e-e443-4fdd-b989-caae6a46b34f", "Bob", "0366ea79-ca58-4207-9d01-945d9b36c0ca", "Alice", "f569275d-a42c-4282-b41f-fff90eb960cb", "Eve" ]; FactTable | extend innerJson=todynamic(IncludeUsers) | project-away IncludeUsers | mv-expand innerJson to typeof(string) | lookup kind=leftouter DimTable on $left.innerJson==$right.userGuid | summarize Users=make_set(UserName) by RuleAnyone got a solution for this? And while this works in this very simple example, my real data is much messier and has multiple instances of GUIDs I'd like to replace in records with human readable names.
It seems like I have an answer to my own question, but it only lets me pack up one field. If I wanted to keep the GUIDs and the UserName I don't have a solution for this.
let FactTable=datatable(Rule:string,IncludeUsers:string) [
"Allow Rule", '["b152239e-e443-4fdd-b989-caae6a46b34f","0366ea79-ca58-4207-9d01-945d9b36c0ca"]',
"Deny Rule", '["f569275d-a42c-4282-b41f-fff90eb960cb"]'
];
let DimTable=datatable(userGuid:string,UserName:string) [
"b152239e-e443-4fdd-b989-caae6a46b34f", "Bob",
"0366ea79-ca58-4207-9d01-945d9b36c0ca", "Alice",
"f569275d-a42c-4282-b41f-fff90eb960cb", "Eve"
];
FactTable
| extend innerJson=todynamic(IncludeUsers)
| project-away IncludeUsers
| mv-expand innerJson to typeof(string)
| lookup kind=leftouter DimTable on $left.innerJson==$right.userGuid
| summarize Users=make_set(UserName) by RuleAnyone got a solution for this? And while this works in this very simple example, my real data is much messier and has multiple instances of GUIDs I'd like to replace in records with human readable names.