locked
Querying database to get ring and call time stats for workflow calls RRS feed

  • Question

  • Hi all,

    Just wondering if anyone can help with what I am trying to do. I am trying to query the LcsCRD database to get stats on incoming calls to Lync workflows. I query for calls with 200 response code and all is well and good but the results are strange - so strange that I am finding it hard to generate a query to calculate the talk and ring time. This is because for each successful call to a workflow number, I get three results. Two of them have an identical CorrelationId and the third record (the one with the call time) has no CorrelationId but has a ReplacesDialogIdTime which ties it to one of the other two records. Not ideal for trying to group the results. Has anyone come across this and share how they solved it? 

    Example results:

    Record #1 (call to workflow)

    SessionIdTime                   <time>
    InviteTime                         <time>
    ResponseTime                    <time>
    SessionEndTime                <time>
    User1Id                             2091
    User2Id                             8161
    ReplacesDialofIdTime          NULL
    CorrelationId                       123456789

    Record #2 (Note the inverted UserIds compared to other records)

    SessionIdTime                   <time>
    InviteTime                         <time>
    ResponseTime                    <time>
    SessionEndTime                <time>
    User1Id                             8161
    User2Id                              2091
    ReplacesDialofIdTime           <same as record #1 SessionIdTime >
    CorrelationId                       NULL

    Record #3

    SessionIdTime                   <time>
    InviteTime                         <time>
    ResponseTime                    <time>
    SessionEndTime                <time>
    User1Id                             2091
    User2Id                             8161
    ReplacesDialofIdTime          NULL
    CorrelationId                       123456789

     

    Wednesday, May 31, 2017 5:16 PM

All replies

  • Hi JamesIRL,

    If you want to query those information, we suggest you use Skype for business monitor report service, because LcsCDR database is used for Skype for business monitor report service, monitor report will present it in an easy to read and searchable format:

    https://technet.microsoft.com/en-us/library/gg558662.aspx


    Best Regards,
    Jim Xu
    TechNet Community Support


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Proposed as answer by jim-xu Monday, June 19, 2017 8:53 AM
    • Unproposed as answer by JamesIRL Friday, October 6, 2017 4:26 PM
    Friday, June 2, 2017 12:03 AM
  • Hi Jim,

    Thanks for your time. Sadly, the reporting service is not what we are looking for as we need to be able to create custom reports. 

    Has anyone resolved this query or is able to offer a solution?

    Thanks,
    James. 

    Friday, October 6, 2017 4:29 PM
  • You can use PowerBI with SQL statement and combine it with DAX.

    In LcsCDR there are also a lot of tables to search in. If looking for incoming calls for workgroup, you will need to search in VoipDetailsview table.

    This was a statement I used:

    SELECT  *
      FROM [LcsCDR].[dbo].[VoipDetailsView]
      where ToUri like '%main%'or ToUri like '%accounting%' or ToUri like '%Helpdesk%' 

    This will search for sip uri that contains any of those words. You can replace it with name of your response group.

    When do this in Excel, you can filter out columns not needed. ToUri is for incoming call, if you want for outgoing call you can replace it by FromUri


    Please mark as helpful if you find my contribution useful or as an answer if it does answer your question. That will encourage me - and others - to take time out to help you. Thank you! Off2work


    • Edited by Off2work Monday, October 9, 2017 1:36 PM edit
    Monday, October 9, 2017 1:28 PM