"All Resources Picker Report" in DataCenter Types > Services webpart is running too long and causing timeout at the end
search cancel

"All Resources Picker Report" in DataCenter Types > Services webpart is running too long and causing timeout at the end


Article ID: 162146


Updated On:


IT Management Suite


"All resources Picker report" is being used in Home>Service and Asset management>Manage Configuration Items>DataCenter Types>Services webpart
In large environments it may take up to 8 minute to run underlying SQL query "All Resources Picker Query"{41e81023-8451-4c08-9eef-5095acf01abf}

DECLARE @ResourceType uniqueidentifier
   SET @ResourceType = 'cacccabb-225a-4611-83ce-5c56bd8de562'
DECLARE @TrusteeScope nvarchar(max)
   SET @TrusteeScope = '{2E1F478A-4986-4223-9D1E-B5920A63AB41},{451A954A-7A74-
DECLARE @Culture nvarchar(10)
   SET @Culture = 'pl-PL'
DECLARE @UnnamedResource nvarchar(max)
    SET @UnnamedResource = 
[dbo].fnLocalizeStringByGuid('text.UnnamedResource', cast(0x0 as 
uniqueidentifier), @Culture)
DECLARE @TrusteeScopeCollections table(Trustees nvarchar(max))
    INSERT INTO @TrusteeScopeCollections SELECT * FROM 
DECLARE @ResourceGuid table(ResourceGuid uniqueidentifier) 
    INSERT INTO @ResourceGuid select ResourceGuid from ScopeMembership 
    where ScopeCollectionGuid in (select ScopeCollectionGuid from 
DECLARE @ResourceTypeGuid table(ResourceTypeGuid uniqueidentifier) 
    INSERT INTO @ResourceTypeGuid select ResourceTypeGuid from 
    where BaseResourceTypeGuid = @ResourceType 
SELECT r.[Guid] AS [_ItemGuid],     
    rtl.String AS [Resource Type],
    case when (ril.String is null) or (ltrim(rtrim(ril.String))='')
    then @UnnamedResource else ril.String
    end as [Name],
    r.[ResourceTypeGuid] AS [_ResourceTypeGuid]  
FROM   [vRM_Resource_Item] r  
    outer apply 
fnGetLocalizedString(N'item.name',r.ResourceTypeGuid,@Culture) AS rtl
    outer apply fnGetLocalizedString(N'item.name',r.Guid,@Culture) AS ril
    r.Guid in (select ResourceGuid from @ResourceGuid)   
    and  ((r.ResourceTypeGuid in (select ResourceTypeGuid from 
    or    (@ResourceType = '00000000-0000-0000-0000-000000000000'))  
    [Resource Type] ASC,     
    [Name] ASC

Altiris Profiler trace will show more than 5 minutes processing. It will finish successfully on SQL side, but time outs in console.


There is a workaround to increase default timeout value for that webpart.
To do so, please open <Installpath\Altiris\CMDB\Web\AssetContractCommon\Web.config file in any text editor. On the config file locate following section:

    <!-- set aspnet execution timeout -->
    <httpCookies httpOnlyCookies="true" />
    <!-- Added For AJAX Support-->
    <!-- End of AJAX Support-->
    <httpRuntime executionTimeout="300" requestValidationMode="2.0" />
    <!--ensures that all web service protocols are enabled-->

Change default executionTimeout value from 300 to 600 > 10 minutes. Save file, restart IIS, then check the webpart. It will run as long as SQL qeury runs but won't timeout at the end.

Improved SQL code to decrease report run time will be added in 7.6 HF4.