Forum Discussion
Getting computer OU information
Was able to resolve this on my own.
Sorry, I posted in haste.
I am using the Active Directory connector in Power BI to get information on servers in our domain. I would like to display the servers OU information also on this report. I can then create a filter to show a count of servers in certain OUs. I just need the OU information to be in a better format than what is in the distinguishedName colum.
If I add that to the report as it sits, it has all the LDAP information included.
Example: CN=Server123,OU=Prod,OU=Servers,DC=help,DC=me,DC=out
Format desired: Prod/Servers
Hope that makes more sense.
Was able to resolve this on my own.
I split the column using custom delimiters to break out the information and then replaced the text.
Split column
Custom Delimiter - CN=, (left most)
Then proceeded to do the same for the end (DC info)
Split the new column off again - Custom Delimiter - DC=, (left most)
This left me with OU=something,OU=something
I replaced the text on that colum to change OU= to a "/"
This left me with a column containing the OU location only. I will do this again with pictures, I know it seems a little hard to understand with just me typing it out.
I don't know if anyone else is still looking at this 4 years on but Bing brought me here searching to do similar.
I couldn't work out how the OP got the desired text using split but it got me thinking and instead of doing two splits, I went to the Transform ribbon and selected Extract > "Text Between Delimiters" and then from "," to ",DC="
Then split this into rows on ",". This left me with one row starting "CN=", where ther emust have been no OU, so I filtered on rows beginning "OU=". (I guess I could also have just split on "," and then done this step also)
Then used Replace Values to replace "OU=" with "".
Finally tidied up by deleting the additional columns that were created during these steps.
Now I have a tidy lookup table of computers and there OUs.
