A while ago, I wrote a pretty extensive blog on telemetry, how it worked, What data was collected, And most importantly how to see what the hashed data that is uploaded actually means.

You can read that blog here: http://www.oscc.be/sccm/configuration-manager-telemetry-usage-metrics-work-in-progress-(draft)/

I ended that blog on an open note, and with a couple of stored procedures that generated telemetry data containing hashed data that still needed SQL queries to see the unhashed data.

Update: This is the list of stored procedures validated up until 1606

This blog post is an attempt to list all the stored procedures that contain hashed data, and the corresponding modified SQL statement to see the data unhashed.

The stored procedures that contain hashed data are:

Procedure : TEL_Content_DPState

Original Query

SELECT dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), [ContentID]), 'SHA256') AS [ContentID]
	,[State]
	,COUNT([State]) AS [StateCount]
FROM [ContentDPMap] WITH (NOLOCK)
WHERE [AccessType] = 1
GROUP BY [ContentID]
	,[State]
ORDER BY [ContentID] ASC ,[State] ASC

Query Including the Unhashed data alongside the hashed data (ContentID Field is hashed)

SELECT dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), [ContentID]), 'SHA256') AS [HashedContentID]
	,ContentID
	,[State]
	,COUNT([State]) AS [StateCount]
FROM [ContentDPMap] WITH (NOLOCK)
WHERE [AccessType] = 1
GROUP BY [ContentID]
	,[State]
ORDER BY [ContentID] ASC
	,[State] ASC

 

TEL_Content_Package

Original Query

SELECT dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), [PkgID]), 'SHA256') AS [PkgID]
,      [SourceSize]                                                      
,      [ShareType]                                                       
,      [PackageType]                                                     
,      [PkgFlags]                                                        
,      [LastRefresh]                                                     
,      [SourceVersion]                                                   
,      (
CASE WHEN [AlternateContentProviders] = ''
	OR [AlternateContentProviders] IS NULL THEN 0
                                           ELSE 1
END ) AS [AlternateContentProviders]
FROM [SMSPackages_G] WITH (NOLOCK)

Query Including the Unhashed data alongside the hashed data

SELECT dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), [PkgID]), 'SHA256') AS [PkgID]
,	   PkgID
,      [SourceSize]                                                      
,      [ShareType]                                                       
,      [PackageType]                                                     
,      [PkgFlags]                                                        
,      [LastRefresh]                                                     
,      [SourceVersion]                                                   
,      (
CASE WHEN [AlternateContentProviders] = ''
	OR [AlternateContentProviders] IS NULL THEN 0
                                           ELSE 1
END ) AS [AlternateContentProviders]
FROM [SMSPackages_G] WITH (NOLOCK)

TEL_DCM_BuiltinSettings

Original Query

;WITH RefSettingList AS
(
    SELECT st.Setting_UniqueID
      , dcm_ci.CI_ID                 
    FROM v_CISettings st 
    JOIN CI_SettingReferences ref on ref.Setting_ID = st.Setting_ID 
    JOIN CI_Rules rl on rl.Rule_ID = ref.Rule_ID         
    JOIN CI_ConfigurationItems settings_ci on settings_ci.CI_ID = st.CI_ID 
    JOIN CI_ConfigurationItems dcm_ci on dcm_ci.CI_ID = rl.CI_ID 
    WHERE dcm_ci.IsLatest = 1 and dcm_ci.IsTombstoned = 0 and dcm_ci.IsExpired = 0 /* Limit to latest CI references */
     and settings_ci.IsLatest = 1 and settings_ci.IsTombstoned = 0 and settings_ci.IsExpired = 0 
     and settings_ci.IsUserDefined = 0 /* Limit to built-in setting definition CIs */
),
DeployedSettingList AS
(
    --Cover direct CI deployments 
    SELECT sl.Setting_UniqueID, sl.CI_ID 
    FROM RefSettingList sl 
    JOIN CI_AssignmentTargetedCIs cit on cit.CI_ID = sl.CI_ID     
    JOIN CI_CIAssignments cia on cia.AssignmentID = cit.AssignmentID 
    WHERE cia.IsTombstoned = 0 
    UNION
    --Cover CI deployments via Baseline 
    SELECT sl.Setting_UniqueID, sl.CI_ID 
    FROM RefSettingList sl 
    JOIN CI_ConfigurationItemRelations cir on cir.ToCI_ID = sl.CI_ID 
    JOIN CI_ConfigurationItems baseline on baseline.CI_ID = cir.FromCI_ID     
    JOIN CI_AssignmentTargetedCIs cit on cit.CI_ID = baseline.CI_ID     
    JOIN CI_CIAssignments cia on cia.AssignmentID = cit.AssignmentID 
    WHERE cia.IsTombstoned = 0 
     and baseline.CIType_ID = 2 and baseline.IsLatest = 1 and baseline.IsExpired = 0 
     and baseline.IsTombstoned = 0 and baseline.IsEnabled = 1 
)     
--Set upper bound in case built-in set expands rapidly 
SELECT TOP 1000 
  dbo.fnConvertBinaryToBase64String(
    dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), st.Setting_UniqueID), 'SHA256')) AS Setting_UniqueID,             
  COUNT(distinct st.CI_ID) AS CountDeployedCIs 
FROM DeployedSettingList st     
GROUP BY st.Setting_UniqueID 
ORDER BY CountDeployedCIs DESC

Query Including the Unhashed data alongside the hashed data

;WITH RefSettingList AS
(
    SELECT st.Setting_UniqueID
      , dcm_ci.CI_ID                 
    FROM v_CISettings st 
    JOIN CI_SettingReferences ref on ref.Setting_ID = st.Setting_ID 
    JOIN CI_Rules rl on rl.Rule_ID = ref.Rule_ID         
    JOIN CI_ConfigurationItems settings_ci on settings_ci.CI_ID = st.CI_ID 
    JOIN CI_ConfigurationItems dcm_ci on dcm_ci.CI_ID = rl.CI_ID 
    WHERE dcm_ci.IsLatest = 1 and dcm_ci.IsTombstoned = 0 and dcm_ci.IsExpired = 0 /* Limit to latest CI references */
     and settings_ci.IsLatest = 1 and settings_ci.IsTombstoned = 0 and settings_ci.IsExpired = 0 
     and settings_ci.IsUserDefined = 0 /* Limit to built-in setting definition CIs */
),
DeployedSettingList AS
(
    --Cover direct CI deployments 
    SELECT sl.Setting_UniqueID, sl.CI_ID 
    FROM RefSettingList sl 
    JOIN CI_AssignmentTargetedCIs cit on cit.CI_ID = sl.CI_ID     
    JOIN CI_CIAssignments cia on cia.AssignmentID = cit.AssignmentID 
    WHERE cia.IsTombstoned = 0 
    UNION
    --Cover CI deployments via Baseline 
    SELECT sl.Setting_UniqueID, sl.CI_ID 
    FROM RefSettingList sl 
    JOIN CI_ConfigurationItemRelations cir on cir.ToCI_ID = sl.CI_ID 
    JOIN CI_ConfigurationItems baseline on baseline.CI_ID = cir.FromCI_ID     
    JOIN CI_AssignmentTargetedCIs cit on cit.CI_ID = baseline.CI_ID     
    JOIN CI_CIAssignments cia on cia.AssignmentID = cit.AssignmentID 
    WHERE cia.IsTombstoned = 0 
     and baseline.CIType_ID = 2 and baseline.IsLatest = 1 and baseline.IsExpired = 0 
     and baseline.IsTombstoned = 0 and baseline.IsEnabled = 1 
)     
--Set upper bound in case built-in set expands rapidly 
SELECT TOP 1000 
  dbo.fnConvertBinaryToBase64String(
    dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), st.Setting_UniqueID), 'SHA256')) AS Setting_UniqueID
	,st.Setting_UniqueID             
    ,COUNT(distinct st.CI_ID) AS CountDeployedCIs 
FROM DeployedSettingList st     
GROUP BY st.Setting_UniqueID 
ORDER BY CountDeployedCIs DESC

TEL_EAS_Connectors

Original Query

DECLARE @ConnectorId TABLE ( ConnectorID nvarchar(40) not null )

DECLARE @ExchangeConnectorTable TABLE ( ConnectorID              nvarchar(40) not null
,                                       ConfiguredExchangeServer nvarchar(256) not null
,                                       IsHosted                 int not null )

DECLARE @ExchangePolicyTable TABLE ( ConnectorID      nvarchar(40) not null
,                                    IsPolicyDeployed bit not null )

-- Get unique IDs for exchange connectors
INSERT INTO @ConnectorID
SELECT DISTINCT SUBSTRING(scpl.Name,17, len(scpl.Name))
FROM       SC_Component                    as sc  
INNER JOIN vSMS_SC_Component_PropertyLists    scpl on scpl.ID=sc.ID
WHERE sc.ComponentName LIKE '%Exchange%'
	AND scpl.Name LIKE 'ConnectorConfig%'

-- Get Exchange Server and IsHosted infromation from ConnectorConfig_ property list for each exchange connector
INSERT INTO @ExchangeConnectorTable
SELECT DISTINCT conID.ConnectorID       
,               SCPL.Value               AS ConfiguredExchangeServer
,               CAST(SCPL2.Value AS INT) AS IsHosted
FROM       SC_Component                    AS SC   
INNER JOIN vSMS_SC_Component_PropertyLists AS SCPL  ON SC.ID=SCPL.ID AND SCPL.ValueIndex=0
INNER JOIN vSMS_SC_Component_PropertyLists AS SCPL2 ON SCPL.PropertyListID=SCPL2.PropertyListID AND SCPL2.ValueIndex=1
INNER JOIN @ConnectorId                       conID ON SCPL.Name like '%' + conID.ConnectorID + '%'
WHERE (SC.ComponentName = 'SMS_EXCHANGE_CONNECTOR' AND SCPL.Name like 'Connector%')

--Get IsPolicyDeployed information from ConfiguredSettings_ property list for each exchange connector
INSERT INTO @ExchangePolicyTable
SELECT DISTINCT conID.ConnectorID                          
,               (case when SCPL3.Value is NULL THEN 0
                                               ELSE 1 END ) AS IsManagedBySCCM
FROM       SC_Component                    AS SC   
INNER JOIN vSMS_SC_Component_PropertyLists AS SCPL3 ON SC.ID=SCPL3.ID
INNER JOIN @ConnectorId                       conID ON SCPL3.Name like '%' + conID.ConnectorID + '%'
WHERE (SC.ComponentName = 'SMS_EXCHANGE_CONNECTOR' AND SCPL3.Name like 'Configured%')

SELECT (CASE WHEN COUNT(*) = 1 THEN NULL
                               ELSE COUNT(*)
END )                       as DeviceCount
,      (CASE WHEN eas.ExchangeServer IS NULL THEN NULL
                                             ELSE (dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), eas.ExchangeServer ), 'SHA256')))
END )                       AS DeviceReportedExchangeServer
,      (CASE WHEN ect.ConfiguredExchangeServer IS NULL THEN NULL
                                                       ELSE (dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), ect.ConfiguredExchangeServer ), 'SHA256')))
END )                       AS ConfiguredExchangeServer
,      ect.IsHosted        
,      ept.IsPolicyDeployed
FROM      EAS_Property            eas
FULL JOIN @ExchangeConnectorTable ect ON ect.ConfiguredExchangeServer like '%'+ eas.ExchangeServer + '%'
FULL JOIN @ExchangePolicyTable    ept ON ept.ConnectorID = ect.ConnectorID
GROUP BY eas.ExchangeServer
,        ect.ConfiguredExchangeServer
,        ect.IsHosted
,        ept.IsPolicyDeployed

Query Including the Unhashed data alongside the hashed data

DECLARE @ConnectorId TABLE ( ConnectorID nvarchar(40) not null )

DECLARE @ExchangeConnectorTable TABLE ( ConnectorID              nvarchar(40) not null
,                                       ConfiguredExchangeServer nvarchar(256) not null
,                                       IsHosted                 int not null )

DECLARE @ExchangePolicyTable TABLE ( ConnectorID      nvarchar(40) not null
,                                    IsPolicyDeployed bit not null )

-- Get unique IDs for exchange connectors
INSERT INTO @ConnectorID
SELECT DISTINCT SUBSTRING(scpl.Name,17, len(scpl.Name))
FROM       SC_Component                    as sc  
INNER JOIN vSMS_SC_Component_PropertyLists    scpl on scpl.ID=sc.ID
WHERE sc.ComponentName LIKE '%Exchange%'
	AND scpl.Name LIKE 'ConnectorConfig%'

-- Get Exchange Server and IsHosted infromation from ConnectorConfig_ property list for each exchange connector
INSERT INTO @ExchangeConnectorTable
SELECT DISTINCT conID.ConnectorID       
,               SCPL.Value               AS ConfiguredExchangeServer
,               CAST(SCPL2.Value AS INT) AS IsHosted
FROM       SC_Component                    AS SC   
INNER JOIN vSMS_SC_Component_PropertyLists AS SCPL  ON SC.ID=SCPL.ID AND SCPL.ValueIndex=0
INNER JOIN vSMS_SC_Component_PropertyLists AS SCPL2 ON SCPL.PropertyListID=SCPL2.PropertyListID AND SCPL2.ValueIndex=1
INNER JOIN @ConnectorId                       conID ON SCPL.Name like '%' + conID.ConnectorID + '%'
WHERE (SC.ComponentName = 'SMS_EXCHANGE_CONNECTOR' AND SCPL.Name like 'Connector%')

--Get IsPolicyDeployed information from ConfiguredSettings_ property list for each exchange connector
INSERT INTO @ExchangePolicyTable
SELECT DISTINCT conID.ConnectorID                          
,               (case when SCPL3.Value is NULL THEN 0
                                               ELSE 1 END ) AS IsManagedBySCCM
FROM       SC_Component                    AS SC   
INNER JOIN vSMS_SC_Component_PropertyLists AS SCPL3 ON SC.ID=SCPL3.ID
INNER JOIN @ConnectorId                       conID ON SCPL3.Name like '%' + conID.ConnectorID + '%'
WHERE (SC.ComponentName = 'SMS_EXCHANGE_CONNECTOR' AND SCPL3.Name like 'Configured%')

SELECT (CASE WHEN COUNT(*) = 1 THEN NULL
                               ELSE COUNT(*)
END )                       as DeviceCount
,      (CASE WHEN eas.ExchangeServer IS NULL THEN NULL
                                             ELSE (dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), eas.ExchangeServer ), 'SHA256')))
END )                       AS DeviceReportedExchangeServer
,      (CASE WHEN eas.ExchangeServer IS NULL THEN NULL
                                             ELSE (eas.ExchangeServer)
END )                       AS DeviceReportedExchangeServer
,      (CASE WHEN ect.ConfiguredExchangeServer IS NULL THEN NULL
                                                       ELSE (dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), ect.ConfiguredExchangeServer ), 'SHA256')))
END )                       AS ConfiguredExchangeServer
,      (CASE WHEN ect.ConfiguredExchangeServer IS NULL THEN NULL
                                                       ELSE (ect.ConfiguredExchangeServer)
END )                       AS ConfiguredExchangeServer
,      ect.IsHosted        
,      ept.IsPolicyDeployed
FROM      EAS_Property            eas
FULL JOIN @ExchangeConnectorTable ect ON ect.ConfiguredExchangeServer like '%'+ eas.ExchangeServer + '%'
FULL JOIN @ExchangePolicyTable    ept ON ept.ConnectorID = ect.ConnectorID
GROUP BY eas.ExchangeServer
,        ect.ConfiguredExchangeServer
,        ect.IsHosted
,        ept.IsPolicyDeployed

 

TEL_MAM_PolicySettingsStatistics4Deployment2Collection

Original Query

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
/* Clarification for the return values:
MAMPolicyName: the name of MAM policy
MAMAppDeploymentCount: How many deployment used this MAM policy
DistributeCollectionCount: How many collections are target for this MAM policy
*/

;WITH MAM_DCMDIGEST_SIMPLEVERSION
AS
(
	SELECT ci.CI_ID                                                                                                                               AS MAMPOLICY_CIID
	,      ci.SDMPackageDigest.value(
	'declare namespace dcm="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration";declare namespace name="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules";(/dcm:DesiredConfigurationDigest/dcm:AbstractConfigurationItem/name:Annotation/name:DisplayName/@Text)[1]', 'NVARCHAR(MAX)') AS MAMPOLICY_Name
	FROM CI_ConfigurationItems ci
	WHERE ci.CIType_ID = 70
)
,     ASSIGNMENT_CIS_MAPPING
AS
(
	SELECT ciAssignment.AssignmentID      
	,      ciAssignment.ParentAssignmentID
	,      ciAssignmentTarget.CI_ID        AS FromCI_ID
	,      ciRelation.ToCI_ID              AS ToCI_ID
	,      ciAssignment.AssignmentName    
	,      ciAssignment.TargetCollectionID
	FROM       CI_CIAssignments              ciAssignment      
	INNER JOIN vCI_AssignmentTargetedCIs     ciAssignmentTarget ON ciAssignmentTarget.AssignmentID = ciAssignment.AssignmentID
	INNER JOIN CI_ConfigurationItemRelations ciRelation         ON ciAssignmentTarget.CI_ID = ciRelation.FromCI_ID
	WHERE ciAssignment.ParentAssignmentID IS NOT NULL
)
,     MAM_POLICY_MAPPING
AS
(
	SELECT ciRelation.FromCI_ID AS FromCI_MAMID
	,      ciRelation.ToCI_ID   AS ToCI_MAMID
	FROM       CI_ConfigurationItemRelations ciRelation
	INNER JOIN CI_ConfigurationItems         ci         ON ciRelation.ToCI_ID = ci.CI_ID
	WHERE ciRelation.RelationType=23 AND ci.CIType_ID = 70
)
,     MAM_DCMDIGEST
AS
(
	SELECT mamPolicy.ToCI_MAMID             AS MAMPOLICY_CIID
	,      mamAssignment.FromCI_ID          AS MAMPOLICY_BLID
	,      mamAssignment.ToCI_ID            AS MAMPOLICY_APPID
	,      mamProperty.MAMPOLICY_Name      
	,      mamAssignment.AssignmentID       AS MAMAPP_AssignmentID
	,      mamAssignment.ParentAssignmentID AS MAMAPP_ParentAssignmentID
	,      mamAssignment.TargetCollectionID AS TargetCollectionID
	FROM       MAM_POLICY_MAPPING          mamPolicy    
	INNER JOIN ASSIGNMENT_CIS_MAPPING      mamAssignment ON (mamPolicy.FromCI_MAMID = mamAssignment.ToCI_ID)
	INNER JOIN MAM_DCMDIGEST_SIMPLEVERSION mamProperty   ON (mamPolicy.ToCI_MAMID = mamProperty.MAMPOLICY_CIID)
)
SELECT dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), MAMPOLICY_Name), 'SHA256')) AS MAMPolicyName
,      COUNT(MAMAPP_AssignmentID)                                                                                   AS MAMAppDeploymentCount
,      COUNT(TargetCollectionID)                                                                                    AS DistributedCollectionCount
FROM MAM_DCMDIGEST
GROUP BY MAMPOLICY_Name
ORDER BY MAMPOLICY_Name

Query Including the Unhashed data alongside the hashed data

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
/* Clarification for the return values:
MAMPolicyName: the name of MAM policy
MAMAppDeploymentCount: How many deployment used this MAM policy
DistributeCollectionCount: How many collections are target for this MAM policy
*/

;WITH MAM_DCMDIGEST_SIMPLEVERSION
AS
(
	SELECT ci.CI_ID                                                                                                                               AS MAMPOLICY_CIID
	,      ci.SDMPackageDigest.value(
	'declare namespace dcm="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration";declare namespace name="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules";(/dcm:DesiredConfigurationDigest/dcm:AbstractConfigurationItem/name:Annotation/name:DisplayName/@Text)[1]', 'NVARCHAR(MAX)') AS MAMPOLICY_Name
	FROM CI_ConfigurationItems ci
	WHERE ci.CIType_ID = 70
)
,     ASSIGNMENT_CIS_MAPPING
AS
(
	SELECT ciAssignment.AssignmentID      
	,      ciAssignment.ParentAssignmentID
	,      ciAssignmentTarget.CI_ID        AS FromCI_ID
	,      ciRelation.ToCI_ID              AS ToCI_ID
	,      ciAssignment.AssignmentName    
	,      ciAssignment.TargetCollectionID
	FROM       CI_CIAssignments              ciAssignment      
	INNER JOIN vCI_AssignmentTargetedCIs     ciAssignmentTarget ON ciAssignmentTarget.AssignmentID = ciAssignment.AssignmentID
	INNER JOIN CI_ConfigurationItemRelations ciRelation         ON ciAssignmentTarget.CI_ID = ciRelation.FromCI_ID
	WHERE ciAssignment.ParentAssignmentID IS NOT NULL
)
,     MAM_POLICY_MAPPING
AS
(
	SELECT ciRelation.FromCI_ID AS FromCI_MAMID
	,      ciRelation.ToCI_ID   AS ToCI_MAMID
	FROM       CI_ConfigurationItemRelations ciRelation
	INNER JOIN CI_ConfigurationItems         ci         ON ciRelation.ToCI_ID = ci.CI_ID
	WHERE ciRelation.RelationType=23 AND ci.CIType_ID = 70
)
,     MAM_DCMDIGEST
AS
(
	SELECT mamPolicy.ToCI_MAMID             AS MAMPOLICY_CIID
	,      mamAssignment.FromCI_ID          AS MAMPOLICY_BLID
	,      mamAssignment.ToCI_ID            AS MAMPOLICY_APPID
	,      mamProperty.MAMPOLICY_Name      
	,      mamAssignment.AssignmentID       AS MAMAPP_AssignmentID
	,      mamAssignment.ParentAssignmentID AS MAMAPP_ParentAssignmentID
	,      mamAssignment.TargetCollectionID AS TargetCollectionID
	FROM       MAM_POLICY_MAPPING          mamPolicy    
	INNER JOIN ASSIGNMENT_CIS_MAPPING      mamAssignment ON (mamPolicy.FromCI_MAMID = mamAssignment.ToCI_ID)
	INNER JOIN MAM_DCMDIGEST_SIMPLEVERSION mamProperty   ON (mamPolicy.ToCI_MAMID = mamProperty.MAMPOLICY_CIID)
)
SELECT dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), MAMPOLICY_Name), 'SHA256')) AS MAMPolicyName
,	   MAMPOLICY_Name as [MAMPolicyName Unhashed]	
,      COUNT(MAMAPP_AssignmentID)                                                                                   AS MAMAppDeploymentCount
,      COUNT(TargetCollectionID)                                                                                    AS DistributedCollectionCount
FROM MAM_DCMDIGEST
GROUP BY MAMPOLICY_Name
ORDER BY MAMPOLICY_Name

TEL_Perf_TableSize

Original Query

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100 dbo.fnConvertBinaryToBase64String(
dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), a2.name), 'SHA256')
)                                                              AS Table_name
,              a1.rows                                         as Records
,              (a1.reserved)* 8                                AS Reserved_kb
,              a1.data * 8                                     AS Data_kb
,              (CASE WHEN (a1.used) > a1.data THEN (a1.used) - a1.data
                                              ELSE 0 END ) * 8 AS Indexes_kb
FROM       (SELECT ps.object_id                                                                                         
,                  SUM (CASE WHEN (ps.index_id < 2) THEN row_count
                                                    ELSE 0 END)                                                          AS [rows]
,                  SUM (ps.reserved_page_count)                                                                          AS reserved
,                  SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                                                    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data
,                  SUM (ps.used_page_count)                                                                              AS Used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
)                          AS a1
INNER JOIN sys.all_objects    a2 ON (a1.object_id = a2.object_id)
INNER JOIN sys.schemas        a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a1.data DESC

 

Query Including the Unhashed data alongside the hashed data

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100 dbo.fnConvertBinaryToBase64String(
dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), a2.name), 'SHA256')
)                                                              AS Table_name
,			   a2.name										   AS [Table_name unhashed]	
,              a1.rows                                         as Records
,              (a1.reserved)* 8                                AS Reserved_kb
,              a1.data * 8                                     AS Data_kb
,              (CASE WHEN (a1.used) > a1.data THEN (a1.used) - a1.data
                                              ELSE 0 END ) * 8 AS Indexes_kb
FROM       (SELECT ps.object_id                                                                                         
,                  SUM (CASE WHEN (ps.index_id < 2) THEN row_count
                                                    ELSE 0 END)                                                          AS [rows]
,                  SUM (ps.reserved_page_count)                                                                          AS reserved
,                  SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                                                    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data
,                  SUM (ps.used_page_count)                                                                              AS Used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
)                          AS a1
INNER JOIN sys.all_objects    a2 ON (a1.object_id = a2.object_id)
INNER JOIN sys.schemas        a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a1.data DESC

TEL_SetupInfo

Original Query

-- nolock for scope of procedure
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @LicenseType INT
DECLARE @Version Nvarchar(20)
DECLARE @SysCenterId Nvarchar(MAX)
DECLARE @TenantId Nvarchar(MAX)
DECLARE @TelemetryLevel INT
DECLARE @OfflineMode INT
DECLARE @SiteNumber INT = dbo.fnGetSiteNumber();

select top 1 @LicenseType=value1
from SetupInfo
where id=N'Type'

select top 1 @Version=string1
from SetupInfo
where id=N'VERSION'

select top 1 @SysCenterId=string1
from SetupInfo
where id=N'SYSCENTERID'

select @TelemetryLevel = scp.Value3
FROM       SC_Component          sc 
INNER JOIN SC_Component_Property scp ON scp.ComponentID = sc.ID
WHERE sc.SiteNumber = @SiteNumber
	AND sc.ComponentName = 'SMS_REPLICATION_CONFIGURATION_MONITOR'
	AND scp.Name = 'TelemetryLevel';
select @TenantId = dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX)
,	 [dbo].[fnGetHierarchyID]()), 'SHA256') )

select @OfflineMode = scp.Value3
from       SC_SysResUse          sc 
inner join SC_SysResUse_Property scp on sc.ID = scp.SysResUseID
Where sc.RoleTypeID = 23 and (Name = 'OfflineMode')

select @LicenseType           as LicenseType
,      @Version               as Version
,      @SysCenterId           as SysCenterId
,      @TelemetryLevel        as TelemetryLevel
,      @TenantId              as TenantId
,      isnull(@OfflineMode,0) as OfflineMode

 

Query Including the Unhashed data alongside the hashed data

-- nolock for scope of procedure
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @LicenseType INT
DECLARE @Version Nvarchar(20)
DECLARE @SysCenterId Nvarchar(MAX)
DECLARE @TenantId Nvarchar(MAX)
DECLARE @TelemetryLevel INT
DECLARE @OfflineMode INT
DECLARE @SiteNumber INT = dbo.fnGetSiteNumber();

select top 1 @LicenseType=value1
from SetupInfo
where id=N'Type'

select top 1 @Version=string1
from SetupInfo
where id=N'VERSION'

select top 1 @SysCenterId=string1
from SetupInfo
where id=N'SYSCENTERID'

select @TelemetryLevel = scp.Value3
FROM       SC_Component          sc 
INNER JOIN SC_Component_Property scp ON scp.ComponentID = sc.ID
WHERE sc.SiteNumber = @SiteNumber
	AND sc.ComponentName = 'SMS_REPLICATION_CONFIGURATION_MONITOR'
	AND scp.Name = 'TelemetryLevel';
select @TenantId = dbo.fnConvertBinaryToBase64String(dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX)
,	 [dbo].[fnGetHierarchyID]()), 'SHA256') )

select @OfflineMode = scp.Value3
from       SC_SysResUse          sc 
inner join SC_SysResUse_Property scp on sc.ID = scp.SysResUseID
Where sc.RoleTypeID = 23 and (Name = 'OfflineMode')

select @LicenseType           as LicenseType
,      @Version               as Version
,      @SysCenterId           as SysCenterId
,      @TelemetryLevel        as TelemetryLevel
,      @TenantId              as TenantId
,		[dbo].[fnGetHierarchyID]() AS [TenantId Unhashed]
,      isnull(@OfflineMode,0) as OfflineMode

TEL_SQL_DBSchema

Original Query

SET NOCOUNT ON
/* Clarification for the return values: 
Schema version and whether customer did any customization 
*/
-- Setup Version 
SELECT dbo.fnConvertBinaryToBase64String(
dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), DS.ObjectName), 'SHA256')
) AS ObjectNameHash
,DS.ObjectName AS [ObjectName unhashed],
DS.ObjectVersion AS ObjectVersion,
DS.UpdatedBy AS UpdatedBy,
DS.ObjectHash As ObjectHash 
FROM dbo.DBSchema DS 
INNER JOIN SC_SiteDefinition SS 
ON DS.SiteNumber = SS.SiteNumber 
WHERE ISNULL(SS.parentsitecode, N'') = N''

Query Including the Unhashed data alongside the hashed data

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
/* Clarification for the return values: 
Schema version and whether customer did any customization 
*/
-- Setup Version 
SELECT dbo.fnConvertBinaryToBase64String(
dbo.fnMDMCalculateHash(CONVERT(VARBINARY(MAX), DS.ObjectName), 'SHA256')
) AS ObjectNameHash,
DS.ObjectVersion AS ObjectVersion,
DS.UpdatedBy AS UpdatedBy,
DS.ObjectHash As ObjectHash 
FROM dbo.DBSchema DS 
INNER JOIN SC_SiteDefinition SS 
ON DS.SiteNumber = SS.SiteNumber 
WHERE ISNULL(SS.parentsitecode, N'') = N''

Identifier at the end of each Results entry

Original Query

Not Applicable

Query Including the unhashed data alongside the hashed data

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

Leave a Comment