Query Details
Workspaces And Tables
Query
//This query will display the tables from any/all workspaces across a tenant. Just need to modify the scope on a workspace to include the additional workspaces (as shown in the image) //Image link: https://raw.githubusercontent.com/rod-trent/SentinelKQL/master/Images/scope.png Usage | where StartTime > ago(1d) | extend workspaceName = tostring(split(ResourceUri, "/")[-1]) | extend subscription = tostring(split(ResourceUri, "/")[2]) | extend resourceGroup = tostring(split(ResourceUri, "/")[4]) | summarize ['Table Size'] =sum(Quantity) / 1000 by TenantId, subscription, resourceGroup, workspaceName, ['Table Name'] =DataType, ['IsBillable'] =IsBillable | distinct workspaceName, ['Table Name'] //Adding below the Solution column, i.e., where the table comes from Usage | where StartTime > ago(1d) | extend workspaceName = tostring(split(ResourceUri, "/")[-1]) | extend subscription = tostring(split(ResourceUri, "/")[2]) | extend resourceGroup = tostring(split(ResourceUri, "/")[4]) | summarize ['Table Size'] =sum(Quantity) / 1000 by TenantId, subscription, resourceGroup, workspaceName, ['Table Name'] =DataType, ['IsBillable'] =IsBillable, Solution | distinct workspaceName, ['Table Name'], Solution | sort by Solution asc
Explanation
This query retrieves information about tables from different workspaces in a tenant. It includes the table size, workspace name, table name, and whether the table is billable. The query also adds a column for the solution where the table comes from. The results are filtered to show data from the past 1 day and sorted by the solution in ascending order.
Details
Rod Trent
Released: August 15, 2022
Tables
Usage
Keywords
Usage,StartTime,ago,extend,workspaceName,tostring,split,ResourceUri,subscription,resourceGroup,summarize,Quantity,TenantId,DataType,IsBillable,distinct,sort
Operators
| whereagoextendtostringsplitsummarizesumbydistinctsort