☰

여러 줄의 데이터베이스 로그를 한 줄로 변환

tag-icon • tag-icon text-processing
여러 줄의 데이터베이스 로그를 한 줄로 변환

Postgres 데이터베이스 로그 파일이 있고 여러 줄의 로그를 한 줄로 변환하고 싶습니다. 여러 줄 로그에는 생략해야 하는 캐리지 리턴, 줄 바꿈 및 후행 줄 탭이 있습니다. 내 연구에 따르면 타임스탬프를 기반으로 하거나 Tab을 피벗으로 사용하는 두 가지 방법으로 이 문제를 해결할 수 있습니다. 타임스탬프가 없는 각 줄에는 탭 ASCII 문자가 있습니다. 탭은 항상 시작 부분과 동일한 위치에 있으므로 로그 줄이 동일한 블록에서 온 것임을 이해하는 데 도움이 됩니다. 그렇지 않으면 새 타임스탬프가 있는 경우 로그 블록이 중단됩니다.

이것은 내 로그 파일입니다.

로그 파일:

2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement

    ;

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

           pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

           pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

         pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear

    clear

    select

    ;

예상되는 솔루션:

2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;
2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15
2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;
2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1
2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select;

나는 또한 다음을 시도했다철사 암호:

sed -e '$!N;/^\t.*\n/D' -e 'P;D' db_logs.txt > output.txt

그것은 나에게 다음과 같은 결과를 제공합니다.

22-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear

2022-11-03 17:08:01.281 PKT [5221] LOG:  statement: select

2022-11-03 17:11:31.760 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:11:52.884 PKT [5221] LOG:  statement: select

불행하게도 나머지 줄은 삭제됩니다. 나는 순수한 Linux 도구를 기반으로 한 솔루션을 원합니다. 외부 도구를 사용할 수 없어서 awk및 를 사용했습니다 sed.

답변1

POSIX awk를 사용하십시오.

$ awk -v ORS= '/^[^[:space:]]/{print sep; sep=RS} {sub(/^[[:space:]]+|[[:space:]]+$/," "); print} END{print sep}' file
2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;
2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15
2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;
2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement ;
2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1
2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select ;

또는 어떤 이유로든 데이터 행 사이에 빈 행을 원하는 경우:

$ awk -v ORS= '/^[^[:space:]]/{print sep; sep=RS RS} {sub(/^[[:space:]]+|[[:space:]]+$/," "); print} END{print sep}' file
2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement ;

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select ;

답변2

다음 스크립트를 사용하여 문제를 해결할 수 있었습니다.

awk -v ORS="" '                 
    /^\r?$/ {next}              
    f && !/^\t/ {print "\n"}     
    {
        sub(/\r$/, " ")          
        print                   
        if ($0 ~ /\r/) f = 0    
        else f = 1             
    }
    END {
        print "\n"              
    }
' db_logs.txt > output.txt 
awk 'BEGIN{FS="\t+"; OFS="\t"} {$1=$1; print}' output.txt > out.txt
awk '{ gsub(/ /," "); print }' out.txt > oyt.txt 
awk '{ gsub(/^[ \t]+|[ \t]+$/, ""); print }' oyt.txt > new.txt

관련 정보