Forum Discussion
Active Directory Report Help
Hi djj2,
In your scenario, the key is to extract the city code from the DistinguishedName column to a new column, and create a relationship between the two table with city code column, then you can easily show the expected result on the report.
1. Use the Split Column by Delimiter option to split the DistinguishedName column in Query Editor.
2. Remove other columns, only keep the column(DistinguishedName.3) which contains the city code.
3. Add a new custom column to remove "OU=" from the code value.
=Text.RemoveRange([DistinguishedName.3],0,3)
4. Remove "DistinguishedName.3" column.
5. Click "Close&Apply".
6. Create a relationship between the two table with city code column.
Then you should be able to easily show the expected result on the report.
Here is the sample pbix file for your reference. :smileyhappy:
Regards
Hi djj2,
So the dashboard needs to pull the live data from Active Directory and then do a look up on the device OU to match the location code against the partial string of the Distinguished Name field. So the output is
Device Location
Server1 London
Could you post some sample/mock of the device OU and the Distinguished Name field, so that we can better assist on this issue? :smileyhappy:
Regards
@v-ljerr-msft thank you so much for for coming back to me on this, I feel i didnt explain that very well so i should be clearer.
Here is exactly what i am trying to achieve for my entire project. I have to do an audit of the old objects in AD by location and generate a dashboard the owners can interact with. I have a little bit of knowledge on doing this but i need some guidence.
The field DistinguishedName contains the OU that indicates the location in one table. Please note that there is hundreds of thousands of entries in these tables.
Name DistinguishedName LastLogonDate LONSERVER1 CN=LONSERVER1,OU=Servers,OU=LON,OU=EMEA,DC=domain,DC=company,DC=com 17/09/2017 I want to create a query (by merge i think) on this table that does a look up on the DistinguishedName matches the OU=LON entry and then match each row to another table that has the following.
Region Country City Code EMEA UK London LON Tables that I need to create from this are:
Servername Location Country Region LONSERVER1 London UK EMEA With another table that counts the entries:
Server Count EMEA 50 Uk 20 London 3 And a finally table that looks at the LastLogonDate that is older than 1st Jan 2013.
Name LastLogonDate Server2 17/09/2001 That is all of the problems i have to solve to create my entire project. From these scenarios i can then do all of my other requests I have :-) Really excited to learn PowerBI, i have done so much trauling through forums and cant seem to find the right approach., but i'm also a complete noob and Trial and error isnt going as well as expected.
Thank you so much for any help of guidence you can provide.
DJJ2
Hi djj2,
In your scenario, the key is to extract the city code from the DistinguishedName column to a new column, and create a relationship between the two table with city code column, then you can easily show the expected result on the report.
1. Use the Split Column by Delimiter option to split the DistinguishedName column in Query Editor.
2. Remove other columns, only keep the column(DistinguishedName.3) which contains the city code.
3. Add a new custom column to remove "OU=" from the code value.
=Text.RemoveRange([DistinguishedName.3],0,3)
4. Remove "DistinguishedName.3" column.
5. Click "Close&Apply".
6. Create a relationship between the two table with city code column.
Then you should be able to easily show the expected result on the report.
Here is the sample pbix file for your reference. :smileyhappy:
Regards
