Active Directory:将canonicalName节点值从字符串转换为整数

是否有任何方法可以将AD canonicalName属性中包含的字符串文本值转换为递增的整数值?或者,是否需要手动执行此操作?

例如:

canonicalName (what I am getting)          hierarchyNode (what I need)
\domain.com\                               /1/
\domain.com\Corporate                      /1/1/
\domain.com\Corporate\Hr                   /1/1/1/
\domain.com\Corporate\Accounting           /1/1/2/
\domain.com\Users\                         /1/2/
\domain.com\Users\Sales                    /1/2/1/
\domain.com\Users\Whatever                 /1/2/2/
\domain.com\Security\                      /1/3/
\domain.com\Security\Servers               /1/3/1/
\domain.com\Security\Administrative        /1/3/2/
\domain.com\Security\Executive             /1/3/3/

我正在将用户对象提取到SQL Server数据库中以用于报告目的。用户对象分布在林中的多个OU中。因此,通过识别树上包含用户的最高节点,我就可以利用SQL Server getDegent()方法递归地快速检索用户,而无需编写 1+n 子选择数。

供参考: https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference

更新:

我能够将canonicalName从字符串转换为整数(请参见下面使用SQL Server 2014的内容)。然而,这似乎并不能解决我的问题。我只是通过剥离树叶来建造树枝,这样我就可以通过树枝获得IsDescendant()。但现在,我无法批量插入叶子,因为我似乎需要getDegenant(),它似乎是为一次处理一个插入而构建的。

如何将类似于文件系统路径的Active Directory目录树构建为SQL层次结构?所有示例都将层次结构视为直接的父/子关系,并使用递归CTE从根构建,这要求已经知道父子关系。在我的例子中,只有通过“/”分隔符才能知道父子关系。

-- Drop and re-create temp table(s) that are used by this procedure.
IF OBJECT_ID(N'Tempdb.dbo.#TEMP_TreeHierarchy', N'U') IS NOT NULL 
BEGIN
DROP TABLE #TEMP_TreeHierarchy
END;

-- Drop and re-create temp table(s) that are used by this procedure.
IF OBJECT_ID(N'Tempdb.dbo.#TEMP_AdTreeHierarchyNodeNames', N'U') IS NOT NULL 
BEGIN
DROP TABLE #TEMP_AdTreeHierarchyNodeNames
END;

-- CREATE TEMP TABLE(s)
CREATE TABLE #TEMP_TreeHierarchy(
        TreeHierarchyKey INT IDENTITY(1,1) NOT NULL
        ,TreeHierarchyId hierarchyid NULL
        ,TreeHierarchyNodeLevel int NULL
        ,TreeHierarchyNode varchar(255) NULL
        ,TreeCanonicalName varchar(255) NOT NULL
    PRIMARY KEY CLUSTERED 
(
    TreeCanonicalName ASC
))

CREATE TABLE #TEMP_AdTreeHierarchyNodeNames (
        TreeCanonicalName VARCHAR(255) NOT NULL
        ,TreeHierarchyNodeLevel INT NOT NULL
        ,TreeHierarchyNodeName VARCHAR(255) NOT NULL
        ,IndexValueByLevel INT NULL
    PRIMARY KEY CLUSTERED 
(
    TreeCanonicalName ASC
    ,TreeHierarchyNodeLevel ASC
    ,TreeHierarchyNodeName ASC
))

-- Step 1.) INSERT the DISTINCT list of CanonicalName values into #TEMP_TreeHierarchy. 
--          Remove the reserved character '/' that has been escaped '\/'. Note: '/' is the delimiter.
--          Remove all of the leaves from the tree, leaving only the root and the branches/nodes.
    ;WITH CTE1 AS (SELECT CanonicalNameParseReserveChar = REPLACE(A.CanonicalName, '\/', '') -- Remove the reserved character '/' that has been escaped '\/'.
                   FROM dbo.AdObjects A
                  )

    -- Remove CN from end of string in order to get the distinct list (i.e., remove all of the leaves from the tree, leaving only the root and the branches/nodes).
    -- INSERT the records INTO #TEMP_TreeHierarchy
    INSERT INTO #TEMP_TreeHierarchy (TreeCanonicalName)
    SELECT DISTINCT 
        CanonicalNameTree = REVERSE(SUBSTRING(REVERSE(C1.CanonicalNameParseReserveChar), CHARINDEX('/', REVERSE(C1.CanonicalNameParseReserveChar), 0) + 1, LEN(C1.CanonicalNameParseReserveChar) - CHARINDEX('/', REVERSE(C1.CanonicalNameParseReserveChar), 0)))
    FROM CTE1 C1

-- Step 2.) Get NodeLevel and NodeName (i.e., key/value pair).
--      Get the nodes for each entry by splitting out the '/' delimiter, which provides both the NodeLevel and NodeName.
--      This table will be used as scratch to build the HierarchyNodeByLvl,
--          which is where the heavy lifting of converting the canonicalName value from string to integer occurs.
--      Note: integer is required for the node name - string values are not allowed. Thus this bridge must be build dynamically.
--      Achieve dynamic result by using CROSS APPLY to convert a single delimited row into 1 + n rows, based on the number of nodes.
    -- INSERT the key/value pair results INTO a temp table.
    -- Use ROW_NUMBER() to identify each NodeLevel, which is the key.
    -- Use the string contained between the delimiter, which is the value.
    -- Combined, these create a unique identifier that will be used to roll-up the HierarchyNodeByLevel, which is a RECURSIVE key/value pair of NodeLevel and IndexValueByLevel.
    -- The rolled-up value contained in HierarchyNodeByLevel is what the SQL Server hierarchyid::Parse() function requires in order to create the hierarchyid.
    -- https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/
    INSERT INTO #TEMP_AdTreeHierarchyNodeNames (TreeCanonicalName, TreeHierarchyNodeLevel, TreeHierarchyNodeName)
    SELECT TreeCanonicalName
        ,TreeHierarchyNodeLevel = ROW_NUMBER() OVER(PARTITION BY TreeCanonicalName ORDER BY TreeCanonicalName)
        ,TreeHierarchyNodeName = LTRIM(RTRIM(m.n.value('.[1]','VARCHAR(MAX)')))
    FROM (SELECT TH.TreeCanonicalName
            ,x = CAST('<XMLRoot><RowData>' + REPLACE(TH.TreeCanonicalName,'/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML)
          FROM #TEMP_TreeHierarchy TH
          ) SUB1
    CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

-- Step 3.) Get the IndexValueByLevel RECURSIVE key/value pair
--        Get the DISTINCT list of TreeHierarchyNodeLevel, TreeHierarchyNodeName first
--        Use TreeHierarchyNodeLevel is the key
--        Use ROW_NUMBER() to identify each IndexValueByLevel, which is the value. 
--        Since the IndexValueByLevel exists for each level, the value for each level must be concatenated together to create the final value that is stored in TreeHierarchyNode
    ;WITH CTE1 AS (SELECT DISTINCT TreeHierarchyNodeLevel, TreeHierarchyNodeName
                    FROM #TEMP_AdTreeHierarchyNodeNames
                    ),
        CTE2 AS (SELECT C1.*
                        ,IndexValueByLevel = ROW_NUMBER() OVER(PARTITION BY C1.TreeHierarchyNodeLevel ORDER BY C1.TreeHierarchyNodeName)
                FROM CTE1 C1
                )

    UPDATE TMP1
    SET TMP1.IndexValueByLevel = C2.IndexValueByLevel
    FROM #TEMP_AdTreeHierarchyNodeNames TMP1
    INNER JOIN CTE2 C2
        ON TMP1.TreeHierarchyNodeLevel = C2.TreeHierarchyNodeLevel
        AND TMP1.TreeHierarchyNodeName = C2.TreeHierarchyNodeName

-- Step 4.) Build the TreeHierarchyNodeByLevel.
--          Use FOR XML to roll up all duplicate keys in order to concatenate their values into one string.
--          https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/
    ;WITH CTE1 AS (SELECT DISTINCT TreeCanonicalName
                                ,TreeHierarchyNodeByLevel = 
                                   (SELECT '/' + CAST(IndexValueByLevel AS VARCHAR(10))
                                    FROM #TEMP_AdTreeHierarchyNodeNames TMP1
                                    WHERE TMP1.TreeCanonicalName = TMP2.TreeCanonicalName
                                    FOR XML PATH(''))
                    FROM #TEMP_AdTreeHierarchyNodeNames TMP2
                    ),
            CTE2 AS (SELECT C1.TreeCanonicalName
                            ,C1.TreeHierarchyNodeByLevel
                            ,TreeHierarchyNodeLevel = MAX(TMP1.TreeHierarchyNodeLevel)
                    FROM CTE1 C1
                    INNER JOIN #TEMP_AdTreeHierarchyNodeNames TMP1
                        ON TMP1.TreeCanonicalName = C1.TreeCanonicalName
                    GROUP BY C1.TreeCanonicalName, C1.TreeHierarchyNodeByLevel
                    )

    UPDATE TH
    SET TH.TreeHierarchyNodeLevel = C2.TreeHierarchyNodeLevel
        ,TH.TreeHierarchyNode = C2.TreeHierarchyNodeByLevel + '/'
        ,TH.TreeHierarchyId = hierarchyid::Parse(C2.TreeHierarchyNodeByLevel + '/')
    FROM #TEMP_TreeHierarchy TH
    INNER JOIN CTE2 C2
        ON TH.TreeCanonicalName = C2.TreeCanonicalName

INSERT INTO AD.TreeHierarchy (EffectiveStartDate, EffectiveEndDate, TreeCanonicalName, TreeHierarchyNodeLevel, TreeHierarchyNode, TreeHierarchyId)
SELECT EffectiveStartDate = CAST(GETDATE() AS DATE)
        ,EffectiveEndDate = '12/31/9999'
        ,TH.TreeCanonicalName
        ,TH.TreeHierarchyNodeLevel
        ,TH.TreeHierarchyNode
        ,TH.TreeHierarchyId 
FROM #TEMP_TreeHierarchy TH
ORDER BY TH.TreeHierarchyKey

---- For testing purposes only.
SELECT * FROM AD.TreeHierarchy TH
SELECT * FROM #TEMP_AdTreeHierarchyNodeNames
SELECT * FROM #TEMP_TreeHierarchy

-- Clean-up. DROP TEMP TABLE(s).
DROP TABLE #TEMP_TreeHierarchy
DROP TABLE #TEMP_AdTreeHierarchyNodeNames