实践教程 Q1.4 Q1.4: Over 1 month, how often was each server blocked on disk I/O? SELECT machinename, COUNT() AS spikes FROM logs1 WHERE machinegroup 'Servers' AND cpuwio > 0.99 AND logtime > TIMESTAMP '20161201 00:00:00' AND logtime < TIMESTAMP '20170101 00:00:00' GROUP BY machinename ORDER BY spikes DESC LIMIT 10; Q1.5 Q1.5: Which externally reachable VMs have run low on memory? SELECT machinename, dt, MIN(memfree) AS memfreemin FROM ( SELECT machinename, CAST(logtime AS DATE) AS dt, memfree FROM logs1 WHERE machinegroup 'DMZ' AND memfree IS NOT NULL ) AS r GROUP BY machinename, dt HAVING MIN(memfree) < 10000 ORDER BY machinename, dt; Q1.6 Q1.6: What is the total hourly network traffic across all file servers? SELECT dt, hr, SUM(netin) AS netinsum, SUM(netout) AS netoutsum, SUM(netin) + SUM(netout) AS bothsum FROM ( SELECT CAST(logtime AS DATE) AS dt, EXTRACT(HOUR FROM logtime) AS hr, COALESCE(bytesin, 0.0) / 1000000000.0 AS netin, COALESCE(bytesout, 0.0) / 1000000000.0 AS netout FROM logs1 WHERE machinename IN ('allsorts','andes','bigred','blackjack','bonbon', 'cadbury','chiclets','cotton','crows','dove','fireball','hearts','huey', 'lindt','milkduds','milkyway','mnm','necco','nerds','orbit','peeps', 'poprocks','razzles','runts','smarties','smuggler','spree','stride', 'tootsie','trident','wrigley','york') ) AS r GROUP BY dt, hr ORDER BY bothsum DESC LIMIT 10;