Search across multiple tables provides inconsistent results

[ghmj2417]

When attempting to search across two tables, example 1_call and 1_default, we receive inconsistent results. For example, the following query snip it will return 15 records for one API call and then the next API call it will be 0 records.

    "param": {
        "limit": 200,
        "search": {
            "1_call": [
                {
                    "name": "data_header.callid",
                    "value": "xyz",
                    "type": "string"
                }
            ],
            "1_default": [
                {
                    "name": "data_header.callid",
                    "value": "xyz",
                    "type": "string"
                }
            ]
        }
    },
    "timestamp": {"from": 1621915200000, "to": 1622001599000}

I think I narrowed it down to data/service/search.go SearchData function. I see an iteration of sData.ChildrenMap() which it appears both 1_call and 1_default should be appended to sql var. I don't know much beyond that. After looking at the logs, it appears only one of the tables is being searched per API call which is causing the inconsistent results. SELECT * FROM \"hep_proto_1_call\" WHERE (create_date between $1 AND $2 AND data_header-\u003e\u003e'callid'='xyz') LIMIT 200. If at all possible, I would prefer to not make multiple API calls. I was thinking a UNION ALL could be used when multiple tables are provided, but after reviewing the code, I have no idea where it would go.

And to give background on that search snip it. SIP OPTIONS are sent to 1_default and INVITES, BYES, etc are sent to 1_call. The search function I am using should be oblivious to this. It should just know the tables it needs to search, not which SIP messages are going where. There will be instances when, for example, only a call-id will be provided and the search function shouldn't care about the SIP message type. It would send the search request to look in both tables, and would get all of the messages matching the criteria.

As I mentioned in the first paragraph, If at all possible, I would prefer to not make multiple API calls. What do you guys think? Is this a reasonable ask?

[adubovikov]

in the single search you can search only in one table, but aggregation you can do across multiple tables using correlation mapping

[ghmj2417]

I looked at this, https://github.com/sipcapture/homer-app/wiki/Examples:-Correlation-MAPPING, but nothing stands out in terms of aggregating results across multiple tables. From what I can tell, the correlation mapping will take a value that exists in one table and pull that same value out of another table. In my case, the call id will either exist in 1_default or 1_call but shouldn't exist in both. I also searched Issues for anything relating to my use case and couldn't find anything. If you have some pointers, additional documentation, or examples on what you suggested that would be helpful.

[ghmj2417]

If you can provide any pointers, references, or examples I will do my best to piece it all together. As I said in the previous comment, https://github.com/sipcapture/homer-app/wiki/Examples:-Correlation-MAPPING has some info but nothing that stands out.

[adubovikov]

Single:

[
  {
    "source_field": "data_header.callid",
    "lookup_id": 100,
    "lookup_profile": "default",
    "lookup_field": "sid",
    "lookup_range": [
      -300,
      200
    ]
  }
]

Multiple:

[
  {
    "_help": "this will get all data from table hep_proto_100_default",
    "source_field": "data_header.callid",
    "lookup_id": 100,
    "lookup_profile": "default",
    "lookup_field": "sid",
    "lookup_range": [
      -300,
      200
    ]
  },
  {
    "_help": "this will get all data from table hep_proto_1_registration",
    "source_field": "data_header.callid",
    "lookup_id": 1,
    "lookup_profile": "registration",
    "lookup_field": "sid",
    "lookup_range": [
      -300,
      200
    ]
  }
]

[adubovikov]

you can also use correlation script (javascript base) to do some extra manipulation (input_function)

[adubovikov]

but if you want make multiple search using different search criteria, in Homer it's not possible. Just make 2 API call to get data and aggregate it on your side.

[ghmj2417]

On your Multiple example. I tried that and I didn't get any results.

I added the following to the top of call SIP 1 mapping:

    {
        "source_field": "data_header.callid",
        "lookup_id": 1,
        "lookup_profile": "default",
        "lookup_field": "sid",
        "lookup_range": [
            -300,
            200
        ]
    },

I then searched for a Call ID that exists in 1_default but does not exist in 1_call.

{
  "transaction": {},
  "limit": 200,
  "search": {
    "1_call": [
      {
        "name": "data_header.callid",
        "value": "xyz",
        "type": "string",
        "hepid": 1
      },
      {
        "name": "limit",
        "value": "1000",
        "type": "string",
        "hepid": 1
      }
    ]
  },
  "timestamp": {"from": 1621915200000, "to": 1622001599000}
}

I am either searching incorrectly or what I am trying to do is not possible and I will need to create multiple calls to search different tables.

[adubovikov]

no no, my example was for correlation: if you click on any callid you can aggregate data for a transaction view across multiple tables: for example: logs, rtcp/rtp reports etc, but of course using some uniq. parameters.

but if I understand you correct, you want to make a single search query but to different tables and get union results. As I wrote before, Homer doesn't support such method and in this case I recommend you make two requests and aggregate data on your side. I hope it helps

[ghmj2417]

Got it. Yup, you helped. Thank you for taking the time to explain it.