ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite Endpoint Manager

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.
ConfigMgr CMPivot Favorite Query | SCCM  SQL Query to find out Saved | vSMS_CMPivotFavorite
ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite
  • I have downloaded another query from Community Hub published by MVP colleague Panu Saukko.
ConfigMgr CMPivot Favorite Query | SCCM  SQL Query to find out Saved | vSMS_CMPivotFavorite
ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite
  • 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')
ConfigMgr CMPivot Favorite Query | SCCM  SQL Query to find out Saved | vSMS_CMPivotFavorite
ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite

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.

Patch My PC

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
ConfigMgr CMPivot Favorite Query | SCCM  SQL Query to find out Saved | vSMS_CMPivotFavorite
ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite

The following table gives more information about CMPivot favorite query view.

Script GuidUser NameNameQuery
7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14MEMCM\anoopShows when devices have restarted in the last seven daysOperatingSystem | where LastBootUpTime <= ago(7d) | summarize count() by bin(LastBootUpTime,1d) | render timechart
7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14MEMCM\anoopClients without BoundaryCcmLog(‘LocationServices’) | where LogText contains ‘Client is not in any boundary group.’ | project Device, LogText, DateTime
7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14MEMCM\anoopLogical Disk per Device ChartLogicalDisk | summarize sum( FreeSpace ) by Device | order by sum_ desc | render column chart
ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite

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…

2 thoughts on “ConfigMgr CMPivot Favorite Query | SCCM SQL Query to find out Saved | vSMS_CMPivotFavorite Endpoint Manager”

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

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.