Agent Foskett Academy • Lesson 9 • Finding Unique Values with distinct

Finding Unique Values with distinct

Thousands of rows.
The same IP repeated again and again.
The same user.
The same device.

Then one value appeared that didn’t belong.

The distinct operator helps defenders remove repetition and focus on what is actually unique inside Microsoft security telemetry.

In this lesson, you will learn how to use distinct in KQL to uncover unique users, IP addresses, domains, devices and suspicious investigation pivots.

Start Lesson 9 Back to Academy

Agent Foskett Academy lesson explaining how to find unique values in KQL with distinct

Lesson overview

Learn how to use distinct to reduce repeated rows and identify unique evidence across Microsoft Defender XDR and Sentinel telemetry.

Finding unique users and devices

Reducing repeated telemetry rows

Building investigation pivot lists

Why distinct matters

Security tables can contain thousands or millions of rows. During an investigation, you may not need every repeated event at first.

Sometimes you need a clean list of the unique values involved.

The distinct operator returns only the unique combinations of the columns you choose.

Reduce noiseRemove repeated rows so the important unique values are easier to review.

Create pivot listsBuild lists of unique users, devices, IPs, senders, domains or processes to investigate further.

Find what changedUnique values help defenders spot unusual infrastructure, unexpected accounts and suspicious entities.

The basic distinct pattern

The simplest distinct query chooses one column and returns each unique value only once.

SigninLogs
| where TimeGenerated > ago(24h)
| distinct UserPrincipalName

Plain-English translation:

Show me each user who appeared in sign-in logs during the last 24 hours, but only list each user once.

Understanding distinct

distinct does not count results. It removes duplicates from the selected columns and returns the unique values.

One columndistinct UserPrincipalName returns a clean list of unique users.

Multiple columnsdistinct UserPrincipalName, IPAddress returns unique user and IP combinations.

No event countIf you need counts, use summarize. If you need unique values, use distinct.

Finding unique IP addresses

Unique IP addresses are useful during sign-in investigations, impossible travel reviews, suspicious session analysis and Conditional Access troubleshooting.

SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName contains "@contoso.com"
| distinct IPAddress

Plain-English translation:

Show me the unique IP addresses used by Contoso users over the last 7 days.

Finding unique user and IP combinations

Sometimes one unique column is not enough. A single IP address may be used by many users, or a single user may sign in from many IP addresses.

Using distinct with multiple columns shows the unique combinations.

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| distinct UserPrincipalName, IPAddress, Location

UserShows which account signed in.

IP addressShows the network source used during the sign-in.

LocationAdds context that can help identify suspicious or unexpected sign-in geography.

Finding unique email senders

distinct is very useful for email investigations. Instead of reviewing every email row, defenders can quickly list the unique senders involved in a campaign.

EmailEvents
| where Timestamp > ago(24h)
| where Subject contains "invoice"
| distinct SenderFromAddress, SenderFromDomain

Plain-English translation:

Find the unique senders and sender domains involved in invoice-related emails over the last 24 hours.

Finding unique devices involved in endpoint activity

During endpoint investigations, repeated process rows can make it difficult to see which devices are actually involved. distinct can quickly produce a clean device list.

DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| distinct DeviceName

Device shortlistQuickly identify which devices have PowerShell activity.

Pivot pointUse the device list to drill into command lines, parent processes and user context.

Noise reductionHundreds of process events become a short list of affected endpoints.

Combining where, project, distinct and order by

In real investigations, distinct is often used after filtering and projecting only the useful columns.

EmailEvents
| where Timestamp > ago(7d)
| where DeliveryAction != "Blocked"
| project SenderFromDomain, SenderIPv4, Subject
| distinct SenderFromDomain, SenderIPv4
| order by SenderFromDomain asc

Plain-English translation:

Find unique sender domain and sender IP combinations for delivered email over the last 7 days.

distinct vs summarize

distinct and summarize can both reduce results, but they answer different investigation questions.

Use distinct when asking:Which unique users, IPs, devices, domains or senders are present?

Use summarize when asking:How many times did each user, IP, device, domain or sender appear?

Investigation tipStart with distinct for a clean pivot list, then use summarize when you need volume and ranking.

Beginner mistakes with distinct

distinct is easy to use, but small mistakes can make the results confusing.

Selecting too many columnsThe more columns you include, the more unique combinations you create.

Expecting countsdistinct does not count how often something appears. Use summarize count() for that.

Skipping filtersFilter first with where so your unique list focuses on the investigation scope.

Investigator mindset

When everything repeats, defenders need a way to step back and ask a simpler question.

What users are involved?

What IP addresses appeared?

What domains, devices or senders are unique?

distinct helps turn noisy telemetry into a clean investigation map.

What values are unique?Start by identifying the distinct users, devices, IPs, domains or senders involved.

What looks unexpected?Review the unique list for unfamiliar infrastructure, unusual locations or unexpected devices.

What should I pivot into next?Use the unique values as starting points for deeper KQL hunting.

What you learned

In this lesson, you learned how distinct helps defenders remove repetition and find unique values inside Microsoft security telemetry.

distinct removes duplicatesIt returns unique values or unique combinations from the columns you choose.

unique values become pivotsUsers, IP addresses, devices, domains and senders can become the next step in an investigation.

distinct is not countingUse distinct to find unique values. Use summarize count() when you need volume.

Next lesson coming soon

The next Agent Foskett Academy lesson will introduce top, helping defenders quickly identify the highest-volume users, devices, IP addresses, senders and suspicious activity across Microsoft security telemetry.

Lesson 10 — Using top to Find High-Volume Activity Learn how top helps defenders quickly surface the busiest accounts, IP addresses, devices, senders and domains.

Keep building the investigation After finding unique values, the next step is learning how to rank the most active or suspicious values.

Continue learning with Agent Foskett Academy, Microsoft Defender KQL Threat Hunting Guide, EmailEvents KQL Guide, Finding Time Patterns with bin(), Microsoft Security and the GEMXIT Security Review.

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