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 LcsCDRYou need to change the red text to match your profile(s) so it 'cleans' the number.
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
Outcome:
hi,
ReplyDeletethanks 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?
Hello Filip,
ReplyDeleteYou can find that option on the OCS MMC:
Right click your monitoring server and select properties.
You can config your maximum days logged there.
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.
ReplyDeleteSelect 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'
thank you for the post , it helps alot
ReplyDeleteYou are a complete whizzard and sql guru.
ReplyDeleteYour 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.
This comment has been removed by the author.
ReplyDeleteYou are welcome JBaker.
ReplyDeleteThanks for the comment!
hi,
ReplyDeletewhat 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
Hello Filip,
ReplyDeleteAt 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.
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.
ReplyDeleteYeah, im sure it is.
ReplyDeleteBut 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.
Hi Hugo,
ReplyDeleteNice 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
Hi,
ReplyDeleteThanks.
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.
What is the "location Profile here"
ReplyDeleteHi Ramesh,
ReplyDeleteOCS had location profiles, which are now in the Lync world called Dial Plans.
This query used for find Lync user to PSTN user details.....
ReplyDeleteDevcentrics.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')