Конвертировать лог в базу даних SQL

[CaHi33]

Добрый день,

задача ковертировать записи лога в базу даних SQL и из базы даних рисовать график

Для примера, возьмем лог:

Лог состоит из строк. Строки лога снабжены временной меткой и каким-то сообщением.

(Нажмите, чтобы показать/скрыть)

[11:48:50.484] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:6b58d569
из ряда створювати следующу таблицю:
|      Date       |        Time         | InData | Type |     Login     |       ServerName      |       ChanelID            | ecmTime |
|2020-08-04 | 11:48:50.484 |  False  | ecm  | pal-ko16-2 |                                    | 0d97:000000:7726 |       -10      |

[11:48:50.490] -> ecm to Newcamd server1 (192.168.1.31:6048) ch 0d97:000000:7726:6b58d569
|      Date       |        Time         | InData | Type |     Login     |       ServerName      |        ChanelID            | ecmTime |
|2020-08-04 | 11:48:50.490 |   False | ecm  |                     |  192.168.1.31:6048 | 0d97:000000:7726 |        -10       |

[11:48:50.634] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (150ms)
|      Date       |        Time         | InData | Type |     Login      |       ServerName      |        ChanelID           | ecmTime |
|2020-08-04 | 11:48:50.634 |    True  |   cw   | pal-ko16-2 |                                    | 0d97:000000:7726 |   150ms    |

[11:48:50.685] <= cw from server (192.168.1.31:6048) ch 0d97:000000:7726 (198ms)
|      Date       |        Time         | InData | Type |     Login     |       ServerName      |        ChanelID            | ecmTime |
|2020-08-04 | 11:48:50.685 |   True    |  cw   |                     |  192.168.1.31:6048 | 0d97:000000:7726 |    198ms   |

итд.
[11:49:00.494] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:88c03517
[11:49:00.631] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (137ms)
[11:49:10.488] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:f2fd5015
[11:49:10.636] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (148ms)
[11:49:20.492] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:33c75639
[11:49:20.494] -> ecm to Newcamd server1 (192.168.1.31:6048) ch 0d97:000000:7726:33c75639
[11:49:20.601] -> ecm to Newcamd server2 (192.168.1.55:18400) ch 0d97:000000:7726:33c75639
[11:49:20.630] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (138ms)
[11:49:20.711] <= cw from server (192.168.1.31:6048) ch 0d97:000000:7726 (218ms)
[11:49:20.801] <= cw from server (192.168.1.55:18400) ch 0d97:000000:7726 (201ms)
[11:49:30.490] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:3cf41fd1
[11:49:30.631] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (141ms)
[11:49:40.499] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:2bb75843
[11:49:40.644] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (145ms)
[11:49:50.488] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:fda326b1
[11:49:50.494] -> ecm to Newcamd server1 (192.168.1.31:6048) ch 0d97:000000:7726:fda326b1
[11:49:50.649] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (161ms)
[11:49:50.689] <= cw from server (192.168.1.31:6048) ch 0d97:000000:7726 (196ms)
[11:50:00.494] <- ecm from client 'pal-ko16-2' ch 0d97:000000:7726:4d3e4771
[11:50:00.635] => cw to client 'pal-ko16-2' ch 0d97:000000:7726 (141ms)
[11:52:18.907] <- ecm from CCcam client 'marius' ch 0b02:000000:7726:f3e1d380
[11:52:19.214] -> ecm to Newcamd server1 (192.168.1.31:6032) ch 0b02:000000:7726:f3e1d380
[11:52:19.607] -> ecm to Newcamd server2 (192.168.0.12:1992) ch 0b02:000000:7726:f3e1d380
[11:52:19.615] <= cw from server (192.168.1.31:6032) ch 0b02:000000:7726 (404ms)
[11:52:19.615] => cw to CCcam client 'marius' ch 0b02:000000:7726 (708ms)
[11:52:20.972] <= cw from server (192.168.0.12:1992) ch 0b02:000000:7726 (1368ms)
[11:52:22.538] <- ecm from CCcam client 'lorin' ch 0b02:000000:7726:f3e1d380*
[11:52:22.538] => cw to CCcam client 'lorin' ch 0b02:000000:7726 (0ms)
[11:54:40.765] <- ecm from mgcamd client 'jose' ch 0b02:000000:7726:5ac046a8
[11:54:41.101] -> ecm to Newcamd server1 (192.168.1.31:6032) ch 0b02:000000:7726:5ac046a8
[11:54:41.141] <= cw from server (192.168.1.31:6032) ch 0b02:000000:7726 (41ms)
[11:54:41.141] => cw to mgcamd client 'jose' ch 0b02:000000:7726 (376ms)
[11:54:42.794] <- ecm from CCcam client 'lorin' ch 0b02:000000:7726:5ac046a8*
[11:54:42.794] => cw to CCcam client 'lorin' ch 0b02:000000:7726 (0ms)
[11:54:44.084] <- ecm from client 'd3z2' ch 0b02:000000:7726:54c3e668
[11:54:44.391] -> ecm to Newcamd server1 (192.168.1.31:6032) ch 0b02:000000:7726:54c3e668
[11:54:44.542] -> ecm to Newcamd server2 (192.168.1.61:20000) ch 0b02:000000:7726:54c3e668
[11:54:46.008] <= cw from server (192.168.1.31:6032) ch 0b02:000000:7726 (1621ms)
[11:54:46.008] => cw to client 'd3z2' ch 0b02:000000:7726 (1924ms)
[11:54:47.087] <| decode failed from server (192.168.1.61:20000) ch 0b02:000000:7726 (2546ms)
[11:56:15.928] <- ecm from mgcamd client 'stareb' ch 0b02:000000:7726:227f3584
[11:56:16.234] -> ecm to Newcamd server1 (192.168.1.31:6032) ch 0b02:000000:7726:227f3584
[11:56:16.309] <= cw from server (192.168.1.31:6032) ch 0b02:000000:7726 (78ms)
[11:56:16.312] => cw to mgcamd client 'stareb' ch 0b02:000000:7726 (384ms)
[11:56:17.773] <- ecm from client 'd3z2' ch 0b02:000000:7726:227f3584*
[11:56:17.773] => cw to client 'd3z2' ch 0b02:000000:7726 (0ms)
[11:56:59.771] <- ecm from client 'general' ch 0b02:000000:7726:f3e1d380
[11:57:00.118] -> ecm to CCcam server1 (192.168.0.2:15014) ch 0b02:000000:7726:f3e1d380
[11:57:01.826] -> ecm to CCcam server2 (192.168.2.19:11000) ch 0b02:000000:7726:f3e1d380
[11:57:02.168] <- ecm from client 'd3z2' ch 0b02:000000:7726:f3e1d380*
[11:57:02.272] |> decode failed to client 'general' ch 0b02:000000:7726 (2501ms)
[11:57:02.272] |> decode failed to client 'd3z2' ch 0b02:000000:7726 (104ms)
[11:57:02.290] <|> decode failed to client 'd3z2' ch 0b02:000000:7726, already failed
[11:57:02.303] <|> decode failed to client 'd3z2' ch 0b02:000000:7726, already failed
[11:57:02.314] <|> decode failed to client 'd3z2' ch 0b02:000000:7726, already failed
[11:57:02.326] <|> decode failed to client 'general' ch 0b02:000000:7726, already failed
[11:57:03.198] <| decode failed from CCcam server (192.168.0.2:15014) ch 0b02:000000:7726 (3080ms)
[11:57:07.385] <| decode failed from CCcam server (192.168.2.19:11000) ch 0b02:000000:7726 (5560ms)
[11:58:19.751] <- ecm from client 'general' ch 0b02:000000:7726:41eb7e61
[11:58:20.615] -> ecm to CCcam server1 (192.168.2.35:12000) ch 0b02:000000:7726:41eb7e61
[11:58:20.998] <- ecm from CCcam client 'becze' ch 0b02:000000:7726:41eb7e61*
[11:58:22.254] |> decode failed to client 'general' ch 0b02:000000:7726 (2503ms)
[11:58:22.254] |> decode failed to CCcam client 'becze' ch 0b02:000000:7726 (1256ms)
[11:58:22.414] <= cw from CCcam server (192.168.2.35:12000) ch 0b02:000000:7726 (1799ms)
[11:59:41.278] <- ecm from client 'd3z2' ch 0b02:000000:7726:170c76a5
[11:59:41.582] -> ecm to Newcamd server1 (192.168.1.31:6032) ch 0b02:000000:7726:170c76a5
[11:59:42.341] -> ecm to CCcam server2 (192.168.2.19:11000) ch 0b02:000000:7726:170c76a5
[11:59:43.779] |> decode failed to client 'd3z2' ch 0b02:000000:7726 (2501ms)
[11:59:45.152] <| decode failed from server (192.168.1.31:6032) ch 0b02:000000:7726 (3572ms)
[11:59:46.179] <|> decode failed to client 'general' ch 0b02:000000:7726, already failed
[11:59:46.800] <| decode failed from CCcam server (192.168.2.19:11000) ch 0b02:000000:7726 (4459ms)
[12:00:36.944] <- ecm from mgcamd client 'd3alapSrv' ch 1880:000000:7726:a61f0ff1
[12:00:37.300] -> ecm to Newcamd server1 (192.168.2.5:46330) ch 1880:000000:7726:a61f0ff1
[12:00:38.520] -> ecm to Newcamd server2 (192.168.1.31:6040) ch 1880:000000:7726:a61f0ff1
[12:00:38.607] <= cw from server (192.168.1.31:6040) ch 1880:000000:7726 (88ms)
[12:00:38.607] => cw to mgcamd client 'd3alapSrv' ch 1880:000000:7726 (1663ms)
[12:00:39.846] <| decode failed from server (192.168.2.5:46330) ch 1880:000000:7726 (2547ms)
[12:01:01.450] <- ecm from mgcamd client 'koler' ch 0b02:000000:7726:227f3584
[12:01:01.756] -> ecm to Newcamd server1 (192.168.1.31:6032) ch 0b02:000000:7726:227f3584
[12:01:01.796] <= cw from server (192.168.1.31:6032) ch 0b02:000000:7726 (43ms)
[12:01:01.797] => cw to mgcamd client 'koler' ch 0b02:000000:7726 (347ms)
[12:02:10.668] <- ecm from mgcamd client 'jose' ch 1815:000000:7726:f40a1287
[12:02:10.769] -> ecm to CCcam server1 (172.16.1.20:8803) ch 1815:000000:7726:f40a1287
[12:02:11.018] -> ecm to CCcam server2 (172.31.20.71:8888) ch 1815:000000:7726:f40a1287
[12:02:11.068] => cw to mgcamd client 'jose' ch 1815:000000:7726 (400ms)
[12:02:43.926] <- ecm from CCcam client 'mcsuser_0727' ch 1880:000000:7726:40c24183
[12:02:45.207] -> ecm to Newcamd server1 (192.168.1.47:47058) ch 1880:000000:7726:40c24183
[12:02:45.883] <= cw from server (192.168.1.47:47058) ch 1880:000000:7726 (677ms)
[12:02:45.883] => cw to CCcam client 'mcsuser_0727' ch 1880:000000:7726 (1957ms)
[12:03:05.300] <- ecm from CCcam client 'lorin' ch 1802:000000:7726:8b145ba8
[12:03:05.405] -> ecm to CCcam server1 (192.168.2.35:12000) ch 1802:000000:7726:8b145ba8
[12:03:05.469] <| decode failed from CCcam server (192.168.2.35:12000) ch 1802:000000:7726 (64ms)
[12:03:05.469] (RE1) -> ecm to CCcam server (192.168.2.35:12000) ch 1802:000000:7726
[12:03:05.600] -> ecm to CCcam server2 (192.168.2.19:11000) ch 1802:000000:7726:8b145ba8
[12:03:05.663] <| decode failed from CCcam server (192.168.2.35:12000) ch 1802:000000:7726 (194ms)
[12:03:05.663] (RE2) -> ecm to CCcam server (192.168.2.35:12000) ch 1802:000000:7726
[12:03:05.704] -> ecm to CCcam server3 (172.16.1.20:8803) ch 1802:000000:7726:8b145ba8
[12:03:05.817] -> ecm to Newcamd server4 (192.168.0.58:30039) ch 1802:000000:7726:8b145ba8
[12:03:05.847] <| decode failed from CCcam server (192.168.2.35:12000) ch 1802:000000:7726 (184ms)
[12:03:05.847] (RE3) -> ecm to CCcam server (192.168.2.35:12000) ch 1802:000000:7726
[12:03:05.999] <= cw from CCcam server (192.168.2.19:11000) ch 1802:000000:7726 (400ms)
[12:03:05.999] => cw to CCcam client 'lorin' ch 1802:000000:7726 (699ms)
[12:03:06.033] <| decode failed from CCcam server (192.168.2.35:12000) ch 1802:000000:7726 (186ms)
[12:03:06.238] <= cw from server (192.168.0.58:30039) ch 1802:000000:7726 (422ms)
[12:03:21.081] <- ecm from CCcam client 'roni03' ch 1802:000000:7726:9fa91e30
[12:03:21.083] => cw to CCcam client 'roni03' ch 1802:000000:7726 (2ms)
[12:04:29.027] <- ecm from client 'd3z2' ch 0b02:000000:7726:f6466df7
[12:04:29.027] => cw to client 'd3z2' ch 0b02:000000:7726 (0ms)
[12:04:39.261] <- ecm from mgcamd client 'stareb' ch 0b02:000000:7726:d0b2f0b5
[12:04:39.598] -> ecm to Newcamd server1 (192.168.1.61:20000) ch 0b02:000000:7726:d0b2f0b5
[12:04:39.748] -> ecm to CCcam server2 (192.168.0.2:15014) ch 0b02:000000:7726:d0b2f0b5
[12:04:39.850] -> ecm to Newcamd server3 (192.168.1.31:6032) ch 0b02:000000:7726:d0b2f0b5
[12:04:41.004] => cw to mgcamd client 'stareb' ch 0b02:000000:7726 (1743ms)
[12:04:41.037] <= cw from server (192.168.1.61:20000) ch 0b02:000000:7726 (1440ms)
[12:04:41.044] <= cw from server (192.168.1.31:6032) ch 0b02:000000:7726 (1195ms)
[12:06:41.127] <- ecm from mgcamd client 'jose' ch 1815:000000:7726:5a432d99
[12:06:41.275] -> ecm to Newcamd server1 (192.168.0.3:33000) ch 1815:000000:7726:5a432d99
[12:06:41.398] => cw to mgcamd client 'jose' ch 1815:000000:7726 (271ms)
[12:06:41.455] <= cw from server (192.168.0.3:33000) ch 1815:000000:7726 (184ms)
[12:07:20.878] <- ecm from mgcamd client 'jose' ch 1815:000000:7726:53527955
[12:07:20.882] -> ecm to CCcam server1 (172.16.1.20:8803) ch 1815:000000:7726:53527955
[12:07:21.202] => cw to mgcamd client 'jose' ch 1815:000000:7726 (324ms)
[12:07:30.291] <- ecm from client 'd3z2' ch 1880:000000:7726:d0486167
[12:07:30.292] => cw to client 'd3z2' ch 1880:000000:7726 (1ms)
[12:07:34.715] <- ecm from client 'general' ch 0b02:000000:7726:4c9a9e59
[12:07:37.218] |> decode failed to client 'general' ch 0b02:000000:7726 (2503ms)
[12:08:01.062] <- ecm from CCcam client 'roni03' ch 1802:000000:7726:418cbd79
[12:08:01.068] -> ecm to CCcam server1 (172.16.1.20:8803) ch 1802:000000:7726:418cbd79
[12:08:01.071] => cw to CCcam client 'roni03' ch 1802:000000:7726 (9ms)

Кто-нибудь может мне с этим помочь?

[ALiEN175]

Ubuntu тут каким боком? Вам на биржу фрилансеров.

[CaHi33]

Я намеревался выполнить это преобразование используя скрипт оболочки через командной строк.
Лог генерируется сервером, работающим под Ubuntu.
я так думал, что могу спросить здесь...

[SergeyIT]

CaHi33, преобразуй свой текст в csv файл и закидывай в любую БД.
 https://ru.wikipedia.org/wiki/CSV

[CaHi33]

CaHi33, преобразуй свой текст в csv файл и закидывай в любую БД.
 https://ru.wikipedia.org/wiki/CSV

С помощью утилиты awk НЕ получается непосредственно загнать в .db?

[SergeyIT]

CaHi33, в БД надо загонять с помощью утилит данной БД

[jura12]

а excel импорт поможет?

[zg_nico]

jura12, а каким образом? ТС не таблицу хочет получить, а какой-то файл .db неведомой структуры. Excel, насколько я знаю, в *.db сохранять не умеет. Если б csv или xml - можно было б поковырять, а поставленная ТС задача в ее текущей формулировке, ИМХО, вообще нерешаема. Согласен с ALiEN175, это скорее к фрилансерам ("написать скрипт на питоне, на выходе которого получается файл db по такой-то спецификации")

[CaHi33]

а excel импорт поможет?

Нет, нехочу усложнять с excel-ем.
Я хочу просто:
- направить лог-поток card-сервера в awk,
- на awk преобразовать строку в формат таблицы,
- вставить эту таблицу в базу данных с помощью mysql INSERT.

Идея така что реально получается - увидим!

[SergeyIT]

Я хочу просто...

А что не получается?
csv файл можно так преобразовать http://chriseiffel.com/uncategorized/how-to-import-a-large-csv-file-to-mysql/
sql файл тоже можно...
Только за вас никто этого делать не будет.

[digger]

Проще такой лог парсить программкой на любом доступном языке и из нее же сразу вставлять записи в базу.