Jan 29 2021 01:46 AM
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?
Feb 19 2021 04:01 AM
Solution
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) - M... Look for startofmonth()
Feb 22 2021 12:35 AM
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>
Feb 22 2021 12:46 AM
Feb 22 2021 12:48 AM
thanks for your fast @CliveWatson I will try it today
Feb 19 2021 04:01 AM
Solution
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) - M... Look for startofmonth()