Agent Foskett Academy • Lesson 96 • Advanced KQL

Using make_set() in KQL.

The alert was only one row.

But the investigation had many users, devices, IP addresses and domains.

Agent Foskett did not need every duplicate event.

He needed the unique evidence set.

Start Lesson 96 Back to Academy

Agent Foskett Academy lesson using make_set in KQL for Microsoft Defender XDR investigations

Lesson overview

Learn how to use make_set() with summarize to create unique lists of investigation values, helping defenders understand scope, affected entities and repeated activity across Microsoft Defender XDR telemetry.

Build unique evidence lists

Summarise users, devices and IPs

Compare make_set() and make_list()

Improve investigation clarity

Why make_set() matters

During an investigation, defenders often need to know which unique users, devices, IP addresses, URLs or files were involved. make_set() helps turn repeated rows into a concise evidence summary.

Duplicates hide the storyRaw telemetry often repeats the same users, processes or indicators many times. make_set() helps remove duplicates so the investigation view becomes clearer.

Scope becomes easier to explainA unique set of devices, users or IP addresses helps defenders describe the size and impact of suspicious activity.

Summaries support decisionsWhen make_set() is combined with count(), dcount() and summarize, analysts can quickly separate isolated activity from wider patterns.

The make_set() investigation workflow

Agent Foskett uses make_set() when he wants a compact list of unique values instead of hundreds or thousands of repeated rows.

1. Choose the entityDecide whether the investigation needs unique users, devices, IP addresses, URLs, domains, file names or process names.

2. Filter the dataReduce the query to the timeframe and event types that matter before building the set.

3. Group with summarizeUse summarize to group the data by the parent entity, such as account, device, sender, domain or alert.

4. Add make_set()Use make_set(ColumnName, limit) to collect unique values into a dynamic array.

5. Add countsCombine make_set() with count() and dcount() so the output shows both volume and unique spread.

6. Keep the output readableLimit the set size and project only the fields that help explain the investigation.

Step 1 — Build a unique process list per device

Start with a simple endpoint example. This query shows which unique processes appeared on each device in the last 24 hours.

DeviceProcessEvents
| where Timestamp > ago(24h)
| summarize EventCount = count(),
            UniqueProcesses = make_set(FileName, 50)
      by DeviceName
| order by EventCount desc

Step 2 — Summarise remote IPs per device

Use make_set() to see the unique remote IP addresses contacted by each device during network activity.

DeviceNetworkEvents
| where Timestamp > ago(24h)
| where isnotempty(RemoteIP)
| summarize ConnectionCount = count(),
            UniqueRemoteIPs = dcount(RemoteIP),
            RemoteIPSet = make_set(RemoteIP, 50)
      by DeviceName
| order by UniqueRemoteIPs desc

Step 3 — Create a URL set for suspicious email senders

Email investigations often need a quick summary of which URLs were associated with a sender.

EmailUrlInfo
| where Timestamp > ago(7d)
| where isnotempty(Url)
| summarize UrlCount = count(),
            UniqueUrls = dcount(Url),
            UrlSet = make_set(Url, 25)
      by SenderFromAddress
| where UniqueUrls > 3
| order by UniqueUrls desc

Step 4 — Compare make_set() with make_list()

make_set() returns unique values. make_list() keeps repeated values. That difference matters when duplicates are important.

DeviceLogonEvents
| where Timestamp > ago(24h)
| where ActionType == "LogonSuccess"
| summarize UniqueDevices = make_set(DeviceName, 20),
            DeviceSequence = make_list(DeviceName, 20),
            LogonCount = count()
      by AccountName
| order by LogonCount desc

Step 5 — Build an IOC summary

When investigating indicators of compromise, make_set() can summarise where each indicator appeared.

let IOCs = dynamic(["evil.example", "malicious.example", "203.0.113.10"]);
DeviceNetworkEvents
| where Timestamp > ago(7d)
| where RemoteUrl has_any (IOCs) or RemoteIP in (IOCs)
| summarize SeenOnDevices = make_set(DeviceName, 50),
            SeenByAccounts = make_set(InitiatingProcessAccountUpn, 50),
            FirstSeen = min(Timestamp),
            LastSeen = max(Timestamp),
            EventCount = count()
      by RemoteUrl,
         RemoteIP
| order by LastSeen desc

Step 6 — Summarise activity by account

This example gives defenders a compact account-centric view of devices, processes and remote destinations.

DeviceNetworkEvents
| where Timestamp > ago(24h)
| where isnotempty(InitiatingProcessAccountUpn)
| summarize Devices = make_set(DeviceName, 20),
            Processes = make_set(InitiatingProcessFileName, 30),
            RemoteUrls = make_set(RemoteUrl, 30),
            RemoteIPs = make_set(RemoteIP, 30),
            EventCount = count()
      by InitiatingProcessAccountUpn
| order by EventCount desc

make_set() patterns to remember

Use it with summarizemake_set() is normally used inside summarize so values can be collected for each group.

Set a sensible limitUse the optional limit parameter so the output stays readable during investigations.

Pair it with dcount()dcount() tells you how many unique values exist, while make_set() shows examples of those values.

Use it for scopemake_set() is excellent for listing affected users, devices, IPs, URLs and file names.

Do not overfill the outputVery large sets can become hard to read. Summarise first, then drill into details when needed.

Remember the output is dynamicThe returned set is a dynamic array, which can be expanded later with mv-expand if deeper analysis is needed.

Real-world investigation

The suspicious domainA domain appears in DeviceNetworkEvents, but the first query returns hundreds of repeated connection rows.

The scope questionThe analyst needs to know which devices, accounts and processes were involved without reading every repeated event.

The make_set() pivotAgent Foskett groups the activity by RemoteUrl and uses make_set() to list unique devices, accounts and process names.

The count comparisonHe adds count() and dcount() so the investigation shows both total activity and unique spread.

The outcomeThe final output shows that one domain was contacted by multiple devices using the same initiating process.

The lessonmake_set() turns noisy rows into a clear investigation summary that helps defenders explain scope quickly.

Investigation checklist

Choose the right groupingGroup by the entity that explains the investigation, such as RemoteUrl, AccountName, DeviceName or SenderFromAddress.

Filter before summarisingKeep the data focused before building sets, especially across large Defender XDR tables.

Use count and dcount togetherShow both event volume and unique spread so the result is easier to interpret.

Limit large setsUse make_set(ColumnName, 20) or another sensible limit to avoid oversized output.

Project clean columnsRemove raw noise and present the final fields that support the investigation story.

Expand only when neededUse mv-expand later if the unique set needs to become separate rows for follow-up hunting.

Related Agent Foskett Academy lessons

Using arg_max() in KQLFind the latest event for each user, device or investigation entity.

Counting and Grouping with summarizeReview the foundation of grouping and summarising KQL results.

Working with Dynamic Data and JSONUnderstand dynamic arrays and JSON values that often appear in advanced KQL output.

Expanding Multi-Value Data with mv-expandExpand dynamic arrays into separate rows for deeper investigation.

Using let StatementsBuild cleaner reusable query blocks for advanced hunting investigations.

Building an IOC HuntUse sets of domains, IPs and URLs to hunt for indicators across Defender XDR.

Coming next

Lesson 97 — Expanding Multi-Value Data with mv-expandNext, Agent Foskett Academy explores how defenders use mv-expand to turn dynamic arrays and multi-value fields into individual rows for deeper investigation.

Why this mattersmake_set() creates useful arrays of unique values. mv-expand helps defenders open those arrays back up when each value needs to be investigated separately.

Final thought

The investigation did not need more rows. It needed a clearer set of evidence.

Agent Foskett mindsetWhen telemetry becomes noisy, summarise the unique evidence before drilling into every detail.

Unique values tell the scopeUsers, devices, URLs, IP addresses and process names become easier to understand when duplicates are removed.

Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Using make_set() in KQL

Agent Foskett Academy Lesson 96 teaches defenders how to use make_set() in KQL to build unique lists of users, devices, IP addresses, URLs, domains and indicators during Microsoft Defender XDR investigations.

Learn make_set() for Microsoft Defender XDR

The KQL make_set() aggregation function helps analysts summarise repeated telemetry into unique evidence sets that explain investigation scope across Defender XDR hunting tables.

KQL make_set() tutorial for Microsoft security analysts

This lesson explains how to combine make_set(), summarize, count(), dcount() and dynamic arrays to create clear threat hunting summaries for Microsoft security operations.