ConfigMgr Current Branch, Service Connection Point and what's in the telemetry

Article
05/02/2016

When installing ConfigMgr 1511 you are prompted whether you will be working in online or offline mode. A new site system role called Service connection point (this is the old Microsoft Intune connecter), will (when working in Online mode) communicate with Microsoft and upload telemetry data about your environment. The amount of telemetry data depends on what level you selected for collecting data usage, Basic, Enhanced, or Full.

You can find further data regarding the diagnostic and usage data for ConfigMgr here: https://technet.microsoft.com/en-US/library/mt613113.aspx
Through some simple SQL queries you can get a list of the tables, the stored procedures learning what is collected and how it is stored in ConfigMgr for telemetry. This should provide insight and knowledge for those who have company security officers asking for this data.

[Actions to get a list of all of the options for queries against telemetry]
1. Open SQL Management Studio
2. Select the ConfigMgr database
3. Create a new query
4. Enter the query:

select * from telemetryresults

5. Execute the query

[Actions to see the telemetry results (what is sent)]
1. Open SQL Management Studio
2. Select the ConfigMgr database
3. Create a new query
4. Enter the query:

select * from telemetryresults

or you can use

select * from tel_telemetryresults

5. Execute the query

Note the data is anonymized and the data does not contain any personal data, more further down.
There are several stored procedures in ConfigMgr that are a part of gathering the telemetry data, you can locate the stored procedures by performing the following query:

[Actions to get a list of stored procedures for telemetry]
1. Open SQL Management Studio
2. Select the ConfigMgr database
3. Create a new query
4. Enter the query:

SELECT distinct o.name As 'Stored Procedures',o.*
FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE o.name like 'tel_%' and o.xtype = 'P'

5. Execute the query

This returns a bit of information, so if you are looking to narrow it down to only data in regards to the telemetryresults table of the ConfigMgr database, then you can run the following query for the stored procedure instead:

[Actions to get a list of stored procedures for telemetryresults table]
1. Open SQL Management Studio
2. Select the ConfigMgr database
3. Create a new query
4. Enter the query:

SELECT distinct o.name As 'Stored Procedures',o.*
FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE o.name like 'tel_%' and o.xtype = 'P' and o.name in (select name from Telemetry)

5. Execute the query

When reviewing the query against the relemetryresults table, you will note that each row has a column that ends with a returning hash.
The hast is used to correlate data between the different rows in your telemetry results so the product team can store all the data coming from one customer together. This is done without making your company name or anything similar that could identify your environment, anonymizing the data. The process in anonymizing the data uses SHA256 hash ID.

You can look up your own hierarchy id and has to validate your own data by performing the following:

[Actions to return your ID and data]
1. Open SQL Management Studio
2. Select the ConfigMgr database
3. Create a new query
4. Enter the query:

Declare @tenantid as nvarchar(max)

select @TenantId = dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), [dbo].[fnGetHierarchyID]()),
'SHA256'))

Declare @hierarchyid as nvarchar(max)
select @hierarchyid = [dbo].[fnGetHierarchyID]()
select @hierarchyid, @tenantid

5. Execute the query

Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use

Main System Center blog: https://blogs.technet.com/b/systemcenter/

Configuration Manager Support Team blog: https://blogs.technet.com/configurationmgr/

Data Protection Manager Team blog: https://blogs.technet.com/dpm/

Orchestrator Team blog: https://blogs.technet.com/b/orchestrator/

Operations Manager Team blog: https://blogs.technet.com/momteam/

Service Manager Team blog: https://blogs.technet.com/b/servicemanager

Virtual Machine Manager Team blog: https://blogs.technet.com/scvmm

Microsoft Intune: https://blogs.technet.com/b/microsoftintune/

WSUS Support Team blog: https://blogs.technet.com/sus/

RMS blog: https://blogs.technet.com/b/rms/

App-V Team blog: https://blogs.technet.com/appv/

MED-V Team blog: https://blogs.technet.com/medv/