LindenSec's KQL CTF #1: The Phantom Admin Login

Welcome to the first Lindensec KQL CTF. You’re the analyst on shift when Microsoft Defender flags a suspicious login to an admin account following a password spray.

Your mission: unravel what happened using KQL.

This CTF is hands-on, you’ll ingest fake (but realistic) datasets into the free Azure Data Explorer cluster and investigate like a real SOC analyst.

Step 1 – Load the Data

All datasets live on GitHub, please download these locally: KQL-CTF-Series/kql_ctf1_datasets at main · LindenSec/KQL-CTF-Series

Files included:

• SigninLogs.csv

• DeviceProcessEvents.csv

• CommonSecurityLog.csv
  

Ingest into the free ADX cluster

1. Go to Azure Data Explorer Free Cluster.

2. Click Sign in and create a cluster.
   

   

3. Click on Query on the left-hand side.

4. Run these commands to create tables:

.create table SigninLogs (TimeGenerated: datetime, UserPrincipalName: string, IPAddress: string, ResultType: string, Role: string)

.create table DeviceProcessEvents (TimeGenerated: datetime, DeviceName: string, AccountName: string, FileName: string, ProcessCommandLine: string, InitiatingProcessFileName: string)

.create table CommonSecurityLog (TimeGenerated: datetime, DeviceName: string, SourceIP: string, DestinationIP: string, DestinationHostName: string)

One by one until you see the tables on the left

5. In the left panel, right-click the table and select Get data.

6. Make sure you have the CSV files downloaded, then press on Local File.

7. Add the CSV to the table with the same name. Redo this for all tables
   

   

Verify with a quick query:

SigninLogs | take 5

Challenges

Challenge 1 – The Suspicious Login

Defender reported a foreign login attempt after a password spray. What is the IP?

• Your flag: the attacker’s IP address.

• Hint: This followed a password spray, look for failed sign-ins before a successful one!
  

Challenge 2 – Endpoint Activity

The compromised account logged into SRV-WEB01. Check what happened after.

• Your flag: the dropped file name.

• Hint: 💪🐚
  

Challenge 3 – Tracing Exfiltration

Minutes later, the server reached out to the internet. Which rare destination looks malicious?

• Your flag: the suspicious domain name.

• Hint: Which domain has only a single hit? What’s left after filtering the noise?
  

Bonus – Building a Detection

Combine your queries into a detection:

• Condition: password spray success on an admin → PowerShell activity → rare outbound domain.

• Flag: none, this is just practice turning hunts into detections.
  

Answers

⚠️ Stop here if you haven’t solved the challenges yet! ------------

Challenge 1 – Flag: 91.202.45.77

KQL hint:

SigninLogs
| summarize Failures = countif(ResultType != "0"),
            Success = countif(ResultType == "0")
  by UserPrincipalName, IPAddress
| where Failures > 5 and Success > 0

Challenge 2 – Flag: invoice_updater.exe

KQL hint:

DeviceProcessEvents
| where DeviceName == "SRV-WEB01"
| where InitiatingProcessFileName == "powershell.exe"
| project TimeGenerated, FileName, ProcessCommandLine

Challenge 3 – Flag: updates-checker[.]com

KQL hint:

CommonSecurityLog
| where DeviceName == "SRV-WEB01"
| summarize Hits = count() by DestinationHostName
| where Hits < 3

Closure

In this CTF you:

• Detected a password spray → compromise

• Investigated endpoint behavior with PowerShell and file drops

• Found the exfiltration domain hidden in noisy network logs

• Learned how to stitch everything into a detection
  

This is exactly how we hunt in real SOCs: pivoting between authentication, process, and network telemetry until the story unfolds.

What queries did you use? Do you want to see more, larger challenges like this? Let me know! I'd love to continue this series.

Datasets have been created with ChatGPT.