none
Change AD fromat from CN to DN in query

    Question

  • I'm trying to create a query in SQL for a SCCM report. But run into an issue around formatting between 2 tables that contains AD location information.

    The primary format used is DN within the other tables.

    What I'm tying to achieve is a query similar to...

    Select AD-Convert('Test.local/Domain',DN)

    that would return

    OU=DOMAIN,DC=Test,DC=LOCAL
    Is there a way to do this, convert/change from CN to DN.

    Wednesday, April 17, 2019 11:10 AM

Answers

  • Seems I got a solution that might work best for me...

    1st need to create a function.

    CREATE FUNCTION [ReverseStringOUs]
    (
        @Source VARCHAR(MAX)
    )
    RETURNS VARCHAR(MAX)  
    BEGIN
             
       DECLARE @Destination VARCHAR(MAX)
       SET @Destination = '' 
        
       DECLARE @Length INT
         
       WHILE LEN(@Source) > 0
       BEGIN
          IF CHARINDEX(',', @Source) > 0
          BEGIN
             SET @Destination = SUBSTRING(@Source, 0, CHARINDEX(',', @Source)) + ' ' + @Destination
             SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(',', @Source) + 1, LEN(@Source))))
          END
          ELSE
          BEGIN
             SET @Destination = @Source + ',' + @Destination
             SET @Source = ''
          END
       END          
       RETURN @Destination
    END

    And then the following query works

    select 
    		AD.Name
    	  ,Case 
    			when ch.SearchBase like 'LDAP://%' then 'Covered'
    			Else 'Not Covered' end as 'SCCM Discovery Coveredge'
    	  ,  AD.ParentContainer
    	   , ([dbo].[ReverseStringOUs]('OU=' + Replace((Replace (AD.ParentContainer, left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer))), '')),'/',',OU=')))
    		  + 
    		 ('DC=' + Replace((Left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer)-1))),'.',',DC='))
    		 'AD converted CN to DN'
    	  ,  ch.SearchBase
    
    from AD_Data AD
    left join ActiveDirectoryDiscoveryMetaData CH on 
    			( ([dbo].[ReverseStringOUs]('OU=' + Replace((Replace (AD.ParentContainer, left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer))), '')),'/',',OU=')))
    	        + 
    		    ('DC=' + Replace((Left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer)-1))),'.',',DC=')))
    			Like
    			'%' + (replace (SearchBase, 'LDAP://', ''))
    			and agent = 2




    Thursday, April 18, 2019 9:00 AM

All replies

  • Hi Hentie Lategan

    Do you need return information present each three column? Column OU and COLUMN DC or in one column ?

    I need more data for example.

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 

    Wednesday, April 17, 2019 11:22 AM
  • Hi

    I've only displayed part of the query I have a problem with...
    It would end being part of a much lager query I'm busy with.
    The results need to be a single column that would be used to compare with anther column of another table using case statements also.

    The problem is, I can't say "case when 'Test.local/Domain' = 'OU=DOMAIN,DC=Test,DC=LOCAL' then 'True'"
    Or more correctly say "case when v.CNname = d.DNname then 'True'

    Most likely end with a sub-query and end into something.

    Case when
       (Select replace OU, Replace DC, Count something
          Where somthing else
        ) = d.DNname then 'True


    Wednesday, April 17, 2019 11:42 AM
  • I need to change the format of the data in the results/returns from one table to be able to link to another table.
    Both table contains the AD locations of entries. The one is in Common Name (CN) format. The other table is in Distinguished Name (DN) format.

    eg...

    In table 1 is over 2000 entries in CN format, like
     Test.local/Domain
     Test.local/Doc
     Test.local/Doc/time

    The results I need is in DN format
     OU=DOMAIN,DC=TEST,DC=LOCAL
     OU=DOC,DC=TESY,DC=LOCAL
     OU=TIME,OU=DOC,DC=TEST,DC=LOCAL


    In the end it would have multiple columns when the full query is run. eg.

    System OS AD Location
    Current John Windows 10 Test.local/Doc/time
    Should be John Windows 10 OU=TIME,OU=DOC,DC=TEST,DC=LOCAL

    So I can't just use replace() as the format would not be in the correct order.


    Wednesday, April 17, 2019 7:59 PM
  • create table domains (domain varchar(50))
    insert into domains values
    ('Test.local/Domain'),
    ('Test.local/Doc'),
    ('Test.local/Doc/time');
    
    with tCte 
    as (
    	select replace(domain,'/',',OU=') ou,'DC='+replace(domain,'.',',DC=') dc
    	from domains
    )
    select UPPER(SUBSTRING(dc,1,CHARINDEX('/',dc)-1)+','+SUBSTRING(ou,CHARINDEX(',',ou)+1,len(ou)))
    from tCte;
    
    drop table domains;

    Wednesday, April 17, 2019 8:10 PM
  • Hi Lokesh Vij

    Thanks, but that does not work correctly. The format is wrong.

    This query returns DC=TEST,DC=LOCAL,OU=DOC,OU=TIME
    It should return     OU=TIME,OU=DOC,DC=TEST,DC=LOCAL

    Currently the query looks like this. But still not correct as the OU is revered order.

    with tCte 
            as ( select ad.Name
                    ,ad.ParentContainer
                    ,replace(ad.ParentContainer,'/',',OU=') ou
                    ,'DC='+replace(ad.ParentContainer,'.',',DC=') dc
                 FROM AD_Data AD
                    )
            select name
                    , ParentContainer
                    , UPPER(
                        /* OU in Wrong order */
                        SUBSTRING(ou,CHARINDEX(',',ou)+1,len(ou))
                        +','+
                        SUBSTRING(dc,1,CHARINDEX('/',dc)-1)
                        )
            from tCte



    Thursday, April 18, 2019 5:16 AM
  • CREATE TABLE domains(domain VARCHAR(50));
    INSERT INTO domains
    VALUES('Test.local/Domain'), ('Test.local/Doc'), ('Test.local/Doc/time');
    
    SELECT domain CN, 
           SUBSTRING(replace(SUBSTRING(domain, CHARINDEX('/', domain), LEN(domain)), '/', ',OU=') + replace(('.' + SUBSTRING(domain, 1, CHARINDEX('/', domain) - 1)), '.', ',DC='), 2, 50) DN
    FROM domains;
    
    
    --Results:
    --CN	                DN
    --Test.local/Domain	    OU=Domain,DC=Test,DC=local
    --Test.local/Doc	    OU=Doc,DC=Test,DC=local
    --Test.local/Doc/time	OU=Doc,OU=time,DC=Test,DC=local

    value 50 - its length datatype

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 

    Thursday, April 18, 2019 5:40 AM
  • Hi Natiq

    The OU in the DN is the wrong sequence. The last one has 2 OUs. It should read OU=TIME,OU=DOC,DC=TEST,DC=LOCAL

    Thursday, April 18, 2019 6:06 AM
  • In one DN maximum count OU ?
    Thursday, April 18, 2019 6:11 AM
  • The problem with converting CN to DN is the sequence of the names. The OU is in reverse order and before the DC. The DC are in the same order.
    This is why I said, a simple replace would not work.
    Just need to find a way to reverse the order of the OU now and it should be OK.

    Thursday, April 18, 2019 6:30 AM
  • DECLARE	@Sample TABLE
    	(
    		Content VARCHAR(100)
    	);
    
    INSERT	@Sample
    VALUES	('Test.local/Doc/time');
    
    -- swePeso
    WITH cteSource
    AS (
    	SELECT		ROW_NUMBER() OVER (ORDER BY CHARINDEX(g.value, s.Content)) AS rn,
    			COUNT(*) OVER () AS cnt,
    			g.value
    	FROM		@Sample AS s
    	CROSS APPLY	STRING_SPLIT(s.Content, '/') AS f
    	CROSS APPLY	STRING_SPLIT(f.value, '.') AS g
    ), cteData
    AS (
    	SELECT	CASE
    			WHEN rn = 1 THEN cnt - 1
    			WHEN rn = 2 THEN cnt
    			ELSE cnt - rn
    		END AS theOrder,
    		CASE 
    			WHEN rn >= 3 THEN ',OU=' + UPPER(value)
    			ELSE ',DC=' + UPPER(value)
    		END AS Content
    	FROM	cteSource
    )
    SELECT	STUFF(STRING_AGG(Content, '') WITHIN GROUP (ORDER BY theOrder), 1, 1, '')
    FROM	cteData;
    


    www.sqltopia.com

    Thursday, April 18, 2019 6:41 AM
  • --CREATE FUNCTION 
    
    CREATE FUNCTION dbo.fnParseList
    (
    	@Delimiter CHAR,
    	@Text TEXT
    )
    RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
    AS
    
    BEGIN
    	DECLARE	@NextPos INT,
    		@LastPos INT
    
    	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
    		@LastPos = 0
    
    	WHILE @NextPos > 0
    		BEGIN
    			INSERT	@Result
    				(
    					Data
    				)
    			SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
    
    			SELECT	@LastPos = @NextPos,
    				@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
    		END
    
    	IF @NextPos <= @LastPos
    		INSERT	@Result
    			(
    				Data
    			)
    		SELECT	SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)
    
    	RETURN
    END
    
    Author :Peter Larsson
    
    
    --After this run following query
    
    with cte
    as(
    			 select domain,f.Data,row_number() over ( partition by domain order by f.RowID desc) frn from domains d cross apply  dbo.fnParseList('.',replace(SUBSTRING(d.domain, CHARINDEX('/', d.domain), LEN(d.domain)), '/', '.,OU=')) f 
    	  )
    
    	  select domain, abc = upper(STUFF(
                 (SELECT '' +  data
                  FROM cte t1
                  WHERE t1.domain = t2.domain order by frn
                  FOR XML PATH ('')) , 1, 1, '') +replace(('.' + SUBSTRING(domain, 1, CHARINDEX('/', domain) - 1)), '.', ',DC=')) from cte t2 group by domain  
    
    

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 

    Thursday, April 18, 2019 7:32 AM
  • Seems I got a solution that might work best for me...

    1st need to create a function.

    CREATE FUNCTION [ReverseStringOUs]
    (
        @Source VARCHAR(MAX)
    )
    RETURNS VARCHAR(MAX)  
    BEGIN
             
       DECLARE @Destination VARCHAR(MAX)
       SET @Destination = '' 
        
       DECLARE @Length INT
         
       WHILE LEN(@Source) > 0
       BEGIN
          IF CHARINDEX(',', @Source) > 0
          BEGIN
             SET @Destination = SUBSTRING(@Source, 0, CHARINDEX(',', @Source)) + ' ' + @Destination
             SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(',', @Source) + 1, LEN(@Source))))
          END
          ELSE
          BEGIN
             SET @Destination = @Source + ',' + @Destination
             SET @Source = ''
          END
       END          
       RETURN @Destination
    END

    And then the following query works

    select 
    		AD.Name
    	  ,Case 
    			when ch.SearchBase like 'LDAP://%' then 'Covered'
    			Else 'Not Covered' end as 'SCCM Discovery Coveredge'
    	  ,  AD.ParentContainer
    	   , ([dbo].[ReverseStringOUs]('OU=' + Replace((Replace (AD.ParentContainer, left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer))), '')),'/',',OU=')))
    		  + 
    		 ('DC=' + Replace((Left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer)-1))),'.',',DC='))
    		 'AD converted CN to DN'
    	  ,  ch.SearchBase
    
    from AD_Data AD
    left join ActiveDirectoryDiscoveryMetaData CH on 
    			( ([dbo].[ReverseStringOUs]('OU=' + Replace((Replace (AD.ParentContainer, left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer))), '')),'/',',OU=')))
    	        + 
    		    ('DC=' + Replace((Left(AD.ParentContainer,(CHARINDEX('/',AD.ParentContainer)-1))),'.',',DC=')))
    			Like
    			'%' + (replace (SearchBase, 'LDAP://', ''))
    			and agent = 2




    Thursday, April 18, 2019 9:00 AM
  • Please mark some helpful reply as answer
    Thursday, April 18, 2019 9:46 AM
  • HI Hentie Lategan

    What is problem my solution ?

    Thursday, April 18, 2019 11:32 AM