Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Off2w0rk
New Member

Lync/Skype lcscdr database

Hi all,

 

Im pretty new with PowerBI and its component in Excel and also new using SQL query.

Im managing Skype for business server 2015 in our environment and would like to build up some reports. As far as i can see, there are no Country table so I have to sort users out manually.

 

In PowerBI or Excel i connect to sql server, define database and define the following sql query:

 

 

Select *

FROM[LcsCDR].[dbo].[VoipDetailsview]

Where ToUri like '%MainSwitch%' or ToUri like '%Accounting%'

 

This i wil get all Uri, which in this case is response groups. It works fine, but now i would like to do user query from Active Directory OU instead.

So if CEO wants all statistic for all users in France office (has own OU), is it possible?

What if CEO wants statistic for everyone in French accounting team (does not have own OU, but has it in Department attribute), is this possible as well?

 

Thanks!

 

3 REPLIES 3
JasonDunbar
Resolver I
Resolver I

Hi there, you could try checking whether or not the Active Directory connector in Power BI Desktop will do what you want?

 

If you click on the "Get Data" button:

PBI_Get_Data_Button.png

Then search for 'Active', you'll see the Active Directory data source. When you click it, it'll then ask you for a domain.

PBI_Desktop_AD_Data.png

 

Though I've not used it myself and not currently in a position to test it (I don't have access to a domain right now), I suspect that it'll at minimum let you bulk export some users so that you can shape the dataset in Power BI (e.g create relationsips, apply filters etc.)

 

Hope that helps.

____________________________________________________
My other computer is an Azure data centre.

Hi Jason and thanks for reply! 🙂

 

I did an Active Directory query as you described, but it shows a long list of tables and most of them are empty. If i choose user (first table) not many user attributes are shown. IE missing country,manager and other basic attributes. Account im using is domain admin so it should have access to "every" attributes.

 

I could do export using powershell and get all attribute i want, but it is static and i have to manually export them when new user is introduced or leaves the company.

Other ideas are much appriciated!

The default view of users will most likely give you a number of 'Record' columns which you'll then have to expand to get at the information that is going to be useful for you.

 

Just tried it myself, right now. That's one complicated schema 🙂

____________________________________________________
My other computer is an Azure data centre.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.