Quick Tip: Get Subscriber Access Usage Stats
Posted on July 03, 2018 by Richard Brynteson in Development
So recently I had a task to figure out how often Subscriber Access was being used in an environment. Thankfully all of the data you need is in the SQL Database, specifically in the LcsCDR database.
/****** - Author: Richard Brynteson, T2M - Date: 7/3/2018 - Version: 0.9 - - Instructions: Set the variables for the Subscriber Access Name and time range ******/ USE LcsCDR; DECLARE @SAName nvarchar(50); DECLARE @StartTime datetime; DECLARE @EndTime datetime; SET @SAName = 'email@example.com' SET @StartTime = '4/1/2018' SET @EndTime = '7/31/2018' SELECT SUM(SessionIdSeq) AS NumSessions, FromUriType, DATEPART(mm, SessionIdTime) as MonthNum, DATEPART(YEAR, SessionIdTime) as YearNum FROM [LcsCDR].[dbo].[SessionDetailsView] WHERE ToUri = @SAName AND InviteTime BETWEEN @StartTime AND @EndTime GROUP BY FromUriType, DATEPART(mm, SessionIdTime), DATEPART(YEAR, SessionIdTime) ORDER BY YearNum, MonthNum, FromUriType
So in this script, all you need to do is to update the three variables to the items to your specific needs and run the script connected to the CDR Database.
The expected output would be:
The output will show you Subscriber Access number of calls separated by PSTN and Client calls into the SA Service.