Check if a call has been "delivered" to a user.

[Psypher]

Hello again.

 

I am trying to check, if an incoming call has been made from the same telephone number in the last 60 minutes and if it was transferred/connected successfully to a user. If so, it will be transferred to the same user who received the previous call.

 

I therefore have to filter all the calls to the "main telephone number" away and only have the calls that was received by a user left.

 

I have looked through a lot of the ressources since last post and have read the CDR specification and also tried to observe the behavior in the CDR database for a multiple of scenarios.

 

I have thought 3 times, that I had the right parameters to check for, but only to realise that in certain conditions they didn't behave like I thought.

I have tried something like this: "SELECT TOP 1 DestinationNumber FROM IpPbxCDR WHERE OriginationNumber = '+4520321345' AND DATEDIFF(minute, StartTime, GETDATE()) < 60 AND State = 'Connected' AND DeliveredDate IS NOT NULL ORDER BY StartTime DESC;".

 

I have since realized that it is not right to check the State and DeliveredDate, even though it looked that way for some time and some tests.

 

There might be something useful built-in functions that I ain't aware of or a much simpler and more logical parameter to check for, that I just can't get my head around at the moment - can anybody enlighten me?

 

Kind Regards

 

Kasper S.

 

 

PS. the ECR manual ( http://www.swyxdownload.com/download/Swyx_ECR_english.pdf ) is wrongfully stating "SwyxWare Documentation for administrators As of: August 2018" on the first page. 

[Tom Wellige]

Hi Kasper,

 

there no place else than the CDRs to get this information from. In fact, that is one of the many reasons why they are written in the first place  

 

As I see it you should check the "ConnectDate" and not the "DeliveredDate". If the call was only alerted but not connected, you will get a "DeliveredDate", but no "ConnectDate". From what I understood you are looking for successfully connected calls, right?

 

Aside of that your query looks fine for me.

 

[Psypher]

Hi Tom.

 

Thanks for the quick response time! 🙂

 

Yeah, I get that the CDR is for that, but I thought that maybe there was a function, constant, etc. that could retrieve the "main telephone number", so I could filter all those calls away.

 

It could of course be made with a hardcoded global variable in the beginning of the script - like sMainNumber = "+4580801000" and  then use it in the SQL-statement, but I guess we both can agree that it is a BAD solution!

I actually came to the same solution, as you wrote, like 5 minutes before leaving work, so I changed it to use ConnectDate, but only got to test it with two scenarios.

 

I will first be back and able to try it out for real in next week, but I feel confident, that this is the right way to do it.

Thanks.

Kasper S.

[Tom Wellige]

Hi Kasper,

 

for the number configuration of the trunks and users (external numbers) there is no API available on the Server Script API.

 

You could of course run a PowerShell script (or ConfigDataStore wrapper) from within the call routing to get the number you need, but here you definitely have to keep an eye on the performance and resources. For each such call a ConfigDataStore client will be instantiated which comes with roughly 50MB consumption. Depending on the call load on your system this can sum up to quite considerable numbers.

 

So a constant definition at the beginning of your script might actually be the better solution.

 

[Psypher]

Hi Tom.

Now I had time to check it out and test the code more.

 

Yes, that would be worse performance-wise and too complex compared to a constant in the script.

 

I just wanted to avoid that, so the adaptation and setting up for the script to use in different situations would be easier (especially for other persons than me) and to avoid errors. It isn't really a requirement, but more nice-to-have, good practice and smoother.

 

If it is just well-documented in the code, in the modules and in paper documentation, then it is all good.

 

I can use it now and maybe later get an epiphany with a smarter solution.

 

I am still learning, so the CDR specification and constantly inspecting the database for how it behaves in different scenarios, is a great help and suddenly inspires to new and better approaches.

 

Thanks a lot, Tom.

Best regards

 

Kasper S. 

[Tom Wellige]

You are mostly welcome, Kasper