Let’s see how to identify the devices which are connected to SCCM infra via Cloud Management Gateway. In this post, I shall provide the details of the WQL and SQL queries to Identify Devices Connected via SCCM CMG.
Related Post – Fix SCCM Client CMG Communication Failure Error 0x87d0027e | ConfigMgr
Introduction
SCCM Cloud Management Gateway (CMG) is an Internet client management feature introduced by Microsoft as a pre-release feature in ConfigMgr CB 1610.
This feature not only simplifies the management but also reduces the complex configuration involved in the implementation of Internet-based client management (IBCM with reverse proxy).
With the release of the Co-Management feature in ConfigMgr CB 1710, the Cloud Management gateway plays an important role in transitioning from traditional management to modern management.
SCCM CMG Related Operational Question?
One of the major questions which came up after the successful implementation of Cloud Management Gateway was “How to identify the machines connected via SCCM Cloud Management Gateway?“. It’s really difficult to Identify the machines connected to the internet via Cloud Management Gateway. Initially, the machines were identified by querying the last update of the scan package source location using the below query.
select count(*) as count,LastScanPackageLocation
from v_updatescanstatus
group by LastScanPackageLocation
This query gives the machines which have scanned SUP from the intranet and internet. But still, we will not be able to find the last time these machines were online and when the machines were connected via the cloud management gateway.
Microsoft, with the release of ConfigMgr CB 1710, updated the schema to include the status of machines that are connected via Cloud Management Gateway. Similar to the client’s online/offline status, new status is available for the client machines to monitor the Internet status and online Management Point name.
Add respective fields (Device Online From Internet & Device Online Management Point)
Collection Members view after adding the above-mentioned fields.
How to Create Collection to Find out CMG Connected Devices
Well, this information is more helpful when we can create a collection based on Device Online from Internet criteria. Use the below WQL query to create a collection based on Device Online from Internet status.
Download the WQL Query for collection – How-to-Create-Collection-to-Find-out-CMG-Connected-Devices/How to Create Collection.sql at main · AnoopCNair/How-to-Create-Collection-to-Find-out-CMG-Connected-Devices (github.com)
Create the collection with Incremental Updates so that the collection will be updated automatically as and when the machines connect from the intranet/internet.
Once the collection is ready and if the admins want to restart the machines connected via cloud management gateway then they can use the latest feature introduced in ConfigMgr 1710. Restart works on machines connected via the SCCM cloud management gateway perfectly.
How to Create the SSRS or SQL reports to Find Out CMG Connected Devices
The same information can be queried from the CM database as well. If your management is asking for a report of online machines from the Internet, then an SSRS report can be created to pull this information. Use the below SQL query to pull the information.
select Name, SiteCode, ClientVersion, LastPolicyRequest, LastDDR as [Last Heartbeat], LastHardwareScan, max(CNLastOnlinetime) as [Last Online Time], max(CNLastOfflineTime) as [Last Offline Time], CNAccessMP as [Internet Access MP] from v_CollectionMemberClientBaselineStatus Where CNIsOnInternet =1 group by Name, SiteCode, ClientVersion, LastPolicyRequest, LastDDR, LastHardwareScan, CNLastOnlinetime, CNLastOfflineTime, CNAccessMP order by CNLastOnlineTime desc
References:
- How to export database schema
- What’s new in ConfigMgr 1710
- Cloud Management gateway
- All Co-Management Video tutorials in one post here.
Author
Rajul is a Technical Architect with more than 12 years of experience as an Endpoint Manager Configuration Manager and has hands-on experience in SCOM, SCVMM, SCEP, SQL, Azure, Intune, etc. His main area of interest is the design and implementation of ConfigMgr and OpsManager Infrastructure. He has a vast knowledge of ConfigMgr infrastructure & Client-side troubleshooting.
Very Informative and detailed info as usual 🙂
Is there a way to get a list of clients and the MP’s they’re connected to when they are connected via IBCM?
Very good post on managing CMG clients
Very informative post on managing CMG clients
This is what i was looking for. Thanks for the post.
I still have some confusion on the machines count which is reporting to CMG. You have give great idea so I can research more from here. Thanks
Thx for thoose reports but it seems to me that they show the client connected on “internet mode”, and not to the CMG this is different
Great post. This is my second one from your site to assist. Thanks!
Thank you for putting this together. I’m struggling to add a Collection parameter. I’ve tried a couple of tutorials with no luck.
is there a way to find machines which are connected to CMG in last 30 days
Please share if you have any query , i am unable to find .