get a table of all tables across x number of log analytics workspaces

%3CLINGO-SUB%20id%3D%22lingo-sub-3358407%22%20slang%3D%22en-US%22%3Eget%20a%20table%20of%20all%20tables%20across%20x%20number%20of%20log%20analytics%20workspaces%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3358407%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20~20%20Log%20Analytics%20workspaces%20and%20would%20like%20to%20create%20a%20query%20that%20would%20basically%20return%20my%20a%20table%20that%20would%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'workspace_name'%20%7C'tables'%3C%2FP%3E%3CP%3E----------------------------------------------------%3C%2FP%3E%3CP%3Eworkspace%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20ActivityLog%2C%20Perf%2C%20Event%3C%2FP%3E%3CP%3E----------------------------------------------------%3C%2FP%3E%3CP%3Eworkspace%202%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20SecurityEvent%2C%20Perf%2C%20Update%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20list%20all%20the%20workspaces%20and%20the%20tables%20in%20them.%3C%2FP%3E%3CP%3EWhat%20I%20have%20right%20now%20is%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3Eunion%20withsourcce%3D%20table%20*%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20summerize%20Size%20%3D%20sum(_BilledSize)%20by%20table%0A%7C%20project%20%5B'Table%20Name'%5D%20%3D%20table%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20returns%20the%20tables%20in%20a%20given%20workspace%2C%20but%20I%20don't%20know%20how%20to%20achieve%20the%20above.%20Any%20advice%20is%20welcome%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3366128%22%20slang%3D%22en-US%22%3ERe%3A%20get%20a%20table%20of%20all%20tables%20across%20x%20number%20of%20log%20analytics%20workspaces%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3366128%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1384991%22%20target%3D%22_blank%22%3E%40cryptoSHA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20a%20Cross%20Workspace%20Query%2C%20please%20read%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flogs%2Fcross-workspace-query%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EQuery%20across%20resources%20with%20Azure%20Monitor%20-%20Azure%20Monitor%20%7C%20Microsoft%20Docs%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20Usage%20table%20is%20optimised%20to%20gather%20this%20data%2C%20and%20its%20cross%20workspace%20friendly%20-%26nbsp%3B%20One%20example%20is%3A%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eworkspace(%22yourWorkspaceName%22).Usage%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20%20SizeMB%20%3D%20sum(Quantity)%2C%20SizeGB%20%3D%20sum(Quantity)%2F1000%20by%20DataType%2C%20IsBillable%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3E%26nbsp%3BYou%20can%20extend%20this%20and%20use%20Pivot%20mode%20to%20display%20the%20results%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eunion%20%0A(Usage%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20%20SizeMB%20%3D%20sum(Quantity)%2C%20SizeGB%20%3D%20sum(Quantity)%2F1000%20by%20DataType%2C%20IsBillable%2C%20workspaceName%3D'local'%0A)%2C%0A(%0Aworkspace(%22nnnnn%22).Usage%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20%20SizeMB%20%3D%20sum(Quantity)%2C%20SizeGB%20%3D%20sum(Quantity)%2F1000%20by%20DataType%2C%20IsBillable%2C%20workspaceName%3D'fake'%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Clive_Watson_0-1652373091916.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371227iD4DD99BA674DC0E6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Clive_Watson_0-1652373091916.png%22%20alt%3D%22Clive_Watson_0-1652373091916.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIf%20you%20know%20about%20Azure%20Workbooks%2C%20that%20has%20a%20feature%20where%20you%20can%20run%20a%20Query%20against%20any%20selected%20Workspace.%20An%20example%20if%20you%20want%20to%20go%20and%20look%20at%20the%20process%2C%20but%20its%20will%20only%20show%20data%20if%20you%20have%20Sentinel%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fblob%2Fmaster%2FWorkbooks%2FSentinelCentral.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure-Sentinel%2FSentinelCentral.json%20at%20master%20%C2%B7%20Azure%2FAzure-Sentinel%20(github.com)%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I have ~20 Log Analytics workspaces and would like to create a query that would basically return my a table that would look like this:

 

'workspace_name' |'tables'

----------------------------------------------------

workspace 1          | ActivityLog, Perf, Event

----------------------------------------------------

workspace 2          | SecurityEvent, Perf, Update

 

Basically list all the workspaces and the tables in them.

What I have right now is this:

 

union withsourcce= table *
| where TimeGenerated > ago(1d)
| summerize Size = sum(_BilledSize) by table
| project ['Table Name'] = table

 

This returns the tables in a given workspace, but I don't know how to achieve the above. Any advice is welcome !

1 Reply

@cryptoSHA 

 

You need a Cross Workspace Query, please read Query across resources with Azure Monitor - Azure Monitor | Microsoft Docs

The Usage table is optimised to gather this data, and its cross workspace friendly -  One example is: 

workspace("yourWorkspaceName").Usage
| where TimeGenerated > ago(1d)
| summarize  SizeMB = sum(Quantity), SizeGB = sum(Quantity)/1000 by DataType, IsBillable


 You can extend this and use Pivot mode to display the results

union 
(Usage
| where TimeGenerated > ago(1d)
| summarize  SizeMB = sum(Quantity), SizeGB = sum(Quantity)/1000 by DataType, IsBillable, workspaceName='local'
),
(
workspace("nnnnn").Usage
| where TimeGenerated > ago(1d)
| summarize  SizeMB = sum(Quantity), SizeGB = sum(Quantity)/1000 by DataType, IsBillable, workspaceName='fake'
)

 

Clive_Watson_0-1652373091916.png


If you know about Azure Workbooks, that has a feature where you can run a Query against any selected Workspace. An example if you want to go and look at the process, but its will only show data if you have Sentinel  Azure-Sentinel/SentinelCentral.json at master · Azure/Azure-Sentinel (github.com)