Hi all
I am very new to Power BI and I have been asked to put together a way of reporting that the company can use to solve a variety of problems. I have power BI available to me and with the AD integration, I think it will be the perfect tool. I have already put together some dashboards but i am stuck on the approach for the remaining ones.
Here is exactly what I am trying to achieve for my 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 guidance.
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.
[TABLE="width: 1"]
<tbody>[TR]
[TD][TABLE="width: 1"]
<tbody>[TR]
[TD]Name[/TD]
[TD]DistinguishedName[/TD]
[TD]LastLogonDate[/TD]
[/TR]
[TR]
[TD]LONSERVER1[/TD]
[TD]CN=LONSERVER1,OU=Servers,OU=LON,OU=EMEA,DC=domain,DC=company,DC=com[/TD]
[TD]17/09/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to create a query (by merge I think) on this table that does a lookup on the DistinguishedName matches the OU=LON entry and then match each row to another table that has the following.
[TABLE="width: 1"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]UK[/TD]
[TD]London[/TD]
[TD]LON[/TD]
[/TR]
</tbody>[/TABLE]
Tables that I need to create from this are:
[TABLE="width: 1"]
<tbody>[TR]
[TD]Servername[/TD]
[TD]Location[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]LONSERVER1[/TD]
[TD]London[/TD]
[TD]UK[/TD]
[TD]EMEA[/TD]
[/TR]
</tbody>[/TABLE]
With another table that counts the entries:
[TABLE="width: 1"]
<tbody>[TR]
[TD][/TD]
[TD]Server Count[/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Uk[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
And a final table that looks at the LastLogonDate that is older than 1st Jan 2013.
[TABLE="width: 1"]
<tbody>[TR]
[TD]Name[/TD]
[TD]LastLogonDate[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]17/09/2001[/TD]
[/TR]
</tbody>[/TABLE]
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 trawling through forums and can't seem to find the right approach., but I'm also a complete noob and Trial and error isn't going as well as expected.
Thank you so much for any help of guidence you can provide.
DJJ2
I am very new to Power BI and I have been asked to put together a way of reporting that the company can use to solve a variety of problems. I have power BI available to me and with the AD integration, I think it will be the perfect tool. I have already put together some dashboards but i am stuck on the approach for the remaining ones.
Here is exactly what I am trying to achieve for my 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 guidance.
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.
[TABLE="width: 1"]
<tbody>[TR]
[TD][TABLE="width: 1"]
<tbody>[TR]
[TD]Name[/TD]
[TD]DistinguishedName[/TD]
[TD]LastLogonDate[/TD]
[/TR]
[TR]
[TD]LONSERVER1[/TD]
[TD]CN=LONSERVER1,OU=Servers,OU=LON,OU=EMEA,DC=domain,DC=company,DC=com[/TD]
[TD]17/09/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to create a query (by merge I think) on this table that does a lookup on the DistinguishedName matches the OU=LON entry and then match each row to another table that has the following.
[TABLE="width: 1"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]UK[/TD]
[TD]London[/TD]
[TD]LON[/TD]
[/TR]
</tbody>[/TABLE]
Tables that I need to create from this are:
[TABLE="width: 1"]
<tbody>[TR]
[TD]Servername[/TD]
[TD]Location[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]LONSERVER1[/TD]
[TD]London[/TD]
[TD]UK[/TD]
[TD]EMEA[/TD]
[/TR]
</tbody>[/TABLE]
With another table that counts the entries:
[TABLE="width: 1"]
<tbody>[TR]
[TD][/TD]
[TD]Server Count[/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Uk[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
And a final table that looks at the LastLogonDate that is older than 1st Jan 2013.
[TABLE="width: 1"]
<tbody>[TR]
[TD]Name[/TD]
[TD]LastLogonDate[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]17/09/2001[/TD]
[/TR]
</tbody>[/TABLE]
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 trawling through forums and can't seem to find the right approach., but I'm also a complete noob and Trial and error isn't going as well as expected.Thank you so much for any help of guidence you can provide.
DJJ2
