Azure Log Analytics查询：对比当日与上周同时段记录数

Hey there, let's walk through what's not working in your current query and get it sorted for you!

First, here are the key issues with your existing code:

• Time range logic is off: When you use TimeGenerated > now(-7d), you're pulling all data from the last 7 days up to now—not just the same 1-hour window as today. We need to target exactly the same hour, 7 days ago.
• Join field mismatch: Your left query projects responseCode, but the right one uses responseCode_d—these don't match, so the join will never return results.
• Unnecessary grouping: Since you're already filtering for responseCode_d == 200, grouping by responseCode doesn't add any value (you'll only get one row anyway).
• Count can be simplified: count(responseCode) works, but count() is cleaner when you just need the total number of records.

Here's the corrected query that does exactly what you need: #

// Calculate today's last hour of 200 responses
let current_hour = 
    Table1
    | where TimeGenerated between (ago(1h) .. now())
    | where responseCode_d == 200
    | summarize CurrentCount = count();
// Calculate the SAME hour, 7 days ago
let last_week_same_hour = 
    Table1
    | where TimeGenerated between (ago(7d) - 1h .. ago(7d))
    | where responseCode_d == 200
    | summarize LastWeekCount = count();
// Combine both results into a single, easy-to-read output
current_hour 
| join kind=fullouter last_week_same_hour on $left.empty = $right.empty
| project 
    CurrentHour_200_Count = coalesce(CurrentCount, 0),
    LastWeekSameHour_200_Count = coalesce(LastWeekCount, 0)

A quick breakdown of the changes: #

• Used let statements to split the query into logical chunks—this makes it way easier to read and modify later.
• between (start .. end) ensures we're targeting an exact 1-hour window, which is more precise than just TimeGenerated > ago(1h).
• fullouter join guarantees we'll see results even if one of the windows has zero 200 responses (we use coalesce to turn nulls into 0 for clarity).
• Removed redundant grouping since we only care about the total count for the filtered response code.

If you ever want to compare multiple response codes at once (not just 200), you can adjust it to group by responseCode_d in both blocks, like this:

let current_hour = 
    Table1
    | where TimeGenerated between (ago(1h) .. now())
    | summarize CurrentCount = count() by responseCode_d;
let last_week_same_hour = 
    Table1
    | where TimeGenerated between (ago(7d) - 1h .. ago(7d))
    | summarize LastWeekCount = count() by responseCode_d;
current_hour 
| join kind=inner last_week_same_hour on responseCode_d
| project 
    ResponseCode = responseCode_d,
    CurrentHour_Count = CurrentCount,
    LastWeekSameHour_Count = LastWeekCount

内容的提问来源于stack exchange，提问作者vinoth