Microsoft Lync Telephony System Billing – Part 1

Hello there,

It’s been a while since I posted anything, I was pretty busy for the last couple of months and now I found the time to share some information with you.

I was working recently on a Billing System for Microsoft Lync Telephony System. so i will post a couple of articles addressing ins and outs for the billing system and some tweaks to make your life easier .

I think i should start how to get billing information out of the Lync Database, and to be able to do so you need to install Lync monitoring Server or Role, which keeps track of every incoming and outgoing call, for those who don’t click here to read more. The end result for Monitoring server installation is to Have LSCDR Database with all the related tables populated and ready to go.

LSCDR Database doesn’t give you billing information out of the box so you need to make your hands dirty with MS-SQL a little bit to be able to get some valid billing information.

and the SQL Query that you have to do looks like below

To view all the code please refer to https://github.com/sghaida/Lync-Billing

SELECT  
	VoipDetails.SessionIdTime,  
	VoipDetails.SessionIdSeq,  
	Users_1.UserUri AS SourceUserUri,  
	Users_2.UserUri AS DestinationUserUri,  
	Phones.PhoneUri AS SourceNumberUri,  
	Phones_1.PhoneUri AS DestinationNumberUri,  
	MediationServers.MediationServer AS FromMediationServer,  
	MediationServers_1.MediationServer AS ToMediationServer,  
	Gateways.Gateway AS FromGateway,  
	Gateways_1.Gateway AS ToGateway,  
	EdgeServers.EdgeServer AS SourceUserEdgeServer,  
	EdgeServers_1.EdgeServer AS DestinationUserEdgeServer,  
	Servers.ServerFQDN,  
	Pools.PoolFQDN,  
	SessionDetails.ResponseTime,  
	SessionDetails.SessionEndTime,  
	CONVERT(decimal(8, 0),  
	DATEDIFF(second, SessionDetails.ResponseTime,  SessionDetails.SessionEndTime)) AS Duration  
FROM     SessionDetails  
	LEFT OUTER JOIN Servers ON SessionDetails.ServerId = Servers.ServerId  
	LEFT OUTER JOIN Pools ON SessionDetails.PoolId = Pools.PoolId  
	LEFT OUTER JOIN SIPResponseMetaData ON SessionDetails.ResponseCode = SIPResponseMetaData.ResponseCode  
	LEFT OUTER JOIN EdgeServers AS EdgeServers_1 ON SessionDetails.User2EdgeServerId = EdgeServers_1.EdgeServerId  
	LEFT OUTER JOIN EdgeServers ON SessionDetails.User1EdgeServerId = EdgeServers.EdgeServerId  
	LEFT OUTER JOIN Users AS Users_2 ON SessionDetails.User2Id = Users_2.UserId  
	LEFT OUTER JOIN Users AS Users_1 ON SessionDetails.User1Id = Users_1.UserId  
	RIGHT OUTER JOIN VoipDetails  
	LEFT OUTER JOIN Gateways AS Gateways_1 ON VoipDetails.ToGatewayId = Gateways_1.GatewayId  
	LEFT OUTER JOIN Gateways ON VoipDetails.FromGatewayId = Gateways.GatewayId  
	LEFT OUTER JOIN MediationServers AS MediationServers_1 ON VoipDetails.ToMediationServerId = MediationServers_1.MediationServerId  
	LEFT OUTER JOIN MediationServers ON VoipDetails.FromMediationServerId = MediationServers.MediationServerId  
	LEFT OUTER JOIN Phones AS Phones_1 ON VoipDetails.ConnectedNumberId = Phones_1.PhoneId  
	LEFT OUTER JOIN Phones ON VoipDetails.FromNumberId = Phones.PhoneId ON SessionDetails.SessionIdTime = VoipDetails.SessionIdTime AND  
	SessionDetails.SessionIdSeq = VoipDetails.SessionIdSeq 
WHERE  
	Users_1.UserUri IS NOT NULL AND  
	Users_1.UserUri NOT LIKE '%;phone%' AND
	Users_1.UserUri NOT LIKE '%;user%' AND  
	Users_1.UserUri NOT LIKE '+%@%' AND  
	SessionDetails.ResponseCode = 200 AND  
	SessionDetails.MediaTypes = 16

Note:

The Query above will give you billable output of all successful outgoing calls to another Lync client or to PSTN Gateway

Posted in Database, Programming, Technology, Windows and tagged , , , , , , , , , , . Bookmark the permalink. RSS feed for this post. Leave a trackback.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

Swedish Greys - a WordPress theme from Nordic Themepark.