sipcapture / homer-api Goto Github PK

Greetings, after reinstall homer-api from scratch I get following stuff during search request:

{
"status": 500,
"error": "PDOException",
"message": "SQLSTATE[42000] [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-ui.alias as hcs ON t.source_ip=hcs.ip AND t.source_port=hcs.port LEFT JOIN home' at line 3"
}
Maybe I misconfigured something ?
Thanks.

Hi,

I have been following the homer 5 installation guide, and copied the kamailio config file to a kamailio 4.3.1 (rpm distribution) installation.

When I start sending traffic over to the homer kamailio capture agent I am getting the following errors in the log of the capture node:

ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Table 'homer_data.sip_capture_rest_%Y%m%d' doesn't exist
ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query
ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Table 'homer_data.sip_capture_registration_%Y%m%d' doesn't exist
ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query

I have not pushed a call through it yet, so I presume the same error will come up when attempting to insert to the sip_capture_call_%Y_%m%d tables.

I have checked the mysql homer_data database and the tables do exist:

mysql> show tables;
+-----------------------------------+
| Tables_in_homer_data              |
+-----------------------------------+
| logs_capture                      |
| report_capture                    |
| rtcp_capture                      |
| sip_capture_call_20150407         |
| sip_capture_call_20150805         |
| sip_capture_call_20150806         |
| sip_capture_call_20150807         |
| sip_capture_registration_20150407 |
| sip_capture_registration_20150805 |
| sip_capture_registration_20150806 |
| sip_capture_registration_20150807 |
| sip_capture_rest_20150407         |
| sip_capture_rest_20150805         |
| sip_capture_rest_20150806         |
| sip_capture_rest_20150807         |
+-----------------------------------+
15 rows in set (0.00 sec)

Kamailio version is 4.3.1
MySQL version is 5.6.26
MySQL config taken from 'MySQL Tables' section in following url: https://github.com/sipcapture/homer/wiki/HOMER-3.6
Homer 5 version is git release as of 5 Aug 2015.

I have double checked the mysql username/password in the kamailio config and the listen ip address, these all appear correct.

Please let me know if you need any further info from me to troubleshoot further.

Apparantly, only one column can have a DEFAULT CURRENT_TIMESTAMP.

da8b25b broke this in the schema_statistics.sql

ERROR 1293 (HY000) at line 76: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

When I try to execute schema_capture.sql it fails with error
ERROR 1064 (42000) at line 9: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT=1' at line 24

If I remove /*! MySQL-specific code */ it appears to be working at least for first table creation.

I have a multi-node setup where on the 2nd node only has a MySQL and Kamailio running.

Aliasses defined in the UI are only saved locally, so calls returned from the 2nd node don't have the configured aliasses applied.

It seems that the aliasses are applied by using a LEFT JOIN in MySQL itself, but this ofcourse doesn't work on the 2nd node that doesn't have the aliasses.

I guess a proper solution would be to apply the aliasses in code before returning the results to homer-ui.

Hi,
there is an issue when I want show SIP stream when I click on call-id.

Apache errors:

[Wed Jul 29 10:46:14.857728 2015] [:error] [pid 27646] [client XXXXX041] PHP Notice: Undefined variable: lnodes in /var/www/homer-ui/api/RestApi/Search.php on line 536, XXXX
[Wed Jul 29 10:46:14.857760 2015] [:error] [pid 27646] [client XXXX51041] PHP Warning: Invalid argument supplied for foreach() in /var/www/homer-ui/api/RestApi/Search.php on line 536, XXXX

endeed, in Json POST there is no node
{"timestamp":{"from":1438153042857,"to":1438157113028},"param":{"search":{"id":5362,"callid":["3200762-3647140992-581717@XXXXXX"]},"location":{"node":[]},"transaction":{"call":true,"registration":false,"rest":false}}}

thanks

Hi guys,

How do I get post deal delay (PDD) info in Homer? I went trough database schema but didn't find such table field.. Looks like there is no much information across Internet on this as well.. The one found is Alexandr Homer-Shooting presentation, where PDD is getting with sipgrep http://www.kamailio.org/events/2014-KamailioWorld/day2/21-Alexandr.Dubovikov-Homer-Shooting.pdf

Thanks!

Hi,

I'm setting the Homer everythings looks, the DB looks good, the Web configs Looks good, but im having problems with the rotate scripts, im getting:

DBD::mysql::db do failed: Invalid default value for 'to_date' at ./homer_mysql_partrotate_unixtimestamp.pl line 160.
Failed to execute query [Invalid default value for 'to_date'] with error: DBD::mysql::db do failed: Invalid default value for 'to_date' at ./homer_mysql_partrotate_unixtimestamp.pl line 160.
Failed to execute query [Invalid default value for 'to_date'] with error: DBD::mysql::db do failed: Invalid default value for 'to_date' at ./homer_mysql_partrotate_unixtimestamp.pl line 160.
Failed to execute query [Invalid default value for 'to_date'] with error: DBD::mysql::db do failed: Invalid default value for 'to_date' at ./homer_mysql_partrotate_unixtimestamp.pl line 160.
Failed to execute query [Invalid default value for 'to_date'] with error: DBD::mysql::db do failed: Invalid default value for 'to_date' at ./homer_mysql_partrotate_unixtimestamp.pl line 160.

Everytime i run the homer_rotate script.

Please advice if you have some time.

Regards,

Hello,

I'm having problems with the login, im getting the error:

[Tue Apr 05 17:18:46 2016] [error] [client 192.168.81.79] File does not exist: /var/www/html/webhomer/api/v1, referer: http://192.168.81.146/

Here's my setup:
Mysql Version: Server version: 5.7.11 MySQL Community Server
PHP version: 5.5.33 (php55w-mysql package installed)
Apache Version: 2.2
OS: CentOS 6.7

Vhost Config httpd.conf:

<IfModule !mod_rewrite.c>
LoadModule rewrite_module modules/mod_rewrite.so

<VirtualHost *:80>
ServerAdmin root@localhost
ServerName XX.XX.XX.XX
# Indexes + Directory Root.
DirectoryIndex index.php index.html index.htm
DocumentRoot /var/www/html/webhomer/

    <Directory /var/www/html/webhomer/>
        Options Indexes FollowSymLinks MultiViews
        AllowOverride all
        Order allow,deny
        allow from all
    </Directory>

    # CGI Directory
    ScriptAlias /cgi-bin/ /var/www/html/cgi-bin/
    <Location "/var/www/html/cgi-bin">
        Options +ExecCGI -MultiViews
        Order allow,deny
        Allow from all
    </Location>
 <Directory "/var/www/html/webhomer/api">
            Options Indexes FollowSymLinks MultiViews
            AllowOverride all
            RewriteEngine On
           Order allow,deny
            allow from all
    </Directory>

    # Logfiles
    ErrorLog  /var/log/httpd/sipcapture-error.log
    CustomLog /var/log/httpd/sipcapture-access.log combined

Currently default is 5min but some people prefer better granulation, but you have to dig into cfg file to find what need to change. So It be nice to have variable at top of config for easier management.

https://github.com/sipcapture/homer-api/blob/master/examples/sipcapture/kamailio.cfg#L459
https://github.com/sipcapture/homer-api/blob/master/examples/sipcapture/kamailio.cfg#L462

Call-flow mode assume that endpoint is strict IP:PORT. This create additional node in call-flow graphs, if my node send request from one port and expect answer on different port (contact header).

Here example how it looks like, where 10.10.10.10 is same node in term of callflow.

10.10.10.10:65000->172.16.1.1:5060->10.10.10.10:5600

Personally I like how wireshark make callflow graph (where port number shows at end of arrow)

Hello.

I just noticed, that for a very small amount of calls, HOMER returns me blank results on the details page, even when looks like the DB has all the info on it (at least the data db):

This is what I see:

• Call flow
  
• Error I see on the browser console:

reloading
resultCtrl.js:37 AUTH TRUE
resultCtrl.js:299 modal1 opened from url trans-1870421790
resultCtrl.js:299 modal1 opened from url trans-1870421790
dialogCtrl.js:296 Get call duration....
dialogCtrl.js:229 modal1 message opened from url message1070437127
dialogCtrl.js:229 modal1 message opened from url message1070437127
angular.js:12520 TypeError: Cannot read property 'id' of undefined
    at dialogCtrl.js:73
    at angular.js:14792
    at r.$eval (angular.js:16052)
    at r.$digest (angular.js:15870)
    at r.$apply (angular.js:16160)
    at g (angular.js:10589)
    at T (angular.js:10787)
    at XMLHttpRequest.w.onload (angular.js:10728)(anonymous function) @ angular.js:12520(anonymous function) @ angular.js:9292(anonymous function) @ angular.js:14800r.$eval @ angular.js:16052r.$digest @ angular.js:15870r.$apply @ angular.js:16160g @ angular.js:10589T @ angular.js:10787w.onload @ angular.js:10728

• Data on the DB

*************************** 1. row ***************************
              id: 5013880
            date: 2016-01-22 08:34:48
        micro_ts: 1453451687295576
          method: INVITE
    reply_reason: 
            ruri: sip:*****@*****
       ruri_user: ***********2
     ruri_domain: 
       from_user: ********6
     from_domain: 
        from_tag: xxxxxxxx
         to_user: **********2
       to_domain: 
          to_tag: 
        pid_user: 
    contact_user: yyyyyyyy
       auth_user: ***********6
          callid:*********@*************:5060
     callid_aleg: 
           via_1: SIP/2.0/UDP a.b.c.d:5060;branch=xxxxxxxxxxx
    via_1_branch: *********
            cseq: 103 INVITE
       diversion: 
          reason: 
    content_type: application/sdp
            auth: 
      user_agent: USER_AGENT_1
       source_ip: a.b.c.d
     source_port: 5060
  destination_ip: d.e.f.g
destination_port: 5060
      contact_ip: a.b.c.d
    contact_port: 5060
   originator_ip: 
 originator_port: 0
  correlation_id: *****@*******:5060
   custom_field1: 
   custom_field2: 
   custom_field3: 
           proto: 1
          family: 2
        rtp_stat: 
            type: 1
            node: node1:1012
             msg: INVITE [...]

Call was made on the 22nd, query on homer on the 24th, we keep the data for more than 2 days in the db

-----
$maxparts = $ARGV[3] // 7; #7 days How long keep the data in the DB
$newparts = 2; #new partitions for 2 days. Anyway, start this script daily!
@stepsvalues = (86400, 3600, 1800, 900);
$partstep = $ARGV[2] // 0; # 0 - Day, 1 - Hour, 2 - 30 Minutes, 3 - 15 Minutes 
------
#Make new table rotate. Homer5 Style. 10 days for calls, 7 days for registrations, 5 days for rest
$new_table 0 10,7,5

Any idea why is this happening?

Thanks Alessio

Hello,

I just noticed, by checking why useragent statistics did not seem to be displayed, that current APIs for usergent statistics use a query not compatible with recent changes in MySQL's default sql_mode (see https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html, https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes). Specifically, I have (from ngrep)

T 2015/12/15 12:01:05.268456 127.0.0.1:50464 -> 127.0.0.1:3306 [AP] 5....SELECT id, UNIX_TIMESTAMP(`from_date`) as from_ts, UNIX_TIMESTAMP(`to_date`) as to_ts, useragent, method, COUNT(id) as cnt, SUM(total) as total FROM stats_useragent WHERE (`to_date` BETWEEN FROM_UNIXTIME(1450088318) AND FROM_UNIXTIME(1450174718)) AND ((method = 'INVITE')) GROUP BY useragent order by id DESC

T 2015/12/15 12:01:05.268805 127.0.0.1:3306 -> 127.0.0.1:50464 [AP] �...�..#42000Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'homer_statistic.stats_useragent.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It may be nice to see if at least the suggested configuration for MySQL can include a proper sql-mode override (removing ONLY_FULL_GROUP_BY, or if future API changes can take into account these "strict" modes.

Hi,

I am trying to update the default admin node from the web-ui and am getting the following error when clicking the "save changes" button, it apprears that you have a "like" command in an update staement somewhere:

2015/08/05 14:01:13 [error] 5415#0: *74 FastCGI sent in stderr: "PHP message: Fail to execute query: [UPDATE node SET status = '1', host = '127.0.0.1', dbname = 'homer_data', dbport = '3306', dbusername = 'homer', dbpassword like 'password', dbtables = 'sip_capture', name = 'homer01' WHERE id=1]
PHP message: Array
(
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'like 'password', dbtables = 'sip_capture', name = 'homer01' WHERE id=1' at line 1
)" while reading response header from upstream, client: 10.6.0.25, server: trace_node, request: "POST /api/admin/node/edit HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "capture_node", referrer: "http://capture_node/"

Hi, Guys!

I suggest 2 variants of this feature implementation:

1. Use DNS resolver at data read time (have performance impact, because we will do resolution for every API query every time), it is fast, but it will be done every time we handle a query.
2. Use DNS resolver at write time. We resolve once, then use plain flat(without JOINs) SELECT queries on read API quries. Much better performance and changes independency (now we have host named XXXX, and in all system logs it named at the same manner, but tomorrow it will be named YYYYY, but in all logs already written to storage it will be still named as XXXXX).

FROM_UNIXTIME is only available in MySQL

    $var(t_date) = "FROM_UNIXTIME(" + $var(t1) + ", '%Y-%m-%d %H:%i:00')";
    $var(f_date) = "FROM_UNIXTIME(" + $var(t2) + ", '%Y-%m-%d %H:%i:00')";

should be changed to something like this:

    $var(t_date) = "to_char(date(to_timestamp(" + $var(t1) + ")), '%Y-%m-%d %H:%i:00')";
    $var(f_date) = "to_char(date(to_timestamp(" + $var(t2) + ")), '%Y-%m-%d %H:%i:00')";

Hi, Guys.

Is it critical for application, that some fields of tables have no default values in DB?
I made a fixup in my test installation because of sipcapture module of kamailio repoted errors like:
***: Filed 'XXXX' doesn't have a defalt value

If it is not critical, I'l add default to all fields in DB and make a pull request.

Hi,

I've just installed the Homer 5 Beta but I cannot login with the default user (admin/test123).
I get "Email and password combination not recognised". (also tried [email protected]/test123)
How can i find out what is wrong? The apache logs don't show any errors, the database username and password work when I connect via phpmyadmin and/or mysql cli.
Is there any way to enable more verbose logging?

The UI wasn't working and I saw the error permission denied for relation user_menu in Apache error.log

I needed to log in to each database and run the command:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public  TO homer_user;

Maybe this (or a more fine tuned set of permissions) needs to be specified at the bottom of each schema_*.sql file for PostgreSQL

I'm getting a lot of errors from the homer API pertaining to a webrtc_capture table.

Unfortunately, there's no reference in the db schemas (homer-api/sql/schema_data.sql), nor the kamailio configuration (homer-api/examples/sipcapture/sipcapture.kamailio) for this.

2016/05/25 13:16:17 [error] 532#0: *1982 FastCGI sent in stderr: "PHP message: PHP Notice:  Undefined index: node in /opt/homer/homer-ui/api/RestApi/Report.php on line 1451
PHP message: Fail to execute query: [SELECT *, 'single' as dbnode FROM webrtc_capture WHERE (`date` BETWEEN FROM_UNIXTIME(1464169348) AND FROM_UNIXTIME(1464213689)) AND ( ( correlation_id = '[email protected]:5060' OR correlation_id = '[email protected]:5060_b2b-1' OR correlation_id = '[email protected]:5060_b2b-' OR correlation_id = '[email protected]:506' ))]
PHP message: Array
(
    [0] => 42S02
    [1] => 1146
    [2] => Table 'homer_data.webrtc_capture' doesn't exist
)" while reading response header from upstream

Hello

I tried to add LDAP support , it worked eventually but i had to do couple of changes as below

so i liked to share them

diff --git a/api/Authentication/LDAP.php b/api/Authentication/LDAP.php
index 5e5dad5..1e26cdd 100644
--- a/api/Authentication/LDAP.php
+++ b/api/Authentication/LDAP.php
@@ -67,12 +67,11 @@ class LDAP extends Authentication {
                      @ldap_start_tls($ds);
                }

-               if (defined('LDAP_BIND_USER') && defined('LDAP_ADMIN_USER')) {
+               if (defined('LDAP_BIND_USER') && defined('LDAP_BIND_PASSWORD')) {
                      if (!@ldap_bind( $ds, LDAP_BIND_USER, LDAP_BIND_PASSWORD)) {
                            return array();
                       }
                }
-
                 $r=@ldap_search( $ds, LDAP_BASEDN, LDAP_USERNAME_ATTRIBUTE_OPEN .$param['username'].LDAP_USERNAME_ATTRIBUTE_CLOSE);
                 if ($r) {
                      $result = @ldap_get_entries( $ds, $r);
@@ -90,7 +89,11 @@ class LDAP extends Authentication {
                                    // Default each user has normal User Privs
                                     $_SESSION['loggedin'] = $param['username'];
                                     $_SESSION['userlevel'] = LDAP_USERLEVEL;
-                       
+                                   $_SESSION['uid']        = 1;
+                                   $_SESSION['username']   = $param['username'];
+                                           $_SESSION['gid']        = 10;
+                                   $_SESSION['grp']        = "users";
+       
                                    $user['uid']     =  1;
                                    $user['username'] = $param['username'];
                                    $user['gid']      = 10;
@@ -107,7 +110,8 @@ class LDAP extends Authentication {

                                        if ($value == $param['username']) {
                                          $_SESSION['userlevel'] = 1; # LDAP_ADMINLEVEL;
-                                         $user['grp'] = "users,admins";   
+                                         $user['grp'] = "users,admins"; 
+                                         $_SESSION['grp']        = "users";  
                                        }
                                    }
                                     return $user;

Also in preference.php

/* LDAP SETTINGS */


define('LDAP_HOST',"192.168.XX.11");
define('LDAP_PORT',389);
define('LDAP_BASEDN',"ou=people,dc=XXXX,dc=com");
define('LDAP_REALM',"My Realm");
define('LDAP_USERNAME_ATTRIBUTE_OPEN',"uid=");
define('LDAP_USERNAME_ATTRIBUTE_CLOSE',"");
define('LDAP_USERLEVEL',3); 
define('LDAP_BIND_USER','uid=XXXXX,cn=admins,cn=XXXXX');
define('LDAP_BIND_PASSWORD','XXXXXX');
define('LDAP_ENCRYPTION','no');
define('LDAP_VERSION','3');
define('LDAP_ADMIN_USER','ali');

After a successful Homer install we noticed the Call-Flow/QoS dialog was showing a zero-length session duration for all calls. Additionally, the SDP Session data was not present on the QoS tab.

After sifting through the code it appears that part of the query engine ($layerHelper) was targeting a non-existent table on the back end in api/RestApi/Report.php. This seems to be due to an empty field assignment at Report.php:1147.

The $layerHelper['table']['type'] field should be set to 'call' for the query engine to hit the right table, but the $query_type variable being used for the assignment is empty.

We've temporarily hard-coded that line to 'call' and we are now seeing the correct call lengths displayed on the Call-Flow/QoS dialogs. Additionally, SDP Session data is now showing correctly on the QoS tab as well.

i.e. Replacing the following line:

        $layerHelper['table']['type'] = $query_type;

with the following:

        $layerHelper['table']['type'] = 'call';

has resolved the issue for us.

Hi all,
I found an issue and I found a workaround. But I'm not php dev. I just posting my correction.
But for me, the workaround must been add in different part of the Report.php

In all foreach function:

foreach($nodes as $node) # Ex: line 507 in api/RestApi/Report.php

I have to add $layerHelper['values'] = []; to empty this array (layerHelper).

Else the sql resquest is wrong.

Before modification

Sep  2 11:30:07 localhost myhomerapilog[10809]: RTCP Query: 2016/09/02 11:30:07 : Query: SELECT *,'capturenode1' as dbnode FROM rtcp_capture  WHERE (date BETWEEN FROM_UNIXTIME(1472802721) AND FROM_UNIXTIME(1472804032)) AND ( ( correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4b' OR correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4' )) LIMIT 100
Sep  2 11:30:07 localhost myhomerapilog[10809]: RTCP Query: 2016/09/02 11:30:07 : Query: SELECT *,'capturenode1' as dbnode,*,'capturenode2' as dbnode FROM rtcp_capture  WHERE (date BETWEEN FROM_UNIXTIME(1472802721) AND FROM_UNIXTIME(1472804032)) AND ( ( correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4b' OR correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4' )) LIMIT 100

We can see the issue in second request:
SELECT ,'capturenode1' as dbnode,,'capturenode2' as dbnode FROM rtcp_capture

with this correction, we can see:

Sep  2 11:37:14 localhost myhomerapilog[11920]: RTCP Query: 2016/09/02 11:37:14 : Query: SELECT *,'capturenode1' as dbnode FROM rtcp_capture  WHERE (date BETWEEN FROM_UNIXTIME(1472802721) AND FROM_UNIXTIME(1472804032)) AND ( ( correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4b' OR correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4' )) LIMIT 100
Sep  2 11:37:14 localhost myhomerapilog[11920]: RTCP Query: 2016/09/02 11:37:14 : Query: SELECT *,'capturenode2' as dbnode FROM rtcp_capture  WHERE (date BETWEEN FROM_UNIXTIME(1472802721) AND FROM_UNIXTIME(1472804032)) AND ( ( correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4b' OR correlation_id = '5ee70fdd-eb86-1234-d7a9-d4ae52736d4' )) LIMIT 100

we can see:
SELECT *,'capturenode1' as dbnode FROM
and
SELECT *,'capturenode2' as dbnode FROM

++

I was getting the following error in Firebug during search request:
{
"status": 500,
"error": "PDOException",
"message": "SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111)"
}
At the same time the statistics are showing well. The DB is reachable on localhost port 3213 (it's haproxy listener).

The data in node table seems correct:
mysql> select * from node;
+----+-----------+------------+--------+------------+------------------+-------------+------+--------+
| id | host | dbname | dbport | dbusername | dbpassword
| dbtables | name | status |
+----+-----------+------------+--------+------------+------------------+-------------+------+--------+
| 22 | 127.0.0.1 | homer_data | 3213 | sipcapture | xxxx
| sip_capture | RDS | 1 |
+----+-----------+------------+--------+------------+------------------+-------------+------+--------+
1 row in set (0.01 sec)

I can login with this host, port and password:

root@1378bdbb84dd:/var/tmp# mysql -u sipcapture -h127.0.0.1 -P 3213
homer_data -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
...

I've added another haproxy listener on localhost port 3306 and search works well now.
To me it looks like some of the PHP classes are connecting to default port 3306 instead of the port defined in node table.
Could you please follow up? Thanks.

Users in the "user" group can modify all dashboards. There doesn't seem to be a configuration option for this.

fresh installation, i cant get the dashboards to display,
nginx tells me the following on every dashboard / link:

2015/06/03 14:24:21 [error] 3805#0: 84 FastCGI sent in stderr: "PHP message: PHP Notice: Undefined variable: parameters in /var/www/api/RestService/Client.php on line 180
PHP message: PHP Warning: preg_match(): Compilation failed: unmatched parentheses at offset 8 in /var/www/api/RestService/Server.php on line 1198
PHP message: PHP Notice: Undefined property: RestAPI\Dashboard::$authmodule in /var/www/api/RestApi/Dashboard.php on line 50
PHP message: PHP Notice: Undefined property: RestAPI\Dashboard::$instance in /var/www/api/RestApi/Dashboard.php on line 368" while reading response header from upstream, client: *__..._**, server: "blacked-out", request: "GET /api/dashboard/store/search HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "blacked-out"

Debian Wheezy
nginx 1.2.1
php5-fpm 5.4.39
mysql-server: 5.5.43

Hello,

I noticed that search stoped giving back up-to-date results. Found below insertion error to mysql:

Jul 10 13:06:15 homer kamailio[20754]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query
Jul 10 13:06:15 homer kamailio[20754]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Data too long for column 'correlation_id' at row 1
Jul 10 13:06:15 homer kamailio[20754]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query
Jul 10 13:06:15 homer kamailio[20754]: ERROR: sipcapture [sipcapture.c:1476]: sip_capture_store(): failed to insert into database(second attempt)
Jul 10 13:06:15 homer kamailio[20754]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Data too long for column 'correlation_id' at row 1
Jul 10 13:06:15 homer kamailio[20754]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query
Jul 10 13:06:15 homer kamailio[20754]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Data too long for column 'correlation_id' at row 1
Jul 10 13:06:15 homer kamailio[20754]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query
Jul 10 13:06:15 homer kamailio[20754]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Data too long for column 'correlation_id' at row 1

It looks like it rather kamailio issue.. Any help greatly appreciated!

After few days kamailio can't write to mysql....why ? who is creating new table every day?

mysql> show tables;
+-----------------------------------+
| Tables_in_homer_data |
+-----------------------------------+
| logs_capture |
| report_capture |
| rtcp_capture |
| sip_capture_call_20151129 |
| sip_capture_call_20151130 |
| sip_capture_call_20151201 |
| sip_capture_registration_20151129 |
| sip_capture_registration_20151130 |
| sip_capture_registration_20151201 |
| sip_capture_rest_20151129 |
| sip_capture_rest_20151130 |
| sip_capture_rest_20151201 |
+-----------------------------------+

Thanks!

Yuval Cnaan

I needed to do this in the homer_data schema, otherwise Kamailio couldn't insert anything:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public  TO homer_user;

Currently the stack trace from the API discloses the password if MySQL can't connect to the database.

Hello,

The "HOMER_CUSTOM_STATS" in current Kamailio config example found here is not in accordance with the current example for CustomStats found here.

I tried using the plain default examples but the error below appeared in Kamailio log as shown below when receiving data from statsmrtr.

Also please include RTCP stats in the same configuration examples; something like

if($var(n) == "rtp_stat") {
.....
else if($var(n) == "rtcp_stat") {

etc.

Kamailio log

WARNING: HTTP request received on 8080, /api/v1/stats/push
WARNING: Type is rtp_stat
ERROR: <core> [lvalue.c:345]: lval_pvar_assign(): non existing right pvar
ERROR: <core> [lvalue.c:405]: lval_assign(): assignment failed at pos: (865,45-865,60)

RTPSniff prettyfied

{
"timestamp": 1457210300,
"interval": 10,
"streams": 5,
"packets": 1444,
"lost": 4,
"late": 0,
"lost_perc": 0.277008,
"late_perc": 0.000000,
"out_of_seq": 0,
"delay_min": 0,
"delay_max": 102273,
"delay_avg": 51136,
"type": "rtp_stat"
`}

Statsmrtr (no errors observed)

Stats: { rcvd: 26, parsed: 13, sent: 13, err: 0 }

It would be easier if the homer_mysql_new_table.pl, homer_mysql_partrotate_unixtimestamp.pl and homer_rotate files could fetch the mysql user and password information from one place. That way, the files are unmodified in the local git.

I also have to change sipcapture.kamailio, but I don't think there is a way around that, unless maybe some build script is added to generate those files based on the files in the repository?

Ideally, a git status should give no changes (since configuration files should be in .gitignore) on a fully configured system.

Hi Homer Team,

I was building a server from scratch today and it failed when I was loading the schema_statistic.sql file.

[root@ip-10-81-89-89 homer-git]# mysql -u "root" -p"xxxxx" homer_statistic < homer-api/sql/schema_statistic.sql
ERROR 1067 (42000) at line 76: Invalid default value for 'to_date'

[root@ip-10-81-89-89 homer-git]# mysql --version
mysql Ver 14.14 Distrib 5.6.30, for Linux (x86_64) using EditLine wrapper

The problem seems to be related to this recent change 497da4d

The log rotation config here:
https://github.com/sipcapture/homer-api/blob/master/examples/sipcapture/sipcapture.logrotated

relies on a PID file. It probably doesn't exist at that location in most systems.

Most modern logrotate configs use invoke-rc.d, here is an example:
https://anonscm.debian.org/cgit/collab-maint/rsyslog.git/tree/debian/rsyslog.logrotate

Hello seems like SQL-script ignores 'DB_CONFIGURATION' from config :

$query .= " LEFT JOIN homer_configuration.alias as hcs ON t.source_ip=hcs.ip AND t.source_port=hcs.port LEFT JOIN homer_configuration.alias as hcd ON t.destination_ip=hcd.ip AND t.destination_port=hcd.port";

Thanks

I was getting the following error when trying to log in:

[Thu Nov 26 00:36:58.274946 2015] [:error] [pid 40264] [client 10.0.0.7:59922] PHP Notice:  Use of undefined constant DATABASE_CONNECTOR - assumed 'DATABASE_CONNECTOR' in /var/www/html/api/Authentication/internal.php on line 54

Which was solved by adding:

define('DATABASE_CONNECTOR', 'PDOConnector');

to the configuration.php file

Just wanted to leave it here in case anyone encounters the same issue. Plus it would be great to update the sample config file with that line.

Hi, Guys.
As we can see, kamailio may be installed with default layout, but binary may be started as different services with different configuration.
So my suggestion is to add a package, which will contain:

1. kamailio configuration in /etc/kamailio/kamailio-sipcapture.cfg
2. statrtup defaults for kamailio in (for RHEL and forks):/etc/sysconfig/sipcapture (pidfile,lockfile,cfgfile parameters)
3. startup script for kamailio in (for RHEL6 and forks):/etc/init.d/sipcapture, startup module (for RHEL7 and forks):/usr/lib/systemd/system/sipcapture.service
4. dependencies on all needed kamilio packages

This will allow us to run kamailio as a separate service and do not make "manual configuration". I think those technics are very usefull for automated/mass deployment.

After updating homer-ui and homer-api to yesterday's git revision, I now get a 500 response from /api/v1/search/data with message "Unable to load Database/Layer/mysql."

I am able to successfully login and view my db node, users aliases etc, but when I got to search, with no entries in the search fields, and I select any of the signalling types (calls/registrations/other), I get an empty search result, but nginx log shows 500 response, and ngrep shows message return is 500.

Versions

homer-api]# git rev-parse HEAD
aef9ffc8c5e96894cf67fc9ad7417172044f256c
homer-ui]# git rev-parse HEAD
edb54c3a9c488c5a5c379a4dfc1ce14951b24eec

API post request:

POST /api/v1/search/data HTTP/1.1
Host: 1.2.3.4
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:42.0) Gecko/20100101 Firefox/42.0
Accept: application/json, text/plain, */*
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
DNT: 1
Content-Type: application/json;charset=utf-8
Referer: http://1.2.3.4/
Content-Length: 172
Cookie: HOMERSESSID=v1eh7uehciv4jvnikob7tepna6
Connection: keep-alive
Pragma: no-cache
Cache-Control: no-cache


{"param":{"transaction":{"call":true},"limit":200,"search":{},"location":{"node":[{"id":"22","name":"bfievstrace"}]}},"timestamp":{"from":1449729510105,"to":1449751110105}}

The response from this query is:

HTTP/1.1 500 Internal Server Error
Server: nginx/1.8.0
Date: Thu, 10 Dec 2015 12:38:34 GMT
Content-Type: application/json; charset=utf-8
Content-Length: 104
Connection: keep-alive
X-Powered-By: PHP/5.6.16
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Pragma: no-cache

{
    "status": 500,
    "error": "Exception",
    "message": "Unable to load Database\/Layer\/mysql."
}

The mysql modules are installed and appear to be working properly or else I would not be able to login, as my user is defined within the database. The statistics are all displayed as well, so I presume homer is reading that from the database as well.

Any insight would be appreciated.

Is it possible to change sip search results columns (i would like to add cseq)
there are 3 custom columns (F1 F2 F3) in ui and in DB, how can i utilize these fields (example)

Inside preferences.php you have;

define('DASHBOARD_STORE','db');
define('DASHBOARD_PARAM', ROOT.'/store/dashboard');

However searching through the code shows that DASHBOARD_STORE is never checked for db, and always goes to disk.

This configurable should either be dropped from default config, or support added into the code.

Running a new install of homer5, after executing the default rotate.sh script, the new_tables.pl script hasn't created old enough tables for our kamailio install to be able to insert data. It would appear that this is a timezone related issue (we are in AEST)

Hello, every time search executed php throws string to error log:
Notice: Undefined variable: result in /srv/www/api/Database/PDOConnector.php on line 151
Maybe SQL query: [SET time_zone = "+00:00";] causes this.
Is it bug or not ?
Thank you.

I haven't dug into this to try to submit a PR, but in trying to implement the new rotation scripts I ran into this error message

Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1

Full output from running script:

root@f10c4691fbd2:/# /opt/homer_rotate/homer_rotate 
ZZ: [user]
ZZ: [password]
ZZ: [host]
ZZ: [port]
ZZ: [db_data]
ZZ: [db_stats]
ZZ: [newtables]
ZZ: [engine]
ZZ: [compress]
ZZ: [sip_capture_call]
ZZ: [sip_capture_registration]
ZZ: [sip_capture_rest]
ZZ: [sip_capture_call]
ZZ: [sip_capture_registration]
ZZ: [sip_capture_rest]
ZZ: [rtcp_capture]
ZZ: [logs_capture]
ZZ: [report_capture]
ZZ: [stats_ip]
ZZ: [stats_geo]
ZZ: [alarm_data]
ZZ: [stats_method]
ZZ: [stats_useragent]
ZZ: [rtcp_capture]
ZZ: [logs_capture]
ZZ: [report_capture]
ZZ: [stats_ip]
ZZ: [stats_geo]
ZZ: [alarm_data]
ZZ: [stats_method]
ZZ: [stats_useragent]
DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042600 VALUES LESS THAN (1461715200)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042600 VALUES LESS THAN (1461715200)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042700 VALUES LESS THAN (1461801600)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042700 VALUES LESS THAN (1461801600)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042600 VALUES LESS THAN (1461715200)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042600 VALUES LESS THAN (1461715200)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042700 VALUES LESS THAN (1461801600)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042700 VALUES LESS THAN (1461801600)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042500 VALUES LESS THAN (1461628800)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042600 VALUES LESS THAN (1461715200)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042600 VALUES LESS THAN (1461715200)' at line 1] with error: DBD::mysql::db do failed: Partitioning can not be used stand-alone in query near 'PARTITION p2016042700 VALUES LESS THAN (1461801600)' at line 1 at /opt/homer_rotate/homer_mysql_rotate.pl line 345.
Failed to execute query [Partitioning can not be used stand-alone in query near 'PARTITION p2016042700 VALUES LESS THAN (1461801600)' at line 1] with error: Can't use an undefined value as a HASH reference at /opt/homer_rotate/homer_mysql_rotate.pl line 187.

I also have PR I'm getting ready that addresses the default path for the ini, I'll have it right up.

When i try to make Search using both fields Source IP and Dest IP. Resulting page ignores Dest ip.
Actually browser sends data as a post request but seems that field is ignored.
That I see in Chrome Debug:
curl 'http://webhomer.co.volia.com/api/profile/store/search' -H 'Pragma: no-cache' -H 'Origin: http://webhomer.co.volia.com' -H 'Accept-Encoding: gzip, deflate' -H 'Accept-Language: ru,en-US;q=0.8,en;q=0.6' -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.130 Safari/537.36' -H 'Content-Type: application/json;charset=UTF-8' -H 'Accept: application/json, text/plain, /' -H 'Cache-Control: no-cache' -H 'Referer: http://webhomer.co.volia.com/' -H 'Cookie: VISITORID=848416697; __utma=56072236.2042610939.1412169153.1422954601.1433229510.27; __utmz=56072236.1415003501.13.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); _ga=GA1.2.2042610939.1412169153; checkcookie=true; HOMERSESSID=vpikhfb0baml2m49hrilrbtmo7' -H 'Connection: keep-alive' --data-binary '{"id":"search","param":{"destion_ip":"77.120.8.192","source_ip":"77.120.8.178","ruri_user":"","from_user":""}}' --compressed

And here Homer`s SQL request :
SELECT t.id, date, (micro_ts DIV 1000) as milli_ts, micro_ts,method,reply_reason,ruri,ruri_user,ruri_domain,from_user,from_domain,from_tag,
to_user,to_domain,to_tag,pid_user,contact_user,auth_user,callid,callid_aleg,via_1,via_1_branch,cseq,diversion,reason,content_type,auth,
user_agent,source_ip,source_port,destination_ip,destination_port,contact_ip,contact_port,originator_ip,originator_port,correlation_id,proto,family,rtp_stat,type,node, 'call' as trans, 'single' as dbnode, hcs.alias as source_alias, hcd.alias as destination_alias FROM sip_capture_call_20150703 as t LEFT JOIN homerv5.alias as hcs ON t.source_ip=hcs.ip AND t.source_port=hcs.port LEFT JOIN homerv5.alias as hcd ON t.destination_ip=hcd.ip AND t.destination_port=hcd.port WHERE (t.date BETWEEN FROM_UNIXTIME(1435920105) AND FROM_UNIXTIME(1436276506)) AND ( source_ip = '77.120.8.178') order by t.id DESC LIMIT 200

Maybe I missed something in configuration ?

Thank you.

Hi,
I have a issue (maybe with multi-node) when I want to see any methods.

With the firefox console, I can see no node param sent to the api.

PS: these screenshots are differents (not the same call-id).

Can you help me with this issue, I can't find where it is.

thanks in advance

I've suggested a new cfg parameter in Kamailio to make the tag name configurable:

kamailio/kamailio#780

After that is done, please consider adding log_tag to the example cfg files.

Hi!

SQL for deleting old table in homer_mysql_new_table.pl in line 111 is broken, bellow patch should fix it:

root@homer:/opt/scripts# diff -urN homer_mysql_new_table.pl.new homer_mysql_new_table.pl.org
--- homer_mysql_new_table.pl.new 2015-08-12 16:19:12.023200107 +0200
+++ homer_mysql_new_table.pl.org 2015-08-12 16:17:28.939024179 +0200
@@ -108,7 +108,7 @@
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ ;
END

-$DROP="DROP TABLE IF EXISTS sip_capture_[TRANSACTION]_[TIMESTAMP];";
+$DROP="DROP TABLE IF EXISTS sip_capture_[TRANSACTION]_[TIMESTAMP];";

$db = DBI->connect("DBI:mysql:$mysql_dbname:$mysql_host:3306", $mysql_user, $mysql_password);
#$db->{PrintError} = 0;

Now we search in tables like this:

1. set OVERAL limit to 200
2. select from table calls,
3. decrease limit for result rows count
4. if limit is still > 0 then we select from table registrations
5. decrease limit for result rows count
6. if limit is still > 0 then we select from table rest
7. return results

Ok. User defined a "time rage" as a criteria in search request, so, he expects, that software will return all packets from defined time rage, but not more then "result limit"(200).
What we have in reality? We have an issue, because there will be no results from the second and the third tables included in response if select query from the first table will return >= 200 records. but it IS incorrect behavior because of there are records in those 2 tables, that match query criteria and have higher priority by time filed, but they will not be included in query results.
I see a solution in UNITE query with ordering by time filed or in SELECT/INSERT to temporary(memory) table and then select from it with LIMIT and ORDER.

After copy example kamailio config make local changes and starting kamailio I'm getting these errors:

db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Table 'homer_data.sip_capture_call_20150619' doesn't exist
<core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query

From kamailio config I don't see where it suppose to create such tables, only tries to insert data

It would appear some API dashboard calls in Homer cause MySQL credentials to be sent back to the client;

http://127.0.0.1:8080/api/v1/dashboard/store/_1432037872

"query": "select * from \"homer.stats.counters.polycomMos.count\" where time > $fromts AND time < $tots;\nselect * from \"homer.stats.counters.baresipMos.count\" where time > $fromts AND time < $tots;\nselect * from \"homer.stats.counters.snomMos.count\" where time > $fromts AND time < $tots;",
"path": "http:\/\/homer.localhost:8086\/query",
"db": {
    "name": "homer_stats",
    "user": "root",
    "password": "root"
},