cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
djj2 Frequent Visitor
Frequent Visitor

Active Directory Report Help

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: 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.

split1.PNGsplit2.PNGsplit3.PNG

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)

addcolumn1.PNG

4. Remove "DistinguishedName.3" column.

 

5. Click "Close&Apply".

 

6. Create a relationship between the two table with city code column.

relation1.PNG

Then you should be able to easily show the expected result on the report.

r2.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

3 REPLIES 3
v-ljerr-msft Super Contributor
Super Contributor

Re: Active Directory Report Help

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? Smiley Happy

 

Regards

djj2 Frequent Visitor
Frequent Visitor

Re: Active Directory Report Help

@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.

 

NameDistinguishedNameLastLogonDate
LONSERVER1CN=LONSERVER1,OU=Servers,OU=LON,OU=EMEA,DC=domain,DC=company,DC=com17/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.

 

RegionCountryCityCode
EMEAUKLondonLON

 

 Tables that I need to create from this are:

 

ServernameLocationCountryRegion
LONSERVER1LondonUKEMEA

 

 

With another table that counts the entries:

 

 Server Count
EMEA50
Uk20
London3

 

And a finally table that looks at the LastLogonDate that is older than 1st Jan 2013.

 

NameLastLogonDate
Server217/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 Super Contributor
Super Contributor

Re: 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.

split1.PNGsplit2.PNGsplit3.PNG

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)

addcolumn1.PNG

4. Remove "DistinguishedName.3" column.

 

5. Click "Close&Apply".

 

6. Create a relationship between the two table with city code column.

relation1.PNG

Then you should be able to easily show the expected result on the report.

r2.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards