DEV Community: Alan Conlon

Hybrid Cloud Security: The Gaps Nobody Talks About Until It's Too Late

Alan Conlon — Fri, 23 Oct 2026 22:16:41 +0000

Most organisations don’t move to the cloud in one clean jump. They end up with a foot in both worlds: some systems on-premises, some in Azure or AWS, and a lot of plumbing connecting the two. That in-between state is where most of the real security risk lives, and it rarely gets the attention it deserves.

I’ve spent years working in and around hybrid environments, and the same gaps come up again and again. None of them are exotic or unusual. They are just the kind of thing that gets missed when everyone assumes someone else is handling it.

What is the identity seam in a hybrid setup?

When you connect your on-premises Active Directory to a cloud identity provider, you create a seam. Accounts sync, permissions flow, and suddenly a compromise in one environment can walk straight into the other.

The mistake I see most often is treating the two directories as if they’re one trusted system. They are not. A service account with too much reach on-premises becomes a much bigger problem the moment it has a path into your cloud tenant. Privileged accounts especially need to be looked at end to end, not just within whichever environment you happened to be reviewing that day. It’s the same reason break-glass accounts should be cloud-only.

Map out exactly what can move between the two, and assume an attacker will find the shortest path. Usually they do.

Stale permissions that never get cleaned up

Hybrid setups accumulate permissions like a garage accumulates junk. Someone needs temporary access for a migration project, it gets granted, the project ends, and the access stays. Multiply that across a few years and a few hundred people and you’ve got a sprawl that nobody fully understands anymore.

The fix isn’t glamorous. It’s regular review, automated where possible, and a genuine willingness to remove access that isn’t being used. The hardest part is cultural. People hate having access taken away, even access they’ve never touched. But every unused permission is a door left unlocked.

Logging that looks fine until you actually need it

Plenty of organisations tick the “logging enabled” box and move on. Then an incident happens, they go to investigate, and they find the logs don’t cover the thing they actually care about, or they’re scattered across systems that don’t talk to each other.

Hybrid makes this worse because your visibility is split. On-premises logs live in one place, cloud logs in another, and the connection between them is exactly where you need clarity during an incident. It’s worth doing the boring work upfront. Decide what you’d need to see if something went wrong, then check you’re actually capturing it. Test it before you’re depending on it at 2am. If those logs land in Sentinel, a few lines of KQL is the quickest way to check what’s really there.

The shared responsibility confusion

Cloud providers are clear about where their responsibility ends and yours begins. The problem is that internally, teams often assume the provider handles more than it does. “It’s in the cloud, so it’s covered” is a sentence that has preceded a lot of incidents.

In a hybrid setup this gets muddier still, because responsibility shifts depending on which part of the stack you’re looking at. The only fix is to be explicit. Write down who owns what, for each system, and make sure the people running things have actually read it.

None of this is about buying more tools

Here’s the part that tends to surprise people. Most hybrid security gaps get solved by understanding your environment properly, removing what you don’t need, and being honest about who’s responsible for what, rather than by spending more money on products.

Tools do help, but a clear picture of your own estate, kept up to date, is worth more than any dashboard you bolt on top of confusion. Handling hybrid well has far less to do with the size of the security budget than with knowing what you’re actually running.

That’s the unglamorous truth of it. Good security in a hybrid world is mostly about paying attention to the joins, the places where two systems meet and everyone assumes the other side has it covered.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

How Many SCUs Does Security Copilot Actually Need? Sizing Capacity Without the Bill Shock

Alan Conlon — Fri, 23 Oct 2026 22:16:15 +0000

The question that always lands after the Security Copilot demo is the same one: what’s this going to cost. The honest answer is unsatisfying. It depends entirely on how you size it, and most people get the sizing wrong in one of two directions. They either reserve a pile of capacity they never use and quietly bleed money, or they run it too lean and watch it fall over in the middle of the one investigation where they actually needed it.

Start with your licence. If you’re on Microsoft 365 E5 or E7, Security Copilot is included with a monthly SCU allowance, and you don’t need to provision or buy anything to start. Most of this post is about the standalone route, where you buy capacity yourself, and about what to do if the included allowance isn’t enough.

So this is the sizing post. If you’ve already switched it on, this is the bit where you work out how much compute to put behind it without overpaying or starving it.

An SCU is a unit of work, not a seat

Security Copilot is metered in Security Compute Units. An SCU is just a measure of compute, the fuel the platform burns to do something. The thing to get straight early is that consumption is driven by work, not by headcount. It is not a per-user licence in disguise.

Different actions cost wildly different amounts. A big, multi-step prompt that reaches across several data sources and reasons over the results can burn several units in one go. A quick incident summary might cost a fraction of one. So two teams with the same number of analysts can run up completely different bills depending on what they actually ask it to do. When you’re sizing, think in terms of how much work and how often, not how many people.

Is provisioned Security Copilot capacity a yearly commitment?

You pay for that compute two ways, and each has its own way of hurting you.

Provisioned capacity is always-on. You reserve a number of units and pay for them by the hour, every hour, whether anyone uses Copilot or not. The trap here is obvious once you say it out loud: reserve too much and you’re paying rent on idle compute around the clock, including the nights and weekends when nobody’s running a thing.

It isn’t a long-term commitment, though. You can raise or lower provisioned units at any time, and you’re billed for each hour they’re provisioned.

Overage is on-demand. It kicks in once you’ve exhausted the provisioned units, and you’re billed only for what you use. You can leave it unlimited or set a cap. The trap here is the cap. Set a hard overage limit that’s too low and you’ve built yourself a cliff: once real demand uses up both the provisioned units and the overage, Copilot returns an error and won’t take new prompts until the next hour. More on that further down, because it’s the failure mode that actually bites.

The billing quirk that catches people out

Here’s the one nobody mentions until the invoice arrives. Provisioned capacity is billed in whole clock-hour blocks, 9:00 to 10:00 and so on, with a one-hour minimum. Each provisioned SCU is charged for the full hour, whether you ran one prompt or none. Unused units expire at the end of the hour and don’t roll over.

The practical effect shows up when you change capacity. Add an SCU at 9:05, remove it at 9:35, add another at 9:45, and you pay for two SCUs in that hour. So make provisioning changes at the start of the hour. Overage works differently. It’s charged on the exact units consumed, to one decimal place, with no rounding up.

How many SCUs do you get with Microsoft 365 E5?

If your organisation is on Microsoft 365 E5 or E7, set most of the above aside for a moment, because you’re on a different model. Security Copilot comes included, with a monthly allocation of 400 SCUs for every 1,000 paid user licences, up to a ceiling of 10,000 a month, at no extra cost. That capacity isn’t billed by the hour the way standalone provisioned capacity is. Usage is simply deducted from the monthly pot. Microsoft says usage beyond the allocation will be throttled at a future date, with an option to pay for more at $6 an SCU, and 30 days’ notice before that starts.

Two things follow. First, the allocation resets every month and doesn’t roll over, so anything you don’t use just disappears. Second, a lot of E5 shops already have more capacity than they realise and don’t need to rush out and buy standalone SCUs at all. Check what you’re entitled to before you provision anything. And if you already had standalone capacity before the inclusion arrived, Microsoft’s advice is to keep it rather than delete it, so you don’t lose access.

How many SCUs do you need?

The method that works is the unglamorous one, and it’s the same shape as sizing anything else.

Start with what you already have. On E5 or E7, run real work against the included allowance first. If you’re buying standalone capacity, start small. Whether you provision in Azure or in the Security Copilot portal, you need at least one provisioned unit. You can’t set it to zero and run purely on overage. For a first look, Microsoft’s own suggestion is three provisioned units with overage left unlimited. Either way, you begin with very little reserved.

Then run real workloads and read the data. The in-product usage dashboard holds ninety days of history and shows you what’s genuinely being consumed: how many provisioned and overage units, which plugins are doing the work, and who’s running the sessions. There’s also a capacity calculator if you want an estimate up front. Between the two, you can see your actual steady-state demand instead of guessing at it.

Only then do you set your provisioned level. Reserve enough to cover your normal baseline, the floor of activity that happens most working hours, and let overage absorb the spikes on top. That gives you predictable billing for the routine and flexibility for the busy days, without paying to keep a peak-sized allocation warm twenty-four hours a day. Look at real usage first, size second. Resist the urge to pick a big round number on day one because it feels safe.

On the actual money: Microsoft’s US pricing example uses $4 per provisioned SCU per hour and $6 per overage SCU. Treat any figure you read, including that one, as indicative. Run the calculator against your own numbers for anything you’d put in front of finance.

Give each team its own capacity

If you’re running more than one team or region, use workspaces. A workspace is backed by its own capacity, and the detail that matters is that SCUs don’t flow between workspaces. You can’t keep one big shared pool that quietly rebalances itself.

That sounds like a limitation, and sometimes it is, but it’s also useful. It means one team having a heavy week can’t silently drain the capacity another team is relying on. Provision each workspace for its own workload, size them independently off their own dashboard data, and you get cost and capacity that line up with how the teams actually operate rather than one undifferentiated bill nobody can break down.

What running out actually looks like

This is the failure mode worth designing against, because it turns up at the worst possible moment. As usage climbs towards your limit, analysts start getting capacity warnings inside their sessions, including the embedded Copilot experiences inside Defender and Sentinel. That’s your early signal.

Push past the limit entirely and it stops being a warning. Once both the provisioned units and any overage are used up, Copilot returns an error saying it can’t respond because of high usage. Nobody can submit more prompts until capacity frees up in the next hour. Raising the units fixes it, but changes can take up to 30 minutes to take effect. Picture that landing halfway through a live incident, with an analyst told to go and find whoever owns the capacity to get it raised. That’s a self-inflicted wound, and it traces straight back to a hard overage cap set too low and never revisited.

The fix isn’t complicated. Either leave overage unlimited and watch the dashboard, or set a cap you’ve genuinely thought through and make sure the people who can lift it are reachable when it matters. Capacity that runs dry quietly during an incident is the one sizing mistake you really don’t want to make.

The short version

Sizing Security Copilot isn’t a number you get right once. It’s start small, run real work, read the dashboard, then set provisioned to your baseline and let overage handle the rest. The bill shock people complain about almost always comes from guessing at the start instead of measuring, or from a capacity cap nobody looked at again. Neither is the product’s fault, and both are easy to avoid.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Break-Glass Accounts: The Ones You Hope You Never Use, Done Properly

Alan Conlon — Fri, 23 Oct 2026 22:15:39 +0000

Every guide to Conditional Access, MFA and identity hardening opens with the same instruction: set up break-glass accounts first. Then it moves on, as if the accounts themselves were the easy part. They are not. A break-glass account done carelessly is worse than none at all, because it gives you the comfort of a safety net while quietly being a hole in the floor. This is the unglamorous detail of getting them right.

A break-glass account, sometimes called an emergency access account, exists for one moment: the day everything else locks you out. A Conditional Access policy goes wrong and nobody can sign in. Your federated identity provider falls over and takes SSO with it. MFA infrastructure has an outage and every normal login is stuck at the second factor. In each case you need a way back into the tenant that doesn’t depend on the thing that broke. That is the entire job. The account earns its existence maybe once in its life, and on that day it is the most important account you own.

Why the obvious version is wrong

The instinct is to make an admin account, give it a strong password, and call it your break-glass. That misses the point in a few ways at once, and each one is a way the account fails you at exactly the wrong moment.

If it depends on MFA and your MFA is what’s broken, it’s useless the day you need it. If it’s tied to a named person, it’s unusable when that person has left or is unreachable at 3am. If it’s subject to the same Conditional Access policies as everyone else, the policy that locked out the tenant locks it out too, which defeats the whole purpose. And if it’s an everyday admin account you also use for real work, it accumulates the same risk exposure as any other account while being held to less scrutiny.

Getting break-glass right is really about deliberately breaking each of those dependencies.

What does a proper break-glass account look like?

Cloud only. The account should be created directly in Entra ID, not synced from on-premises Active Directory. If it came from on-prem and your sync or your domain controllers are what failed, the account fails with them. Cloud-native means it survives an on-prem outage, and stays clear of the identity seam in a hybrid setup.

Not tied to a person. It belongs to the organisation, not to Alan or to whoever holds the admin role this quarter. No personal email in its recovery, nothing that walks out the door when someone resigns. Two accounts, not one, so a problem with either still leaves you a way in.

Excluded from Conditional Access. Every policy you create should carve these accounts out. This is the part people forget, and it is the part that matters most, because the entire scenario you are guarding against is a Conditional Access policy gone wrong. An account still subject to that policy goes down with the ship.

A very long passphrase, stored offline. Because the account has to work when your normal MFA might not, its password is doing more of the security work than usual, so it should be long, random, and genuinely secret. Split it if you like, half known to one trusted person and half to another, so no single individual can use it alone. Store it somewhere physical and secure, a sealed envelope in a safe is not old-fashioned here, it’s appropriate. It must be retrievable in a crisis without depending on the systems that might be down.

Global Administrator, and nothing less. This is the one account where you do want the top role, because the emergencies it exists for often require fixing tenant-wide configuration. It’s the deliberate exception to least privilege, justified precisely because it’s otherwise so tightly controlled and so heavily watched. Every other admin role belongs behind just-in-time activation (coming soon).

The exclusion from MFA is where people get nervous

There’s an obvious tension. You exclude the account from Conditional Access and you’re cautious about MFA dependencies, but an unprotected Global Admin account is exactly what every attacker dreams of. Both things are true, and the resolution is to compensate with a different control rather than protect the account the normal way: watch it like nothing else in your tenant.

Microsoft has settled part of that tension. Since mandatory MFA arrived for the Azure, Entra, Intune and Microsoft 365 admin portals, emergency accounts must complete MFA too, and there’s no exclusion. So give each one a FIDO2 security key, or certificate-based authentication, that no other admin account uses. Store the key physically with the credentials. It survives the outages you’re guarding against, it satisfies the enforcement, and it still keeps the account out of your Conditional Access policies. The principle holds: the account’s protection must not depend on the systems it exists to rescue you from.

Watching them is the actual security control

An account that bypasses your policies is only safe if any use of it is impossible to miss. So the compensating control for all that deliberate exposure is alerting. Every single sign-in to a break-glass account should raise an immediate, loud, high-priority alert to whoever runs security, because in normal operation these accounts are never used. A break-glass sign-in is, by definition, either a real emergency or an intruder, and both demand that someone looks right now.

This is straightforward to build. In Sentinel, an analytics rule watching SigninLogs for those specific account names, set to fire on any successful sign-in, gets you there. Because the expected rate of use is zero, you don’t need a clever threshold. One sign-in is the signal.

SigninLogs
| where UserPrincipalName in ("breakglass1@yourtenant.onmicrosoft.com",
                              "breakglass2@yourtenant.onmicrosoft.com")
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName, IPAddress,
    Location = tostring(LocationDetails.city), AppDisplayName

Wire that to notify immediately, not to batch into a daily digest. The whole value is in the speed of the response.

Test them, or you don’t really have them

Here is the part almost everyone skips. An emergency access account you have never signed into is a theory, not a control. You do not want the first time you use it to be during the actual crisis, discovering then that the password in the safe is wrong, or the account expired, or the alert never fired. Test them on a schedule, quarterly is reasonable. Retrieve the credentials the proper way, sign in, confirm the account works and has its access, confirm your alert fired, then reset the password and put it back. That drill turns a hopeful arrangement into one you can actually rely on.

None of this is difficult. It’s a handful of accounts and an afternoon of care. But it’s the kind of care that’s easy to defer forever because the payoff is invisible right up until the one day it’s the only thing that saves you. When an identity lockout does come, the organisations that get through it calmly are the ones that set these up properly and then checked that they worked. Clever tooling has very little to do with it.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Starting a Site with Hugo: Own the Place You Write

Alan Conlon — Tue, 20 Oct 2026 00:00:00 +0000

Everywhere you write online, you’re renting. Most of the time that’s fine. I syndicate to Medium and Dev.to myself, and they bring readers my own site never would. But the platform decides who sees your work, how it looks and what the rules are next year, and none of that is yours to change. The landlord can always change the locks.

So this series is about building the place you do own: the site everything else points back to, on a stack that costs nothing and answers to nobody but you. This first part is the why and the first ten minutes, getting Hugo running on your own machine so you’ve got something to look at. Later parts put it on the internet, make it look like yours, and lock it down.

Why static, and why this stack

The site you’re reading is built with Hugo, kept in a GitHub repository, and hosted on Cloudflare Pages. Three pieces, each doing one job. Hugo turns a folder of Markdown files into a finished website. GitHub holds that folder and its history. Cloudflare Pages watches the repository, runs Hugo whenever it changes, and serves the result to the world.

The word doing the heavy lifting is static. A static site is just files, HTML, CSS, images, generated ahead of time and handed to the browser as-is. There’s no database, no server-side code running on each request, no admin login sitting on the public internet waiting to be brute-forced. Compare that to WordPress, which is a database and a pile of PHP and a plugin ecosystem you have to keep patched or watch get compromised. A static site has almost no attack surface, because there’s almost nothing running. For anyone who spends their day thinking about attack surface, that alone is a decent reason.

The other reasons are ordinary and add up. It’s genuinely fast, because serving a pre-built file from a CDN is about the quickest thing the web does. It’s free at this scale, Cloudflare Pages doesn’t charge for a personal site’s traffic. And because the whole thing is text files in git, you get version history for free, you can see every change you ever made, and nothing important lives anywhere you can’t back up with a git clone.

Three pieces, one job each. Nothing running that an attacker can knock on.

Get Hugo on your machine

Hugo is a single binary, which is part of its charm, there’s no runtime to install alongside it, no dependency tree to manage. Install it however your platform prefers: brew install hugo on a Mac, winget install Hugo.Hugo.Extended on Windows, or your package manager of choice on Linux.

One thing that catches people, so get it right now and save yourself a confusing afternoon: install the extended edition. Many themes rely on features only the extended edition has, such as SCSS processing, and the error you get when you hit that wall doesn’t say “you installed the wrong Hugo”, it says something oblique about a missing feature. The winget line above already grabs the extended build; on other platforms, check the version string after installing with hugo version and confirm the word extended is in there.

That command is also your first sanity check. If hugo version prints something, you’re ready. If it doesn’t, the install didn’t take, and there’s no point going further until it does.

Scaffold a site and see it running

Now the satisfying bit. Create a new site:

hugo new site mysite
cd mysite

That drops a folder structure in front of you, and it’s worth a five-second tour because these are the folders you’ll live in. content is where your Markdown posts go. layouts is where the templates live, the HTML that wraps your content. static is for files served as-is, and assets for files that go through Hugo’s pipeline. hugo.toml is the site’s configuration. Nearly empty for now, all of it, which is exactly why a fresh Hugo site shows you nothing when you first run it.

Run it anyway:

hugo server

Open the address it prints, usually localhost:1313, and you’ll see a blank page. That blank page is the win. It means Hugo is installed, the site is building, and the local server is live-reloading, change a file and the browser refreshes itself. You now have a working development loop on your own machine, disconnected from the internet, where you can break things freely.

It’s blank because you’ve got no content and no theme yet. That’s the next two things to sort, and it’s where the first real decision lands.

Themes, or roll your own

Hugo has a large gallery of ready-made themes. Drop one in, point your config at it, and you’ve got a designed site in minutes. For getting started, that’s a completely reasonable path, and I’d not talk anyone out of it for a first site.

I rolled my own instead, a handful of my own templates rather than someone else’s theme, and I’d gently argue it’s worth doing eventually even if not on day one. A theme is someone else’s decisions about how your site looks and works, and the day you want to change something specific, you’re reverse-engineering a stranger’s code. Your own layouts are usually simpler than a general-purpose theme, because they only have to do what your site does, not everything every site might want. That’s a later part of this series (coming soon), though. For now, if you want to see something other than a blank page today, grab a theme and move on.

Where this goes next

At this point you’ve got Hugo installed and a site building locally, which is further than most people who mean to start a blog ever get. It’s still trapped on your laptop, though, localhost is a site with an audience of one.

The next part (coming soon) takes it live: a GitHub repository as the source of truth, Cloudflare Pages wired up to build from it, your own domain pointed at it, and HTTPS handled automatically. That’s the step where it stops being a folder on your machine and becomes a place on the internet with your name on it. The place you own.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Hunting Password Spray in Sentinel: One Password Against a Thousand Doors

Alan Conlon — Tue, 22 Sep 2026 00:00:00 +0000

Everyone pictures a password attack as hammering one account: thousands of guesses against a single login until something gives. That’s brute force, and it’s easy to catch, a pile of failures on one account trips any lockout worth its salt. Password spray is the smarter cousin, and it’s built specifically to slide under exactly that defence.

The trick is to flip the ratio. Instead of many passwords against one account, spray tries one password, a common one, Winter2026! or the company name with a number, against many accounts. One attempt each, or two, spread across hundreds of users. No single account sees enough failures to lock, so smart lockout never fires, and the attacker only needs one person in the whole directory to have picked the guessable password. In an organisation of any size, someone always has.

It’s a numbers game, and the numbers favour the attacker. Which is exactly why it’s still one of the most common ways in, and why it’s worth being able to see.

What does password spray look like in the sign-in logs?

Because spray inverts the ratio, it inverts the shape in the logs too. Brute force is a tall spike on one account. Spray is a wide, shallow smear: one source touching a great many distinct accounts, each only once or twice, in a short window. That breadth is the tell. Ordinary failed sign-ins are scattered across the day and across sources; spray is one origin reaching for dozens or hundreds of different usernames in minutes.

The failures land in SigninLogs with a result code, and the one that matters here is 50126, the code for an invalid username or password. That’s the bad-guess code, the one a wrong password produces.

Brute force is tall and narrow. Spray is wide and shallow. Count the distinct users, not the attempts.

Look before you detect

Same discipline as the primer and every post since: look at the raw failures in your own tenant before you try to be clever, because you can’t tell an attack from a busy Monday until you’ve seen a normal one.

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "50126"
| take 20

Run that, read the rows. You’ll see the ordinary background of fat-fingered passwords and stale cached credentials on people’s phones. Note what a normal source looks like, one user, from one IP, failing a couple of times and then succeeding. That’s the baseline the attack has to stand out from.

Now count the breadth

The detection isn’t “how many failures”, it’s “how many different accounts from one source”. So we count distinct users per IP inside a time window:

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "50126"
| summarize
 DistinctUsers = dcount(UserPrincipalName),
 Attempts = count()
 by IPAddress, bin(TimeGenerated, 1h)
| where DistinctUsers >= 15
| sort by DistinctUsers desc

Read it as a sentence. Take the last day of bad-password failures, count the distinct users and total attempts per IP per hour, and show me any source that failed against fifteen or more different accounts in a single hour. dcount is the operator doing the real work: it counts distinct users, which is the breadth that separates spray from one person locked out of their own account. That user fails fifteen times against one username; a sprayer fails once against fifteen usernames. Same fifteen failures, completely different dcount.

The threshold of fifteen is a starting point, not gospel. Your baseline from the first query is the guide, a big tenant with a chatty VPN egress might need it higher, a small one lower. Tune it to your own noise.

The refinement that turns a hunt into an incident

A source spraying and failing is an attack attempt. A source that sprayed and then, somewhere in the same window, succeeded against one of those accounts is a breach in progress, because it means one of the guessed passwords was right.

let Lookback = 1d; // how far back to look
let Threshold = 15; // distinct accounts before a source counts as spraying
let SprayIPs = SigninLogs
 | where TimeGenerated > ago(Lookback)
 | where ResultType == "50126"
 | summarize DistinctUsers = dcount(UserPrincipalName), FirstSpray = min(TimeGenerated) by IPAddress
 | where DistinctUsers >= Threshold;
SigninLogs
| where TimeGenerated > ago(Lookback)
| where ResultType == "0"
| join kind=inner SprayIPs on IPAddress
| where TimeGenerated > FirstSpray // only successes after the spray began
| project TimeGenerated, UserPrincipalName, IPAddress,
 DistinctUsers, Location, AppDisplayName
| sort by TimeGenerated desc

Two ideas you’ll recognise from the MFA fatigue hunt. let names the set of spraying IPs, the sources that failed broadly. Then join matches those same IPs against successful sign-ins (ResultType == "0"), and FirstSpray keeps only the successes that came after the spray began. What falls out is the list that matters: an account that logged in successfully, after the spray started, from an IP that was spraying the rest of your directory. That’s not a coincidence you explain away. That’s the one they got. The one exception is a shared NAT or VPN egress IP, which can still match, so check the source before you call it.

If that query returns a row, treat it as compromise: revoke the sessions, reset the credential, and check what that account touched before you do anything else.

Both spray queries are in the free queries repo, commented and with the thresholds pulled out as let values at the top.

What it misses

Honesty about the gaps, as always. The big one is distributed spray: an attacker who sprays from a botnet, a different IP per account, defeats the per-IP count entirely, because no single source touches enough users to cross the threshold. Catching that needs a different angle, counting the breadth per password or per time window across all sources rather than per IP, and it’s a harder, noisier hunt. This query catches the common, cheap version, the one source spraying away, which is still most of what you’ll see.

It also leans on 50126 specifically, and a spray that trips smart lockout will start throwing 50053 instead, so a truly patient attacker pacing under the lockout stays on 50126 and a clumsy one shifts codes. Worth watching both. And none of it helps if the sprayed account had no MFA behind the password, which is the real lesson underneath the query: spray works because a password on its own is still a single point of failure. Blocking legacy authentication with Conditional Access takes away the endpoints where that’s still true.

Promote it slowly

You know the routine. Run it in the logs for a week or two, watch what it flags, learn which of your egress IPs are just busy rather than hostile, tune the threshold and window, and only then wire the refinement query into a scheduled analytics rule that alerts on a spray-then-success. The broad count is for hunting; the join is for paging someone.

Password spray has been the opening move in more breaches than almost any other technique, and it survives entirely on the gap between “one account’s lockout” and “the whole directory’s failure rate”. Close that gap by counting breadth instead of depth, and the wide shallow smear that was invisible to per-account defences lights right up.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

5 DAX Mistakes That Quietly Break Your Power BI Reports

Alan Conlon — Tue, 08 Sep 2026 00:00:00 +0000

The worst DAX bugs don’t throw errors. The formula validates, the report refreshes, everything looks fine, and the number is just wrong. No red squiggle, no warning, nothing pointing you at the cause. You usually find out when someone in a meeting taps the screen and asks why that figure doesn’t match the source.

I’ve lost more hours to that kind of silent failure than to any actual error message. Here are the five that catch people most often, and what to do instead.

Reserved keywords as variable names

DAX won’t let you name a variable after a reserved word or an existing table. Some names, such as Date and Value, also clash with function names. That makes formulas hard to read and easy to break.

The fix is dull and it works: prefix every variable with a lowercase v. vStatus, not Status. Once it’s a habit you stop thinking about it.

VAR vStatus = SELECTEDVALUE('Table'[Status_Column])
VAR vClass = SELECTEDVALUE('Table'[Class_Column])
RETURN
 IF(ISBLANK(vStatus), "Unknown", vStatus)

Chains of = comparisons instead of a list membership check

When you want to check whether a value is one of several options, a chain of vType = "Express" || vType = "Priority" comparisons is the obvious move. It’s long, it’s easy to get wrong, and = treats a BLANK as equal to 0 or “”. Use a list membership check instead.

vType IN {"Express","Priority","Same-Day"} and CONTAINSROW(vList, vType) do the same thing. Both use strict comparison, so a BLANK won’t match 0 or “”. Pick one and keep the list in a variable:

VAR vList = {"Express", "Priority", "Same-Day"}
RETURN
SWITCH(TRUE(),
 CONTAINSROW(vList, vType), "Fast",
 "Standard"
)

The list lives in one place, and adding a value is a one-line change.

Not handling blanks before you compare

Blanks cause more wrong-but-not-broken results than anything else here. A check like vStatus = "Active" does something unhelpful when vStatus is blank, and the row ends up in the wrong bucket without a word of warning.

Catch blanks first, before any other comparison:

SWITCH(TRUE(),
 ISBLANK(vStatus), "No Status", -- blanks first, always
 vStatus = "Active", "Live",
 "Other"
)

Make that your opening branch and everything below it can assume it’s working with a real value. Most of my classification logic starts this way now, out of pure self-preservation.

Using SELECTEDVALUE in the wrong place

In a calculated column, reference the column directly. The row context gives you the current row.

VAR vRevenue = 'Sales'[Revenue]

SELECTEDVALUE() is for measures. There it reads what the filter context, such as a slicer or a matrix row, has narrowed to. It returns BLANK when more than one value is in play. Use it in a calculated column and it sees the whole column, so you get BLANK on every row. It’s the usual reason a formula works fine as a measure but breaks as a column.

Dividing without DIVIDE()

The classic. [Covered] / [Total] works perfectly right up until Total is zero. Then the visual quietly shows Infinity or NaN instead of a number.

DIVIDE() exists for this exact reason:

RETURN DIVIDE(vCovered, vTotal)

It handles the zero gracefully: blank by default, or a value you nominate. I can’t think of a good reason to use the / operator directly in a measure, except dividing by a constant (say / 100). There Microsoft recommends /, because it can’t fail and it’s faster.

How do you stop DAX failing silently?

None of these fixes are clever. They’re careful. Assume values can be blank. Assume a list needs a real membership check. Assume division will hit zero eventually, because it will. Good DAX is mostly defensive DAX. You’re writing for the messy data you’ll actually get, not the clean data someone promised you in the kickoff meeting.

These silent failures are also a common reason two measures or visuals in the same report disagree. When your Power BI metrics conflict, check these five first.

Get these into muscle memory and the whole silent-failure category mostly goes away. That’s a decent chunk of your week back.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Hunting MFA Fatigue in Sentinel: Building the Detection One Line at a Time

Alan Conlon — Tue, 25 Aug 2026 00:00:00 +0000

MFA fatigue is the attack that works because people are tired. The attacker already has the password, usually from a phishing kit or a credential dump, and MFA is the only thing left in the way. So they hammer it. Push notification after push notification, sometimes at two in the morning, until the person on the other end approves one just to make their phone shut up. That single tap is the whole breach. It's how some of the most publicised compromises of the last few years started, and there's nothing exotic about it. It's a denial-of-patience attack.

The good news is that it's loud. An attacker bombing someone with MFA prompts leaves a very recognisable shape in your sign-in logs, and you can find it with the same handful of KQL operators from the primer. This post builds that detection from nothing, one line at a time. If you haven't read the primer, it's the place to start; this one assumes you know what a pipe does.

What does MFA fatigue look like in SigninLogs?

Every MFA prompt that gets denied or times out lands in SigninLogs with a result code. The one that matters here is 500121, which is Entra's code for an MFA challenge that failed: the user declined it, ignored it, or it timed out. One of those on its own is nothing. Someone fat-fingered a prompt or left their phone in the kitchen.

The attack shape is different: a burst of them, against one account, in a short window. Ten denied prompts in fifteen minutes isn't someone making tea. That clustering is the entire detection.

Start by looking, not detecting

Same discipline as always. Before writing anything clever, look at what the failures actually look like in your own tenant:

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "500121"
| take 20

Run that and read the rows. You'll see who's failing MFA in an ordinary week, which apps trigger it, and roughly how often it happens when nothing is wrong. That baseline matters, because the difference between noise and an attack is volume against time, and you can't judge volume until you've seen normal.

Now add the shape

The attack is a cluster, so we count failures per user inside small time windows. bin() chops time into buckets, and summarize counts inside them:

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "500121"
| summarize FailedPrompts = count() by UserPrincipalName, bin(TimeGenerated, 15m)
| where FailedPrompts >= 8
| sort by FailedPrompts desc

Read it as a sentence. Take the last day of sign-ins, keep only failed MFA challenges, count them per user per fifteen-minute window, and show me anyone with eight or more in a single window. That's the whole detection, and it's five lines.

The threshold is yours to tune. Eight in fifteen minutes is a reasonable starting point, but your baseline from the first query is the real guide. Set it low enough to catch a determined attacker, high enough that the person with a flaky Authenticator setup doesn't set it off every morning.

The refinement that makes it serious

Here's the version worth promoting to an analytics rule, and the thinking behind it. A burst of denied prompts is suspicious. A burst of denied prompts followed by a success is the actual disaster, because it means the bombing worked. Someone got tired and tapped approve.

let Failures = SigninLogs
    | where TimeGenerated > ago(1d)
    | where ResultType == "500121"
    | summarize FailedPrompts = count(), LastFail = max(TimeGenerated)
        by UserPrincipalName
    | where FailedPrompts >= 8;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| join kind=inner Failures on UserPrincipalName
| where TimeGenerated > LastFail
| project UserPrincipalName, FailedPrompts, LastFail,
    SuccessAt = TimeGenerated, IPAddress, Location, AppDisplayName

Two new ideas, both small. let names a query so you can reuse it, here the set of accounts that got bombed. Then join matches those accounts against successful sign-ins (ResultType == "0") that happened after the last failure. What comes out is the list you actually care about: accounts that were hammered and then let someone in, with the IP, location and app of the sign-in that got through.

If that query ever returns a row, stop hunting and treat it as an incident: that account needs its sessions revoked and its credentials reset before you finish your coffee.

Both queries from this post are in the free queries repo, commented line by line, with the window and threshold set as let values you can change.

What it misses, because every detection misses something

Honesty about the gaps is what separates a detection you trust from one you hope about. This one won't see an attacker who paces themselves below your threshold, and it won't catch number-matching bypasses or token theft, which don't generate denied prompts at all. It also assumes push MFA; if you've already moved to phishing-resistant methods for privileged accounts, whole classes of this attack stop applying to those users, which is rather the point of moving.

None of that makes the query less worth running. It means this is one detection, not a strategy. The strategy is number matching turned on, phishing-resistant MFA for the accounts that matter most, enforced through Conditional Access, and this query watching for the accounts still on push.

Promote it slowly, same as always

Run it in the logs for a week or two first. Watch what it catches, tune the threshold and the window to your tenant, and only then wire it into a scheduled analytics rule with an automation that flags the account. The rule you understand at 2am is the rule that was built slowly at 2pm. That was true in the primer and it doesn't stop being true when the attack gets a scarier name.

The wider lesson is the one worth keeping. Good detections are rarely clever. This one is a count, a threshold and a join, and it catches an attack that has embarrassed some very large organisations. The gap was never the difficulty of the query. It was that nobody had opened the logs and asked.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Setting Up Microsoft Security Copilot: Capacity, Roles, and the Defaults That Bite

Alan Conlon — Tue, 28 Jul 2026 00:00:00 +0000

Before you do anything with Security Copilot, check whether it's already running. If your organisation is on Microsoft 365 E5 or E7, Microsoft may well have provisioned it for you, quietly, as part of the licence. It turns up with a monthly allocation of compute and a set of default roles already attached, and most people don't notice until someone opens the portal and starts poking around. That "it's already on" moment is exactly when the setup matters most, because the defaults are not what you'd choose if you'd sat down and thought about it.

I'll leave the incident-investigation side for another day. The bit nobody walks you through is the enablement and the permissions, and that's the bit that actually decides whether this thing is safe to have switched on.

Is Security Copilot already turned on in your tenant?

The licensing splits into two camps, and which one you're in changes everything about setup.

If you're on Microsoft 365 E5 or E7, Security Copilot is included and auto-provisioned. You get an allocation of Security Compute Units, the compute that powers it, scaled to your licence count: 400 units a month for every 1,000 paid user licences, up to a ceiling of 10,000. That allocation resets every month and doesn't roll over, so unused capacity just evaporates. Nothing to buy, nothing to stand up. It's on.

What do you need before provisioning Security Copilot capacity?

If you're not on E5 or E7, none of that applies. You provision the compute yourself, which means an Azure subscription and someone with the right roles to create the capacity. That's Azure Contributor or Owner on the subscription or resource group, plus Security Administrator or higher in the tenant. You also need to buy at least one provisioned SCU. Different starting line entirely, so confirm your licence position before you follow any guide, including this one. Half the confusion online comes from people following E5 steps without an E5 licence, or the reverse.

What you're actually paying for: compute units

Security Copilot runs on Security Compute Units, or SCUs. On E5 or E7 they come from the included monthly allowance, and none of this section's buying decisions apply. If you're buying standalone, they come in two flavours. Provisioned capacity is always-on. You decide how many units to keep warm, and you pay for them by the hour whether Copilot does a single thing or sits idle all weekend. Overage is the opposite: on-demand units that kick in when you exhaust the provisioned ones, billed only when used.

The gotcha is the provisioned side. It's billed hourly on what you've reserved, not on what you consume, so an oversized always-on allocation is money quietly leaving the building. If you're just standing it up to have a look, Microsoft's own recommendation is modest: three provisioned units with overage set to unlimited. On the standalone route you can't go lower than one provisioned unit, though. Microsoft requires a minimum of one, so there's no running purely on overage. One unit with overage on top is the cheapest way to trial it.

Provisioning the capacity needs more than a credit card. You'll want Azure Contributor or Owner on the subscription or resource group, plus Security Administrator or higher in the tenant. The usage dashboard keeps ninety days of history, and it's where you go to see what's actually being consumed before you decide how much to reserve. Look at real usage first, size the capacity second. There's a whole post on sizing SCUs without overpaying.

The default depends on when you deployed

Copilot Contributor isn't a Microsoft Entra role. Security Copilot has its own two platform roles, Owner and Contributor, that live inside Copilot and only govern access to the platform itself. Who gets Contributor out of the box is the part that has changed, and it's worth knowing which era your tenant belongs to.

New instances now default to the Recommended Microsoft Security roles group: a bundle that grants Contributor access only to users who already hold security-relevant Entra roles. That's a sensible middle ground, and if you're standing Copilot up fresh, it's what you'll get.

Older instances are a different story. Earlier deployments defaulted to granting Copilot Contributor to every user in the tenant through the built-in Everyone group, and existing customers who have that assignment keep it until someone removes it. So the day-one check is simple: open Role assignment and look at what's actually there. If the Everyone group is still assigned, remove it and replace it with either the recommended roles bundle or a scoped group of the people who should genuinely have access. One wrinkle to know before you act: once the Everyone group is removed, it can't be assigned again, so this is a one-way door (a good one, but a door).

If you do use your own groups, note that Security Copilot only supports role-assignable groups, and assign roles to groups rather than individuals. It's less to manage and far easier to review six months later.

Copilot inherits permissions, it doesn't grant them

This is the part that reassures people once they understand it, and trips them up before they do. Security Copilot uses on-behalf-of authentication, which means it can never see more than the signed-in user can already see. It has no privileges of its own. It borrows yours.

So Contributor access gets someone onto the platform, but it gets them no data. To actually pull Sentinel incidents, the user still needs a Sentinel role like Microsoft Sentinel Reader. To see devices and policies through the Intune connection, they need an Intune role. Defender is the same. The real scoping of what Copilot can touch happens in the underlying products, through their normal RBAC, not in Copilot's own settings. That's a good thing. It means your existing least-privilege model carries straight through.

It also means you should resist a tempting shortcut. Granting someone Security Administrator hands them Copilot Owner access automatically, but it also gives them a pile of tenant-wide security permissions that have nothing to do with Copilot. Microsoft says this plainly, and they're right: don't hand out a privileged role just to get someone into Copilot. Put the person in the scoped group instead.

Owners, and why PIM belongs here

A handful of Entra and Purview roles inherit Copilot Owner automatically: Global Administrator and Security Administrator as you'd expect, but also Billing Administrator, Intune Administrator and Compliance Administrator. A Billing Admin silently holding Copilot Owner is exactly the kind of default worth knowing about. Owner can manage capacity, plugins, workspaces and roles, so it's worth knowing exactly who holds it by inheritance rather than by design.

For those high-privilege roles, this is textbook territory for Privileged Identity Management. Make them eligible rather than permanently assigned, require activation, and you shrink the standing pool of people who can reconfigure the platform down to nobody until somebody actually needs it. While you're in Owner settings, turn on Logging audit data in Microsoft Purview, and make sure auditing is switched on in Purview itself. You'll want those logs long before you think you need them.

How do you set up Security Copilot workspaces?

If you're running more than one team or region, workspaces let you carve capacity up and right-size it per team, each backed by its own compute. If your tenant was set up automatically through E5 or E7, a default workspace was created for you, so it's already there whether you've looked at it or not.

If you're provisioning yourself, the first-run setup in the Security Copilot portal walks you through it:

Pick the Azure subscription that will pay for the capacity.
Pick or create a resource group.
Give the capacity a name.
Choose the prompt evaluation location.
Set the number of provisioned SCUs, and decide on overage.
Link the capacity to your workspace.

The rollout that works is the unexciting one. Start with a small provisioned allocation, even a single unit, a tightly scoped access group, owners locked behind PIM, audit logging on, and the underlying product RBAC doing the real gatekeeping. Watch the usage dashboard for a couple of weeks. Then, and only then, widen access and tune the capacity to what you've actually seen.

The interesting work, the investigations and the promptbooks, comes after all this. But none of it is safe or sensible until the boring part is done properly. Get the capacity sized, get the Everyone group out of Contributor, and let your existing permissions model do its job. That's the whole setup, and it's almost entirely things you already know how to do.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Conditional Access: Getting It Right Without Locking Yourself Out

Alan Conlon — Tue, 14 Jul 2026 00:00:00 +0000

Almost everyone who has run Entra ID for any length of time has a Conditional Access story, and it usually ends badly. Someone builds a sensible-looking policy, flips it straight to On, and within the hour the service desk is lighting up. Or worse: the person who built it can no longer sign in to turn it off. Conditional Access is one of the most powerful controls you have, and one of the easiest to hurt yourself with.

The mistakes are predictable, though. The same handful come up again and again, and none of them are exotic. Here are the ones worth knowing before you go near a production tenant.

Break-glass accounts first, and exclude them from everything

This is the one that saves you, so do it before anything else. You need at least two emergency access accounts: cloud-only, each with its own FIDO2 key or certificate (mandatory MFA applies to them too), not tied to any individual, and excluded from every Conditional Access policy you ever create.

The reasoning is blunt. Conditional Access can lock out everyone, and that includes you. A policy that demands a compliant device or a particular authentication method can, if it’s wrong, leave nobody able to sign in to switch it off. The break-glass accounts are your way back in. People skip this because it feels paranoid, right up until the afternoon it’s the only thing standing between them and a support ticket to Microsoft.

Exclude them from everything, then watch them like a hawk. Alert on every sign-in, because an account that bypasses all your controls is exactly what an attacker would love to find. There’s more on setting break-glass accounts up properly, including the alert query.

How do you roll out a policy without locking everyone out?

Report-only mode is the single best habit in Conditional Access, and the one most people skip because they’re in a hurry. A new policy set to report-only is evaluated against every sign-in and logged, but it doesn’t actually enforce anything. You get to see precisely who it would have blocked, and what it would have demanded, before it can do any damage.

Leave it there for a week or two and read the sign-in logs. You’ll nearly always find something you didn’t expect: a service account authenticating in a way you’d forgotten about, a group of users on a platform the policy quietly catches, a legitimate workflow you were about to break. Fix those first. Only then flip it to On.

The temptation to skip this is strongest exactly when you shouldn’t, when the policy feels obvious and low-risk. Those are the ones that bite.

Block legacy authentication: the high-value policy you’re putting off

If you do nothing else this year, block legacy authentication. Older protocols that predate modern auth, basic auth endpoints, legacy mail clients speaking POP, IMAP or SMTP AUTH, have no way to perform an MFA challenge. Conditional Access still sees them, but a policy that requires MFA can never be satisfied by them. Unless you block them outright, a password alone gets through. This is exactly why password spray attacks still work in 2026: attackers deliberately target the legacy endpoints because they know MFA won’t be in the way.

The reason people delay is that they’re not sure what still depends on it, and they’re afraid of breaking something. Which is fair. So this is another one for report-only first: switch on a block-legacy-auth policy in report-only, watch what gets caught, chase down whatever’s still using it, and migrate it. The boring legwork is the actual job here. The policy itself takes thirty seconds.

Report-only turns “I don’t know what’ll break” into a list of names.

“All users, all apps” is a bigger blast radius than it looks

It’s tempting to scope a policy to everyone and everything because it feels thorough. Sometimes that’s right. Often it’s how you catch the one account or workload you really shouldn’t have.

Think about the blast radius before you save. Who is genuinely in scope, and what happens to the edge cases: the guests, the service principals, the accounts synced from on-premises, the break-glass accounts, the one team with a workflow that doesn’t fit the rule? Scope deliberately, exclude what needs excluding, and know what you’re aiming the thing at. A precise policy you understand beats a broad one you’re nervous about.

Don’t build forty policies you’ll never understand again

Conditional Access estates rot the same way permissions do. Someone adds a policy for a one-off project, names it “Test 3,” and never removes it. A year later there are thirty-odd policies, several overlapping, a couple contradicting each other, and nobody fully sure what the live behaviour actually is.

Keep the set small and name them so a stranger could read the list and understand your intent: who, what, and the condition, right there in the name. Review them on a schedule, because they will drift, and the drift is invisible until an incident forces you to read every one. The hard part isn’t technical, it’s the discipline to delete the policy nobody remembers creating.

Test it before you trust it

Microsoft gives you a What-If tool that simulates how your policies apply to a given user, app and condition. Use it. It costs nothing and it’ll show you interactions between policies you wouldn’t spot by reading them one at a time: the two that combine to block someone, or the exclusion that quietly cancels the control you thought you’d applied.

Between What-If and report-only logs, you’ve no real excuse to be guessing. A Conditional Access policy you haven’t tested is a policy you’re hoping about, and hope isn’t a control.

What good Conditional Access looks like

Good Conditional Access means a small set of policies you understand, can explain, and can stand over at 2am when something fires, rather than the most policies. Build them slowly, roll them out in report-only, exclude your way back in, and document what each one is for. None of it needs cleverness, just care, and care counts for a lot when one wrong toggle can lock out the building.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.

Getting Started with Sentinel Hunting: A KQL Primer for People Who Keep Putting It Off

Alan Conlon — Tue, 30 Jun 2026 00:00:00 +0000

Most people who run Sentinel don't really hunt in it. They wire up the connectors, switch on a pile of built-in analytics rules, and then treat the whole thing as a box that pings them when something's wrong. Which is fine, until the day you actually need to go looking for something the built-in rules never thought to ask about. That's when you open the logs, stare at the query bar, and realise you've been avoiding KQL for two years.

I put it off myself for a long time. It looked like another query language to learn on top of everything else, and the examples online were either trivially basic or written by someone showing off. The truth is somewhere in between, and the useful middle is much smaller than it looks. You can get genuinely productive with about five operators. Everything else you pick up when you need it.

Where do you start with KQL in Sentinel?

The mistake most people make is reaching for a detection before they've ever looked at the raw data. Don't. Open the logs and run the most boring query there is:

SigninLogs
| take 10

That's it. take 10 grabs ten rows so you can see what's actually in the table, the column names, what the values look like and where the useful stuff lives. Sign-in logs are huge and nested, and you'll save yourself a lot of grief by seeing the shape of them before you try to be clever. Do the same with whatever table you care about. Look first, query second.

The pipe is the whole idea

KQL reads top to bottom, and every line starts with a pipe. Each step takes the table that came out of the line above and does one thing to it. That's the entire mental model. You're passing a table down a chain and narrowing or reshaping it as you go.

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| take 20

Read it as a sentence. Take the sign-in logs, keep the last 24 hours, keep only the failures, show me twenty. ResultType != "0" is the failures, because zero means success. The quotes matter too, the column holds text even though the values look like numbers. One of those things nobody tells you and everybody just has to learn.

Filter early, always

Here's the one habit worth building from day one: cut the data down before you do anything expensive with it. Put your where clauses high up, especially the time filter. Sentinel makes you wait for everything you scan, and on the cheaper data lake tiers it bills you for it too. So a query that filters to the last hour before it starts counting things will be faster and cheaper than one that counts everything and trims afterwards.

It's the difference between this:

SigninLogs
| where TimeGenerated > ago(1h)
| summarize count() by UserPrincipalName

and the version that summarises the whole table first and then filters. Same answer. Wildly different speed. Get the time bound in early and it becomes second nature.

Summarize is where it gets useful

where finds rows. summarize answers questions. The moment you want to know how many or how often rather than just show me, you're reaching for summarize.

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedAttempts = count() by UserPrincipalName
| sort by FailedAttempts desc

That counts failed sign-ins per user over the last day and puts the worst offenders at the top. It's about four lines and it's already more useful than half the dashboards I've seen. You can see where this goes: repeated failures clustered on one account in a short window is exactly the shape of someone being attacked, and you found it with five operators and no special tooling.

A commented copy of that query is in the free queries repo if you'd rather start from a file.

Don't trust a detection you can't read

This is the part that matters more than the syntax. There's a temptation, once you find a clever query online, to paste it into an analytics rule and move on. Resist it. A detection you don't understand is worse than no detection, because it gives you confidence you haven't earned. When it fires at 2am you need to know what it actually checked, and when it stays silent you need to know whether that means you're safe or whether the query was quietly broken the whole time.

So build them slowly. Get the query returning sensible rows in the logs first. Look at what it catches and what it misses. Only then promote it to a scheduled rule. The boring, incremental version is the one that works.

Where to go next

Five operators get you a long way: take, where, summarize, sort, and project (which trims the columns down to the ones you care about). Build a few queries by hand against the tables you already have: failed sign-ins, sign-ins from somewhere you don't operate, accounts that suddenly got handed an admin role. The detections that catch real attacks are rarely exotic. They're usually just someone who took the time to look. For a worked example built the same way, start with the MFA fatigue hunt.

That's the whole secret, honestly. The people who get value out of Sentinel aren't the ones who memorised the language. They're the ones who actually opened the logs and started asking questions.

I write about security, Azure, data, and the practical side of running things. If this was useful, connect with me on LinkedIn.

Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.