Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

ASDK stored procedure spASDKGetItemsByType

Created: 20 Oct 2011 • Updated: 25 Oct 2011
Language Translations
bmc_saxman's picture
0 0 Votes
Login to vote

If you are using the Administrator SDK method GetItemsByType you may be experiencing some timeout issues in larger enterprise environments. This is due to the SQL stored procedure spASDKGetItemsByType not being as properly written/optimized as it should be.

We have the following SQL that can be used to replace the above mentioned stored procedure which corrects the timeout problem. The following SQL code should alter the existing stored procedure and give you the needed fixes to help this stored procedure run more efficiently.

All you need to do is copy the SQL below and paste it into a query window against your current CMDB database and run the query. It will automatically update the existing stored procedure.

 

spASDKGetItemsByType

 

ALTER proc [dbo].[spASDKGetItemsByType]
           @Culture char(10),
           @TypeName nvarchar(2048)
        as  

create table #asdkTmp
                (Guid uniqueidentifier primary key clustered,
                ClassGuid uniqueidentifier,
                Attributes int
                )
create index asdkTmp_Class on #asdkTmp(ClassGuid)

declare @TypeGuids table
                (ClassGuid uniqueidentifier
                )

insert into @TypeGuids
select Guid from Class where Type = @TypeName

if not exists (select * from @TypeGuids)
begin
                insert into @TypeGuids
                select
                                BaseGuid
                from
                                String
                where 1 = 1
                                and StringRef in ('creatableclass', 'itemtype')
                                and Culture = ''
                                and String = @TypeName
end

insert into #asdkTmp (Guid, ClassGuid, Attributes)
select distinct
                i.Guid,
                i.ClassGuid,
                i.Attributes
from
                vItem i join
                Class on i.ClassGuid=Class.Guid join
                ClassBaseClass CBC on Class.Guid=CBC.ClassGuid left outer join
                vItemFolder ifldr on i.Guid=ifldr.ItemGuid join
                @TypeGuids tg on CBC.BaseClassGuid = tg.ClassGuid

-- table 0  
select
                tmp.Guid Guid,  
                st1.String Name,  
                st2.String Description,  
                Class.Type ClassTypeName,   
                Class.Guid ClassTypeGuid,  
                st3.String ParentFolderName,  
                ifl.ParentFolderGuid ParentFolderGuid,  
                tmp.Attributes,
                case when cbc.BaseClassGuid is not null then 1 else 0 end 
IsPolicy,
                ISNULL(ia.Enabled, 0) Enabled,
                case when ci.InterfaceGuid is not null then 1 else 0 end 
IsSchedulableItem
FROM
                #asdkTmp tmp join
                Class on tmp.ClassGuid=Class.Guid left outer join
                vItemFolder ifl on tmp.Guid=ifl.ItemGuid left outer join
                ItemActive ia on tmp.Guid=ia.Guid left outer join
                vStringCache st1 ON tmp.Guid=st1.BaseGuid
                                and st1.StringRef='item.name'
                                and st1.Culture=@Culture left outer join
                vStringCache st2 ON tmp.Guid=st2.BaseGuid
                                and st2.StringRef='item.description'
                                and st2.Culture=@Culture left outer join
                vStringCache st3 ON ifl.ParentFolderGuid=st3.BaseGuid
                                and st3.StringRef='item.name'
                                and st3.Culture=@Culture left outer join
                ClassBaseClass cbc on tmp.ClassGuid = cbc.ClassGuid
                                and cbc.BaseClassGuid = '10B9F151-82D6-4e00-BFD8-3C75C1DF8BDD' left outer join
                ClassInterface ci on tmp.ClassGuid = ci.ClassGuid
                                and ci.InterfaceGuid = '0E88E0BC-4D27-4421-A302-5336AB3D62BB'

-- table 1
select
                iat.ItemGuid ItemGuid,
                iat.ResourceTargetGuid  
from
                ItemAppliesTo iat join 
                #asdkTmp tmp on tmp.Guid = iat.ItemGuid  

-- table 2  
select
                item.Guid,
                item.State 
from
                vItem item join 
                #asdkTmp tmp on tmp.Guid=item.Guid

-- table 3
select
                item.Guid,
                item.SecurityGuid,
                case when res.Guid is not null then 1 else 0 end IsResource
from
                #asdkTmp tmp join
                vItem item  on tmp.Guid = item.Guid left outer join
                ItemResource res on tmp.Guid = res.Guid

drop table #asdkTmp