Search & Analysis

Querying Audit Logs

Search and analyze billions of audit events with powerful filtering, aggregations, and export capabilities.

Event Schema

Every audit event captured by DB Audit follows a consistent schema. Understanding these fields helps you write effective queries.

Field	Type	Description
`id`	`string`	Unique event identifier
`type`	`string`	Event type (e.g., database.query, security.threat_detected)
`timestamp`	`datetime`	When the event occurred (ISO 8601)
`database_id`	`string`	Database identifier
`database_name`	`string`	Human-readable database name
`user`	`string`	Database user who performed the action
`operation`	`string`	SQL operation (SELECT, INSERT, UPDATE, DELETE, etc.)
`query`	`string`	The SQL query (may be masked for sensitive data)
`query_hash`	`string`	Hash of the normalized query for pattern matching
`source_ip`	`string`	Client IP address
`source_host`	`string`	Client hostname (if resolved)
`duration_ms`	`number`	Query execution time in milliseconds
`rows_affected`	`number`	Number of rows affected by the query
`severity`	`string`	Event severity: info, warning, critical
`classification`	`array`	Data classification tags (PII, financial, etc.)
`policy_matches`	`array`	List of matched audit policy IDs
`geo`	`object`	Geolocation data (country, city, coordinates)
`session_id`	`string`	Database session identifier
`application`	`string`	Client application name (if available)

Query Examples

Basic Time-Based Search

Search for events within a specific time range using the Events API.

# Search for events in the last 24 hours
GET /v1/events?
  start_date=2025-01-14T00:00:00Z&
  end_date=2025-01-15T00:00:00Z

Filter by Database and User

Narrow down results to specific databases and users.

# Filter by database and user
GET /v1/events?
  database_id=db_production&
  user=app_service&
  limit=100

Search SQL Patterns

Find events containing specific SQL patterns.

# Search for specific SQL patterns
GET /v1/events?
  query_contains=DELETE FROM users&
  severity=high

Advanced Queries

Complex Filtering

Use the search endpoint for complex queries with multiple conditions, wildcards, and sorting.

# Complex filter with multiple conditions
POST /v1/events/search
{
  "filters": {
    "databases": ["db_prod_*"],
    "users": ["admin", "dba_*"],
    "operations": ["DELETE", "DROP", "TRUNCATE"],
    "time_range": {
      "start": "2025-01-01T00:00:00Z",
      "end": "2025-01-15T23:59:59Z"
    }
  },
  "sort": {
    "field": "timestamp",
    "order": "desc"
  },
  "pagination": {
    "limit": 50,
    "offset": 0
  }
}

Aggregations

Group and aggregate events for analytics and reporting.

# Aggregate events by user and operation
POST /v1/events/aggregate
{
  "group_by": ["user", "operation"],
  "metrics": ["count", "avg_duration"],
  "filters": {
    "time_range": {
      "start": "2025-01-01T00:00:00Z",
      "end": "2025-01-15T23:59:59Z"
    }
  }
}

# Response
{
  "results": [
    {
      "user": "app_service",
      "operation": "SELECT",
      "count": 1542890,
      "avg_duration_ms": 12.5
    },
    {
      "user": "admin",
      "operation": "UPDATE",
      "count": 3421,
      "avg_duration_ms": 45.2
    }
  ]
}

Dashboard Metrics

Retrieve multiple metrics in a single request for dashboards.

# Query for dashboard metrics
POST /v1/events/metrics
{
  "metrics": [
    {
      "name": "events_by_severity",
      "type": "count",
      "group_by": "severity"
    },
    {
      "name": "events_over_time",
      "type": "timeseries",
      "interval": "1h",
      "aggregation": "count"
    },
    {
      "name": "top_users",
      "type": "top_n",
      "field": "user",
      "limit": 10
    }
  ],
  "time_range": {
    "start": "2025-01-14T00:00:00Z",
    "end": "2025-01-15T00:00:00Z"
  }
}

Filter Operators

DB Audit supports a rich set of filter operators for precise queries.

Operator	Description	Example
`eq`	Equals	`"user": {"eq": "admin"}`
`ne`	Not equals	`"operation": {"ne": "SELECT"}`
`in`	In list	`"severity": {"in": ["critical", "high"]}`
`contains`	String contains	`"query": {"contains": "users"}`
`starts_with`	String starts with	`"user": {"starts_with": "app_"}`
`regex`	Regular expression match	`"query": {"regex": "DROP.*TABLE"}`
`gt`	Greater than	`"rows_affected": {"gt": 1000}`
`gte`	Greater than or equal	`"duration_ms": {"gte": 100}`
`lt`	Less than	`"rows_affected": {"lt": 10}`
`lte`	Less than or equal	`"duration_ms": {"lte": 50}`
`exists`	Field exists/is not null	`"geo.country": {"exists": true}`

Exporting Logs

Export audit logs for external analysis, compliance reporting, or archival. Supports CSV, JSON, and Parquet formats.

CSV Export

Human-readable format for spreadsheets and basic analysis.

JSON Export

Structured format for programmatic processing and SIEM ingestion.

Parquet Export

Columnar format optimized for big data analytics tools.

# Export logs to CSV
POST /v1/events/export
{
  "format": "csv",
  "filters": {
    "time_range": {
      "start": "2025-01-01T00:00:00Z",
      "end": "2025-01-31T23:59:59Z"
    },
    "severity": ["critical", "high"]
  },
  "fields": [
    "timestamp",
    "database_name",
    "user",
    "operation",
    "query",
    "source_ip",
    "severity"
  ],
  "delivery": {
    "method": "email",
    "recipients": ["security@company.com"]
  }
}

Data Retention & Storage Tiers

DB Audit uses tiered storage to balance query performance with cost efficiency. Recent data is instantly searchable, while older data is archived for compliance.

Hot Storage

7 days

Full-text search, instant queries, real-time dashboards

Warm Storage

30 days

Indexed search, sub-second queries

Cold Storage

1 year

Archived storage, minutes to query

Glacier Storage

7 years

Compliance archive, hours to retrieve

Custom Retention

Enterprise plans can customize retention periods to meet specific compliance requirements (e.g., 10-year retention for SOX).