Core ConfigMgr Queries for Power BI

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

Please considered that I don’t take 100% credit for the post series queries.  These are made up from multiple sources in our community.  Without the community this may have not been possible. 

Core ConfigMgr Queries for Power BI

Getting Setup

For the step by step portion we will utilize the direct query template we created in the first series. 

We start with direct query template because we always can convert to Import.  We cannot convert Import to Direct Query.

Get to the Queries Section

  • Fill in the parameters from our template, and then select Load

Template Parameters

  • Select Edit Queries

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

Query SQL Server

  • Change the server and database values to your ConfigMgr parameters

ConfigMgr SQL 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

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. 

ConfigMgr Field Relationships

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

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

Multi Select CTRL

 

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

Standard ConfigMgr Queries for Power BI

  • 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)

Power BI Storage Mode Conversion

Power BI Storage Mode Conversion Zoom

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

PowerBI Getting Started Template

Share