Поиск по этому блогу

19.03.2015

Обработка CDR c Cisco ISR

Продолжение вот этой заметки.
Настройки маршрутизатора с присоединением к оператору связи по Е1 следующие:
gw-accounting file
primary ftp 999.99.9.1/ username cdr password cdr
maximum cdrflush-timer 15
На ftp-сервере, где разрешена запись пользователю cdr, по-умолчанию создается "скрытый" (ls -la) файл вида .имя_хоста_дата_время. Файл периодически дописывается, если есть о чем писать. Новый создается раз в сутки относительно времени настройки аккаунтинга.
VOIP-C2901(config-gw-accounting-file)#maximum fileclose-timer ?
<60-1440> in minutes, default is 1440min
Нет желания использовать компактный режим, так как в детализированном виде есть время соединения в секундах. Все поля разделены "," и идут ровно в том порядке, в котором они указаны в документации. Нужны поля, выделенные ниже в примере: unix_time, leg-type, username, clid, dnis, h323-setup-time, override-session-time. Поле leg-type имеет несколько значений и это удобно использовать, так как на каждую "ногу" создается отдельная запись. Интересует только стык с оператором - "1", а именно вторая запись о звонке с id "80CD1398 50739150 854F5801 AC117AEF".
1426682736,788957,0,2,"80CD1398 50739150 854F5801 AC117AEF","","","19:45:04.169 KRSK Wed Mar 18 2015","","19:45:11.829 KRSK Wed Mar 18 2015","19:45:36.849 KRSK Wed Mar 18 2015","","","answer",0,"",594,93528,1413,226080,"662","662","089830000000","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",25,"","","0","999.99.9.100","","","","","","","","","","","","","","","","","","","","","","ton:0,npi:0,#:089830000000","ton:0,npi:0,pi:0,si:1,#:662","","","","","","","","","","","","Unknown","","","cisco","","","TWC","03/18/2015 19:45:04.168","662","089830000000",0,700847,80CD1398 50739150 854F5801 AC117AEF,C09DD,"","","","","","","",""
1426682737,788958,0,1,"80CD1398 50739150 854F5801 AC117AEF","","","19:45:04.525 KRSK Wed Mar 18 2015","19:45:08.415 KRSK Wed Mar 18 2015","19:45:11.815 KRSK Wed Mar 18 2015","19:45:37.185 KRSK Wed Mar 18 2015","","","originate",0,"",1413,237384,598,94168,"662","3912000000","89830000000","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",25,"Tariff:Unknown","","0","","","","","","","","","","","","","0/1:1","","","","","","","","","","ton:0,npi:0,#:089830000000","ton:0,npi:0,pi:0,si:1,#:662","","","","ton:0,npi:0,#:89830000000","ton:0,npi:0,pi:0,si:1,#:3912000000","","","","","","","Unknown","","","","","","TWC","03/18/2015 19:45:04.172","3912000000","89830000000",0,700848,80CD1398 50739150 854F5801 AC117AEF,C09DE,"","","","","","","",""
Мне нужны данные только об исходящих в телефонную сеть оператора звонках, которые будут складываться в базу sqlite:
bash$ awk -F"," '{if ($4=='1' && $14~'originate' && $68>0 && $22~'3912000') {print "insert into cisco values ("$1","$21","$22","$23","$8","$68");"} }' имя_файла_cdr > OUT.sql
bash$ sqlite3 ciscocdr.db < OUT.sql
Пример работы с базой:
bash$ sqlite3 ciscocdr.db 
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema cisco
CREATE TABLE cisco ( unix_time integer, username , clid , dnis , setuptime , sessiontime integer );
sqlite> select * from cisco where sessiontime>300 and dnis like '8%' and setuptime like '%Mar 18%' order by sessiontime desc;