Forum Discussion

Andrew Gan's avatar
Andrew Gan
Copper Contributor
Jan 07, 2019

Skype for business CDR database - how to determine if user joined by phone

Hi all,

 

Not sure if this has been asked before, as I did not find it by search.

 

I am extracting the data from the Skype CDR database to get stats on usage for my company.

 

Does anyone know if the database contains information if a user joined a conference using the join by phone option? I want to be able to distinguish those that dialed into the conference using the Skype phone number(s) and those that join through the Skype application.

 

Thanks your help!

 

CDR database

5 Replies

  • AlexanderHolmeset's avatar
    AlexanderHolmeset
    MVP
    Jan 07, 2019
    I think you at least can see what kind of device is used. I took a deep dive in that database early last year, let me see if i can dig up some info. Answering from the phone now, so il check tomorrow morning.
    • Andrew Gan's avatar
      Andrew Gan
      Copper Contributor
      Jan 10, 2019

      Thanks Alexander. Will wait for your input.

       

      In the meantime, I will try what hariom suggested.

       

      • AlexanderHolmeset's avatar
        AlexanderHolmeset
        MVP
        Jan 10, 2019

        I thought i had some reports laying around, but didnt find them, il let you know if i do. I had some custom reports setup at a customer. 

        I guess you can find Caller or callee URI on  a conference that would contain a phonenumber. Just dont remeber the table it was in

  • hariom jindal's avatar
    hariom jindal
    Brass Contributor
    Jan 07, 2019

    Sql Query/Script to get Skype for Business/ Lync 2013 Dial-in Conferencing QOE

    This query you can use to get Dial-in quality and also filter with time and phone number.

     

    ==============================================

    select fu.URI as CallingUser, tu.URI as CalledUser, *

    from [QoEMetrics].[dbo].[Session] as sd

    left outer join [QoEMetrics].[dbo].[User] as tu on sd.CalleePAI = tu.UserKey

    left outer join [QoEMetrics].[dbo].[User] as fu on sd.CallerPAI = fu.UserKey

    where CalleePAI like ‘%1241790%’ and ConferenceDateTime > (getutcdate() – 30)

    order by ConferenceDateTime

    ===============================================================

    Here you need to modify your number in CalleePAI like ‘%yournumber%’ and define the number days in  (getutcdate() – Numberofdays)

     

    Sometimes running sql queries affects the database performance and its data so make sure you analyse its impact on your Skype/Lync 2013 environment and run it on own risk.

    https://tech24online.com/2018/03/01/hello-world/ 

    • Andrew Gan's avatar
      Andrew Gan
      Copper Contributor
      Jan 10, 2019

      Thanks hariom for the tips. I will try it out and let you know how it goes.

Resources