none
Response Group Usage Report not working

    Question

  • Hi Everyon

    I'm not sure this is the correct forum but here goes:

    We recently migrated to a Lync 2013 setup with SQL server mirror with a witness server.

    All reports are working except the Response Group Usage Report. When the report is run it never returns any data untill it finally times out.

    When I run the associated stored procedure manually I find the same behavior.

    Anyone have any ideas or suggestions on how to proceed?

    Br.

    Daniel

    Monday, December 9, 2013 8:02 AM

Answers

  • So I contacted MS support regarding this issue and it looks like recreating the index [IX_SessionDetails_CorrelationId_SessionIdTime] on [dbo].[SessionDetails] fixes the issue.  Here is the exact query ran on our Lync Monitoring SQL instance by the MS SQL Rep:

    /*
    USE [LcsCDR]
    GO
    DROP INDEX [IX_SessionDetails_CorrelationId_SessionIdTime] ON [dbo].[SessionDetails]
    GO
    */
    
    CREATE NONCLUSTERED INDEX [IX_SessionDetails_CorrelationId_SessionIdTime] ON [dbo].[SessionDetails]
    (
    	[CorrelationId] ASC,
    	[SessionIdTime] ASC,
    	[ReplacesDialogIdTime] ASC,
    	[ReplacesDialogIdSeq] ASC,
    	[CallFlag] ASC,
    	[MediaTypes] ASC,
    	[User1ClientVerId] ASC,
    	[User2ClientVerId] ASC,
    	[SessionIdSeq] ASC,
    	[SessionStartedById] ASC,
    	[User1Id] ASC,
    	[User2Id] ASC,
    	[ReferredById] ASC
    )
    INCLUDE ( 	[TargetUserId],
    	[ResponseTime],
    	[ResponseCode],
    	[SessionEndTime]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    go
    
    /*
    USE [LcsCDR]
    GO
    DROP INDEX [IX_SessionDetails_ReplacesDialogIdTime_SessionIdTime] ON [dbo].[SessionDetails]
    GO
    */
    
    CREATE NONCLUSTERED INDEX [IX_SessionDetails_ReplacesDialogIdTime_SessionIdTime] ON [dbo].[SessionDetails]
    (
    	[ReplacesDialogIdTime] ASC,
    	[SessionIdTime] ASC,
    	[ReplacesDialogIdSeq] ASC,
    	[CallFlag] ASC,
    	[MediaTypes] ASC,
    	[User1ClientVerId] ASC,
    	[User2ClientVerId] ASC,
    	[SessionIdSeq] ASC,
    	[SessionStartedById] ASC,
    	[User1Id] ASC,
    	[User2Id] ASC,
    	[CorrelationId] ASC,
    	[ReferredById] ASC
    )
    INCLUDE ( 	[TargetUserId],
    	[ResponseTime],
    	[ResponseCode],
    	[SessionEndTime]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    go

    Now the response group usage report runs even monthly reports in under a minute!

    Cheers!

    • Proposed as answer by Kyle M. Dixon Thursday, April 10, 2014 2:16 PM
    • Marked as answer by dhjensenWork Friday, April 11, 2014 9:56 AM
    Thursday, April 10, 2014 2:15 PM

All replies

  • Firstly update your Lync Server 2013 to the latest version.

    Did you check the response group works correctly after the migration?

    Try to create a new response group, then check if the sever can collect Response Group usage information.


    Lisa Zheng
    TechNet Community Support

    Tuesday, December 10, 2013 7:45 AM
    Moderator
  • Firstly update your Lync Server 2013 to the latest version.

    Did you check the response group works correctly after the migration?

    Try to create a new response group, then check if the sever can collect Response Group usage information.


    Lisa Zheng
    TechNet Community Support

    Sure I will make sure the servers are updated to the latest version.

    After migrating the response Group didnt Work and we had to recreate them... The agent Groups were duplicated and the calls where routed to the wrong ones.

    I can try to create a new responce Group. How would I check if the server can collect Response Group usage information?


    Tuesday, December 10, 2013 1:41 PM
  • Daniel Ignore the bland Lisa Zheng reply (I wish they did there homework to help people rather than reading a guide) as it's unhelpful to everyone!! Hope you read this Lisa!!! This. Is a know bug in the response group usage report. IF Daniel you ran the report with a 10 minute criteria selection between to from and to times it will bring back information. I feel it's either badly indexed as a report or there is something more fundamentally wrong with the usage report. Your interim solution is to go into the reporting services and select the child response group reports as these work perfectly. Hope this helps Regards Iain Smith
    Tuesday, December 10, 2013 8:11 PM
  • Daniel Ignore the bland Lisa Zheng reply (I wish they did there homework to help people rather than reading a guide) as it's unhelpful to everyone!! Hope you read this Lisa!!! This. Is a know bug in the response group usage report. IF Daniel you ran the report with a 10 minute criteria selection between to from and to times it will bring back information. I feel it's either badly indexed as a report or there is something more fundamentally wrong with the usage report. Your interim solution is to go into the reporting services and select the child response group reports as these work perfectly. Hope this helps Regards Iain Smith

    Hi Iain,

    Thx for your answer.

    Is it a bug you know Microsoft is working on fixing or should we open a case?

    Where are those "Child response Group" reports your refering to? I can find Response Group Call List Report? I can confirm this one works.

    Tuesday, December 10, 2013 8:42 PM
  • Hello Daniel This is 'acknowledged' by Microsoft meaning... Anything to be honest On the usage summary report you can select sub reports from the hyperlinks. So in the event of the buggy summary you could jump straight to the sun reports via report services. Regards Iain Smith
    Thursday, December 12, 2013 9:09 PM
  • I have the same problem as the OP.  So there's no fix or workaround to getting the Response Group Call Usage report to work?  The Response Group Call List report is useful but in no provides a summary or quick view of traffic on response groups.  Wondering if MS has/had anything to say about it?
    Wednesday, January 8, 2014 4:16 PM
  • I have the same problem as the OP.  So there's no fix or workaround to getting the Response Group Call Usage report to work?  The Response Group Call List report is useful but in no provides a summary or quick view of traffic on response groups.  Wondering if MS has/had anything to say about it?

    Our Issue remains.

    We haven't done anything else and yes your problem looks like it's an exact match to ours.

    We haven't contacted Microsoft about this issue, but please if you do let me know the response :)

    /Daniel

    Wednesday, January 22, 2014 1:15 PM
  • Hi, I have this problem and work around it as below - not a solution but still worth knowing if you haven't worked it out yet.

    While your waiting for the report to generate, can you please click cancel in order for the time frame filters to become active at the top of the screen, and then reduce the time frame windows to something like an hour and then click view report.

    Unlike some of the other reports, the response group report is spanning 7 days by default. I have exactly the same problem if I let it try to pull reports back for the default 7 days. But ammending my time frame window after cancel the intitial GET works fine for me.

    The only time I have been able to retrieve a full 7 day report was using an alternate brower (Chrome in this case)

    Kind regards
    Ben

    Wednesday, January 22, 2014 2:01 PM
  • Hi, I have this problem and work around it as below - not a solution but still worth knowing if you haven't worked it out yet.

    While your waiting for the report to generate, can you please click cancel in order for the time frame filters to become active at the top of the screen, and then reduce the time frame windows to something like an hour and then click view report.

    Unlike some of the other reports, the response group report is spanning 7 days by default. I have exactly the same problem if I let it try to pull reports back for the default 7 days. But ammending my time frame window after cancel the intitial GET works fine for me.

    The only time I have been able to retrieve a full 7 day report was using an alternate brower (Chrome in this case)

    Kind regards
    Ben

    Hi Ben,

    Thank for your workaround which is much like what "Iain" suggested. Yes I can do a report like this some times. Only that I might have to reduce it to a 10-15 min interval, which Again is more or less useless :-)

    We have a system with like 350 lync users and about 30 RGS so I dont know if that have any impact.

    We are moving the SQL server to a faster SAN, that might change how fast the Associated stored procedure runs. 

    I will let you Guys know of our findings.

    /Daniel

    Wednesday, January 22, 2014 2:44 PM
  • So I contacted MS support regarding this issue and it looks like recreating the index [IX_SessionDetails_CorrelationId_SessionIdTime] on [dbo].[SessionDetails] fixes the issue.  Here is the exact query ran on our Lync Monitoring SQL instance by the MS SQL Rep:

    /*
    USE [LcsCDR]
    GO
    DROP INDEX [IX_SessionDetails_CorrelationId_SessionIdTime] ON [dbo].[SessionDetails]
    GO
    */
    
    CREATE NONCLUSTERED INDEX [IX_SessionDetails_CorrelationId_SessionIdTime] ON [dbo].[SessionDetails]
    (
    	[CorrelationId] ASC,
    	[SessionIdTime] ASC,
    	[ReplacesDialogIdTime] ASC,
    	[ReplacesDialogIdSeq] ASC,
    	[CallFlag] ASC,
    	[MediaTypes] ASC,
    	[User1ClientVerId] ASC,
    	[User2ClientVerId] ASC,
    	[SessionIdSeq] ASC,
    	[SessionStartedById] ASC,
    	[User1Id] ASC,
    	[User2Id] ASC,
    	[ReferredById] ASC
    )
    INCLUDE ( 	[TargetUserId],
    	[ResponseTime],
    	[ResponseCode],
    	[SessionEndTime]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    go
    
    /*
    USE [LcsCDR]
    GO
    DROP INDEX [IX_SessionDetails_ReplacesDialogIdTime_SessionIdTime] ON [dbo].[SessionDetails]
    GO
    */
    
    CREATE NONCLUSTERED INDEX [IX_SessionDetails_ReplacesDialogIdTime_SessionIdTime] ON [dbo].[SessionDetails]
    (
    	[ReplacesDialogIdTime] ASC,
    	[SessionIdTime] ASC,
    	[ReplacesDialogIdSeq] ASC,
    	[CallFlag] ASC,
    	[MediaTypes] ASC,
    	[User1ClientVerId] ASC,
    	[User2ClientVerId] ASC,
    	[SessionIdSeq] ASC,
    	[SessionStartedById] ASC,
    	[User1Id] ASC,
    	[User2Id] ASC,
    	[CorrelationId] ASC,
    	[ReferredById] ASC
    )
    INCLUDE ( 	[TargetUserId],
    	[ResponseTime],
    	[ResponseCode],
    	[SessionEndTime]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    go

    Now the response group usage report runs even monthly reports in under a minute!

    Cheers!

    • Proposed as answer by Kyle M. Dixon Thursday, April 10, 2014 2:16 PM
    • Marked as answer by dhjensenWork Friday, April 11, 2014 9:56 AM
    Thursday, April 10, 2014 2:15 PM
  • Hi 13lind,

    Thx alot for posting this solution that works perfectly.

    However the SQL script does not recreate the index as the index does not exist to start with. Thats probably the reason the report is so slow to create.

    The drop part of the SQL is commented out. Uncommenting it will tell you that the index does not exist.

    Br.

    Daniel



    Friday, April 11, 2014 9:56 AM
  • That made a big improvement for me.  It should be posted somewhere in the Lync community where it will get better visibility.

    Tuesday, October 7, 2014 3:27 PM
  • That made a big improvement for me.  It should be posted somewhere in the Lync community where it will get better visibility.

    I created and post it in a blog here: http://blog.dhjensen.dk/2014/12/lync-response-group-usage-report-very.html


    Tuesday, December 16, 2014 2:45 PM
  • Has anyone tried this fix on Skype for Business Server 2015? I have the same issue with this report.

    I know there are some differences in the database schema which is why I ask.

    Thanks,

    Thursday, May 5, 2016 2:10 PM
  • I have now tried this on Skype for Business Server 2015.

    I can confirm that is works and that the performance of the affected report is now a lot better.

    Thanks,

    Tuesday, May 10, 2016 5:23 PM
  • Trying to run this report on our SfB server results in cpus pegged for 30 minutes and then it erroring out.

    I tried the sql query provided but I get this:

    Msg 1088, Level 16, State 12, Line 8
    Cannot find the object "dbo.SessionDetails" because it does not exist or you do not have permissions.
    Msg 1088, Level 16, State 12, Line 37
    Cannot find the object "dbo.SessionDetails" because it does not exist or you do not have permissions.

    Thursday, January 12, 2017 12:04 AM
  • Nevermind, had to run it against LCScdr, duh.
    Thursday, January 12, 2017 12:07 AM
  • I would however request that you perform a full back up. But I can concur that this worked for me in 2018.

    robinhood_montreal


    Friday, April 13, 2018 2:39 PM
  • Tested ok on Skype for Business 2015 with SQL 2008 R2 backend. Thanks!
    Thursday, January 24, 2019 7:33 AM
  • Hi Kyle,

    We have a client that is experiencing the same issue and would like to try running the above query in SQL.  We are hesitant because of the size of company and their infrastructure.  I'll also admit I'm not a solid SQL guy.  Did Microsoft happen to mention what permissions are needed in the SQL server to run this query and make the changes to the index?


    John K. Boslooper | Sr. Voice Engineer | Lutheran Social Services of Michigan

    Thursday, May 2, 2019 1:58 PM
  • This has worked for me on latest CU for Skype for Business 2015 and SQL Backend 2014.

    Mohammed JH

    Thursday, May 9, 2019 10:06 PM