I accidentally found the ConfigMgr CMPivot favorite query option to save your favorite queries. I thought it’s useful to share here so that you don’t have to save the CMPivot queries in OneNote or some other place. CMPivot favorite option is helpful when you download the CMPivot query that is published in Community Hub.
I have explained how to download and start using the CMPivot query from the Community Hub in the following post. ConfigMgr CMPivot Query to Find Clients not Part of Any Boundary Groups | SCCM. But, I was a bit confused to understand where I can find the downloaded CMPivot query.
How to Save CMPivot Queries?
It’s interesting to know that once you download the CMPivot query from Community Hub, that query will get automatically added as a CMPivot favorite query. Let’s see where is this favorite query icon in CMPivot.
- Launch the CMPivot tool from the console or standalone tool.
- Click on 📁🔍 the icon on the right-side corner of the CMPivot tool.
- You can see a list of queries in the following screenshot. Some of them downloaded this query from Community Hub and others are manually saved.
- I have downloaded another query from Community Hub published by MVP colleague Panu Saukko.
- I have closed and reopened the CMPivot tool.
- Click on the CMPivot Favorite query icon (📁🔍).
- The Windows Cumulative Update CMPivot query is available under the Favorite query.
// Shows the pie chart of Windows 10/2016/2019 cumulative update versions
OperatingSystem
| project Device, BuildNumber
| join (Registry('HKLM:\\Software\\Microsoft\\Windows NT\\CurrentVersion')
| where Property =='UBR' | project Device, CU=Value)
| project Device, FullBuild=strcat(BuildNumber,'.',tostring(CU))
| summarize count() by FullBuild
| render piechart with(title='Windows CUs')
vSMS_CMPivotFavorite
The SQL view vSMS_CMPivotFavorite gets you the details of all the CMPivot favorite queries of all the admins. So you might be able to see some interesting data from this view. Be careful if you want to keep your query a secret, even though I don’t recommend keeping this a secret.
You can run the following SQL query from SQL management studio to find out which are the favorite CMPivot queries of SCCM admins in your environment.
Select * from vSMS_CMPivotFavorite
The following table gives more information about CMPivot favorite query view.
Script Guid | User Name | Name | Query |
---|---|---|---|
7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14 | MEMCM\anoop | Shows when devices have restarted in the last seven days | OperatingSystem | where LastBootUpTime <= ago(7d) | summarize count() by bin(LastBootUpTime,1d) | render timechart |
7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14 | MEMCM\anoop | Clients without Boundary | CcmLog(‘LocationServices’) | where LogText contains ‘Client is not in any boundary group.’ | project Device, LogText, DateTime |
7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14 | MEMCM\anoop | Logical Disk per Device Chart | LogicalDisk | summarize sum( FreeSpace ) by Device | order by sum_ desc | render column chart |
Resources
Author
Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…
Do you know if it’s possible to add favorite queries to all users’ CMPivot favorites? I can see the favorites I’ve made, but other users don’t see my favorites. I think that would be a great feature to provide favorites to the helpdesk for example.
This is really great information Anoop! Thank you for sharing all of the useful and timely information that you do.