What is cross-workspace query and how does it work? In this blog post, we will explore these questions and more, and show you how to use cross-workspace query in Azure Sentinel to enhance your security analysis and response. Before diving into the concept of cross-workspace KQL queries, let’s explore its significance through a scenario:
Enterprise organizations are now adopting the zero trust principle and they talk a lot about implementing the principle of least privilege. Cybersecurity is a crucial aspect of any modern enterprise, especially when dealing with big data. Big data refers to the large and complex datasets that are generated from various sources, such as Firewalls, Network devices, Servers, Endpoints, Applications, Cloud platforms, etc. Big data can provide valuable insights for business intelligence, but it also poses significant challenges for security analysts. How can they effectively monitor, analyze, and respond to the security threats and incidents that arise from big data?
One of the solutions that many enterprises adopt is Azure Sentinel which collects, stores, and processes security data from different sources. But depending on the size, scope, and complexity of the enterprise, there may be different requirements and preferences for how to manage security data. For example, enterprises are now adopting multiple log analytics workspaces in Azure Sentinel. The diagram attached below shows an example architecture for such use cases:
Central Monitoring — Cross workspace query
But how can security analysts access and query the security data from multiple log analytics workspaces in Azure Sentinel? And how can they do so without violating the principle of least privilege, which states that users should only have the minimum level of access and permissions necessary to perform their tasks? This is where the cross-workspace query feature comes in handy.
What is Cross-workspace query?
Cross-workspace query is a new feature that Microsoft introduced in Azure Sentinel to enable security analysts to run queries across multiple log analytics workspaces. This feature allows security analysts to gain more visibility and insights into the security data, as well as to perform cross-workspace correlation, aggregation, and comparison.
Cross workspace management — Microsoft
Query data across Log Analytics workspace
There are multiple ways to query data from multiple workspaces, applications, and resources. Explicitly by specifying the workspace, app, or resource information using the workspace(), app(), or resource() expressions. This article explains how to use the workspace() expressions to query data from multiple Log Analytics workspaces, applications, and resources.
Permissions required
Following permissions required to run cross-workspace KQL:
1- To query Log Analytics workspaces, ensure you have the Microsoft.OperationalInsights/workspaces/query/*/read permissions. These permissions are typically granted through roles like the Log Analytics Reader built-in role.
2- If you want to save a query, you'll need microsoft.operationalinsights/querypacks/queries/action permissions within the relevant query pack. The Log Analytics Contributor built-in role provides these permissions.
Limitations
Following are some limitations of cross-workspace KQL:
1- Querying across a large number of resources can substantially slow down the query.
2- You can include up to 100 Log Analytics workspaces or classic Application Insights resources in a single query.
3- Cross-resource and cross-service queries don't support parameterized functions and functions whose definition includes other cross-workspace or cross-service expressions, including adx(), arg(), resource(), workspace(), and app().
Let’s understand cross-workspace query with some examples:
Aggregate Security Alerts by Severity from different workspaces:
union workspace("workspace1").SecurityAlert,
workspace("workspace2").SecurityAlert,
workspace("workspace3").SecurityAlert
| summarize TotalAlerts = count() by Severity
Retrieve Top 10 IP Addresses with Highest Traffic Across Workspaces:
union workspace("workspace1").SecurityEvent,
workspace("workspace2").SecurityEvent,
workspace("workspace3").SecurityEvent
| where EventID == 4624
| summarize TotalEvents = count() by RemoteIP
| top 10 by TotalEvents desc
Check for Malicious File Downloads Across Workspaces:
union workspace("workspace1").FileDownloadEvents,
workspace("workspace2").FileDownloadEvents
workspace("workspace3").FileDownloadEvents
| where ActionType == "Downloaded"
| where FileName endswith ".exe" or
FileName endswith ".dll"
| project TimeGenerated, InitiatingUser, FileName, RemoteIP
Detect password spray attack across various workspaces:
let timeframe = 7d;
let threshold = 3;
union
SigninLogs
workspace("Region-A Workspace").SigninLogs,
workspace("Region-B Workspace").SigninLogs,
workspace("Region-C Workspace").SigninLogs,
workspace("Region-D Workspace").SigninLogs
| where TimeGenerated >= ago(timeframe)
| where ResultType == "50057"
| where ResultDescription =~ "User account is disabled. The account has been disabled by an administrator."
| summarize
StartTime = min(TimeGenerated),
EndTime = max(TimeGenerated),
count(),
applicationSet =
make_set(AppDisplayName),
ApplicationCount = dcount(AppDisplayName)
by UserPrincipalName, IPAddress
| where ApplicationCount >= threshold
Analysis of User Logon Failures across cross-workspace
union
workspace("Region-A Workspace").SecurityEvent,
workspace("Region-B Workspace").SecurityEvent,
workspace("Region-C Workspace").SecurityEvent,
workspace("Region-D Workspace").SecurityEvent
| where AccountType == 'User' and EventID == 4625
| extend Reason = case(
SubStatus == '0xc000005e', 'No logon servers available to service the logon request',
SubStatus == '0xc0000062', 'Account name is not properly formatted',
SubStatus == '0xc0000064', 'Account name does not exist',
SubStatus == '0xc000006a', 'Incorrect password', SubStatus == '0xc000006d', 'Bad user name or password',
SubStatus == '0xc000006f', 'User logon blocked by account restriction',
SubStatus == '0xc000006f', 'User logon outside of restricted logon hours',
SubStatus == '0xc0000070', 'User logon blocked by workstation restriction',
SubStatus == '0xc0000071', 'Password has expired',
SubStatus == '0xc0000072', 'Account is disabled',
SubStatus == '0xc0000133', 'Clocks between DC and other computer too far out of sync',
SubStatus == '0xc000015b', 'The user has not been granted the requested logon right at this machine',
SubStatus == '0xc0000193', 'Account has expirated',
SubStatus == '0xc0000224', 'User is required to change password at next logon',
SubStatus == '0xc0000234', 'Account is currently locked out',
strcat('Unknown reason substatus: ', SubStatus))
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), count() by Reason,Account
| extend timestamp = StartTimeUtc
Conclusion
In conclusion, cross-workspace KQL (Kusto Query Language) is valuable for security analysts. It allows for the aggregation and analysis of data across multiple workspaces, providing a comprehensive view of an organization’s security posture.
By mastering cross-workspace KQL, security analysts can enhance their threat detection and response capabilities, improve their understanding of the security landscape, and ultimately, better protect their organizations from cyber threats. As we’ve seen in the examples provided, whether it’s identifying high severity alerts, tracking failed sign-in attempts, or monitoring network communications, the possibilities with KQL are vast and impactful.
Remember, the key to effective security analysis is not just having data, but being able to understand and interpret that data to make informed decisions. And that’s where cross-workspace KQL truly shines.
Latest News
Every week we publish exclusive content on various topics.
