Hi,
I made myself a query in SQL returning me 5 devices having problems communicating over Wi-Fi.
Unfortunately I can’t make an alert out of it.
Does anyone know a more efficient way to do this?
SELECT d.sysName, d.hostname, d.display, p.ifDescr, e.message, COUNT(1)
FROM eventlog e, devices d, ports p
WHERE
e.DATETIME > DATE_ADD(NOW(), INTERVAL -30 DAY)
AND d.device_id = e.device_id
AND p.device_id = d.device_id
AND p.port_id = e.reference
AND p.ifDescr LIKE ('wlan%')
AND e.message LIKE ('ifSpeed%')
AND e.message NOT LIKE ('ifSpeed: 6% Mbps -> 6% Mbps')
AND e.message NOT LIKE ('ifSpeed: 5% Mbps -> 5% Mbps')
AND e.message NOT LIKE ('ifSpeed: 4% Mbps -> 4% Mbps')
AND e.message NOT LIKE ('ifSpeed: 3% Mbps -> 3% Mbps')
AND e.message NOT LIKE ('ifSpeed: 2% Mbps -> 2% Mbps')
AND e.message NOT LIKE ('ifSpeed: 6% Mbps -> 5% Mbps')
AND e.message NOT LIKE ('ifSpeed: 5% Mbps -> 4% Mbps')
AND e.message NOT LIKE ('ifSpeed: 4% Mbps -> 3% Mbps')
AND e.message NOT LIKE ('ifSpeed: 3% Mbps -> 2% Mbps')
AND e.message NOT LIKE ('ifSpeed: 5% Mbps -> 6% Mbps')
AND e.message NOT LIKE ('ifSpeed: 4% Mbps -> 5% Mbps')
AND e.message NOT LIKE ('ifSpeed: 3% Mbps -> 4% Mbps')
AND e.message NOT LIKE ('ifSpeed: 2% Mbps -> 3% Mbps')
GROUP BY d.sysName
HAVING COUNT(1) > 3
ORDER BY COUNT(1) DESC
LIMIT 5;
