This post is a walkthrough of the KC7 investigation case of KC7 HopsNStuff - Section 2: 🥚Info💰 . 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:

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.

I need to explain this one

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

Community Help

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