ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 95 • Advanced KQL

Using arg_max() in KQL.

The table had hundreds of rows for the same device.

The same user appeared again and again.

The same sender had multiple messages, multiple verdicts and multiple timestamps.

Agent Foskett did not need every row. He needed the latest meaningful row for each investigation entity.

Start Lesson 95 Back to Academy
Agent Foskett Academy lesson using arg_max in KQL for Microsoft Defender XDR investigations
Lesson overview

Learn how to use arg_max() to return the newest event for each device, user, sender, URL or process during Microsoft Defender XDR threat hunting.

Find the latest event per entity
Preserve useful columns
Summarise investigation state
Build cleaner hunting outputs

What arg_max() does

arg_max() returns the row that has the maximum value for an expression, usually the newest Timestamp, while keeping selected columns from that row.
Latest row per groupUse arg_max(Timestamp, *) to return the most recent event for each grouped entity.
Keeps row contextUnlike max(Timestamp), arg_max() can keep the columns from the row where that latest timestamp occurred.
Perfect for investigationsUse it when you need the newest status, newest action, newest logon, newest device event or newest email evidence.

The investigation pattern

Agent Foskett uses arg_max() when the question is not how many events happened, but what is the latest known state for each thing being investigated.
1. Choose the entityGroup by the thing you care about: DeviceName, AccountUpn, SenderFromAddress, RemoteUrl or SHA256.
2. Choose the latest fieldMost Defender XDR investigations use Timestamp as the value to maximise.
3. Choose columns to keepUse * for all columns during exploration, then project the useful fields for a cleaner final query.
4. Validate the resultCheck that each returned row really represents the latest useful evidence for that entity.
5. Add contextJoin or extend only after the latest event set is reduced and understood.
6. Explain the stateUse the output to describe the current or most recent known behaviour clearly.

Step 1 — Latest event per device

This query returns the latest process event seen for each device in the last 24 hours.
step-1-latest-event-per-device.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
DeviceProcessEvents
| where Timestamp > ago(24h)
| summarize arg_max(Timestamp, *) by DeviceName
| project Timestamp,
          DeviceName,
          AccountName,
          FileName,
          ProcessCommandLine
| order by Timestamp desc

Step 2 — Latest PowerShell activity per device

Filter first, then use arg_max() to keep only the newest PowerShell event per device.
step-2-latest-powershell-per-device.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ "powershell.exe"
| summarize arg_max(Timestamp, *) by DeviceName
| project Timestamp,
          DeviceName,
          AccountName,
          FileName,
          ProcessCommandLine,
          InitiatingProcessFileName
| order by Timestamp desc

Step 3 — Latest email verdict per sender

When one sender sends many messages, arg_max() helps identify the most recent message and its latest Defender email evidence.
step-3-latest-email-verdict-per-sender.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
EmailEvents
| where Timestamp > ago(7d)
| where isnotempty(SenderFromAddress)
| summarize arg_max(Timestamp, *) by SenderFromAddress
| project Timestamp,
          SenderFromAddress,
          SenderMailFromAddress,
          RecipientEmailAddress,
          Subject,
          ThreatTypes,
          DeliveryAction,
          DeliveryLocation
| order by Timestamp desc

Step 4 — Latest URL click per user

Use arg_max() to find the most recent URL click for each user during a phishing investigation.
step-4-latest-url-click-per-user.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
UrlClickEvents
| where Timestamp > ago(7d)
| where isnotempty(AccountUpn)
| summarize arg_max(Timestamp, *) by AccountUpn
| project Timestamp,
          AccountUpn,
          Url,
          ActionType,
          Workload,
          NetworkMessageId
| order by Timestamp desc

Step 5 — Latest file event per hash

When investigating a suspicious file hash, arg_max() can show the newest file activity seen for each SHA256 value.
step-5-latest-file-event-per-hash.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
DeviceFileEvents
| where Timestamp > ago(30d)
| where isnotempty(SHA256)
| summarize arg_max(Timestamp, *) by SHA256
| project Timestamp,
          SHA256,
          FileName,
          FolderPath,
          DeviceName,
          ActionType,
          InitiatingProcessFileName
| order by Timestamp desc

Step 6 — Latest logon result per user

This query helps identify the most recent identity logon evidence for each user.
step-6-latest-logon-result-per-user.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
IdentityLogonEvents
| where Timestamp > ago(7d)
| where isnotempty(AccountUpn)
| summarize arg_max(Timestamp, *) by AccountUpn
| project Timestamp,
          AccountUpn,
          ActionType,
          LogonType,
          DeviceName,
          IPAddress,
          FailureReason
| order by Timestamp desc

Step 7 — Latest event after grouping by more than one field

arg_max() becomes even more useful when grouping by multiple investigation fields, such as device and process name.
step-7-latest-event-by-device-and-process.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "mshta.exe")
| summarize arg_max(Timestamp, *) by DeviceName, FileName
| project Timestamp,
          DeviceName,
          FileName,
          AccountName,
          ProcessCommandLine,
          InitiatingProcessFileName
| order by DeviceName asc, Timestamp desc

arg_max() patterns to remember

Use Timestamp most oftenIn Defender XDR, Timestamp is usually the field you maximise to find the newest evidence.
Group by the investigation entityThe by clause controls whether you get the latest row per user, device, sender, hash, URL or process.
Use * while exploringarg_max(Timestamp, *) is useful while learning the data, but final queries should usually project only useful columns.
Filter before summarisingReduce the dataset with where before using summarize arg_max().
Do not confuse latest with worstThe newest event is not always the most suspicious event. It is the latest known row for that group.
Make the output explainableProject and rename fields so the result makes sense to another analyst.

Real-world investigation

The repeated senderA phishing sender appears dozens of times in EmailEvents across several recipients.
The questionThe analyst needs to know the latest message, latest delivery action and latest location for that sender.
The mistakeUsing max(Timestamp) only shows the newest time, but not the subject, recipient or delivery details from that event.
The fixAgent Foskett uses summarize arg_max(Timestamp, *) by SenderFromAddress to keep the newest row and its context.
The resultThe output shows the latest subject, recipient, ThreatTypes, DeliveryAction and DeliveryLocation for each sender.
The lessonWhen the row matters, use arg_max(). When only the value matters, max() may be enough.

Investigation checklist

Know the entityDecide whether the investigation is centred on a user, device, URL, sender, hash or process.
Filter the time rangeStart with a sensible Timestamp window before summarising.
Filter the signalSearch for the activity you care about before selecting the latest row.
Use arg_max()Return the newest row for each grouped entity.
Project the resultKeep the fields that explain the evidence clearly.
Compare with raw eventsValidate suspicious findings by reviewing the underlying rows when needed.

Related Agent Foskett Academy lessons

Counting and Grouping with summarizeUnderstand the foundation that arg_max() builds on.
Optimising KQL Queries for PerformanceFilter early and keep advanced summaries efficient.
Using let StatementsStore reusable entity sets and query blocks for cleaner hunts.
Advanced join Techniques in KQLCombine latest-event evidence with other Defender XDR tables.
Building an Incident TimelineUse latest events alongside timeline reconstruction.
EmailEvents KQL GuideApply arg_max() to sender, recipient and delivery investigations.

Coming next

Lesson 96 — Using make_set() in KQLNext, Agent Foskett Academy explores how make_set() helps defenders collect unique values such as devices, users, URLs, senders and file names into investigation summaries.
Why this mattersAfter finding the latest event with arg_max(), defenders often need to collect the surrounding evidence into a compact summary.

Final thought

Sometimes the latest row tells the story better than every row.
Agent Foskett mindsetUse arg_max() when the investigation needs the most recent known state for each entity.
Context beats timestamp alonemax(Timestamp) tells you when. arg_max(Timestamp, *) helps show what happened at that time.
Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD
ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026