OSQuery Integration

Aquilon DLP exposes security findings through OSQuery virtual tables. This guide covers the available tables, column schemas, query examples, and alert triage workflows.

Overview

Aquilon DLP registers as an OSQuery extension, providing custom tables that can be queried using standard SQL. All interaction with Aquilon DLP data occurs through OSQuery queries.

Prerequisites

• OSQuery installed and running
• Aquilon DLP extension loaded (automatic with package installation)

aquilon_dlp_alerts Table

The primary table for accessing DLP findings and managing alert triage.

Column Reference

JSON Columns

The triage and context columns contain JSON data for flexible querying.

triage Column

Contains triage workflow information:

{
  "owner": "analyst@company.com",
  "comment": "False positive - test data file",
  "timestamp": 1727794245
}

Empty fields are omitted. An alert with no triage data has an empty object: {}

context Column

Contains file metadata, text snippets, and container information:

{
  "snippet": "...text around the match...",
  "keywords": ["ssn", "pii"],
  "file": {
    "hash": "d7c4529ffe273e1dc...",
    "size": 20666,
    "container": {
      "path": "archive.zip/inner.txt",
      "depth": 1
    }
  },
  "metadata": {"gdpr_article": "Article-4"}
}

The container object is only present when the finding is inside an archive (depth > 0).

Querying JSON Columns

Use SQLite JSON_EXTRACT to query JSON fields:

-- Extract file hash from context
SELECT path, JSON_EXTRACT(context, '$.file.hash') as file_hash
FROM aquilon_dlp_alerts
LIMIT 5;

-- Filter by file size
SELECT path, scanner, JSON_EXTRACT(context, '$.file.size') as size
FROM aquilon_dlp_alerts
WHERE CAST(JSON_EXTRACT(context, '$.file.size') AS INTEGER) > 10000;

-- Find findings inside containers
SELECT path, JSON_EXTRACT(context, '$.file.container.path') as container_path
FROM aquilon_dlp_alerts
WHERE JSON_EXTRACT(context, '$.file.container.depth') > 0;

-- Query triage owner
SELECT path, JSON_EXTRACT(triage, '$.owner') as owner
FROM aquilon_dlp_alerts
WHERE JSON_EXTRACT(triage, '$.owner') IS NOT NULL;

Basic Queries

View Recent Alerts

-- All alerts from last 24 hours
SELECT path, scanner, severity, policy, timestamp
FROM aquilon_dlp_alerts
WHERE timestamp > (strftime('%s', 'now') - 86400)
ORDER BY timestamp DESC;

Filter by Severity

-- Critical and high severity alerts
SELECT path, scanner, policy, confidence, match_count
FROM aquilon_dlp_alerts
WHERE severity IN ('critical', 'high')
ORDER BY severity, timestamp DESC;

Filter by Policy

-- HIPAA violations
SELECT path, scanner, severity, data_type
FROM aquilon_dlp_alerts
WHERE policy = 'HIPAA'
ORDER BY timestamp DESC;

-- PCI DSS violations
SELECT path, scanner, severity, match_count
FROM aquilon_dlp_alerts
WHERE policy = 'PCI_DSS'
ORDER BY timestamp DESC;

Group by Scanner Type

-- Count findings by scanner
SELECT scanner, COUNT(*) as count,
       AVG(confidence) as avg_confidence
FROM aquilon_dlp_alerts
GROUP BY scanner
ORDER BY count DESC;

Files with Multiple Finding Types

-- Files containing multiple types of sensitive data
SELECT path,
       GROUP_CONCAT(DISTINCT scanner) as scanners,
       COUNT(*) as total_findings
FROM aquilon_dlp_alerts
GROUP BY path
HAVING COUNT(DISTINCT scanner) > 1
ORDER BY total_findings DESC
LIMIT 20;

Container/Archive Findings

-- Findings within archives (ZIP, TAR, etc.)
SELECT path,
       JSON_EXTRACT(context, '$.file.container.path') as container_path,
       JSON_EXTRACT(context, '$.file.container.depth') as container_depth,
       scanner, severity
FROM aquilon_dlp_alerts
WHERE JSON_EXTRACT(context, '$.file.container.depth') > 0
ORDER BY container_depth DESC, timestamp DESC;

Compliance Reporting

HIPAA PHI Summary

SELECT
  scanner,
  severity,
  COUNT(*) as count
FROM aquilon_dlp_alerts
WHERE policy = 'HIPAA'
GROUP BY scanner, severity
ORDER BY
  CASE severity
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    ELSE 4
  END;

PCI DSS Cardholder Data

SELECT path, scanner, match_count, timestamp
FROM aquilon_dlp_alerts
WHERE policy = 'PCI_DSS'
  AND scanner IN ('credit_card', 'magnetic_stripe', 'cvv')
ORDER BY timestamp DESC;

GDPR Personal Data

SELECT
  scanner,
  COUNT(*) as exposures,
  COUNT(DISTINCT path) as files_affected
FROM aquilon_dlp_alerts
WHERE policy = 'GDPR'
GROUP BY scanner
ORDER BY exposures DESC;

Alert Triage

The aquilon_dlp_alerts table supports UPDATE operations for managing alert lifecycle. This allows security analysts to acknowledge, investigate, and resolve alerts directly through OSQuery.

Triage Status Values

Updating Triage Status

Use OSQuery UPDATE statements to manage alert triage. The triage_status column is a flat column, while triage is a JSON column containing owner, comment, and timestamp.

Acknowledge an Alert

UPDATE aquilon_dlp_alerts
SET triage_status = 'acknowledged',
    triage = JSON_OBJECT('owner', 'analyst@company.com', 'comment', 'Investigating potential data exposure')
WHERE path = '/data/reports/customer_export.csv'
  AND scanner = 'ssn';

Resolve an Alert

UPDATE aquilon_dlp_alerts
SET triage_status = 'resolved',
    triage = JSON_OBJECT('owner', JSON_EXTRACT(triage, '$.owner'), 'comment', 'File moved to secure location and access restricted')
WHERE path = '/data/reports/customer_export.csv'
  AND scanner = 'ssn';

Mark as False Positive

UPDATE aquilon_dlp_alerts
SET triage_status = 'ignored',
    triage = JSON_OBJECT('owner', 'security-team', 'comment', 'False positive - test data file with synthetic SSNs')
WHERE path = '/test/fixtures/sample_data.txt';

Bulk Triage by Policy

-- Acknowledge all new PCI alerts for investigation
UPDATE aquilon_dlp_alerts
SET triage_status = 'acknowledged',
    triage = JSON_OBJECT('owner', 'pci-compliance-team')
WHERE policy = 'PCI_DSS'
  AND triage_status = 'new';

Triage Workflow Queries

Alerts Needing Review

-- New alerts requiring triage
SELECT path, scanner, severity, policy, timestamp
FROM aquilon_dlp_alerts
WHERE triage_status = 'new'
ORDER BY
  CASE severity
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    ELSE 3
  END,
  timestamp DESC;

My Assigned Alerts

-- Alerts assigned to specific analyst
SELECT path, scanner, severity, triage_status,
       JSON_EXTRACT(triage, '$.comment') as triage_comment
FROM aquilon_dlp_alerts
WHERE JSON_EXTRACT(triage, '$.owner') = 'analyst@company.com'
  AND triage_status IN ('new', 'acknowledged')
ORDER BY timestamp DESC;

Triage Summary

-- Overview of triage status
SELECT
  triage_status,
  COUNT(*) as count
FROM aquilon_dlp_alerts
GROUP BY triage_status
ORDER BY
  CASE triage_status
    WHEN 'new' THEN 1
    WHEN 'acknowledged' THEN 2
    WHEN 'resolved' THEN 3
    WHEN 'ignored' THEN 4
  END;

Recently Resolved

-- Alerts resolved in last 7 days
SELECT path, scanner,
       JSON_EXTRACT(triage, '$.owner') as triage_owner,
       JSON_EXTRACT(triage, '$.comment') as triage_comment,
       JSON_EXTRACT(triage, '$.timestamp') as triage_timestamp
FROM aquilon_dlp_alerts
WHERE triage_status = 'resolved'
  AND JSON_EXTRACT(triage, '$.timestamp') > (strftime('%s', 'now') - 604800)
ORDER BY triage_timestamp DESC;

Triage Notes

• The triage.timestamp is automatically set when you update triage fields
• INSERT and DELETE operations are not supported - alerts are generated only by the scanner
• Triage updates persist in the SQLite database
• Multiple alerts for the same file/scanner combination can be updated individually or in bulk

aquilon_config Table (Enterprise)

Enterprise Only: This table is only available in Aquilon DLP Enterprise edition.

The configuration table exposes all Aquilon DLP settings as queryable rows with a simple key-value schema. Scalar values are stored as strings, and arrays are stored as JSON arrays.

Column Reference

Basic Queries

View All Configuration

-- View all configuration keys and their current values
SELECT key, value
FROM aquilon_config
ORDER BY key;

Query Scan Settings

-- All scan-related configuration
SELECT key, value
FROM aquilon_config
WHERE key LIKE 'scan.%'
ORDER BY key;

View Array Configuration (as JSON)

Arrays are stored as JSON arrays. Use json_each() to expand them:

-- View array keys (value contains JSON array)
SELECT key, value
FROM aquilon_config
WHERE key IN ('watch_paths', 'exclude_paths', 'policies.enabled_policies')
ORDER BY key;

-- Expand array to individual rows
SELECT c.key, j.value AS item
FROM aquilon_config c, json_each(c.value) j
WHERE c.key = 'watch_paths';

Check Specific Setting

-- Get current value of a specific setting
SELECT key, value
FROM aquilon_config
WHERE key = 'scan.max_scan_size_mb';

Modifying Configuration

The aquilon_config table supports runtime modifications for mutable settings. Changes take effect immediately and persist to the configuration file.

Operation Rules:

• UPDATE: Set value for any mutable key (scalars as strings, arrays as JSON)
• DELETE: Reset mutable key to its compiled default value
• INSERT: Not supported (keys are predefined)
• Immutable keys: Cannot be modified at runtime

Update Settings

Use UPDATE to modify any mutable configuration value:

-- Update max scan size (integer setting)
UPDATE aquilon_config
SET value = '100'
WHERE key = 'scan.max_scan_size_mb';

-- Enable/disable cache (boolean setting)
UPDATE aquilon_config
SET value = 'false'
WHERE key = 'cache.enabled';

-- Update CPU limit (float setting)
UPDATE aquilon_config
SET value = '75.5'
WHERE key = 'resource_limits.max_cpu_percent';

Update Array Settings

Arrays are stored as JSON. Use UPDATE with a JSON array value:

-- Set exclusion paths (replaces entire array)
UPDATE aquilon_config
SET value = '["/var/log/*", "/tmp/*", "*.swp"]'
WHERE key = 'exclude_paths';

-- Set watch paths
UPDATE aquilon_config
SET value = '["/home/%%", "/data/sensitive/*"]'
WHERE key = 'watch_paths';

Reset to Default

Use DELETE to reset a mutable key to its compiled default value:

-- Reset exclusion paths to default
DELETE FROM aquilon_config
WHERE key = 'exclude_paths';

-- Reset cache TTL to default
DELETE FROM aquilon_config
WHERE key = 'cache.ttl_secs';

Configuration Operation Reference

Common mutable scalar keys:

• scan.max_scan_size_mb - Maximum file size to scan (MB)
• scan.max_findings_per_scanner - Limit findings per scanner
• cache.enabled - Enable/disable file hash cache
• cache.ttl_secs - Cache time-to-live
• resource_limits.enabled - Enable CPU/memory limits
• resource_limits.max_cpu_percent - CPU usage limit

Common mutable array keys (use JSON arrays):

• watch_paths - Paths to monitor for changes
• exclude_paths - Glob patterns for paths to skip
• policies.enabled_policies - Active policy names

Error Cases

Understanding error messages helps diagnose configuration issues. The examples below show common mistakes and the errors they produce.

Immutable Key Error

Attempting to modify or delete an immutable key:

-- This will fail: database_path is immutable
UPDATE aquilon_config
SET value = '/new/path/aquilon.db'
WHERE key = 'database_path';

Error: Key 'database_path' is immutable

-- This will also fail
DELETE FROM aquilon_config
WHERE key = 'database_path';

Error: Key 'database_path' is immutable

INSERT Not Supported

INSERT operations are not supported (keys are predefined):

INSERT INTO aquilon_config (key, value)
VALUES ('custom_key', 'some_value');

Error: INSERT not supported, use UPDATE to modify values

Invalid Value Type

Providing wrong type for a key:

-- This will fail: expects integer
UPDATE aquilon_config
SET value = 'not_a_number'
WHERE key = 'scan.max_scan_size_mb';

Error: Invalid value for 'scan.max_scan_size_mb': expected Integer

Invalid JSON for Array

Providing invalid JSON for an array key:

-- This will fail: not valid JSON array
UPDATE aquilon_config
SET value = '/path1, /path2'
WHERE key = 'exclude_paths';

Error: Invalid value for 'exclude_paths': expected JSON array

Value Out of Range

Providing a value outside allowed range:

-- This will fail: max_scan_size_mb has limits
UPDATE aquilon_config
SET value = '999999'
WHERE key = 'scan.max_scan_size_mb';

Error: Value 999999 out of range for 'scan.max_scan_size_mb' (1-10000)

Troubleshooting with Config Table

The config table helps diagnose scanning issues by exposing current settings.

Why Isn’t My File Being Scanned?

Check if the file path matches an exclusion pattern:

-- Check current exclusion patterns (stored as JSON array)
SELECT key, value
FROM aquilon_config
WHERE key = 'exclude_paths';

-- Expand exclusion patterns for easier reading
SELECT j.value AS excluded_pattern
FROM aquilon_config c, json_each(c.value) j
WHERE c.key = 'exclude_paths';

Check Active Policies

Verify which policies are enabled:

-- List enabled policies (stored as JSON array)
SELECT key, value
FROM aquilon_config
WHERE key = 'policies.enabled_policies';

-- Expand policies for easier reading
SELECT j.value AS policy_name
FROM aquilon_config c, json_each(c.value) j
WHERE c.key = 'policies.enabled_policies';

Diagnose Performance Issues

Check resource limits and scan settings:

-- Check resource and scan limits
SELECT key, value
FROM aquilon_config
WHERE key LIKE 'resource_limits.%'
   OR key LIKE 'scan.%'
ORDER BY key;

Verify Cache Configuration

Check if caching is enabled and its settings:

-- Check cache settings
SELECT key, value
FROM aquilon_config
WHERE key LIKE 'cache.%'
ORDER BY key;

Command Line Usage

Interactive Queries

# Start OSQuery interactive shell
osqueryi

# Run a query
osqueryi "SELECT * FROM aquilon_dlp_alerts LIMIT 10;"

JSON Output

# Get results as JSON for scripting
osqueryi --json "SELECT path, scanner, severity FROM aquilon_dlp_alerts WHERE severity = 'critical';"

Scheduled Queries

Configure scheduled queries in /etc/osquery/osquery.conf:

{
  "schedule": {
    "dlp_critical_alerts": {
      "query": "SELECT * FROM aquilon_dlp_alerts WHERE severity = 'critical' AND triage_status = 'new'",
      "interval": 300,
      "description": "Critical DLP alerts needing triage"
    },
    "dlp_daily_summary": {
      "query": "SELECT scanner, severity, COUNT(*) as count FROM aquilon_dlp_alerts WHERE timestamp > (strftime('%s', 'now') - 86400) GROUP BY scanner, severity",
      "interval": 86400,
      "description": "Daily DLP finding summary"
    }
  }
}

Related

• Monitoring
• Configuration
• Compliance Frameworks
• Admin Guide