Query Details
Subscription Inventory Logs Azure Subscription Modification
Query
let query_frequency = 1h;
let query_period = 2d;
let ingestion_period = 1d;
union
(
SubscriptionInventoryLogs
| where TimeGenerated > ago(query_frequency)
| summarize arg_max(TimeGenerated, *) by SubscriptionId
),
(
SubscriptionInventoryLogs
| where TimeGenerated between (ago(query_period) .. ago(query_frequency))
| summarize arg_max(TimeGenerated, *) by SubscriptionId
)
| summarize
minTimeGenerated = arg_min(TimeGenerated, State),
maxTimeGenerated = arg_max(TimeGenerated, *)
by SubscriptionId
| project-rename PreviousState = State, CurrentState = State1
| extend AlertName = case(
minTimeGenerated > ago(query_frequency), strcat("Azure subscription created", " - ", ResourceName),
maxTimeGenerated < ago(ingestion_period), strcat("Azure subscription removed", " - ", ResourceName),
isnotempty(PreviousState) and isnotempty(CurrentState) and CurrentState != PreviousState, strcat("Azure subscription ", tolower(CurrentState), " - ", ResourceName),
""
)
| where isnotempty(AlertName)
| project
TimeGenerated = maxTimeGenerated,
ResourceName,
SubscriptionId,
PreviousState,
CurrentState,
ResourceId,
AlertName
Explanation
The query retrieves information about Azure subscription changes and generates alerts based on different conditions. It first retrieves the latest log for each subscription where the log was generated within the last hour. Then, it retrieves the latest log for each subscription within the last 2 days but excluding the logs from the last hour. The results from both queries are combined.
Next, it summarizes the data by finding the earliest and latest timestamps for each subscription and assigns them to minTimeGenerated and maxTimeGenerated respectively. It also renames the State column to PreviousState and State1 column to CurrentState.
Then, it extends the query by adding an AlertName column based on different conditions. If the minTimeGenerated is greater than the last hour, it creates an alert for a newly created Azure subscription. If the maxTimeGenerated is older than the last day, it creates an alert for a removed Azure subscription. If both PreviousState and CurrentState are not empty and they are different, it creates an alert for a changed Azure subscription.
Afterwards, it filters out any rows where the AlertName is empty. Finally, it selects and renames the relevant columns: TimeGenerated, ResourceName, SubscriptionId, PreviousState, CurrentState, ResourceId, and AlertName.
Details
Jose Sebastián Canós
Released: September 7, 2023
Tables
Keywords
Operators