Logparser는 MS에서 만든 강력한 IIS, W3C, 이벤트 로그 분석툴로서 SQL 쿼리타입으로 조건별 검색할 수 있는 강력한 툴입니다.
1) 가장 최근에 생성된 시간을 기준으로 ASP 스크립트를 변조한 Trojan Files 여부를 진단
C:\logparser2.2\logparser -i:FS "SELECT TOP 20 Path, CreationTime FROM C:\inetpub\wwwroot\*.* ORDER BY CreationTime DESC" -rtp:-1
2). 가장 최근에 수정된 Files 로그 찾기
C:\logparser2.2\logparser -i:FS "SELECT TOP 20 Path, LastWriteTime FROM C:\inetpub\wwwroot\*.* ORDER BY LastWriteTime DESC" -rtp:-1
3). 해커가 Trojan Files을 삭제한 경우에 HTTP 200 서버코드 흔적 로그를 찾는다.
C:\logparser "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits FROM ex*.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1
* nc.exe, tini.exe, root.exe, cmd.exe, upload.asp, aspexec.asp, cmd.asp 같은 파일 이름이 있으면 의심
4) Script Abuse 분석(가장 많은 Request 요청을 받은 Executable 파일의 확장자 확인)
C:\logparser -i:FS "SELECT TO_LOWERCASE(SUBSTR(Name, LAST_INDEX_OF(Name, '.'), STRLEN(Name))) AS Extenstion, Count(*) AS Files FROM C:\inetpub \wwwroot\*.*, C:\inetpub\scripts\*.* WHERE Attribute NOT LIKE 'D%' GROUP BY Extenstion ORDER BY Files DESC" -rtp:-1
* 특히, .ASP, .DLL 파일 요청을 유심히 봐야함
5) HTTP 서버 500 에러코드 검사
C:\logparser "SELECT [cs-uri-stem], [cs-uri-query], Count(*) AS [Hits] FROM c:\logs\web\ex*.log WHERE sc-status = 500 GROUP BY [cs-uri-stem], [cs-uri-query] ORDER BY [hits], [cs-uri-stem] DESC" -rtp:-1 -i:iisw3c
6) 가장 많은 Request Hit 수를 높음 ASP, DLL 파일 확인
C:\logparser "SELECT TO_STRING(TO_TIMESTAMP(date, time), 'yyyy-MM-dd') AS Day, cs-uri-stem, Count(*) AS Total ex*.log WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' OR TO_LOWERCASE(cs-uri-stem) LIKE '%.exe') GROUP BY Day, cs-uri-stem ORDER BY cs-uri-stem, Day" -rtp:-1
7) 시간당 에러수가 가장 많이 발생한 날짜 확인
C:\logparser "SELECT date, QUANTIZE(time, 3600) AS hour, sc-status, Count(*) AS Errors FROM ex03*.log WHERE sc-status>=400 GROUP BY date, hour, sc-status HAVING Errors>25 ORDER BY Error DESC" -rtp:-1
* 25개 이상의 에러코드(404코드)를 발생한 날짜와 시간 결과를 출력
8) 하루동안 50번이상 동일 페이지에 접속을 시도한 클라이언트 IP 확인
C:\logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits DESC" -rtp:-1
9) 하루동안 50번이상 동일 페이지에 접속을 시도한 클라이언트 IP 확인
C:\logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits DESC" -rtp:-1
10) 모든 ASP 에러 기록 확인
C:\logparser "SELECT cs-uri-query, Count(*) AS Total FROM ex*.log WHERE sc-status>=500 GROUP BY cs-uri-query ORDER BY Total DESC" -rtp:-1
* 특히, ODBC와 ADO 에러는 SQL Injection 가능성이 있으므로 주의깊게 살펴봐야 함
11) 스크립트 및 Executable 파일의 HTTP 서버 코드 기록 확인
C:\logparser "SELECT cs-uri-stem, sc-status, Count(*) AS Total FROM ex*.log WHERE TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%' GROUP BY cs-uri-stem, sc-status ORDER BY cs-uri-stem, sc-status" -rtp:-1
12) Win32 Status Code 분석을 통한 Attack 확인
C:\logparser "SELECT cs-uri-stem, WIN32_ERROR_DESCRIPTION(sc-win32-status) AS Error, Count(*) AS Total FROM ex*.log WHERE sc-win32-status>0 AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' OR TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%') GROUP BY cs-uri-stem, Error ORDER BY cs-uri-stem, Error" -rtp:-1
13) HTTP Method 통계 분석
C:\logparser "SELECT cs-uri-stem, cs-method, Count(*) AS Total FROM ex*.log WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%') GROUP BY cs-uri-stem, cs-method ORDER BY cs-uri-stem, cs-method" -rtp:-1
1) 가장 최근에 생성된 시간을 기준으로 ASP 스크립트를 변조한 Trojan Files 여부를 진단
C:\logparser2.2\logparser -i:FS "SELECT TOP 20 Path, CreationTime FROM C:\inetpub\wwwroot\*.* ORDER BY CreationTime DESC" -rtp:-1
2). 가장 최근에 수정된 Files 로그 찾기
C:\logparser2.2\logparser -i:FS "SELECT TOP 20 Path, LastWriteTime FROM C:\inetpub\wwwroot\*.* ORDER BY LastWriteTime DESC" -rtp:-1
3). 해커가 Trojan Files을 삭제한 경우에 HTTP 200 서버코드 흔적 로그를 찾는다.
C:\logparser "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits FROM ex*.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1
* nc.exe, tini.exe, root.exe, cmd.exe, upload.asp, aspexec.asp, cmd.asp 같은 파일 이름이 있으면 의심
4) Script Abuse 분석(가장 많은 Request 요청을 받은 Executable 파일의 확장자 확인)
C:\logparser -i:FS "SELECT TO_LOWERCASE(SUBSTR(Name, LAST_INDEX_OF(Name, '.'), STRLEN(Name))) AS Extenstion, Count(*) AS Files FROM C:\inetpub \wwwroot\*.*, C:\inetpub\scripts\*.* WHERE Attribute NOT LIKE 'D%' GROUP BY Extenstion ORDER BY Files DESC" -rtp:-1
* 특히, .ASP, .DLL 파일 요청을 유심히 봐야함
5) HTTP 서버 500 에러코드 검사
C:\logparser "SELECT [cs-uri-stem], [cs-uri-query], Count(*) AS [Hits] FROM c:\logs\web\ex*.log WHERE sc-status = 500 GROUP BY [cs-uri-stem], [cs-uri-query] ORDER BY [hits], [cs-uri-stem] DESC" -rtp:-1 -i:iisw3c
6) 가장 많은 Request Hit 수를 높음 ASP, DLL 파일 확인
C:\logparser "SELECT TO_STRING(TO_TIMESTAMP(date, time), 'yyyy-MM-dd') AS Day, cs-uri-stem, Count(*) AS Total ex*.log WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' OR TO_LOWERCASE(cs-uri-stem) LIKE '%.exe') GROUP BY Day, cs-uri-stem ORDER BY cs-uri-stem, Day" -rtp:-1
7) 시간당 에러수가 가장 많이 발생한 날짜 확인
C:\logparser "SELECT date, QUANTIZE(time, 3600) AS hour, sc-status, Count(*) AS Errors FROM ex03*.log WHERE sc-status>=400 GROUP BY date, hour, sc-status HAVING Errors>25 ORDER BY Error DESC" -rtp:-1
* 25개 이상의 에러코드(404코드)를 발생한 날짜와 시간 결과를 출력
8) 하루동안 50번이상 동일 페이지에 접속을 시도한 클라이언트 IP 확인
C:\logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits DESC" -rtp:-1
9) 하루동안 50번이상 동일 페이지에 접속을 시도한 클라이언트 IP 확인
C:\logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits DESC" -rtp:-1
10) 모든 ASP 에러 기록 확인
C:\logparser "SELECT cs-uri-query, Count(*) AS Total FROM ex*.log WHERE sc-status>=500 GROUP BY cs-uri-query ORDER BY Total DESC" -rtp:-1
* 특히, ODBC와 ADO 에러는 SQL Injection 가능성이 있으므로 주의깊게 살펴봐야 함
11) 스크립트 및 Executable 파일의 HTTP 서버 코드 기록 확인
C:\logparser "SELECT cs-uri-stem, sc-status, Count(*) AS Total FROM ex*.log WHERE TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%' GROUP BY cs-uri-stem, sc-status ORDER BY cs-uri-stem, sc-status" -rtp:-1
12) Win32 Status Code 분석을 통한 Attack 확인
C:\logparser "SELECT cs-uri-stem, WIN32_ERROR_DESCRIPTION(sc-win32-status) AS Error, Count(*) AS Total FROM ex*.log WHERE sc-win32-status>0 AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' OR TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%') GROUP BY cs-uri-stem, Error ORDER BY cs-uri-stem, Error" -rtp:-1
13) HTTP Method 통계 분석
C:\logparser "SELECT cs-uri-stem, cs-method, Count(*) AS Total FROM ex*.log WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%') GROUP BY cs-uri-stem, cs-method ORDER BY cs-uri-stem, cs-method" -rtp:-1
| Purpose | Query | Sample Output | ||||||||||||||||||||||||
| Number of Hits per Client IP, including a Reverse DNS lookup (SLOW) | SELECT c-ip As Machine, REVERSEDNS(c-ip) As Name, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Machine ORDER BY Hits DESC |
| ||||||||||||||||||||||||
| Top 25 File Types | SELECT TOP 25 EXTRACT_EXTENSION(cs-uri-stem) As Extension, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Extension ORDER BY Hits DESC |
| ||||||||||||||||||||||||
| Top 25 URLs | SELECT TOP 25 cs-uri-stem as Url, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY cs-uri-stem ORDER By Hits DESC |
| ||||||||||||||||||||||||
| Number of hits per hour for the month of March | SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 3600) AS Hour, COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE date>'2010-03-01' and date<'2010-04-01' Group By Hour |
| ||||||||||||||||||||||||
| Number of hits per Method (GET, POST, etc) | SELECT cs-method As Method, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Method |
| ||||||||||||||||||||||||
| Number of requests made by user | SELECT TOP 25 cs-username As User, COUNT(*) as Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE User Is Not Null GROUP BY User |
| ||||||||||||||||||||||||
| Extract Values from Query String (d and t) and use them for Aggregation | SELECT TOP 25 EXTRACT_VALUE(cs-uri-query,'d') as Query_D, EXTRACT_VALUE(cs-uri-query,'t') as Query_T, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE Query_D IS NOT NULL GROUP BY Query_D, Query_T ORDER By Hits DESC |
| ||||||||||||||||||||||||
| Find the Slowest 25 URLs (in average) in the site | SELECT TOP 25 cs-uri-stem as URL, MAX(time-taken) As Max, MIN(time-taken) As Min, Avg(time-taken) As Average FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY URL ORDER By Average DESC |
| ||||||||||||||||||||||||
| List the count of each Status and Substatus code | SELECT TOP 25 STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) As Status, COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Status ORDER BY Status ASC |
| ||||||||||||||||||||||||
| List all the requests by user agent | SELECT cs(User-Agent) As UserAgent, COUNT(*) as Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY UserAgent ORDER BY Hits DESC |
| ||||||||||||||||||||||||
| List all the Win32 Error codes that have been logged | SELECT sc-win32-status As Win32-Status, WIN32_ERROR_DESCRIPTION(sc-win32-status) as Description, COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE Win32-Status<>0 GROUP BY Win32-Status ORDER BY Win32-Status ASC |
|
최근 덧글