Redash를 이용한 전화송수신 프로그램 대체하기

Redash 화면(참고)

회사에 구식 유선전화 송수신 내역을 저장하는 서버가 한대 있습니다.

서버라고 쓰긴하지만 그냥 윈도우에다가 프로그램을 설치하고 해당 프로그램이 전화서버(이하 전화교환기)에서

발생하는 로그를 수집해서 보여주고 추가로 요금계산도 해주는 그런 프로그램이었습니다.

 

해당 프로그램이 10년이상 되다보니 윈도우7에서만 실행되서 해당 PC를 계속 유지보수 하고(라고적고 방치)

운영했으나 가끔 해당 프로그램이 자동으로 종료되어 교환기의 로그 수집을 제대로 하지 못하는 문제점이 있더군요.

그런 경우 전화 통화내역이나 몇건이나 송수신을 했는지 간혹 사업부에서 문의가 올 경우 확인이 안되는 경우가 

왕왕 생기곤 했습니다.

 

이전에 FTP에서 자료를 받아서 Redash로 작업을 하였던 내용을 기록했던 아래 링크를 참고하시어 바뀐 부분만 기술해

보도록 하겠습니다.

링크 : https://openature.com/708

FTP에서 데이터 받아서 DB입력후 Redash로 보여주기

 

이전 작업과 이번 부분에서 가장 다른부분은 바로 데이터를 받는 부분과 DB입력을 진행하는 LOAD DATA LOCAL

INFILE 부분 입니다.

 

업체에서 해당 전화 내역을 저장하는 프로그램에 대한 유지보수와 데이터 받아오는 방법에 대해서 문의하니

1. 해당 프로그램은 오래되서 유지보수가 안되고 새로 다른 프로그램을 구매시 400만원 입니다. 

2. 해당 프로그램은 telnet ip:5001 를 통해서 데이터를 받아옵니다(실시간 통화내역이 올라옴)

라고 답변이 옵니다. 예산도 잡아두지 않았고 IP전화로 바뀔수도 있으니 방법이 없습니다.

뭐 그럼 일단 해당 전화교환기에 Telnet으로 접속해서 뭐가 어떻게 나오는지 한번 봅니다.

Telnet 접속화면

해당 전화교환기에 telnet으로 접속하니 위와같이 전화번호들과 여러 정보들이 막 올라옵니다. 먼가 정신없습니다.

우리가 교체할 프로그램은 저 정보를 다 받아서 저장하고 처리하는듯 합니다.(실시간으로)

저는 이걸 DB에 실시간으로 입력을 하기엔 실력이 일단 부족하니 매일 06시부터 00시 까지의 데이터를 수집한뒤

DB에 입력하는 방식으로 틀을 잡아 봅니다.(전날 기준 수/발신 내역을 저장 및 카운트)

 

해당 Telnet에 저장하는 방법을 리눅스 Shell에서 내보내기인 > 를 이용해서 해보려고 했는데 저장이 안됩니다...-_-;;;

이런 해보겠다고 해놓고선 저장부터 안됩니다..구글링을 해보니 nc 라는 network capture를 이용하랩니다.

아래와 같이 스크립트를 짜봅니다.(여기저기서 줏어서 만든거라 허접)

#!/bin/bash

nc 192.168.1.100 5100 > /home/baps/Tel_log.txt 

#centos에선 아래의 명령어로 진행(nc명령어 참고)
#nc -t --recv-only 192.168.1.100 5100 > /home/test/Tel_log.txt 

 

해당 Telnet에 올라오는 내용을 저장했으면 가공을 해야겠죠. 위와같이 필요없는 Header가 있으니 짤라주고 utf8로 변경합니다.

#!/bin/bash
pkill nc
rm /home/test/csv/Tel_log_utf8.csv
sed '1,7d' /home/test/Tel_log.txt > /home/baps/Tel_log_ok.txt
iconv -f euc-kr -t UTF-8 /home/test/Tel_log_ok.txt > /home/test/csv/Tel_log_utf8.csv
sleep 5
rm /home/test/Tel_log.txt
rm /home/test/Tel_log_ok.txt

 

nc 명령어를 강제로 kill 해주시고 난뒤에 어제 생성한 파일 미리 삭제하고 그다음에 1열부터 7열까지 삭제한뒤

utf8로 변경 해줍니다.

 

그러고 나서 이제 LOAD DATA LOCAL INFILE을 아래와 같이 이용하셔 넣어줍니다.

LOAD DATA LOCAL INFILE 
'/home/test/csv/Tel_log_utf8.csv' 
INTO TABLE baps
(@row)
SET t = TRIM(SUBSTR(@row,1,4)),
    ext = TRIM(SUBSTR(@row,5,5)),
    trk = TRIM(SUBSTR(@row,10,10)),
    mmdd = TRIM(SUBSTR(@row,20,6)),
    sttime = TRIM(SUBSTR(@row,26,9)),
    duration = TRIM(SUBSTR(@row,35,9)),
    fg = TRIM(SUBSTR(@row,44,3)),
    dialeddigit = TRIM(SUBSTR(@row,47,11)),
    accounttime = TRIM(SUBSTR(@row,59,8)),
    cost = TRIM(SUBSTR(@row,67,14)),
    clipnumber = TRIM(SUBSTR(@row,81,20)),
    clipname = TRIM(SUBSTR(@row,101,17))
    ;

이전 블로그 내용을 보시면 DB접속 및 LOAD DATA파일 사용법이 있으니 해당 내용 참고하시어 사용하시면 됩니다. 

이번 LOAD DATA LOCAL INFILE은 잘 보시면 행이 일정한 경우 해당 칸을 세어서 넣어주면 정확히 짤라서 데이터를

넣어주게 됩니다. 위 Telnet화면에서 보듯이 일정한 간격으로 데이터가 나올때 가장 효율적으로 사용 가능하다고 

생각됩니다.

이 작업을 crontab에 잘 넣어주면 아래와 같이 Redash 화면으로 DB에 입력된 자료를 보실 수 있습니다.

DB에 입력된 Redash화면

그리고 아래와 같이 Redash의 Visualize를 해서 만들어 봅니다. 이하 내용은 Redash쪽 공부를 하시면 됩니다.

그래프로 표현한 송/수신 건수