Elastic Search, Logstash, and Kibana (ELK) stack for Freeswitch

[bcmike]

Hi everyone,

One thing that I can never find is a good solution for visualizing and querying Freeswitch queue stats. Since there's nothing that meets my requirements I'm thinking about trying to develop something for the ELK stack. I'm not a whiz bang expert so its going to be a bit of a slog for me as far as the learning curve is concerned.

I'm just wondering if anyone has seen any examples of an ELK stack drawing logs from Freeswitch and visualizing them? Any hints or pointers would be appreciated.

Of course if I make it anywhere I'll document and post it here.

 

[Adrian Fretwell]

I not sure what ELk stack is, can you post a link? When you say "queue stats" are you talking about receive buffers?

 

[bcmike]

I not sure what ELk stack is, can you post a link? When you say "queue stats" are you talking about receive buffers?

Hi, ELK is basically a very fancy log parser. It will suck logs from almost anything and let you put them into dashboards, set alerts, run queries against the data, etc.. I'm currently implementing it to get real time logs from my firewalls so hopefully we can detect attacks quicker.

However I've also been looking for a package to to present queue stats to customer, ie avg call time, avg hold time, who takes the most calls, and more. In Asterisk queues create logs and there are several packages that will process those logs and spit out all sorts of pertinent stats. I'm hoping A) to capture Freeswitch stats into logs and B) process those logs through something like elk to give the customers a dash board.

It's a big task, but something we really need. Also if someone's already done it I don't want to re-invent the wheel.

Elk link: https://www.elastic.co/elastic-stack/

 

[Adrian Fretwell]

Mike, thank you for the link.

It it is of any help, I pull stats on extensions from time to time, I wrote a small .php script to do it. The script takes two parameters, the domain name and the number of hours to look back over. It produces a .csv outupt that can be imported into a spreadsheet. Script shown below:

#!/usr/bin/php
<?php

if ($argc < 3 )
{
    exit( "Usage: stats-per-extension-for-domain.php <domain> <hours>\n   Eg: stats-per-extension-for-domain.php mycustomer.mysip.uk 168\n\n" );
}

$dbconn = pg_connect("host=127.0.0.1 port=5432 dbname=fusionpbx user=fusionpbx password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");

if (!$dbconn) {
    echo "An error occurred connecting to the database.\n\n";
    exit(1);
}

$sql = "select * from v_domains where domain_name = '".$argv[1]."'";
$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_domains database query.\n\n";
    exit(1);
}
$rec_count = pg_num_rows($result);

if ($rec_count < 1) {
    echo "Error: Domain does not exist.\n\n";
    exit(1);
}

if ($rec_count > 1) {
    echo "Error: More than 1 Domain selected.\n\n";
    exit(1);
}

$domain = pg_fetch_row($result);
pg_free_result($result);
unset($sql);

$sql = "select extension, effective_caller_id_name from v_extensions where domain_uuid = '".$domain[0]."'";
$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_extensions database query.\n\n";
    exit(1);
}
while($row = pg_fetch_row($result)) {
    $extn_data[$row[0]] = $row[1];
}
pg_free_result($result);
unset($sql);

$sql = "select ring_group_extension, ring_group_name from v_ring_groups where domain_uuid = '".$domain[0]."'";
$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_ring_groups database query.\n\n";
    exit(1);
}
while($row = pg_fetch_row($result)) {
    $rgrp_data[$row[0]] = $row[1];
}
pg_free_result($result);
unset($sql);

$sql_extn_ib_add = "and (a.destination_number in (";

foreach($extn_data as $t_extn => $t_name) {
    $sql_extn_ib_add .= "'".$t_extn."',";
}
foreach($rgrp_data as $t_extn => $t_name) {
    $sql_extn_ib_add .= "'".$t_extn."',";
}

$sql_extn_ib_add = rtrim($sql_extn_ib_add, ",");
$sql_extn_ib_add .= ")) ";


$sql_hours_add = "and a.start_stamp > now() - INTERVAL '".$argv[2]." hours' ";
$sql_select_add = "and a.domain_uuid = '".$domain[0]."' ";


$sql = "select a.destination_number, count(a.direction) as \"call_count\",
 to_char((avg(a.answer_stamp - a.start_stamp) || ' second')::interval, 'HH24:MI:SS') as \"avg_ring\",
 to_char((avg(a.duration) || ' second')::interval, 'HH24:MI:SS') as \"avg_call_duration\",
 to_char((max(a.answer_stamp - a.start_stamp) || ' second')::interval, 'HH24:MI:SS') as \"longest_wait_time\"
 from v_xml_cdr a where a.direction = 'inbound' and a.hangup_cause = 'NORMAL_CLEARING' ";

$sql .= $sql_select_add;
$sql .= $sql_extn_ib_add;
$sql .= $sql_hours_add;

$sql .= "group by a.destination_number order by a.destination_number ";

$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_xml_cdr database query.\n\n";
    exit(1);
}
$rec_count = pg_num_rows($result);


while($row = pg_fetch_row($result)) {
    $call_data[$row[0]][0] = $row[1];
    $call_data[$row[0]][1] = $row[2];
    $call_data[$row[0]][2] = $row[3];
    $call_data[$row[0]][3] = $row[4];
    $call_data[$row[0]][4] = "0";
    $call_data[$row[0]][5] = "0";
    $call_data[$row[0]][6] = "0";
    $call_data[$row[0]][7] = "0";
    $call_data[$row[0]][8] = "0";
    $call_data[$row[0]][9] = "0";
}

pg_free_result($result);
unset($sql);

$sql = "select a.destination_number, count(a.direction) as \"call_count\" from v_xml_cdr a
 where a.direction = 'inbound'
 and a.hangup_cause = 'ORIGINATOR_CANCEL'  ";

$sql .= $sql_select_add;
$sql .= $sql_extn_ib_add;
$sql .= $sql_hours_add;
$sql .= "group by a.destination_number order by a.destination_number ";

$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_xml_cdr database query.\n\n";
    exit(1);
}
$rec_count = pg_num_rows($result);

while($row = pg_fetch_row($result)) {
    if (!isset($call_data[$row[0]][0])) $call_data[$row[0]][0] = "0";
    if (!isset($call_data[$row[0]][1])) $call_data[$row[0]][1] = "0";
    if (!isset($call_data[$row[0]][2])) $call_data[$row[0]][2] = "0";
    if (!isset($call_data[$row[0]][3])) $call_data[$row[0]][3] = "0";
    $call_data[$row[0]][4] = $row[1];
    if (!isset($call_data[$row[0]][5])) $call_data[$row[0]][5] = "0";
    if (!isset($call_data[$row[0]][6])) $call_data[$row[0]][6] = "0";
    if (!isset($call_data[$row[0]][7])) $call_data[$row[0]][7] = "0";
    if (!isset($call_data[$row[0]][8])) $call_data[$row[0]][8] = "0";
    if (!isset($call_data[$row[0]][9])) $call_data[$row[0]][9] = "0";
}

pg_free_result($result);
unset($sql);

$sql = "select b.extension, count(a.direction) as \"call_count\",
 to_char((avg(a.answer_stamp - a.start_stamp) || ' second')::interval, 'HH24:MI:SS') as \"avg_ring\",
 to_char((avg(a.duration) || ' second')::interval, 'HH24:MI:SS') as \"avg_call_duration\",
 to_char((max(a.answer_stamp - a.start_stamp) || ' second')::interval, 'HH24:MI:SS') as \"longest_wait_time\"
 from v_xml_cdr a, v_extensions b where a.direction = 'outbound' and a.hangup_cause = 'NORMAL_CLEARING' and b.domain_uuid = a.domain_uuid and b.extension_uuid = a.extension_uuid ";


$sql .= $sql_select_add;
$sql .= $sql_hours_add;

$sql .= "group by b.extension order by b.extension ";

//echo $sql."\n";
$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_xml_cdr database query.\n\n";
    exit(1);
}
$rec_count = pg_num_rows($result);


while($row = pg_fetch_row($result)) {
    if (!isset($call_data[$row[0]][0])) $call_data[$row[0]][0] = "0";
    if (!isset($call_data[$row[0]][1])) $call_data[$row[0]][1] = "0";
    if (!isset($call_data[$row[0]][2])) $call_data[$row[0]][2] = "0";
    if (!isset($call_data[$row[0]][3])) $call_data[$row[0]][3] = "0";
    if (!isset($call_data[$row[0]][4])) $call_data[$row[0]][4] = "0";
    $call_data[$row[0]][5] = $row[1];
    $call_data[$row[0]][6] = $row[2];
    $call_data[$row[0]][7] = $row[3];
    $call_data[$row[0]][8] = $row[4];
    if (!isset($call_data[$row[0]][9])) $call_data[$row[0]][9] = "0";
}

pg_free_result($result);
unset($sql);

$sql = "select b.extension, count(a.direction) as \"call_count\" from v_xml_cdr a, v_extensions b
 where a.direction = 'outbound'
 and a.hangup_cause = 'ORIGINATOR_CANCEL' and b.domain_uuid = a.domain_uuid and b.extension_uuid = a.extension_uuid ";

$sql .= $sql_select_add;
$sql .= $sql_hours_add;
$sql .= "group by b.extension order by b.extension ";

//echo $sql."\n";
$result = pg_query($dbconn, $sql);
if (!$result) {
    echo "An error occurred with the v_xml_cdr database query.\n\n";
    exit(1);
}
$rec_count = pg_num_rows($result);

while($row = pg_fetch_row($result)) {
    if (!isset($call_data[$row[0]][0])) $call_data[$row[0]][0] = "0";
    if (!isset($call_data[$row[0]][1])) $call_data[$row[0]][1] = "0";
    if (!isset($call_data[$row[0]][2])) $call_data[$row[0]][2] = "0";
    if (!isset($call_data[$row[0]][3])) $call_data[$row[0]][3] = "0";
    if (!isset($call_data[$row[0]][4])) $call_data[$row[0]][4] = "0";
    if (!isset($call_data[$row[0]][5])) $call_data[$row[0]][5] = "0";
    if (!isset($call_data[$row[0]][6])) $call_data[$row[0]][6] = "0";
    if (!isset($call_data[$row[0]][7])) $call_data[$row[0]][7] = "0";
    if (!isset($call_data[$row[0]][8])) $call_data[$row[0]][8] = "0";
    $call_data[$row[0]][9] = $row[1];
}

pg_free_result($result);
unset($sql);


pg_close($dbconn);

echo "\"Extension\",\"Name\",\"Inbound Calls\",\"Inbound Avg Ring Time\",\"Inbound Avg Call Duration\",\"Inbound Longest Ring Time\",\"Inbound Missed Calls\",\"Outbound Calls\",\"Outbound Avg Ring Time\",\"Outbound Avg Call Duration\",\"Outbound Longest Ring Time\",\"Outbound Cancelled Calls\"\n";
foreach($call_data as $key => $val) {
    echo "\"".$key."\",\"";
    if(array_key_exists($key, $extn_data)) {
        echo $extn_data[$key];
    } else {
        if (array_key_exists($key, $rgrp_data)) {
        echo $rgrp_data[$key];
        } else {
            echo "No Name";
        }
    }
    echo "\",\"".$val[0]."\",\"".$val[1]."\",\"".$val[2]."\",\"".$val[3]."\",\"".$val[4]."\",\"".$val[5]."\",\"".$val[6]."\",\"".$val[7]."\",\"".$val[8]."\",\"".$val[9]."\"\n";
}

unset($call_data);
unset($extn_data);
exit(0);

 

[gflow]

You could check out Graylog as an alternative, somebody made a post on here a while back about it:

SOLVED - Install Graylog2 Plugin

We use a Graylog2 server, and it has been on our wishlist to get the logs from FreeSwitch to our Graylog server. FreeSwitch has a "graylog2" module (see https://freeswitch.org/stash/projects/FS/repos/freeswitch/browse/src/mod/loggers/mod_graylog2), but unfortunately that mod does not come with...

www.pbxforums.com

 

[bcmike]

You could check out Graylog as an alternative, somebody made a post on here a while back about it:

SOLVED - Install Graylog2 Plugin

We use a Graylog2 server, and it has been on our wishlist to get the logs from FreeSwitch to our Graylog server. FreeSwitch has a "graylog2" module (see https://freeswitch.org/stash/projects/FS/repos/freeswitch/browse/src/mod/loggers/mod_graylog2), but unfortunately that mod does not come with...

www.pbxforums.com

Thanks for this, we'll investigate Graylog as well.