It has been a little while since I have had time to write a blog post, but with the long Thanksgiving weekend, I found myself with the opportunity to complete and reflect upon a course that I enrolled in from BluRaven entitled, “Advanced Hands-On KQL for Threat Hunting and Detection Engineering from Scratch”. I decided to enroll in the course having a moderate comfort level in Kusto Query Language (KQL), finding myself able to mine information from both Microsoft Sentinel and Defender without too much trouble. Given this, I was a little hesitant in the “from scratch” portion of the course, figuring it would be little more than a review of commands and statements that I already knew.
About the Course
I took advantage of a Black Friday sale, which offered the Advanced Hands-On KQL for Threat Hunting and Detection Engineering from Scratch course for €499 minus a 35% discount, bringing the total cost to approximately $350 USD. Within 1 hour, I had credentials and an invitation to connect to BluRaven’s Azure Data Explorer instance and was able to launch. My plan was to launch the course from my work computer, that was connected to an Azure Active Directory instance already. As warned during the setup instructions, I was not able to connect using my work computer and alternate AAD credentials. I created a throwaway email on Outlook.com and used that account to configure and establish my environment on a non-work computer. Within 2 hours and a pair of emails to BluRaven, I was cooking with gas.
Course Structure
Because I bought the “from Scratch” version of the courseware, I was taken to the basic course material, where I learned about databases versus spreadsheets, as well as some basic familiarity with Microsoft Sentinel and Microsoft 365 Defender. During this material, I was refreshed about how Kusto worked and fundamental principles of databases, but it was not long before I was transported to the material comprising the heart of the course. Yes, this section was a review, but for someone without any experience in KQL, databases, or spreadsheets, the material was a good refresher to reinforce key concepts pertaining to databases.
With the basics out of the way, it was time to begin diving KQL syntax and exploring data within the tables. The lessons started with basic query statements and defining the various data types in KQL, and before long, I was writing basic KQL queries. The role of the pipe command was explored for sequential data processing. To reinforce the material, BluRaven asks a handful of questions that required writing KQL queries to solve. The questions mimicked real-life sample queries that I would run in the field. After accepting the input for the questions, the material provided walkthroughs of the answers so that any corrections or problems can be identified and eliminated.
Next, the course dove into various techniques to search and filter data in a Kusto query. Beginning with the ‘search’ operator, and ending with an in-depth look at the ‘where’ operator, the course combined theory and application to teach the learner about the key roles that these two operators serve in finding the appropriate fields to display and filter off of. Material covered logical, numeric, and string operators and how to manipulate and interrogate those fields, as well as an introduction to scalar and IPv4 Functions, as well as working with JSON data. The lesson completed with over 10 questions about the environment, whereby the learner is to complete the exercises. Again, after each exercise, BluRaven provided a walkthrough for solving the questions.
The next lesson dove into creating and manipulating fields, covering parse, project, and extend operators, as well as defining scalar functions for creating fields. This section was really good, and I learned that the todynamic function is being deprecated in favor of the parse_json function. After a short exercise, I proceeded to the next section of the course material: combining data sets. In the combining datasets section, the course explored the use of the Union operator, and provided solid recommendations for using union in my KQL queries.
The next section of the course material extended the application of the Union operator by introducing the concept of joining datasets using the join operator. Each join type is covered in detail, including the seldomly used Semi and Anti joins, along with best practices for joining datasets. For each join type, BluRaven provided concrete examples of the join, its effects, and any considerations when using the join. The section wrapped up talking about best practices and how to choose the correct join type for your queries.
At this point, I would say that I was beginning to transform from a KQL novice to an advanced practitioner. The next several lessons introduced concepts that I had never used in my own KQL queries. Beginning with using external threat intelligence feeds and the external data operator to enrich and provide additional detail to the information stored in the tables. The externaldata operator was thoroughly explained, and examples provided that pulled information from .csv files (with headers and without). Finally, BluRaven demonstrated how to massage the data from the external file as a semi-structured IOC list. After explaining how to pull data from external files, BluRaven introduced the concept of time traveling, a concept that allows the analyst to turn back the system clock. This was seriously cool stuff for me, as it allows me to perform backwards and forwards retrospectives.
Additional advanced concepts awaited me in the next section, where I learned about aggregating data using dynamic, statistical, row selector, and conditional aggregation functions. Items such as make_list and make-set were described and illustrated, as well as count and dcount, arg_max and arg_min, and make_set_if() functions were explained. When completed with that section, the final approach began, with dedicated modules for anomaly detection and Time-series anomaly detection, before finishing the course material with Attack Flows with Process Mining and using Graph semantics to identify attack patterns. In these lessons, BluRaven explains the scan operator, as well as match-graph and other very advanced concepts.
Overall Impression of the Course
I will be honest. I had very high expectations for this course, especially since I was coming out of my own pocket for the costs. I felt that the skills developed would be a force multiplier in my own querying and analysis pursuits. I am happy to report that the course was everything I could ask for and more. BluRaven provided an excellent environment to learn, and the lessons were both technically accurate, free from typos and other grammatical errors, and presented in a very engaging and linear fashion. From the learner perspective, I always knew where I was in relation to the lesson and the overall course.
The course was expertly paced and the concepts were presented in a manner that simply made sense. I assessed my KQL skills at about a 5 heading into the course, and if I am being honest, I feel as though I was closer to a 3. However, upon coming out of the course, I would have to say that my skills are hovering between an 8 or a 9, most of which can be attributed to not having the opportunity to stretch my legs with advanced concepts in my day job.
Support was first-class when I was attempting to launch. BluRaven was extremely accommodating and patient as I worked through getting access to the security data sets used in the examples. I was a little disappointed that I could not launch from my work machine, but that only affected my educational pursuit slightly. Once I was connected to the datasets, everything fell into place very nicely.
Finally, upon completion of the course, you will receive a certificate of completion worth 40 CPE credit hours towards your certifications. Technically, I suppose it could be framed, but if I am being honest, I would say the certificate could use a facelift.
Recommendations for the Course
I can not recommend this course highly enough if you are responsible for querying data from Sentinel and Defender. If you choose to enroll in this course, here are a couple of tips:
- Type out each example, and resist doing copy/pasta of the code. I found that I retained a lot more of the information when I invested the time in typing out the KQL queries.
- Find a quiet place to absorb the material. There are no videos in the material, so there is a fair amount of reading and comprehending. Go somewhere where you can concentrate.
- The course will take 40 hours if you are moving through the lessons at a normal pace.
- Pay attention in lessons 11-14. This is advanced KQL-fu, and it took me a couple of times through to realize the power of the operators covered in these lessons.
