Friday 23 April 2010

Useful OCS SQL queries (Part II)

Another day, another query. This time, a more important one that can be used for accounting.

This query will show all the completed calls to the PSTN, showing call time, who dialed and where to. Advanced reporting could be built on top of this query to find out how much this calls cost.

Use LcsCDR
SELECT    CONVERT(varchar(10), VoipDetails.SessionIdTime, 103) [Start Date],
        CONVERT(varchar(10), VoipDetails.SessionIdTime, 108) [Start Time],
        CONVERT(varchar(10), SessionDetails.SessionEndTime, 103) [End Date],
        CONVERT(varchar(10), SessionDetails.SessionEndTime, 108) [End Time],
        CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
        REPLACE(REPLACE(Phones.PhoneUri,';phone-context=LOCATIONPROFILEHERE',''),';phone-context=enterprise','') [From Number],
        Users1.UserUri [From Sip],

        REPLACE(REPLACE(Phones1.PhoneUri,';phone-context=LOCATIONPROFILEHERE',''),';phone-context=enterprise','') [Number Dialed],
        Users3.UserUri [On Behalf Of],
        Users4.UserUri [Referred By]

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 LEFT OUTER JOIN
                      Users Users2 ON SessionDetails.User2Id = Users2.UserId
                      LEFT OUTER JOIN Users Users3 ON SessionDetails.OnBehalfOfId = Users3.UserId
                    LEFT OUTER JOIN Users Users4 ON SessionDetails.ReferredById = Users4.UserId

where SessionDetails.ResponseCode = 200 and
    Users2.UserUri is null and
    SessionDetails.SessionEndTime is not null
order by VoipDetails.SessionIdTime
You need to change the red text to match your profile(s) so it 'cleans' the number.

Outcome:

16 comments:

  1. hi,

    thanks for the sql query. But I have a question about the log. At this moment we only can query the last 2 months. How and where can we change the setting the keep more than 2 months in the ocs log tables?

    ReplyDelete
  2. Hello Filip,

    You can find that option on the OCS MMC:

    Right click your monitoring server and select properties.
    You can config your maximum days logged there.

    ReplyDelete
  3. ann.perretti@kraft.com4 August 2010 at 19:29

    Thanks for posting this query. It was very helpful to me. I was wondering if you could take a look at a query I have to pull conference call users. My objective is to identify each participant and host on one conference call. This looks like it is giving me what I need but I am not confident it is correct based on the fact the end number I come up with doesn't match the "canned" conference participant count report. Thanks in advance for any advice.

    Select Convert(varchar(10),Conferences.ConferenceStartTime,103) [Start Date],
    Convert(varchar(10),Conferences.ConferenceStartTime,108) [Start Time],
    Convert(varchar(10),Conferences.ConferenceEndTime,103) [End Date],
    Convert(varchar(10),Conferences.ConferenceEndTime,108) [End Time],
    Convert(varchar(10),Conferences.ConferenceEndTime -
    Conferences.ConferenceStartTime,108) Time, FocusJoinsAndLeaves.UserRole,
    FocusJoinsAndLeaves.UserId, Users.UserUri, Conferences.OrganizerId
    From Conferences Inner Join
    FocusJoinsAndLeaves On Conferences.SessionIdTime =
    FocusJoinsAndLeaves.SessionIdTime And Conferences.SessionIdSeq =
    FocusJoinsAndLeaves.SessionIdSeq Inner Join
    Users On Users.UserId = FocusJoinsAndLeaves.UserId,
    Mcus
    Where Conferences.ConferenceStartTime >= '06-01-2010' And
    Conferences.ConferenceStartTime <= '06-30-2010' And dbo.Mcus.McuType =
    'audio-video'

    ReplyDelete
  4. thank you for the post , it helps alot

    ReplyDelete
  5. You are a complete whizzard and sql guru.
    Your code solves a real problem I had in manipulating the lync reports to form a charge sheet for users.
    Fabulous, and many thanks for taking the trouble to make it public.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. You are welcome JBaker.
    Thanks for the comment!

    ReplyDelete
  8. hi,

    what sql can i use the get this report: i want a report that show me which pstn numbers called us on ocs.

    how can i do that?

    Filip

    ReplyDelete
  9. Hello Filip,

    At the moment, due to personal life events, i am unable to help you with this query. I'll try and get it for you as soon as possible.

    ETA is maybe 2/3 weeks.

    ReplyDelete
  10. Is it possible to generate a Conference Report that would tell me who initaited a conference, the date the conference was initiated, duration of the conference and the number of participants who joined the conference in OCS 2007. If yes, can you please help me with the SQL query? Thanks.

    ReplyDelete
  11. Yeah, im sure it is.

    But atm i have no way to try it as i have no Ocs 2007 Database with me.

    If you are willing to share a stripped backup from your OCS 2007 with me i'm sure i can figure something out.

    ReplyDelete
  12. Hi Hugo,

    Nice query. Have you updated it to Lync 2013 yet? Perhaps we can exchange some knowledge? We have a multi tentant Lync environment and I have created some scripts for billing (including initiated conferences) but perhaps we can integrate some knowledge... let me know: lburkels(at)parentix.nl

    ReplyDelete
  13. Hi,

    Thanks.

    I haven't had the time to do anything similar for Lync 2013 yet no. Unfortunately i'm way to much busy with work and out of work events, can't even keep up with the update of the blog itself :)

    Hopefully it will be better soon and i'll be able to look into that.

    ReplyDelete
  14. What is the "location Profile here"

    ReplyDelete
  15. Hi Ramesh,

    OCS had location profiles, which are now in the Lync world called Dial Plans.

    ReplyDelete
  16. This query used for find Lync user to PSTN user details.....
    Devcentrics.com

    SELECT sd.SessionIdTime, sd.SessionIdSeq, sd.InviteTime, sd.FromUri, sd.ToUri, sd.FromUriType, sd.ToUriType, sd.FromTenant, sd.ToTenant, sd.FromEndpointId,
    sd.ToEndpointId, sd.EndTime, sd.FromMessageCount, sd.ToMessageCount, sd.FromClientVersion, sd.FromClientType, sd.FromClientCategory, sd.ToClientVersion,
    sd.ToClientType, sd.ToClientCategory, sd.TargetUri, sd.TargetUriType, sd.TargetTenant, sd.OnBehalfOfUri, sd.OnBehalfOfUriType, sd.OnBehalfOfTenant,
    sd.ReferredByUri, sd.ReferredByUriType, sd.ReferredByTenant, sd.DialogId, sd.CorrelationId, sd.ReplacesDialogIdTime, sd.ReplacesDialogIdSeq,
    sd.ReplacesDialogId, sd.ResponseTime, sd.ResponseCode, sd.DiagnosticId, sd.ContentType, sd.FrontEnd, sd.Pool, sd.FromEdgeServer, sd.ToEdgeServer,
    sd.IsFromInternal, sd.IsToInternal, sd.CallPriority, sd.MediaTypes, sd.FromUserFlag, sd.ToUserFlag, sd.CallFlag, sd.Location, p1.PhoneUri AS FromPhone,
    p2.PhoneUri AS ToPhone, u.UserUri AS DisconnectedByUri, u.UserUriType AS DisconnectedByUriType, u.TenantKey AS DisconnectedByTenant,
    p3.PhoneUri AS DisconnectedByPhone, ms1.MediationServer AS FromMediationServer, ms2.MediationServer AS ToMediationServer, gw1.Gateway AS FromGateway,
    gw2.Gateway AS ToGateway
    FROM dbo.VoipDetails AS vd LEFT OUTER JOIN
    dbo.SessionDetailsView AS sd ON vd.SessionIdTime = sd.SessionIdTime AND vd.SessionIdSeq = sd.SessionIdSeq LEFT OUTER JOIN
    dbo.UsersView AS u ON vd.DisconnectedByURIId = u.UserId LEFT OUTER JOIN
    dbo.Phones AS p1 ON vd.FromNumberId = p1.PhoneId LEFT OUTER JOIN
    dbo.Phones AS p2 ON vd.ConnectedNumberId = p2.PhoneId LEFT OUTER JOIN
    dbo.Phones AS p3 ON vd.DisconnectedByPhoneId = p3.PhoneId LEFT OUTER JOIN
    dbo.MediationServers AS ms1 ON vd.FromMediationServerId = ms1.MediationServerId LEFT OUTER JOIN
    dbo.MediationServers AS ms2 ON vd.ToMediationServerId = ms2.MediationServerId LEFT OUTER JOIN
    dbo.Gateways AS gw1 ON vd.FromGatewayId = gw1.GatewayId LEFT OUTER JOIN
    dbo.Gateways AS gw2 ON vd.ToGatewayId = gw2.GatewayId
    where sd.MediaTypes=16 and ( ms1.MediationServer is null and ms2.MediationServer ='host-17.app.devcn.com' )
    and (sd.FromUriType='UserUri' and sd.ToUriType='PhoneUri')

    ReplyDelete