안녕하세요.
다름이 아니라 SQL문 자체만으로 특정 문자열 안에 있는 문자열을 얻을 수 있는지 궁금해서
질문을 올리게 되었습니다.
먼저 주어진 문자열은 아래와 같이 2가지가 있습니다.
test: User [test] logged in from [192.168.1.200] via [DSM].
Event: download, Path: /home/background_61802498.pptx, File/Folder: File, Size: 1.62 MB, User: admin, IP: 192.168.1.200
2가지 문자열이 주어졌는데요.
각 문자열에서 Select 문으로 뽑아내고자 하는 것은 계정명과 IP주소입니다.
첫 번째 문장의 경우 test, 192.168.1.200을 Select 결과로 얻고 싶고,
두 번째 문장의 경우도 마찬가지로 admin, 192.168.1.200을 Select 문을 통해 얻고자 합니다.
사실 정규표현식으로 뽑아낼까 하다가 SQL Select 쿼리로 처리가 가능하면 그렇게 하는 편이 효과적일 것 같아서
정보를 검색하고 있었는데 제가 DB SQL 쿼리쪽을 잘 몰라서 급하게 이 곳에 질문을 드리게 되었습니다.
DBA님들의 많은 관심과 조언 부탁드리겠습니다.
감사합니다.
문자열이 항상 저런 형태인가요? 단순히 저런 형태에서 user 값과 ip 값만 바뀌는
거라면 간단히
with t as ( select 'test: User [test] logged in from [192.168.1.200] via [DSM].' txt from dual ) select regexp_replace(txt,'(.*)User \[(.*)\] logged(.*)','\2') from t union all select regexp_replace(txt,'(.*)from \[(.*)\] via(.*)','\2') from t with t as ( select 'Event: download, Path: /home/background_61802498.pptx, File/Folder: File, Size: 1.62 MB, User: admin, IP: 192.168.1.200' txt from dual ) select regexp_replace(txt,'(.*)User: (.*), IP: (.*)','\2') from t union all select regexp_replace(txt,'(.*)User: (.*), IP: (.*)','\3') from t
이렇게 해보셔도 될 것 같은데요.
jkson님 답변주셔서 감사합니다.
제가 질문을 좀 잘못 올린거 같아서요.
원본 DB의 table명과 앞서 말씀드린 문자열을 데이터로 값는 컬럼(필드)의 이름이 다음과 같습니다.
Table: logs / Field name: msg
그래서 위에서 예로 든 문자열은 select msg from logs를 한 결과 값입니다.
select 결과 중 예시로 첫번째와 두번째 튜플 값을 예제로 질문 글을 작성했었습니다.
그래서 알려주신 SQL문으로 적용이 잘 안되어서 재차 질문드리게 되었습니다.
txt 부분과 문자열 부분을 수정해야하는데 어떻게 수정을 해야할지 잘 모르겠습니다.
제가 처음부터 질문 글을 제대로 작성해야했는데 죄송하네요.
다시 한 번만 확인해주시고, 도와주시면 감사하겠습니다.
감사합니다.
select
regexp_replace(txt,
'(.*)User \[(.*)\] logged(.*)'
,
'\2'
)
from
t
union
all
select
regexp_replace(txt,
'(.*)from \[(.*)\] via(.*)'
,
'\2'
)
from
t
select regexp_replace(msg,'(.*)User \[(.*)\] logged(.*)','\2') from logs union all select regexp_replace(msg,'(.*)from \[(.*)\] via(.*)','\2') from logs
WITH logs AS ( SELECT 'test: User [test] logged in from [192.168.1.200] via [DSM].' msg FROM dual UNION ALL SELECT 'Event: download, Path: /home/background_61802498.pptx, File/Folder: File, Size: 1.62 MB, User: admin, IP: 192.168.1.200' FROM dual ) -- 정규식 안쓰고 풀어 봤습니다. -- SELECT CASE gb WHEN 1 THEN SUBSTR(msg, x+6, y-x-8) WHEN 2 THEN SUBSTR(msg, a+6, b-a-8) END AS usr , CASE gb WHEN 1 THEN SUBSTR(msg, y+16, z-y-16) WHEN 2 THEN SUBSTR(msg, b+4) END AS ip FROM (SELECT msg , CASE WHEN msg LIKE '%User [%] logged in from [%] via%' THEN 1 WHEN msg LIKE '%User: %, IP: %' THEN 2 END AS gb , INSTR(msg, 'User [') x , INSTR(msg, 'logged in from [') y , INSTR(msg, '] via') z , INSTR(msg, 'User: ') a , INSTR(msg, 'IP: ') b FROM logs ) ;
마농님께.
먼저 다시 한 번 저의 감정적인 언행에 송구스럽고 죄송하다는 말씀드립니다.
알려주신 SQL문을 파이썬 코드에 넣어 실행시켜 보았습니다.
결과는 제가 너무 원했던 대로 나왔습니다.
if __name__ == "__main__": conn = sqlite3.connect("2016-06-22_2016-07-13.DB") c = conn.cursor() c.execute("""SELECT CASE gb WHEN 1 THEN SUBSTR(msg, x+6, y-x-8) WHEN 2 THEN SUBSTR(msg, a+6, b-a-8) END AS usr , CASE gb WHEN 1 THEN SUBSTR(msg, y+16, z-y-16) WHEN 2 THEN SUBSTR(msg, b+4) END AS ip FROM (SELECT msg , CASE WHEN msg LIKE '%User [%] logged in from [%] via%' THEN 1 WHEN msg LIKE '%User: %, IP: %' THEN 2 END AS gb , INSTR(msg, 'User [') x , INSTR(msg, 'logged in from [') y , INSTR(msg, '] via') z , INSTR(msg, 'User: ') a , INSTR(msg, 'IP: ') b FROM logs ) ;""")
결과 중 일부 값을 뽑아오면 아래와 같이 출력 됩니다.
(u'admin', u'192.168.1.200')
뭐라고 감사의 말씀을 전해야할지 모르겠습니다.
이제 뽑아온 해당 값을 SQLite Insert 문으로 새로운 table에 저장만 하면 될 것 같습니다.
진심으로 감사드리고, 사실 알려주신 구문을 이해해보려고 했는데 이해하기는 쉽지 않았습니다.
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) END AS usr , CASE gb WHEN 1 THEN SUBSTR(msg, y+16, z-y-16) WHEN 2 THEN SUBSTR(msg, b+4) 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 END AS gb , INSTR(msg, 'User [') x , INSTR(msg, 'logged in from [') y , INSTR(msg, '] via') z , INSTR(msg, 'User: ') a , INSTR(msg, 'IP: ') b FROM logs ) ORDER BY ldate, ltime, msg -- 요기 ;
감사합니다 정말 감사합니다!
아 그리고.. 정말 마지막으로 추가 질문드립니다.....
SYSTEM: User [tt7899] failed to log in from [192.168.1.53] via [DSM].
위의 형식 처럼 failed to log in from 이 포함된 문자열도 같이 CASE 에 넣어보려고 아래와 같이 설정을 해보았는데 정상적으로 출력되질 않아서 질문드리게 되었습니다. 제가 해본 SQL 쿼리는 다음과 같습니다.
SELECT 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) 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, x+6, z-w-31) END AS ip FROM (SELECT 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 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 FROM logs )
뺄셈하는 숫자의 의미를 제가 잘 몰라서 번거롭게 질문 드리지 않고 해결해보려고 했는데 2시간 째 헤매다가 다시 질문드리게 되었습니다. 문자의 갯수를 의미하는거 같은데 정확히 어떤 원리로 뺄셈은 하는것인지는 이해를 못했습니다...
문자 w로 표시한 곳이 제가 수정한 부분입니다! THEN 3으로 넣어놨는데.
다시 한번 도움 부탁드립니다.
감사합니다.
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) 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) 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 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 FROM logs ) ORDER BY ldate, ltime, msg ;
감사합니다.
SUBSTR(), INSTR()가 어떤 용도로 사용되는지 조사를 해봤습니다.
특히 SUBSTR()의 경우 2번째 인자가 문자열을 잘라내기 위한 시작 위치를 나타내고, 3번째 인자는 시작위치로부터 몇개의 문자를 잘라낼 것인지 정하는 것인지는 이해했습니다.
그런데 마농님께서 답변주신 것을 보면 두번째 인자와 세번째 인자에 각각 INSTR()에서 정의한 문자열 길이만큼 더하고, 빼는 과정을 거치는데 사실 제가 이 부분을 아직 이해를 못했습니다.
더 이상 여쭤보는 것도 죄송하고, 다른 문자열이 생겼을 때 제가 스스로 검색할 수 있도록 응용해보려고 하는데 이 부분에 대해서 설명 부탁드려도 될까요?