DSQL - DNS Science Query Language

DSQL Language Reference

Core Data Types

Domain Object Properties

Basic Properties

• domain.name - Domain name string
• domain.tld - Top-level domain
• domain.sld - Second-level domain
• domain.created_at - First discovered date
• domain.last_checked - Last scan timestamp

Email Security

• spf.status - "valid", "invalid", "missing"
• spf.valid - Validation status (boolean)
• dmarc.policy - "none", "quarantine", "reject"
• dmarc.pct - Percentage (0-100)
• dkim.enabled - DKIM enabled (boolean)

SSL/TLS

• ssl.valid - Valid certificate (boolean)
• ssl.issuer - Certificate issuer
• ssl.expires_in - Days until expiry
• ssl.not_after - Expiry date
• ssl.fingerprint - SHA256 fingerprint

DNSSEC

• dnssec.enabled - DNSSEC enabled (boolean)
• dnssec.valid - DNSSEC valid (boolean)
• dnssec.status - "valid", "invalid", "missing"

Statements

FIND Statement

FIND domains WHERE condition [LIMIT n] [OFFSET n]

SHOW Statement

SHOW subdomains OF example.com WHERE condition

CHECK Statement

CHECK example.com FOR conditions

COUNT Statement

COUNT domains WHERE condition [GROUP BY attribute]

LIST Statement

LIST name, ssl.expires_in FROM domains WHERE condition

Operators

Built-in Functions

Aggregation Functions

• count(items) - Count items
• sum(values) - Sum of values
• avg(values) - Average of values
• min(values) - Minimum value
• max(values) - Maximum value

String Functions

• upper(str) - Convert to uppercase
• lower(str) - Convert to lowercase
• trim(str) - Remove whitespace
• concat(str1, str2) - Concatenate strings

Date/Time Functions

• now() - Current date/time
• today() - Current date at midnight
• days_between(date1, date2) - Days between dates

Real-World DSQL Examples

Email Security Audits

Find Domains Without SPF

FIND domains WHERE spf.record IS NULL
ORDER BY last_checked DESC
LIMIT 100

Comprehensive Email Security Audit

LIST
  name,
  spf.status,
  dmarc.policy,
  dkim.enabled,
  mx.provider
FROM domains
WHERE
  spf.status != "valid" OR
  dmarc.policy NOT IN ["quarantine", "reject"] OR
  dkim.enabled = false
ORDER BY name

SSL Certificate Management

Certificates Expiring This Month

LIST
  name,
  ssl.issuer,
  ssl.expires_in,
  ssl.not_after
FROM domains
WHERE
  ssl.expires_in <= 30.days AND
  ssl.expires_in > 0
ORDER BY ssl.expires_in ASC

Self-Signed Certificates

FIND domains WHERE
  ssl.issuer = "self-signed" OR
  ssl.subject_cn = ssl.issuer_cn

Security Monitoring

Monitor Production Domains

MONITOR portfolio WHERE tags CONTAINS "production" EVERY 1.hour {
  -- Critical: SSL expiring soon
  ALERT ON ssl.expires_in < 7.days {
    severity: "critical"
    notify: ["ops@example.com", "pagerduty://oncall"]
    message: "CRITICAL: SSL for ${domain.name} expires in ${ssl.expires_in} days"
  }

  -- High: DNSSEC issues
  ALERT ON dnssec.valid = false {
    severity: "high"
    notify: ["dns-team@example.com"]
    message: "DNSSEC validation failed for ${domain.name}"
  }

  -- Warning: MX record changes
  ALERT ON mx.changed {
    severity: "warning"
    notify: ["security@example.com"]
    message: "MX records changed for ${domain.name}"
  }
}

Automated Remediation

Auto-Renew Certificate Reminders

FOR EACH domain IN FIND domains WHERE
  ssl.expires_in < 30.days AND
  ssl.expires_in > 0 {

  -- Email domain owner
  SEND email TO domain.owner WITH {
    template: "cert_renewal_reminder",
    domain: domain.name,
    expires_in: domain.ssl.expires_in,
    expires_on: domain.ssl.not_after
  }

  -- Create ticket
  CREATE ticket IN "SSL Renewals" WITH {
    title: "Renew SSL for ${domain.name}",
    priority: domain.ssl.expires_in < 7.days ? "critical" : "high",
    assignee: domain.owner,
    labels: ["ssl", "security", "renewal"]
  }

  -- Tag domain
  UPDATE domain SET tags = ADD("ssl-expiring")
}

Compliance Reporting

SOC 2 Security Controls Report

REPORT soc2_security_controls {
  TITLE "SOC 2 Security Controls - Q${quarter} ${year}"

  SECTION "SSL/TLS Encryption" {
    METRIC "Domains with Valid SSL" {
      value: COUNT domains WHERE ssl.valid = true
    }

    TABLE {
      title: "SSL Issues"
      data: LIST name, ssl.issuer, ssl.expires_in
            FROM domains
            WHERE ssl.valid = false OR ssl.expires_in < 30.days
    }
  }

  SECTION "Email Security" {
    METRIC "DMARC Reject Policy" {
      value: COUNT domains WHERE dmarc.policy = "reject"
    }

    TABLE {
      title: "Email Security Gaps"
      data: LIST name, spf.status, dmarc.policy
            FROM domains
            WHERE spf.status != "valid" OR dmarc.policy != "reject"
    }
  }

  EXPORT TO pdf, email("compliance@example.com")
}

Threat Detection

High-Risk Domain Report

LIST
  name,
  risk_score,
  threat_indicators,
  last_checked
FROM domains
WHERE risk_score > 75
ORDER BY risk_score DESC
LIMIT 100

Domains on Threat Intelligence Feeds

FIND domains WHERE
  threat_intel.blocklisted = true OR
  threat_intel.malware_detected = true OR
  threat_intel.phishing_detected = true

DSQL Quick Reference Card

Core Statements

FIND domains [WHERE condition] [LIMIT n] [OFFSET n]
SHOW attribute OF domain [WHERE condition]
CHECK domain FOR conditions
COUNT domains [WHERE condition] [GROUP BY attrs]
LIST attrs FROM domains [WHERE condition] [ORDER BY attr] [LIMIT n]

Monitoring

MONITOR target EVERY interval {
  ALERT ON condition {
    severity: "critical|high|warning|info"
    notify: ["email@example.com", "slack://channel"]
    message: "Alert message with ${variables}"
  }
}

Automation

FOR EACH domain IN source WHERE condition {
  SEND type TO recipient WITH {params}
  CREATE type IN project WITH {params}
  UPDATE domain SET attr = value
  EXPORT domains TO format FILE "path"
}

Policies

POLICY name {
  REQUIRE condition
  ON VIOLATION {
    severity: "critical"
    remediation: "Description"
  }
}

APPLY POLICY name TO target

Common Attributes

Operators

=, !=, >, <, >=, <=           -- Comparison
AND, OR, NOT                   -- Logical
IN, CONTAINS, HAS             -- Collections
MATCHES, LIKE                 -- Pattern matching
STARTS WITH, ENDS WITH        -- String matching
IS NULL, IS NOT NULL          -- Null checks
EXISTS, NOT EXISTS            -- Existence

Duration Literals

30.days, 1.hour, 5.minutes, 2.weeks, 1.year

CLI Commands

# Execute query
dsql execute 'FIND domains WHERE spf.status = "missing"'

# From file
dsql execute --file query.dsql

# Format output
dsql execute --format json 'COUNT domains'

# Interactive mode
dsql repl

API Endpoints

POST   /api/dsl/execute        # Execute query
POST   /api/dsl/validate       # Validate query
GET    /api/dsl/queries        # List saved queries
POST   /api/dsl/queries        # Save query
GET    /api/dsl/monitors       # List monitors
GET    /api/dsl/history        # Query history

Quick Tips

1. Always use LIMIT for large result sets
2. Query indexed columns (spf.valid, ssl.expires_in)
3. Test queries before automation
4. Use comments for complex queries
5. Start monitors with low severity
6. Use variables for repeated queries