LG G3 히든메뉴 진입

다이얼에서

*#546368#*460# 

하면 히든메뉴 진입...

매번 헤매느니 한번 적자..

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

SQL AGENT 에서 Application Name 으로 프로시저 찾기

먼저 하단 함수 생성

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (
   @program_name nvarchar(128)
)
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @start_of_job_id int
SET @start_of_job_id = CHARINDEX('(Job 0x', @program_name) + 7
RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(
      SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) + 
      SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 16, 4) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)
   ELSE NULL
   END
END --FUNCTION

에이전트 로그에서

SELECT *
FROM msdb.dbo.sysjobs
WHERE
job_id = dbo.GetJobIdFromProgramName ('SQLAgent - TSQL JobStep (Job 0xC8FAC217480CAB478E3BB07AB02962E9 : Step 2) ') 

같이 검색하면 짜잔~~! 나옴.

이거 땜시로 오늘 완전 개고생 함..