This post is a write-up or clues on how to resolve the KC7 investigation case of Frognado in Valdoria . 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 feel free to explore your path.

Section 1: Maybe it’s just a tadpole? 😢👀

Question 1:

You Got this 👾

Question 2: What is the MITRE ATT&CK ID for defacement?

Conduct the search on mitre .

Question 3: Who is the Web Administrator? (Paste the full name.)

You Got this 👾

Question 4: What is the hostname of the Web Administrator machine?

You Got this 👾

Question 5: When did the defacement happen exactly? (Paste the full timestamp.)

We have the hostname from the answer to question number 4, but let’s ensure we are using " " and not ‘ ’.

Question 6: When was the first image uploaded? (Paste the full timestamp.)

ProcessEvents
| where hostname == 'MYZB-LAPTOP'
| where process_commandline contains 'frog_mall_meme'

Question 7: What is the Sha256 hash of the first meme that was uploaded to the webserver?

FileCreationEvents
| where hostname == 'MYZB-LAPTOP'
| where filename startswith "frog_mall_meme"

Question 8: What domain were the images downloaded from?

First, we meet the requirement and find Anita’s IP address.

Employees
| where name has "Anita Bath"
| project name, ip_addr

OutboundNetworkEvents
| where src_ip == '10.10.0.8'
| where url contains 'frog_mall_meme'
| extend domain = parse_url(url).Host
| distinct tostring(domain)

Question 9: Which command did the attacker use to look for files containing passwords?

ProcessEvents
| where hostname == 'MYZB-LAPTOP'
| where process_commandline has 'password'

Question 10: What is the name of the file containing passwords?

ProcessEvents
| where hostname == 'MYZB-LAPTOP'
| where process_commandline has 'password'

Question 11: What is the name of that domain?

You Got this 👾

Question 12: What is the last IP address that the domain you found in Q11 resolve to?

PassiveDns
| where domain == 'newdevelopmentupdates.org'

Question 13: Do the IPs found in Q11 resolve to other domains? If they do, answer with the domain. If not, type no.

You Got this 👾

Question 14: What version of Firefox is the threat actor using?

Employees
| where name has "Anita Bath"
| project name, username

let TA_ips =
PassiveDns
| where domain == 'newdevelopmentupdates.org'
| distinct ip;
AuthenticationEvents
| where src_ip in (TA_ips)
| where username == 'anbath'

Question 15: What is Anita’s email address?

You Got this 👾

Question 16: What is the subject of the email she received?

let TA_domains =
PassiveDns
| where domain == 'newdevelopmentupdates.org'
| distinct ip
| lookup PassiveDns on ip
| distinct domain;
Email
| where recipient == 'anita_bath@framtidxdevcorp.com'
| where link has_any(TA_domains)

You Got this 👾

OutboundNetworkEvents
| where src_ip == '10.10.0.8'
| where url == 'https://greenprojectnews.net/share/modules/files/share/enter'

Question 19: What is the full url showing her doing just that?

OutboundNetworkEvents
| where src_ip == '10.10.0.8'
| where url startswith 'https://greenprojectnews.net/share/modules/files/share/enter'
| where url has 'username'

Question 20: Who sent Anita the mail?

As instructed, we will use the quarry from question 16.

let TA_domains =
PassiveDns
| where domain == 'newdevelopmentupdates.org'
| distinct ip
| lookup PassiveDns on ip
| distinct domain;
Email
| where recipient == 'anita_bath@framtidxdevcorp.com'
| where link has_any(TA_domains)

Question 21: Type in ribbit to finish this section.

ribbit

Section 2: KQL 101 📚

Question 1: Type ’let’s do this’ to begin your KQL training.

let's do this

Question 2: Once you’ve looked at all the tables type when in doubt take 10 to continue.

when in doubt take 10

Question 3: How many employees work at FramtidX?

Employees
| count

Question 4: What is the CEO’s name?

Employees
| where role has "CEO"

Question 5: Enter operator, operator to continue.

operator, operator

Question 6: How many emails did Mona Hunter receive?

Employees
| where name has "Mona Hunter"

Email
| where recipient == "mona_hunter@framtidxdevcorp.com"
| count

Question 7: How many distinct senders were seen in the email logs from techinnovators.io?

Email
| where sender has "techinnovators.io"
| distinct sender
| count

Question 8: How many distinct websites did Mona Hunter visit?

We have Mona Hunter’s IP address from the Employees table.

OutboundNetworkEvents
| where src_ip == "10.10.0.131"
| distinct url
| count

Question 9: How many distinct domains in the PassiveDns records contain the word green?

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

Question 10: How many distinct URLs did employees with the first name ‘Dorothy’ visit?

let dorothy_ips =
Employees
| where name has "Dorothy"
| distinct ip_addr;
OutboundNetworkEvents
| where src_ip in (dorothy_ips)
| distinct url

Question 11: How many authentication attempts did we see to the accounts of employees with the first name Dorothy?

Great! Let’s practice what we learned so far. When in doubt take 10 of the AuthenticationEvents. By following the same strategy we used in the answer to question 10 we replace dorothy_ips to dorothy_auth for better identification and clarification, the table OutboundNetworkEvents to AuthenticationEvents and the src_ip to username.

let dorothy_auth =
Employees
| where name has "Dorothy"
| distinct username;
AuthenticationEvents
| where username in (dorothy_auth)
| count

Question 12: Type ready for more to continue to the next part of the investigation.

ready for more

Section 3: Alright it’s definitely an angry frog or two 🥵😨

Question 1: What is the name of Erik Bjorn’s colleague?

After reading the question guidelines, I know that her role is that of a chief architect.

Employees
| where role has "Chief Architect"

Question 2: What is the subject of these emails?

We have the following information from Section 1 Question 16

  • Sofia’s email address: sofia_lindgren@framtidxdevcorp.com
  • Internal address: alex_johnson@framtidxdevcorp.com

We will use a let statement to find both email addresses for the Chief Architects.

let  ChiefArchitectEmials =
Employees
| where role has "Chief Architect"
| project email_addr;
Email
| where recipient in (ChiefArchitectEmials)
| where sender has "alex_johnson@framtidxdevcorp.com"

Question 3: Which domain is the page hosted on?

You Got this 👾

Just for KQLing!

let  ChiefArchitectEmials =
Employees
| where role has "Chief Architect"
| project email_addr;
Email
| where recipient in (ChiefArchitectEmials)
| where sender has "alex_johnson@framtidxdevcorp.com"
| project tostring(parse_url(link).Host)
| distinct Host

Question 4: What type of phishing attack is this?

You Got this 👾

Question 5: How many distinct pages on the company’s website did the threat actor browse to?

First, we need to identify the IP address of the domain greenprojectnews.net; then, we search in InboundNetworkEvents.

PassiveDns
| where domain has "greenprojectnews.net"
| distinct ip

InboundNetworkEvents
| where src_ip in ('239.72.6.37', '239.72.6.38')
| distinct url
| count

Just for KQLing!

let TA_ips =
PassiveDns
| where domain has "greenprojectnews.net"
| distinct ip;
InboundNetworkEvents
| where src_ip in (TA_ips)
| distinct url
| count

We will modify the query above to find the answer to this question.

let TA_ips =
PassiveDns
| where domain has "greenprojectnews.net"
| distinct ip;
InboundNetworkEvents
| where src_ip in (TA_ips)
//| distinct url
//| count 
| summarize count() by referrer
| order by count_ asc

By analyzing the results we can identify the company’s career page.

Question 7: Did any of them try to log in to that actor controlled page? If only one of them did, answer with their name, if both did, type both.

First, let’s check the OutboundNetworkEvents table for any traffic to greenprojectnews.net.

OutboundNetworkEvents
| where url has "greenprojectnews.net"

We can’t identify the users by looking at the results, but we know the Employees table has each employee ip_addr. The best route is to have both of the employee’s IP addresses in a let statement and search for the outbound traffic.

let ChiefArchitectIPs =
Employees
| where role has "Chief Architect"
| distinct ip_addr;
OutboundNetworkEvents
| where src_ip in (ChiefArchitectIPs)
| where url has "greenprojectnews.net"

Analyze the urls to find the answer.

Question 8: What time did they manage to log in to Sofia’s machine?

Let’s follow the same strategy from Question 14 but with the user name solindgren and the greenprojectnews.net domain.

Employees
| where name has "Sofia"
| project name, username

let TA_ips =
PassiveDns
| where domain == 'greenprojectnews.net'
| distinct ip;
AuthenticationEvents
| where src_ip in (TA_ips)
| where username == 'solindgren'

Question 9: What is the first Powershell cmdlet used to delete something from Erik and Sofia’s machines?

By reviewing the available tables the ProcessEvents Records processes created on employee’s devices. should have logged this activity. After taking a sample of the available data by taking 10, it shows a hostname column available in the Employees.

let ChiefArchitectHostname =
Employees
| where role has "Chief Architect"
| distinct hostname;
ProcessEvents
| where hostname in (ChiefArchitectHostname)
| where process_commandline has "Remove-Item"

Remove-Item .

Question 10: What is the name of the file that was deleted?

You Got this 👾

Question 11: What is the name of the file they downloaded?

In Question 16 We identified the list of domains used by the threat actors. We can identify the file by merging what we discovered from that question and what we know from the guideline.

let ChiefArchitectHostname =
Employees
| where role has "Chief Architect"
| distinct hostname;
let TA_domains =
PassiveDns
| where domain == 'newdevelopmentupdates.org'
| distinct ip
| lookup PassiveDns on ip
| distinct domain;
ProcessEvents
| where hostname in (ChiefArchitectHostname)
| where process_commandline has_any (TA_domains)

Question 12: Which domain hosted that file?

You Got this 👾

Question 13: What Powershell cmdlet did the attackers use to rename the downloaded file?

let ChiefArchitectHostname =
Employees
| where role has "Chief Architect"
| distinct hostname;
ProcessEvents
| where hostname in (ChiefArchitectHostname)
| where process_commandline has "fake_plans"

Question 14: What was the file renamed to?

You Got this 👾

Question 15: According to MITRE, what kind of impact is this an example of?

T1565

Section 4: Nope, it’s a full on frognado!!!! 🐸🌪️😱

Question 1: What is Alex Johnson’s role in the company?

Employees
| where name has "Alex Johnson"

Question 2: How many internal phishing emails were sent from Alex’s email address?

After finding the email address we could find the emails send by Alex containing the phishing links.f

Email
| where sender == "alex_johnson@framtidxdevcorp.com"
| where link contains "green" or link contains "development"

HINT

Email
| where sender == "alex_johnson@framtidxdevcorp.com"
| where link has "greenprojectnews" or link has "newdevelopmentupdates"

Question 3: How many distinct roles were targeted by the spearphishing emails?

Let’s build on the query from the question above by checking the Employees table.

let spearPhishingTargetetRoles =
Email
| where sender == "alex_johnson@framtidxdevcorp.com"
| where link has "greenprojectnews" or link has "newdevelopmentupdates"
| distinct recipient;
Employees
| where email_addr in (spearPhishingTargetetRoles)
| distinct role

Question 4: What is the name of the very important person who was targeted?

You Got this 👾

Question 5: What was the subject of the mail targeting the person found in Q4?

Email
| where sender == "alex_johnson@framtidxdevcorp.com"
| where link has "greenprojectnews" or link has "newdevelopmentupdates"
| where recipient == "johanna_karlsson@framtidxdevcorp.com"

Question 6: What are they trying to find out about the person from Q4?

In the past section, we identified How many distinct pages on the company’s website did the threat actor browse to . We can build on this query to find what the treat actors are trying to find about the CEO.

let TA_ips =
PassiveDns
| where domain has "greenprojectnews.net" or domain has "newdevelopmentupdates.org"
| distinct ip;
InboundNetworkEvents
| where src_ip in (TA_ips)
| distinct url

The results show 78 records! Let’s filter more to narrow the results.

let TA_ips =
PassiveDns
| where domain has "greenprojectnews.net" or domain has "newdevelopmentupdates.org"
| distinct ip;
InboundNetworkEvents
| where src_ip in (TA_ips)
| where url contains "CEO"
| distinct url

Question 7: Did Johanna type in her credentials? yes/no.

let CEOip =
Employees
| where role == "CEO"
| project ip_addr;
OutboundNetworkEvents
| where url has "http://newdevelopmentupdates.org/public/signin"
| where src_ip in (CEOip)

Question 8: When did the threat actor log in to Johanna’s machine?

Nice, we have done that before in Question 14

Employees
| where role == "CEO"
| project username

let TA_ips =
PassiveDns
| where domain == 'newdevelopmentupdates.org'
| distinct ip;
AuthenticationEvents
| where src_ip in (TA_ips)
| where username == 'jokarlsson'

Question 9: In which folder did they collect the incriminating files?

The available tables have one that logs records processes created on employees’ devices called ProcessEvents. Let’s take 10 to see what data is available. We do know the threat actors signed in to the device, and we know the hostname.

Employees
| where role == "CEO"
| project hostname

let CEOhostname =
Employees
| where role == "CEO"
| project hostname;
ProcessEvents
| where hostname in (CEOhostname)
| where timestamp >= datetime(2024-06-27T12:40:59Z)

Question 10: What command did they use to do this?

You Got this 👾

Question 11: What is the email address of that person?

Email
| where sender == "johanna_karlsson@framtidxdevcorp.com"
| summarize total = count() by recipient

Question 12: How many emails total can you find between them?

There are multiple ways to get this answer. Have some fun!

Just for KQLing!

let senders = dynamic(["johanna_karlsson@framtidxdevcorp.com", "erik.stevens@valdoriapublicworks.gov"]);
let recipients = dynamic(["erik.stevens@valdoriapublicworks.gov", "johanna_karlsson@framtidxdevcorp.com"]);
Email
| where (sender has_any (senders) and recipient has_any (recipients))

Email
| where (sender has "johanna_karlsson@framtidxdevcorp.com" and recipient == "erik.stevens@valdoriapublicworks.gov") 
    or (sender has "erik.stevens@valdoriapublicworks.gov" and recipient == "johanna_karlsson@framtidxdevcorp.com")

Question 13: What is the mayor looking forward to?

You Got this 👾

You Got this 👾

Question 15: When did this happen?

Let’s analyze the results we discovered on Question 9 .

Question 16: How many chunks were the archive divided into?

You Got this 👾

Question 17: What is that email address?

Email
| where sender has "framtidxdevcorp.com" and recipient !has "framtidxdevcorp.com"
| summarize total = count(recipient) by recipient
| project recipient, total

Question 18: Who is the other recipient?

Email
| where subject has "Confidential Documents"
| where recipient !has "htuortwodahs@yopmail.com"

Question 19: What is the subject of the emails sent to the editor?

You Got this 👾

Question 20: Type slurp to finish the module."

slurp

Community Help

Join Discord to discuss the module KC7 Discord frognado-in-valdoria .