United Kingdom Endpoint Management User Group

Hi Nico,

great job, very useful sql query.

Hi,

I created a stored procedure to get all objets within a specified folder and all its sub-folders (to get tasks in my exemple), with the full hierarchical folder path :

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_DMI_fnGetTasksWithPathInFolder]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[_DMI_fnGetTasksWithPathInFolder]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[_DMI_fnGetTasksWithPathInFolder]
(
@MainFolderGuid uniqueidentifier = '455ae0db-ec74-455b-b262-89421c96908d' -- By default the root folder "Jobs and Tasks"
,@ExcludeFolderGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000' -- By default no exclusion
)
RETURNS @Tasks table (FolderPath nvarchar(255), TaskName nvarchar(200), TaskGuid uniqueidentifier not null)

BEGIN

-------------------------------------------------------------------
-- Creation of the path hierarchy of the tasks in the specified folder
-------------------------------------------------------------------
    
    declare @FolderGuid uniqueidentifier
    declare @FolderPath nvarchar(255) = ''    
    declare @PFolderGuid uniqueidentifier
    declare @PFolderName nvarchar(200)

    -- Browse all folders included in the specified folder (except exclusion)
    declare c_folders CURSOR FOR    
        select distinct fol.ItemGuid as [FolderGuid]
        from ItemFolder fol
        inner join FolderBaseFolder froot on froot.FolderGuid=fol.ParentFolderGuid
        left join FolderBaseFolder fexcept on fexcept.FolderGuid=fol.ItemGuid and fexcept.ParentFolderGuid=@ExcludeFolderGuid
        where fol.IsFolder=1
        and froot.ParentFolderGuid=@MainFolderGuid
        and (@ExcludeFolderGuid='00000000-0000-0000-0000-000000000000' or fexcept.Depth is null)
        
    open c_folders
    FETCH NEXT FROM c_folders into @FolderGuid
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Browse parent folders of the selected folder, starting from root folder
        declare c_parents CURSOR FOR
            select f.Guid as [PFolderGuid], cast(f.Name as nvarchar(200)) as [PFolderName]
            from FolderBaseFolder parents
            inner join vFolder f on f.Guid=parents.ParentFolderGuid
            where parents.FolderGuid=@FolderGuid
            order by parents.Depth desc
            
        open c_parents
        FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName
        
        -- Move until the main specified folder
        WHILE @PFolderGuid<>@MainFolderGuid
        BEGIN
        FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName
        END
        
        -- Add name of the folder at the right side of its parents already written in the variable (main specified folder excluded)
        FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @FolderPath = @FolderPath + @PFolderName + '\'
        FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName
        END
        close c_parents
        deallocate c_parents

        -- Insert all tasks included directly in the selected folder, with their folder path (except specified exclusion)
        INSERT INTO @Tasks
            select @FolderPath as [FolderPath], i.Name as [TaskName], i.Guid as [TaskGuid]
            from ItemFolder tsk
            inner join Item i on i.Guid=tsk.ItemGuid
            -- Exclusion of the hidden tasks
            where i.Attributes=0
            and tsk.IsFolder=0
            and tsk.ParentFolderGuid=@FolderGuid
        
        SET @FolderPath=''
        
    FETCH NEXT FROM c_folders into @FolderGuid
    END
    close c_folders
    deallocate c_folders

RETURN

END

GO