KC7 CTF — Castle and Sand Writeup: Section 1 & 2

Full score on the scoreboard 😎

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:

• 🦆 for hard challenges
• Section 3 requires some Section 2 challenges
• Section 5 requires some Section 4 challenges

Some context before we start!

Section 1: KQL 101 🧰

1. How many employees are in the company?

Employees
| count

2. Which employee has the IP address: “10.10.2.1”?

Employees
| where ip_addr == "10.10.2.1"

3. How many emails did Jacqueline Henderson receive?

Email
| where recipient == "jacqueline_henderson@castleandsand.com"
| count

4. 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

5. 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

6. How many distinct domains in the PassiveDns records contain the word “shark”?

PassiveDns
| where domain contains "shark"
| distinct domain
| count

7. What IPs did the domain “sharkfin.com” resolve to (enter any one of them)?

PassiveDns
| search "sharkfin.com"

8. 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

Section 2: Shark Attack! 🦈

1. Oh no! Castle&Sand has been hit with ransomware!!! They posted a ransom note and locked all of the company’s files. View the ransom note here: https://drive.google.com/file/d/1C9E2rSOSu1vYnZdW7VSVtbmix8a62i0C/view?usp=sharing

• Email: sharknadorules_gang@onionmail.org
• Unique decryption ID: SUNNYDAY123329JA0

2. The ransom note filename was called PAY_UP_OR_SWIM_WITH_THE_FISHES.txt. How many notes appeared in Castle&Sand’s environment?

FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| count

3. How many distinct hostnames had the ransom note?

FileCreationEvents
| where filename == "PAY_UP_OR_SWIM_WITH_THE_FISHES.txt"
| count

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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"

11. What's the SHA256 hash of that file?

71daa56c10f7833848a09cf8160ab5d79da2dd2477b6b3791675e6a8d1635016

12. What application created that file?

(process_name) firefox.exe

13. Look for other files with that same name. How many unique hosts had that file on their systems?

FileCreationEvents
| where filename == "Chomping-Schedule_Changes.xlsx"
| distinct hostname
| count

14. 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

15. 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

16. 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

134.136.25.2, 17.72.123.89, 165.185.77.18, 213.30.8.133, 19.216.253.112, 193.248.75.126

17. 🦆 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")

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"

193.248.75.126

More reference: How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language)

18. There was another domain found from Q14. How many unique IPs did that domain resolve to?

PassiveDns
| where domain == "sharkfin.com"
| distinct ip
| count

180.5.6.199, 157.242.169.232, 188.203.116.15, 200.106.38.88

19. 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

20. 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

21. 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)

22. 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

23. When was the earliest email sent?

Timestamp: 2023-05-25T16:33:09Z
Sender: legal.sand@verizon.com

24. 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

25. 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

26. 🦆 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

byte-apex.com, byte-fin.com, byte-jaw.com, jawshark.com, jawfin.com, sharkfin.com

27. 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

28. 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

29. 🦆 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

30. 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

31. When was the first file observed?

2023-05-25T16:43:20Z

32. 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

33. 🦆 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

34. 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

35. Which host machine did the powershell activity execute on?

CL8Q-LAPTOP

36. 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

37. What legitimate Windows process was this file trying to masquerade as?

svchost.exe (Based on knowledge → Typosquatting)

38. 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

39. 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

40. 🦆 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

41. 🦆 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

42. 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

43. 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

44. When was the earliest time found in Q43?

2023–06–09T19:43:58Z

45. 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"

2023–06–09T19:43:48Z

Wew, that’s a long one, see you in the next section 👀(We still have Section 3–6 to go HAHAH)