CREATE TABLE `internet_host` (
`Host` varchar(255) NOT NULL,
`Client` varchar(244) NOT NULL,
`LastScan` timestamp NULL DEFAULT NULL,
`HostRep` varchar(50) DEFAULT '22',
`DbUptime` int(11) DEFAULT 0,
`Distribution` varchar(255) DEFAULT NULL,
`IspUpload` int(11) DEFAULT 0,
`IpGateway` varchar(255) DEFAULT NULL,
`HttpsDomain` varchar(100) DEFAULT NULL,
`DNSNameServer` varchar(100) DEFAULT NULL,
`HttpsIssuer` varchar(100) DEFAULT NULL,
`VmHost` varchar(255) DEFAULT NULL,
`Gitbranch` varchar(50) DEFAULT NULL,
`LastDisplay` datetime DEFAULT NULL,
`HttpsSerial` varchar(100) DEFAULT NULL,
`error:` varchar(255) DEFAULT NULL,
`IspLastCheck` datetime DEFAULT NULL,
`Daylag` int(11) DEFAULT 0,
`HttpsPem` text DEFAULT NULL,
`Active` int(11) DEFAULT 1,
`HostDB` varchar(50) DEFAULT '22',
`CpuPercent` int(11) DEFAULT 0,
`Version` varchar(255) DEFAULT NULL,
`HttpsNotValidAfter` datetime DEFAULT NULL,
`HostName` varchar(255) DEFAULT NULL,
`Latitude` varchar(255) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`DNSMxSecondary` varchar(100) DEFAULT NULL,
`HostAPI` varchar(50) DEFAULT '22',
`DrivePercent` int(11) DEFAULT 0,
`AlertTime` datetime DEFAULT NULL,
`Gitpulldate` varchar(255) DEFAULT NULL,
`DNSMx` varchar(100) DEFAULT NULL,
`Kernel` varchar(255) DEFAULT NULL,
`DNSNameServerIP` varchar(100) DEFAULT NULL,
`HttpsNotValidBefore` datetime DEFAULT NULL,
`Os` varchar(255) DEFAULT NULL,
`CollectSleep` int(11) DEFAULT 10,
`LastLoginDate` datetime DEFAULT NULL,
`Ip` varchar(255) DEFAULT NULL,
`GatewayIp` varchar(255) DEFAULT NULL,
`Filesystem` varchar(255) DEFAULT NULL,
`MemoryPercent` int(11) DEFAULT 0,
`Alert` int(11) DEFAULT 0,
`IspRating` int(11) DEFAULT 0,
`Boottime` int(11) DEFAULT 0,
`Longitude` varchar(255) DEFAULT NULL,
`IspPing` int(11) DEFAULT 0,
`Isp` varchar(255) DEFAULT NULL,
`PollingSpeed` int(11) DEFAULT 1,
`PrimaryApp` varchar(255) DEFAULT NULL,
`IspDownload` int(11) DEFAULT 0,
`VmName` varchar(255) DEFAULT NULL,
`HostWeb` varchar(50) DEFAULT '22',
`CpuCores` int(11) DEFAULT 0,
`Url` varchar(255) DEFAULT NULL,
`Role` varchar(255) DEFAULT NULL,
`CpuTemp` int(11) DEFAULT 22,
`error:_date` datetime DEFAULT NULL,
`HasWebmin` varchar(1) DEFAULT NULL,
`Uptime` int(11) DEFAULT 0,
`DriveFree` int(11) DEFAULT 0,
`DbVersion` varchar(255) DEFAULT NULL,
`Gitpulltime` varchar(255) DEFAULT NULL,
`MemoryTotal` int(11) DEFAULT 0,
`VmHostIp` varchar(255) DEFAULT NULL,
`ServeReport9400` varchar(255) DEFAULT NULL,
`ServeReport9400_date` datetime DEFAULT NULL,
`ServeReport9401` varchar(255) DEFAULT NULL,
`ServeReport9401_date` datetime DEFAULT NULL,
`ServeWebHook9500` varchar(255) DEFAULT NULL,
`ServeWebHook9500_date` datetime DEFAULT NULL,
`ServeWebHook9501` varchar(255) DEFAULT NULL,
`ServeWebHook9501_date` datetime DEFAULT NULL,
`ServeWebHook9502` varchar(255) DEFAULT NULL,
`ServeWebHook9502_date` datetime DEFAULT NULL,
`ServeWebHook9503` varchar(255) DEFAULT NULL,
`ServeWebHook9503_date` datetime DEFAULT NULL,
`ServeWebHook9504` varchar(255) DEFAULT NULL,
`ServeWebHook9504_date` datetime DEFAULT NULL,
`ServeWebHook9505` varchar(255) DEFAULT NULL,
`ServeWebHook9505_date` datetime DEFAULT NULL,
`ServeWebHook9506` varchar(255) DEFAULT NULL,
`ServeWebHook9506_date` datetime DEFAULT NULL,
`ServeWebHook9507` varchar(255) DEFAULT NULL,
`ServeWebHook9507_date` datetime DEFAULT NULL,
`ServeWebHook9508` varchar(255) DEFAULT NULL,
`ServeWebHook9508_date` datetime DEFAULT NULL,
`ServeWebHook9509` varchar(255) DEFAULT NULL,
`ServeWebHook9509_date` datetime DEFAULT NULL,
`ServeWebHook9510` varchar(255) DEFAULT NULL,
`ServeWebHook9510_date` datetime DEFAULT NULL,
`ServeWebHook9511` varchar(255) DEFAULT NULL,
`ServeWebHook9511_date` datetime DEFAULT NULL,
`ServeWebHook9512` varchar(255) DEFAULT NULL,
`ServeWebHook9512_date` datetime DEFAULT NULL,
`ServeWebHook9513` varchar(255) DEFAULT NULL,
`ServeWebHook9513_date` datetime DEFAULT NULL,
`ServeWebHook9514` varchar(255) DEFAULT NULL,
`ServeWebHook9514_date` datetime DEFAULT NULL,
`ServeWebHook9515` varchar(255) DEFAULT NULL,
`ServeWebHook9515_date` datetime DEFAULT NULL,
`ServeWebHook9516` varchar(255) DEFAULT NULL,
`ServeWebHook9516_date` datetime DEFAULT NULL,
`ServeWebHook9517` varchar(255) DEFAULT NULL,
`ServeWebHook9517_date` datetime DEFAULT NULL,
`ServeWebHook9518` varchar(255) DEFAULT NULL,
`ServeWebHook9518_date` datetime DEFAULT NULL,
`ServeWebHook9519` varchar(255) DEFAULT NULL,
`ServeWebHook9519_date` datetime DEFAULT NULL,
`Dns` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
`GLobalIpGateway` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Host`,`Client`),
KEY `INDEX` (`LastScan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} ROW_FORMAT=DYNAMIC
CREATE TABLE `internet_host_proxmox` (
`Host` varchar(255) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
`Active` int(11) DEFAULT NULL,
`VmType` varchar(255) DEFAULT NULL,
`VmId` int(11) DEFAULT NULL,
`CpuCores` int(11) DEFAULT NULL,
`CpuPercent` int(11) DEFAULT NULL,
`MemoryUsed` bigint(20) DEFAULT NULL,
`MemoryTotal` bigint(20) DEFAULT NULL,
`DiskUsed` bigint(20) DEFAULT NULL,
`DiskTotal` bigint(20) DEFAULT NULL,
`Uptime` bigint(20) DEFAULT NULL,
`NetIn` bigint(20) DEFAULT NULL,
`NetOut` bigint(20) DEFAULT NULL,
`SwapUsed` bigint(20) DEFAULT NULL,
`SwapTotal` bigint(20) DEFAULT NULL,
`LastScan` datetime DEFAULT NULL,
`IpAddress` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `internet_host_proxmox_log` (
`Host` varchar(255) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
`Active` int(11) DEFAULT NULL,
`VmType` varchar(255) DEFAULT NULL,
`VmId` int(11) DEFAULT NULL,
`CpuCores` int(11) DEFAULT NULL,
`CpuPercent` int(11) DEFAULT NULL,
`MemoryUsed` bigint(20) DEFAULT NULL,
`MemoryTotal` bigint(20) DEFAULT NULL,
`DiskUsed` bigint(20) DEFAULT NULL,
`DiskTotal` bigint(20) DEFAULT NULL,
`Uptime` bigint(20) DEFAULT NULL,
`NetIn` bigint(20) DEFAULT NULL,
`NetOut` bigint(20) DEFAULT NULL,
`SwapUsed` bigint(20) DEFAULT NULL,
`SwapTotal` bigint(20) DEFAULT NULL,
`LastScan` datetime DEFAULT NULL,
`log_time` datetime NOT NULL,
PRIMARY KEY (`Host`,`log_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `internet_hostalert` (`Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `AlertTime` datetime, `Alert` varchar(255) COLLATE {collation}, `SnoozeTime` datetime, `DisplayTime` datetime, `AlertCloseTime` datetime, `AlertValue` int(11), `AlertCheckTime` datetime, `Active` int(11))
CREATE TABLE `internet_hostalertlog` (`Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `AlertTime` datetime, `Alert` varchar(255) COLLATE {collation}, `AlertCheckTime` datetime, `AlertValue` int(11))
CREATE TABLE `internet_hostapp` (`App` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `Host` varchar(255) COLLATE {collation}, `Uptime` int(11), `HttpsSerial` varchar(100) COLLATE {collation}, `CpuCores` int(11), `Daylag` int(11), `Pid` int(11), `MemoryTotal` int(11), `DbVersion` varchar(255) COLLATE {collation}, `LastDisplay` datetime, `DNSMxSecondary` varchar(100) COLLATE {collation}, `Boottime` int(11), `Memory` int(11), `GitBranch` varchar(255) COLLATE {collation}, `CPU` int(11), `Name` varchar(255) COLLATE {collation}, `DNSMx` varchar(100) COLLATE {collation}, `HttpsDomain` varchar(100) COLLATE {collation}, `DNSNameServerIP` varchar(100) COLLATE {collation}, `Alert` int(11), `VmhostIP` varchar(255) COLLATE {collation}, `Active` int(11), `CpuPercent` int(11), `HttpsIssuer` varchar(100) COLLATE {collation}, `IpGateway` varchar(255) COLLATE {collation}, `MemoryPercent` int(11), `AlertTime` datetime, `Distribution` varchar(255) COLLATE {collation}, `DbUptime` int(11), `Os` varchar(255) COLLATE {collation}, `PrimaryApp` varchar(255) COLLATE {collation}, `Kernel` varchar(255) COLLATE {collation}, `DNSNameServer` varchar(100) COLLATE {collation}, `Ip` varchar(255) COLLATE {collation}, `Vmhost` varchar(255) COLLATE {collation}, `HttpsNotValidAfter` datetime, `Version` varchar(255) COLLATE {collation}, `HttpsNotValidBefore` datetime, `LastLoginDate` datetime, `CpuTemp` int(11))
CREATE TABLE `internet_hostappusagelog` (`App` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `Host` varchar(255) COLLATE {collation}, `Readcount` int(11), `Writebytes` int(11), `Readcountdiff` int(11), `Instances` int(11), `Readbytesdiff` int(11), `Pid` varchar(15) COLLATE {collation}, `Module` varchar(15) COLLATE {collation}, `Cmdline` varchar(255) COLLATE {collation}, `Readbytes` int(11), `User` varchar(15) COLLATE {collation}, `Memory` int(11), `Writecount` int(11), `Writecountdiff` int(11), `Cpu` int(11), `Writebytesdiff` int(11))
CREATE TABLE `internet_hostdevice` (`Device` varchar(255) COLLATE {collation}, `Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `DriveFree` float, `Mount` varchar(255) COLLATE {collation}, `LastScan` datetime, `Filesystem` varchar(50) COLLATE {collation}, `Touch` int(11), `Mountpoint` varchar(255) COLLATE {collation}, `Mounted` int(11), `Drivepercent` int(11))
CREATE TABLE `internet_hostdrive` (`Device` varchar(255) COLLATE {collation}, `Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `DriveFree` float, `Mount` varchar(255) COLLATE {collation}, `LastScan` datetime, `Filesystem` varchar(50) COLLATE {collation}, `Touch` int(11), `Mounted` int(11), `Drivepercent` int(11))
CREATE TABLE `internet_hostdrivelog` (`Device` varchar(255) COLLATE {collation}, `Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `ReadCount` int(11), `WriteBytes` int(11), `Mount` varchar(255) COLLATE {collation}, `WriteTime` int(11), `ReadBytes` int(11), `WriteCount` int(11), `DriveFree` float, `ReadTime` int(11), `Drivepercent` int(11))
CREATE TABLE `internet_hostdriveusagelog` (`Device` varchar(255) COLLATE {collation}, `Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `ReadCount` int(11), `WriteBytes` int(11), `ReadCountDiff` int(11), `ReadBytesDiff` int(11), `LastScanEpoch` int(20), `WriteTime` int(11), `ReadBytes` int(11), `WriteCount` int(11), `WriteCountDiff` int(11), `WriteBytesDiff` int(11), `ReadTime` int(11))
CREATE TABLE `internet_hostfile` (`Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `File` varchar(255) COLLATE {collation}, `LastScan` datetime, `FileSize` int(11), `FileDate` datetime, PRIMARY KEY (`Host`, `Client`, `File`))
CREATE TABLE `internet_hostfilelog` (`Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `File` varchar(255) COLLATE {collation}, `LastScanEpoch` int(20), `FileSize` int(11), `FileDate` datetime)
CREATE TABLE `internet_hostisplog` (`Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `Download` bigint(11), `Isp` varchar(255) COLLATE {collation}, `Ping` float, `Upload` bigint(11), `UserActive` int(11), `UserRecent` int(11), `UserTotal` int(11))
CREATE TABLE `internet_hostlog` (`Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `LastScan` datetime, `CpuPercentAvg` int(11), `CpuPercent` int(11), `MemoryPercentAvg` int(11), `MemoryFree` float, `MemoryTotal` float, `LastScanEpoch` int(20), `MemoryPercent` int(11), `error:` varchar(255) COLLATE {collation}, `error:_date` datetime)
CREATE TABLE `internet_hostnames` (`Url` varchar(255) COLLATE {collation}, `Host` varchar(100) COLLATE {collation}, `Client` varchar(100) COLLATE {collation}, `Hostname` varchar(255) COLLATE {collation}, `Appname` varchar(255) COLLATE {collation}, PRIMARY KEY (`Host`))
CREATE TABLE `internet_hostport` (`PortType` varchar(5) COLLATE {collation}, `Host` varchar(255) COLLATE {collation}, `Client` varchar(244) COLLATE {collation}, `Port` varchar(10) COLLATE {collation}, `PortIp` varchar(255) COLLATE {collation}, `PortStatus` varchar(10) COLLATE {collation}, `RemoteHost` varchar(255) COLLATE {collation}, `LastScan` datetime, `LastOpen` datetime, `PortBanner` text COLLATE {collation}, `Active` int(11), `Description` varchar(255) COLLATE {collation})
CREATE TABLE `internet_hostportlog` (`PortType` varchar(5) COLLATE {collation}, `Host` varchar(255) COLLATE {collation}, `Client` varchar(255) COLLATE {collation}, `LastScan` datetime, `Port` varchar(10) COLLATE {collation}, `PortIp` varchar(255) COLLATE {collation}, `PortBanner` text COLLATE {collation}, `PortStatus` varchar(10) COLLATE {collation})
CREATE TABLE `internet_hostreachable` (`Host` varchar(100) COLLATE {collation}, `Client` varchar(100) COLLATE {collation}, `ToIP` varchar(50) COLLATE {collation}, `Reachable` varchar(1) COLLATE {collation}, `Critical` varchar(1) COLLATE {collation}, `Ping` float, `LastScan` datetime)
CREATE TABLE `internet_hostreachablelog` (`Host` varchar(100) COLLATE {collation}, `LastScan` datetime, `ToIP` varchar(50) COLLATE {collation}, `Client` varchar(100) COLLATE {collation}, `Reachable` varchar(100) COLLATE {collation}, `Ping` float)
CREATE TABLE `internet_relationship` (`Client` varchar(244) COLLATE {collation}, `HostIp` varchar(255) COLLATE {collation}, `Type` varchar(255) COLLATE {collation}, `RemoteIp` varchar(255) COLLATE {collation})
CREATE TABLE `internet_login_event` (
`EventId` BIGINT NOT NULL AUTO_INCREMENT,
`Host` VARCHAR(255) NOT NULL,
`Client` VARCHAR(244) NOT NULL,
`Username` VARCHAR(255) NOT NULL,
`LoginType` VARCHAR(50) NOT NULL DEFAULT 'web',
`Success` TINYINT(1) NOT NULL DEFAULT 1,
`IpAddress` VARCHAR(45) DEFAULT NULL,
`UserAgent` VARCHAR(512) DEFAULT NULL,
`SessionId` VARCHAR(255) DEFAULT NULL,
`FailureReason` VARCHAR(255) DEFAULT NULL,
`Provider` VARCHAR(50) DEFAULT NULL,
`EventTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`EventId`),
KEY `idx_login_user` (`Username`, `EventTime`),
KEY `idx_login_host` (`Host`, `EventTime`),
KEY `idx_login_time` (`EventTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
¶ internet_command_log
CREATE TABLE `internet_command_log` (
`CommandId` BIGINT NOT NULL AUTO_INCREMENT,
`Host` VARCHAR(255) NOT NULL,
`Client` VARCHAR(244) NOT NULL,
`Username` VARCHAR(255) NOT NULL,
`Command` VARCHAR(1024) NOT NULL,
`CommandSource` VARCHAR(50) NOT NULL DEFAULT 'history',
`WorkingDir` VARCHAR(512) DEFAULT NULL,
`ExitCode` INT DEFAULT NULL,
`IpAddress` VARCHAR(45) DEFAULT NULL,
`EventTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`CommandId`),
KEY `idx_cmd_user` (`Username`, `EventTime`),
KEY `idx_cmd_host` (`Host`, `EventTime`),
KEY `idx_cmd_time` (`EventTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `internet_cron_event` (
`EventId` BIGINT NOT NULL AUTO_INCREMENT,
`Host` VARCHAR(255) NOT NULL,
`Client` VARCHAR(244) NOT NULL,
`Username` VARCHAR(255) NOT NULL,
`Command` VARCHAR(1024) NOT NULL,
`Success` TINYINT(1) NOT NULL DEFAULT 1,
`EventTime` DATETIME NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`EventId`),
KEY `idx_cron_host` (`Host`, `EventTime`),
KEY `idx_cron_time` (`EventTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `data_monitor_poll` (
`HostName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`Ip` VARCHAR(45) DEFAULT NULL,
`SysDescr` VARCHAR(500) DEFAULT NULL,
`SysName` VARCHAR(255) DEFAULT NULL,
`SysUptime` VARCHAR(100) DEFAULT NULL,
`CpuUser` DECIMAL(5,1) DEFAULT 0,
`CpuSystem` DECIMAL(5,1) DEFAULT 0,
`CpuIdle` DECIMAL(5,1) DEFAULT 0,
`MemTotal` BIGINT DEFAULT 0,
`MemUsed` BIGINT DEFAULT 0,
`MemFree` BIGINT DEFAULT 0,
`MemCached` BIGINT DEFAULT 0,
`DiskTotal` BIGINT DEFAULT 0,
`DiskUsed` BIGINT DEFAULT 0,
`DiskPercent` DECIMAL(5,1) DEFAULT 0,
`IfCount` INT DEFAULT 0,
`LoadAvg1` DECIMAL(6,2) DEFAULT 0,
`LoadAvg5` DECIMAL(6,2) DEFAULT 0,
`LoadAvg15` DECIMAL(6,2) DEFAULT 0,
`TempC` DECIMAL(5,1) DEFAULT NULL,
`TcpConnections` INT DEFAULT 0,
`ProcessCount` INT DEFAULT 0,
`IfTrafficIn` BIGINT DEFAULT 0,
`IfTrafficOut` BIGINT DEFAULT 0,
`PollStatus` VARCHAR(20) DEFAULT 'ok',
`Source` VARCHAR(20) DEFAULT 'snmp',
`PollTime` DATETIME DEFAULT NULL,
PRIMARY KEY (`HostName`, `Package`),
KEY `idx_poll_status` (`PollStatus`),
KEY `idx_poll_time` (`PollTime`),
KEY `idx_poll_source` (`Source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `internet_pending_update` (
`Host` VARCHAR(255) NOT NULL,
`Client` VARCHAR(244) NOT NULL,
`PackageName` VARCHAR(255) NOT NULL,
`CurrentVersion` VARCHAR(100) DEFAULT NULL,
`AvailableVersion` VARCHAR(100) DEFAULT NULL,
`SecurityUpdate` TINYINT(1) NOT NULL DEFAULT 0,
`ScanTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`Host`, `Client`, `PackageName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `internet_disk_latency` (
`SampleId` BIGINT NOT NULL AUTO_INCREMENT,
`Host` VARCHAR(255) NOT NULL,
`Client` VARCHAR(244) NOT NULL,
`Device` VARCHAR(100) NOT NULL,
`LatencyMs` FLOAT DEFAULT NULL,
`ReadAwaitMs` FLOAT DEFAULT NULL,
`WriteAwaitMs` FLOAT DEFAULT NULL,
`IoUtil` FLOAT DEFAULT NULL,
`SampleTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`SampleId`),
KEY `idx_lat_host` (`Host`, `SampleTime`),
KEY `idx_lat_device` (`Device`, `SampleTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `internet_security_finding` (
`FindingId` BIGINT NOT NULL AUTO_INCREMENT,
`Host` VARCHAR(255) NOT NULL,
`Client` VARCHAR(244) NOT NULL,
`Category` VARCHAR(100) NOT NULL,
`Severity` VARCHAR(20) NOT NULL DEFAULT 'INFO',
`Description` VARCHAR(512) NOT NULL,
`Detail` TEXT DEFAULT NULL,
`ScanTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`FindingId`),
KEY `idx_sec_host` (`Host`, `ScanTime`),
KEY `idx_sec_severity` (`Severity`, `ScanTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
ALTER TABLE `{monitor_db}`.{table}
ADD COLUMN SecurityStatus VARCHAR(50)
SELECT *
FROM `{monitor_db}`.Internet_Host
WHERE Host = '{host}'
INSERT IGNORE INTO `{monitor_db}`.Internet_Host (
Host, LastScan, Os, Name, Kernel, Client
) VALUES (
'{host}', NOW(), '{os}', '{name}', '{kernel}', '{package}'
)
INSERT IGNORE INTO `{monitor_db}`.Internet_Hostnames (
Client, Host, Hostname
) VALUES (
'{client}', '{host}', '{hostname}'
)
UPDATE `{monitor_db}`.Internet_Host
SET Client = '{package}'
WHERE Host = '{host}'
AND Client IS NULL
UPDATE `{monitor_db}`.internet_host
SET VMHOST = 'AWS EC2'
WHERE VMHost = ''
AND Kernel LIKE '%aws'
UPDATE `{monitor_db}`.Internet_Host
SET
Version = '{version}',
Vmhost = '{vm_host}',
Ip = '{ip}',
IpGateway = '{ip_gateway}',
CpuTemp = {cpu_temp},
LastScan = NOW(),
AlertTime = NOW()
WHERE Host = '{host}'
SHOW GLOBAL STATUS
LIKE 'Uptime'
SHOW VARIABLES
LIKE 'version'
UPDATE `{monitor_db}`.Internet_Host
SET
IspDownload = '{isp_download}',
IspUpload = '{isp_upload}',
IspPing = '{isp_ping}',
IspRating = '{isp_rating}',
Isp = '{isp}',
City = '{city}',
IspLastCheck = NOW()
WHERE Host = '{host}'
OR GlobalIpGateway = '{global_ip_gateway}'
SELECT FileSize
FROM `{monitor_db}`.Internet_HostFile
WHERE Host = '{host}'
AND File = '{file_profile}'
INSERT IGNORE INTO `{monitor_db}`.Internet_HostFile (
Host, LastScan, File
) VALUES (
'{host}', NOW(), '{file_profile}'
)
UPDATE `{monitor_db}`.Internet_HostFile
SET
FileSize = '{file_size}',
LastScan = NOW(),
FileDate = '{date_time}'
WHERE Host = '{host}'
AND File = '{file_profile}'
INSERT IGNORE INTO `{monitor_db}`.Internet_HostFileLog (
Host, LastScan, File, FileSize, FileDate
) VALUES (
'{host}', NOW(), '{file_profile}', '{file_size}', '{date_time}'
)
INSERT INTO `{monitor_db}`.internet_login_event (
Host, Client, Username, LoginType,
Success, IpAddress, UserAgent,
SessionId, FailureReason, Provider,
EventTime, Module
) VALUES (
'{host}', '{client}', '{username}',
'{login_type}', {success},
{ip_address}, {user_agent},
{session_id}, {failure_reason},
{provider}, NOW(), {module}
)
SELECT EventId, Host, Client, Username,
LoginType, Success, IpAddress,
SessionId, Provider, EventTime
FROM `{monitor_db}`.internet_login_event
WHERE Host = '{host}'
ORDER BY EventTime DESC
LIMIT {limit}
SELECT EventId, Host, Client, Username,
LoginType, Success, IpAddress,
SessionId, Provider, EventTime,
FailureReason
FROM `{monitor_db}`.internet_login_event
WHERE Username = '{username}'
AND Client = '{client}'
ORDER BY EventTime DESC
LIMIT {limit}
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN Success = 1 THEN 1
ELSE 0 END) AS Successful,
SUM(CASE WHEN Success = 0 THEN 1
ELSE 0 END) AS Failed,
COUNT(DISTINCT Username) AS UniqueUsers,
COUNT(DISTINCT IpAddress) AS UniqueIps
FROM `{monitor_db}`.internet_login_event
WHERE Host = '{host}'
AND EventTime >= '{window_start}'
SELECT s.Username, s.SessionStart,
le.IpAddress, le.LoginType
FROM sys_user_session s
LEFT JOIN `{monitor_db}`.internet_login_event le
ON le.SessionId = s.Session
WHERE s.SessionEnd IS NULL
AND s.Package = '{package}'
ORDER BY s.SessionStart DESC
DELETE FROM `{monitor_db}`.internet_login_event
WHERE EventTime < '{cutoff_date}'
¶ insert_command_log
INSERT INTO `{monitor_db}`.internet_command_log (
Host, Client, Username, Command,
CommandSource, WorkingDir, ExitCode,
IpAddress, EventTime, Module
) VALUES (
'{host}', '{client}', '{username}',
'{command}', '{command_source}',
{working_dir}, {exit_code},
{ip_address}, NOW(), {module}
)
¶ read_command_log
SELECT CommandId, Host, Client, Username,
Command, CommandSource, WorkingDir,
ExitCode, IpAddress, EventTime
FROM `{monitor_db}`.internet_command_log
WHERE Host = '{host}'
ORDER BY EventTime DESC
LIMIT {limit}
¶ read_command_log_by_user
SELECT CommandId, Host, Client, Username,
Command, CommandSource, WorkingDir,
ExitCode, IpAddress, EventTime
FROM `{monitor_db}`.internet_command_log
WHERE Username = '{username}'
AND Host = '{host}'
ORDER BY EventTime DESC
LIMIT {limit}
¶ count_command_log
SELECT
COUNT(*) AS Total,
COUNT(DISTINCT Username) AS UniqueUsers,
COUNT(DISTINCT Command) AS UniqueCommands
FROM `{monitor_db}`.internet_command_log
WHERE Host = '{host}'
AND EventTime >= '{window_start}'
¶ cleanup_command_log
DELETE FROM `{monitor_db}`.internet_command_log
WHERE EventTime < '{cutoff_date}'
INSERT INTO `{monitor_db}`.internet_cron_event (
Host, Client, Username, Command,
Success, EventTime, Module
) VALUES (
'{host}', '{client}', '{username}',
'{command}', {success}, '{event_time}',
{module}
)
SELECT EventId, Host, Username, Command,
Success, EventTime
FROM `{monitor_db}`.internet_cron_event
WHERE Host = '{host}'
ORDER BY EventTime DESC
LIMIT {limit}
DELETE FROM `{monitor_db}`.internet_cron_event
WHERE EventTime < '{cutoff_date}'
DELETE FROM `{monitor_db}`.internet_pending_update
WHERE Host = '{host}'
AND Client = '{client}'
INSERT INTO `{monitor_db}`.internet_pending_update (
Host, Client, PackageName, CurrentVersion,
AvailableVersion, SecurityUpdate, ScanTime,
Module
) VALUES (
'{host}', '{client}', '{package_name}',
'{current_version}', '{available_version}',
{security_update}, NOW(), {module}
) ON DUPLICATE KEY UPDATE
CurrentVersion = VALUES(CurrentVersion),
AvailableVersion = VALUES(AvailableVersion),
SecurityUpdate = VALUES(SecurityUpdate),
ScanTime = NOW()
SELECT PackageName, CurrentVersion,
AvailableVersion, SecurityUpdate,
ScanTime
FROM `{monitor_db}`.internet_pending_update
WHERE Host = '{host}'
ORDER BY SecurityUpdate DESC, PackageName
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN SecurityUpdate = 1 THEN 1
ELSE 0 END) AS Security
FROM `{monitor_db}`.internet_pending_update
WHERE Host = '{host}'
INSERT INTO `{monitor_db}`.internet_disk_latency (
Host, Client, Device, LatencyMs,
ReadAwaitMs, WriteAwaitMs, IoUtil,
SampleTime, Module
) VALUES (
'{host}', '{client}', '{device}',
{latency_ms}, {read_await_ms},
{write_await_ms}, {io_util},
NOW(), {module}
)
SELECT Device, LatencyMs, ReadAwaitMs,
WriteAwaitMs, IoUtil, SampleTime
FROM `{monitor_db}`.internet_disk_latency
WHERE Host = '{host}'
ORDER BY SampleTime DESC
LIMIT {limit}
DELETE FROM `{monitor_db}`.internet_disk_latency
WHERE SampleTime < '{cutoff_date}'
INSERT INTO `{monitor_db}`.internet_security_finding (
Host, Client, Category, Severity,
Description, Detail, ScanTime, Module
) VALUES (
'{host}', '{client}', '{category}',
'{severity}', '{description}',
{detail}, NOW(), {module}
)
DELETE FROM `{monitor_db}`.internet_security_finding
WHERE Host = '{host}'
AND Client = '{client}'
SELECT FindingId, Category, Severity,
Description, Detail, ScanTime
FROM `{monitor_db}`.internet_security_finding
WHERE Host = '{host}'
ORDER BY
FIELD(Severity, 'CRITICAL', 'HIGH',
'MEDIUM', 'LOW', 'INFO'),
ScanTime DESC
LIMIT {limit}
SELECT
b.BackupName,
COALESCE(b.BackupType, '') AS BackupType,
b.Remoteconnection,
b.DatabaseMask,
b.Filesize,
b.LastCheck,
s.Status AS LastStatus,
s.TimeCompleted AS LastRun,
TIMESTAMPDIFF(
HOUR, s.TimeCompleted, NOW()
) AS AgeHours
FROM def_Backup b
LEFT JOIN stage_backup s
ON s.BackupName = b.BackupName
AND s.Status = 'DONE'
AND s.TimeCompleted = (
SELECT MAX(s2.TimeCompleted)
FROM stage_backup s2
WHERE s2.BackupName = b.BackupName
AND s2.Status = 'DONE'
)
WHERE b.Package = '{package}'
AND COALESCE(b.Active, 'Y') = 'Y'
ORDER BY b.BackupName
SELECT BackupName, TargetDatabase,
TimeCompleted, Result2
FROM stage_backup
WHERE Status = 'ERROR'
AND Package = '{package}'
AND TimeCompleted >= '{since}'
ORDER BY TimeCompleted DESC
LIMIT {limit}
SELECT
Host,
COUNT(*) AS Total,
SUM(CASE WHEN Severity = 'CRITICAL'
THEN 1 ELSE 0 END) AS Critical,
SUM(CASE WHEN Severity = 'HIGH'
THEN 1 ELSE 0 END) AS High,
SUM(CASE WHEN Severity = 'MEDIUM'
THEN 1 ELSE 0 END) AS Medium,
MAX(ScanTime) AS LastScan
FROM data_security_audit
WHERE ScanTime >= '{since}'
GROUP BY Host
ORDER BY Critical DESC, High DESC
SELECT Host, HttpsDomain,
HttpsNotValidAfter,
HttpsIssuer
FROM `{monitor_db}`.internet_host
WHERE HttpsNotValidAfter IS NOT NULL
AND Client = '{client}'
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN ResolvedAt IS NULL
AND AcknowledgedBy IS NULL
AND AlertNote != 'PASS'
THEN 1 ELSE 0 END)
AS Unacknowledged,
SUM(CASE WHEN ResolvedAt IS NULL
AND EscalationLevel > 0
AND AlertNote != 'PASS'
THEN 1 ELSE 0 END)
AS Escalated,
SUM(CASE WHEN ResolvedAt IS NULL
AND AlertNote != 'PASS'
THEN 1 ELSE 0 END)
AS Active
FROM track_alert
WHERE Package IN
('SYSTEM', 'CORE', '{package}')
AND AlertTriggerTime >= '{since}'
SELECT Alert, AlertNote, Severity,
AlertTriggerTime,
AcknowledgedBy, ResolvedAt
FROM track_alert
WHERE Package IN
('SYSTEM', 'CORE', '{package}')
AND AlertNote != 'PASS'
ORDER BY AlertTriggerTime DESC
LIMIT {limit}
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN Severity = 'CRITICAL'
THEN 1 ELSE 0 END) AS Critical,
SUM(CASE WHEN Severity = 'HIGH'
THEN 1 ELSE 0 END) AS High,
SUM(CASE WHEN Status = 'INVESTIGATING'
THEN 1 ELSE 0 END) AS Investigating
FROM def_alert_incident
WHERE Package = '{package}'
AND Status NOT IN ('RESOLVED', 'CLOSED')
CREATE DATABASE IF NOT EXISTS `{monitor_db}`
INSERT INTO `{monitor_db}`.internet_hostlog
({columns}) VALUES ({values})
INSERT INTO `{monitor_db}`.internet_hostisplog
({columns}) VALUES ({values})
¶ show_columns
SHOW COLUMNS FROM {table_name}
SHOW INDEX FROM {table_name}
WHERE Key_name = 'PRIMARY'
ALTER TABLE {table_name}
DROP PRIMARY KEY,
ADD PRIMARY KEY (`{host_col}`, `{ts_col}`)
INSERT INTO data_monitor_poll
(HostName, Package, Ip, SysDescr,
SysName, SysUptime, CpuUser,
CpuSystem, CpuIdle, MemTotal,
MemUsed, MemFree, MemCached,
DiskTotal, DiskUsed, DiskPercent,
IfCount, LoadAvg1, LoadAvg5,
LoadAvg15, TempC, TcpConnections,
ProcessCount, IfTrafficIn,
IfTrafficOut, PollStatus, Source,
PollTime)
VALUES
('{hostname}', '{package}', '{ip}',
'{sys_descr}', '{sys_name}',
'{sys_uptime}', {cpu_user},
{cpu_system}, {cpu_idle},
{mem_total}, {mem_used},
{mem_free}, {mem_cached},
{disk_total}, {disk_used},
{disk_percent}, {if_count},
{load_avg1}, {load_avg5},
{load_avg15}, {temp_c},
{tcp_connections}, {process_count},
{if_traffic_in}, {if_traffic_out},
'{poll_status}', '{source}', NOW())
ON DUPLICATE KEY UPDATE
SysDescr = '{sys_descr}',
SysName = '{sys_name}',
SysUptime = '{sys_uptime}',
CpuUser = {cpu_user},
CpuSystem = {cpu_system},
CpuIdle = {cpu_idle},
MemTotal = {mem_total},
MemUsed = {mem_used},
MemFree = {mem_free},
MemCached = {mem_cached},
DiskTotal = {disk_total},
DiskUsed = {disk_used},
DiskPercent = {disk_percent},
IfCount = {if_count},
LoadAvg1 = {load_avg1},
LoadAvg5 = {load_avg5},
LoadAvg15 = {load_avg15},
TempC = {temp_c},
TcpConnections = {tcp_connections},
ProcessCount = {process_count},
IfTrafficIn = {if_traffic_in},
IfTrafficOut = {if_traffic_out},
PollStatus = '{poll_status}',
Source = '{source}',
PollTime = NOW()
SELECT HostName, Ip, SysName,
SysUptime, CpuUser, CpuSystem,
MemTotal, MemUsed, DiskPercent,
LoadAvg1, PollStatus, Source,
PollTime
FROM data_monitor_poll
WHERE Package = '{package}'
ORDER BY HostName
SELECT *
FROM data_monitor_poll
WHERE HostName = '{hostname}'
AND Package = '{package}'
SELECT Role, RoleDescription,
Owner, NotifyEmail
FROM def_host
WHERE HostName = '{hostname}'
LIMIT 1