ZOUHAIER KHARROUBI

-- =============================================

-- Author:     Zouhaier KHARROUBI

-- Create date: 03/12/2021

-- Description: Afficher les membres d'un groupe Active Directory

-- =============================================

       -- Add the parameters for the stored procedure here

DECLARE      @CommandeSelect VARCHAR(150) -- netgroup ou dsquerygroup

SELECT @CommandeSelect='netgroup'

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

    -- Insert statements for procedure here

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ResultatCMD]'))

             BEGIN

                    DROP TABLE #ResultatCMD

             END

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))

             BEGIN

                    DROP TABLE #Resultat

             END

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeGroupAD]'))

             BEGIN

                    DROP TABLE #ListeGroupAD

             END

       CREATE TABLE #ResultatCMD

       (

             Num_Lig                          INT IDENTITY (1,1),

             GroupAD                          VARCHAR(350) NULL,

             Resultat                  VARCHAR(800) NULL,

             Commande                  VARCHAR(MAX) NULL

       )

       CREATE TABLE #Resultat

       (

             Num_Lig                          INT IDENTITY (1,1),

             GroupAD                          VARCHAR(350) NULL,

             Resultat                  VARCHAR(800) NULL,

             Commande                  VARCHAR(MAX) NULL

       )

       CREATE NONCLUSTERED INDEX [IDX_#Resultat_Resultat] ON #Resultat

       (

             Resultat ASC

       )

       CREATE TABLE #ListeGroupAD

       (

             Num_Lig                          INT IDENTITY (1,1),

             GroupAD                          VARCHAR(350) NULL

       )

       INSERT INTO #ListeGroupAD (GroupAD)

       SELECT GroupAD FROM (

       SELECT 'GroupeAD_1' AS GroupAD UNION

       SELECT 'GroupeAD_2' AS GroupAD UNION

       SELECT 'GroupeAD_3' AS GroupAD

       )T1

       DECLARE  @Commande                            NVARCHAR(3000)

                    ,@Nbre_Lig                              INT

                    ,@Num_Lig                               INT

                    ,@GroupAD                               VARCHAR(350)

       SELECT @Num_Lig=1,@Nbre_Lig=COUNT(GroupAD) FROM #ListeGroupAD

       WHILE @Num_Lig <= @Nbre_Lig

             BEGIN

                    TRUNCATE TABLE #ResultatCMD

                    SELECT @GroupAD=LTRIM(RTRIM(GroupAD)) FROM #ListeGroupAD WHERE Num_Lig=@Num_Lig

                    IF @CommandeSelect='netgroup'

                          BEGIN

                                 SELECT @Commande='net group "' + @GroupAD + '" /domain'

                          END

                    ELSE

                          BEGIN

                                 SELECT @Commande='cmd.exe dsquery group -name "' + @GroupAD + '" | dsget group -members -expand | dsget user -display'

                          END

                    INSERT INTO #ResultatCMD (Resultat)

                    EXEC Master..xp_cmdshell @Commande

                    UPDATE #ResultatCMD

                    SET

                          GroupAD=@GroupAD

                          ,Commande=@Commande

                    INSERT INTO #Resultat(GroupAD,Resultat,Commande)

                    SELECT GroupAD,Resultat,Commande FROM #ResultatCMD

                    WHERE Resultat IS NOT NULL

                    AND LTRIM(RTRIM(Resultat))<>'Members'

                    AND Resultat NOT LIKE 'comment%'

                    AND Resultat NOT LIKE 'Group%name%'

                    AND Resultat NOT LIKE '--%'

                    AND Resultat NOT LIKE 'The%request%will%be%processed%'

                    AND Resultat NOT LIKE 'The%command%completed%'

                    ORDER BY Num_Lig

                    SELECT @Num_Lig = @Num_Lig + 1

             END

       SELECT DISTINCT GroupAD,Resultat,Commande FROM #Resultat

       ORDER BY GroupAD

       DROP TABLE #ResultatCMD

       DROP TABLE #Resultat

       DROP TABLE #ListeGroupAD

END

GO