Replace / Transform, DN to OU Active Directory query

[baatch]

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

[Greg_Deckler]

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]

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]

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