Active Directory Report Help

[djj2]

Hello All

I am complete noob to PowerBi but i need to generate some dashboards urgently from our AD for a project i am working on. 

I have created a table that has an abreviation for each city and its full name. 

Code   Location 

LON     London 

Then i have pulled in the data i want from AD into a table, however location can only be measured by the OU the device is in. So the only way i can identify a devices location is by for example the Distinguished Name field where OU=LON. 

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

I also then want to populate another table with the number of devices in that location.

Location.   Devices

London.     50

I appreciate these are very noob questions and your help would be greatly appreciated.

Thank you for your help

[v-ljerr-msft]

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? 

Regards

[djj2]

@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

[v-ljerr-msft · Accepted solution]

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. 

Regards