Active Directory：将canonicalNameName节点值从字符串转换为整数 [英] Active Directory: Convert canonicalName node value from string to integer

问题描述

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

Are there any methods available to convert the string text value contained within the AD canonicalName attribute to an incremented integer value? Or, does this need to be performed manually?

例如：

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 GetDescendent（）方法快速递归地检索用户，而不必编写 1 + n 个子选择。

I am extracting user objects into a SQL Server database for reporting purposes. The user objects are spread throughout multiple OU's in the forest. So, by identifying the highest node on the tree that contains users, I can then utilize the SQL Server GetDescendent() method to quickly retrieve users recursively without having to write 1 + n number of sub-selects.

供参考： https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/hierarchyid-data-type-method-reference

更新：

我能够将canonicalName从字符串转换为整数（请参见下文，使用SQL Server 2014）。但是，这似乎无法解决我的问题。我只是通过剥离叶子来构建树的分支，这样我就可以通过树分支来获取IsDescendant（）。但是现在，我无法批量插入叶子，因为我似乎需要GetDescendant（），它似乎是为一次处理一个插入而构建的。

I am able to convert the canonicalName from string to integer (see below using SQL Server 2014). However, this doesn't seem to solve my problem. I have only built the branches of the tree by stripping off the leafs, that way I can get IsDescendant() by tree branch. But now, I cannot insert the leafs in batch as it appears I need to GetDescendant(), which appears to be built for handling inserts one at a time.

如何将类似于文件系统路径的Active Directory目录树构建为SQL层次结构？所有示例都将层次结构视为直接的父/子关系，并使用递归CTE从根开始进行构建，这要求已经知道父子关系。就我而言，父子关系仅通过'/'分隔符知道。

How can I build the Active Directory directory tree, which resembles file system paths, as a SQL Hierarchy? All examples treat the hierarchy as an immediately parent/child relationship and use a recursive CTE to build from the root, which requires the parent child relationship to already be know. In my case, the parent child relationship is known only through the '/' delimiter.

-- 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

推荐答案

这就是我的思想所在

我给了您9个等级，但是这种模式很容易看到和扩展

I gave you 9 levels, but the pattern is easy to see and expand

没有正确的顺序，我默认按节点字母顺序排列。

Without a proper sequence I defaulted to alphabetical by node.

它也支持多个根节点

示例

Select A.*
      ,Nodes = concat('/',dense_rank() over (Order By N1),'/'
              ,left(nullif(dense_rank() over (Partition By N1                      Order By N2)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2                   Order By N3)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3                Order By N4)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4             Order By N5)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5          Order By N6)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5,N6       Order By N7)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5,N6,N7    Order By N8)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5,N6,N7,N8 Order By N9)-1,0),5)+'/'
              )
 From  YourTable A
 Cross Apply (
                Select N1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,N2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,N3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,N4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,N5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,N6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,N7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,N8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,N9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(stuff([canonicalName],1,1,''),'\','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B
 Order By 1

退货

canonicalName                       Nodes
\domain.com\                        /1/
\domain.com\Corporate               /1/1/
\domain.com\Corporate\Accounting    /1/1/1/
\domain.com\Corporate\Hr            /1/1/2/
\domain.com\Security\               /1/2/
\domain.com\Security\Administrative /1/2/1/
\domain.com\Security\Executive      /1/2/2/
\domain.com\Security\Servers        /1/2/3/
\domain.com\Users\                  /1/3/
\domain.com\Users\Sales             /1/3/1/
\domain.com\Users\Whatever          /1/3/2/

这篇关于Active Directory：将canonicalNameName节点值从字符串转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！