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 ...
Need Help
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)
)