Timeout or HTTP Error 500 when running Summary compliance for a configuration item by computer

Report timeouts due to V_CIRelation_All and Forefront Definition Updates.

We recently had a customer who ran into a problem with certain ConfigMgr Reports timing out, more specifically the reports that use the view V_CIRelation_All (which is the view that tracks the relationship of all configuration items. All updates are considered Configuration Items so this view will contain data if you’re synchronizing software updates or have created any configuration items.).

Under some circumstances when you synchronize forefront definition updates this view will contain too many rows for the report to generate before the timeout is reached.

To verify if you have this problem you can try running “SELECT COUNT(*) FROM V_CIRelation_All” against this view to see how many rows this view contains, note that this query will generate quite a bit of CPU load on the SQL server (another probable outcome is that this query will run for anywhere between several minutes to several hours, in that case abort the query).

If you have this problem you should contact Microsoft Premium Support to make sure you get a supported configuration afterwards but if you’re just running this in a lab environment here’s how you can fix it (This solution is provided AS IS without warranty of any kind):

  •     Open SQL Management Studio (if this is installed locally on your SQL server it might be necessary to run as administrator).
  •     Open a new query and paste the following. The code will create the view v_CIRelation_All_Alternative:

—Code begins below this—

Create VIEW [dbo].[v_CIRelation_All_Alternative] AS
SELECT CI_ID=FromCI_ID, ReferencedCI_ID=ToCI_ID, Level=RelationDepth FROM CI_ConfigurationItemRelations_Flat

—Code ends above this—

  •     Configure the same permissions on this view as on the old view V_CIRelation_All
  •     After you have verified that the view has been created run the following query to verify that the new view is working:

—Code begins below this—

SELECT * FROM v_CIRelation_All_Alternative

—Code ends above this—

  •     Duplicate the report that does not work, edit the sql query in it and replace all occurences of “V_CIRelation_All” with “v_CIRelation_All_Alternative”.
  •     Run the new report to verify that it’s working.

That’s it!

Best regards,

Jonas Lagerström

Posted in Blog.