Java로 SQL Agent Log에서 잡다한 거 빼고 보기

0. 일단.. SQL Server Profiler 한 바퀴 돌려주고

1. xml로 저장해서 java로 parsing 한다.

2. 내 맘대로 짠거임.. 알아서 변용하도록

3. 남자답게!! 출처는 밝히자!!

package com.lifebook.util;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class parseSqlLog {
 private static Connection conn = null;
 private static PreparedStatement psmt = null;
 private static ResultSet rs = null;
 private static String conUrl = "니꺼 jdbc 접속 문자열!";
 private static String[] chkFilterList = {
  "SQL:BatchCompleted",
  "Trace Stop",
  "RPC:Completed",
  "select @@microsoftversion",
  "select convert(sysname, serverproperty(N'servername'))",
  "SET TEXTSIZE 1024",
  "SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())",
  "SELECT N'Testing Connection...'",
  "SET NO_BROWSETABLE ON",
  "SET NO_BROWSETABLE OFF",
  "set fmtonly off",
  "select serverproperty(N'instancename')",
  "ServerProperty('",
  "SERVERPROPERTY",
  "@@microsoftversion",
  "#err_log_tmp er",
  "SERVERPROPERTY(N'",
  "SET LOCK_TIMEOUT",
  "EXEC master.dbo.xp_readerrorlog",
  "CAST(serverproperty(N'Servername') AS sysname)",
  "DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname)",
  "select value_in_use from sys.configurations where configuration_id",
  "select is_srvrolemember('sysadmin') * 1 +is_srvrolemember('serveradmin') * 2 +is_srvrolemember('setupadmin') * 4 +is_srvrolemember('securityadmin') * 8 +is_srvrolemember('processadmin') * 16 +is_srvrolemember('dbcreator') * 32 +is_srvrolemember('diskadmin') * 64+ is_srvrolemember('bulkadmin') * 128",
  "select SERVERPROPERTY(N'servername')",
  "EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0xC8FAC217480CAB478E3BB07AB02962E9",
  "BEGIN TRAN UPDATE msdb.dbo.sysjobsteps SET last_run_outcome"
 };

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  try {
   File fXmlFile = new File("E:\\workspace\\parseSqlLog\\src\\com\\lifebook\\util\\20170703.xml");
   DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
   DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
   Document doc = dBuilder.parse(fXmlFile);
   doc.getDocumentElement().normalize();
   System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
   System.out.println("----------------------------");
   NodeList nList = doc.getElementsByTagName("Events");
   double rowCnt = 0d;
   for (int temp = 0; temp < nList.getLength(); temp++) {
    System.out.println("item:::" + nList.item(temp).getChildNodes().getLength());
    Node nNode = nList.item(temp);
    NodeList cList = nNode.getChildNodes();
    for(int cCnt = 0 ; cCnt < cList.getLength() ; cCnt ++){
     Node cNode = cList.item(cCnt);
     if (cNode.getNodeType() == Node.ELEMENT_NODE) {
      Element eElement = (Element) cNode;
      if(chkString(eElement.getAttribute("name").toString())){
       //System.out.println("name : " + eElement.getAttribute("name"));
       NodeList colsList = cNode.getChildNodes();
       for(int colCnt = 0 ; colCnt < colsList.getLength() ; colCnt ++){
        Node colNode = colsList.item(colCnt);
        //System.out.println(colNode.getNodeName());
        if (colNode.getNodeType() == Node.ELEMENT_NODE) {
         Element colEle = (Element) colNode;
         //System.out.println("cols name : " + colEle.getAttribute("name"));
         if(colEle.getAttribute("name").toString().equals("TextData")){
          if(chkString(colEle.getTextContent())){
           rowCnt ++;
           //System.out.println(rowCnt + "::::::" + colEle.getTextContent());
           //System.out.println(colEle.getTextContent().toLowerCase().indexOf("select"));
           chkDbCmd(colEle.getTextContent());
           if(colEle.getTextContent().toLowerCase().indexOf("select")>=0){
            //System.out.println(rowCnt + "::::::" + colEle.getTextContent());
            //chkDbCmd(colEle.getTextContent());    
           }
          }          
         }
        }
       }
      }
     }
    }
   }
  } catch (ParserConfigurationException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SAXException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 public static boolean chkString(String chkStr){
  boolean rtnBool = true;
  for(int chkCnt = 0 ; chkCnt < chkFilterList.length ; chkCnt++){
   //System.out.println(chkStr);
   //System.out.println(chkFilterList[chkCnt]);
   //System.out.println(chkStr.indexOf(chkFilterList[chkCnt]));
   if(chkStr.indexOf(chkFilterList[chkCnt]) >= 0){
    rtnBool = false;
    break;
   }
  }
  return rtnBool;
 }
 
 public static void chkDbCmd(String sql){//얘는 SQL 실행시 오류 발생하는지 알아볼라고 만든거임..
 try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   conn = DriverManager.getConnection(conUrl);
   psmt = conn.prepareStatement(sql);
   rs = psmt.executeQuery();
   
   rs.close();
   psmt.close();
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println(sql);
   e.printStackTrace();
  } finally {
   if (conn != null) {
    try {
     conn.close();
    } catch (Exception e) {
    }
   }
   
   if (psmt != null) {
    try {
     psmt.close();
    } catch (Exception e) {
    }
   }
   
   if (rs != null) {
    try {
     rs.close();
    } catch (Exception e) {
    }
   }
  }
 }
}