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) {
}
}
}
}
}
