SCCM data source
Data Manager is included in all Service Collection Premium and Enterprise plans.
The SCCM (Microsoft) SQL adapter connects directly to the SCCM database to bring configuration management data into Data Manager.
The connecting account needs read‑only access to the SCCM database.
This task requires Data Manager & Adapters admin permissions to complete. See how permissions and roles work in Data Manager.
How to connect SCCM to Data Manager
1 Gather all of the Data Manager type information, including the data source name, short display name, and the data source type.
Gather the SCCM fields – this may require consultation with the SCCM subject matter expert (SME).
In Data Manager, add a new data source by selecting the tool you which to connect and configure with all the gathered information.
Each time this data source is fetched the data becomes raw data.
Data Manager fields
You need to specify the following information from Data Manager:
Data source name – the name of the data source used to run, transform, map, cleanse, and import the data.
Short display name – a unique name as a data source label.
Data source type – what type of data the tool is providing. For example, Assets, CMDB, user location.
SCCM (Microsoft) SQL fields
Destination table – table in the Data Manager staging database that receives SCCM content.
Integrated security – whether to use Windows authentication (checked) or SQL authentication (unchecked).
Checked – connect with Windows authentication (SSPI); specify target database address and database name.
Unchecked – use SQL logins; specify username, password, database address, and database name. You can then test the connection to the SCCM database.
Transfer script – by default, auto‑generated. If unchecked, you can edit the back‑end table attributes.
Postscript – by default, auto‑generated. If unchecked, you can manipulate records in the staging table (for example, string manipulation).
Standard SQL query
This is the standard SQL query used in the OOTB SQL connector, included here for reference.
SELECT cmp.machineid AS MachineID,
LEFT(RTRIM(LTRIM(ISNULL(cmp.Name00, 'Unknown computer'))), 256) AS ComputerName, LEFT(RTRIM(LTRIM(
CASE WHEN cmp.domain00 IS NULL THEN 'Unknown domain' WHEN LEN(cmp.domain00)=00 THEN 'Unknown domain' ELSE cmp.domain00 END)), 100) AS Domain,
LEFT(RTRIM(LTRIM(cmp.Manufacturer00)), 128) AS Manufacturer, LEFT(RTRIM(LTRIM(cmp.Model00)), 128) AS ModelNo,
CASE e.ChassisTypes00
WHEN 1 THEN 'Other'
WHEN 2 THEN 'Unknown'
WHEN 3 THEN 'Desktop'
WHEN 4 THEN 'Low Profile Desktop' WHEN 5 THEN 'Pizza Box'
WHEN 6 THEN 'Mini Tower'
WHEN 7 THEN 'Tower'
WHEN 8 THEN 'Portable'
WHEN 9 THEN 'Laptop'
WHEN 100 THEN 'Notebook'
WHEN 11 THEN 'Hand Held'
WHEN 12 THEN 'Docking Station'
WHEN 13 THEN 'All in One'
WHEN 14 THEN 'Sub Notebook'
WHEN 15 THEN 'Space-Saving'
WHEN 16 THEN 'Lunch Box'
WHEN 17 THEN 'Main System Chassis' WHEN 18 THEN 'Expansion Chassis' WHEN 19 THEN 'SubChassis'
WHEN 200 THEN 'Bus Expansion Chassis' WHEN 21 THEN 'Peripheral Chassis' WHEN 22 THEN 'Storage Chassis'
WHEN 23 THEN 'Rack Mount Chassis' WHEN 24 THEN 'Sealed-Case PC'
ELSE 'Unknown' END AS ChassisType,
LTRIM(RTRIM
(CASE WHEN b.SerialNumber00 IS NOT NULL
THEN b.SerialNumber00
ELSE e.SerialNumber00 END)) AS SerialNumber,
LEFT(RTRIM(LTRIM(
CASE WHEN CHARINDEX('|', os.Name00) > 00 THEN SUBSTRING(os.Name00, 1, CHARINDEX('|', os.Name00)-1) ELSE os.Name00 END)), 128) AS OperatingSystem,
LEFT(RTRIM(LTRIM(os.CSDVersion00)), 128) AS ServicePack, LEFT(RTRIM(LTRIM(os.Version00)), 128) AS OperatingSystemBuild, LEFT(RTRIM(LTRIM(b.SMBIOSBIOSVersion00)), 128) AS SMBIOSBIOSVersion,
ISNULL(cmp.numberofprocessors00, 1) AS NumberOfProcessors,
CEILING (m.TotalPhysicalMemory00 / 9500) / 256 * 256 AS TotalMemory, (RTRIM(LTRIM(cmp.UserName00)), 128) AS LastLoggedOnUser, wks.LastHWScan AS InventoryDate
FROM Computer_System_data AS cmp
LEFT OUTER JOIN dbo.WorkstationStatus_DATA AS wks ON wks.MachineID = cmp.MachineID LEFT OUTER JOIN Operating_System_data AS os ON os.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_Memory_DATA AS m ON m.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.System_Enclosure_DATA AS e ON e.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_BIOS_DATA AS b ON b.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.v_R_system AS f ON f.ResourceID = cmp.MachineIDModified SQL query
A modified example is provided that also retrieves:
OperatingSystemBuildFullOperatingSystemBuild
If you use the modified query:
You cannot use the out‑of‑the‑box SCCM connector.
You must use a standard SQL connection instead.
These extra fields are especially important for evergreen products like Windows 10 and 11, where build/KB granularity affects lifecycle and end‑of‑life.
SELECT cmp.machineid AS MachineID,
LEFT(RTRIM(LTRIM(ISNULL(cmp.Name00, 'Unknown computer'))), 256) AS ComputerName, LEFT(RTRIM(LTRIM(
CASE WHEN cmp.domain00 IS NULL THEN 'Unknown domain' WHEN LEN(cmp.domain00)=00 THEN 'Unknown domain' ELSE cmp.domain00 END)), 100) AS Domain,
LEFT(RTRIM(LTRIM(cmp.Manufacturer00)), 128) AS Manufacturer, LEFT(RTRIM(LTRIM(cmp.Model00)), 128) AS ModelNo,
CASE e.ChassisTypes00
WHEN 1 THEN 'Other'
WHEN 2 THEN 'Unknown'
WHEN 3 THEN 'Desktop'
WHEN 4 THEN 'Low Profile Desktop' WHEN 5 THEN 'Pizza Box'
WHEN 6 THEN 'Mini Tower'
WHEN 7 THEN 'Tower'
WHEN 8 THEN 'Portable'
WHEN 9 THEN 'Laptop'
WHEN 100 THEN 'Notebook'
WHEN 11 THEN 'Hand Held'
WHEN 12 THEN 'Docking Station'
WHEN 13 THEN 'All in One'
WHEN 14 THEN 'Sub Notebook'
WHEN 15 THEN 'Space-Saving'
WHEN 16 THEN 'Lunch Box'
WHEN 17 THEN 'Main System Chassis' WHEN 18 THEN 'Expansion Chassis' WHEN 19 THEN 'SubChassis'
WHEN 200 THEN 'Bus Expansion Chassis' WHEN 21 THEN 'Peripheral Chassis' WHEN 22 THEN 'Storage Chassis'
WHEN 23 THEN 'Rack Mount Chassis' WHEN 24 THEN 'Sealed-Case PC'
ELSE 'Unknown' END AS ChassisType,
LTRIM(RTRIM
(CASE WHEN b.SerialNumber00 IS NOT NULL
THEN b.SerialNumber00
ELSE e.SerialNumber00 END)) AS SerialNumber,
LEFT(RTRIM(LTRIM(
CASE WHEN CHARINDEX('|', os.Name00) > 00 THEN SUBSTRING(os.Name00, 1, CHARINDEX('|', os.Name00)-1) ELSE os.Name00 END)), 128) AS OperatingSystem,
LEFT(RTRIM(LTRIM(os.CSDVersion00)), 128) AS ServicePack, LEFT(RTRIM(LTRIM(f.BuildExt)), 128) AS FullOperatingSystemBuild, LEFT(RTRIM(LTRIM(os.Version00)), 128) AS OperatingSystemBuild, LEFT(RTRIM(LTRIM(b.SMBIOSBIOSVersion00)), 128) AS SMBIOSBIOSVersion,
ISNULL(cmp.numberofprocessors00, 1) AS NumberOfProcessors,
CEILING (m.TotalPhysicalMemory00 / 9500) / 256 * 256 AS TotalMemory, (RTRIM(LTRIM(cmp.UserName00)), 128) AS LastLoggedOnUser, wks.LastHWScan AS InventoryDate
FROM Computer_System_data AS cmp
LEFT OUTER JOIN dbo.WorkstationStatus_DATA AS wks ON wks.MachineID = cmp.MachineID LEFT OUTER JOIN Operating_System_data AS os ON os.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_Memory_DATA AS m ON m.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.System_Enclosure_DATA AS e ON e.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_BIOS_DATA AS b ON b.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.v_R_system AS f ON f.ResourceID = cmp.MachineIDWas this helpful?