Azure Azure Log Analytics Dashboard OMS

Azure Log Analytics – Cross Workspace Query Resolve Workspace ID

FigureOut

At Ignite 2017 Microsoft introduced a new functionality in Azure Log Analytics (ALA) to write queries across workspaces. This has been a long awaited feature for many customers. Why? Let’s imagine you want to provide IT services for customers and you would like to keep data separated for each customer in its own workspace.  At the end of the day you want to know across all customer workspace, which agents are sending heartbeats so you have your agent “inventory” and to which customer the agent belongs to. This is just a very simple example but I think you get the idea.

If we write a query to combine data from two different workspaces it looks like this…

image

The only way to distinguish the customers is the TenantId column which corresponds to the workspace id. Well, this kind of data is not very readable for a human being. Wouldn’t it be nice to have a way to resolve  the workspace id to the customer name? Azure Log Analytics provides a datatable (lookup table) which we could use in this case.

First define the datatable…

let CustomerTable = datatable(TenantId:string, Customer:string)
[
"6599bc34-xxxx-xxxx-xxxx-xxxxxxxx", "Customer1",
"c14aa418-xxxx-xxxx-xxxx-xxxxxxxx", "Customer2",
];

…next we define which workspaces to query from and use an inner join to match records on TenantId from our the workspace query to match the corresponding records in the datatable…

union workspace("workspace-001").Heartbeat , workspace("workspace-002").Heartbeat
| join kind = inner CustomerTable on TenantId
| summarize dcount(TenantId) by TenantId, Computer, Customer

…the output will look like this…

image

I think this is a quick and convenient way to make your data more readable and of course you could extend it as much as you need it. Have fun!

One Reply to “Azure Log Analytics – Cross Workspace Query Resolve Workspace ID

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.