데이터베이스 질문과 답변
안녕하세요 마농님.
7월경 올린 질문에 대한 연장 질문인데 당시 마농님께서 답변을 잘해주셔서 문제를 해결했기에
부득이하게 마농님께 재차 도움을 구하게 되었습니다.
이전에 제가 질문 드린 내용은 http://www.gurubee.net/article/67761#comment_134219 내용인데요.
문자열 내에 포함된 특정 문자열을 뽑아내는 SQL 쿼리문을 질문드려서 마농님께서 답변을 해주셨었습니다..
문제는 마농님께서 알려주신 것을 수정하여 추가적인 문자열을 처리하려고 하는데 계산방법이 틀렸는지
처리가 잘 안되는 문자열이 있어서 문의 드리게 되었습니다.
처리하고자 하는 문자열은 다음과 같습니다.
Event: download, Path: /home/background_61802498.pptx, File/Folder: File, Size: 1.62 MB, User: admin, IP: 192.168.1.200
Event: delete, Path: /backup/test2.txt, File/Folder: File, Size: 0 Bytes, User: admin, IP: 192.168.0.10
Event: upload, Path: /home/dschoi08, File/Folder: File, Size: 1.2 Bytes, User: admin, IP: 192.168.0.10
Event: mkdir, Path: /home/test, File/Folder: Folder, Size: NA, User: admin, IP: 192.168.0.10
첫번째 문장을 예로 문장 내에서 뽑아내고 싶은 특정 문자열은 Event, Path, File Format 종류, File/Folder, Size의 값 입니다. User, IP 뽑는 방법은 이미 사전에 알려주셨었구요.
File format은 .exe .pptx .dll 과 같이 파일확장명만 따로 뽑아내고 싶은데...
그런데 Event 값을 보면 문자열 길이(mkdir: 5자, download: 8자)가 각각 달라져서
SUBSTR() 사용 시 시작점과 범위 계산을 어떻게 해줘야할지 막막하여 질문드리게 되었습니다.
SELECT ldate,
ltime,
msg,
CASE gb WHEN 1 THEN SUBSTR(msg, x+6, y-x-8)
WHEN 2 THEN SUBSTR(msg, a+6, b-a-8)
WHEN 3 THEN SUBSTR(msg, x+6, w-x-8)
WHEN 4 THEN SUBSTR(msg, c+7, w-x-8)
END AS usr,
CASE gb WHEN 1 THEN SUBSTR(msg, y+16, z-y-16)
WHEN 2 THEN SUBSTR(msg, b+4)
WHEN 3 THEN SUBSTR(msg, w+23, z-w-23)
WHEN 4 THEN SUBSTR(msg, , )
END AS ip
FROM (SELECT ldate,
ltime,
msg,
CASE WHEN msg LIKE '%User [%] logged in from [%] via%' THEN 1
WHEN msg LIKE '%User: %, IP: %' THEN 2
WHEN msg LIKE '%User [%] failed to log in from [%] via%' THEN 3
WHEN msg LIKE '%Event: %, Path: %, File/Folder: %, Size: %, User: %, IP: %' THEN 4
END AS gb
, INSTR(msg, 'User [') x
, INSTR(msg, 'logged in from [') y
, INSTR(msg, 'failed to log in from [') w
, INSTR(msg, '] via') z
, INSTR(msg, 'User: ') a
, INSTR(msg, 'IP: ') b
, INSTR(msg, 'Event: ') c
, INSTR(msg, ', Path: ') d
FROM logs
)
ORDER BY ldate, ltime, msg
WITH logs AS
(
SELECT 1 ldate, 1 ltime, 'Event: download, Path: /home/background_61802498.pptx, File/Folder: File, Size: 1.62 MB, User: admin, IP: 192.168.1.200' msg FROM dual
UNION ALL SELECT 1, 2, 'Event: delete, Path: /backup/test2.txt, File/Folder: File, Size: 0 Bytes, User: admin, IP: 192.168.0.10' FROM dual
UNION ALL SELECT 1, 3, 'Event: upload, Path: /home/dschoi08, File/Folder: File, Size: 1.2 Bytes, User: admin, IP: 192.168.0.10' FROM dual
UNION ALL SELECT 1, 4, 'Event: mkdir, Path: /home/test, File/Folder: Folder, Size: NA, User: admin, IP: 192.168.0.10' FROM dual
UNION ALL SELECT 1, 5, 'test: User [test] logged in from [192.168.1.200] via [DSM].' FROM dual
UNION ALL SELECT 1, 6, 'test: User [test] failed to log in from [192.168.1.200] via [DSM].' FROM dual
)
SELECT ldate
, ltime
, msg
, "Event"
, "Path"
, SUBSTR("Path", INSTR("Path"||'.', '.')+1) "Format"
, "File/Folder"
, "Size"
, "User"
, "IP"
FROM (SELECT ldate
, ltime
, msg
, SUBSTR(msg, (a+ 7), b-(a+ 7)-2) "Event"
, SUBSTR(msg, (b+ 6), c-(b+ 6)-2) "Path"
, SUBSTR(msg, (c+13), d-(c+13)-2) "File/Folder"
, SUBSTR(msg, (d+ 6), e-(d+ 6)-2) "Size"
, CASE gb WHEN 1 THEN SUBSTR(msg, (e+6), f-(e+6)-2)
WHEN 2 THEN SUBSTR(msg, (x+6), y-(x+6))
END "User"
, CASE gb WHEN 1 THEN SUBSTR(msg, (f+4))
WHEN 2 THEN SUBSTR(msg, (v+9), w-(v+9))
END "IP"
FROM (SELECT ldate
, ltime
, msg
, INSTR(msg, 'Event:' ) a
, INSTR(msg, 'Path:' ) b
, INSTR(msg, 'File/Folder:') c
, INSTR(msg, 'Size:' ) d
, INSTR(msg, 'User:' ) e
, INSTR(msg, 'IP:' ) f
, INSTR(msg, 'User [' ) x
, INSTR(msg, ']' ) y
, INSTR(msg, 'in from [' ) v
, INSTR(msg, '] via' ) w
, CASE WHEN msg LIKE 'Event: %' THEN 1
WHEN msg LIKE '%User [%] % in from [%] via%' THEN 2
END AS gb
FROM logs
)
)
ORDER BY ldate, ltime, msg
;
여러가지 유형이 있고, 각 유형마다 일정한 패턴을 갖고 있다면 패턴을 DB 로 관리한다면 향후 유지보수도
좀더 수월해 지지 않을까 하네요 이하는 샘플 코드 입니다.
WITH LOGS AS
(
SELECT 1 LDATE, 1 LTIME, 'Event: download, Path: /home/background_61802498.pptx, File/Folder: File, Size: 1.62 MB, User: admin, IP: 192.168.1.200' MSG FROM DUAL
UNION ALL SELECT 1, 2, 'Event: delete, Path: /backup/test2.txt, File/Folder: File, Size: 0 Bytes, User: admin, IP: 192.168.0.10' FROM DUAL
UNION ALL SELECT 1, 3, 'Event: upload, Path: /home/dschoi08, File/Folder: File, Size: 1.2 Bytes, User: admin, IP: 192.168.0.10' FROM DUAL
UNION ALL SELECT 1, 4, 'Event: mkdir, Path: /home/test, File/Folder: Folder, Size: NA, User: admin, IP: 192.168.0.10' FROM DUAL
UNION ALL SELECT 1, 5, 'test: User [test] logged in from [192.168.1.200] via [DSM].' FROM DUAL
UNION ALL SELECT 1, 6, 'test: User [test] failed to log in from [192.168.1.200] via [DSM].' FROM DUAL
), PATTERN(CODE, SEQ, PTN) AS (
SELECT 'EVENT', 1, '^Event:[^,]+' FROM DUAL UNION ALL
SELECT 'EVENT', 2, 'Path:[^,]+' FROM DUAL UNION ALL
SELECT 'EVENT', 3, 'File/Folder:[^,]+' FROM DUAL UNION ALL
SELECT 'EVENT', 4, 'Size:[^,]+' FROM DUAL UNION ALL
SELECT 'EVENT', 5, 'User:[^,]+' FROM DUAL UNION ALL
SELECT 'EVENT', 6, 'IP:[^,]+' FROM DUAL UNION ALL
SELECT 'TEST', 1, ' \[[^\.]+\]' FROM DUAL UNION ALL
SELECT 'TEST', 2, '[0-9]{1,}.[0-9]{1,}.[0-9]{1,}.[0-9]{1,}' FROM DUAL
)
SELECT
MSG,
REGEXP_SUBSTR(MSG, PTN, 1, 1) TXT
FROM LOGS A INNER JOIN PATTERN B
ON ( B.CODE = UPPER(SUBSTR(MSG, 1, 5)) OR B.CODE = UPPER(SUBSTR(MSG, 1, 4)) )
ORDER BY LDATE, LTIME, SEQ
) 버튼을 클릭하여 작성 하시면 됩니다.