0.5 Your First Query

Introduction

Sections 0.1 to 0.4 described the language, the data and the places queries run. This sub writes a whole query, from a question in words to an answer that has been checked, using only operators the earlier subs have shown.

The question is an ordinary one, the kind an analyst or a manager might ask on any Monday: which applications do people sign in to most, and how often do those sign-ins fail? It needs a table, a period, a couple of measures, a grouping, an order and a check, and each of those is one line.

You'll build the query a line at a time, as every later query in the course is built, running it after each line to see what changed. You'll add a failure rate, then confirm that the groups add back up to the whole before trusting the top five.

You'll comment the finished query so the next reader understands it. And you'll run the same question over the whole month, where one application stands out, which is how first queries usually end: with a better second question.

The diagram is the method, step by step, as this sub follows it. Each part of the question becomes a line, and the line after the last answers a question of its own: does the answer add up? Every query in the rest of the course follows the same path, however many lines it grows to.

Nothing in this sub is new to the course's later modules; every operator here is covered in depth there. The point is the path from question to answer, which is the same whether the query has six lines or sixty, and which is worth practicing on a question simple enough that the path is all there is to see.

The question has parts, and each part names a piece of the query. Reading it word by word is enough to find them. Which applications: a grouping by application. Sign in: the sign-in table. Most: a count, and an order.

How often do they fail: a count of failures and a rate. On a working week: a period. Writing those parts down, before any KQL, on paper or in a comment, is the step that makes the query almost write itself.

SigninLogs
| take 5
| project TimeGenerated, UserPrincipalName, AppDisplayName, ResultType

A look at a few rows, before writing anything longer, confirms the parts. Each sign-in records the application, in AppDisplayName, and the result, in ResultType, so every part of the question has a column to answer it. Had the table lacked either, this is the moment to find out, before writing anything longer.

It also catches vague questions early. Which applications do people use is not quite the same as which do they sign in to, and the second is what SigninLogs can answer. A question that cannot be answered by any table is better discovered on paper than after half an hour of queries.

A good question for a first query has three properties, and the application question has been chosen to have all of them. It names something countable, here sign-ins. It names a way to divide them, here by application. And it names a period, here a working week.

Questions without the first are not answerable by a query; questions without the second produce a single number; questions without the third produce numbers that change. The application question has all three, which is why it makes a good first query.

Questions in real investigations often start much vaguer than this: is anything odd about sign-ins? Turning that into something countable, divided and bounded is the first piece of analytical work, and it happens before KQL. Which sign-ins, divided how, over what period, and what would count as odd, are the four questions that turn a worry into a query.

The first two lines name the table and the period, the two decisions every query makes first. The period is a where on the time column, between two datetimes: Monday 9 March to the end of Friday 13 March 2026. Putting it straight after the table, as Section 10.1 explained, lets the engine skip everything outside it.

SigninLogs
| where TimeGenerated between (datetime(2026-03-09) .. datetime(2026-03-14))
| count

1,743 sign-ins in the working week. That number matters well beyond this step: every later result describes these rows, and the check at the end compares against it. Running a count after the first lines is a cheap way to know what the rest of the query is working with.

The period has to be chosen as well as written. A working week suits a question about ordinary use, because weekends are quiet and would pull the averages down. A whole month suits a question about patterns, which is how Section 07 uses it. Choosing the period is part of choosing the question, and stating it in the query makes the choice visible.

The datetimes are written in year, month, day order, which avoids any confusion between day-first and month-first conventions, and the end is midnight at the start of Saturday, so that all of Friday is included.

Running the first two lines also confirms that the period was written correctly. A count of zero would mean a typing error in a date; a count near the whole month's 8,203 would mean the filter was not applied.

1,743, about a fifth of the month's sign-ins in five of its thirty days, a little more than a sixth because weekdays are busier than weekends, is the kind of number the period should give.

The question asks for two measures, and naming them is the next line: how many sign-ins, and how many of them failed. summarize computes both in one step, count() for all rows and countif() for the rows where a condition holds.

SigninLogs
| where TimeGenerated between (datetime(2026-03-09) .. datetime(2026-03-14))
| summarize SignIns = count(), Failed = countif(ResultType != "0")

One row, two numbers: the week's sign-ins and its failures, side by side. countif is the tool for any question of the form how many of these were that, such as how many sign-ins failed or how many came from abroad, and it saves a second query with a second filter.

The completed query below writes the measure.

The week's sign-ins and failures. The measures are right for the whole week; the next step splits them by application.

Naming the results, SignIns and Failed, in the summarize itself, rather than accepting the default names that count and countif would produce, keeps the later lines readable. An extend that divides Failed by SignIns says what it does; one that divides count_ by countif_ needs explaining.

countif's condition can be anything that is true or false for a row: a result code, a country, a time of day. Several countifs in one summarize give several measures side by side, which is how many of the course's later summaries are built.

It is worth pausing on why the failures are counted at all. A sign-in can fail for ordinary reasons, a mistyped password or an expired session, and for less ordinary ones. A failure rate per application is a baseline: what normal looks like. A baseline is what makes an unusual number recognizable later, which is exactly what happens in Section 07.

Adding by AppDisplayName to the summarize computes the same two measures once per application, so one row becomes sixteen, one per application anyone used that working week. An extend then turns the two counts into a rate, and top keeps the applications the question cares about.

SigninLogs
| where TimeGenerated between (datetime(2026-03-09) .. datetime(2026-03-14))
| summarize SignIns = count(), Failed = countif(ResultType != "0") by AppDisplayName
| extend FailRate = round(100.0 * Failed / SignIns, 1)
| top 5 by SignIns desc

SharePoint Online leads the week with 204 sign-ins and a 4.4 percent failure rate, followed by Microsoft Office 365, Microsoft Office, Lync and Graph PowerShell.

print WholeNumbers = 100  83 / 762, WithDecimals = round(100.0  83 / 762, 1)

Dividing whole numbers in KQL keeps only the whole part of the result, which is why the rate is computed with 100.0, not 100, so that the division keeps its decimal places, and round keeps one of them. The question is answered, in five rows a person can read in a few seconds and then act on.

top keeps the five rows with the most sign-ins, in order, and discards the other eleven. Five is a choice, not a rule: enough to show which applications dominate, few enough to read at a glance. A report might keep ten; a quick look might keep three. Changing the number changes nothing else in the query.

Graph PowerShell among the week's top five is worth a second look in its own right, even in a first query. It is a tool for administering the directory from scripts, and its place among everyday applications suggests that some people at the company do a lot of scripted administration. Whether that is expected is a question for the people who run the directory; the query has made it visible.

Across the sub so far, a question in words became five lines of KQL, each run and seen to work before the next was added. The table and period gave 1,743 rows; the measures counted them; the grouping, rate and order turned them into an answer.

The record collects the steps, in the order the query was built.

The last row is the one beginners skip and careful analysts never do. It takes one more query and catches the mistakes that produce plausible answers.

The first two rungs are planning; the last two are testing. The third rung is what makes the method reliable. A query run after every new line never has more than one untested line in it, so any surprise points straight at the line that caused it.

The first two mistakes cost time and confidence. The third mistake matters most when a query is saved or shared, which first queries often are. A query without a period answers a different question every time new data arrives, and nobody reading its result can tell which.

The method also scales, from first queries to the longest in the course. The longest queries in this course, the graphs of Module 9 and the health checks of Module 10, were written the same way, a line at a time with the rows inspected after each. Length makes the method more valuable, because a long query written in one go has many places for a mistake to hide.

Writing the question down also helps when a query's answer is surprising. The surprise is either about the data, which is interesting, or about the query, which is a mistake, and the written question is what tells them apart: does the query ask what the words ask?

The method has a cost, a few extra runs, and it is always worth paying. A query that has been run after every line has been tested as it was written, one step at a time, and the errors it might have contained have already been found. A query written in one go has been tested once, at the end, when any error is hardest to locate.

A top five is part of something, and the check is whether the something is right, in one more summarize over the same groups: do all the groups together add up to the rows the query started with?

SigninLogs
| where TimeGenerated between (datetime(2026-03-09) .. datetime(2026-03-14))
| summarize SignIns = count() by AppDisplayName
| summarize Applications = count(), Total = sum(SignIns)

Sixteen applications, whose counts sum to 1,743, the same as Step one. The check summarizes the groups a second time, counting them and adding their sign-ins, which is a pattern worth keeping.

Nothing was dropped or double-counted along the way, so the top five is truly five of sixteen applications in a week of 1,743 sign-ins. This kind of check takes one extra summarize, and Module 10 showed how often a result that skipped it turned out to be wrong.

The exercise below adds the missing period.

SharePoint Online with 204 again, the same as Section 04, now from a query that states its week. Without the period, the same lines answered for whatever the table held, which in a real workspace changes every minute.

Other checks suit other queries. A count of distinct accounts should not exceed the number of accounts in the table; a rate should lie between 0 and 100; a time should fall inside the period asked for. Each is one short query, and each catches a different kind of mistake. Choosing the check is part of finishing the query.

The checks also build confidence that is worth having when a result is passed to someone else, who cannot see how it was produced. An answer that has been seen to add up can be stated plainly; one that has not needs a caveat, and caveats are easy to lose when a number is copied into a report.

When the check fails, the totals not matching, the cause is almost always in one of the lines since the last count: a filter that removed more than intended, a grouping that split rows unexpectedly, a join that multiplied them. Moving the count down the query one line at a time, as Section 10.1 did, finds it.

A check that passes is also worth recording. The write-up that says the sixteen applications' counts add to the week's 1,743 sign-ins tells its reader that the numbers have been tested, and lets them repeat the test if they doubt it.

A finished query is worth keeping, and a kept query needs explaining, to others and to its author. KQL comments start with two slashes and run to the end of the line; the engine ignores them, so they can be added anywhere without changing any result at all.

// Sign-ins by application, Monday 9 to Friday 13 March 2026
SigninLogs
| where TimeGenerated between (datetime(2026-03-09) .. datetime(2026-03-14))  // the working week
| summarize SignIns = count(), Failed = countif(ResultType != "0") by AppDisplayName
| extend FailRate = round(100.0 * Failed / SignIns, 1)   // percent of sign-ins that failed
| top 5 by SignIns desc

The same five rows, from a query that now says what it is for in its first line, which week it covers and what the rate means. A colleague, or you in a month, can read it without working it out again. Every query worth saving deserves a first line saying what question it answers, and a comment wherever a line's purpose is not obvious from the line itself.

SigninLogs
| summarize SignIns = count(), Failed = countif(ResultType != "0") by AppDisplayName
| extend FailRate = round(100.0 * Failed / SignIns, 1)
| top 6 by SignIns desc

Asked of the whole month, which in the lab is the entire table, the question gives a second result worth a look. The leading applications fail between 2 and 6 percent of the time, except Office 365 Exchange Online, which fails 10.9 percent.

A first query has done its job when it answers its question and suggests the next one: which accounts failed to sign in to Exchange Online, when, and from where. Those are questions for the modules ahead, which build the operators that answer them.

In a real workspace the same query over a month would name the month explicitly, for the reason the Fix exercise gave; in the lab, where the table holds exactly one month, the whole table is the month, so the period line can be left out without changing the answer.

Writing up also means stating the answer in words alongside the query: in the week of 9 March, SharePoint Online had the most sign-ins, 204, with 4.4 percent failing; over the month, Exchange Online failed nearly twice as often as any other leading application. A sentence like that, with the query that produced it, is what a colleague or a manager actually needs.

A second question is a sign the first query worked, not that it fell short, and the best first queries usually end with one. Investigations proceed this way: each answer narrows the next question, and the queries grow more specific as the picture fills in. The course's modules follow the same rhythm, and each one ends with questions its operators can answer and the next module's cannot yet.

The habit of writing up applies to the course's exercises too, including the one below. Each one asks a question; answering it with a query, a sentence and a check is good practice for the investigations later modules build, where the write-up is what other people act on.

The exercise asks the question for the whole sample month, which in the lab is everything the table holds, so no period line is needed here, with the failure rate as a percentage to one decimal place, and keeps the top six applications.

The grader checks for six rows, one with a failure rate of 10.9, Exchange Online's. If the rates come back as whole numbers, the division was between whole numbers; write 100.0 rather than 100.

The query is four lines, each of which you have now seen work on its own. Read the six rows as your first answer and your first lead.

The answer is which applications people use and how often they fail; the lead is the one application that fails nearly twice as often as the next. Following leads like that, a query at a time, is what the rest of the course teaches.

Practice

Write the question in words, turn each word into a line, run after each line, and check the result before writing it up.

The next sub, Section 0.6, describes the practice lab and the sample month the course is built on.