Home
%3CLINGO-SUB%20id%3D%22lingo-sub-990843%22%20slang%3D%22en-US%22%3EQuerying%20multiple%20Log%20analytics%20workspace%20at%20once.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-990843%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20folks%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%E2%80%99ve%20been%20in%20Orlando%20all%20week%20at%20%3CA%20href%3D%22https%3A%2F%2Fmyignite.techcommunity.microsoft.com%2Fhome%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EMicrosoft%20Ignite%3C%2FA%3E%20Orlando%2C%20and%20it%20has%20been%20a%20busy%20week.%26nbsp%3B%20Today%2C%20I%20meet%20with%20a%20sysadmin%20who%20wanted%20to%20know%20the%20best%20option%20to%20query%20multiple%20Azure%20Log%20Analytics%20workspace.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20scenario%20he%20was%20looking%20at.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22text-align%3A%20center%3B%22%3E%3CEM%3E%E2%80%9COur%20company%20deploys%20a%20solution%20to%20different%20subscriptions.%201%20per%20customer.%26nbsp%3B%20So%20a%20new%20customer%20is%20on-boarded%20by%20creating%20a%20new%20subscription%2C%20deploying%20the%20solution%20in%20it%20and%20providing%20the%20new%20URL%20of%20the%20service%20we%20provide%20to%20the%20customer.%E2%80%9D%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThey%20have%20decided%20to%20do%20this%20to%20be%20able%20to%20separate%20the%20billing%20per%20subscriptions%20cleanly.%26nbsp%3B%20Their%20first%20idea%20was%20to%20ingest%20all%20the%20data%20from%20all%20the%20Log%20Analytics%20workspace%20in%20a%20%E2%80%9CMaster%E2%80%9D%20workspace.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155562i9199A8852DC51BCE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFrom%20there%2C%20they%20would%20write%20all%20the%20queries%20they%20need%20for%20their%20dashboards%20and%20alert%20without%20having%20to%20run%20them%20in%20each%20workspace.%26nbsp%3B%20While%20this%20is%20possible%2C%20it%E2%80%99s%20not%20the%20most%20efficient%20way%20of%20doing%20it%2C%20and%20it%20could%20become%20costly%20because%20they%20would%20then%20be%20ingesting%20the%20data%20twice%2C%20and%20this%20would%20affect%20the%20%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fpricing%2Fdetails%2Fmonitor%2F%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Epricing%3C%2FA%3E.%20(remember%20that%20the%20first%205GB%20of%20data%20ingested%20is%20free%20in%20a%20Pay-As-You-Go%20model)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20we%20came%20up%20with%20was%20to%20start%20using%20cross-resource%20log%20queries.%26nbsp%3B%20This%20allows%20them%20to%20query%20not%20only%20across%20multiple%20Log%20Analytics%20workspaces%2C%20but%20also%20data%20from%20Application%20Insights%20in%20the%20same%20resource%20group%2C%20another%20resource%20group%2C%20or%20another%20subscription.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20acceptable%20to%20them%2C%20but%20if%20you%E2%80%99re%20considering%20this%20solution%20for%20yourself%2C%20remember%20that%20there%20are%20some%20limitations%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fazure-monitor%2Flog-query%2Fcross-workspace-query%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%23cross-resource-query-limits%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ECross-resource%20query%20limits%3C%2FA%3E%20(%3C%2FSTRONG%3E%3CEM%3EExcerpt%20from%20Docs.Microsoft.com)%3C%2FEM%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20number%20of%20Application%20Insights%20resources%20and%20Log%20Analytics%20workspaces%20that%20you%20can%20include%20in%20a%20single%20query%20is%20limited%20to%20%3CSTRONG%3E100%3C%2FSTRONG%3E.%3C%2FLI%3E%0A%3CLI%3EThe%20cross-resource%20query%20is%20not%20supported%20in%20View%20Designer.%20You%20can%20Author%20a%20query%20in%20Log%20Analytics%20and%20pin%20it%20to%20Azure%20dashboard%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fazure-monitor%2Flearn%2Ftutorial-logs-dashboards%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Evisualize%20a%20log%20query%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3ECross-resource%20query%20in%20log%20alerts%20is%20supported%20in%20the%20new%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Frest%2Fapi%2Fmonitor%2Fscheduledqueryrules%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EscheduledQueryRules%20API%3C%2FA%3E.%20By%20default%2C%20Azure%20Monitor%20uses%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Fapi-alerts%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Elegacy%20Log%20Analytics%20Alert%20API%3C%2FA%3E%26nbsp%3Bfor%20creating%20new%20log%20alert%20rules%20from%20Azure%20portal%2C%20unless%20you%20switch%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Falerts-log-api-switch%23process-of-switching-from-legacy-log-alerts-api%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Elegacy%20Log%20Alerts%20API%3C%2FA%3E.%20After%20the%20switch%2C%20the%20new%20API%20becomes%20the%20default%20for%20new%20alert%20rules%20in%20Azure%20portal%2C%20and%20it%20lets%20you%20create%20cross-resource%20query%20log%20alerts%20rules.%20You%20can%20create%20cross-resource%20query%20log%20alert%20rules%20without%20making%20the%20switch%20by%20using%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Falerts-log%23log-alert-with-cross-resource-query-using-azure-resource-template%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Resource%20Manager%20template%20for%20scheduledQueryRules%20API%3C%2FA%3E%26nbsp%3B%E2%80%93%20but%20this%20alert%20rule%20is%20manageable%20though%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Frest%2Fapi%2Fmonitor%2Fscheduledqueryrules%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EscheduledQueryRules%20API%3C%2FA%3E%26nbsp%3Band%20not%20from%20Azure%20portal.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20sysadmin%2C%20I%20was%20speaking%20with%20those%20limitations%20were%20not%20an%20issue.%26nbsp%3B%20But%20this%20is%20a%20stop-gap%20measure%20until%20they%20can%20figure%20out%20a%20permanent%20solution.%26nbsp%3B%20(they%20really%20hope%20to%20have%20more%20than%20100%20customers%E2%80%A6)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20query%20multiple%20workspaces%2C%20you%20need%20to%20reference%20the%20workspace%20in%20your%20query%2C%20using%20the%20workspace%20identifier%2C%20and%20for%20an%20app%20from%20Application%20Insights%2C%20use%20the%20app%20identifier.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20identifiers%20can%20be%20multiple%20types%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EResource%20name%20or%20Component%20Name%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155563i958D4C60458CA58A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EQualified%20name.%20It%E2%80%99s%20like%20the%20fully%20qualified%20name%20in%20this%20format%20%E2%80%9C%3CEM%3EsubscriptionName%2FresourceGroup%2FcomponentName%E2%80%9D.%3C%2FEM%3E%3CEM%3E%26nbsp%3B%20Considering%20that%20component%20names%20may%20not%20be%20unique%2C%20this%20is%20a%20good%20option.%3C%2FEM%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155564iD3D1D1D146A219AC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20Workspace%20ID.%20It%E2%80%99s%20the%20unique%20identifier%20assigned%20to%20each%20workspace%20represented%20as%20a%20globally%20unique%20identifier%20(GUID).%20This%20is%20a%20better%20option%20since%20it%20is%20unique%2C%20but%20in%20my%20opinion%2C%20it%20can%20be%20confusing%20since%20very%20few%20of%20us%20can%20actually%20remember%20a%20GUID.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155565iC2D479A6032347A4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20Azure%20Resource%20ID.%20The%20Azure-defined%20unique%20identity%20of%20the%20workspace.%20This%20is%20the%20best%20option%20since%20it%E2%80%99s%20unique%20and%20easy%20to%20recognize.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155567iFAA3870209EDB213%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_4.png%22%20title%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20they%20wanted%20to%20query%20Application%20Insights%20instead%20of%20Log%20Analytics%2C%20the%20query%20would%20start%20with%20%E2%80%9Capp()%E2%80%9D%20instead%20of%20%E2%80%9Cworkspace().%E2%80%9D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20end%2C%20all%20their%20queries%20will%20still%20need%20to%20be%20modified%20to%20add%20the%20proper%20cross-query%20information%20like%20the%20example%20below.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155566iC8B32BA929DEC1E0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_5.png%22%20title%3D%22clipboard_image_5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20more%20info%20on%20Azure%20Data%20Explorer%20Reference%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fkusto%2F%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%26nbsp%3B%20And%20as%20for%20the%20Query%20Language%2C%20there%20is%20a%20detailed%20reference%20which%20you%20can%20find%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fkusto%2Fquery%2Findex%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%26nbsp%3B%20Or%20you%20can%20visit%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fkusto%2Fquery%2Ftutorial%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20page%20for%20a%20tutorial%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20end%2C%20they%20might%20end%20up%20ingesting%20all%20the%20data%20in%20a%20master%20log%20analytics%20workspace.%20But%20that%E2%80%99s%20the%20subject%20of%20my%20next%20post.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheers%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPierre%20Roman%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-990843%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20folks%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%E2%80%99ve%20been%20in%20Orlando%20all%20week%20at%20%3CA%20href%3D%22https%3A%2F%2Fmyignite.techcommunity.microsoft.com%2Fhome%3FWT.mc_id%3DITOpsTalk-Blog-pierrer%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3EMicrosoft%20Ignite%3C%2FA%3E%20Orlando%2C%20and%20it%20has%20been%20a%20busy%20week.%26nbsp%3B%20Today%2C%20I%20meet%20with%20a%20sysadmin%20who%20wanted%20to%20know%20the%20best%20option%20to%20query%20multiple%20Azure%20Log%20Analytics%20workspace.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20explore%20the%20options%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-990843%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELog%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPierre%20Roman%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Hello folks,

 

We’ve been in Orlando all week at Microsoft Ignite Orlando, and it has been a busy week.  Today, I meet with a sysadmin who wanted to know the best option to query multiple Azure Log Analytics workspace.

 

Here is the scenario he was looking at.

 

“Our company deploys a solution to different subscriptions. 1 per customer.  So a new customer is on-boarded by creating a new subscription, deploying the solution in it and providing the new URL of the service we provide to the customer.”

 

They have decided to do this to be able to separate the billing per subscriptions cleanly.  Their first idea was to ingest all the data from all the Log Analytics workspace in a “Master” workspace.

 

 

clipboard_image_0.png

From there, they would write all the queries they need for their dashboards and alert without having to run them in each workspace.  While this is possible, it’s not the most efficient way of doing it, and it could become costly because they would then be ingesting the data twice, and this would affect the pricing. (remember that the first 5GB of data ingested is free in a Pay-As-You-Go model)

 

What we came up with was to start using cross-resource log queries.  This allows them to query not only across multiple Log Analytics workspaces, but also data from Application Insights in the same resource group, another resource group, or another subscription.

 

This is acceptable to them, but if you’re considering this solution for yourself, remember that there are some limitations:

 

Cross-resource query limits (Excerpt from Docs.Microsoft.com)

  • The number of Application Insights resources and Log Analytics workspaces that you can include in a single query is limited to 100.
  • The cross-resource query is not supported in View Designer. You can Author a query in Log Analytics and pin it to Azure dashboard to visualize a log query.
  • Cross-resource query in log alerts is supported in the new scheduledQueryRules API. By default, Azure Monitor uses the legacy Log Analytics Alert API for creating new log alert rules from Azure portal, unless you switch from legacy Log Alerts API. After the switch, the new API becomes the default for new alert rules in Azure portal, and it lets you create cross-resource query log alerts rules. You can create cross-resource query log alert rules without making the switch by using the Azure Resource Manager template for scheduledQueryRules API – but this alert rule is manageable though scheduledQueryRules API and not from Azure portal.

 

For the sysadmin, I was speaking with those limitations were not an issue.  But this is a stop-gap measure until they can figure out a permanent solution.  (they really hope to have more than 100 customers…)

 

To query multiple workspaces, you need to reference the workspace in your query, using the workspace identifier, and for an app from Application Insights, use the app identifier.

 

The identifiers can be multiple types:

  • Resource name or Component Name

clipboard_image_1.png

 

  • Qualified name. It’s like the fully qualified name in this format “subscriptionName/resourceGroup/componentName”.  Considering that component names may not be unique, this is a good option.

clipboard_image_2.png

 

  • The Workspace ID. It’s the unique identifier assigned to each workspace represented as a globally unique identifier (GUID). This is a better option since it is unique, but in my opinion, it can be confusing since very few of us can actually remember a GUID.

 

clipboard_image_3.png

 

  • The Azure Resource ID. The Azure-defined unique identity of the workspace. This is the best option since it’s unique and easy to recognize.

 

clipboard_image_4.png

 

If they wanted to query Application Insights instead of Log Analytics, the query would start with “app()” instead of “workspace().”

 

In the end, all their queries will still need to be modified to add the proper cross-query information like the example below.

 

clipboard_image_5.png

 

You can find more info on Azure Data Explorer Reference here.  And as for the Query Language, there is a detailed reference which you can find here.  Or you can visit this page for a tutorial.

 

In the end, they might end up ingesting all the data in a master log analytics workspace. But that’s the subject of my next post.

 

Cheers

 

Pierre Roman