public List queryServerPerformance(String day, String field) {
List list = new ArrayList();
StringBuffer sql = new StringBuffer(200);
sql.append(
"select topo_tbl.ip_long ip_long,topo_tbl.ip_address ip_address,topo_tbl.alias alias,cpu_tbl.id node_id,cpu_value,mem_value,disk_value from");
sql.append(" (select * from topo_host_node where category=4) topo_tbl left join");
/* cpu */
sql.append(
" (select a.node_id id,ROUND(avg(a.percentage),1) cpu_value from topo_node_multi_data a");
sql.append(
" where a.moid in ('001001','004001') or a.moid='001001' and substring(a.log_time,1,10)='");
sql.append(day);
sql.append("' group by a.node_id) cpu_tbl ");
sql.append(" on (topo_tbl.id=cpu_tbl.id) left join");
/* mem */
sql.append(
" (select b.node_id id,ROUND(avg(b.value),1) mem_value from topo_node_single_data b");
sql.append(" where b.moid in('001002','004002') and substring(b.log_time,1,10)='");
sql.append(day);
sql.append("' group by b.node_id ) mem_tbl ");
sql.append(" on (topo_tbl.id=mem_tbl.id) left join");
/* disk */
sql.append(
" (select c.node_id id,ROUND(avg(c.percentage),1) disk_value from topo_node_multi_data c");
sql.append(" where c.moid in ('001003','004003') and substring(c.log_time,1,10)='");
sql.append(day);
sql.append("' group by c.node_id ) disk_tbl ");
sql.append(" on (topo_tbl.id=disk_tbl.id)");
if (field.equals("ip_long")) {
sql.append(" order by " + field);
} else {
sql.append(" order by " + field + " desc");
}
try {
rs = conn.executeQuery(sql.toString());
while (rs.next()) {
ServerPerformance vo = new ServerPerformance();
vo.setNodeId(rs.getInt("node_id"));
vo.setIpAddress(rs.getString("ip_address"));
vo.setAlias(rs.getString("alias"));
vo.setCpuValue(rs.getFloat("cpu_value"));
vo.setDiskValue(rs.getFloat("disk_value"));
vo.setMemValue(rs.getFloat("mem_value"));
list.add(vo);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
conn.close();
}
return list;
}
public void createReport() {
setHead("服务器性能数据报表");
setNote("数据来源：东华网管软件");
setTableHead(new String[] {"序号", "服务器名", "IP地址", "CPU利用率", "内存利用率", "硬盘利用率"});
setColWidth(new int[] {2, 5, 4, 3, 3, 3});
InformDao dao = new InformDao();
List list = dao.queryServerPerformance(timeStamp, orderField);
table = new String[list.size()][tableHead.length];
for (int i = 0; i < list.size(); i++) {
ServerPerformance vo = (ServerPerformance) list.get(i);
table[i][0] = String.valueOf(i + 1); // 序号
table[i][1] = vo.getAlias();
table[i][2] = vo.getIpAddress();
table[i][3] = vo.getCpuValue() + "%";
table[i][4] = vo.getMemValue() + "%";
table[i][5] = vo.getDiskValue() + "%";
}
}
