🏗️ SCCM to Intune Migration Plan – Architect View

 

🏗️ SCCM to Intune Migration Plan – Architect View

---

1️⃣ Assessment & Planning

• Review current SCCM infrastructure and workloads.

• Inventory all devices and identify dependencies.

• Prepare Azure AD, Intune, and licensing readiness.

• Define migration strategy — Co-management, Tenant Attach, or Full Cloud.

---

2️⃣ Pilot & Co-Management

• Enable co-management for a small device group.

• Redirect selective workloads (e.g., compliance, updates).

• Validate MDM enrollment and cloud policy delivery.

• Fine-tune network, proxy, and certificates.

---

3️⃣ Policy & Application Migration

• Repackage SCCM apps using Intune Win32 Packaging Tool.

• Test app deployment in pilot devices.

• Migrate GPOs using Group Policy Analytics.

• Implement security baselines and compliance policies.

---

4️⃣ Security & Encryption

• Move BitLocker key management to Azure AD via Intune.

• Migrate Defender, Firewall, and LAPS policies.

• Validate endpoint protection and compliance reporting.

---

5️⃣ Device Enrollment & Deployment

• Migrate existing SCCM clients to Intune MDM gradually.

• Set up Windows Autopilot for new device provisioning.

• Retire legacy OSD task sequences.

---

6️⃣ Validation & Rollout

• Conduct pilot feedback and validation checks.

• Train support teams and communicate with end-users.

• Roll out migration in phases by site or department.

---

7️⃣ Decommission & Handover

• Decommission SCCM roles (DP, MP, SUP) once workloads are moved.

• Document configuration, runbook, and SOPs.

• Establish monitoring and reporting in Intune.

• Perform final sign-off and project closure.

📊 SCCM SQL Queries for Server Role Inventory and Client Health Analysis

When working with System Center Configuration Manager (SCCM), having detailed visibility into site systems, their roles, and client health is crucial for proactive monitoring and reporting. Below are two powerful SQL queries designed to help administrators:

1. Identify server roles deployed within the SCCM hierarchy.

2. Audit client health, hardware configuration, OS details, and system usage.(SCCM Server

 

  🔍 Query 1: SCCM Server Role Inventory

 This query returns a consolidated view of SCCM site systems and the roles assigned to each server. It helps you answer:

  📘 SQL:

 

SELECT

    srl.ServerName,

    srl.SiteCode,

    vs.SiteName,

    vrs.AD_Site_Name0 as ADSite,

    vs.ReportingSiteCode as Parent,

    vs.Installdir,

    MAX(CASE srl.rolename WHEN 'SMS Site System' THEN 'Yes' ELSE ' ' END) as SiteSys,

    MAX(CASE srl.rolename WHEN 'SMS Component Server' THEN 'Yes' ELSE ' ' END) as CompSer,

    MAX(CASE srl.rolename WHEN 'SMS Site Server' THEN 'Yes' ELSE ' ' END) as SiteSer,

    MAX(CASE srl.rolename WHEN 'SMS Management Point' THEN 'Yes' ELSE ' ' END) as MP,

    MAX(CASE srl.rolename WHEN 'SMS Distribution Point' THEN 'Yes' ELSE ' ' END) as DP,

    MAX(CASE srl.rolename WHEN 'SMS SQL Server' THEN 'Yes' ELSE ' ' END) as 'SQL',

    MAX(CASE srl.rolename WHEN 'SMS Software Update Point' THEN 'Yes' ELSE ' ' END) as SUP,

    MAX(CASE srl.rolename WHEN 'SMS SRS Reporting Point' THEN 'Yes' ELSE ' ' END) as SSRS,

    MAX(CASE srl.RoleName WHEN 'SMS Reporting Point' THEN 'Yes' ELSE ' ' END) as RPT,

    MAX(CASE srl.rolename WHEN 'SMS Fallback Status Point' THEN 'Yes' ELSE ' ' END) as FSP,

    MAX(CASE srl.rolename WHEN 'SMS ServerName Locator Point' THEN 'Yes' ELSE ' ' END) as SLP,

    MAX(CASE srl.rolename WHEN 'SMS PXE Service Point' THEN 'Yes' ELSE ' ' END) as PXE,

    MAX(CASE srl.rolename WHEN 'AI Update Service Point' THEN 'Yes' ELSE ' ' END) as AssI,

    MAX(CASE srl.rolename WHEN 'SMS State Migration Point' THEN 'Yes' ELSE ' ' END) as SMP,

    MAX(CASE srl.rolename WHEN 'SMS System Health Validator' THEN 'Yes' ELSE ' ' END) as SysVal,

    MAX(CASE srl.rolename WHEN 'SMS Notification Server' THEN 'Yes' ELSE ' ' END) as NotiSer,

    MAX(CASE srl.rolename WHEN 'SMS Provider' THEN 'Yes' ELSE ' ' END) as SMSPro,

    MAX(CASE srl.rolename WHEN 'SMS Application Web Service' THEN 'Yes' ELSE ' ' END) as WebSer,

    MAX(CASE srl.rolename WHEN 'SMS Portal Web Site' THEN 'Yes' ELSE ' ' END) as WebSite,

    MAX(CASE srl.rolename WHEN 'SMS Branch distribution point' THEN 'Yes' ELSE ' ' END) as BranDP

FROM v_SystemResourceList as srl

LEFT JOIN v_site vs on srl.ServerName = vs.ServerName

LEFT JOIN v_R_System_Valid vrs on LEFT(srl.ServerName, CHARINDEX('.', srl.ServerName) - 1) = vrs.Netbios_Name0

GROUP BY

    srl.ServerName,

    srl.SiteCode,

    vs.SiteName,

    vs.ReportingSiteCode,

    vrs.AD_Site_Name0,

    vs.InstallDir

ORDER BY srl.sitecode, srl.ServerName;

 

 🧠 Query 2: SCCM Server Client Health & Inventory Report

This advanced query returns a comprehensive inventory of all devices acting as SCCM site systems and includes:

 

 Client health status

 Hardware details (RAM, disk, CPU)

 Operating System info

 Last boot time, OS install age, client age

 Manufacturer and model

 Assigned site code and domain details

 

 📘 SQL:

 

SELECT DISTINCT

    VRS.Netbios_Name0 AS 'Server Name',

    CASE WHEN VRS.Client0 = 1 THEN 'Yes' ELSE 'No' END AS 'Client',

    CASE WHEN VRS.Active0 = 1 THEN 'Yes' ELSE 'No' END AS 'Active',

    CASE

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 THEN 'VMWare'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN ('3','4') THEN 'Desktop'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN ('8','9','10','11','12','14') THEN 'Laptop'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 THEN 'Mini Tower'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 THEN 'Tower'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 THEN 'All in One'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 THEN 'Space-Saving'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 THEN 'Main System Chassis'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 THEN 'Peripheral Chassis'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 THEN 'Storage Chassis'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 THEN 'Rack Mount Chassis'

        WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 THEN 'Sealed-Case PC'

        ELSE 'Others'

    END AS 'CaseType',

    LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0),

        ISNULL(NULLIF(CHARINDEX(',', MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) - 1, -1),

        LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)))) AS 'IPAddress',

    MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) AS 'MACAddress',

    v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS 'AssignedSite',

    VRS.Client_Version0 AS 'ClientVersion',

    VRS.Creation_Date0 AS 'ClientCreationDate',

    DATEDIFF(D, VRS.Creation_Date0, GETDATE()) AS 'ClientCreationDateAge',

    VRS.AD_Site_Name0 AS 'ADSiteName',

    dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS 'OSInstallDate',

    DATEDIFF(D, v_GS_OPERATING_SYSTEM.InstallDate0, GETDATE()) AS 'OSInstallDateAge',

    CONVERT(VARCHAR, v_Gs_Operating_System.LastBootUpTime0, 100) AS 'LastBootDate',

    DATEDIFF(D, CONVERT(VARCHAR, v_Gs_Operating_System.LastBootUpTime0, 100), GETDATE()) AS 'LastBootDateAge',

    PC_BIOS_DATA.SerialNumber00 AS 'SerialNumber',

    v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS 'AssetTag',

    PC_BIOS_DATA.ReleaseDate00 AS 'ReleaseDate',

    PC_BIOS_DATA.Name00 AS 'BiosName',

    PC_BIOS_DATA.SMBIOSBIOSVersion00 AS 'BiosVersion',

    v_GS_PROCESSOR.Name0 AS 'ProcessorName',

    CASE

        WHEN Computer_System_DATA.Manufacturer00 LIKE 'VMware%' THEN 'VMWare'

        WHEN Computer_System_DATA.Manufacturer00 LIKE 'Gigabyte%' THEN 'Gigabyte'

        WHEN Computer_System_DATA.Manufacturer00 LIKE 'VIA Technologies%' THEN 'VIA Technologies'

        WHEN Computer_System_DATA.Manufacturer00 LIKE 'MICRO-STAR%' THEN 'MICRO-STAR'

        ELSE Computer_System_DATA.Manufacturer00

    END AS 'Manufacturer',

    Computer_System_DATA.Model00 AS 'Model',

    Computer_System_DATA.SystemType00 AS 'OSType',

    v_GS_COMPUTER_SYSTEM.Domain0 AS 'DomainName',

    VRS.User_Domain0 + '\' + VRS.User_Name0 AS 'UserName',

    v_R_User.Mail0 AS 'EMailID',

    CASE

        WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 0 THEN 'Standalone Workstation'

WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 1 THEN 'Member Workstation'

WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 2 THEN 'Standalone Server'

WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 3 THEN 'Member Server'

WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 4 THEN 'Backup Domain Controller'

WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 5 THEN 'Primary Domain Controller'

END AS 'Role',

CASE

WHEN Operating_System_DATA.Caption00 LIKE '%2003, Enterprise%' THEN 'Microsoft Windows Server 2003 Enterprise Edition'

WHEN Operating_System_DATA.Caption00 LIKE '%2003, Standard%' THEN 'Microsoft Windows Server 2003 Standard Edition'

WHEN Operating_System_DATA.Caption00 LIKE '%2003, Web%' THEN 'Microsoft Windows Server 2003 Web Edition'

ELSE Operating_System_DATA.Caption00

END AS 'OSName',

Operating_System_DATA.CSDVersion00 AS 'ServicePack',

Operating_System_DATA.Version00 AS 'Version',

((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) AS 'TotalRAMSize(GB)',

MAX(v_GS_LOGICAL_DISK.Size0 / 1024) AS 'TotalHDDSize(GB)',

v_GS_WORKSTATION_STATUS.LastHWScan AS 'LastHWScan',

DATEDIFF(D, v_GS_WORKSTATION_STATUS.LastHwScan, GETDATE()) AS 'LastHWScanAge'

FROM V_R_System VRS

LEFT OUTER JOIN PC_BIOS_DATA ON PC_BIOS_DATA.MachineID = VRS.ResourceId

LEFT OUTER JOIN Operating_System_DATA ON Operating_System_DATA.MachineID = VRS.ResourceId

LEFT OUTER JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId

LEFT OUTER JOIN Computer_System_DATA ON Computer_System_DATA.MachineID = VRS.ResourceId

LEFT OUTER JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_Gs_Operating_System ON v_Gs_Operating_System.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_RA_System_SMSAssignedSites ON v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_GS_NETWORK_ADAPTER_CONFIGUR ON v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId

LEFT OUTER JOIN v_GS_LOGICAL_DISK ON v_GS_LOGICAL_DISK.ResourceID = VRS.ResourceId AND v_GS_LOGICAL_DISK.DriveType0 = 3

LEFT OUTER JOIN v_R_User ON VRS.User_Name0 = v_R_User.User_Name0

WHERE (VRS.Obsolete0 = 0 OR VRS.Obsolete0 IS NULL)

AND VRS.Netbios_Name0 IN (

SELECT LEFT(ServerName, CHARINDEX('.', ServerName) - 1)

FROM v_SystemResourceList

WHERE RoleName = 'SMS Site System'

)

GROUP BY

VRS.Netbios_Name0, VRS.Client0, VRS.Active0, v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0, VRS.Client_Version0, VRS.Creation_Date0,

VRS.AD_Site_Name0, v_Gs_Operating_System.InstallDate0, v_Gs_Operating_System.LastBootUpTime0,

PC_BIOS_DATA.SerialNumber00, v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0, PC_BIOS_DATA.ReleaseDate00,

PC_BIOS_DATA.Name00, PC_BIOS_DATA.SMBIOSBIOSVersion00, v_GS_PROCESSOR.Name0,

Computer_System_DATA.Manufacturer00, Computer_System_DATA.Model00, Computer_System_DATA.SystemType00,

v_GS_COMPUTER_SYSTEM.Domain0, VRS.User_Domain0, VRS.User_Name0, v_R_User.Mail0,

v_GS_COMPUTER_SYSTEM.domainrole0, Operating_System_DATA.Caption00, Operating_System_DATA.CSDVersion00,

Operating_System_DATA.Version00, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_WORKSTATION_STATUS.LastHWScan

ORDER BY VRS.Netbios_Name0;

 

 💡 Final Thoughts

These queries are extremely useful for:

- Daily SCCM health checks

- Capacity planning

- Hardware lifecycle management

- Compliance and audit readiness

 

Monitor Application Details by Region with SCCM

 In enterprise environments, tracking software installations across different regions is essential for compliance, support, and inventory management. This blog post provides an SCCM SQL query to help identify devices with a specific version of Google Chrome installed, categorized by geographic region, user, and install date.

📄 The SQL Query

SELECT 

    SYS.Name0 AS [Device Name], 

    SYS.User_Name0 AS [Last Logged On User], 

    ARP.InstallDate0 AS [Install Date], 

    CASE CS.CurrentTimeZone0 

        WHEN -300 THEN 'USA (Central Time)' 

        WHEN -240 THEN 'Puerto Rico / Atlantic Time' 

        WHEN 60 THEN 'UK / Western Europe' 

        WHEN 120 THEN 'Central Europe / South Africa' 

        WHEN 180 THEN 'Eastern Europe / Russia' 

        WHEN 480 THEN 'India / APAC' 

        ELSE 'Unknown Region' 

    END AS [Country/Region], 

    ARP.DisplayName0 AS [Application Name], 

    ARP.Version0 AS [Version] 

FROM 

    v_R_System AS SYS 

    INNER JOIN v_Add_Remove_Programs AS ARP 

        ON SYS.ResourceID = ARP.ResourceID 

    LEFT JOIN v_GS_Computer_System AS CS 

        ON SYS.ResourceID = CS.ResourceID 

WHERE 

    ARP.DisplayName0 LIKE '%Chrome%' 

    AND ARP.Version0 = '5.9.124'

ORDER BY 

    [Country/Region], SYS.Name0;

 

Time Zone to Region Mapping

 

| CurrentTimeZone0 | Region Description            |

| ------------------ | ----------------------------- |

| -300               | USA (Central Time)            |

| -240               | Puerto Rico / Atlantic Time   |

| 60                 | UK / Western Europe           |

| 120                | Central Europe / South Africa |

| 180                | Eastern Europe / Russia       |

| 480                | India / APAC                  |

| Other values       | Unknown Region                |

 

🔚Final Thoughts

Tracking Chrome installations by version and region helps ensure that your environment is secure, compliant, and up to date. This query is a straightforward way to get that insight leveraging SCCM data.

Single-Row SQL Output for Multiple Software Installations

 💡 What This Query Does

 

 Retrieves all machines in a specific collection (CollectionID = ''`).

 Checks if Google Chrome or Microsoft Edge is installed.

 Displays the software version (or shows "Not Installed" if missing).

 Uses temporary tables to keep things clean and organized.

 🧾 SQL Query Breakdown

-- STEP 1: Chrome installations

SELECT DISTINCT

    sys1.ResourceID,

    sys1.Netbios_Name0 AS [Computer Name],

    arp.DisplayName0 AS [Software Name],

    arp.Version0 AS [Version]

INTO #TEMP_ENT8

FROM v_R_System AS sys1

INNER JOIN v_Add_Remove_Programs AS arp

    ON sys1.ResourceID = arp.ResourceID

WHERE arp.DisplayName0 LIKE 'Google Chrome';

 

-- STEP 2: Edge installations

SELECT DISTINCT

    sys1.ResourceID,

    sys1.Netbios_Name0 AS [Computer Name],

    arp.DisplayName0 AS [Software Name],

    arp.Version0 AS [Version]

INTO #TEMP_ENT9

FROM v_R_System AS sys1

INNER JOIN v_Add_Remove_Programs AS arp

    ON sys1.ResourceID = arp.ResourceID

WHERE arp.DisplayName0 LIKE 'Microsoft Edge';

  -- STEP 3: Final result with 'Not Installed' instead of NULL

SELECT DISTINCT

    COALESCE(ent8.[Computer Name], ent9.[Computer Name]) AS [Computer Name],

     ISNULL(ent8.[Software Name], 'Not Installed') AS [Google Chrome],

    ISNULL(ent8.Version, 'Not Installed') AS [Google Chrome Version],

     ISNULL(ent9.[Software Name], 'Not Installed') AS [Microsoft Edge],

    ISNULL(ent9.Version, 'Not Installed') AS [Microsoft Edge Version]

 FROM v_FullCollectionMembership AS fc

LEFT JOIN #TEMP_ENT8 AS ent8 ON ent8.ResourceID = fc.ResourceID

LEFT JOIN #TEMP_ENT9 AS ent9 ON ent9.ResourceID = fc.ResourceID

WHERE fc.CollectionID = 'CM400609'

  AND (ent8.ResourceID IS NOT NULL OR ent9.ResourceID IS NOT NULL);

 

-- STEP 4: Clean up temporary tables

DROP TABLE #TEMP_ENT8;

DROP TABLE #TEMP_ENT9;

✅ Sample Output

 

| Computer Name | Google Chrome | Chrome Version | Microsoft Edge | Edge Version  |

| ------------- | ------------- | -------------- | -------------- | ------------- |

| PC001         | Installed     | 117.0.5938.92  | Not Installed  | Not Installed |

| PC002         | Not Installed | Not Installed  | Installed      | 118.0.2088.69 |

 

 🧠 Final Notes

 

This script is a simple way to:

 

 Audit browser installations.

 Ensure compliance with your company’s software standards.

 Report on missing or outdated browsers.

 

You can modify the `CollectionID` or extend the query for other software like Firefox, Zoom, or antivirus tools.