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:
