CREATE TABLE IF NOT EXISTS `internet_resolve` (
`Clienthost` VARCHAR(45) NOT NULL,
`Name` VARCHAR(255) DEFAULT NULL,
`Isp` VARCHAR(255) DEFAULT NULL,
`Company` VARCHAR(255) DEFAULT NULL,
`Countrycode` VARCHAR(5) DEFAULT NULL,
`CountryName` VARCHAR(100) DEFAULT NULL,
`Timezone` VARCHAR(50) DEFAULT NULL,
`City` VARCHAR(100) DEFAULT NULL,
`RegionName` VARCHAR(100) DEFAULT NULL,
`Region` VARCHAR(50) DEFAULT NULL,
`Latitude` VARCHAR(20) DEFAULT NULL,
`Longitude` VARCHAR(20) DEFAULT NULL,
`Proxy` VARCHAR(5) DEFAULT NULL,
`Mobile` VARCHAR(5) DEFAULT NULL,
`Reverse` VARCHAR(255) DEFAULT NULL,
`Hits` INT DEFAULT 0,
`Lookup` VARCHAR(1) DEFAULT 'N',
`Block` VARCHAR(5) DEFAULT NULL,
`Firewalled` VARCHAR(5) DEFAULT NULL,
`Monitor` VARCHAR(1) DEFAULT NULL,
`CreateDate` DATETIME DEFAULT CURRENT_TIMESTAMP,
`AsNumber` VARCHAR(20) DEFAULT NULL,
`Cidr` VARCHAR(50) DEFAULT NULL,
`Netname` VARCHAR(100) DEFAULT NULL,
`AbuseContact` VARCHAR(255) DEFAULT NULL,
`LatencyMs` FLOAT DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`Clienthost`),
KEY `idx_resolve_lookup` (`Lookup`),
KEY `idx_resolve_country` (`Countrycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
INSERT IGNORE INTO `{monitor_db}`.internet_host
(Host, HostName)
VALUES ('{host}', '{host}')
UPDATE `{monitor_db}`.internet_host
SET VmHost = '{ip_address}',
VmType = '{vm_type}',
Cpucores = '{cpus}',
VmStatus = '{status}',
VmId = '{vm_id}'
WHERE Host = '{host}'
OR HostName = '{host}'
INSERT INTO `{monitor_db}`.internet_host
(Host, LastScan, Isp, IspLastCheck, Ip,
IpGateway, HostDB, HostName, Dns, Os,
City, Distribution)
VALUES
('{dns_label}', NOW(), 'NETBIRD', NOW(),
'{ip}', '{connection_ip}', '',
'{hostname}', '{dns_label}', '{os}',
'{city_name}', '{kernel_version}')
UPDATE `{monitor_db}`.internet_host
SET LastScan = NOW(),
Isp = 'NETBIRD',
IspLastCheck = NOW(),
Ip = '{ip}',
IpGateway = '{connection_ip}',
HostDB = '',
HostName = '{hostname}',
Dns = '{dns_label}',
Os = '{os}',
City = '{city_name}',
Distribution = '{kernel_version}'
WHERE Host = '{dns_label}'
SELECT * FROM {resolve_table}
WHERE Clienthost LIKE '{ip}'
INSERT IGNORE INTO {resolve_table}
(Clienthost)
VALUES ('{ip}')
UPDATE {resolve_table}
SET Name = '{name}',
Isp = '{isp}',
Company = '{company}',
Countrycode = '{country_code}',
CountryName = '{country_name}',
Timezone = '{timezone}',
City = '{city}',
RegionName = '{region_name}',
Region = '{region}',
Latitude = '{latitude}',
Longitude = '{longitude}',
Proxy = '{proxy}',
Mobile = '{mobile}',
Reverse = '{reverse}',
Lookup = 'Y',
AsNumber = '{as_number}',
Cidr = '{cidr}',
Netname = '{netname}',
AbuseContact = '{abuse_contact}',
LatencyMs = {latency_ms}
WHERE Clienthost = '{ip}'
SELECT DISTINCT Clienthost
FROM {resolve_table}
WHERE Lookup = 'N'
SELECT DISTINCT RemoteIp
FROM sys_Weblog
WHERE Lookup = 'N'
UPDATE sys_Weblog
SET Lookup = 'Y'
WHERE RemoteIp = '{ip}'
DELETE FROM sys_Weblog
WHERE User = '' OR Page LIKE '/'
DELETE FROM sys_weblog
WHERE DATEDIFF(NOW(), Start) > 30
SELECT Clienthost
FROM internet_resolve
WHERE Block LIKE 'Y'
AND Firewalled LIKE ''
SELECT Clienthost
FROM internet_resolve
WHERE Block LIKE 'A'
AND Firewalled LIKE ''
DELETE FROM sys_Weblog
WHERE RemoteIp LIKE '{ip}'
SELECT DISTINCT UserAgent
FROM sys_Weblog