This post is a walkthrough of the KC7 investigation case of KC7 HopsNStuff . You can use it as a helpful guide when encountering an obstacle or trying to understand a question. Different ways to answer questions might exist, so don’t be afraid to explore your path.

Section 2: đŸ„šInfo💰

Question 1: Let’s take a look at our SecurityAlerts. A security alert flagged on a file that was quarantined on March 31, 2023. Which host was this identified on?

The question provided us with the timestamp and flag reason.

SecurityAlerts
| where timestamp between (datetime(2023-03-31T00:00:00Z) .. datetime(2023-03-31T23:59:59Z))

Question 2: Which employee uses this device? (the one found in Question 2)

We must provide the employee name, and the Employees table contains the hostname. By searching for the hostname, we can identify the employee.

Employees
| where hostname contains "QBYQ-DESKTOP"
| project name

Question 3: How many unique hosts had this file on their system? (the file seen in the alert from question 1)

Reviewing the KQL results from question one, we identified the file name as Dev-Requirements.zip from the description filed. The question is asking for unique, so this informs us that we will use distinct.

FileCreationEvents
| where filename contains "Dev-Requirements.zip"
| distinct hostname
| count

Question 4: When was the earliest time this file was seen? (the file seen in the alert from question 1) Make sure to paste the exact timestamp from ADX

We added order by timestamp asc to find the earliest timestamp.

FileCreationEvents
| where filename contains "Dev-Requirements.zip"
| order by timestamp asc

Question 5: Investigate the earliest device that had this file. (the file seen in the alert from question 1) What’s the role of the employee who uses this host machine?

Since we have the device name, we can check the Employees table for the requested information.

Employees
| where hostname contains "FTVO-LAPTOP"
| project role

Just for Kqling!

let EmployeeRole = FileCreationEvents
| where filename contains "Dev-Requirements.zip"
| top 1 by timestamp asc
| project hostname;
Employees
| where hostname in (EmployeeRole)
| project role

Question 6: How many external emails did this employee (the one from question 5) receive based on the data you have?

Find the email address from the ‘Employees’ table and filter out all internal senders from hopsnstuff.com.

Employees
| where hostname contains "FTVO-LAPTOP"
| project email_addr

Email
| where recipient has "meghann_geisinsky@hopsnstuff.com"
| where sender !contains "hopsnstuff.com"
| count

Just for Kqling!

let EmployeeEmail = Employees
| where hostname contains "FTVO-LAPTOP"
| project email_addr;
Email
| where recipient in (EmployeeEmail) and sender !contains "hopsnstuff.com"
| count

Question 7: How many external emails were sent to the employees from the unique host machines identified in Question 3?

Let’s break this a part to easy explain how did we get the answer.

The question asks to find the number of emails sent from an external domain, not hopsnstuff.com, to the email addresses of the employees who have the file Dev-Requirements.zip created on their machines.

We have the list of host names, which we were able to resolve when answering question 3.

FileCreationEvents
| where filename == "Dev-Requirements.zip"
| distinct hostname
  • JCKA-LAPTOP
  • HVYC-DESKTOP
  • FTVO-LAPTOP
  • QBYQ-DESKTOP
  • AAGB-LAPTOP
  • DSKT-MACHINE
  • D8QS-MACHINE
  • UZ2E-LAPTOP
  • TEY9-LAPTOP

We could run the following KQL or a similar query to find the list of email addresses.

Employees
| where hostname has_any("JCKA-LAPTOP", "HVYC-DESKTOP", "FTVO-LAPTOP", 
"QBYQ-DESKTOP", "AAGB-LAPTOP", "DSKT-MACHINE", "D8QS-MACHINE", 
"UZ2E-LAPTOP", "TEY9-LAPTOP")
| project email_addr

or

Employees
| where hostname in ("JCKA-LAPTOP", "HVYC-DESKTOP", "FTVO-LAPTOP", "QBYQ-DESKTOP", "AAGB-LAPTOP", "DSKT-MACHINE", "D8QS-MACHINE", "UZ2E-LAPTOP", "TEY9-LAPTOP")
| project email_addr

Now we have the list of email addresses of the employees of the host names, which has the file Dev-Requirements.zip.

We can query to get the count and filter out the internal email communication from hopsnstuff.com.

Email
| where recipient in ("diane_bowlin@hopsnstuff.com",
"merle_hoener@hopsnstuff.com",
"barbara_rochford@hopsnstuff.com",
"michael_reynolds@hopsnstuff.com",
"george_gonzalez@hopsnstuff.com",
"meghann_geisinsky@hopsnstuff.com",
"john_clark@hopsnstuff.com",
"rosita_stevens@hopsnstuff.com",
"george_owens@hopsnstuff.com")
| where sender !endswith "@hopsnstuff.com"
| count

You are right. This query could be a better detection rule. However, it would only be suitable for answering one question. Let’s make it more efficient by using a let statement.

Using ’let’

Now we understand the task, so let’s use a ’let’ statement for efficiency.

let CompromisedHost = FileCreationEvents
| where filename == "Dev-Requirements.zip"
| project hostname;
let CompromisedEmail = Employees
| where hostname in (CompromisedHost)
| project email_addr;
Email
| where sender !endswith "@hopsnstuff.com"
| where recipient in (CompromisedEmail)

The beauty of KQL queries and data is that you can show the answer differently based on your understanding and experience. I was lucky to get the correct answer; all HopsNStuff employees use the domain hopsnstuff.com for email communications. However, we would have missed some results if the company had multiple domains. I highly recommend going over kellamity walkthrough. KC7 — Eggstravaganza walkthrough— Section 2 . In the walkthrough, kellamity filtered by all internal emails, and I thought that was a nice line in the query to provide a better accuracy rate for tenants with multiple domains.

Question 8: From the emails you’ve found in Question 7, what is the email address of the sender that sent the most recent email that was BLOCKED?

We will start with the same query from question 7 but add more lines to find the email address. By analyzing it,  we find the word BLOCKED in the verdict column. I like to use =~ to make it non-case-sensitive. To filter the results, we could use | order by timestamp desc or | top 1 by timestamp.

let CompromisedHost = FileCreationEvents
| where filename == "Dev-Requirements.zip"
| project hostname;
let CompromisedEmail = Employees
| where hostname in (CompromisedHost)
| project email_addr;
Email
| where sender !endswith "@hopsnstuff.com"
| where recipient in (CompromisedEmail)
| where verdict =~ "blocked"
//| order by timestamp desc
| top 1 by timestamp

Question 9: What country is the email provider from the sender (8) headquartered in?

If you don’t know the answer, use Hosting Checker or your favorite tool.

Question 10: Let’s investigate the file name that was flagged in Question 1. Look for this file in the emails you filtered for on Question 7. One of the emails marked SUSPICIOUS looks like it came from a partner company. What was the sender’s email address?

We are starting with the query from question 7 as the base. From the answer to question one, our file is Dev-Requirements.zip, which we will search for in the link ‘| where link contains “Dev-Requirements.zip”’. Then, from the question, we know that the email was marked as SUSPICIOUS, which we can filter by adding ‘| where verdict =~“SUSPICIOUS”’.

let CompromisedHost = FileCreationEvents
| where filename == "Dev-Requirements.zip"
| project hostname;
let CompromisedEmail = Employees
| where hostname in (CompromisedHost)
| project email_addr;
Email
| where sender !endswith "@hopsnstuff.com"
| where recipient in (CompromisedEmail)
| where link contains "Dev-Requirements.zip"
| where verdict =~"SUSPICIOUS"

Question 11: When was the email from question 10 sent?

You Got this đŸ‘Ÿ

Question 12: What is the email in the reply_to field in the email from question 10?

You Got this đŸ‘Ÿ

Based on our finding of the answer to question 10, we run the domain to the PassiveDns table.

PassiveDns
| where domain contains "development-module.com"

**

From what we found in answering question 11 we know the email address is catherine_phillips@ingredientswarehouse.com

let SuspiciousDomain = Email
| where sender == "catherine_phillips@ingredientswarehouse.com"
| where verdict == "SUSPICIOUS"
| project domain = parse_url(link).Host;
PassiveDns
| where domain in (SuspiciousDomain)

OutboundNetworkEvents
| where url contains "development-module.com"

Question 15: How many unique domains hosting the same file were clicked by employees?

OutboundNetworkEvents
| where url contains "Dev-Requirements.zip"
| extend SuspiciousUrl = tostring(parse_url(url).Host)
| distinct SuspiciousUrl
| count

Question 16: Let’s look at the emails from Question 10. Some of the emails mention a programming language. What is it?

You Got this đŸ‘Ÿ

Question 17: Let’s look for any references to the answer from Question 16 in running processes. What’s the full command line that references the programming language?

The question asks about a command line, and our hint is that it is running a process. By checking our tables, the ProcessEvents column has the name process_commandline.

ProcessEvents
| where process_commandline contains "python"

Question 18: How many unique hosts had this command line from the previous question?

Get the host names by running ‘distinct’

ProcessEvents
| where process_commandline contains "python"
| distinct hostname
| count

Question 19: Two of the Parent Process Names do not appear to be legitimate Windows executables. How many records in total have these Parent Process Names?

Every question contains the seed of its own answer; understanding the question is the first step to discovering the truth.

let suspiciousProcess =
ProcessEvents
| where process_commandline contains "Python"
| where parent_process_name != "powershell.exe"
| distinct parent_process_name;
ProcessEvents
| where parent_process_name in (suspiciousProcess)

KC7 — Eggstravaganza walkthrough— Section 2

Question 20: How many distinct hosts had these processes run on their systems?

Get the count of hostname by adding distinct.

let suspiciousProcess =
ProcessEvents
| where process_commandline contains "Python"
| where parent_process_name != "powershell.exe"
| distinct parent_process_name;
ProcessEvents
| where parent_process_name in (suspiciousProcess)
| distinct hostname

Question 21: When was the earliest time these processes were seen?

Filter the results or add | top 1 by timestamp asc.

let suspiciousProcess =
ProcessEvents
| where process_commandline contains "Python"
| where parent_process_name != "powershell.exe"
| distinct parent_process_name;
ProcessEvents
| where parent_process_name in (suspiciousProcess)
| top 1 by timestamp asc

Question 22: Look for where the file was first seen on a host. Where was this file located? Copy and paste the full path.

let suspiciousProcess =
ProcessEvents
| where process_commandline contains "Python"
| where parent_process_name != "powershell.exe"
| distinct parent_process_name;
let filePath =ProcessEvents
| where parent_process_name in (suspiciousProcess)
| distinct parent_process_name;
FileCreationEvents
| where filename in (filePath)
| top 1 by timestamp asc

Question 23: What is the SHA256 hash of this file?

You Got this đŸ‘Ÿ

Question 24: Search this hash on virustotal.com. What is the md5 hash for this file?

You Got this đŸ‘Ÿ

Question 25: Let’s look for the file in other places. What was the path for the file observed on March 13, 2023?

We know the file name is rabbitmq.exe and the question provided us with the timestamp. I like to use ’top 1 by [column] asc’ to narrow down the results.

FileCreationEvents
| where filename == "rabbitmq.exe"
| where timestamp >= datetime(2023-03-13)
| top 1 by timestamp asc

Question 26: Now let’s look at that path across all of the devices. We’ve already seen some files here from our previous investigation. Which file haven’t we seen before?

Run the following query to find the file in question. It is one of the files we haven’t seen before.

FileCreationEvents
| where path has "C:\\Users\\Public"
| project filename
| distinct filename

Question 27: Now that we have 3 file names, let’s go back to our process events and find all parent processes that use these files. How many total records are there?

Let’s build on our query from the answer to question 26 by adding it to a variable fileNames and check the ProcessEvents table for fileNames in the ‘parent_process_name’.

let fileNames = FileCreationEvents
| where path has "C:\\Users\\Public"
| project filename
| distinct filename;
ProcessEvents
| where parent_process_name in (fileNames)

Question 28: These executables seem to run powershell.exe on the command line and has some garbled text after it. After figuring out how to ungarble it, investigate what it’s doing. A file is referenced at the end. What is it?

I continued building one query by adding ‘| where process_name has “powershell.exe”’ to filter for processes run by powershell.exe and set a ‘| order by process_commandline’ for easy analysis.

let fileNames = FileCreationEvents
| where path has "C:\\Users\\Public"
| project filename
| distinct filename;
ProcessEvents
| where parent_process_name in (fileNames)
| where process_name has "powershell.exe"
| order by process_commandline

You can copy the command to your favorite AI chatbot to explain it ‘powershell.exe -Nop -ExecutionPolicy bypass -enc ’. Then, copy the “garbled text” or base64 to a base64 decoder. This time I used IT - Tools .

Question 29: This threat actor left the answer to a question among those processes. What’s the answer?

Let’s remove the | where process_name has "powershell.exe" to view all the processes. By analyzing the list of processes, we will locate another base64, which we can decode using IT - Tools and get our answer.

let fileNames = FileCreationEvents
| where path has "C:\\Users\\Public"
| project filename
| distinct filename;
ProcessEvents
| where parent_process_name in (fileNames)
//| where process_name has "powershell.exe"
| order by process_commandline
| distinct process_commandline

Question 30: How many unique IP addresses did this attacker use to communicate with the infected hosts?

I worked with Kusto GPT By Chibi Vikramathithan to create this regex ‘(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})’.

let fileNames = FileCreationEvents
| where path has "C:\\Users\\Public"
| project filename
| distinct filename;
ProcessEvents
| where parent_process_name in (fileNames)
| extend ip_address = extract(@"(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})", 0, process_commandline)
| where isnotempty(ip_address)
| distinct ip_address
| count

Question 31: On what date did this actor send their earliest email? YYYY-MM-DD

let ThreatEmail= Email
| where link has "Dev-Requirements.zip"
| distinct sender;
Email
| where sender in (ThreatEmail)
| top 1 by timestamp asc

Question 32: What was the earliest time of day that this actor sent an email? (answer is the hour, e.g. 5 or 5am)

let ThreatEmail= Email
| where link has "Dev-Requirements.zip"
| distinct sender;
Email
| where sender in (ThreatEmail)
| top 1 by timestamp desc

Question 33: What was the latest time of day that this actor sent an email? (The answer is just one digit or E.G. 3PM)

let ThreatEmail= Email
| where link has "Dev-Requirements.zip"
| distinct sender;
Email
| where sender in (ThreatEmail)
| project hour = datetime_part("hour", timestamp)
| distinct hour

Question 34: What was the latest time of day that this actor sent an email? (The answer is just one digit or E.G. 3PM)

let ThreatEmail= Email
| where link has "Dev-Requirements.zip"
| distinct sender;
Email
| where sender in (ThreatEmail)
| project hour = datetime_part("hour", timestamp)
| distinct hour

Section 3: Golden 🐇

Question 1: A law enforcement agency informed HopsNStuff that an adversary was attempting to gain access to their company. They said the actor may have sent a PDF file called **Ginger_beer_secret_recipe.pdf ** in February of 2023. What hostname had this file first?

You aren’t alone if you started to check the Email table for links with the file name. A good tip is to use search to narrow down your search results.

search "Ginger_beer_secret_recipe.pdf"
| distinct $table

Based on the results, the table FileCreationEvents has the hostname which downloaded the file first.

FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| where timestamp between (datetime(2023-02-01) .. datetime(2023-02-28))
| order by timestamp asc

Question 2: When was this file created on the host machine?

You Got this đŸ‘Ÿ

Question 3: How many host machines total have a file with this observed filename (Ginger_beer_secret_recipe.pdf) ?

The questions isn’t referencing the months so let’s filter that out and ‘distinct’ the hostname.

FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| distinct hostname

Question 4: What is the role of the employees of those host machines?

let suspiciousHost = FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| distinct hostname;
Employees
| where hostname in (suspiciousHost)
| distinct role

Question 5: Based on where the files are located on the hosts, how many files total are found within that same path? (Hint: count the total number of files across all the observed filepaths)

From the answer to the first question we have found the table that contains Ginger_beer_secret_recipe.pdf and we can start building on it.

FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"

By analyzing the file path it shown the file is being downloaded to the username downloads folder. To parse the path we can use built in KQL scalar function parse_path() Docs .

path
C:\Users\alstamos\Downloads\Ginger_beer_secret_recipe.pdf
C:\Users\brkrebs\Downloads\Ginger_beer_secret_recipe.pdf
C:\Users\crflyer\Downloads\Ginger_beer_secret_recipe.pdf
C:\Users\eukaspersky\Downloads\Ginger_beer_secret_recipe.pdf

Add the following query lines to filter the distincted folder paths we need.

| project FolderPath = parse_path(path) .DirectoryPath
| distinct tostring(FolderPath)

Now, store the query to FolderTarget using a let statement. Then, we will filter for all the files in the paths.

let FolderTarget =
FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| project FolderPath = parse_path(path) .DirectoryPath
| distinct tostring(FolderPath);
FileCreationEvents
| where path has_any (FolderTarget)
| count

The query above provides the number of files and directories in downloads folder of the nines targeted users.

Question 6: How many of those files are PDFs?

Add | where filename endswith "pdf" to the end of the query above for filter for files ending the pdf extension.

let FolderTarget =
FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| project FolderPath = parse_path(path) .DirectoryPath
| distinct tostring(FolderPath);
FileCreationEvents
| where path has_any (FolderTarget)
| where filename endswith "pdf"

Question 7: How many distinct PDF filenames are there from the previous question?

Add | distinct filename to the end of the query above.

Question 8: Did any of the other files hit on security alerts? Answer “None” if there weren’t any, or submit any of the other filenames that did.

Add the last three lines from the answer to questions six and seven to a let statement to store the results. The question is asking for other files which doesn’t include Ginger_beer_secret_recipe.pdf which we filtered out using | where description !contains "Ginger_beer_secret_recipe.pdf".

let FolderTarget = FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| project FolderPath = parse_path(path) .DirectoryPath
| distinct tostring(FolderPath);
let SusFiles = FileCreationEvents
| where path has_any (FolderTarget)
| where filename endswith "pdf"
| distinct filename;
SecurityAlerts
| where description !contains "Ginger_beer_secret_recipe.pdf"
| where description has_any (SusFiles)

Question 9: Were there any additional host machines identified from the answer from the previous question? Answer “None” if there weren’t any, or submit any of the other hostnames you identified.

Let’s add the query from question 3 as ‘suspiciousHost’ variable and the file we found from question 4 to a ‘maliciousFile’ variable. Then, we will filter out the ‘suspiciousHost’ and look for ‘maliciousFile’ the ‘Employees’ table.

let suspiciousHost = FileCreationEvents
| where filename has "Ginger_beer_secret_recipe.pdf"
| distinct hostname;
let maliciousFile = 
FileCreationEvents
| where filename contains "Brewery_layout.pdf"
| distinct hostname;
Employees
| where hostname !in (suspiciousHost) and hostname in (maliciousFile)

Source

Question 10: Let’s investigate where the two suspicious pdf files came from. How many emails had a reference to the file(s)?

Our suspicious pdf files are Ginger_beer_secret_recipe.pdf and Brewery_layout.pdf. We can find the number of emails with a reference to the files by searching the ‘Email’ table and filtering the link column.

let maliciousFiles = dynamic(["Ginger_beer_secret_recipe.pdf","Brewery_layout.pdf"]);
Email
| where link has_any (maliciousFiles)

Question 11: How many Outbbound connections referenced the file(s)?

let maliciousFiles = dynamic(["Ginger_beer_secret_recipe.pdf","Brewery_layout.pdf"]);
OutboundNetworkEvents
| where url has_any (maliciousFiles)

let maliciousFiles = dynamic(["Ginger_beer_secret_recipe.pdf","Brewery_layout.pdf"]);
OutboundNetworkEvents
| where url has_any (maliciousFiles)
| project domain = tostring(parse_url(url).Host)
| distinct domain

Question 13: How many unique file(s) are referenced from the identified domains?

let maliciousDomains = dynamic([
    "moneybags.us",
    "moneybags.org",
    "moneybags.net",
    "moneybags.biz",
    "getyabreadup.org",
    "getyabreadup.com",
    "getrichorbouncetrying.org",
    "getrichorbouncetrying.net",
    "getrichorbouncetrying.com",
    "getrichorbouncetrying.biz",
    "betterthansilver.us",
    "betterthansilver.org",
    "betterthansilver.net",
    "betterthansilver.biz",
    "abunslife.us",
    "abunslife.org",
    "abunslife.net",
    "abunslife.com",
    "abunslife.biz"
]);
OutboundNetworkEvents
| where url has_any(maliciousDomains)
| project file = parse_path(tostring(parse_url(url).Path)).Filename
| where isnotempty(file)
| distinct tostring(file)

Question 14: Based on your investigation, HopsNStuff may have been a victim of what type of initial attack?

What is the ID for (T1189)[https://attack.mitre.org/techniques/T1189/] called in MITRE ATT&CK? It is not an exact match of the name.

Question 15: Let’s investigate this activity further. What’s the parent_process_hash of the tool that was used to steal user credentials?

Question 16: Investigate the running processes. There are suspicious processes conducting reconnaissance. How many unique directory paths are these suspicious processes located in on infected host machines? (Find the recon command, and go identify its process parent file on disk)

Question 17: How many distinct filenames are located in these directory paths?

Question 19: Which IP address appears to be located in South America? Hint: Check AbuseIPDB or MaxMind GeoIP2 Database

Question 20: How many IP addresses appear to be located in Asia?

Question 21: What file may have been used to exfiltrate data?

Question 22: How many unique domains were used for exfiltration? Answer 0 if you did not find any.

Question 23: Let’s look at the most recent exfiltration activity and the domain used in Questions 21-22. What IP address does this domain resolve to? (Hint: choose the IP address closest to when the date of activity occured)

Question 24: Law enforcement agents say the threat actor may have searched for “egg” on the HopsNStuff’s website. How many distinct IP addresses do this?

Question 25: Law enforcement agents also tell you that the threat actor may have used a batch file but REFUSE to elaborate any further. They tell you it’s classified. What command might they be referring to?

Question 26: A very specific APT defined by Mandiant has used the exact same cmd.exe command used by this attacker and the same credential stealer for lateral movement. Which APT group is this?

Question 27: What is the name of one of non-Linux backdoors used by the APT group from question 25?

Question 28: How many DNS records have domains with the word “moneybags”?

Question 29: On February 8, 2023, Robert Boyce’s machine had a file created with a single letter for its name. Search the hash of this file on VirusTotal. When was it first submitted?

Question 30: Employee Cindy Lozano reported some strange activity with her email account. A weird file was seen in her Sent folder but she deleted it right away without looking at the name. What was the name of this file?

Question 31: Do you think this activity is linked to Section 2? Yes/No (It’s free points, but log down what you answered for future discussions).

Question 32: On what date did this actor send their earliest email? YYYY-MM-DD

let susIPs = 
    InboundNetworkEvents
    | where url has_any ("login_user", "mailbox_senderfolder")
    | distinct src_ip;
let susDomains = 
    PassiveDns
    | where ip in (susIPs)
    | distinct domain;
Email
| where link has_any(susDomains)
| top 1 by timestamp asc

Question 33: On what date did this actor send their most recent email? YYYY-MM-DD

Question 34: What was the earliest time of day that this actor sent an email? (#AM/PM)

Question 35: What was the latest time of day that this actor sent an email? (#AM/PM)

Section 4: 🍭 Sugar Rush

Question 1: IP 158.235.158.156 was observed exfiltrating data from mailboxes at HopsNStuff. How many mailboxes were affected?

The question provided the IP address 158.235.158.156 which we ill use to filer InboundNetworkEvents table.

InboundNetworkEvents
| where src_ip has "158.235.158.156"

By analyzing the url column we notice some login activities.

Let’s filter for those activities.

| where url has "login"

Question 2: How many total accounts did IP 158.235.158.156 successfully login into?

We have the IP address and we are looking for successful logins which are logged in AuthenticationEvents.

AuthenticationEvents
| where src_ip == "158.235.158.156"
| where result has "successful"

Question 3: Whose account was first accessed by IP 158.235.158.156?

AuthenticationEvents
| where src_ip == "158.235.158.156"
| where result has "successful"
| top 1 by timestamp asc

Question 4: What was the subject of the email that the user in (3) received leading to their account being compromised?

First, I need to find the email address of lebedford in the ‘Employees’ table.

Employees
| where username has "lebedford"
| project email_addr

Then, we check the results of the inbound emails in the Email table.

Email
| where recipient has "leonard_bedford@hopsnstuff.com"

I’m presented with 57 results and I would like to reduce the number of logs to analyze by filtering for the timetamp for activites before the login event from the answer to question three.

Email
| where recipient has "leonard_bedford@hopsnstuff.com"
| where timestamp <= datetime(2023-03-01T10:26:28Z)

By viewing the log results and checking the verdict column I can identify two ‘SUSPICIOUS’ events before the timestamp.

Question 5: How many actor email addresses were observed associated with the subject in (4)?

By running the following query we will find that 11 emails were send with the subject [EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival.

Email
| where subject has "[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival"

When we add | distinct sender we get the number 3 but it is not the right answer. The questions is ascking for the number of actors. In other works is is asking for the number of the email addresses uses in this phishing attack. By looking at the other columns in the Email table I found the reply-to column which has one more email address that isn’t in the Senders column.

Senderreply_to
tasting_beer@yahoo.comtasting_beer@yahoo.com
candyfestivals@yahoo.comcraftbeer@yahoo.com
beer.beer@yahoo.com-

Just for KQLing

let sender_results = 
    Email
    | where subject has "[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival"
    | distinct sender;
let reply_to_results = 
    Email
    | where subject has "[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival"
    | distinct reply_to;
reply_to_results
| union sender_results
| summarize by email_address = iff(isnotempty(reply_to), reply_to, sender)
| distinct email_address

In this query:

  • We first store the distinct reply_to and sender results in separate variables using the let statement.
  • Then, we use the union operator to combine the results of both queries into one table.
  • Next, we use the summarize function to group the results by the email address and choose either the reply_to or sender value based on which one is not empty.
  • Finally, we use the distinct function to get only unique email addresses in the final output.

Question 6: How many actor domains were observed associated with the subject in (5)?

Email
| where subject has "[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival"
| extend actorDomain = tostring(parse_url(link).Host)
| distinct actorDomain

Question 7: How many IP addresses were associated via PassiveDNS with the domains in (6)?

We will build on the query from question six by filtering for the PassiveDns table.

let domainDNS = Email
| where subject has "[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival"
| extend actorDomain = tostring(parse_url(link).Host)
| distinct actorDomain;
PassiveDns
| where domain in (domainDNS)
| distinct ip

Question 8: How many top level domains (TLDs) are used by this actor (based on your observations so far)?

They are easy to find from the qurey in question six.

Just for KQLing

In this query, we are filtering email subjects that contain the phrase “[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival”. Next, we extract the domain from the link in the email and then further extract the Top-Level Domain (TLD) from that domain. Finally, we list the distinct TLDs found in the emails.

Email
| where subject has "[EXTERNAL] Exclusive Invitation to our Candy Themed Beer Festival"
| extend actorDomain = tostring(parse_url(link).Host)
| extend tld = tostring(split(actorDomain, ".")[-1])
| distinct tld

Here’s a breakdown of what each part of the query does:

  • Filtering by Subject: | where subject has "[EXTERNAL] Exclusive Invitation to o Candy Themed Beer Festival": Filters the emails based the subject containing the specified text.
  • Extracting Domain and TLD: | extend actorDomain = tostring(parse_url(link).Host) Extracts the domain from the link in the email and create a new column called actorDomain. | extend tld = tostring(split(actorDomain, ".")[-1]) Splits the domain by “.” and extracts the last element which represents the Top-Level Domain (TLD). This is store in a new column called tld.
  • Listing Distinct TLDs: | distinct tld: Returns only the unique Top-Level Domains found in the emails.

Question 9: How many domains are associated with this actor? Look for patterns and build a query based on their infrastructure registration TTPs (Hint: between 75 and 300)

Question 10: How many emails did this actor send?

Question 11: How many of this actor’s emails were actually delivered (not blocked)?

Question 13: How many accounts at HopsNStuff did this actor attempt to log into?

Question 14: How many mail accounts did this actor exfiltrate data from? (Hint: Look for clear evidence of this.)

Community Help

Join Discord to discuss the module KC7 Discord #easter-eggstravaganza .