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.
- The next problem with this solution is that the summarize function need to specifcy every other field you want to retain after the "by" clause
- Thats how summarize works, an alternative is to use arg_min or probably arg_max - you will need the TimeGenerated column (the real data will have that).
arg-max allows you to wildcard the columns "*" or you can name them as well. People often add a project or project-reorder after a summarize to order the columns
