report_capture table inconsistent references

[billsimon]

I see in my apache error log:

[client 192.168.4.203:60673] Fail to execute query: [SELECT *,'single' as dbnode FROM report_capture_all_20170606...

but DB only uses a single report_capture table. The homer rotate scripts and schema are set for a single report_capture table, and the kamailio.cfg is set to write only to report_capture.

RestApi/Report.php looks for partitioned report_capture when the RTCP tables are set up this way...

if (defined('RTCP_TABLE_PARTITION') && RTCP_TABLE_PARTITION == 1){ ... }

So we need API, DB scripts, and kamailio config to all match.

[adubovikov]

https://github.com/sipcapture/homer-api/blob/master/api/preferences_example.php#L28

/ Partition rtcp_capture table. Set to 1 if you use rtcp_capture_all_YYYYMMDDD / define('RTCP_TABLE_PARTITION', 0);

[billsimon]

It is set to 1 because rtcp_capture_all_ table is set up this way.

[adubovikov]

sorry, I lost you. Do you use rtcp_capture as a single table or rtcp_capture_all_YYYYMMDD ?

[billsimon]

Using rtcp_capture_all_YYYYMMDD because I collect a lot of rtcp traffic. No problem with that. But seems that report_capture is somehow tied to this and the schema, rotate script, and kamailio.cfg are not set up to use report_capture_YYYYMMDD

[esmsnt]

I see this same error on my current implementation of homer. I am running the latest api and ui from git, installed on Centos 7 using the homer install script. Using CaptAgent to gather remote SIP traffic and ship it to Homer via HEP.

I just started working with Homer a few days ago and the docs are sparse so I want to make sure I am understanding how this should be working before I submit a pull request, but it appears to me that the report_capture table does not rotate. The rotate scripts do not contain the table definition or any code to rotate the report_capture table.

It also appears that the Kamailio.cfg file references a single report_capture table on line ~289 when handling RTCP-XR and on line ~984 when handling HEP packets. You will notice on the second it looks like someone did configure for rotation at some point but it is commented out.

Kamailio.cfg line ~289:

else if(is_method("PUBLISH"))
      {
          if(has_body("application/vq-rtcpxr") && $(rb{s.substr,0,1}) != $
                  $var(table) = "report_capture";

Kamailio.cfg line ~984:

else if($hep(0x00b) == 32 || $hep(0x00b) == 33){
        #If report lets proceed here with payload
        #xlog("HEP Report QPS CHUNK PAYLOAD $hep(0x00f) request from $si$
        #Do parsing internal
        $var(utc) = $utimef(%Y%m%d);
        #$var(table) = "report_capture_" + $var(utc);
        $var(table) = "report_capture"

Based on all of this I believe that the intention is not to rotate the report_capture table. If I am wrong please let me know.

In the Homer API, /RestApi/Report.php most of the references to the report_capture table do not append a type or timestamp key, but on line ~594 it does add a timestamp key if you have RTCP_TABLE_PARTITION enabled, which from what I am able to determine you must have enabled for the kamailio configuration to work with RTCP inserts. Here is the code I mentioned above from the API, if you comment out the check for the RTCP table it will resolve this error and should make all the references to the report_table consistent (this is the odd one out). The reference to report_capture on line ~752 of the RestApi/Report.php and on ~1647 do not have any handling that I see for rotating report_capture tables.

$layerHelper['table']['base'] = "report_capture";
$layerHelper['where']['type'] = $and_or ? "OR" : "AND";
$layerHelper['where']['param'] = $callwhere;
$layerHelper['time'] = $time;
foreach($nodes as $node) {
    $db->dbconnect_node($node);
    $limit = $limit_orig;
    //if (defined('RTCP_TABLE_PARTITION') && RTCP_TABLE_PARTITION == 1){
    //  foreach($timearray as $tkey=>$tval) {
    //      $layerHelper['table']['type'] = "all";
    //      $layerHelper['table']['timestamp'] = $tkey;
    //  }
    //}

I could be completely wrong on this, I don't actually see any data in my report_capture table today because I do not capture any QoS reports locally. I can submit a pull request for the API or you can just make this adjustment if you prefer.

[techman83]

@esmsnt rtcp does get rotated and I just noted the kamailo.cfg in homer-docker is quite out of date and doesn't actually inject data into the rotated tables.

Manually populating data in rtcp_capture_20180605 (for today) from rtcp_capture when combined with define('RTCP_TABLE_PARTITION', 1);, makes data appear in the homer-api built from master.

So if kamailo starts putting data in the right place it'll probably all work and not end up with an ever growing rtcp_capture table.

[esmsnt]

Yes, rtcp_capture does get rotated in my setup too, it is report_capture that does not get rotated. There is a section in the API Report.php file where the report_capture table gets accessed as if it was rotated based on the RTCP_TABLE_PARTITION setting. I think this is a mistake/bug.

[techman83]

@esmsnt Oh of course! I'll blame end of day brain and a lot of chatter about RTCP :smile:

My report capture table doesn't get rotated and is pretty tiny compared to the rtcp table (which kamailo is populating instead of the rotated tables.

+---------------------+----------------------------------------------------+------------+
| Database            | Table                                              | Size in MB |
+---------------------+----------------------------------------------------+------------+
| homer_data          | rtcp_capture                                       |    7367.02 |
| homer_data          | report_capture                                     |       0.89 |
| homer_data          | rtcp_capture_all_20180527                          |       0.09 |
| homer_data          | rtcp_capture_all_20180606                          |       0.09 |
| homer_data          | rtcp_capture_all_20180605                          |       0.09 |
| homer_data          | rtcp_capture_all_20180604                          |       0.09 |
| homer_data          | rtcp_capture_all_20180603                          |       0.09 |
| homer_data          | rtcp_capture_all_20180602                          |       0.09 |
| homer_data          | rtcp_capture_all_20180526                          |       0.09 |
| homer_data          | rtcp_capture_all_20180601                          |       0.09 |
| homer_data          | rtcp_capture_all_20180531                          |       0.09 |
| homer_data          | rtcp_capture_all_20180530                          |       0.09 |
| homer_data          | rtcp_capture_all_20180529                          |       0.09 |
| homer_data          | rtcp_capture_all_20180528                          |       0.09 |
| homer_data          | rtcp_capture_all_20180607                          |       0.09 |
+---------------------+----------------------------------------------------+------------+

Though I'm not super across what the report_capture table is for and why it relates to RTCP. I'm pretty sure it doesn't and the OP is talking about something entirely different to what we're looking into (QoS data aka RTCP) and likely belongs in another issue.

For the OP, by the sounds of it Kamailio config will need to ensure whatever reports are end up in a rotated tables and the rotation script include report_capture to create the tables/drop the old ones.

[adubovikov]

http://install.sipcapture.io/