none
How to Get the Pstn Outbound call Report

    Question

  • Hi,

    How can we get the detailed PSTN call Report. Like From Number to Number Call Duration : Start -End 


    Whenever you see a helpful reply, click on Vote As Helpful & click on Mark As Answer if a post answers your question.

    Tuesday, November 19, 2013 3:23 PM

Answers

  • Thanks For the reply i am able to get the reports by SQL Query.

    Here is the code if any one looking for :)

    Use LcsCDR
    SELECT  VoipDetails.SessionIdTime [Date],
            CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
            Phones.PhoneUri [From Number],
            Users1.UserUri [From Sip],
            Phones1.PhoneUri [Number Dialed]


    FROM         VoipDetails LEFT OUTER JOIN SessionDetails 
    ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq 
    LEFT OUTER JOIN  Phones 
    ON VoipDetails.FromNumberId = Phones.PhoneId 
    LEFT OUTER JOIN  Phones Phones1 
    ON VoipDetails.ConnectedNumberId = Phones1.PhoneId 
    LEFT OUTER JOIN  Users Users1 
    ON SessionDetails.User1Id = Users1.UserId 

    where SessionDetails.ResponseCode = 200     
    order by VoipDetails.SessionIdTime


    Whenever you see a helpful reply, click on Vote As Helpful & click on Mark As Answer if a post answers your question.

    • Proposed as answer by Murali Krishnan - Friday, November 22, 2013 9:10 AM
    • Marked as answer by Kent-Huang Sunday, December 8, 2013 9:01 AM
    Friday, November 22, 2013 7:49 AM

All replies

  • Hi, you have to install Lync Monitoring to get these (and many Others) info.
    Regards
    Luca

    Tuesday, November 19, 2013 3:25 PM
  • For Lync 2010 (assuming based on the forum you posted in), you'll need to have the monitoring role deployed.

    http://technet.microsoft.com/en-us/library/gg412793(v=ocs.14).aspx

    Then after a few calls, everything you'll need is in that SQL database and the included reports:

    http://technet.microsoft.com/en-us/library/gg558662(v=ocs.14).aspx


    Please remember, if you see a post that helped you please click "Vote As Helpful" and if it answered your question please click "Mark As Answer". http://www.swc.com/solutions/unified-communications

    Tuesday, November 19, 2013 3:26 PM
  • We Already have Monitoring server. But it doesn't give the exact report 


    Whenever you see a helpful reply, click on Vote As Helpful & click on Mark As Answer if a post answers your question.

    We are looking for PSTN Outbound Report Like

    From : Number 

    To : Number

    Date:

    Call Duration:

    • Edited by Raju_raju Tuesday, November 19, 2013 3:59 PM added
    Tuesday, November 19, 2013 3:47 PM
  • If you want a specific format, you'll need to write a custom SQL query or report.  For 2010, the three tables you'll want to be looking at are SessionDetails, VoIPDetails, and Users.

     


    Please remember, if you see a post that helped you please click "Vote As Helpful" and if it answered your question please click "Mark As Answer". http://www.swc.com/solutions/unified-communications

    Tuesday, November 19, 2013 4:29 PM
  • Hi,

    As Anthony mentioned, all the information you require is in the SQL Db's. just need to extract the info using relevant sql queries if you have sql expertise in house. Else you might want to look at thirdparty solutions such as the ones you see under Billing and enhanced reporting.  

    http://technet.microsoft.com/en-us/lync/hh972602.aspx

    Hope that helps.


    Murali Krishnan| My blogs: UnifiedMe | Twitter: @Mkris9

    Tuesday, November 19, 2013 4:58 PM
  • Hi,

    If you want create a custom SQL query to solve you issue you can refer to the link about How to Create Additional Lync Server 2010 Call Details Reports:

    http://blogs.technet.com/b/nexthop/archive/2012/01/09/how-to-create-additional-lync-server-2010-call-details-reports-part-1.aspx

    Best Regards,

    Eason Huang


    Eason Huang
    TechNet Community Support

    Thursday, November 21, 2013 1:30 AM
    Moderator
  • Thanks For the reply i am able to get the reports by SQL Query.

    Here is the code if any one looking for :)

    Use LcsCDR
    SELECT  VoipDetails.SessionIdTime [Date],
            CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
            Phones.PhoneUri [From Number],
            Users1.UserUri [From Sip],
            Phones1.PhoneUri [Number Dialed]


    FROM         VoipDetails LEFT OUTER JOIN SessionDetails 
    ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq 
    LEFT OUTER JOIN  Phones 
    ON VoipDetails.FromNumberId = Phones.PhoneId 
    LEFT OUTER JOIN  Phones Phones1 
    ON VoipDetails.ConnectedNumberId = Phones1.PhoneId 
    LEFT OUTER JOIN  Users Users1 
    ON SessionDetails.User1Id = Users1.UserId 

    where SessionDetails.ResponseCode = 200     
    order by VoipDetails.SessionIdTime


    Whenever you see a helpful reply, click on Vote As Helpful & click on Mark As Answer if a post answers your question.

    • Proposed as answer by Murali Krishnan - Friday, November 22, 2013 9:10 AM
    • Marked as answer by Kent-Huang Sunday, December 8, 2013 9:01 AM
    Friday, November 22, 2013 7:49 AM
  • Hi,

    How can I export report between two dates. For example, I want a report of PSTN calls made between 1st to 7th. Is it possible?

    Thanks,

    Tejas

    Monday, March 6, 2017 11:25 AM