ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 30 • Adding Conditional Logic with iff()

Adding Conditional Logic with iff()

Some investigations only need a simple answer.
Was the sign-in successful or failed? Was the email delivered or blocked? Does this command line contain a suspicious pattern or not?

This is where iff() becomes useful. It allows defenders to create a new value based on a true-or-false condition.

In this Agent Foskett Academy lesson, you will learn how defenders use the KQL iff() function to create simple investigation labels, flag suspicious activity and make Microsoft Defender XDR and Microsoft Sentinel results easier to triage.

Start Lesson 30 Back to Academy
Agent Foskett Academy lesson explaining how to use iff in KQL investigations
Lesson overview

Learn how iff() helps defenders create simple true-or-false labels inside KQL investigation results.

Understand iff()
Create simple evidence flags
Triage results faster
🎯 iff() is best when the investigation has two possible outcomes.
Use it when you need a clear yes/no, suspicious/normal, failed/successful or review/no-review label.
Review Lesson 29 →

Why iff() matters

The iff() function evaluates a condition and returns one value when the condition is true and another value when the condition is false.

This makes it ideal for simple investigation labels where you do not need many categories.

If case() is useful for several possible labels, iff() is useful when the question is simple and direct.
Create simple labelsTurn a true-or-false condition into a readable evidence field that analysts can review quickly.
Flag suspicious activityMark events as suspicious, normal, needs review or already handled based on one clear condition.
Reduce investigation noiseMake large result sets easier to triage by adding simple decision fields to the output.

Investigation scenario

Agent Foskett is reviewing Defender XDR and Sentinel telemetry after a phishing alert, several failed sign-ins and suspicious PowerShell activity were reported.

The analyst does not need a complex scoring model yet. They need simple labels that answer clear investigation questions.

By using iff() with extend, each row can be marked with a useful true-or-false style label before the evidence is reviewed.

Step 1 — Flag delivered emails that need review

Start by creating a simple label that separates delivered emails from emails that were already handled by protection.
iff-flag-delivered-emails.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
EmailEvents
| where Timestamp > ago(7d)
| extend ReviewStatus = iff(DeliveryAction == "Delivered", "Needs review", "Already handled")
| project Timestamp, SenderFromAddress, RecipientEmailAddress, Subject, DeliveryAction, ReviewStatus
| order by Timestamp desc

Step 2 — Flag failed sign-ins

Use iff() to create a readable sign-in status from the raw result value in Sentinel sign-in telemetry.
iff-flag-failed-signins.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
SigninLogs
| where TimeGenerated > ago(7d)
| extend SignInStatus = iff(ResultType != "0", "Failed sign-in", "Successful sign-in")
| project TimeGenerated, UserPrincipalName, IPAddress, Location, ResultType, SignInStatus
| order by TimeGenerated desc

Step 3 — Flag risky PowerShell command lines

iff() is useful when one suspicious pattern should create an immediate review flag.
iff-flag-powershell-risk.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| extend PowerShellRisk = iff(ProcessCommandLine has_any ("EncodedCommand", "DownloadString"), "High risk command", "No obvious high-risk pattern")
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine, PowerShellRisk
| order by Timestamp desc

How iff() works

The iff() function has three parts: the condition, the value returned when the condition is true and the value returned when the condition is false.

It is short, readable and useful when the investigation decision only has two possible outcomes.
ConditionThe test that is checked, such as DeliveryAction == "Delivered" or ResultType != "0".
True resultThe label shown when the condition matches, such as Needs review or Failed sign-in.
False resultThe fallback label shown when the condition does not match.

Step 4 — Create a simple external sender flag

Use iff() to separate internal-looking senders from external senders during email triage.
iff-flag-external-senders.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
EmailEvents
| where Timestamp > ago(30d)
| extend SenderDomain = tostring(split(SenderFromAddress, "@")[1])
| extend SenderType = iff(SenderDomain endswith "gemxit.au", "Internal sender", "External sender")
| project Timestamp, SenderFromAddress, SenderDomain, RecipientEmailAddress, Subject, SenderType
| order by Timestamp desc

iff() compared with case()

Use iff() for two outcomesChoose iff() when the result is simple, such as suspicious or normal, failed or successful, internal or external.
Use case() for many outcomesChoose case() when you need several labels, risk levels or categories in one field.
Keep logic readableIf nested iff() statements become hard to read, switch to case() so the investigation logic is easier to explain.

Common investigation uses

Email triageFlag delivered phishing emails, external senders, suspicious subject lines or messages that need manual review.
Identity investigationsLabel sign-ins as failed or successful, risky or normal, managed or unmanaged, trusted or untrusted.
Endpoint huntingFlag command lines, file names and process activity that match a simple suspicious condition.

Common mistakes

Using iff() for too many outcomesNested iff() statements can become difficult to read. Use case() when the logic has several branches.
Returning unclear labelsUse labels that help the analyst understand the decision, not vague values such as yes, no, true or false.
Forgetting the false resultAlways make the fallback value meaningful so every row has useful investigation context.

What you learned

iff() creates simple logicYou learned how to create true-or-false style investigation labels using one clear condition.
Two outcomes work bestYou learned why iff() is ideal for simple decisions and why case() is better for multiple categories.
Flags improve triageYou learned how simple evidence flags make Defender XDR and Sentinel results easier to review.
Next lesson: Converting Values with tostring()
Now that you can build conditional labels, the next step is learning how to convert values so dynamic evidence can be searched, projected and explained cleanly.
Back to Academy →

Related Agent Foskett Academy lessons

Creating New Evidence Fields with extend Review how defenders create calculated fields before applying conditional logic.
Classifying Evidence with case() Review when case() is better for multiple investigation labels and risk categories.

Continue learning with Using has_any to Find Suspicious Text, KQL Threat Hunting Guide and Microsoft Security.

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

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026

Adding Conditional Logic with iff()

Agent Foskett Academy Lesson 30 teaches defenders how to use the KQL iff() function to create simple true-or-false investigation labels and triage Microsoft security telemetry.

Learn KQL iff() for Microsoft Defender XDR and Sentinel

This lesson explains how iff() supports Microsoft Defender XDR and Microsoft Sentinel investigations by creating readable flags for emails, sign-ins, endpoint commands and other security evidence.