Quick Tip: Get Subscriber Access Usage Stats

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 = 'sa@t2mdev.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.

 

One thought on “Quick Tip: Get Subscriber Access Usage Stats

Leave a Reply

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