{'MacAddress': 'VARCHAR(17) NOT NULL', 'Ip': 'VARCHAR(45)', 'Hostname': 'VARCHAR(255)', 'Interface': 'VARCHAR(50)', 'InterfaceDesc': 'VARCHAR(100)', 'Vendor': 'VARCHAR(255)', 'OsHint': 'VARCHAR(255)', 'Services': 'VARCHAR(500)', 'OpenPorts': 'VARCHAR(500)', 'DhcpType': 'VARCHAR(20)', 'DhcpExpires': 'VARCHAR(100)', 'DhcpStatus': 'VARCHAR(50)', 'DhcpComment': 'VARCHAR(255)', 'DhcpServer': 'VARCHAR(100)', 'ArpState': 'VARCHAR(20)', 'PingMs': 'DECIMAL(10,2)', 'Banners': 'TEXT', 'LastScan': 'DATETIME', 'FirstSeen': 'DATETIME', 'SecurityStatus': 'VARCHAR(50)', 'Active': "CHAR(1) DEFAULT 'Y'", 'Module': 'VARCHAR(255)', 'primary_key': 'MacAddress'}
{'Ip': 'VARCHAR(45) NOT NULL', 'MacAddress': 'VARCHAR(17)', 'Hostname': 'VARCHAR(255)', 'Interface': 'VARCHAR(50)', 'InterfaceDesc': 'VARCHAR(100)', 'Vendor': 'VARCHAR(255)', 'OsHint': 'VARCHAR(255)', 'Services': 'VARCHAR(500)', 'OpenPorts': 'VARCHAR(500)', 'DhcpType': 'VARCHAR(20)', 'DhcpExpires': 'VARCHAR(100)', 'DhcpStatus': 'VARCHAR(50)', 'DhcpComment': 'VARCHAR(255)', 'DhcpServer': 'VARCHAR(100)', 'ArpState': 'VARCHAR(20)', 'PingMs': 'DECIMAL(10,2)', 'Banners': 'TEXT', 'LastScan': 'DATETIME', 'FirstSeen': 'DATETIME', 'SecurityStatus': 'VARCHAR(50)', 'Active': "CHAR(1) DEFAULT 'Y'", 'Module': 'VARCHAR(255)', 'primary_key': 'Ip'}
{'Id': 'INT AUTO_INCREMENT', 'MacAddress': 'VARCHAR(17)', 'Ip': 'VARCHAR(45)', 'ChangeDate': 'DATETIME', 'FieldName': 'VARCHAR(50)', 'OldValue': 'TEXT', 'NewValue': 'TEXT', 'Module': 'VARCHAR(255)', 'primary_key': 'Id'}
{'Host': 'VARCHAR(255) NOT NULL', 'ServiceName': 'VARCHAR(255) NOT NULL', 'ServiceType': 'VARCHAR(50)', 'Status': 'VARCHAR(50)', 'Pid': 'VARCHAR(20)', 'Uptime': 'VARCHAR(100)', 'CpuPct': 'DECIMAL(5,1)', 'MemMb': 'DECIMAL(10,1)', 'Image': 'VARCHAR(255)', 'Ports': 'VARCHAR(500)', 'LastSeen': 'DATETIME', 'Module': 'VARCHAR(255)', 'primary_key': 'Host, ServiceName'}
{'Id': 'BIGINT AUTO_INCREMENT', 'Host': 'VARCHAR(255)', 'ServiceName': 'VARCHAR(255)', 'ChangeDate': 'DATETIME', 'OldStatus': 'VARCHAR(50)', 'NewStatus': 'VARCHAR(50)', 'Module': 'VARCHAR(255)', 'primary_key': 'Id'}
{'Host': 'VARCHAR(255) NOT NULL', 'User': 'VARCHAR(100) NOT NULL', 'Terminal': 'VARCHAR(50) NOT NULL', 'LoginTime': 'VARCHAR(50)', 'RemoteIp': 'VARCHAR(45)', 'LastSeen': 'DATETIME', 'Module': 'VARCHAR(255)', 'primary_key': 'Host, User, Terminal'}
{'Id': 'BIGINT AUTO_INCREMENT', 'Host': 'VARCHAR(255)', 'ChangeDate': 'VARCHAR(50)', 'Action': 'VARCHAR(20)', 'Package': 'VARCHAR(255)', 'OldVersion': 'VARCHAR(100)', 'NewVersion': 'VARCHAR(100)', 'Module': 'VARCHAR(255)', 'primary_key': 'Id'}
{'Host': 'VARCHAR(255) NOT NULL', 'SensorName': 'VARCHAR(100) NOT NULL', 'SensorType': 'VARCHAR(20)', 'CurrentValue': 'DECIMAL(10,2)', 'HighValue': 'DECIMAL(10,2)', 'CriticalValue': 'DECIMAL(10,2)', 'LastSeen': 'DATETIME', 'Module': 'VARCHAR(255)', 'primary_key': 'Host, SensorName'}
{'Host': 'VARCHAR(255) NOT NULL', 'Interface': 'VARCHAR(50) NOT NULL', 'BytesSent': 'BIGINT', 'BytesRecv': 'BIGINT', 'PacketsSent': 'BIGINT', 'PacketsRecv': 'BIGINT', 'ErrorsIn': 'BIGINT', 'ErrorsOut': 'BIGINT', 'DropsIn': 'BIGINT', 'DropsOut': 'BIGINT', 'LastSeen': 'DATETIME', 'Module': 'VARCHAR(255)', 'primary_key': 'Host, Interface'}
{'FindingId': 'BIGINT 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', 'ScanTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)', 'primary_key': 'FindingId'}
INSERT INTO `{monitor_db}`.internet_lan (
MacAddress, Ip, Hostname, Interface,
InterfaceDesc, Vendor, OsHint,
Services, OpenPorts, DhcpType, DhcpExpires,
DhcpStatus, DhcpComment, DhcpServer,
ArpState, PingMs, Banners,
LastScan, FirstSeen, Active, Module
) VALUES (
'{mac}', '{ip}', '{hostname}', '{interface}',
'{interface_desc}', '{vendor}',
'{os_hint}', '{services}', '{open_ports}',
'{dhcp_type}', '{dhcp_expires}',
'{dhcp_status}', '{dhcp_comment}',
'{dhcp_server}', '{arp_state}', {ping_ms},
'{banners}', NOW(), NOW(), 'Y',
'ObjInternet.discover'
)
ON DUPLICATE KEY UPDATE
Ip = '{ip}',
Hostname = IF('{hostname}' != '',
'{hostname}', Hostname),
Interface = IF('{interface}' != '',
'{interface}', Interface),
InterfaceDesc = IF('{interface_desc}' != '',
'{interface_desc}', InterfaceDesc),
Vendor = IF('{vendor}' != '',
'{vendor}', Vendor),
OsHint = IF('{os_hint}' != '',
'{os_hint}', OsHint),
Services = IF('{services}' != '',
'{services}', Services),
OpenPorts = IF('{open_ports}' != '',
'{open_ports}', OpenPorts),
DhcpType = IF('{dhcp_type}' != '',
'{dhcp_type}', DhcpType),
DhcpExpires = '{dhcp_expires}',
DhcpStatus = '{dhcp_status}',
DhcpComment = IF('{dhcp_comment}' != '',
'{dhcp_comment}', DhcpComment),
DhcpServer = IF('{dhcp_server}' != '',
'{dhcp_server}', DhcpServer),
ArpState = '{arp_state}',
PingMs = {ping_ms},
Banners = IF('{banners}' != '',
'{banners}', Banners),
LastScan = NOW(),
Active = 'Y',
Module = 'ObjInternet.discover'
INSERT INTO `{monitor_db}`.internet_lan_ip (
Ip, MacAddress, Hostname, Interface,
Vendor, OsHint,
Services, OpenPorts, DhcpType, DhcpExpires,
DhcpStatus, DhcpComment, DhcpServer,
ArpState, PingMs, Banners,
LastScan, FirstSeen, Active, Module
) VALUES (
'{ip}', '{mac}', '{hostname}', '{interface}',
'{vendor}',
'{os_hint}', '{services}', '{open_ports}',
'{dhcp_type}', '{dhcp_expires}',
'{dhcp_status}', '{dhcp_comment}',
'{dhcp_server}', '{arp_state}', {ping_ms},
'{banners}', NOW(), NOW(), 'Y',
'ObjInternet.discover'
)
ON DUPLICATE KEY UPDATE
MacAddress = IF('{mac}' != '',
'{mac}', MacAddress),
Hostname = IF('{hostname}' != '',
'{hostname}', Hostname),
Interface = IF('{interface}' != '',
'{interface}', Interface),
InterfaceDesc = IF('{interface_desc}' != '',
'{interface_desc}', InterfaceDesc),
Vendor = IF('{vendor}' != '',
'{vendor}', Vendor),
OsHint = IF('{os_hint}' != '',
'{os_hint}', OsHint),
Services = IF('{services}' != '',
'{services}', Services),
OpenPorts = IF('{open_ports}' != '',
'{open_ports}', OpenPorts),
DhcpType = IF('{dhcp_type}' != '',
'{dhcp_type}', DhcpType),
DhcpExpires = '{dhcp_expires}',
DhcpStatus = '{dhcp_status}',
DhcpComment = IF('{dhcp_comment}' != '',
'{dhcp_comment}', DhcpComment),
DhcpServer = IF('{dhcp_server}' != '',
'{dhcp_server}', DhcpServer),
ArpState = '{arp_state}',
PingMs = {ping_ms},
Banners = IF('{banners}' != '',
'{banners}', Banners),
LastScan = NOW(),
Active = 'Y',
Module = 'ObjInternet.discover'
SELECT * FROM `{monitor_db}`.internet_lan_ip
WHERE Ip = '{ip}'
SELECT * FROM `{monitor_db}`.internet_lan_ip
WHERE Active = 'Y'
ORDER BY INET_ATON(Ip)
SELECT MacAddress, Ip, Hostname, Vendor,
OsHint, Services, OpenPorts, DhcpType,
DhcpStatus, DhcpComment, DhcpServer,
ArpState, Active
FROM `{monitor_db}`.internet_lan
WHERE MacAddress = '{mac}'
SELECT Ip, MacAddress, Hostname, Vendor,
OsHint, Services, OpenPorts, DhcpType,
DhcpStatus, DhcpComment, DhcpServer,
ArpState, Active
FROM `{monitor_db}`.internet_lan_ip
WHERE Ip = '{ip}'
INSERT INTO `{monitor_db}`.internet_lan_history (
MacAddress, Ip, ChangeDate,
FieldName, OldValue, NewValue, Module
) VALUES (
'{mac}', '{ip}', NOW(),
'{field}', '{old_value}', '{new_value}',
'ObjInternet.discover'
)
SELECT ChangeDate, MacAddress, Ip,
FieldName, OldValue, NewValue
FROM `{monitor_db}`.internet_lan_history
WHERE (MacAddress = '{mac}' OR Ip = '{ip}')
ORDER BY ChangeDate DESC
LIMIT {limit}
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}
INSERT INTO `{monitor_db}`.internet_service (
Host, ServiceName, ServiceType, Status,
Pid, Uptime, CpuPct, MemMb, Image, Ports,
LastSeen, Module
) VALUES (
'{host}', '{name}', '{service_type}',
'{status}', '{pid}', '{uptime}',
{cpu_pct}, {mem_mb}, '{image}', '{ports}',
NOW(), '{module}'
)
ON DUPLICATE KEY UPDATE
Status = '{status}',
Pid = '{pid}',
Uptime = '{uptime}',
CpuPct = {cpu_pct},
MemMb = {mem_mb},
Image = IF('{image}' != '', '{image}', Image),
Ports = IF('{ports}' != '', '{ports}', Ports),
LastSeen = NOW()
SELECT ServiceName, Status
FROM `{monitor_db}`.internet_service
WHERE Host = '{host}'
INSERT INTO `{monitor_db}`.internet_service_history (
Host, ServiceName, ChangeDate,
OldStatus, NewStatus, Module
) VALUES (
'{host}', '{name}', NOW(),
'{old_status}', '{new_status}',
'{module}'
)
INSERT INTO `{monitor_db}`.internet_session (
Host, User, Terminal, LoginTime,
RemoteIp, LastSeen, Module
) VALUES (
'{host}', '{user}', '{terminal}',
'{login_time}', '{remote_ip}',
NOW(), 'ObjMonitor'
)
ON DUPLICATE KEY UPDATE
LoginTime = '{login_time}',
RemoteIp = '{remote_ip}',
LastSeen = NOW()
DELETE FROM `{monitor_db}`.internet_session
WHERE Host = '{host}'
AND LastSeen < DATE_SUB(NOW(),
INTERVAL 10 MINUTE)
INSERT INTO `{monitor_db}`.internet_package_change (
Host, ChangeDate, Action, Package,
OldVersion, NewVersion, Module
) VALUES (
'{host}', '{change_date}', '{action}',
'{package}', '{old_version}',
'{new_version}', 'ObjMonitor'
)
SELECT ChangeDate
FROM `{monitor_db}`.internet_package_change
WHERE Host = '{host}'
ORDER BY Id DESC
LIMIT 1
INSERT INTO `{monitor_db}`.internet_sensor (
Host, SensorName, SensorType,
CurrentValue, HighValue, CriticalValue,
LastSeen, Module
) VALUES (
'{host}', '{sensor}', '{sensor_type}',
{current}, {high}, {critical},
NOW(), 'ObjMonitor'
)
ON DUPLICATE KEY UPDATE
CurrentValue = {current},
HighValue = {high},
CriticalValue = {critical},
LastSeen = NOW()
INSERT INTO `{monitor_db}`.internet_throughput (
Host, Interface, BytesSent, BytesRecv,
PacketsSent, PacketsRecv,
ErrorsIn, ErrorsOut, DropsIn, DropsOut,
LastSeen, Module
) VALUES (
'{host}', '{interface}',
{bytes_sent}, {bytes_recv},
{packets_sent}, {packets_recv},
{errin}, {errout}, {dropin}, {dropout},
NOW(), 'ObjMonitor'
)
ON DUPLICATE KEY UPDATE
BytesSent = {bytes_sent},
BytesRecv = {bytes_recv},
PacketsSent = {packets_sent},
PacketsRecv = {packets_recv},
ErrorsIn = {errin},
ErrorsOut = {errout},
DropsIn = {dropin},
DropsOut = {dropout},
LastSeen = NOW()
UPDATE `{monitor_db}`.{table}
SET SecurityStatus = '{status}'
WHERE Hostname = '{hostname}'
UPDATE def_host
SET LastSeen = NOW()
WHERE HostName = '{hostname}'
OR Ip IN (
SELECT Ip
FROM `{monitor_db}`.internet_lan_ip
WHERE Hostname = '{hostname}'
)
SELECT WebhookCode, BaseUrl,
Description, Httpmethod, Timeout
FROM def_webhook
WHERE Direction = 'IN'
AND BaseUrl IS NOT NULL
AND BaseUrl != ''
AND BaseUrl NOT LIKE '$%'
AND Package IN (
'{package}', '{archetype}',
'ADHOC', 'CORE'
)
SELECT DISTINCT NotifyEmail
FROM def_host
WHERE Active = 'Y'
AND NotifyEmail IS NOT NULL
AND NotifyEmail != ''
AND (
HostName = '{hostname}'
OR Ip = '{ip}'
)
SELECT * FROM `{monitor_db}`.internet_lan
WHERE MacAddress = '{mac}'
SELECT * FROM `{monitor_db}`.internet_lan
WHERE Active = 'Y'
ORDER BY Ip
UPDATE `{monitor_db}`.internet_lan
SET Active = 'N'
WHERE LastScan < DATE_SUB(NOW(),
INTERVAL {days} DAY)
AND Active = 'Y'
INSERT IGNORE INTO `{monitor_db}`.internet_HostPort (
Host, Port, PortType
) VALUES (
'{host}', '{port}', '{type}'
)
UPDATE `{monitor_db}`.internet_HostPort
SET
Active = 1,
RemoteHost = '{host_ip}',
PortBanner = '{banner}',
Description = '{port_name}',
LastScan = NOW(),
LastOpen = NOW(),
PortStatus = 'OPEN'
WHERE Host LIKE '{host}'
AND Port LIKE '{port}'
AND PortType LIKE '{type}'
UPDATE `{monitor_db}`.internet_HostPort
SET
Active = 1,
RemoteHost = '{host_ip}',
LastScan = NOW(),
PortStatus = 'CLOSED',
Description = '{port_name}'
WHERE Host LIKE '{host}'
AND Port LIKE '{port}'
AND PortType LIKE '{type}'
DELETE FROM `{monitor_db}`.internet_HostPortLog
WHERE PortStatus LIKE 'OPEN'
SELECT DISTINCT PortStatus
FROM `{monitor_db}`.internet_HostPort
WHERE Host LIKE '{host}'