The KQL User Audit Playbook: Your Template for Investigations

Alright, class. Take your seats.

It’s 3 PM on a Tuesday. An urgent message lands in your inbox from HR, Legal, or maybe even the CISO. The subject line is simple: "Urgent request: User Activity."

A user is under investigation, and they need a full accounting of their digital life. What have they been doing? Where have they been logging in from? What files have they accessed? Suddenly, you're not a threat hunter chasing an external adversary; you're a digital archaeologist, tasked with reconstructing a single user's timeline from a mountain of log data.

This is a core task for any SOC analyst or IT admin, and it's one where you need to be both fast and thorough. Fumbling with queries under pressure isn't an option. You need a ready-made playbook.

Consider this your template. Below is a comprehensive set of KQL queries designed to give you a 360-degree view of a user's activity. We'll follow the exact workflow of a real investigation: first, establish identity. Then, map the footprint. Finally, follow the activity trail. Let's get digging.

Part 1: Establishing Identity – Who Are We Investigating?

Before you dive into a single sign-in or process log, you need to answer the most basic question: Who is this person in the context of our organisation? What's their role? What groups are they in? What access should they even have? Without this baseline, you're just looking at data without context.

The Official Identity Record

This is ground zero. It’s the "HR file" of your digital world. It tells you their job title, department, and group memberships, which is crucial for understanding if their subsequent activity is normal or suspicious.

let UPN_ = "user@domain.com";
IdentityInfo
| where AccountUPN == UPN_
| project AccountUPN, GivenName, Surname, GroupMembership, Department, JobTitle, IsAccountEnabled, UserType

Let's break it down: This query pulls from IdentityInfo, a summarized table that holds the ground truth from Entra ID. The GroupMembership field is especially valuable. If you see this user accessing financial data later, but they aren't in the "Finance" group, you've already found a major red flag.

The Magic Link: Entra ID User Profile

Sometimes you need to jump from the command line to the GUI. This handy query constructs a direct, clickable URL to the user's profile in the Azure portal.

let User_UPN = "user@domain.com";
SigninLogs
| where ResultType == 0
| where UserPrincipalName == User_UPN
| take 1
| project UserProfile = strcat("https://portal.azure.com/#blade/Microsoft_AAD_IAM/UserDetailsMenuBlade/Profile/userId/", UserId)

Let's break it down: This isn't an analysis query; it's a utility. It grabs just one successful sign-in to find the user's immutable UserId and then uses strcat to build the full URL. Copy the result, paste it into your browser, and you're looking at their live profile.

Part 2: The Access Footprint – Where and How?

Now that we know who they are, we can start mapping their digital footprint. This is about answering the fundamental questions: where, when, and from what devices has this user been active?

The Full-Spectrum Sign-in Report

This is your broad overview of all cloud sign-in activity. It’s your first look at their "pattern of life."

let UserPrincipalName_ = "user@domain.com"; 
SigninLogs 
| where UserPrincipalName has UserPrincipalName_
| extend City = tostring(LocationDetails.city),
         State = tostring(LocationDetails.state),
         CountryOrRegion = tostring(LocationDetails.countryOrRegion),
         DeviceDisplayName = tostring(DeviceDetail.displayName),
         DeviceOperatingSystem = tostring(DeviceDetail.operatingSystem),
         StatusFailureReason = tostring(Status.failureReason)
| project TimeGenerated, 
          OperationName, 
          UserDisplayName, 
          IPAddress, 
          UserType, 
          AppDisplayName, 
          ClientAppUsed, 
          Location, 
          DeviceDisplayName, 
          DeviceOperatingSystem, 
          City, 
          State,
          CountryOrRegion,
          StatusFailureReason
| sort by TimeGenerated desc

Let's break it down: This query uses extend to crack open the LocationDetails and DeviceDetail JSON blobs, turning messy nested data into filterable columns. It’s the perfect starting point for seeing their locations, devices, and any failed login attempts.

Pinpointing Interactive Device Logons

Cloud sign-ins are one thing, but what about direct machine access? This query zeroes in on successful interactive logons to a specific endpoint.

let DeviceName_ = "<Device_Name>"; 
DeviceLogonEvents
| where DeviceName == DeviceName_
| where ActionType == "LogonSuccess" 
| where LogonType == "Interactive"
| where InitiatingProcessCommandLine has "lsass.exe"
| project TimeGenerated, DeviceName, AccountName, LogonType, InitiatingProcessCommandLine
| sort by TimeGenerated desc

Let's break it down: LogonType == "Interactive" is the key. It filters out system services and scheduled tasks to show you only when a user was at the keyboard or in an RDP session.

The Last Known Location

When an incident is active, you often need one piece of information immediately: what were the full details of the user's absolute last login?

let UserPrincipalName_ = "user@domain.com";
SigninLogs
| where UserPrincipalName has UserPrincipalName_
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| project TimeGenerated,
          UserPrincipalName,
          AppDisplayName,
          IPAddress,
          Location = LocationDetails,
          DeviceDetail,
          ConditionalAccessStatus,
          UserType

Let's break it down: summarize arg_max(TimeGenerated, *) is a powerhouse. It tells KQL: "For this user, find the single event with the most recent timestamp, and give me back all the columns from that one specific row." It's the fastest way to get a complete snapshot.

Part 3: The Activity Trail – What Did They Do?

With identity and access established, we move to the core of the investigation: what actions did the user take?

Tying it to Sentinel: Recent Alerts

Before you go any further, find out what your security tools already know. Has Sentinel already generated an alert or incident for this user?

let User_UPN = "user@domain.com";
SecurityAlert
| where TimeGenerated > ago(30d)
| mv-expand todynamic(Entities)
| extend EntityName = tostring(Entities.DisplayName)
| where isnotempty(EntityName)
| where Entities contains User_UPN
| project TimeGenerated, AlertName, Description, ProviderName, ConfidenceLevel, EntityName
| sort by TimeGenerated desc

Let's break it down: This is an easy query that we want to use to see the number of alerts on which entities will contain User_UPN We can achieve this result by using mv-expand and extend to bring Entity Name value in front.

Entra ID Audit Trail

Did they reset passwords? Create users? Change permissions? The AuditLogs table holds the answers.

let UserPrincipalName_ = "user@domain.com";
AuditLogs
| extend InitiatorUPN = tostring(InitiatedBy.user.userPrincipalName)
| where isnotempty(InitiatorUPN) and InitiatorUPN has UserPrincipalName_
| mv-expand TargetResources
| extend TargetUPN = tostring(TargetResources.userPrincipalName),
         ModifiedProperties = TargetResources.modifiedProperties
| project TimeGenerated,
          InitiatorUPN,
          OperationName,
          ActivityDisplayName,
          TargetUPN,
          Result,
          ResultReason,
          ModifiedProperties
| sort by TimeGenerated desc

Let's break it down: This is a masterclass in handling complex logs. It pulls out the initiator's UPN, then uses mv-expand on TargetResources because one action can affect multiple objects. This gives you a precise audit of who did what to whom.

Top Applications Accessed

What's their normal application usage pattern? Is a user from finance suddenly accessing developer tools?

let User_UPN = "user@domain.com";
SigninLogs
| where TimeGenerated > ago(30d)
| where UserPrincipalName == User_UPN
| summarize AppAccessCount = count() by AppDisplayName
| where isnotempty(AppDisplayName)
| top 50 by AppAccessCount desc

Let's break it down: A straightforward but powerful use of summarize and top. This gives you a ranked list of the user's most-used applications, making outliers easy to spot.

Part 4: The Data and Communication Trail

Finally, we follow the data itself. What files did they touch, and who did they talk to?

File Access, Download, and Sharing History

These queries dive into OfficeActivity to see how the user interacted with files in SharePoint and OneDrive.

Files Accessed & Downloaded:

let UserPrincipalName_ = "user@domain.com"; 
OfficeActivity 
| where RecordType == "SharePointFileOperation" 
| where UserId has UserPrincipalName_ and Operation in ("FileAccessed", "FileDownloaded")
| project TimeGenerated, UserId, Operation, SourceFileName, Site_Url, OfficeWorkload, ClientIP

File Sharing:

let UserPrincipalName_ = "user@domain.com"; 
OfficeActivity
| where Operation in ("SecureLinkCreated", "AddedToSecureLink", "SharingLinkUpdated", "SharingLinkCreated", "AnonymousLinkCreated") 
| where UserId has UserPrincipalName_ 
| summarize GuestShares = count(), 
           SharedLinks = make_set(OfficeObjectId), 
            SharedToUsers = make_set(TargetUserOrGroupName) by bin(TimeGenerated, 1d), Sharer = UserId
| sort by TimeGenerated desc

Most Frequently Accessed Files:

let User_UPN = "user@domain.com";
OfficeActivity
| where TimeGenerated > ago(30d)
| where UserId == User_UPN
| where Operation in ("FileAccessed", "FileDownloaded", "FileModified", "FileUploaded")
| summarize AccessCount = count() by SourceFileName, SourceFileExtension, Operation
| where isnotempty(SourceFileName)
| top 50 by AccessCount desc

Email and Attachment Trail

Who is this user sending emails to? And what attachments are they sending?

Outbound Email:

let Sender_ = "user@domain.com";
EmailEvents
| where SenderFromAddress == Sender_ and EmailDirection == "Outbound"
| project TimeGenerated, RecipientEmailAddress, Subject, ThreatTypes, DetectionMethods
| sort by TimeGenerated desc

Attachment Forensics:

let Sender_ = "user@domain.com";
EmailAttachmentInfo
| where TimeGenerated > ago(30d)
| where SenderFromAddress has Sender_
| project FileName, FileType, SHA256, SenderFromAddress, RecipientEmailAddress

Let's break it down: The first query gives you a communication log. The second is for forensics; the SHA256 hash is a unique fingerprint you can use to hunt for a malicious attachment across your entire environment.

You now have a complete, well-rounded audit of the user's digital life. Each of these queries is a piece of the puzzle. Together, they provide the comprehensive story you need to answer that urgent request with confidence and precision.

Class dismissed.