In this next installment for Getting Started with ConfigMgr and Power BI; we will walk through creating a set of core ConfigMgr queries for Power BI. We will first pull in in your devices, then your collections, and collection members. These are the foundation queries for every device query afterwards.
This post is part of a series of posts in my “Getting Started with ConfigMgr and Power BI”
Getting Started with ConfigMgr and Power BI
(this post)
ConfigMgr Make Model Power BI Report
ConfigMgr Client Boot Time Power BI Report
Getting Setup
For the step by step portion we will utilize the direct query template we created in the first series.
Get to the Queries Section
- Fill in the parameters from our template, and then select Load
- Select Edit Queries
Core ConfigMgr Queries for Power BI
The below sub sections outline our standard queries; capturing the refresh date, ConfigMgr system information, collections, and collection membership.
The following subsections all require getting data from SQL query source. They follow the below steps.
- Right click on your ConfigMgr folder and select New Query > SQL Server
- Change the server and database values to your ConfigMgr parameters
Capturing the Current Date and Time
To never question when the last time your data was updated, and the following SQL query. We will add a SQL statement to pull in the current date and time.
- Create a new SQL Server and paste the following SQL statement
SELECT ID = 1, GETDATE() as [TimeStamp]
- If may receive an message stating it could not be ran, select Retry
- Rename the SQL query to CM_SQLRefresh
ConfigMgr System Query
The system query for ConfigMgr brings in the identifiers needed and the ability to display detailed information. We will pull in the key identifiers, operating system, domain, and network information.
- Create a new SQL Server and paste the following SQL statement
SELECT sr.[ResourceID], sr.[Name0] as [Name], sr.[AD_Site_Name0] as [Location], sr.[Build01], sr.[Resource_Domain_OR_Workgr0] as [Domain],
sr.[User_Domain0] as [UserDomain], sr.[User_Name0] as [UserName], sr.[Distinguished_Name0] as [OULocation], sr.[Last_Logon_Timestamp0] as [LastLogonTimeStamp],
sr.[SMS_Unique_Identifier0] as [SMSGUID],
sr.[AADDeviceID] as [Azure ID],
CASE
when [sr].[Operating_System_Name_and0] like 'iOS%' then 'iOS'
when [sr].[Operating_System_Name_and0] like 'Android%' then 'Android'
when [sr].[Operating_System_Name_and0] like 'OS X%' then 'OS X'
when [sr].[Operating_System_Name_and0]='Microsoft Windows NT Advanced Server 6.3'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Server 6.3'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Advanced Server 6.4'
OR [sr].[Operating_System_Name_and0]='Windows Technical Preview for Enterprise 6.4'
then 'Server 2012 R2'
when [sr].[Operating_System_Name_and0]='Microsoft Windows NT Workstation 5.0' then 'Windows 2000 Pro'
when [sr].[Operating_System_Name_and0] like '%Windows%Workstation 6.1%' then 'Windows 7'
when [sr].[Operating_System_Name_and0] like '%Windows%Workstation 6.2%' then 'Windows 8'
when [sr].[Operating_System_Name_and0] like '%Windows%Workstation 6.3%'
OR [sr].[Operating_System_Name_and0] like '%Windows%Workstation 6.4%'
OR [sr].[Operating_System_Name_and0] like 'Windows 8.1%'
then 'Windows 8.1'
when [sr].[Operating_System_Name_and0] ='Microsoft Windows NT Advanced Server 5.2' then 'Server 2003'
when [sr].[Operating_System_Name_and0] ='Microsoft Windows NT Advanced Server 6.0' then 'Server 2008'
when [sr].[Operating_System_Name_and0] ='Microsoft Windows NT Advanced Server 6.1' then 'Server 2008 R2'
when [sr].[Operating_System_Name_and0] ='Microsoft Windows NT Server 6.1' then 'Server 2008 R2'
when [sr].[Operating_System_Name_and0] ='Microsoft Windows NT Advanced Server 6.2' then 'Server 2012'
when [sr].[Operating_System_Name_and0] ='Microsoft Windows NT Advanced Server 10.0' then 'Server 2016'
when [sr].[Operating_System_Name_and0] like 'Windows Phone%' then 'Windows Phone'
when [sr].[Operating_System_Name_and0] like 'Windows 10%'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Workstation 10.0'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Workstation 10.0 (Tablet Edition)'
then 'Windows 10'
when [sr].[Operating_System_Name_and0]='Microsoft Windows NT Advanced Server 10.0'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Server 10.0'
OR [sr].[Operating_System_Name_and0] like 'Windows Server 2016%'
then 'Server 2016'
when [sr].[Operating_System_Name_and0]='Microsoft Windows NT Server 6.0'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Workstation 6.0'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Workstation 6.0 (Tablet Edition)'
then 'Windows Vista'
when [sr].[Operating_System_Name_and0]='Microsoft Windows NT Workstation 5.1'
OR [sr].[Operating_System_Name_and0]='Microsoft Windows NT Server 5.2'
then 'Windows XP'
else [sr].[Operating_System_Name_and0]
end [Operating System],
os.[OperatingSystemSKU0] as [SKU],
os.[InstallDate0] as [OS Install Date],
sr.[Client_Version0] as [Client Version],
net.[IPAddress] as [IP Address],
net.[DefaultIPGateway0] as [Default Gateway]
FROM v_R_System as sr
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM as os ON os.[ResourceID] = sr.[ResourceID]
OUTER APPLY (
SELECT TOP 1 LEFT(net.[IPAddress0], LEN('%.%.%.%.%.%.%') - patIndex(',', net.[IPAddress0])) AS [IPAddress], net.[DefaultIPGateway0]
FROM dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION as net
WHERE net.[ResourceID] = sr.[ResourceID]
ORDER BY net.[IPAddress0] desc
) as net
WHERE sr.[Obsolete0] = 0 and sr.[ResourceType] = 5 and sr.[Decommissioned0] = 0
- If may receive an message stating it could not be ran, select Retry
- Rename the SQL query to CM_Devices
ConfigMgr Device Collections
The next key query is importing your environment’s device collections. We will be pulling in the collection ID and name. This will allow us in later steps to filter device information based on collections.
- Create a new SQL Server and paste the following SQL statement
SELECT c.[CollectionID], c.[Name] FROM [dbo].[v_Collection] as c WHERE c.[CollectionType] = 2
- If may receive an message stating it could not be ran, select Retry
- Rename the SQL query to CM_DevicesCollections
ConfigMgr Devices Collection Membership
To be able to filter by collection membership, we now need to pull the membership information for all the resources in ConfigMgr.
- Create a new SQL Server and paste the following SQL statement
SELECT ccm.[CollectionID] as [Collection ID], ccm.[ResourceID] as [Resource ID] FROM [dbo].[v_ClientCollectionMembers] as ccm INNER JOIN [dbo].[v_Collection] as c ON ccm.[CollectionID] = c.[CollectionID] INNER JOIN [dbo].[v_R_System] as s ON s.[ResourceID] = ccm.[ResourceID] AND s.[Obsolete0] = 0 AND s.[Decommissioned0] = 0 WHERE c.[CollectionType] = 2
- If may receive an message stating it could not be ran, select Retry
- Rename the SQL query to CM_DevicesCollectionMembership
- Select Close & Apply
Validating the Data Relationships
Power BI Desktop will not automatically create relationships for Direct Query storage. To validate and add select the Manage Relationships button under the Home ribbon menu.
Finalizing the Core Template for ConfigMgr
Below will creation our base queries for future reporting on ConfigMgr data. We can now close the query editor, make some visualization updates, and save our new template.
Adding Basic Visualizations
The below guidelines will walk through adding a basic title, collection selection slicer, and the last ConfigMgr SQL timestamp.
- Under the Home ribbon in the Insert section select Text Box
- Place it by your logo, format as a title, and type Insert Title Here
- Turn off the background under visualizations
- Under visualization select the data slicer
- Drag the CM_DevicesCollections : Name column under field
- Double click on the Name field and rename to Collections
- Change the slicer to Dropdown
- Resize the visualization to better size for your collections name
- Select the paint roller under Visualizations and select Selection Controls
- Turn off Multi-select with CTRL
- This a personal prefrence. I do not want my users to have to hold down the CTRL button to select multiple entries
- Expand the CM_SQLRefresh under Fields and select TimeStamp
- Select Modeling ribbon menu and under Formating and select Format
- Format to your desired date and time format
- Select the Card visualizations and drag the TimeStamp column under Fields
- Under the paint roller reduce the font size and update the rename the field to Last SQL Update
Once you have something similar to below, now save your new import ConfigMgr Power BI template.
- Save your updated template for future use
Converting to Import Storage Mode
Similarly, once you grow your Power BI datasets to tie together other data sources, you will have to utilize the import storage mode. These data sets can include Desktop Analytics and Intune. Above all, you can change this later as you grow your Power BI reports.
- Click on the bottom right corner where it states Storage Mode: DirectQuery (click to change)
- Save your updated template for future use
Conclusion
To review, we went through creating a great base template to start reporting on ConfigMgr devices. We have included the resource ID, which ties all other devices views to it. We also included the SMS GUID and Azure AD ID for future purposes. And gaining the ability to filter those devices based on collection membership.
I have updated the download file to include this series first post templates with these. Hope you enjoy and stay tuned for the next post in this series.
