Example Query that shows contents of a specific Folder, its sub-folders, its Filters, and Computers on those Filters

Products

• IT Management Suite

Issue/Introduction

You are trying to accomplish the following:

1. You need a report/query where you can specify one of your "folders" that contains custom Filters.
2. The report/query should return/display all the "sub-folders" that this "parent/root" folder contains.
3. The report/query should return/display all the Filters that this "parent/root" folder and "sub-folders" contain.
4. The report/query should then return/display all the computers that those filters currently have a membership for.
5. The report/query should have columns where you can see "main/root" folder, a specific "computer", and "OS" version.

Environment

ITMS 8.7.x

Resolution

--Option 1:
--Simplified version

--Example query where root folder GUID is '448cd45e-9e1e-47c7-b33d-a261911c3ee9'

DECLARE @FolderGuid uniqueidentifier
SET @FolderGuid = '448cd45e-9e1e-47c7-b33d-a261911c3ee9' --Replace with Root Folder GUID 

SELECT vc.[Name]
    , vc.[OS Name]
    , vi.[Name] as [Filter Name]
    , ifo.[Name] as [Folder]
FROM  FolderBaseFolder fo
    JOIN ItemFolder vfo on vfo.ParentFolderGuid = fo.FolderGuid
    JOIN [Collection] col on col.[Guid] = vfo.[ItemGuid]
    JOIN Item vi on vi.[Guid] = col.[Guid]
    JOIN Item ifo on ifo.[Guid] = vfo.ParentFolderGuid
    JOIN CollectionMembership cm on cm.CollectionGuid = col.[Guid]
    JOIN Inv_AeX_AC_Identification vc on vc.[_ResourceGuid] = cm.[ResourceGuid]
    WHERE
        fo.ParentFolderGuid = @FolderGuid  
ORDER BY 1,4

--Option 2:
--Using a custom function "fnGetFolderPathNames"

--Helper Function (to show whole path to folder)

drop function dbo.fnGetFolderPathNames
go
 
create function dbo.fnGetFolderPathNames (@folderGuid uniqueidentifier, @maxParent uniqueidentifier, @reverse bit) 
returns nvarchar(4000)
as
begin
declare @n nvarchar(1000); select top 1 @n = [Name] from Item where [Guid] = @folderGuid
declare @p uniqueidentifier; select top 1 @p = fo.ParentFolderGuid from ItemFolder fo where fo.ItemGuid = @folderGuid
if @p is null or @p = 0x0 or @folderGuid = @maxParent
return @n
 
if @reverse = 1
return @n + ' / ' + dbo.fnGetFolderPathNames(@p, @maxParent, 1)
 
return dbo.fnGetFolderPathNames(@p, @maxParent, 0) + ' / ' + @n
end
go
 

--Main Query

select vc.[Name]
, vc.[OS Name]
, vi.[Name] as [Filter Name]
, dbo.fnGetFolderPathNames(vfo.ParentFolderGuid, @FolderGuid, 1) as [Folder]
from  FolderBaseFolder fo
join ItemFolder vfo on vfo.ParentFolderGuid = fo.FolderGuid
join [Collection] col on col.[Guid] = vfo.[ItemGuid]
join Item vi on vi.[Guid] = col.[Guid]
join CollectionMembership cm on cm.CollectionGuid = col.[Guid]
join Inv_AeX_AC_Identification vc on vc.[_ResourceGuid] = cm.[ResourceGuid]
where 
fo.ParentFolderGuid = @FolderGuid