Finding an Object in the Console

I put together a report that locates where in the console an object resides – for instance, if you are looking for software package “Windows 7 x64 ABCD Software” and can’t find it in the folder structure, this report will help.

objects

I didn’t bother with the Configuration Baselines, Configuration Items…we don’t use them much and the folder structure is basically limited to one folder named “Terence”… and Software Updates are in the root, so no need there to hack that one out.

Here are the queries for the parameters.

Object Type

select distinct ObjectType, ObjectTypeName from vFolderMembers

where ObjectType in (6000, 19, 5000, 5001, 25, 23, 18, 9, 2, 7, 20)

order by ObjectTypeName

Once the Object Type is specified, this query populates the Name drop down:

select SecurityKey InstanceKey, Name + ‘ (‘ + SecurityKey + ‘)’ Name from v_Package

where ObjectTypeID =

case

when @ObjectType = 6000 /* Application */ then 31

else @ObjectType

end

union all

/* Collections */

select CollectionID InstanceKey, Name + ‘ (‘ + CollectionID + ‘)’ Name from v_Collection

where CollectionType =

case

when @ObjectType = 5000 /* Device Collection */ then 2

when @ObjectType = 5001 /* User Collection */ then 1

else 0

end

union all

/* Queries (not the built in ones) */

select QueryID InstanceKey, Name + ‘ (‘ + QueryID + ‘)’ Name from v_Query where @ObjectType = 7 and QueryID not like ‘SMS%’

union all

/* Metered Product Rules */

select SecurityKey InstanceKey, ProductName + ‘ (‘ + SecurityKey + ‘)’ Name from v_MeteredProductRule where @ObjectType = 9

union all

/* Drivers */

select CI_UniqueID InstanceKey, DriverINFFile + ‘ ‘ + DriverVersion + ‘ (‘ + convert (varchar, DriverDate, 110) + ‘)’ from v_CI_DriversCIs where @ObjectType = 25

order by 2

Once the parameters are specified, the instance key and object type are passed to a function that builds the string (function defined in a supplemental database).

create function vFolderMembers$GetFolderStructure

(

@InstanceKey varchar (max),

@ObjectType int

)

returns varchar (max)

as

begin

declare @ContainerNodeID int = (select ContainerNodeID from CM_XXX.dbo.vFolderMembers where InstanceKey = @InstanceKey and ObjectType = @ObjectType)

declare @LinkedList nvarchar (max) = (select Name from CM_XXX.dbo.vSMS_Folders where ContainerNodeID = @ContainerNodeID)

while (1 = 1)

begin

select @ContainerNodeID = p.ContainerNodeID from CM_XXX.dbo.vSMS_Folders c

inner join CM_XXX.dbo.vSMS_Folders p on p.ContainerNodeID = c.ParentContainerNodeID

where c.ContainerNodeID = @ContainerNodeID

if @@rowcount = 0

break

set @LinkedList = (select Name from CM_XXX.dbo.vSMS_Folders where ContainerNodeID = @ContainerNodeID) + ‘\’ + @LinkedList

end

return @LinkedList

end

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s