KC7 Module: A Scandal in Valdoria: Section 1 KQL 101

Here is the link to the Module: A Scandal in Valdoria 🌟

Breakdown

The module is broken up into four different sections: KQL 101, Welcome to Valdoria!, Plenty of Phish, and A Scandal. In the write-up we will be focusing on the first section, KQL 101. As you work you way through the questions each should build onto each other. Example being, you may discover an IP address in one question that leads you to investigate it for several other questions. Additionally, KC7 also provides an amazing resource for this room, which I just linked.

Run on your own

While you can use the KC7 ADX (Azure Data Explorer) provided. You are able to run them yourself using your own ADX. The way to do this is to click the Query Data (ADX) at the top of the ADX panes.

From here you will need to either use your Microsoft account or create a new Microsoft account. Once you get logged in you will be brought to your own ADX. You will have a pop-up asking if you trust the KC7 data that is trying to be added to your ADX. Click Trust, and the kc7001.eastus data will be added to your ADX.

We need to expand and select the correct data, so we can be able to investigate. First click on the carat to the left of kc7001.eastus, which will drop down the different data correlating to each module. Then scroll down till you see ValdyTimes.

Once you see ValdyTimes, click on it to load the data. Additionally, you can click on the carat, which will drop down the different tables we will be able to use during this module.

The Reason why

The purpose of these modules is to help you learn how to investigate. They do this by laying the ground work and fundamentals needed to understand why you are looking. Join me as I go through the room. But please note, I may not share all the questions in this write up, only the ones pretendant to the investigation. I will be including the answers to the questions as well. But please know, while the answers may change, the way that you get them should remain the same. Now, let’s get started!!!

Section 1: KQL 101

Learning about Valdoria and the Scandal

Welcome to Valdoria!
On the eve of the election, Nene Leaks, the esteemed editor of The Valdorian Times, awoke to a nightmare. The Valdorian Times, the beacon of truth for the city, published a scandalous article accusing Luffy of corruption and misconduct. The article, a vile concoction of lies, was not what she had approved.
The article alleged that Luffy, hailed for his environmental activism and social reforms, was secretly involved in a land deal scandal, exploiting his position to benefit a shadowy network of real estate moguls. Furthermore, it accused Luffy of accepting substantial bribes to push environmentally damaging policies, a stark contradiction to his public persona.
However, the article, a vile concoction of lies, was not what had been approved by the newspaper’s editor 😵.
The Valdorian Times has hired you as a cyber incident responder to help investigate the incident and get to the bottom of how the falsified article was published.

Familiarize yourself

Before starting any investigation, you should be familiar with the information at hand. You need to know what you do and don’t have access to. In our case, the tables that are present in the picture below, we are able to use. As explained above in the Run on your own section, we can easily see the tables in our ADX.

Knowing these tables will help us to better understand where we need to go to get the information during our investigation. I would also suggest that as you gather new information, that you keep it stored somewhere. For me I am using Notion to write/type this down, and created a Database to store the information gathered. Here it what we know so far:

Nene Leaks

Editor at the Valdorian Times
Claims she didn’t approve of the article written about Luffy Monk, that was printed on January 20, 2024

Luffy Monk

Mayoral candidate
Environmental Advocate

Erik Stevens

Current Mayor
Platform aligns more with business and rapid expansion

How many employees work at the Valdorian Times?

Heading over to our Query Pane, we know that the table we want to start looking at is Employees. From there we can use the count function to give us the number of records in the Employees table. In the Query Pane, type:

Employees  
| count

Then click the blue Run button in the top left of the query pane (I am show this one, but won’t be from here on out).

In the Results Pane at the bottom, only one column will appear. In the Count Column will be the answer. Type it into the Answer box and click Submit.

ANSWER: 100

What is the Editorial Director’s name?

Again, sticking with the Employees table. We know from the question, that the role of the person is Editorial Director. With this information, we can craft the query where we are looking at the Employees table. Then from that table we only want to see which Employee’s have the role of Editorial Director. If they do, it will show us the result’s in the Results Pane. Our query should look like the following:

Employees  
| where role == "Editorial Director"

Once typed into the Query Pane, press the blue Run button in the top left. There will be only one result in the Result Pane. On said result, look for the name column. The Employee’s name will be listed under here. We can either type the name into the Answer Box or right-click and choose copy, then paste the answer into the Answer Box. Then click Submit.

ANSWER: Nene Leaks

How many emails did Nene Leaks receive?

From our previous query we can also get Nene’s email address (nene_leaks@valdoriantimes(.)news). We can use this information to search the Email table. To start our query, we first need to declare the table, which again is Email. Then we press enter to start a new line, and a pipe ( | ) should automatically get placed on the new line. If it doesn’t then type a pipe ( | ) before you start typing. From here we need to pull only Nene Leaks emails that she received. This can be done by using where, which will filter a table using a filter given. In our case that filter is recipient. We then know what Nene Leaks email address is, so our next qualifier is contains. Finally, we add Nene Leaks email address between double quotes ( “ ). Lastly, we need to start a new line, and again making sure the pipe ( | ) is in place. Then use the function count to count the number of results. Altogther the query should look like the following:

Email  
| where recipient contains "nene_leaks@valdoriantimes.news"  
| count

Run the query in ADX, and look at the Results Pane for the results. Since we used the count function, there will only be one column, under which is the answer to the question. Type the answer into the Answer Box then click Submit.

ANSWER: 18

How many distinct senders were seen in the email logs from the domain name “weprinturstuff.com”?

In our ADX we can expand the Email table by clicking on the carat. This will then show the different columns of information we have access to. Amoungst them is the Senders column. With this information we can now build a query to answer our question.

We will be sticking with the Email table. Using the similar query we used in our previous attempt, we only need to change a couple things. In this case instead of recipients we will change it to sender, and instead of “nene_leaks@valdoriantimes.news” it will be “weprinturstuff.com”. Finally we will leave the last line the same, that being count. Your query should look like the one below.

Email  
| where sender contains "weprinturstuff.com"  
| count

Click the Run button to search using our query. When it is finished running, you will see one column in the Results pane. Under that column will be the answer, type it into the Answer Box and click Submit.

ANSWER: 100

How many distinct websites did “Lois Lane” visit?

First we need to gather some information on Lois Lane. To do this we need to query up information from the Employees table. The query will start with the table name, that being Employees. We have her name, so we can build out the query with where name == “Lois Lane”. The final query should look like it does in the code block below.

Employees  
| where name == "Lois Lane"

Click the blue Run button. Once it has completed, we will have information regarding Lois Lane. This includes her IP Address. I have placed this and some of her information on the Information Gathered database I created in Notion. Time to create our query to find the number of unique (distinct) websites that Lois Lane visited.

Looking at the different tables we have access, the one that works best for this situation is the OutboundNetworkEvents.

Starting our query out with OutboundNetworkEvents. We we need to only show results that pertain to Lois Lanes IP Address. To do this we use where src_ip == “10.10.0.22”. Next line down we want to only see the unique (distinct) URL_s. Finally, the last line will be _count. If we type the query out correctly it should look like the following in the code block:

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

ANSWER: 62

How many distinct domains in the PassiveDns records contain the word “hire”?

The question contains key information that will aid us in figuring out the proper query to use. Here is the keywords to focus in on: distinct, domains, PassiveDns, and “hire”. Looking at the usable tables, we can see that PassiveDns is one of the tables. Meaning that we know how we will start our query, using the PassiveDns table.

Next up we need to pull the domains. From the question we are looking for domain names that contain the word “hire”. So from previous queries, we should know how to pull this type of information. Using the following line: where domain contains “hire”. This will pull all the domains that contain hire, even multiple instances of them. On the next line we will take care of that using the distinct function. Finally, on the last line we will use the count function to count the number of unique domains. The final query should look like it does below:

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

ANSWER: 6

What IPs did the domain “jobhire.org” resolve to (enter any one of them)?

We will stick with the PassiveDns table again for this question. From there we will are looking for the domain “jobhire.org”. We can use the same type of query line as we have done before: where domain == “jobhire.org”. With that we our query should look like the following:

PassiveDns  
| where domain == "jobhire.org"

Click the Run button to search using our query. When it is finished running, we will see two columns. One for IP and the other for the domain. Click on the IP Address and copy ( ctrl c ) and paste ( ctrl v ) the IP Address in the Answer Box. Then click Submit

ANSWER 191.7.248.112

How many distinct websites did employees with the first name “Mary” Visit?

For this question, we first need to find out how many people named Mary work for the Valdorian Times and get their IP addresses. Then take those IP addresses and search them against outbound connections. Finally, only showing one instance of website connected to and then counting those instances. With all that understood, let’s build our query. All the above can be done in one query!! To do this we will create a variable that will encapsulate the results from the first part of query and use it in our second. Enough talking about it let’s do it!!

To set a variable we will use let followed by what we want to name our variable and the equals ( = ) symbol. I named my variable marys_ips. Additionally you can’t have spaces in the name of the variable, which is why I added the underscore ( _ ). Next up, we want to pull from the Employees table. In the next line we will use the where function and name column, but this time using the startswith function as well. This will take a string that we specify, and look at the name column to see if it starts with that. To end this variable, we only want to show one instance of each unique IP address. This can be done using distinct ip_addr. Finally end the line with a semi-colon ( ; ), which will tell the query that everything to that point will be in the variable we started with.

Time for the next part of our query. The next line will be directly under the previous one, no extra spaces needed. We will be using the OutboundNetworkEvents table for this part of the query. The next line down, we will use the where function and src_ip column. Followed by the in operator and our variable within parathesis. This will take the IP addresses from the first query and only give us results pertaining to these IP addresses. Onto the next line, we will want to only have a single instance of an website visited. To show this we use the distinct function with the url column. Lastly, we want to count how many unique instances there were. Again, we will use the count function. After all is said and done, our query should look like it does below:

let marys_ips =   
Employees  
| where name startswith "Mary"  
| distinct ip_addr;  
OutboundNetworkEvents  
| where src_ip in (marys_ips)  
| distinct url  
| count

ANSWER: 58

How many authentication attempts did we see to the accounts of employees with the first name Mary?

This question seems quite similar to our previous question. The difference here though is that we are looking for Authentication attempts, which give us a clue for one of the tables we need to use. To be able to authenticate to a service you are going to need a piece of information, in this case it would be the usernames for the different Mary’s at Valdorian times. Let’s us get to building our query!!

The query is going to be very similar to our previous query we ran, so let’s start at the top and work our way down. Like before we are going to create a variable, I changed the variable’s name to marys_usernames. Ending the line with an equal ( = ). The next line will be the Employees table, followed by line underneath: where name startswith “Mary”. Onto the next line down we make another change, instead of the ip_addr from the previous command. We will be using distinct username, indicating that we want the employees username. Finally ending the variable with a semi-colon ( ; ).

As I mention at the beginning of this question, we are checking for the Authentication attempts. Which looking at the available tables, one is named AuthenticationEvents. This is the table we will want to check. The next line down will have another change to it, this time being: where username in (marys_usernames). Which means that we are pulling out each instance that a Mary attempted to authenticate. Lastly, we want to count these instances, which is done by using the count function. When all is typed out, it should look like it does below:

let marys_usernames =   
Employees  
| where name startswith "Mary"  
| distinct username;  
AuthenticationEvents  
| where username in (marys_usernames)  
| count

ANSWER 70

Learning Points

This section is a great primer to get you started in the world of KQL. By this point you should know how to do the following:

Basic KQL query structure and syntax
Using the ‘where’ clause to filter results
Employing the ‘contains’ operator for partial string matches
Utilizing the ‘distinct’ function to eliminate duplicates
Applying the ‘count’ function to tally results
Creating and using variables with the ‘let’ statement
Chaining multiple operations in a single query
Querying across different tables (e.g., PassiveDns, Employees, OutboundNetworkEvents)
Using the ‘startswith’ function for string comparisons
Implementing the ‘in’ operator to check for multiple values

All the above skills are going to be vital to understanding and creating better KQL queries for log analysis in the future.

Congrats!

Congratulations, you have completed the first section of the A Scandal in Valdoria module from KC7. Don’t stop now, there are still three more sections to complete!