0.7 How to Learn SPL: the Mastery Ladder

Introduction

SPL is learned in layers, each built on the ones beneath it. A first search reads and filters; the next shapes and summarizes; later ones combine sources, take text apart, reuse what was built, follow events through time, search data models, and check the answer. Each layer uses everything beneath it, and the course is built as a ladder with one module per rung.

This section shows the ladder by asking one question, about the month's failed sign-ins, at each rung in turn, and sets out the habits that make the climb work.

You will count the failures, break them down by application, by department through a lookup, by partner firm through text taken apart, through a macro, by day, through a data model, and finally check that the parts add up. By the end you should know what each module adds, how the rungs depend on each other, and how to learn each new command the course introduces.

The diagram is the course's structure, one module per rung. The first four rungs are the foundations every analyst uses daily; the next five turn those foundations into the searches that find attacks and can be trusted.

The first habit is the one the course asks for most often, in the predictions before its searches. The last is the one it asks for most seriously, because a wrong answer reported is worse than no answer.

None of the habits is specific to SPL. They are how any query language, and most technical skills, are learned well; this course makes them routine by building them into every section's searches and exercises.

The card is how the course teaches, and how to learn from it most quickly and lastingly. Each line is a habit, and the sections below show them at work.

The first rung reads and filters; the third summarizes. Rung two, shaping with eval and table, sits between them and is used throughout.

index=azuread category=SignInLogs action=failure
| stats count

action=failure is the term that makes this a question about failures; without it, the same search counts all 8,203 sign-ins. One term in the base search sets the subject of everything that follows.

The 338 will appear in several forms in this section, split by application, department, partner, day and country. Each split is a different question about the same failures, apart from the data model's count, which reads more sources than Entra ID.

The first rung's whole search is a base search and a count. Everything the course teaches later starts from a base search like this one, and many of its hardest searches still end with a count.

One rung up, stats groups the same failures by the application they were for.

index=azuread category=SignInLogs action=failure
| stats count by app
| sort - count
| head 3

Office 365 in general, with 42 failures, is the front door every Microsoft sign-in can use, and the spray's 19 attempts went there. SharePoint's 37 are failed attempts to reach shared files.

sort and head kept the three largest groups, and the other applications are below them. A top three is a quick summary and never the whole story; the full list is one head away.

Exchange Online's 83 failures match the guessing's 83, and the match is not chance: the guessing was against r.scott's mailbox. A breakdown at one rung often confirms a finding from another.

Two rungs, two answers: a count, and where the failures happen. The question is the same; the second rung says more because stats can group.

Rung two, shaping, appears in nearly every search from here on: eval to compute a field, table to choose columns, rename to make a result readable. It has no search of its own in this list because every rung above uses it.

Rung four brings in data the events do not hold; rung five takes apart data the events hold in awkward forms, inside names and lists.

index=azuread category=SignInLogs action=failure
| lookup identity user OUTPUT department
| stats count by department
| sort - count
| head 3

Guests and some service accounts have no row in the identity table, so their failures have no department and fall out of this count. The departments add up to 324, exactly the staff's failures from rung five; the guests' 14 have no department.

The lookup added a department to each failure, and stats grouped by it. The identity table's own columns, department, job title, manager, city, are all available the same way.

Marketing's 113 are mostly r.scott's 89, so the department's lead is one person's attack, not a department-wide problem. A combined result is read with the uncombined one beside it, which the top rung makes a habit.

A department for each account, from a table the events never mention. Module 5 teaches lookups and the other ways of combining sources.

index=azuread category=SignInLogs action=failure
| rex field=user "_(?<home_domain>[^#]+)#EXT#"
| eval home_domain=coalesce(home_domain, "ne.com (staff)")
| stats count by home_domain
| sort - count

The three partners are an engineering firm in Germany, a marine company and an audit firm, judging by their domains. Their 14 failures look like ordinary guests mistyping, spread thinly over the month, and a later search can set them aside with a reason.

coalesce gave staff a label of their own, because their names have no partner domain for rex to find. Without it, staff failures would have no value in the by field and drop out of the count, the empty-field trap Module 9 meets in tstats.

The rex pattern reads the part of the name between the underscore and #EXT#, where Entra ID writes a guest's own domain. Patterns like this are the core of Module 6, and they are easier to write than to read at first sight.

A partner firm for each guest, from inside the account name. Module 6 teaches rex and the multivalue commands that take text and lists apart.

The order of rungs is also the order of a search's steps: read, enrich, unpack, then summarize. A search that summarizes before enriching has nothing to summarize by.

The broken search returned nothing at all: stats grouped by a field no event had, so there were no groups. An empty result after a by clause is the cue to check that the field exists at that point in the search.

The fix adds the lookup before the stats that needs its field. Rungs are not only levels of difficulty; they are an order, and a search that uses a field before the step that creates it finds nothing.

Rungs four and five are where security searches start to differ from general reporting. Real security data rarely holds everything a question needs in one clean field, and combining and unpacking are how the missing parts are found.

The middle and upper rungs make searches reusable, put events in time, and reach many sources at once through shared names.

`signins` action=failure
| stats count

The macro takes no arguments here. Module 7 also writes macros that do, such as a threshold or a field name passed in, which turns one search into a family of searches.

A macro changes nothing about the answer and a great deal about upkeep. If the sign-ins moved to another index, one macro would change instead of every search that names the index.

The macro's name in backticks is replaced by its definition before the search runs. In the lab, the course's macros are defined in its knowledge-object app; at work, a team's macros live in Splunk's settings.

The same 338, through a macro. Module 7 covers macros, lookup definitions, event types and the other knowledge objects a team builds once and shares.

index=azuread category=SignInLogs action=failure
| bin _time span=1d
| stats count by _time
| sort - count
| head 2

The two days are three days apart, a Monday and a Thursday. Two attacks in one week, with different shapes and from different countries, is the kind of pattern a timeline makes visible and a total hides.

bin set each failure's time to the start of its day, so stats could count per day. The same two commands with a different span answer per hour or per week, the starting point of every timeline in Module 8.

88 failures on 2 March: the guessing's 83 and five ordinary ones. 24 on 5 March: the spray's 19 and five more. A day's count is a sum of everything on that day, and the breakdowns at other rungs say what it is made of.

Two days stand out. Module 8 teaches bins, baselines, running windows and sequences, the tools for turning events into a timeline.

The day with most failures, 2 March, is a Monday. An attack at the start of a week, at ten at night, is a pattern the course's time searches make easy to see.

span=1d makes calendar days; span=1h would make hours, and Section 0.6's weekday count used strftime instead of bin. Module 8 compares the ways of putting events in time and when each is right.

The completion exercise is bin. Most questions about attacks become questions about time at some point, and bin is where that starts.

| tstats count from datamodel=Authentication.Failed_Authentication
    by sourcetype

The Entra ID share, 338, is the same failures every other rung counted. The model's total is larger because it reads more sources, not because it counts differently, and a by sourcetype is what shows that.

The search begins with a pipe and tstats, reading the model's records rather than events. It is the fastest search in this section and the hardest to check without the slower ones beside it.

The other three sources add 79: AWS CloudTrail 52, Defender 15 and the Windows Security log 12. The model brought them together under one set of names, which is its purpose and, as Module 9 finds, also its risk.

417 failures across four sources, more than Entra ID alone. Module 9 explains data models, what they include and what they leave out.

The three rungs in this section are where searches stop being single questions and become tools: a macro a team shares, a timeline an investigation follows, a data model a detection reads every five minutes.

The top rung is not a new command but a habit: checking every answer before using it, however simple the search that produced it.

index=azuread category=SignInLogs action=failure
| stats count by src_country
| eventstats sum(count) as total

Five countries: Great Britain, Lithuania, Germany, Switzerland and the Netherlands. The same breakdown in Section 0.5 found two attacks in it; here it is the check.

eventstats wrote the total onto every row, so the check is visible in the result itself. The top rung's searches are often this small: one more command that proves the rest.

The country breakdown's total, 338, matches rung one's count from a different search. Two routes to one number is the strongest single check, and the top rung uses it often.

The parts add to 338, the total from rung one. Module 10 teaches six such checks, along with the costs and limits of searches, and closes the language by reading searches written by others.

The first mistake is the easiest to fall into with a course full of working searches. Running each one, nodding, and moving on teaches very little; predicting first, even badly, teaches a great deal.

The second mistake is tempting because the higher rungs are where the interesting searches are. A tstats search written without stats behind it is hard to check and harder to fix, because its author cannot reach the same answer the slow way.

The third mistake is the one the top rung exists for. A search that runs has produced a result, not an answer; the 417 at rung eight is a true count of failures the model holds, and not the month's failures from every source.

Verification is placed at the top because it needs everything below it. Checking an answer means reaching it another way, and the more rungs an analyst can use, the more other ways there are.

Every command in the course is learned the same way, simple or advanced: run it as given, predict a variation, use it on a new question, and find where it stops working.

The first step means reading every column of the result, not only the one the search was for. Extra columns often answer the next question before it is asked.

The second step is the one that turns reading into learning. Predicting what a changed argument will do, and finding out, exposes a misunderstanding in seconds that reading the reference might never reveal.

The fourth step is where understanding becomes reliable. A command whose limits are known, empty fields, large data, default settings, can be trusted; one used only on the examples that work cannot.

The course's sections follow the same four steps for each command they introduce, so the method is visible in every lesson. After a few modules it becomes the natural way to meet any new command, at work as much as in the course.

Each section's searches ask for a prediction before they run, and explain the answer after.

index=azuread category=SignInLogs action=failure
| eval hour=strftime(_time, "%H")
| stats count by hour
| sort - count
| head 3

14:00 is second with 59, the hour of the spray on 5 March and of ordinary office mistakes. An hour can hold both an attack and normal life, which is why a breakdown by hour is a start, not an answer.

strftime with %H wrote each failure's hour as two digits, and stats counted per hour. Rung two's eval does most of its work like this, making a field a question needs out of fields the events have.

The hour 22 holds 83 failures, the guessing's 83 again, the same number reached by application and by hour, and nearly by day, where 2 March's 88 includes five ordinary failures. A finding that shows up whichever way the data is cut is a finding that can be trusted.

A prediction that turned out wrong is the most useful kind. Ten at night is not when people mistype passwords; it is when an attacker works without interruption. Completion exercises fill one gap; fix exercises start from a broken search; write exercises start from a question. Practice questions at the end of each section ask for a number.

The order is deliberate. Predicting makes a wrong expectation visible; fixing a broken search teaches what breaks; writing from a question practices the whole method of Section 0.5. Each kind of exercise exercises a different part of the skill.

The exercises are checked by running the answer against the month, not by comparing text. Any search that gives the right result passes, which is how searches are judged at work too: by what they find, not how they look.

Splunk's Search Reference documents every command and function, with examples; the course cites it wherever it states how Splunk behaves. Keeping a personal library of searches that worked, with a sentence on what each answers, turns the course into something usable at work, and the habit outlasts any course.

index=azuread category=SignInLogs action=failure