CREATE TABLE IF NOT EXISTS data_security_audit (
Guid VARCHAR(64) NOT NULL,
AuditDate DATETIME NOT NULL,
Host VARCHAR(255) NOT NULL,
Category VARCHAR(50) NOT NULL,
Title VARCHAR(255) NOT NULL,
Severity VARCHAR(20) NOT NULL,
Detail TEXT,
Remediation TEXT,
Package VARCHAR(50),
PRIMARY KEY (Guid),
KEY idx_audit_host (Host, AuditDate),
KEY idx_audit_severity (Severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
¶ data_subdomain_cache
CREATE TABLE IF NOT EXISTS data_subdomain_cache (
Domain VARCHAR(255) NOT NULL,
Subdomain VARCHAR(255) NOT NULL,
Ip VARCHAR(45) DEFAULT NULL,
LastScan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (Domain, Subdomain),
KEY idx_sub_domain (Domain, LastScan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS data_security_audit_summary (
Guid VARCHAR(64) NOT NULL,
AuditDate DATETIME NOT NULL,
Host VARCHAR(255) NOT NULL,
Critical INT DEFAULT 0,
High INT DEFAULT 0,
Medium INT DEFAULT 0,
Low INT DEFAULT 0,
Info INT DEFAULT 0,
TotalFindings INT DEFAULT 0,
TotalPassed INT DEFAULT 0,
DurationSeconds INT DEFAULT 0,
AiSummary TEXT DEFAULT NULL,
Package VARCHAR(50),
PRIMARY KEY (Guid),
KEY idx_summary_host (Host, AuditDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS def_security_audit_hosts (
Guid VARCHAR(64) NOT NULL,
Host VARCHAR(255) NOT NULL,
Description VARCHAR(255),
Package VARCHAR(50) NOT NULL,
Environment VARCHAR(50) DEFAULT 'production',
Checks VARCHAR(255) DEFAULT 'ports,ssl,http_headers,ssh,dns,mysql',
Enabled TINYINT(1) DEFAULT 1,
Owner VARCHAR(255),
NotifyEmail TEXT,
NotifySeverity VARCHAR(20) DEFAULT 'HIGH',
Notes TEXT,
PRIMARY KEY (Host, Package),
KEY idx_audit_hosts_guid (Guid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT * FROM def_security_audit_hosts
WHERE Enabled = 1
ORDER BY Package, Host
SELECT * FROM def_security_audit_hosts
WHERE Host = '{host}'
AND Package = '{package}'
INSERT INTO def_security_audit_hosts
(Guid, Host, Description, Package, Environment, Checks, Enabled, Owner, NotifyEmail, NotifySeverity, Notes)
VALUES
('{guid}', '{host}', '{description}', '{package}', '{environment}',
'{checks}', {enabled}, '{owner}', '{notify_email}', '{notify_severity}', '{notes}')
ON DUPLICATE KEY UPDATE
Description = VALUES(Description),
Environment = VALUES(Environment),
Checks = VALUES(Checks),
Enabled = VALUES(Enabled),
Owner = VALUES(Owner),
NotifyEmail = VALUES(NotifyEmail),
NotifySeverity = VALUES(NotifySeverity),
Notes = VALUES(Notes)
INSERT INTO data_security_audit
(Guid, AuditDate, Host, Category,
Title, Severity, Detail,
Remediation, Package)
VALUES
('{guid}', NOW(),
'{host}', '{category}',
'{title}', '{severity}',
'{detail}', '{remediation}',
'{package}')
INSERT INTO data_security_audit_summary
(Guid, AuditDate, Host, Critical,
High, Medium, Low, Info,
TotalFindings, TotalPassed,
DurationSeconds, AiSummary, Package)
VALUES
('{guid}', NOW(),
'{host}', {critical}, {high},
{medium}, {low}, {info},
{total_findings}, {total_passed},
{duration_seconds},
{ai_summary}, '{package}')
¶ upsert_subdomain_cache
INSERT INTO data_subdomain_cache
(Domain, Subdomain, Ip, LastScan)
VALUES
('{domain}', '{subdomain}',
'{ip}', NOW())
ON DUPLICATE KEY UPDATE
Ip = '{ip}',
LastScan = NOW()
¶ read_subdomain_cache
SELECT Subdomain, Ip
FROM data_subdomain_cache
WHERE Domain = '{domain}'
AND LastScan >= '{min_date}'
ORDER BY Subdomain
¶ count_subdomain_cache
SELECT COUNT(*)
FROM data_subdomain_cache
WHERE Domain = '{domain}'
AND LastScan >= '{min_date}'