Forum Discussion

cryptoSHA's avatar
cryptoSHA
Copper Contributor
May 11, 2022

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

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

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    May 12, 2022

    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'
)

     


    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)