<style>pre { line-height: 125%; } td.linenos .normal { color: #6e7681; background-color: #0d1117; padding-left: 5px; padding-right: 5px; } span.linenos { color: #6e7681; background-color: #0d1117; padding-left: 5px; padding-right: 5px; } td.linenos .special { color: #e6edf3; background-color: #6e7681; padding-left: 5px; padding-right: 5px; } span.linenos.special { color: #e6edf3; background-color: #6e7681; padding-left: 5px; padding-right: 5px; } .codehilite .hll { background-color: #6e7681 } .codehilite .c { color: #8b949e; font-style: italic } /* Comment */ .codehilite .err { color: #f85149 } /* Error */ .codehilite .esc { color: #e6edf3 } /* Escape */ .codehilite .g { color: #e6edf3 } /* Generic */ .codehilite .k { color: #ff7b72 } /* Keyword */ .codehilite .l { color: #a5d6ff } /* Literal */ .codehilite .n { color: #e6edf3 } /* Name */ .codehilite .o { color: #ff7b72; font-weight: bold } /* Operator */ .codehilite .x { color: #e6edf3 } /* Other */ .codehilite .p { color: #e6edf3 } /* Punctuation */ .codehilite .ch { color: #8b949e; font-style: italic } /* Comment.Hashbang */ .codehilite .cm { color: #8b949e; font-style: italic } /* Comment.Multiline */ .codehilite .cp { color: #8b949e; font-weight: bold; font-style: italic } /* Comment.Preproc */ .codehilite .cpf { color: #8b949e; font-style: italic } /* Comment.PreprocFile */ .codehilite .c1 { color: #8b949e; font-style: italic } /* Comment.Single */ .codehilite .cs { color: #8b949e; font-weight: bold; font-style: italic } /* Comment.Special */ .codehilite .gd { color: #ffa198; background-color: #490202 } /* Generic.Deleted */ .codehilite .ge { color: #e6edf3; font-style: italic } /* Generic.Emph */ .codehilite .gr { color: #ffa198 } /* Generic.Error */ .codehilite .gh { color: #79c0ff; font-weight: bold } /* Generic.Heading */ .codehilite .gi { color: #56d364; background-color: #0f5323 } /* Generic.Inserted */ .codehilite .go { color: #8b949e } /* Generic.Output */ .codehilite .gp { color: #8b949e } /* Generic.Prompt */ .codehilite .gs { color: #e6edf3; font-weight: bold } /* Generic.Strong */ .codehilite .gu { color: #79c0ff } /* Generic.Subheading */ .codehilite .gt { color: #ff7b72 } /* Generic.Traceback */ .codehilite .g-Underline { color: #e6edf3; text-decoration: underline } /* Generic.Underline */ .codehilite .kc { color: #79c0ff } /* Keyword.Constant */ .codehilite .kd { color: #ff7b72 } /* Keyword.Declaration */ .codehilite .kn { color: #ff7b72 } /* Keyword.Namespace */ .codehilite .kp { color: #79c0ff } /* Keyword.Pseudo */ .codehilite .kr { color: #ff7b72 } /* Keyword.Reserved */ .codehilite .kt { color: #ff7b72 } /* Keyword.Type */ .codehilite .ld { color: #79c0ff } /* Literal.Date */ .codehilite .m { color: #a5d6ff } /* Literal.Number */ .codehilite .s { color: #a5d6ff } /* Literal.String */ .codehilite .na { color: #e6edf3 } /* Name.Attribute */ .codehilite .nb { color: #e6edf3 } /* Name.Builtin */ .codehilite .nc { color: #f0883e; font-weight: bold } /* Name.Class */ .codehilite .no { color: #79c0ff; font-weight: bold } /* Name.Constant */ .codehilite .nd { color: #d2a8ff; font-weight: bold } /* Name.Decorator */ .codehilite .ni { color: #ffa657 } /* Name.Entity */ .codehilite .ne { color: #f0883e; font-weight: bold } /* Name.Exception */ .codehilite .nf { color: #d2a8ff; font-weight: bold } /* Name.Function */ .codehilite .nl { color: #79c0ff; font-weight: bold } /* Name.Label */ .codehilite .nn { color: #ff7b72 } /* Name.Namespace */ .codehilite .nx { color: #e6edf3 } /* Name.Other */ .codehilite .py { color: #79c0ff } /* Name.Property */ .codehilite .nt { color: #7ee787 } /* Name.Tag */ .codehilite .nv { color: #79c0ff } /* Name.Variable */ .codehilite .ow { color: #ff7b72; font-weight: bold } /* Operator.Word */ .codehilite .pm { color: #e6edf3 } /* Punctuation.Marker */ .codehilite .w { color: #6e7681 } /* Text.Whitespace */ .codehilite .mb { color: #a5d6ff } /* Literal.Number.Bin */ .codehilite .mf { color: #a5d6ff } /* Literal.Number.Float */ .codehilite .mh { color: #a5d6ff } /* Literal.Number.Hex */ .codehilite .mi { color: #a5d6ff } /* Literal.Number.Integer */ .codehilite .mo { color: #a5d6ff } /* Literal.Number.Oct */ .codehilite .sa { color: #79c0ff } /* Literal.String.Affix */ .codehilite .sb { color: #a5d6ff } /* Literal.String.Backtick */ .codehilite .sc { color: #a5d6ff } /* Literal.String.Char */ .codehilite .dl { color: #79c0ff } /* Literal.String.Delimiter */ .codehilite .sd { color: #a5d6ff } /* Literal.String.Doc */ .codehilite .s2 { color: #a5d6ff } /* Literal.String.Double */ .codehilite .se { color: #79c0ff } /* Literal.String.Escape */ .codehilite .sh { color: #79c0ff } /* Literal.String.Heredoc */ .codehilite .si { color: #a5d6ff } /* Literal.String.Interpol */ .codehilite .sx { color: #a5d6ff } /* Literal.String.Other */ .codehilite .sr { color: #79c0ff } /* Literal.String.Regex */ .codehilite .s1 { color: #a5d6ff } /* Literal.String.Single */ .codehilite .ss { color: #a5d6ff } /* Literal.String.Symbol */ .codehilite .bp { color: #e6edf3 } /* Name.Builtin.Pseudo */ .codehilite .fm { color: #d2a8ff; font-weight: bold } /* Name.Function.Magic */ .codehilite .vc { color: #79c0ff } /* Name.Variable.Class */ .codehilite .vg { color: #79c0ff } /* Name.Variable.Global */ .codehilite .vi { color: #79c0ff } /* Name.Variable.Instance */ .codehilite .vm { color: #79c0ff } /* Name.Variable.Magic */ .codehilite .il { color: #a5d6ff } /* Literal.Number.Integer.Long */</style><h1>Welcome to Castle&Sand</h1> <p>🥳 Today is your first day as a Junior Security Operations Center (SOC) Analyst with our company. Your primary job responsibility is to defend Castle&Sand and our employees from malicious cyber actors.</p> <p><img alt="243223309-cdd08773-1d97-4a99-a259-f9acefaea020" src="https://github.com/KC7-Foundation/kc7_data/assets/9474932/d5bbf3c1-bae6-407a-9ef6-4acf037b3940" /></p> <p>Castle&Sand is a renowned company in the beach accessories and gear industry, operating a vast network of stores across multiple locations. With a comprehensive range of products and a dedicated team of knowledgeable staff, Castle&Sand offers customers an exceptional shopping experience. The company's commitment to research and development ensures that they stay ahead of industry trends, providing high-quality and innovative beach accessories and gear. Castle&Sand's focus on customer satisfaction, welcoming store environments, and continuous improvement has solidified its position as a leader in the industry, making it the go-to destination for all beachgoers' needs.</p> <p>Castle&Sand has a series of key partners who contribute to the success of our business:</p> <table> <thead> <tr> <th>Partner Name</th> <th>Relationship</th> </tr> </thead> <tbody> <tr> <td>Sun & Sand Trading (sunandsandtrading.com)</td> <td>Leading distributor of high-quality beach products and accessories. Collaborates closely with Castle&Sand to provide a diverse range of merchandise, ensuring inventory meets the demands and preferences of beachgoers.</td> </tr> <tr> <td>Beach Supply Co (beachsupplyco.com)</td> <td>Specialized online retailer focused on providing beach essentials and supplies. Strategic partnership with Castle&Sand enhances online presence and expands customer base. Complements Castle&Sand's brick-and-mortar stores, creating a seamless omnichannel shopping experience.</td> </tr> <tr> <td>Surf Paradise (surfparadise.com)</td> <td>Popular online platform dedicated to surfing and beach culture. Partnership with Castle&Sand leverages their extensive audience of surf enthusiasts and beach lovers, enhancing brand visibility and driving traffic to stores and online channels. Provides valuable industry insights and trends.</td> </tr> <tr> <td>Beach Lifestyle (beachlifestyle.com)</td> <td>Lifestyle media and content platform focused on beach living, travel, and fashion. Partnership with Castle&Sand taps into engaged community of beach enthusiasts, increasing brand exposure, loyalty, and fostering a sense of community. Content and marketing expertise aligns with Castle&Sand's values.</td> </tr> </tbody> </table> <p>Castle&Sand has been laser-focused on broadening our customer base. Recently, though, we have been getting a lot of attention. All this extra attention has brought along some unwelcome visitors- cyber attackers! That’s why we’ve hired you! To help keep us safe!</p> <p>Like all good companies, Castle&Sand collects log data about the activity performed on the corporate network. These security audit logs are stored in Azure Data Explorer (ADX) - a data storage service in Azure (Microsoft’s cloud). You will use the Kusto Query Language (KQL) to parse through various types of security logs. By analysing these logs, you can help us determine whether we’re being targeted by malicious actors.</p> <p>You can find full documentation on ADX here: <a href="https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial?pivots=azuredataexplorer">https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial?pivots=azuredataexplorer</a></p> <h3>Objectives</h3> <p>🧠 By the end of your first day on the job, you should be able to:</p> <ul> <li>Use Azure Data Explorer and Kusto Query Language</li> <li>Use multiple data sets to answer targeted questions </li> <li>Investigate cyber activity in logs including: email, web traffic, and server logs </li> <li>Use multiple techniques to track the activity of APTs (Advanced Persistent Threats) </li> <li>Use third party data sets to discover things about your attackers</li> <li>Make recommendations on what actions a company can take to protect themselves</li> </ul> <p><strong>The attackers have gotten a head start, so let's not waste any more time... let's get to work!</strong></p> <h3>Legend</h3> <blockquote> <p>🎯Key Point – Occasionally, you will see a dart emoji with a “key point.” These signal explanations of certain concepts that may enhance your understanding of key cybersecurity ideas that are demonstrated in the game. </p> <p>🤔Question – “Thinking” emojis represent questions that will enable you to demonstrate mastery of the concepts at hand. You can earn points by entering your responses to questions in the scoring portal available at kc7cyber.com/scoreboard.</p> <p>🤫 Hint – “Whisper” emojis represent in-game hints. These hints will guide you in the right direction in answering some of the questions.</p> </blockquote> <h2>Section 1: The Walkthrough</h2> <h4>Getting Set Up in Azure Data Explorer (ADX)</h4> <p>ADX is the primary tool used in the Castle&Sand SOC for data exploration and analysis. The great thing about ADX is that it is used by cyber analysts at many of the smallest and largest organizations in the world. </p> <p>Let’s get you logged in and started with ADX:</p> <p>On the left sidebar, you’ll see a button that says <strong>Query Data (ADX)</strong>. Click this and it will redirect you to ADX! (Note: You’ll probably be asked to login with a Microsoft account. You can use an existing personal or organization-issued Microsoft account, or create a new one for free.)</p> <p>Once you login, you should see a cluster called <em>“kc7001.eastus”</em> has already been added to your account.</p> <p><img width="507" alt="image" src="https://github.com/KC7-Foundation/kc7_data/assets/5226058/c36483ba-9521-4199-ac60-f02159019d65"></p> <p>Data in ADX is organized in a hierarchical structure which consists of <strong>clusters</strong>, <strong>databases</strong>, and <strong>tables</strong>.</p> <p><img alt="Untitled-3" src="https://github.com/KC7-Foundation/kc7_data/assets/31902160/1a623eaa-8228-4426-865f-e3e4262b1489" /></p> <p>All of Castle&Sand’s security logs are stored in a single database – the CastleSand database. </p> <ol> <li>Select your database. <ul> <li>Expand the dropdown arrow next to the Castle&Sand database.</li> <li>Click on the <strong>CastleSand</strong> database. Once you’ve done this, you should see the database highlighted- this means you’ve selected the database and are ready to query the tables inside.</li> </ul> </li> </ol> <p>Note: It’s very important that you use the Castle&Sand database for all questions while you’re investigating activity at Castle&Sand! If you choose the wrong database, you won’t be able to answer questions correctly.</p> <p>The big space to the right of your cluster list is the <em>query workspace</em>. That’s where you’ll actually write the queries used to interact with our log data.</p> <p><img alt="Untitled-4" src="https://github.com/KC7-Foundation/kc7_data/assets/31902160/2233d447-08a4-47b6-8b9d-4d044ca3a53a" /></p> <p>Currently, you’ll see there’s a message there welcoming you to Castle&Sand! Click the blue Run button above the query workspace to run your first query! Once you’ve done that, you can erase the welcome message by highlighting it and pressing backspace or delete on your keyboard.</p> <p>Okay, enough introductions… let’s get your hands on the data.</p> <h4>First Look at the data...</h4> <p>The <strong>CastleSand</strong> database contains nine tables. Tables contain many rows of similar data. For security logs, a single row typically represents a single thing done by an employee or a device on the network at a particular time.</p> <p>We currently have nine types of log data. As you’ll see in ADX, each log type corresponds to a table that exists in the <strong>CastleSand</strong> database:</p> <table> <thead> <tr> <th><strong>Table Name</strong></th> <th><strong>Description</strong></th> </tr> </thead> <tbody> <tr> <td>Employees</td> <td>Contains information about the company’s employees</td> </tr> <tr> <td>Email</td> <td>Records emails sent and received by employees</td> </tr> <tr> <td>InboundNetworkEvents</td> <td>Records inbound network events including browsing activity from the Internet to devices within the company network</td> </tr> <tr> <td>OutboundNetworkEvents</td> <td>Records outbound network events including browsing activity from within the company network out to the Internet</td> </tr> <tr> <td>AuthenticationEvents</td> <td>Records successful and failed logins to devices on the company network. This includes logins to the company’s mail server.</td> </tr> <tr> <td>FileCreationEvents</td> <td>Records files stored on employee’s devices</td> </tr> <tr> <td>ProcessEvents</td> <td>Records processes created on employee’s devices</td> </tr> <tr> <td>PassiveDns (External)</td> <td>Records IP-domain resolutions</td> </tr> <tr> <td>SecurityAlerts</td> <td>Records security alerts from an employee’s device or the company’s email security system</td> </tr> </tbody> </table> <blockquote> <p>🎯<strong>Key Point – Over the Horizon (OTH) data</strong>: One of the tables listed above is not like the others – <strong>PassiveDns</strong>. Rather than being an internal security log, PassiveDns is a data source that we’ve purchased from a 3rd party vendor. Not all malicious cyber activity happens within our company network, so sometimes we depend on data from other sources to complete our investigations.</p> </blockquote> <p>You’ll learn more about how to use each of these datasets in just a minute. First, let’s just run some queries so you can practice using KQL and ADX.</p> <h4>KQL 101</h4> <p>Type the following query in the workspace to view the first rows in the <strong>Employees</strong> table. Press “run” or “shift + enter” to execute the query.</p> <div class="codehilite"><pre><span></span><code><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="n">take</span><span class="w"> </span><span class="mi">10</span> </code></pre></div> <p>This query has a few parts. Let’s take a moment to break each of them down:</p> <p><img alt="Diagram Description automatically generated with low confidence" src="https://lh4.googleusercontent.com/y7YpmVbUlak4wMrI43nYzAqieamRnwiANLJzy8UsxHHaYJI5SlJVpTdz4EO47A-g0SVI77ehgZxHB13AZXLKKX02Yr40VZmFsb3blZgHCojeD-vS34SS24yWWqK6rnduGNdhmnXlCAQAMWETrm7_hg" /></p> <table> <thead> <tr> <th><strong>Query Component</strong></th> <th><strong>Description</strong></th> </tr> </thead> <tbody> <tr> <td>Table Name</td> <td>The table name specifies which table/data source the query will pull data from. All queries must start with a table.</td> </tr> <tr> <td>Pipe character (|)</td> <td>The pipe character indicates the start of a new part of the query. A pipe will be added automatically after typing a table name and pressing enter. You can also add a pipe character manually by holding shift and pressing the backslash (\) key. That’s the one just below the backspace key.</td> </tr> </tbody> </table> <p>The <strong>take</strong> operator is a powerful tool you can use to explore rows in a table, and therefore better understand what kinds of data are stored there.</p> <blockquote> <p>🎯<strong>Key Point – What to do when you don’t know what to do</strong>: Whenever you are faced with an unfamiliar database table, the first thing you should do is sample its rows using the <strong>take</strong> operator. That way, you know what fields are available for you to query and you can guess what type of information you might extract from the data source.</p> </blockquote> <p>The Employees table contains information about all the employees in our organization. In this case, we can see that the organization is named “Castle&Sand” and the domain is “castleandsand.com”.</p> <blockquote> <ol> <li>🤔 Try it for yourself! Do a <strong>take</strong> 10 on all the other tables to see what kind of data they contain.</li> </ol> </blockquote> <p>Make sure you record your answer to all the questions from KQL 101 in the scoreboard at kc7cyber.com/scoreboard</p> <p>You can easily write multiple queries in the same workspace tab. To do this, make sure to separate each query by an empty line. Notice below how we have separated the queries for the <strong>Employees</strong>, <strong>Email</strong>, and <strong>OutboundNetworkEvents</strong> tables by adding empty lines between them.</p> <div class="codehilite"><pre><span></span><code><span class="n">Email</span> <span class="o">|</span><span class="w"> </span><span class="n">take</span><span class="w"> </span><span class="mi">10</span> <span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="n">take</span><span class="w"> </span><span class="mi">10</span> <span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="n">take</span><span class="w"> </span><span class="mi">10</span> </code></pre></div> <p>When you have multiple queries, it’s important to tell ADX which query you want to run. To choose a query, just click on any line that is part of that query. </p> <p><strong>Finding Out “How Many”: The count Operator</strong></p> <p>We can use <strong>count</strong> to see how many rows are in a table. This tells us how much data is stored there.</p> <div class="codehilite"><pre><span></span><code><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">count</span> </code></pre></div> <blockquote> <ol> <li>🤔How many employees are in the company?</li> </ol> </blockquote> <p><strong>Filtering Data With the <em>where</em> Operator</strong></p> <p>So far, we’ve run queries that look at the entire contents of the table. Often in cybersecurity analysis, we only want to look at data that meets a set of conditions or criteria. To accomplish this, we apply filters to specific columns.</p> <p>We can use the <strong>where</strong> operator in KQL to apply filters to a particular field. For example, we can find all the employees with the name “Jason” by filtering on the name column in the Employees table.</p> <p><strong>where</strong> statements are written using a particular structure. Use this helpful chart below to understand how to structure a <strong>where</strong> statement.</p> <table> <thead> <tr> <th><strong>where</strong></th> <th><strong>field</strong></th> <th><strong>operator</strong></th> <th><strong>"value"</strong></th> </tr> </thead> <tbody> <tr> <td>where</td> <td>name</td> <td>has</td> <td>"Jason"</td> </tr> </tbody> </table> <div class="codehilite"><pre><span></span><code><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"Jason"</span> </code></pre></div> <p>The <strong>has</strong> operator is useful here because we’re looking for only a partial match. If we wanted to look for an employee with a specific first and last name (an exact match), we’d use the == operator:</p> <div class="codehilite"><pre><span></span><code><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="o">==</span><span class="w"> </span><span class="ss">"Jason Goldsmith"</span> </code></pre></div> <blockquote> <ol> <li>🤔Each employee at Castle&Sand is assigned an IP address. Which employee has the IP address: “10.10.2.1”?</li> </ol> </blockquote> <p>Here are some additional operators we like to use:</p> <table> <thead> <tr> <th>Operator</th> <th>Description</th> <th>Case-Sensitive</th> <th>Example (yields true)</th> </tr> </thead> <tbody> <tr> <td>==</td> <td>Equals</td> <td>Yes</td> <td>"aBc" == "aBc"</td> </tr> <tr> <td>!=</td> <td>Not equals</td> <td>Yes</td> <td>"abc" != "ABC"</td> </tr> <tr> <td>=~</td> <td>Equals</td> <td>No</td> <td>"abc" =~ "ABC"</td> </tr> <tr> <td>contains</td> <td>Right-hand-side (RHS) occurs as a subsequence of left-hand-side (LHS)</td> <td>No</td> <td>"FabriKam" contains "BRik"</td> </tr> <tr> <td>has</td> <td>RHS is a whole term in LHS</td> <td>No</td> <td>"North America" has "america"</td> </tr> <tr> <td>has_all</td> <td>Same as has but works on all of the elements</td> <td>No</td> <td>"North and South America" has_all("south", "north")</td> </tr> <tr> <td>has_any</td> <td>Same as has but works on any of the elements</td> <td>No</td> <td>"North America" has_any("south", "north")</td> </tr> <tr> <td>in</td> <td>Equals to any of the elements</td> <td>Yes</td> <td>"abc" in ("123", "345", "abc")</td> </tr> </tbody> </table> <p>While performing their day-to-day tasks, Castle&Sand employees send and receive emails. A record of each of these emails is stored in the <strong>Email</strong> table.</p> <blockquote> <p>🎯<strong>Key Point – User Privacy and Metadata</strong>: As you can imagine, some emails are highly sensitive. Instead of storing the entire contents of every email sent and received within the company in a database that can be easily accessed by security analysts, we only capture email metadata. </p> </blockquote> <p>Email metadata includes information like: the time the email was sent, the sender, the recipient, the subject line, and any links the email may contain. Storing only email metadata, rather than entire contents, helps protect the privacy of our employees, while also ensuring that our security analysts can keep us safe. Sometimes even metadata can reveal sensitive information, so it’s important that you don’t talk about log data with other employees outside the SOC.</p> <p>We can find information about the emails sent or received by a user by looking for their email address in the sender and recipient fields of the <strong>Email</strong> table. For example, we can use the following query to see all the emails sent by “Jason Goldsmith”:</p> <div class="codehilite"><pre><span></span><code><span class="n">Email</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">sender</span><span class="w"> </span><span class="o">==</span><span class="w"> </span><span class="ss">"jason_goldsmith@castleandsand.com"</span> </code></pre></div> <blockquote> <ol> <li>🤔How many emails did Jacqueline Henderson receive?</li> </ol> </blockquote> <p><strong>Easy as 1, 2, 3… Compound Queries and the distinct Operator</strong></p> <p>We can use the <strong>distinct</strong> operator to find unique values in a particular column. We can use the following query to determine how many of the organization’s users sent emails.</p> <div class="codehilite"><pre><span></span><code><span class="n">Email</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">sender</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"castleandsand.com"</span> <span class="o">|</span><span class="w"> </span><span class="k">distinct</span><span class="w"> </span><span class="n">sender</span> <span class="o">|</span><span class="w"> </span><span class="k">count</span> </code></pre></div> <p>This is our first time using a multi-line query with multiple operators, so let’s break it down:</p> <p>In line 2, we take the Email table and filter the data down to find only those rows with “castleandsand.com” in the sender column.</p> <p>In line 3, we add another pipe character ( | ) and use the distinct operator to find all the unique senders. Here, we aren’t finding the unique senders for all of the email senders, but only the unique senders that are left after we apply the filter looking for rows with “castleandsand.com” in the sender column.</p> <p>Finally, in line 4, we add another pipe character ( | ) and then use the count operator to count the results of lines 1-3 of the query.</p> <blockquote> <ol> <li>🤔 How many distinct senders were seen in the email logs from sunandsandtrading.com?</li> </ol> </blockquote> <p><strong>Tracking Down a Click: OutboundNetworkEvents Data</strong></p> <p>When employees at Castle&Sand browse to a website from within the corporate network, that browsing activity is logged. This is stored in the <strong>OutboundNetworkEvents</strong> table, which contains records of the websites browsed by each user in the company. Whenever someone visits a website, a record of it is stored in the table. However, the user’s name is not stored in the table, only their IP address is recorded. There is a 1:1 relationship between users and their assigned IP addresses, so we can reference the <strong>Employees</strong> table to figure out who browsed a particular website. When a user visits a site, sometimes data from a lot of other sources are loaded as well. For example, images, assets, and other content may be hosted on content delivery network (CDN), which is used to deliver and load content quickly on a website. Sometimes, advertisements will also load from a particular website as well.</p> <p>If we want to figure out what websites Cristin Genao visited, we can find their IP address from the Employees table.</p> <div class="codehilite"><pre><span></span><code><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="o">==</span><span class="w"> </span><span class="ss">"Cristin Genao"</span> </code></pre></div> <p>The query above tells us their IP address is “10.10.0.84”. We can take their IP address and look in the <strong>OutboundNetworkEvents</strong> table to determine what websites they visited.</p> <div class="codehilite"><pre><span></span><code><span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">src_ip</span><span class="w"> </span><span class="o">==</span><span class="w"> </span><span class="ss">"10.10.0.84"</span> </code></pre></div> <blockquote> <ol> <li>🤔 How many unique websites did “Cristin Genao” visit?</li> </ol> </blockquote> <p><strong>What’s in a Name? All about Passive DNS Data</strong></p> <p>Although domain names like “google.com” are easy for humans to remember, computers don’t know how to handle them. So, they convert them to machine readable IP addresses. Just like your home address tells your friends how to find your house or apartment, an IP address tells your computer where to find a page or service hosted on the internet.</p> <blockquote> <p>🎯<strong>Key Point – Practice Good OPSEC</strong>: If we want to find out which IP address a particular domain resolves to, we could just browse to it. But, if the domain is a malicious one, you could download malicious files to your corporate analysis system or tip off the attackers that you know about their infrastructure. As cybersecurity analysts, we must follow procedures and safeguards that protect our ability to track threats. These practices are generally called operational security, or OPSEC.</p> </blockquote> <p>To eliminate the need to actively resolve (that is- directly browse to or interact with a domain to find it’s related IP address) every domain we’re interested in, we can rely on passive DNS data. Passive DNS data allows us to safely explore domain-to-IP relationships, so we can answer questions like:</p> <ul> <li><em>Which IP address does this domain resolve to?</em></li> <li><em>Which domains are hosted on this IP address?</em></li> <li><em>How many other IPs have this domain resolved to?</em></li> </ul> <p>These domain-to-IP relationships are stored in our <strong>PassiveDns</strong> table.</p> <blockquote> <ol> <li>🤔 How many domains in the PassiveDns records contain the word “shark”? (hint: use the contains operator instead of has. If you get stuck, do a take 10 on the table to see what fields are available.)</li> <li>🤔 What IPs did the domain “sharkfin.com” resolve to (enter any one of them)?</li> </ol> </blockquote> <p><strong>🤯Let statements – making your life a bit easier:</strong></p> <p>Sometimes we need to use the output of one query as the input for a second query. The first way we can do this is by manually typing the results into the next query.</p> <p>For example, what if we want to look at all the web browsing activity from employees named “James”?</p> <p>First, you would need to go into the <strong>Employees</strong> table and find the IP addresses used by these employees.</p> <div class="codehilite"><pre><span></span><code><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"James"</span> </code></pre></div> <p><img alt="image" src="https://github.com/KC7-Foundation/kc7_data/assets/31902160/07394f32-7c14-4502-b348-dd49243e2f68" /></p> <p>Then, you could manually copy and paste these IPs into a query against the <strong>OutboundNetworkEvents</strong> table. Note that we can use the in operator to choose all rows that have a value matching any value from a list of possible values. In other words, the == (comparison) operator looks for an exact match, while the in operator checks for any values from the list</p> <div class="codehilite"><pre><span></span><code><span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">src</span><span class="o">-</span><span class="n">ip</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="p">(</span><span class="ss">"10.10.3.102"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.3.48"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.4.37"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.2.143"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.2.143"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.3.155"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.5.180"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.1.178"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.5.67"</span><span class="p">,</span> <span class="w"> </span><span class="ss">"10.10.1.221"</span><span class="p">)</span> </code></pre></div> <p>Although this is a valid way to get the information you need, it may not be as elegant (or timely) if you had 100 or even 1000 employees named “James.”</p> <p>We can accomplish this in a more elegant way by using a <a href="https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/letstatement">let statement,</a> which allows us to assign a name to an expression or a function. We can use a let statement here to save and give a name to the results of the first query so that the values can be re-used later. That means we don’t have to manually type or copy and paste the results repeatedly.</p> <div class="codehilite"><pre><span></span><code><span class="n">let</span><span class="w"> </span><span class="n">james_ips</span><span class="w"> </span><span class="o">=</span><span class="w"> </span> <span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"James"</span> <span class="o">|</span><span class="w"> </span><span class="k">distinct</span><span class="w"> </span><span class="n">ip_addr</span><span class="p">;</span> <span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">src_ip</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="p">(</span><span class="n">james_ips</span><span class="p">)</span> </code></pre></div> <p>On the left of the let statement is the variable name (“james_ips” in this case). The variable name can be whatever we want, but it is helpful to make it something meaningful that can help us remember what values it is storing.</p> <div class="codehilite"><pre><span></span><code><span class="hll"><span class="n">let</span><span class="w"> </span><span class="n">james_ips</span><span class="w"> </span><span class="o">=</span><span class="w"> </span> </span><span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"James"</span> <span class="o">|</span><span class="w"> </span><span class="k">distinct</span><span class="w"> </span><span class="n">ip_addr</span><span class="p">;</span> <span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">src_ip</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="p">(</span><span class="n">james_ips</span><span class="p">)</span> </code></pre></div> <p>On the right side of the let statement in the expression you are storing. In this case, we use the <strong>distinct</strong> operator to select values from only one column – so they are stored in an array – or list of values.</p> <div class="codehilite"><pre><span></span><code><span class="n">let</span><span class="w"> </span><span class="n">james_ips</span><span class="w"> </span><span class="o">=</span><span class="w"> </span> <span class="hll"><span class="n">Employees</span> </span><span class="hll"><span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"James"</span> </span><span class="hll"><span class="o">|</span><span class="w"> </span><span class="k">distinct</span><span class="w"> </span><span class="n">ip_addr</span><span class="p">;</span> </span><span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">src_ip</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="p">(</span><span class="n">james_ips</span><span class="p">)</span> </code></pre></div> <p>The <strong>let</strong> statement is concluded by a semi-colon.</p> <div class="codehilite"><pre><span></span><code><span class="n">let</span><span class="w"> </span><span class="n">james_ips</span><span class="w"> </span><span class="o">=</span><span class="w"> </span> <span class="n">Employees</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="n">has</span><span class="w"> </span><span class="ss">"James"</span> <span class="hll"><span class="o">|</span><span class="w"> </span><span class="k">distinct</span><span class="w"> </span><span class="n">ip_addr</span><span class="p">;</span> </span><span class="n">OutboundNetworkEvents</span> <span class="o">|</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">src_ip</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="p">(</span><span class="n">james_ips</span><span class="p">)</span> </code></pre></div> <p>After we store the value of a query into a variable using the <strong>let</strong> statement, we can refer to it as many times as we like in the rest of the query. The stored query does not show any output. Remember, however, that your KQL query must have a tabular statement – which means that you must have another query following your <strong>let</strong> statement.</p> <blockquote> <ol> <li>🤔 How many unique URLs were browsed by employees named “Karen”?</li> </ol> <p>🎯<strong>Key Point – Pivoting:</strong> Part of being a great cyber analyst is learning how to use multiple data sources to tell a more complete story of what an attacker has done. We call this “pivoting.” We pivot by taking one known piece of data in one dataset and looking in a different dataset to learn something we didn’t already know. You practiced this here when we started in one dataset – the Employees table – and used knowledge from there to find related data in another source – OutboundNetworkEvents.</p> </blockquote> <h2>Section 2: Start Hunting!</h2> <p>You’ve finished your training and you’re ready to get to work protecting Castle&Sand. </p> <p>Work with your team to complete as many challenge questions from the remaining sections in the scoreboard as possible! The goal is to score as many points as you can. There are a lot of questions (the attackers have been busy), so you probably won’t be able to answer them all. Just do as many as you can!</p> <p>As you answer the questions, we will take you on a journey exploring the data and discovering what actions the adversaries have taken. However, you should remember that this is only one of many paths you can take through the data. As you go, don’t forget to pay attention to the details along the way. What patterns do the attackers exhibit that could help you track them better? Do they like to use certain words, themes? Or do they make mistakes? Keeping track of these patterns will help you build the full picture of what happened. </p> <p>Use the provided <a href="https://docs.google.com/document/d/1rZR4eVG886oPziG-5nGeQ5kN_q5Bpq0m/edit?usp=sharing&ouid=105873493764084037775&rtpof=true&sd=true">Actor Preview</a> document to keep track of what you know about the attacker. Building a good profile, timelining the attacker’s activity, and forming a list of indicators of compromise (IOCs) will help you keep track of the attacker. KC7 models some of the techniques used by these attackers from real world threat actors, so it may be a helpful resource for you in the future when you are investigating a real security incident. </p> <p>Now, get out there and keep us safe! The whole company is counting on you. No pressure😊.</p> <h2>Nice to Know</h2> <p>Here are some things that might be helpful to know as we explore the CastleSand Database:</p> <p><strong>Business hours</strong></p> <p>We operate our business from 9am to 5pm, 7 days a week. We should roughly expect to see all legitimate business activity within that timeframe (with some exceptions, of course). Let's be vigilant about any activity that occurs on the network while everyone sleeps ;)</p> <p><strong>Pay attention to the IT Workers</strong></p> <p>Our IT staff at Castle&Sand are granted elevated privileges on machines across the company in order to fulfill their duties. As a result, it can be concerning when their machines get compromised. We, at Castle&Sand, are a security-aware organization, so we do not grant local administrative privileges to users on their computers. However, each laptop and desktop at Castle&Sand is provisioned with a separate local administrative account - which looks something like "marketing_local_admin." These administrator accounts are provisioned with the same username and password per department. For example, all employees in the Marketing department have a local administrator account with the username "marketing_local_admin" and the same password.</p> <p><strong>Castle&Sand uses Active Directory</strong></p> <p>We utilize Active Directory, a directory service developed by Microsoft for Windows networks. It provides us with a centralized and standardized system for managing and organizing network resources, such as user accounts, computers, groups, and other network objects.</p> <p>Unfortunately, compromising the Active Directory Domain Controller would give an adversary credentials for all accounts on our network and enable them to cause severe harm.</p> <p><strong>Alerts and Alert Fatigue</strong></p> <p>In the CastleSand database, we have access to a <strong>SecurityAlerts</strong> table that shows suspicious security events that have occurred on the Castle&Sand network. This can be a great place to begin searching for malicious activity. However, we need to be aware that some of these alerts are false positives, which means they may indicate non-malicious activity. Let's be careful not to waste too much time chasing after these.</p> <h2>Mitre ATT&CK Techniques</h2> <p>The MITRE ATT&CK(TM) framework is a knowledgebase of adversary techniques observed in real-world intrusions. The framework consists of tactics, techniques, and subtechniques. Each of these elements of the framework is assigned a number. You can access the MITRE ATT&CK framework <a href="https://attack.mitre.org">here</a>.</p> <p>The following Mitre ATT&CK techniques were used by the actors in the observed intrusions:</p> <p><strong>Reconnaissance</strong> </p> <ul> <li>T1594 - Search Victim-Owned Websites - Adversaries may search websites owned by the victim for information that can be used during targeting. Victim-owned websites may contain a variety of details, including names of departments/divisions, physical locations, and data about key employees such as names, roles, and contact info (ex: Email Addresses). These sites may also have details highlighting business operations and relationships.</li> </ul> <p><strong>Resource Development</strong> </p> <ul> <li>T1584 - Compromise Infrastructure - Adversaries may compromise third-party infrastructure that can be used during targeting. Infrastructure solutions include physical or cloud servers, domains, and third-party web and DNS services. Instead of buying, leasing, or renting infrastructure an adversary may compromise infrastructure and use it during other phases of the adversary lifecycle.</li> </ul> <p><strong>Initial Access</strong> </p> <ul> <li>T1189 - Drive-by Compromise - Adversaries may gain access to a system through a user visiting a website over the normal course of browsing. With this technique, the user's web browser is typically targeted for exploitation, but adversaries may also use compromised websites for non-exploitation behavior such as acquiring Application Access Token.</li> <li>T1566 - Phishing - Adversaries may send phishing messages to gain access to victim systems. All forms of phishing are electronically delivered social engineering. Phishing can be targeted, known as spearphishing. In spearphishing, a specific individual, company, or industry will be targeted by the adversary. More generally, adversaries can conduct non-targeted phishing, such as in mass malware spam campaigns.</li> <li>T1195 - Supply Chain Compromise - Adversaries may manipulate products or product delivery mechanisms prior to receipt by a final consumer for the purpose of data or system compromise.</li> </ul> <p><strong>Execution</strong> </p> <ul> <li>T1047 - Windows Management Instrumentation - Adversaries may abuse Windows Management Instrumentation (WMI) to execute malicious commands and payloads.</li> <li>T1053 - Scheduled Task/Job - Adversaries may abuse task scheduling functionality to facilitate initial or recurring execution of malicious code.</li> </ul> <p><strong>Persistence</strong> </p> <ul> <li>T1574 - Hijack Execution Flow - Adversaries may execute their own malicious payloads by hijacking the way operating systems run programs.</li> <li>T1078 - Valid Accounts - Adversaries may obtain and abuse credentials of existing accounts as a means of gaining Initial Access, Persistence, Privilege Escalation, or Defense Evasion.</li> </ul> <p><strong>Privilege Escalation</strong> </p> <ul> <li>T1078 - Valid Accounts - Adversaries may obtain and abuse credentials of existing accounts as a means of gaining Initial Access, Persistence, Privilege Escalation, or Defense Evasion.</li> <li>T1484 - Domain Policy Modification - Adversaries may modify the configuration settings of a domain to evade defenses and/or escalate privileges in domain environments.</li> </ul> <p><strong>Defense Evasion</strong> </p> <ul> <li>T1070 - Indicator Removal - Adversaries may delete or modify artifacts generated within systems to remove evidence of their presence or hinder defenses.</li> <li>T1574 - Hijack Execution Flow - Adversaries may execute their own malicious payloads by hijacking the way operating systems run programs.</li> <li>T1562 - Impair Defenses - Adversaries may maliciously modify components of a victim environment in order to hinder or disable defensive mechanisms.</li> </ul> <p><strong>Credential Access</strong> </p> <ul> <li>T1110 - Brute Force - Adversaries may use brute force techniques to gain access to accounts when passwords are unknown or when password hashes are obtained.</li> <li>T1003 - OS Credential Dumping - Adversaries may attempt to dump credentials to obtain account login and credential material, normally in the form of a hash or a clear text password, from the operating system and software.</li> </ul> <p><strong>Discovery</strong> </p> <ul> <li>T1087 - Account Discovery - Adversaries may attempt to get a listing of valid accounts, usernames, or email addresses</li> </ul> <h2>Glossary</h2> <p><strong>Watering hole:</strong> A type of attack where a hacker compromises a website that is frequently visited by a specific group of users, such as employees of a certain organization, and then infects their devices with malware when they visit the site.</p> <p><strong>Phishing:</strong> A type of attack where a hacker sends an email or other message that appears to be from a legitimate source, such as a bank or a colleague, and tries to trick the recipient into clicking on a malicious link, opening an attachment, or providing sensitive information.</p> <p><strong>Credential theft:</strong> A type of attack where a hacker steals or obtains the username and password of a user or an administrator, and then uses them to access their accounts or systems.</p> <p><strong>Password spray:</strong> A type of attack where a hacker tries to guess the passwords of multiple accounts by using common or weak passwords, such as “password” or “123456”, instead of targeting one account with many password attempts.</p> <p><strong>Reconnaissance:</strong> The process of gathering information about a target system, network, organization, or user before launching an attack. This can include scanning for open ports, identifying vulnerabilities, mapping network topology, collecting email addresses, etc.</p> <p><strong>Supply chain compromise:</strong> A type of attack where a hacker infiltrates the software development process or distribution channel of a trusted vendor or partner and inserts malicious code into their products or services. This way, the hacker can compromise the customers or users who install or use those products or services.</p> <p><strong>Malware:</strong> A general term for any software that is designed to harm or disrupt a system, network, device, or data. Malware can include viruses, worms, trojans</p> <p><strong>Command and control:</strong> A type of server or network that is used by hackers to communicate with and control their malware or botnets on compromised systems, networks, or devices. Command and control servers can send commands, receive data, update malware, or launch attacks.</p> <p><strong>Adversary in the middle:</strong> A type of attack where a hacker intercepts and modifies the communication between two parties, such as a user and a website, without their knowledge. Adversary in the middle attacks can be used to steal or alter data, redirect traffic, inject malware, or impersonate either party.</p> <p><strong>Top level domain:</strong> The highest level of domain names in the Internet’s domain name system. Top level domains are the last part of a domain name after the dot, such as .com, .org, .edu, etc. Top level domains can indicate the type or purpose of a website or its geographic location.</p> <h2>Resources</h2> <p>Understanding KQL operators: <a href="https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/datatypes-string-operators">https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/datatypes-string-operators</a></p>