16.3 Activity Reconstruction

Activity Reconstruction

Build a complete picture of Marcus’ data access and transfer activity over the past 30 days. The goal is not to find a single smoking gun — it is to reconstruct the entire pattern and let the pattern tell the story.

Required role: Microsoft Sentinel Reader. Defender for Endpoint Security Reader (for device telemetry).

Step 1: Establish the baseline — what is normal for Marcus?

Before assessing whether recent activity is anomalous, establish what Marcus’ normal activity looks like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

// Marcus' file access baseline — 6 months prior to resignation
CloudAppEvents
| where TimeGenerated between(ago(180d) .. ago(30d))
| where AccountDisplayName == "Marcus Chen"
| where ActionType in ("FileDownloaded", "FileUploaded", "FileCopied",
    "FileAccessed", "FilePreviewed")
| summarize
    DailyAvg = count() / 150.0,  // ~150 work days in 6 months
    TotalEvents = count(),
    UniqueFiles = dcount(tostring(parse_json(RawEventData).ObjectId)),
    PeakDay = max(TimeGenerated)
    by ActionType

What this establishes: Marcus’ normal daily file access volume. If he typically downloads 5-10 files per day and the past 2 weeks show 50-100 per day, that is a 10x deviation — significant. If the past 2 weeks show 8-12 per day, that is within normal variation — not indicative of exfiltration.

Step 2: Recent activity — the investigation window

1
2
3
4
5
6
7
8

// Marcus' file activity in the past 30 days — the investigation window
CloudAppEvents
| where TimeGenerated > ago(30d)
| where AccountDisplayName == "Marcus Chen"
| where ActionType in ("FileDownloaded", "FileUploaded", "FileCopied",
    "FileAccessed", "FileSynced")
| summarize DailyCount = count() by bin(TimeGenerated, 1d), ActionType
| order by TimeGenerated asc

Visualise the trend. Export to Excel or use a Sentinel workbook. You are looking for a step change — a sudden increase in download volume that correlates with the resignation date (31 March) or the weeks leading up to it. Departing employees typically start exfiltration 2-4 weeks before their last day — often starting slowly and accelerating as the last day approaches.

Step 3: What files were accessed?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Specific files Marcus downloaded in the investigation window
CloudAppEvents
| where TimeGenerated > ago(30d)
| where AccountDisplayName == "Marcus Chen"
| where ActionType in ("FileDownloaded", "FileCopied", "FileSynced")
| extend FileName = tostring(parse_json(RawEventData).SourceFileName)
| extend FilePath = tostring(parse_json(RawEventData).ObjectId)
| extend FileSize = tolong(parse_json(RawEventData).FileSizeBytes)
| project TimeGenerated, ActionType, FileName, FilePath,
    FileSizeMB = round(FileSize / 1048576.0, 2)
| order by TimeGenerated desc

What to examine: File names and paths reveal what Marcus was accessing. Look for: proprietary design documents (CAD files, specifications, manufacturing drawings), client project files (proposal documents, contracts, deliverables), financial data (pricing models, cost structures), and source code or IP. Cross-reference with Marcus’ job responsibilities: accessing files related to his current projects is normal. Accessing files from projects he is not assigned to, or from historical archives, is anomalous.

Step 4: Device-level file operations

CloudAppEvents covers SharePoint and OneDrive. For local file operations (USB copy, local folder moves), use Defender for Endpoint telemetry:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Device file events — local file operations on Marcus' device
DeviceFileEvents
| where TimeGenerated > ago(30d)
| where DeviceName == "LAPTOP-NGE027"
| where InitiatingProcessAccountName == "m.chen"
| where ActionType in ("FileCreated", "FileModified", "FileRenamed")
| where FolderPath has_any ("USB", "Removable", "Downloads", "Desktop")
    or FolderPath matches regex @"[D-Z]:\\"  // Non-system drives (USB)
| project TimeGenerated, ActionType, FileName, FolderPath,
    FileSize, SHA256
| order by TimeGenerated desc

What to look for: Files created on removable drives (USB) — the FolderPath will show a non-system drive letter (D:, E:, F:). Files moved to the Downloads folder or Desktop (staging for transfer). Files renamed to obscure their content (“project-docs.zip” renamed to “photos-march.zip”).

Step 5: Build the activity timeline

Compile the findings into a chronological timeline:

This timeline is the primary evidence in the investigation report. Each row must be supported by the query output — time, file name, path, size, and the source query.

Subsection artifact: The 4-step activity reconstruction queries and the timeline template. These form the core of your insider threat investigation playbook.

Knowledge check

Contextualising the findings — legitimate vs suspicious

Raw data shows what happened. Context determines whether it is suspicious. For every anomaly, consider these questions before reporting to HR:

Is there a legitimate business reason? A 5x increase in file downloads the week before a project deadline is normal. A 5x increase the week after resignation with no active project assignments is suspicious. Check the subject’s current project workload with their manager (via HR — not directly).

Is the access within the subject’s normal scope? A senior engineer downloading design documents from their own project sites is normal. The same engineer downloading documents from 5 project sites they are not assigned to — including archived projects from 3 years ago — is anomalous. Cross-reference accessed files against the subject’s SharePoint site memberships and project assignments.

Is the timing unusual? File downloads during business hours from the office IP may be normal work. The same downloads at 23:00 from a home IP the night after resignation may indicate staging for exfiltration. Correlate timing with the subject’s normal work patterns.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// Contextualise: what SharePoint sites did the subject access that they don't normally?
// Compare investigation window to baseline
let BaselineSites = CloudAppEvents
| where TimeGenerated between(ago(180d) .. ago(30d))
| where AccountDisplayName == "Marcus Chen"
| where ActionType in ("FileDownloaded", "FileAccessed")
| extend SiteUrl = tostring(parse_json(RawEventData).SiteUrl)
| distinct SiteUrl;
CloudAppEvents
| where TimeGenerated > ago(30d)
| where AccountDisplayName == "Marcus Chen"
| where ActionType in ("FileDownloaded", "FileAccessed")
| extend SiteUrl = tostring(parse_json(RawEventData).SiteUrl)
| distinct SiteUrl
| join kind=leftanti BaselineSites on SiteUrl
| project NewSite = SiteUrl, Status = "ACCESSED IN INVESTIGATION WINDOW — NOT IN BASELINE"

Sites that appear in the investigation window but NOT in the 6-month baseline are new access — the subject is downloading from sites they have not previously used. This is the strongest file access anomaly indicator.