Audit New Operations
Query
//Find any new operations generated in the Azure AD audit table in the last two weeks compared to the last 180 days, you can adjust the time periods
//e.g. change 180d to 90d and 14d to 7d would find new events in the last week not seen in the 90 prior to that
//Data connector required for this query - Azure Active Directory - Audit Logs
AuditLogs
| where TimeGenerated > ago (180d) and TimeGenerated < ago(14d)
| distinct OperationName, LoggedByService
| join kind=rightanti(
AuditLogs
| where TimeGenerated > ago(14d)
| summarize TotalCount=count(), FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated) by OperationName, LoggedByService
)
on OperationName, LoggedByServiceExplanation
This query is designed to identify any new operations that have appeared in the Azure Active Directory (Azure AD) audit logs within the last two weeks, which were not present in the logs from the preceding 180 days. Here's a simplified breakdown of what the query does:
-
Select Historical Data: It first looks at the audit logs from the period between 180 days ago and 14 days ago. It extracts distinct operation names and the services that logged them during this timeframe.
-
Identify Recent Data: It then examines the audit logs from the last 14 days, summarizing the operations by counting how many times each operation occurred and noting the first and last time each operation was seen.
-
Compare and Find New Operations: The query uses a "right anti join" to compare the two sets of data. This join type returns only the operations from the last 14 days that do not have a match in the historical data set. Essentially, it filters out any operations that were already present in the earlier 180-day period, leaving only the new operations that appeared in the last two weeks.
In summary, this query helps you identify new or unusual operations in your Azure AD audit logs that have occurred recently but were not seen in the past several months. You can adjust the time periods to suit your monitoring needs.
Details
Matt Zorich
Released: March 11, 2024
Tables
Keywords
Operators