Azure Workbook merge query visualization

René 26

I have created an Azure Workbook with a merge query that combines two table sources.
This produces a nice table of resources (in this case, a list of VM's).

Now all I want to do is somehow summarize this merged table and get the total number of VM's count, presented in a tile.
Anyone know if this is possible?

SwathiDhanwada-MSFT 18,766 Reputation points

2022-02-14T04:54:02.67+00:00

@René Welcome to Microsoft Q & A Community Forum. Can you share the query that you have merged so that I can modify it according to your requirement ?

Also, to summarize the count of vm's, kusto has summarize operator which aggregates the contents of the table.
SwathiDhanwada-MSFT 18,766 Reputation points

2022-02-15T06:35:05.77+00:00

@ReneKamp Did you get chance to look into my previous comment ? Kindly revert with the requested information.
René 26 Reputation points

2022-02-15T08:16:18.557+00:00
Hello @SwathiDhanwada-MSFT and thanks for your reply.
The problem is that this is not pure Kusto I'm affraid. This is a workbook in Azure (Sentinel) that makes use of some Kusto.

First query using Azure Reource Manager Graph as log source:

Resources | where type =~ 'Microsoft.Compute/virtualMachines' | project id, name | order by name asc

This produces a table with all VM's we have running.

Second query using Log Anayltics:

DeviceInfo | summarize arg_max(Timestamp, DeviceName, OSPlatform) by DeviceName | project DeviceName, Timestamp, OSPlatform | extend DeviceHostname = split (DeviceName,".")[0]

This produces a table with all VM's that are registered in Defender 365

Now I want to 'left anti-join' the result so we get a list of all VM's that are NOT registered in Defender 365.
In Azure Workbook I see no other option then using the 'Merge Query' which looks like this:

This produces the correct table, but I don't see a way to run Kusto on this to do another query.

Accepted answer

Anonymous

2022-02-15T09:39:01.077+00:00

The Merge is the final stage at the moment, I raised a feature request about this last year, to allow Columns to be exported. A little like the export of a row, but for columns. I havent heard if it will be done, but it was noted and put on the backlog. ![174407-image.png][1] However I do have a workaround. Step 1, create two Parameters in your workbook - I called mine A and B (a= your ARG query, B= Logs query) Step 2, create Query Step and use this code (modify as you see fit) let merge1 = dynamic(['{a}']); let merge2 = dynamic(['{b}']); print a=split(merge1,','), b=split(merge2,',') | project missingComputer=set_difference(a, b) set_difference is used to find Computers in the first array (a) thats are not in the other array (b): https://learn.microsoft.com/en-gb/azure/data-explorer/kusto/query/setdifferencefunction ![174417-screenshot-2022-02-15-093559.png][2] [1]: /api/attachments/174407-image.png?platform=QnA [2]: /api/attachments/174417-screenshot-2022-02-15-093559.png?platform=QnA
Please sign in to rate this answer.

2 people found this answer helpful.
René 26 Reputation points

2022-02-15T10:27:15.567+00:00

Thanks, that is a nice workaround!

Moritz von Witzleben 5 Reputation points

2024-04-11T09:37:50.3566667+00:00

It also helped me to accelerate a workbook significantly! Thanks for sharing!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Workbook merge query visualization

0 additional answers

Your answer