巡检

维护的时候平时检查机器cpu,日志,数据库表空间,日志那些用的,检查的内容在mysql里面配置,不废话了,直接主程序代码

package scttsc.res;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

import ch.ethz.ssh2.StreamGobbler;

public class MyCheckingMachineViaMySqlTest {

 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  MyCheckingMachineViaMySql mc = new MyCheckingMachineViaMySql();
  mc.runCallInMySql("call proctrunctables()");
  mc.execute();
  mc.runCallInMySql("call procchecking()");
  mc.runCallInMySql("call procupdatenextplantime()");
 }
}

class MyCheckingMachineViaMySql {
 private Connection connMySql;

 // host,username,password
 private Hashtable<String, ch.ethz.ssh2.Connection> sshConnections =
   new Hashtable<String, ch.ethz.ssh2.Connection>();

 // host,username,password
 private Hashtable<String, MyTelnet> telnetConnections =
   new Hashtable<String, MyTelnet>();

 // host,port,sid,user,password
 private Hashtable<String, Connection> oracleConnections =
   new Hashtable<String, Connection>();

 private String logFile = "c:/log1.txt";

 public MyCheckingMachineViaMySql()  {
  try {
   MyLogger.log(logFile, "=====================/r/n"+new Date().toString()+"=====================/r/n");
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  MyConnectDb mcd = new MyConnectDb();
  connMySql =
    mcd.mysqlConnectDb("localhost", 3306, "checkmachine", "root", "root");

  // this.getDeploymentViaMysql(areaName);
 }

 private String genCall(String procName, int step, String returnstring) {
  // '替换成/'
  returnstring = returnstring.replaceAll("'", "'");
  String res =
    "call " + procName + "(" + step + ",'" + returnstring + "')";
  return res;
 }

 public void runCallInMySql(String sqlCall) {
  try {
   MyQuerySql mysqlmqs = new MyQuerySql();
   // String tmpcall = "call procupdatequerysqlblocks()";
   System.out.println(sqlCall);
   // MyLogger.log(logFile, "sqlCall:" + sqlCall );
   mysqlmqs.executeDDL(sqlCall, connMySql);
   // } catch (IOException e) {
   // // System.out.println("runCallInMySql,IO异常,sqlCall:"+sqlCall);
   // // TODO Auto-generated catch block
   // e.printStackTrace();
   // try {
   // MyLogger.log(logFile, "IO异常:" + "/r/n" + e.toString());
   // } catch (IOException e1) {
   // // TODO Auto-generated catch block
   // e1.printStackTrace();
   // }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "runCallInMySql(),sql异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
  }
 }

 private void insertResult(int step, MyResultSet mrs) {
  String tablename = "tmp" + step;

  this.mysqlTruncateTable(step);
  for (int i = 0; i < mrs.size(); i++) {
   this.mysqlInsertTableWithoutColumnNames(step, mrs.getOneRecord(i));
  }

  // this.mysqlCreateTable(step, mrs);
  // // for (int i = 0; i < mrs.size(); i++) {
  // // this.mysqlInsertTable(step, mrs.getOneRecord(i));
  // // }
 }

 private int mysqlInsertTableWithoutColumnNames(int step, MyRecord mr) {
  try {
   String tablename = "tmp" + step;
   MyQuerySql mysqlmqs = new MyQuerySql();
   String insertSql =
     "insert into " + tablename + " values(null,null,'";
   for (int i = 0; i < mr.size() - 1; i++) {
    insertSql += mr.getString(i) + "','";
   }
   insertSql += mr.getString(mr.size() - 1) + "')";
   System.out.println(insertSql);
   return mysqlmqs.executeUpdate(insertSql, connMySql);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "mysqlInsertTableWithoutColumnNames(),sql异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
   return 0;// 未影响任何行
  }
 }

 private int mysqlInsertTable(int step, MyRecord mr) {
  try {
   String tablename = "tmp" + step;
   MyQuerySql mysqlmqs = new MyQuerySql();
   String insertSql = "insert into " + tablename + "(";
   String values = "('";
   for (int i = 0; i < mr.size() - 1; i++) {
    insertSql += "col" + i + ",";
    values += mr.getString(i) + "','";
   }
   insertSql += "col" + (mr.size() - 1) + ") values";
   values += mr.getString(mr.size() - 1) + "')";
   insertSql += values;
   System.out.println(insertSql);
   return mysqlmqs.executeUpdate(insertSql, connMySql);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "mysqlInsertTable(),sql异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
   return 0;// 未影响任何行
  }
 }

 private boolean mysqlTruncateTable(int step) {
  try {
   String tablename = "tmp" + step;
   MyQuerySql mysqlmqs = new MyQuerySql();
   String sql = "truncate table " + tablename;
   mysqlmqs.executeDDL(sql, connMySql);
   return true;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "mysqlTruncateTable(),sql异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
   return false;
  }
 }

 /**
  * 各列都采用varchar2(50),注意如果结果集数据过多就会报错哦
  *
  * @param tablename
  *            tmp+step的形式
  * @param mrs
  *            用于getColumnNames();
  * @return
  */
 private boolean mysqlCreateTable(int step, MyResultSet mrs) {
  try {
   String tablename = "tmp" + step;
   Vector<String> mrsColumnNames = mrs.getColumnNames();
   MyQuerySql mysqlmqs = new MyQuerySql();
   // drop
   String sqldroptable = "drop table if exists " + tablename;
   try {
    int r = mysqlmqs.executeDDL(sqldroptable, connMySql);
//    System.out.println(sqldroptable);
   } catch (SQLException e) {
    e.printStackTrace();
//    System.out.println("drop表异常:" + tablename);
    try {
//     MyLogger.log(logFile, "drop表异常,写日志异常:" + tablename + "/r/n"
//       + e.toString());
     MyLogger.log(logFile, "mysqlCreateTable(),sql异常:" + "/r/n" + e.toString());
    } catch (IOException e1) {
     // TODO Auto-generated catch block
     e1.printStackTrace();
    }
   }
   // create
   if (mrsColumnNames.size() > 0) {
    String sqlcreatetable =
      "create table "
        + "`"
        + tablename
        + "`"
        + "(`id` int(11) NOT NULL auto_increment,"
        + "`indate` timestamp NOT NULL default CURRENT_TIMESTAMP,";
    for (int i = 0; i < mrsColumnNames.size(); i++) {
     // sqlcreatetable +=
     // "`col" + i + "` varchar(50) default NULL,";
     sqlcreatetable +=
       "`" + mrsColumnNames.get(i)
         + "` varchar(50) default NULL,";
    }
    sqlcreatetable +=
      ("PRIMARY KEY  (`id`)"
        + ")ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
    // MyLogger.log(logFile, sqlcreatetable);
    mysqlmqs.executeDDL(sqlcreatetable, connMySql);
    // 因为标题很可能有非法字符,将标题行插入作为第一行数据.未实现

    return true;
   }
   return false;
  } catch (SQLException e) {
   System.out.println("数据库异常:");
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "数据库异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
   return false;
//  } catch (Exception e) {
//   e.printStackTrace();
//   try {
//    MyLogger.log(logFile, "异常:" + "/r/n" + e.toString());
//   } catch (IOException e1) {
//    // TODO Auto-generated catch block
//    e1.printStackTrace();
//   }
//   return false;
  }
 }

 public void execute() {
  try {
   MyQuerySql mqs = new MyQuerySql();
   String tmpsql = "select * from viewscripts order by step ";
   // String tmpsql =
   // "select * from viewscripts where step=5 order by step ";
   MyResultSet tmpmrs = mqs.getAllValues(tmpsql, connMySql);
   for (int i = 0; i < tmpmrs.size(); i++) {
    int step =
      tmpmrs.getOneRecord(i).getInt(tmpmrs.columnIndex("step"));
    String runtype =
      tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("runtype")).trim().toLowerCase();
    String runonhost =
      tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("runonhost")).trim().toLowerCase();
    String username =
      tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("username"));
    String password =
      tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("password"));
    String runsentence =
      tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("runsentence"));
    // String
    // outtablename=tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("outtablename")).trim().toLowerCase();
    if (runtype.equals("ssh")) {
     System.out.println(runonhost + "/t" + runsentence);
     String res =
       this.execSSH2(runonhost, username, password, runsentence);
     System.out.println(res);
     String tmpcall =
       this.genCall("procinsertstring", step, res);
     mqs.executeDDL(tmpcall, connMySql);
    } else if (runtype.equals("oracle_rs")) {
     System.out.println(runonhost + "/t" + runsentence);
     String port =
       tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("port"));
     String sid =
       tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("sid")).trim().toLowerCase();
     MyResultSet res =
       this.execOracle(runonhost, port, sid, username, password, runsentence);
     this.insertResult(step, res);
    } else if (runtype.equals("telnet")) {
     System.out.println(runonhost + "/t" + runsentence);
     String telnetprompt =
       tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("telnetprompt")).trim();

     String res =
       this.execTelnet(runonhost, username, password, runsentence, telnetprompt);
     // System.out.println(runsentence);
     // System.out.println("执行结果:/r/n"+res);
     String tmpcall =
       this.genCall("procinsertstring", step, res);
     // System.out.println(tmpcall);
     // MyLogger.log(logFile,tmpcall);
     mqs.executeDDL(tmpcall, connMySql);
    }
   }
   this.closeAllConnections();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "SQL异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   try {
    MyLogger.log(logFile, "SQL异常:" + "/r/n" + e.toString());
   } catch (IOException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
   }
  }
 }

 public void closeAllConnections() {
  // oracle
  Enumeration<String> keys = oracleConnections.keys();
  while (keys.hasMoreElements()) {
   try {
    String[] ka = keys.nextElement().split(",");
    this.closeOracleConnection(ka[0], ka[1], ka[2], ka[3], ka[4]);
   } catch (SQLException e) {
    // TODO: handle exception
    System.out.println(keys.toString());
    e.printStackTrace();
    try {
     MyLogger.log(logFile, "SQL异常:" + "/r/n" + e.toString());
    } catch (IOException e1) {
     // TODO Auto-generated catch block
     e1.printStackTrace();
    }
   }
  }
  // telnet
  keys = telnetConnections.keys();
  while (keys.hasMoreElements()) {
   String[] ka = keys.nextElement().split(",");
   this.closeTelnetConnection(ka[0], ka[1], ka[2]);
  }
  // ssh
  keys = sshConnections.keys();
  while (keys.hasMoreElements()) {
   String[] ka = keys.nextElement().split(",");
   this.closeSSHConnection(ka[0], ka[1], ka[2]);
  }
 }

 private ch.ethz.ssh2.Connection connectSSH(String hostname,
   String username, String password) throws IOException {
  String key = hostname + "," + username + "," + password;
  if (!sshConnections.containsKey(key)) {
   ch.ethz.ssh2.Connection sshconn =
     new ch.ethz.ssh2.Connection(hostname);
   sshconn.connect();
   boolean isAuthenticated =
     sshconn.authenticateWithPassword(username, password);
   if (isAuthenticated == false) {
    System.out.println("user or password is invalid:" + hostname
      + "," + username + "," + password);
    throw new IOException("Authentication failed.");
   }
   sshConnections.put(key, sshconn);
  }
  return sshConnections.get(key);
 }

 private void closeSSHConnection(String hostname, String username,
   String password) {
  String key = hostname + "," + username + "," + password;
  ch.ethz.ssh2.Connection sshconn = sshConnections.get(key);
  System.out.println("关闭ssh" + hostname);
  sshconn.close();
  sshConnections.remove(key);
 }

 private MyTelnet connectTelnet(String hostname, String username,
   String password, String prompt) {
  String key = hostname + "," + username + "," + password;
  if (!telnetConnections.containsKey(key)) {
   MyTelnet mt = new MyTelnet(hostname, username, password, prompt);
   mt.setPrompt(prompt);
   telnetConnections.put(key, mt);
  }
  // else{
  // MyTelnet mt=telnetConnections.get(key);
  // mt.setPrompt(prompt);
  // telnetConnections.put(key, mt);
  // }
  return telnetConnections.get(key);
 }

 private void closeTelnetConnection(String hostname, String username,
   String password) {
  String key = hostname + "," + username + "," + password;
  MyTelnet mt = telnetConnections.get(key);
  System.out.println("关闭telnet" + hostname);
  mt.disconnect();
  telnetConnections.remove(key);
 }

 private void closeOracleConnection(String host, String port, String sid,
   String username, String password) throws SQLException {
  try {
   String key =
     host + "," + port + "," + sid + "," + username + ","
       + password;
   Connection con = oracleConnections.get(key);
   System.out.println("关闭oracle" + host);
   con.close();
   oracleConnections.remove(key);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("oracleConnections 关闭异常"
     + oracleConnections.toString());
   throw e;
  }
 }

 private Connection connectOracle(String host, String port, String sid,
   String username, String password) {
  String key =
    host + "," + port + "," + sid + "," + username + "," + password;
  if (!oracleConnections.containsKey(key)) {
   MyConnectDb mcd = new MyConnectDb();
   Connection conn =
     mcd.oracleConnectDb(host, port, sid, username, password);
   oracleConnections.put(key, conn);
  }
  return oracleConnections.get(key);
 }

 // 1.顺序: String host, String port,String sid,String user, String
 // password
 private MyResultSet execOracle(String host, String port, String sid,
   String username, String password, String command)
   throws SQLException {
  MyQuerySql mqs = new MyQuerySql();
  if (command.endsWith(";")) {
   command = command.substring(0, command.length() - 1);
  }
  return mqs.getAllValues(command, this.connectOracle(host, port, sid, username, password));
 }

 private String execTelnet(String hostname, String username,
   String password, String command, String prompt) {
  MyTelnet mt = this.connectTelnet(hostname, username, password, prompt);
  String s = mt.sendCommand(command);
  System.out.println("关闭telnet" + hostname);
  return s;
 }

 private String execSSH2(String hostname, String username, String password,
   String command) throws IOException {
  StringBuffer sb = new StringBuffer();
  ch.ethz.ssh2.Connection sshconn =
    this.connectSSH(hostname, username, password);
  ch.ethz.ssh2.Session sshsession = sshconn.openSession();
  sshsession.execCommand(command);
  InputStream stdout = new StreamGobbler(sshsession.getStdout());
  BufferedReader br = new BufferedReader(new InputStreamReader(stdout));
  String line;
  while ((line = br.readLine()) != null) {
   sb.append(line + "/r/n");
  }
  sshsession.close();
  return sb.toString();
 }
 // public boolean getDeploymentViaMysql(String[] areaName) {
 // try {
 // MyQuerySql mqs = new MyQuerySql();// 囧,当时怎么没把他做成子类哟...
 // for (int i = 0; i < areaName.length; i++) {
 // String tmpsql =
 // "select * from viewareadbconnectinfo where areanamepy='"
 // + areaName[i] + "' and dbtype='s'";
 // MyResultSet tmpmrs = mqs.getAllValues(tmpsql, connMySql);
 // if (tmpmrs.size() == 1) {
 // String tmpip =
 // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("ip")).trim();
 // String tmpport =
 // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("port")).trim();
 // String tmpuser =
 // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("user")).trim();
 // String tmppwd =
 // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("password")).trim();
 // String[] s =
 // new String[] { tmpip, tmpport, tmpuser, tmppwd };
 // areaToHosts.put(areaName[i], s);
 // // System.out.println(Arrays.asList(s));
 // } else {
 // throw new RuntimeException("viewareadbconnectinfo配置错误");
 // }
 // }
 // } catch (SQLException e) {
 // System.out.println("读取数据库连接配置表出错:");
 // e.printStackTrace();
 // return false;
 // }
 // return true;
 // }
}