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



These are just some random thoughts and ideas from a Microsoft MVP. We might cover everything from Development, to Teams, to Skype and might even through in a random bit of Azure and AWS


Built using Gatsby and Material-UI

Copyright © TheArgyleMVP 2020.