Forum Discussion
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 containing GUIDs. Such as included/excluded users and groups.
I have created some additional tables which will allow me to enrich the data with the user or group display name, however I am having trouble working out the best syntax to achieve this. The pseudocode would read like this:
For each guid in the attribute, add a second field with the display name of that guid.
So far the best I have been able to do is mv-expand the fact data, do the lookup. I now have the extra field that I want, but now I want to zip the events back up again and am not sure how. If only I could get the lookup command to work inside the mv-apply command, but I can't work that out.
Here is an example:
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
Is there a better way of doing this? If not, how do I recombine the events again afterwards?
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
- Thats nearly the perfect way to write a question - thank you. However, what do you mean by" zip the events back up again"? Is it something like this as a last line?
| extend values = pack_all()
to build an array?When using mv-expand it duplicates 1 event into many events which allows me to do the lookup simply. After that I want to collapse the duplicated events back into a single event. I'll have to read up what pack_all() does.
