Forum Discussion
(KQL) Searching for all tables of a customer for MSSPs
Hi guys, I need/want to the number of records in each table (datatype) of a customer (accessed via delegation/lighthouse). So, I would like to perform a search * but restrict it to a specific workspace. The following KQL searchs for the tables in the current workspace (not in a customer's workspaces).
search * | summarize count() by $table |sort by count_
* Note: I didn't find the documentation for the workspace() function (https://docs.microsoft.com/en-us/search/?scope=kusto&terms=workspace
I imagine something like the following (but of course it doesn't work)
search in (workspace("ABC").*) and *| summarize count() by $table
A folloow-up question is on how to restrict the time to the last month (like where TimeGenerated >ago(30d)))
Is there anyone with super master of KQL skills to help me? or forwarding this to the dev team?
This is easy in a Workbook (it handles the complex part), however these queries should be used infrequently as they are resource heavy - its far better to query a small set of known tables where possible. Query across resources with Azure Monitor - Azure Monitor | Microsoft Docs
If you need to have a friendly name, you can look at my Workbook that maps the Workspace ID to the name here:KQLpublic/KQL/Workbooks/Azure Sentinel Central at master · CliveW-MSFT/KQLpublic (github.com)
Tip: Clone the report called "Count of Security Incidents for selected Workspaces and Severity" and change the query to this (I suggest you keep the TimeRange parameter in the workbook to as few days as possible as well):union * // Get the Workspace Name(s) from a parameter | extend stringtoSplit = split("{WorkspaceIDguid}",",") | mv-expand stringtoSplit | where stringtoSplit has TenantId | extend workSpacename = trim(@"[^\w]+",tostring(split(stringtoSplit,":").[1])) // end of get workspace name section | summarize count() by TableName = Type, workSpacename | order by workSpacename asc, count_ desc
result:
KQL Timerange examples are here: How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - Microsoft Tech Community Look for startofmonth()
4 Replies
This is easy in a Workbook (it handles the complex part), however these queries should be used infrequently as they are resource heavy - its far better to query a small set of known tables where possible. Query across resources with Azure Monitor - Azure Monitor | Microsoft Docs
If you need to have a friendly name, you can look at my Workbook that maps the Workspace ID to the name here:KQLpublic/KQL/Workbooks/Azure Sentinel Central at master · CliveW-MSFT/KQLpublic (github.com)
Tip: Clone the report called "Count of Security Incidents for selected Workspaces and Severity" and change the query to this (I suggest you keep the TimeRange parameter in the workbook to as few days as possible as well):union * // Get the Workspace Name(s) from a parameter | extend stringtoSplit = split("{WorkspaceIDguid}",",") | mv-expand stringtoSplit | where stringtoSplit has TenantId | extend workSpacename = trim(@"[^\w]+",tostring(split(stringtoSplit,":").[1])) // end of get workspace name section | summarize count() by TableName = Type, workSpacename | order by workSpacename asc, count_ desc
result:
KQL Timerange examples are here: How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - Microsoft Tech Community Look for startofmonth()Excellent answer CliveWatson
I'm using this in a Jupyter notebook for creating customized reports for our customers.
I still have one question that is: how to call a workspace from a string variable. For example:
let variableName = 'workspaceNameX';
workspace(variableName).TableY
Unfortunately, workspace() doesn't accept the string. Do you know how could I do in a different way>
- You could look at using a Function to hold your list of workspaces?
https://docs.microsoft.com/en-us/azure/azure-monitor/logs/cross-workspace-query#using-cross-resource-query-for-multiple-resources
