Log Analytics - Cross-workspace queries

 Log Analytics allows you to query up to 10 workspaces simultaneously as long as you have at least read permission on them. This is very handy in scenarios when you want to get the results from multiple workspaces at the same time, or when you want to correlate data living in different workspaces or in general when you are managing multiple workspaces even if they hosted in different subscriptions.

 

The way you do this from the Log Analytics workspace is using a query similar to the one below.

union OfficeActivity, workspace('secondworkspace').OfficeActivity, workspace('thirdworkspace').OfficeActivity, workspace('ninethworkspace').OfficeActivity | where TimeGenerated > ago(1d)

The problem with the query above is that you need to retype the workspace names every time and you have to specify the table name as well.

 

One possible solution would be to save the query to reuse it in the future but this is not the ideal solution considering the table is hardcoded in the query and most likely you will want to query different tables from time to time. Furthermore, if you add new workspaces, you will need to update the query.

 

Due to this existing limitation I have come up with the following PowerShell script which will retrieve all workspaces across all subscriptions you have access to and it will execute the query against the table you specify at runtime.

param (

    [Parameter(Mandatory)]$table,

    [Parameter(Mandatory)]$inputQuery

    )

$subscriptions = Get-AzSubscription

$workspaces = @()

$buildQuery = $null

$workingWorkspaceId = "Specify here the workspace id of the workspace you usually work from."

foreach ($subscription in $subscriptions) {

    Select-AzSubscription $subscription

    $workspace = Get-AzOperationalInsightsWorkspace | Select-Object -ExpandProperty Name

    $workspaces += $workspace

    $workspaces = $workspaces -notmatch "specify here the name of the workspace you usually work from so that it is not queried twice"

}

foreach ($workspace in $workspaces) {

    $buildQuery += ",workspace(`"$($workspace)`").$table"

}

$query = @"

union $table$buildQuery$inputQuery

"@

$output = (Invoke-AzOperationalInsightsQuery -WorkspaceId $workingWorkspaceId -Query $query).Results

$output

The script is also available from my Github.

 

To execute the script simply provide the table name and query you would like to use. For example:

.\Invoke-CrossQuery.ps1 -table "OfficeActivity" -inputQuery "| where Time Generated > ago(1d)"