Quick Tip: Get Subscriber Access Usage Stats

July 03, 2018

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
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.



Written by Richard Richard is an Office Apps & Services MVP (Teams / Skype) who lives in Minneapolis, MN. Microsoft Certified Solutions Master (MCSM) and MCSM Instructor - when those were a thing long ago. When not writing code, breaking teams - debate coach and avid golfer.
Follow on Twitter

Built using Gatsby and Material-UI

Copyright © TheArgyleMVP 2022.