none
Active Directory Guid CRM vs. other system RRS feed

  • Frage

  • I have two Microsoft systems (CRM and Project Server) that store the active directory user GUID differently. The first part of it is scrambled, but it has the exact same letters and numbers in it.

    NOTICE in bold the similarites
    84CCB872-37A1-4F89-BF73-4E1C7891E9ED
    72B8CC84-A137-894F-BF73-4E1C7891E9ED

    and if you compare the numbers in the last part of the 1st section: 4F89 vs. 894F (same numbers and letters, different ordering)

    Could someone help me with the SQL logic of 'unscrambling' one of them? My goal is to match users in both systems based on AD Guids, since that is an attribute that is common in both systems for the user.
    Freitag, 2. Oktober 2009 22:08

Antworten

  • Nova, the below function should do the trick for you. It will convert them in sets of 2. Granted, this is a hard coded solution, but it works


    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Carlton Colter
    -- Create date: 10/13/2009
    -- Description:	Guid - Matcher
    -- =============================================
    CREATE FUNCTION ConvertActiveDirectoryGuid 
    (
    	-- Add the parameters for the function here
    	@Start uniqueidentifier
    )
    RETURNS uniqueidentifier
    AS
    BEGIN
    	IF (@Start IS NULL) RETURN NULL
    
    	DECLARE @StartString varchar(50)
    	SET @StartString = CAST(@Start as varchar(50))
    	
    	RETURN CAST(
    	      (Substring(@StartString,7,2) 
             + Substring(@StartString,5,2)
             + Substring(@StartString,3,2)
             + Substring(@StartString,1,2)
             + '-'
             + Substring(@StartString,12,2)
             + Substring(@StartString,10,2)
             + '-'
             + Substring(@StartString,17,2)
             + Substring(@StartString,15,2)
             + Substring(@StartString,19,18)
              ) as uniqueidentifier)
    END
    GO
    
    

    To test the above function, you can use:

    DECLARE @Start uniqueidentifier, @Match uniqueidentifier
    SET @Match = '84CCB872-37A1-4F89-BF73-4E1C7891E9ED'
    SET @Start = '72B8CC84-A137-894F-BF73-4E1C7891E9ED'
    PRINT @Match
    PRINT dbo.ConvertActiveDirectoryGuid(@Start)
    

    You can place that function on either db or a custom db that links the two and does the integration. I'm sure Microsoft would prefer that you don't modify their databases.

    Dienstag, 13. Oktober 2009 18:23

Alle Antworten

  • I dont know why you'd like to do that but as i see it, the numbers are just switched?

    4f89 - 894f


    84CCB872
    you have to switch first and last

    72CCB884 voila

    and then switch the rest
    72B8CC84? Does that do the trick?
    finished.
    Montag, 5. Oktober 2009 16:38
  • Thanks Michael,

    Yeah they need to be switched. I am looking for a sql statement that will take one of them and make it like the other.

    The reason I want to join on the active directory guid in both systems is that the user id in each system is different.

    Thanks.

    NovaCRM
    Dienstag, 6. Oktober 2009 14:28
  • Nova, the below function should do the trick for you. It will convert them in sets of 2. Granted, this is a hard coded solution, but it works


    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Carlton Colter
    -- Create date: 10/13/2009
    -- Description:	Guid - Matcher
    -- =============================================
    CREATE FUNCTION ConvertActiveDirectoryGuid 
    (
    	-- Add the parameters for the function here
    	@Start uniqueidentifier
    )
    RETURNS uniqueidentifier
    AS
    BEGIN
    	IF (@Start IS NULL) RETURN NULL
    
    	DECLARE @StartString varchar(50)
    	SET @StartString = CAST(@Start as varchar(50))
    	
    	RETURN CAST(
    	      (Substring(@StartString,7,2) 
             + Substring(@StartString,5,2)
             + Substring(@StartString,3,2)
             + Substring(@StartString,1,2)
             + '-'
             + Substring(@StartString,12,2)
             + Substring(@StartString,10,2)
             + '-'
             + Substring(@StartString,17,2)
             + Substring(@StartString,15,2)
             + Substring(@StartString,19,18)
              ) as uniqueidentifier)
    END
    GO
    
    

    To test the above function, you can use:

    DECLARE @Start uniqueidentifier, @Match uniqueidentifier
    SET @Match = '84CCB872-37A1-4F89-BF73-4E1C7891E9ED'
    SET @Start = '72B8CC84-A137-894F-BF73-4E1C7891E9ED'
    PRINT @Match
    PRINT dbo.ConvertActiveDirectoryGuid(@Start)
    

    You can place that function on either db or a custom db that links the two and does the integration. I'm sure Microsoft would prefer that you don't modify their databases.

    Dienstag, 13. Oktober 2009 18:23
  • this works great, thank you!
    Dienstag, 13. Oktober 2009 18:27