Recently I got a request to fetch details of all failed sessions. In Lync reporting, we have an option to fetch report but it is not as descriptive as we need. So after exploring many articles, I finally was able to write a SQL query that gives failed sessions with the reason. You may modify the query as per your requirement. I’ll be updating this query further to include many more required details.
NOTE: This query fetches data from the database, and does not delete anything. While modifying the query, make sure that you do not drop any database or table. If required, engage SQL expert to recheck your query.
declare @Begindate datetime, @Enddate datetime
Select @Begindate = ‘2/1/2017’
Select @Enddate = ‘2/28/2017’
Select u.UserId as ‘User ID’, u.UserUri as “User Name”, sd.ReferredById as ‘Forwarded By ID’, sd.ResponseCode, si.Description as ‘Error Description’, sd.InviteTime as ‘Call Began’, sd.SessionEndTime as ‘Call Ended’
From Users u
INNER JOIN SessionDetails sd ON u.UserId = sd.ReferredById
INNER JOIN SIPResponseMetaData si ON sd.ResponseCode = si.ResponseCode
Where sd.ResponseCode != 200 AND sd.ResponseCode != 600 AND sd.InviteTime > @Begindate And sd.InviteTime < @Enddate