0.5 Your First Search

Introduction

Every search in this course is built the same way: write the question as a sentence, start with what to read, add one condition at a time, look at a few events, summarize, and check. Built that way, a search is never a mystery, because each step's effect was seen before the next was added, and a wrong step shows itself at once.

This section builds a first search from a question, "which failed sign-ins came from outside Great Britain?", and follows it to an answer worth reporting. You will count what each condition keeps, read three of the results, break them down by source address and find two attacks in them, check the breakdown against the whole, and write the answer in sentences.

By the end you should be able to take any question about the sample month, build a search for it the same way, and write its answer so someone else can check it.

The diagram is the method, six steps from left to right. Its first and last steps are not SPL at all: a question written down before the search, and a check of the answer after it.

The first row counts every Entra ID event, sign-ins and audit records together. The category term in the second row is the one that turned the index into the source the question was about.

The last row counts groups, not events: nine addresses outside GB had failures. A count after stats always counts rows of its result, which is a different unit from the rows before it.

The fourth row, 109, is where the question's condition took effect. A step that removes about two-thirds of the events is doing real work, and its count is the one to remember when the answer is written.

The card was made by running each version of the search, from the index alone to the final stats, and counting what each returned. Running a growing search this way is the habit Module 10 makes formal; here it is simply the safest way to build anything.

The card counts the search as it grows: 23,843 Entra ID events, 8,203 interactive sign-ins, 338 failures, 109 from abroad, then 9 rows when counted by address. Each row is one change and its effect, the record of how the answer was reached.

The question comes first because it decides everything after it: which events to read, which conditions to add, what the answer should look like.

"Which failed sign-ins came from outside Great Britain?" The company is based in Great Britain, with offices there, so a failure from elsewhere is either someone traveling or someone who should not be there. The question names the events, failed sign-ins, and the condition, a country other than GB.

Written down, the question also says what the answer should look like: a list of sign-ins, or a count, or a breakdown that explains them. This one will end as a breakdown, because a count alone would not say whether anything was wrong.

The card's main finding names two addresses and their shapes, not just their counts. Saying what a number means, guessing or spraying, is the step from a result to an answer.

The card does not say what happened after the failures. A complete answer would add whether either address ever succeeded, which is a second search, and the course asks it where it matters.

The card's shape, a question, a count, a finding and its evidence, and a check, is the shape of any finding worth reporting. Later modules use it for every investigation.

Every line on the card is a fact a search produced. None is a guess, and each can be rerun by anyone with the lab, which is the standard for an answer this course holds itself to.

The card is where this section ends: the answer as sentences, with the evidence for each. It is shown first so the steps can be read against it.

A question with a clear answer shape also suggests the first summary to try. Here the shape is "where from, and how many accounts", which points to stats by address with a distinct count of users, the breakdown Section 4 runs.

The base search reads Entra ID's interactive sign-ins, then narrows to failures, then to countries other than GB. Each term was added and counted before the next, as the card above recorded.

index=azuread category=SignInLogs action=failure NOT src_country=GB
| stats count

About a third of the failures came from abroad, against a company that works almost entirely from Great Britain. A proportion like that is worth stopping on, and the next steps find out why it is so high.

The search counts events, not people or addresses. 109 failed sign-ins could come from one person or a hundred, and only a breakdown says which, which is why the count is a step and not the answer.

The terms in the base search are combined with AND automatically: Entra ID, interactive, failed, and not from GB. Writing AND between them would change nothing; Module 2 shows where writing OR and parentheses does.

109 failures from abroad. The card above shows the steps that led here; at each one, the count said whether the term did what was meant. A term that removed everything, or nothing, would have shown at once.

The NOT term and the count together are the whole search for the question's first half. The second half, explaining the 109, needs the breakdown.

The company's sign-ins come overwhelmingly from Great Britain, 7,779 of the month's 8,203. Against that background, any failure from elsewhere stands out, which is why the question's condition is a good first filter.

The exercise's count, 109, is the number the rest of this section breaks down. A completion exercise that reproduces a figure already seen is a check that the term was understood.

GB is the country code the sign-in data writes for Great Britain. Codes like this are the source's own vocabulary, and a count by src_country, as in Section 5, lists every one the data uses.

NOT in a base search applies to the term after it. Putting it before a field=value term keeps every event that does not match, including, in general, events with no value for the field at all, a subtlety Module 2 covers.

The completion exercise is the NOT term. src_country!=GB gives the same 109 here, because every sign-in has a country; Module 2 explains when the two forms differ.

The count after each term is the search's running commentary. 8,203 sign-ins became 338 failures, about four per cent, an ordinary rate; 338 became 109 from abroad, about a third, which is high for a company based in one country and the first hint that something is there.

Before counting by anything, a look at a few of the 109 shows what kind of events they are and which fields will matter.

index=azuread category=SignInLogs action=failure NOT src_country=GB
| head 3
| table _time user src_ip src_country app

The three events span six days, from 5 to 11 March, which says the 109 are spread through the month rather than in one burst. Spread and bursts look different in a breakdown by time, Module 8's subject.

The app column shows which service each sign-in was for: SharePoint, Lync, Office 365. The spray's attempt was against Office 365 in general, the front door, where the others were people reaching a particular service.

The guest account's name, with #EXT# in it, marks a user from another organization. Partners signing in from their own countries are an ordinary part of a company's failures abroad, and a breakdown has to leave room for them.

head 3 kept the three newest of the 109, because events arrive newest first. They are a sample, not a summary, and their job is to show what the fields look like before any counting decides what to group by.

A guest from a partner firm in Germany, an employee in the Netherlands, and an account failing from the spray's address. Three events are enough to see that the 109 are a mixture, ordinary and not, and that a breakdown will be needed to separate them.

The fields chosen for the table were the ones the question needs: when, who, from which address, from which country, and to which application. Choosing fields before looking keeps a first look short and readable.

A breakdown by the address the sign-ins came from, with the number of accounts each address tried, separates people from attacks in one search.

index=azuread category=SignInLogs action=failure NOT src_country=GB
| stats count dc(user) as accounts by src_ip src_country
| sort - count

Nine rows, nine addresses. A breakdown with a few large rows and many small ones is the usual shape of real data, and the large rows are where to look first.

The country column came along in the by clause because the address determines it; grouping by both costs nothing and keeps the country visible beside each address.

The sort put the largest count first, so the guessing address leads. Sorted by accounts instead, the spray would lead, a reminder that the order of a result depends on which column the question cares about.

The two columns tell different stories. count says how many failures; accounts says how many different users they were spread over. Guessing is many failures on one account, a spray one failure on many, and the two numbers together tell them apart.

Two addresses account for 102 of the 109. One tried a single account 83 times, the shape of guessing a password; the other tried 19 accounts once each, the shape of a spray. The other seven are single failures from ordinary travel and partners.

The nineteen include ordinary staff accounts and r.scott. A spray usually tries accounts from a list, and the list itself says something about what the attacker knew about the company.

The exercise's search adds src_ip to the base search, one more term, and changes stats to count by user. A second question built from the first by small changes is the normal rhythm of an investigation.

Each of the nineteen failed once, and none of them failed again from that address. A spray tries each account a little, to stay under lockout thresholds, which is why a count by account would never have found it.

One of the nineteen is r.scott, the account the guessing address also attacked three days earlier. A name appearing in two attacks is the kind of link the course follows when it combines searches in Module 5.

The write exercise lists the spray's nineteen targets. Asking a second question of an address the first search found is how investigations proceed, one finding leading to the next search.

The breakdown also answers questions the first search did not ask: which accounts the spray tried, when the guessing happened, whether either succeeded. Each of those is a new search, and the course writes several of them in later modules.

An answer is checked before it is used, however obvious it looks. The simplest check is that a breakdown accounts for every event in its total.

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

Switzerland and the Netherlands have one failure each, ordinary travel or partners. Single failures from a country are normal; dozens from one address are not, and the breakdown makes the difference obvious.

The country breakdown is also a small finding of its own: Lithuania appears only because of the guessing. The same address has one successful sign-in too, which a search for failures alone never shows: r.scott, at 22:55 on 2 March, the guess that worked.

The breakdown by country runs over all 338 failures, not the 109, so it checks the NOT term from outside: whatever the NOT kept and removed must add back up to the whole.

eventstats added the total to each row without changing the rows, so the parts and the whole sit side by side. Section 10.7 makes this one of six checks for any answer.

338 failures in all: 229 from GB and 109 from elsewhere. The 109 match the earlier count from a different direction, which rules out a mistake in the NOT term.

The second mistake shows in reports as numbers with no description: 109 of what, from where, over what period. Looking at a few events first is what lets a report say what its numbers count.

The first mistake is natural once a search feels familiar. A long search typed in one go and returning nothing gives no clue which of its parts emptied it; the same search built a step at a time shows exactly where.

The third mistake is the one experienced analysts make under time pressure. 109 failures from abroad is a true statement, and it hides the two findings that matter; a breakdown takes a minute and changes the report.

All three mistakes leave out a step of the method. The first leaves out counting at each step, the second looking, the third breaking the number down.

Lithuania's 83 are all the guessing; Germany's 24 are the spray's 19 and five ordinary failures from partners and staff. The country breakdown and the address breakdown agree, two routes to one picture.

A condition belongs before the step that removes the field it tests, which usually means in the base search.

The where in the broken search compared src_country with GB, and src_country did not exist after the stats. Every row failed the test, so every row was dropped, the same silent miss Section 0.1 showed with a misspelled field name.

head 2 keeps the two largest addresses after the sort, the guessing and the spray, the same two Section 4 found. Ending with the top of a sorted list is a common shape for a first search.

The broken search did not fail with an error; it returned nothing. An empty result from a search that should have found something is the cue to read its steps in order and ask which fields each step still has.

The broken search filtered on src_country after stats had grouped by address only, so the field was gone and nothing survived. Moved into the base search, the condition is applied while the events still have it, and the two attack addresses come first.

The fix is the most common correction to a first search: a condition written where it was thought of, at the end, moved to where it works, at the start. Module 1 explains the order commands run in, and Module 10 shows that the move also makes a search cheaper.

The answer to the question is not 109. It is: of the month's 338 failed sign-ins, 109 came from outside Great Britain, and 102 of those came from two addresses, one guessing a single account's password 83 times and one trying 19 accounts once each. The rest are ordinary failures by staff and partners. Each statement has a search behind it, and the breakdown was checked against the total.

The fourth rung is where this section's answer became trustworthy. The check by country took one search and confirmed every earlier step, which is a small price for an answer that can be defended.

The second rung is what makes a search's behavior visible. Counts at each step turn every condition into a number that can be judged against expectation: too many, too few, or about right.

The first rung is the one people skip, because the question seems obvious. Written down, it often turns out to be two questions, or a question whose answer would not settle anything, and finding that out before searching saves the search.

The ladder is this section's method in four rungs. It is the same method the course applies in every module, to searches far more complex than this one.

An answer written this way can be checked by anyone who reads it: each number names the search behind it, and the searches can be rerun. That is the standard for any finding reported from Splunk, in an incident report, a ticket or a message to a colleague.

The two attacks this search found come back many times: Module 4 counts them, Module 8 times them, Module 9 finds them through data models, and Module 10 checks the numbers. Section 0.6 describes the lab and the month they are in.

The habits are four, the method in short, and they work for any question. Write the question first, as a sentence. Add one condition at a time and count. Look at a few events before counting them. And check the answer against its whole before writing it down.

Practice

Three questions about the first search and what it found.

The three answers are this section's findings in numbers. Rerun each search and check that it gives the same figure; reproducing an answer is the first check of any search.

Next, Section 0.6 describes the practice lab and the sample month, and where the lab differs from Splunk.