Forum Discussion

baatch's avatar
baatch
Icon for Helper I rankHelper I
10 years ago

Replace / Transform, DN to OU Active Directory query

Hi

 

I have a column that is showing the distinguised name of computers in Active Directory like and I would like to extract only the Organizational Unit information from the object.

 

How can I use replace values to transform from:

 

DN

CN=computers1,OU=win7,OU=computers,DC=domain,DC=local

CN=computers2,OU=win7,OU=computers,DC=domain,DC=local

 

To look like this:

 

DN

win7/computers/domain/local

win7/computers/domain/local

 

 

/A

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    Other than a leading "/", which I'm sure you can get rid of easily, this should get you there:

     

    = SUBSTITUTE(SUBSTITUTE(RIGHT([DN],LEN([DN])-FIND(",",[DN])+1),",OU=","/"),",DC=","/")

     

    baatch - Here's an edit that gets rid of the leading /

     

    MyDN = VAR tmpText = SUBSTITUTE(SUBSTITUTE(RIGHT([DN],LEN([DN])-FIND(",",[DN])+1),",OU=","/"),",DC=","/")
RETURN (
RIGHT(tmpText,LEN(tmpText)-1))

     

     

    • baatch's avatar
      baatch
      Icon for Helper I rankHelper I
      10 years ago

      Cool!

       

      Thanks smoupre! I'm just beginning to learn the basics, is there any chance you can explain more in details all the command that are listed below to transform it? I assume it is creating a new column called MyDN?

       

      MyDN = VAR tmpText = SUBSTITUTE(SUBSTITUTE(RIGHT([DN],LEN([DN])-FIND(",",[DN])+1),",OU=","/"),",DC=","/")
RETURN (
RIGHT(tmpText,LEN(tmpText)-1))

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        10 years ago

        Yeah, sorry, it is a bit of a mess, let me break it down:

         

        Here is it again with comments and broken down, it is equivalent and creates a custom column MyDN2:

         

        MyDN2 = 
// Get length of text just before the first , which represents the CN portion of the address
// LEN gets the total length
// FIND gets the position of the first occurance of a comma
// The +1 makes sure that the returned text includes the comma
VAR Length = LEN([DN])-FIND(",",[DN])+1
// Now starting at the right, grab the number of characters calculated above
VAR tmpText1 = RIGHT([DN],Length)
// Now, replace all occurances of ",OU=" with a forward slash "/" 
VAR tmpText2 = SUBSTITUTE(tmpText1,",OU=","/")
// Do the same for ",DC="
VAR tmpText3 = SUBSTITUTE(tmpText2,",DC=","/")
//Finally, return the text minus the first character, a leading "/"
RETURN (
  RIGHT(tmpText3,LEN(tmpText3)-1)
)