Salta al contingut
← Tornar al blog
8 min ca

KQL per a analistes SOC: consultes base a Defender

Consultes KQL de només lectura per investigar inicis de sessió, processos i correu a Microsoft Defender XDR: quina taula fer servir i com acotar el temps.

Escrit per Eduardo Olivares, analista SOC a Barcelona.

  • kql
  • microsoft defender
  • advanced hunting
  • soc
  • blue team
  • microsoft sentinel

KQL (Kusto Query Language) és el llenguatge amb què es consulta la telemetria de Microsoft Defender XDR i de Microsoft Sentinel. Aquest article reuneix quatre consultes de només lectura sobre inicis de sessió, processos i correu, amb les taules i columnes tal com figuren a Microsoft Learn el novembre de 2026. Són un punt de partida: els llindars i els noms de compte s'han d'adaptar a cada entorn, i convé provar-les primer en un tenant de proves.

Resposta curta: per caçar a Defender n'hi ha prou amb saber quina taula respon cada pregunta. SigninLogs o EntraIdSignInEvents per a identitats, DeviceProcessEvents per a processos, EmailEvents per al correu i CloudAppEvents per a l'activitat a Microsoft 365. Acota sempre el temps amb Timestamp > ago(...), filtra abans d'unir taules i projecta només les columnes que vagis a llegir.

Què és KQL i on s'executa

Al portal de Defender, la consulta s'escriu a Hunting > Advanced hunting. Aquesta eina permet explorar fins a 30 dies de dades crues de Defender per a Endpoint, Defender per a Office 365, Defender for Cloud Apps i Defender for Identity. Si hi ha un workspace de Microsoft Sentinel incorporat al portal, també es poden consultar les seves taules, amb la retenció que tingui configurada aquest workspace.

Cada taula depèn que el servei que l'omple estigui desplegat. DeviceProcessEvents ve de Defender per a Endpoint, EmailEvents de Defender per a Office 365 i CloudAppEvents de Defender for Cloud Apps, que a més necessita el connector d'activitats de Microsoft 365. Si el servei no hi és, la consulta no falla: simplement no retorna cap fila.

Tres regles abans de llançar res

  1. Filtra primer per temps. Una condició com Timestamp > ago(1d) al principi redueix el volum abans de qualsevol transformació.
  2. Filtra abans d'unir. Redueix cada taula abans del join, posa a l'esquerra la més petita i fes servir kind=inner: el tipus per defecte (innerunique) elimina files repetides de l'esquerra i pot amagar resultats.
  3. Projecta només el que és útil. Un project amb les columnes que vas a llegir fa els resultats llegibles i, abans d'un join, també més ràpids.

Un detall més de la guia de Microsoft: has rendeix millor que contains quan busques una paraula sencera.

Inicis de sessió sospitosos

Taula i columnes rellevants

SigninLogs és la taula d'inicis de sessió de Microsoft Entra ID a Log Analytics i Sentinel. Les columnes que més s'utilitzen en començar són TimeGenerated, UserPrincipalName, IPAddress, ResultType (0 indica èxit i qualsevol altre valor és un error), ResultDescription, Location, AppDisplayName i ClientAppUsed.

A l'esquema natiu de Defender la taula equivalent és EntraIdSignInEvents, amb Timestamp, AccountUpn, IPAddress, ErrorCode, Country i Application. Requereix llicència Microsoft Entra ID P2. La documentació anunciava que el 19 d'octubre de 2026 substituïa AADSignInEventsBeta i que les consultes antigues es migrarien automàticament. Si trobes material anterior amb AADSignInEventsBeta, canvia el nom de la taula i revisa les columnes.

Consulta: errors repetits seguits d'un èxit

Busca una IP que acumula cinc o més errors per a un compte i després aconsegueix entrar durant l'hora següent a l'últim error. El cinc i l'hora són valors d'exemple.

let ventana = 1h;
let fallos = SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType in ("50126", "50053")
| summarize Fallos = count(), UltimoFallo = max(TimeGenerated) by UserPrincipalName, IPAddress
| where Fallos >= 5;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| project UserPrincipalName, IPAddress, Exito = TimeGenerated
| join kind=inner fallos on UserPrincipalName, IPAddress
| where Exito > UltimoFallo and Exito - UltimoFallo < ventana
| project UserPrincipalName, IPAddress, Fallos, UltimoFallo, Exito

Aquí un error és una contrasenya incorrecta (50126) o un compte bloquejat (50053). Si comptes tot el que no sigui 0, també hi entren els avisos d'MFA pendent (50074, 50076) i el resultat s'omple de soroll. A més, SigninLogs només conté inicis de sessió interactius; els no interactius són a AADNonInteractiveUserSignInLogs.

El resultat no prova un compromís: algú que oblida la contrasenya genera el mateix patró. Per decidir, mira el país (Location), el client (ClientAppUsed) i si la IP apareix en altres comptes. Aquesta consulta agrupa per usuari, de manera que un atac que reparteix pocs intents entre molts comptes no el veurà.

Processos i línia d'ordres (DeviceProcessEvents)

DeviceProcessEvents registra la creació de processos. Les columnes habituals són FileName, ProcessCommandLine, AccountName, DeviceName, InitiatingProcessFileName i InitiatingProcessCommandLine. Microsoft recomana identificar el programa pel nom de fitxer i no per la línia d'ordres completa, i comparar sense distingir majúscules i minúscules (in~, =~), perquè una mateixa ordre es pot escriure de moltes maneres.

Consulta: PowerShell amb arguments codificats

El paràmetre -EncodedCommand de powershell.exe accepta una cadena en Base64 amb codificació UTF-16LE. Té usos legítims, així que aquesta consulta serveix per revisar, no per alertar a cegues.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine matches regex @"(?i)\s-(e|ec|en[a-z]*)\s"
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine,
    InitiatingProcessFileName, InitiatingProcessCommandLine
| order by Timestamp desc
| limit 100

L'expressió regular cobreix les abreviatures que PowerShell accepta per al paràmetre (-e, -ec, -enc i similars); prova-la primer en un tenant de proves. Per llegir-ne el contingut, descodifica el Base64 com a UTF-16LE fora de la consulta. Mira també el procés pare: un InitiatingProcessFileName que no és l'esperat sol aportar més que l'ordre en si.

Correu i regles de la safata d'entrada (EmailEvents, CloudAppEvents)

EmailEvents guarda el processament de cada correu. Per revisar el phishing que va arribar a la bústia interessen ThreatTypes, DeliveryLocation i LatestDeliveryLocation, que reflecteix la ubicació més recent del missatge, per exemple si es va retirar després de lliurar-se. EmailDirection pren els valors Inbound, Outbound i Intra-org.

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| where ThreatTypes has "Phish"
| project Timestamp, NetworkMessageId, SenderFromAddress, RecipientEmailAddress,
    Subject, DeliveryLocation, LatestDeliveryLocation, DetectionMethods
| order by Timestamp desc

Per a regles de la safata d'entrada i reenviaments, CloudAppEvents recull l'activitat d'Exchange Online. Els noms d'ActionType de la consulta surten de les guies de classificació d'alertes de Microsoft sobre reenviament i manipulació de la safata d'entrada.

CloudAppEvents
| where Timestamp > ago(7d)
| where ActionType in ("New-InboxRule", "UpdateInboxRules", "Set-InboxRule", "Set-Mailbox")
| project Timestamp, AccountDisplayName, AccountObjectId, ActionType,
    IPAddress, CountryCode, Isp, UserAgent, RawEventData
| order by Timestamp desc

La configuració de la regla ve a RawEventData. Compara IPAddress, Isp i CountryCode amb l'historial habitual de l'usuari.

Errors comuns

  • Límit de files: cada consulta retorna com a màxim 100.000 registres, i els resultats no poden superar els 64 MB.
  • Temps d'espera: una consulta que passa dels 10 minuts acaba en error. Redueix l'interval de temps o filtra abans.
  • Quota de CPU: si el tenant arriba al 100 %, les consultes es bloquegen fins al següent cicle de 15 minuts. El portal mostra si una consulta té ús Baix, Mitjà o Alt.
  • Fus horari: les consultes s'escriuen en UTC. El portal converteix els resultats al fus horari que tinguis configurat, i aquesta diferència confon en comparar amb altres fonts.
  • Retenció: 30 dies per a les dades natives de Defender. Per a més, cal Sentinel o exportar les dades.
  • Sense mida prevista: si no saps quantes files retornarà una consulta, executa primer count o afegeix limit.

Com s'enllaça amb el triatge i el BEC

Aquestes consultes responen preguntes concretes dins d'un flux més ampli. L'ordre en què mirar les alertes és a triatge d'alertes en un SOC. Les regles de la safata d'entrada i els inicis de sessió des d'ubicacions estranyes encaixen amb els senyals de business email compromise a Microsoft 365.

Preguntes freqüents

On executo aquestes consultes?

Les de Defender (DeviceProcessEvents, EmailEvents, CloudAppEvents) a Hunting > Advanced hunting del portal de Defender. SigninLogs, a Log Analytics o al portal de Defender si has incorporat el workspace de Sentinel.

Quants dies enrere puc consultar?

Fins a 30 dies de dades natives de Defender. Les taules de Sentinel depenen de la retenció configurada al workspace.

Faig servir SigninLogs o EntraIdSignInEvents?

Depèn d'on tinguis les dades. SigninLogs és la taula de Log Analytics i Sentinel; EntraIdSignInEvents és a l'esquema de Defender i exigeix llicència Entra ID P2. Els noms de columna canvien, així que no es poden intercanviar sense adaptar la consulta.

Per què la meva consulta no retorna res?

El més habitual és que el servei que omple la taula no estigui desplegat, que el connector de CloudAppEvents no tingui activada l'activitat de Microsoft 365 o que falti un permís d'accés a aquestes dades. Després, revisa l'interval de temps i els valors exactes dels filtres.

Les puc convertir en regles de detecció?

Microsoft indica que les mateixes consultes de hunting serveixen per crear regles de detecció personalitzades. Les quotes s'apliquen per separat a les consultes manuals i a les regles, així que optimitza abans de programar-les.