How to use join to identify calls to Country A followed by calls to a list of other Countries?

[garryclarke]

I am trying to identify calls from an originating number where a small number ie 1 or 2 are first made to country A followed by a large number of calls to any country.
I have tried this with a join but cant seem to figure out the time part so that calls to country A have happened first.

index=main Country!=NULL | join type=inner caller_no [search index=main Country=Country_A | stats count by caller_no | search count<3] | stats count by caller_no | search count >50

Any help much appreciated

[garryclarke · Accepted solution]

I managed to get this working by adding stats count max(end_date) as maxisdate to my stats command to determine the end_date of my calls to Country A. I then in my query qualifying criteria used where start_date>maxisdate to ensure the other calls happened afterwards

[musskopf]

Sorry, I couldn't understand your question. Do you have an example of your data and an example how the report should look like?

The only thing I can think now is using the command:

| eventstats count AS calls BY Country,caller_no

It would add to each event the number of calls a "caller_no" did to each country (computed over all events). From there you might be able to extract what you're after, like:

| stats max(calls),min(calls) by caller_no

[garryclarke]

Thanks musskopf To provide a real life example I am trying to run a report to identify callers who for example make 1 or 2 calls to the UK followed by a large number of calls to another country. The report would then produce a list of callerids and the total of calls.

01234567986 56

09458765784 21

The query I provided above gets the report correctly its just the logic within the joins to say that the calls to country A ie the UK where made before the other calls.