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() + "%"; } }