NYC Health + Hospitals Breach: Detecting and Mitigating Large-Scale PHI Data Exfiltration

---
title: Potential SQL Database Dumping via sqlcmd
id: 4a3f1c82-9e4b-4d67-bc12-3e5a8f901234
status: experimental
description: Detects the use of sqlcmd to output query results to a file, a common method for database staging prior to exfiltration.
references:
  - https://attack.mitre.org/techniques/T1005/
author: Security Arsenal
date: 2024/04/10
tags:
  - attack.collection
  - attack.t1005
logsource:
  category: process_creation
  product: windows
detection:
  selection:
    Image|endswith: '\sqlcmd.exe'
    CommandLine|contains:
      - '-o '
      - '-o"'
      - 'output '
  condition: selection
falsepositives:
  - Legitimate administrative database exports
level: high
---
title: Mass Compression in User Directories
id: 5b2f1c99-1e5c-4e78-ad12-4e5a8f901567
status: experimental
description: Detects high-volume compression processes (7z, winrar) running in user profile directories, indicative of data staging for exfiltration.
references:
  - https://attack.mitre.org/techniques/T1560/
author: Security Arsenal
date: 2024/04/10
tags:
  - attack.collection
  - attack.t1560.001
logsource:
  category: process_creation
  product: windows
detection:
  selection_img:
    Image|endswith:
      - '\7z.exe'
      - '\winrar.exe'
      - '\rar.exe'
  selection_cli:
    CommandLine|contains:
      - '-tzip'
      - '-a'
  selection_path:
    CurrentDirectory|contains:
      - 'C:\Users\'
      - 'C:\ProgramData\'
  condition: all of selection_*
falsepositives:
  - Users backing up their own files
level: medium

// Hunt for large outbound data transfers or suspicious database queries
// Look for sqlcmd processes writing to disk
DeviceProcessEvents
| where Timestamp >= ago(7d)
| where FileName in~ ("sqlcmd.exe", "bcp.exe")
| where ProcessCommandLine has_any ("-o", "output", "queryout")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName
| extend FileDest = extract(@'output\s*["']?(.*?)\s*["']?', 1, ProcessCommandLine)
| summarize count() by DeviceName, AccountName, FileDest
| where count_ > 10 // Threshold for bulk operations

-- Hunt for recently created archive files in user directories
SELECT FullPath, Size, Mtime, Mode
FROM glob(globs="/*/*.zip", root="/C:/Users/")
WHERE Mtime > now() - 7d
  AND Size > 10000000 // Greater than 10MB

-- Hunt for sqlcmd execution
SELECT Pid, Name, CommandLine, Exe
FROM pslist()
WHERE Name =~ "sqlcmd.exe"
  AND CommandLine =~ "-o"

# Script to Audit Recent Database Export Activities and Compressed Files
# Requires Administrative Privileges

Write-Host "[+] Starting PHI Exfiltration Audit..." -ForegroundColor Cyan

# 1. Check for recent sqlcmd usage in Event Logs
Write-Host "[*] Checking for sqlcmd execution in Security Log..." -ForegroundColor Yellow
$Events = Get-WinEvent -LogName Security -FilterXPath "*[System[(EventID=4688)]] and *[EventData[Data[@Name='NewProcessName'] and (contains(string(), 'sqlcmd.exe'))]]" -ErrorAction SilentlyContinue

if ($Events) {
    $RecentEvents = $Events | Where-Object { $_.TimeCreated -gt (Get-Date).AddDays(-7) }
    Write-Host "[!] Found $($RecentEvents.Count) sqlcmd executions in the last 7 days." -ForegroundColor Red
    $RecentEvents | Select-Object TimeCreated, Id, Message | Format-List
} else {
    Write-Host "[-] No recent sqlcmd execution found in logs." -ForegroundColor Green
}

# 2. Scan for large ZIP files created in User Profiles
Write-Host "[*] Scanning User Profiles for large ZIP files..." -ForegroundColor Yellow
$ZipFiles = Get-ChildItem -Path "C:\Users" -Recurse -Include "*.zip", "*.rar", "*.7z" -ErrorAction SilentlyContinue | 
             Where-Object { $_.LastWriteTime -gt (Get-Date).AddDays(-7) -and $_.Length -gt 10MB }

if ($ZipFiles) {
    Write-Host "[!] Found $($ZipFiles.Count) suspicious compressed files (>10MB, created <7 days ago)." -ForegroundColor Red
    $ZipFiles | Select-Object FullName, Length, LastWriteTime
} else {
    Write-Host "[-] No suspicious compressed files found." -ForegroundColor Green
}