select host,name from hosts where hostid in (select hostid from items where itemid in (select itemid from trends_uint where itemid in (select itemid from items where name="Available memory") group by itemid having min(value_min)>2000000000));

出现10次以上，最大内存不足500MB的主机
select name from hosts where hostid in (select hostid from items where itemid in (select itemid from trends_uint where itemid in (select itemid from items where name="Available memory") and value_max<500000000 group by itemid having count(*)>10));

select * from trends_uint where date_format(FROM_UNIXTIME(clock),'%Y-%m')="2018-11";
29124

select host,name from hosts where hostid in (select hostid from items where itemid in (select itemid from trends_uint where itemid in (select itemid from items where name="Available memory") and date_format(FROM_UNIXTIME(clock),'%Y-%m')="2018-11" ));

select hostid,min(b.value_min),max(b.value_max),avg(value_avg) from items join (select * from trends_uint where itemid in (select itemid from items where name="Available memory") and date_format(FROM_UNIXTIME(clock),'%Y-%m')="2018-11" and itemid=29124) as b on items.itemid=b.itemid group by hostid;

select hostid,min(b.value_min),max(b.value_max),avg(value_avg) from items join (select * from trends_uint where itemid in (select itemid from items where name="Available memory") and date_format(FROM_UNIXTIME(clock),'%Y-%m')="2018-11") as b on items.itemid=b.itemid group by hostid;

--查询月份内存空闲
select host,name,c.* into outfile '/tmp/availmemory.csv' fields terminated by ',' optionally enclosed by ' ' lines terminated by '\r\n' from hosts join (select hostid,min(b.value_min)/(102410241024),max(b.value_max)/(102410241024),avg(value_avg)/(102410241024) from items join (select * from trends_uint where itemid in (select itemid from items where name="Available memory" or name="Free memory") and date_format(FROM_UNIXTIME(clock),'%Y-%m')="2018-11") as b on items.itemid=b.itemid group by hostid) as c on hosts.hostid=c.hostid;

cpu load
select host,name,c.* into outfile '/tmp/cpuload.csv' fields terminated by ',' optionally enclosed by ' ' lines terminated by '\r\n' from hosts join (select hostid,min(b.value_min),max(b.value_max),avg(value_avg) from items join (select * from trends where itemid in (select itemid from items where name like "%1 min average per core%" or name="CPU Load") and date_format(FROM_UNIXTIME(clock),'%Y-%m')="2018-11") as b on items.itemid=b.itemid group by hostid) as c on hosts.hostid=c.hostid;