CREATE TABLE IF NOT EXISTS `def_host` (
`HostName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`Description` VARCHAR(500) DEFAULT NULL,
`HostType` VARCHAR(50) NOT NULL DEFAULT 'PHYSICAL',
`Environment` VARCHAR(20) NOT NULL DEFAULT 'dev',
`Ip` VARCHAR(45) DEFAULT NULL,
`InternalIp` VARCHAR(45) DEFAULT NULL,
`ExternalIp` VARCHAR(45) DEFAULT NULL,
`Dns` VARCHAR(500) DEFAULT NULL,
`Os` VARCHAR(100) DEFAULT NULL,
`Role` VARCHAR(255) DEFAULT NULL,
`Databases` VARCHAR(500) DEFAULT NULL,
`Services` VARCHAR(500) DEFAULT NULL,
`VmHost` VARCHAR(255) DEFAULT NULL,
`VmId` VARCHAR(100) DEFAULT NULL,
`InstanceType` VARCHAR(50) DEFAULT NULL,
`Region` VARCHAR(50) DEFAULT NULL,
`KeyName` VARCHAR(100) DEFAULT NULL,
`Owner` VARCHAR(255) DEFAULT NULL,
`NotifyEmail` VARCHAR(500) DEFAULT NULL,
`SecurityChecks` VARCHAR(255) DEFAULT 'ports,ssl,http_headers,ssh,dns',
`MonitorEnabled` CHAR(1) NOT NULL DEFAULT 'Y',
`BackupEnabled` CHAR(1) NOT NULL DEFAULT 'N',
`SecurityEnabled` CHAR(1) NOT NULL DEFAULT 'Y',
`PentestEnabled` CHAR(1) NOT NULL DEFAULT 'N',
`ReportEnabled` CHAR(1) NOT NULL DEFAULT 'Y',
`NotifyMqttTopic` VARCHAR(255) DEFAULT NULL,
`DnsRecord` VARCHAR(500) DEFAULT NULL,
`Lat` DECIMAL(10,7) DEFAULT NULL,
`Lng` DECIMAL(10,7) DEFAULT NULL,
`Port` VARCHAR(128) DEFAULT NULL,
`MacAddress` VARCHAR(20) DEFAULT NULL,
`CpuCores` INT DEFAULT NULL,
`MemoryMb` INT DEFAULT NULL,
`DiskGb` INT DEFAULT NULL,
`DiskUsed` DECIMAL(5,2) DEFAULT NULL,
`State` VARCHAR(20) DEFAULT NULL,
`DockerVersion` VARCHAR(50) DEFAULT NULL,
`Monitoring` VARCHAR(255) DEFAULT NULL,
`BackupPolicy` VARCHAR(128) DEFAULT NULL,
`AutoStart` CHAR(1) DEFAULT 'Y',
`Networks` TEXT DEFAULT NULL,
`MonthlyCost` DECIMAL(10,2) DEFAULT NULL,
`SshUser` VARCHAR(64) DEFAULT NULL,
`SshPort` INT DEFAULT 22,
`Tags` TEXT DEFAULT NULL,
`UpdateSchedule` VARCHAR(128) DEFAULT NULL,
`WireguardIp` VARCHAR(50) DEFAULT NULL,
`Notes` TEXT DEFAULT NULL,
`Active` CHAR(1) NOT NULL DEFAULT 'Y',
`LastSeen` DATETIME DEFAULT NULL,
`CreatedDate` DATE DEFAULT NULL,
`RoleDescription` TEXT DEFAULT NULL,
`PemFile` TEXT DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`HostName`, `Package`),
KEY `idx_host_package` (`Package`, `Active`),
KEY `idx_host_type` (`HostType`),
KEY `idx_state` (`State`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `def_mac_address` (
`MacAddress` VARCHAR(20) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`Ip` VARCHAR(45) DEFAULT NULL,
`HostName` VARCHAR(255) DEFAULT NULL,
`Vendor` VARCHAR(255) DEFAULT NULL,
`DeviceType` VARCHAR(50) DEFAULT NULL,
`DeviceName` VARCHAR(255) DEFAULT NULL,
`Description` VARCHAR(500) DEFAULT NULL,
`Interface` VARCHAR(50) DEFAULT NULL,
`Vlan` INT DEFAULT NULL,
`DhcpHostname` VARCHAR(255) DEFAULT NULL,
`Location` VARCHAR(255) DEFAULT NULL,
`Owner` VARCHAR(255) DEFAULT NULL,
`Tags` TEXT DEFAULT NULL,
`Notes` TEXT DEFAULT NULL,
`Active` CHAR(1) NOT NULL DEFAULT 'Y',
`FirstSeen` DATETIME DEFAULT NULL,
`LastSeen` DATETIME DEFAULT NULL,
PRIMARY KEY (`MacAddress`, `Package`),
KEY `idx_mac_ip` (`Ip`),
KEY `idx_mac_host` (`HostName`),
KEY `idx_mac_vendor` (`Vendor`),
KEY `idx_mac_type` (`DeviceType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `def_host_schema` (
`SchemaName` VARCHAR(255) NOT NULL,
`HostName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`DbEngine` VARCHAR(20) NOT NULL DEFAULT 'mariadb',
`Environment` VARCHAR(20) NOT NULL DEFAULT 'prod',
`Description` VARCHAR(500) DEFAULT NULL,
`SizeMb` DECIMAL(10,2) DEFAULT NULL,
`TableCount` INT DEFAULT NULL,
`BackupEnabled` CHAR(1) DEFAULT 'Y',
`BackupSchedule` VARCHAR(128) DEFAULT NULL,
`Owner` VARCHAR(255) DEFAULT NULL,
`Notes` TEXT DEFAULT NULL,
`Active` CHAR(1) NOT NULL DEFAULT 'Y',
`LastSynced` DATETIME DEFAULT NULL,
PRIMARY KEY (`SchemaName`, `HostName`),
KEY `idx_schema_package` (`Package`),
KEY `idx_schema_host` (`HostName`),
KEY `idx_schema_engine` (`DbEngine`),
KEY `idx_schema_env` (`Environment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `def_schema_credentials` (
`SchemaName` VARCHAR(255) NOT NULL,
`HostName` VARCHAR(255) NOT NULL,
`Username` VARCHAR(128) NOT NULL,
`Password` VARCHAR(500) DEFAULT NULL,
`Permissions` VARCHAR(255) DEFAULT 'ALL',
`Description` VARCHAR(255) DEFAULT NULL,
`Active` CHAR(1) NOT NULL DEFAULT 'Y',
`LastVerified` DATETIME DEFAULT NULL,
`Notes` TEXT DEFAULT NULL,
PRIMARY KEY (`SchemaName`, `HostName`, `Username`),
KEY `idx_scred_host` (`HostName`),
KEY `idx_scred_user` (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `def_host_credentials` (
`HostName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`Protocol` VARCHAR(20) NOT NULL DEFAULT 'ssh',
`Username` VARCHAR(128) DEFAULT NULL,
`Password` VARCHAR(500) DEFAULT NULL,
`Port` INT DEFAULT NULL,
`KeyFile` TEXT DEFAULT NULL,
`ApiUrl` VARCHAR(500) DEFAULT NULL,
`ApiToken` VARCHAR(500) DEFAULT NULL,
`Description` VARCHAR(255) DEFAULT NULL,
`Active` CHAR(1) NOT NULL DEFAULT 'Y',
`LastVerified` DATETIME DEFAULT NULL,
`Notes` TEXT DEFAULT NULL,
PRIMARY KEY (`HostName`, `Package`, `Protocol`),
KEY `idx_cred_package` (`Package`, `Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `def_network_link` (
`Guid` VARCHAR(64) NOT NULL PRIMARY KEY,
`SourceHost` VARCHAR(255) NOT NULL,
`SourcePort` VARCHAR(50) DEFAULT NULL,
`TargetHost` VARCHAR(255) NOT NULL,
`TargetPort` VARCHAR(50) DEFAULT NULL,
`LinkType` VARCHAR(50) DEFAULT 'ethernet',
`Speed` VARCHAR(20) DEFAULT NULL,
`Vlan` INT DEFAULT NULL,
`Description` TEXT DEFAULT NULL,
`Active` CHAR(1) DEFAULT 'Y',
`Module` VARCHAR(255) DEFAULT NULL,
KEY `idx_source` (`SourceHost`),
KEY `idx_target` (`TargetHost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
INSERT INTO def_host (
HostName, Package, Description,
HostType, Environment, Ip,
InternalIp, ExternalIp, Dns,
Os, Role, `Databases`, `Services`,
VmHost, VmId, InstanceType,
`Region`, KeyName, Module
) VALUES (
'{hostname}', '{package}',
'{description}', '{host_type}',
'{environment}', '{ip}',
'{internal_ip}', '{external_ip}',
'{dns}', '{os}', '{role}',
'{databases}', '{services}',
'{vm_host}', '{vm_id}',
'{instance_type}', '{region}',
'{key_name}', '{module}'
)
ON DUPLICATE KEY UPDATE
Description = '{description}',
HostType = '{host_type}',
Ip = '{ip}',
InternalIp = '{internal_ip}',
ExternalIp = '{external_ip}',
Dns = '{dns}',
Os = '{os}',
VmHost = '{vm_host}',
VmId = '{vm_id}',
InstanceType = '{instance_type}',
Module = '{module}'
SELECT HostName, Package, Description,
HostType, Environment, Ip,
ExternalIp, Role, Active,
MonitorEnabled, SecurityEnabled,
BackupEnabled, LastSeen,
PentestEnabled, DnsRecord
FROM def_host
WHERE Package = '{package}'
ORDER BY HostType, HostName
SELECT HostName, Ip, ExternalIp,
HostType, Environment, Role,
SecurityChecks, NotifyEmail,
Databases, Services
FROM def_host
WHERE Package = '{package}'
AND Active = 'Y'
ORDER BY HostName
SELECT *
FROM def_host
WHERE HostName = '{hostname}'
AND Package = '{package}'
UPDATE def_host
SET Active = 'N'
WHERE HostName = '{hostname}'
AND Package = '{package}'
UPDATE def_host
SET LastSeen = NOW()
WHERE HostName = '{hostname}'
AND Package = '{package}'
SELECT HostName, Ip, ExternalIp,
SecurityChecks, NotifyEmail,
Environment, Description
FROM def_host
WHERE Package = '{package}'
AND Active = 'Y'
AND SecurityEnabled = 'Y'
SELECT HostName, Ip, ExternalIp,
HostType, Environment
FROM def_host
WHERE Package = '{package}'
AND Active = 'Y'
AND MonitorEnabled = 'Y'
SELECT HostName, Ip, Databases,
HostType, Environment
FROM def_host
WHERE Package = '{package}'
AND Active = 'Y'
AND BackupEnabled = 'Y'
AND Databases IS NOT NULL
AND Databases != ''
UPDATE def_host h
INNER JOIN `{monitor_db}`.internet_host ih
ON h.HostName = ih.Host
AND h.Package = ih.Client
SET
h.Ip = COALESCE(ih.Ip, h.Ip),
h.Os = COALESCE(ih.Os, h.Os),
h.VmHost = COALESCE(ih.VmHost, h.VmHost),
h.LastSeen = ih.LastScan
WHERE h.Package = '{package}'
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN Active = 'Y'
THEN 1 ELSE 0 END) AS Active,
SUM(CASE WHEN HostType = 'EC2'
THEN 1 ELSE 0 END) AS EC2,
SUM(CASE WHEN HostType = 'PROXMOX'
THEN 1 ELSE 0 END) AS Proxmox,
SUM(CASE WHEN HostType = 'DOCKER'
THEN 1 ELSE 0 END) AS Docker,
SUM(CASE WHEN HostType = 'PHYSICAL'
THEN 1 ELSE 0 END) AS Physical,
SUM(CASE WHEN HostType = 'CLOUD'
THEN 1 ELSE 0 END) AS Cloud
FROM def_host
WHERE Package = '{package}'
SELECT NotifyEmail, NotifyMqttTopic,
Owner, ReportEnabled
FROM def_host
WHERE Active = 'Y'
AND (HostName = '{hostname}'
OR Ip = '{ip}')
LIMIT 1
SELECT DISTINCT NotifyEmail
FROM def_host
WHERE Active = 'Y'
AND ReportEnabled = 'Y'
AND NotifyEmail IS NOT NULL
AND NotifyEmail != ''
AND (HostName = '{hostname}'
OR Ip = '{ip}')
SELECT HostName, HostType, Ip,
Lat, Lng, Role, State, Services
FROM def_host
WHERE Active = 'Y'
AND Lat IS NOT NULL
AND Lng IS NOT NULL
ORDER BY HostName
UPDATE def_host
SET State = '{state}',
LastSeen = NOW()
WHERE HostName = '{hostname}'
UPDATE def_host
SET DiskUsed = {disk_used},
LastSeen = NOW()
WHERE HostName = '{hostname}'
SELECT HostType,
COUNT(*) AS cnt,
SUM(CASE WHEN State = 'running'
THEN 1 ELSE 0 END) AS running,
SUM(CpuCores) AS total_cpu,
SUM(MemoryMb) AS total_mem,
SUM(DiskGb) AS total_disk
FROM def_host
WHERE Active = 'Y'
GROUP BY HostType
ORDER BY cnt DESC
SELECT HostName, HostType, Ip, Role,
Services, Tags, State
FROM def_host
WHERE Active = 'Y'
AND Tags LIKE '%{tag}%'
ORDER BY HostName
INSERT INTO def_host
(HostName, Package, HostType, Ip,
Hostname, Os, CpuCores, MemoryMb,
DiskGb, DiskUsed, State,
DockerVersion, Active, LastSeen)
VALUES
('{hostname}', '{package}', 'LXC',
'{ip}', '{fqdn}', '{os}',
{cpu_cores}, {memory_mb}, {disk_gb},
{disk_used}, 'running',
'{docker_version}', 'Y', NOW())
ON DUPLICATE KEY UPDATE
Ip = '{ip}',
Hostname = '{fqdn}',
Os = '{os}',
CpuCores = {cpu_cores},
MemoryMb = {memory_mb},
DiskGb = {disk_gb},
DiskUsed = {disk_used},
State = 'running',
DockerVersion = '{docker_version}',
LastSeen = NOW()
UPDATE def_host
SET WireguardIp = '{wireguard_ip}',
LastSeen = NOW()
WHERE HostName = '{hostname}'
INSERT INTO def_schema_credentials
(SchemaName, HostName, Username, Password,
Permissions, Description, Active,
LastVerified, Notes)
VALUES
('{schema_name}', '{hostname}', '{username}',
'{password}', '{permissions}',
'{description}', 'Y', NOW(), '{notes}')
ON DUPLICATE KEY UPDATE
Password = '{password}',
Permissions = '{permissions}',
Description = '{description}',
LastVerified = NOW()
SELECT Username, Password, Permissions,
Description, Notes
FROM def_schema_credentials
WHERE SchemaName = '{schema_name}'
AND HostName = '{hostname}'
AND Active = 'Y'
SELECT SchemaName, Username, Permissions,
Description, LastVerified
FROM def_schema_credentials
WHERE HostName = '{hostname}'
AND Active = 'Y'
ORDER BY SchemaName, Username
INSERT INTO def_host_schema
(SchemaName, HostName, Package, DbEngine,
Environment, Description, SizeMb,
TableCount, BackupEnabled, Owner,
Notes, Active, LastSynced)
VALUES
('{schema_name}', '{hostname}', '{package}',
'{db_engine}', '{environment}',
'{description}', {size_mb},
{table_count}, '{backup_enabled}',
'{owner}', '{notes}', 'Y', NOW())
ON DUPLICATE KEY UPDATE
Package = '{package}',
Environment = '{environment}',
Description = '{description}',
SizeMb = {size_mb},
TableCount = {table_count},
Owner = '{owner}',
LastSynced = NOW()
SELECT SchemaName, Package, DbEngine,
Environment, Description,
SizeMb, TableCount, BackupEnabled,
Owner, LastSynced
FROM def_host_schema
WHERE HostName = '{hostname}'
AND Active = 'Y'
ORDER BY Package, SchemaName
SELECT s.SchemaName, s.HostName,
s.DbEngine, s.Environment,
s.Description, s.SizeMb,
h.Ip
FROM def_host_schema s
LEFT JOIN def_host h
ON s.HostName = h.HostName
WHERE s.Package = '{package}'
AND s.Active = 'Y'
ORDER BY s.HostName, s.SchemaName
SELECT Package,
COUNT(*) AS schema_count,
SUM(SizeMb) AS total_size_mb,
SUM(TableCount) AS total_tables
FROM def_host_schema
WHERE Active = 'Y'
GROUP BY Package
ORDER BY total_size_mb DESC
INSERT INTO def_host_credentials
(HostName, Package, Protocol, Username,
Password, Port, KeyFile, ApiUrl,
ApiToken, Description, Active,
LastVerified, Notes)
VALUES
('{hostname}', '{package}', '{protocol}',
'{username}', '{password}', {port},
'{keyfile}', '{api_url}', '{api_token}',
'{description}', 'Y', NOW(), '{notes}')
ON DUPLICATE KEY UPDATE
Username = '{username}',
Password = '{password}',
Port = {port},
KeyFile = '{keyfile}',
ApiUrl = '{api_url}',
ApiToken = '{api_token}',
Description = '{description}',
LastVerified = NOW(),
Notes = '{notes}'
SELECT Protocol, Username, Password,
Port, KeyFile, ApiUrl, ApiToken,
Description, Notes
FROM def_host_credentials
WHERE HostName = '{hostname}'
AND Package = '{package}'
AND Active = 'Y'
SELECT Username, Password, Port,
KeyFile, ApiUrl, ApiToken
FROM def_host_credentials
WHERE HostName = '{hostname}'
AND Package = '{package}'
AND Protocol = '{protocol}'
AND Active = 'Y'
SELECT c.HostName, c.Protocol, c.Username,
c.Port, c.Description, c.LastVerified,
h.Ip, h.HostType
FROM def_host_credentials c
LEFT JOIN def_host h
ON c.HostName = h.HostName
AND c.Package = h.Package
WHERE c.Package = '{package}'
AND c.Active = 'Y'
ORDER BY h.Ip, c.Protocol
INSERT INTO def_mac_address
(MacAddress, Package, Ip, HostName,
Vendor, DeviceType, DeviceName,
Description, Interface, DhcpHostname,
Location, Owner, Tags, Notes,
Active, FirstSeen, LastSeen)
VALUES
('{mac}', '{package}', '{ip}',
'{hostname}', '{vendor}', '{device_type}',
'{device_name}', '{description}',
'{interface}', '{dhcp_hostname}',
'{location}', '{owner}', '{tags}',
'{notes}', 'Y', NOW(), NOW())
ON DUPLICATE KEY UPDATE
Ip = '{ip}',
HostName = COALESCE(NULLIF('{hostname}', ''), HostName),
Vendor = COALESCE(NULLIF('{vendor}', ''), Vendor),
DeviceType = COALESCE(NULLIF('{device_type}', ''), DeviceType),
DeviceName = COALESCE(NULLIF('{device_name}', ''), DeviceName),
DhcpHostname = COALESCE(NULLIF('{dhcp_hostname}', ''), DhcpHostname),
LastSeen = NOW()
SELECT *
FROM def_mac_address
WHERE MacAddress = '{mac}'
AND Package = '{package}'
SELECT *
FROM def_mac_address
WHERE Ip = '{ip}'
AND Package = '{package}'
SELECT MacAddress, Ip, HostName,
Vendor, DeviceType, DeviceName,
Location, LastSeen
FROM def_mac_address
WHERE Package = '{package}'
AND Active = 'Y'
ORDER BY Ip
SELECT MacAddress, Ip, DhcpHostname,
Vendor, FirstSeen, LastSeen
FROM def_mac_address
WHERE Package = '{package}'
AND Active = 'Y'
AND (DeviceType IS NULL
OR DeviceType = ''
OR DeviceType = 'unknown')
ORDER BY LastSeen DESC
SELECT MacAddress, Ip, HostName,
DeviceType, DeviceName, Location
FROM def_mac_address
WHERE Package = '{package}'
AND Active = 'Y'
AND Vendor LIKE '%{vendor}%'
ORDER BY Ip
INSERT INTO def_host
(HostName, Package, HostType, VmHost,
VmId, Ip, CpuCores, MemoryMb,
DiskGb, DiskUsed, State,
AutoStart, Active, LastSeen)
VALUES
('{hostname}', '{package}', 'LXC',
'{vm_host}', '{vm_id}', '{ip}',
{cpu_cores}, {memory_mb}, {disk_gb},
{disk_used}, '{state}',
'Y', 'Y', NOW())
ON DUPLICATE KEY UPDATE
VmId = '{vm_id}',
Ip = '{ip}',
CpuCores = {cpu_cores},
MemoryMb = {memory_mb},
DiskGb = {disk_gb},
DiskUsed = {disk_used},
State = '{state}',
LastSeen = NOW()