This document explains how to extract data from Microsoft System Center Configuration Manager and upload it into Licenseware’s Microsoft Deployment Manager (MDM) app.
Microsoft SCCM has a SQL Server DB component that stores all the data collected by SCCM. In order to query the DB, you’ll need to satisfy the following requirements:
- Install SQL Server Management Studio (SSMS)
- Make sure that you have access to the SCCM DB
Once the above requirements are satisfied, you can execute the Licenseware Queries for Microsoft SCCM. In order to do that, you’ll need to follow these simple steps:
- Open SQL Server Management Studio
- Logon using an account that has rights
- On the left, expand Database and select your SCCM Database. The SCCM database is the one named CM_XXX
- Right-click the database and select New Query
- In the Query pane, enter the SQL queries from the sections below (code panes)
- Hit the Execute button or hit the F5 key
- Your results show in the bottom pane
You need to execute each query, one by one. Once a query has been executed you’ll be able to export it as CSV.
- Right-click on the Results tab
- Save Results As…
- Select CSV
In the Results to Grid section, please make sure that the options Include column headers in the result set and Quote strings containing list separators when saving .csv results are ticked in the Results to Grid setting.
When saving the files, please make sure that you use this naming convention:
- “sccm_software_inventory” for the software inventory query;
- “sccm_installed_software” for the installed software query;
- “sccm_hardware_inventory” for the hardware query;
Please note that files not using the above naming convention will be ignored as they will not pass the first data validation step.
Licenseware Queries for Microsoft SCCM
Use the following queries to extract the hardware and software data required for analysis.
SCCM Hardware Inventory
Copy/Paste the following script in SCCM to extract the relevant hardware data.
-- SCCM HARDWARE INVENTORY
-- collects hardware inventory from SCCM
-- output data can be processed with the Licenseware Infrastructure Mapper (IFMP) App
-- https://licenseware.io
WITH hardware_inventory AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY
[device_name]
ORDER BY
[creation_date0]
DESC
) AS r,
*
FROM
(
SELECT
creation_date0,
CONVERT(
varchar,
(SYS.ResourceID)
) AS device_key,
(SYS.Name0) AS [device_name],
(
ISNULL(SYS.Client0, 0)
) AS [has_client],
(
CASE IsNull(OPSYS.CSDVersion0, '') WHEN '' THEN ISNULL(OPSYS.Caption0, '') + ' ' + ISNULL(OPSYS.Version0, '') ELSE ISNULL(OPSYS.Caption0, '') + ' ' + ISNULL(OPSYS.Version0, '') + ' (' + ISNULL(OPSYS.CSDVersion0, '') + ')' END
) AS [os],
(
ISNULL(SED.Manufacturer0, '')
) AS device_manufacturer,
(
ISNULL(SED.Model0, '')
) AS device_model,
(
ISNULL(GSSE.SMBIOSAssetTag0, '')
) AS [asset_tag],
(
CONVERT(
varchar(20),
CASE CONVERT(
VarChar(20),
ISNULL(SED.NumberOfProcessors0, '0')
) WHEN '0' THEN '' ELSE CONVERT(
varchar(20),
SED.NumberOfProcessors0
) END
)
) AS [number_of_processors],
(
CASE ISNULL(GSSE.SerialNumber0, '') WHEN 'Not Available' THEN '' WHEN 'NONE' THEN '' WHEN 'To Be Filled By O.E.M.' THEN '' ELSE ISNULL(GSSE.SerialNumber0, '') END
) AS [serial_number],
(
ISNULL(PROCESSOR.Manufacturer0, '')
) AS [processor_manufacturer],
(
ISNULL(PROCESSOR.Name0, '')
) AS [processor_model],
(
ISNULL(
SYS.Resource_Domain_OR_Workgr0,
''
)
) AS [domain],
(
ISNULL(PROCESSOR.NumberOfCores0, '')
) AS [number_of_cores],
PROCESSOR.IsHyperthreadCapable0 As [ht_capable],
PROCESSOR.IsHyperthreadEnabled0 As [ht_enabled],
(
ISNULL(SED.SystemType0, '')
) AS [platform],
(
CASE ISNULL(SED.Manufacturer0, '0') WHEN 'VMware, Inc.' THEN '1' WHEN 'Microsoft Corporation' THEN '1' ELSE '0' END
) AS [virtual_flag]
FROM
v_R_System SYS
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID
LEFT JOIN v_RA_System_IPAddresses IPA ON SYS.ResourceID = IPA.ResourceID
LEFT JOIN v_RA_System_MACAddresses MAC ON SYS.ResourceID = MAC.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM SED ON SYS.ResourceID = SED.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE GSSE ON SYS.ResourceID = GSSE.ResourceID
LEFT JOIN v_RA_System_SystemOUName SYSOU ON SYS.ResourceID = SYSOU.ResourceID
LEFT JOIN v_GS_PROCESSOR PROCESSOR ON SYS.ResourceID = PROCESSOR.ResourceID
WHERE
(
SYS.Active0 <> 0
AND SYS.Obsolete0 <> 1
)
) AS x
)
SELECT
device_key,
[device_name],
[has_client],
[os],
device_manufacturer,
device_model,
[asset_tag],
[number_of_processors],
[serial_number],
[processor_manufacturer],
[processor_model],
[domain],
[number_of_cores],
[ht_capable],
[ht_enabled],
[platform],
[virtual_flag]
FROM
hardware_inventory
WHERE
r = 1
The output should contain as a minimum the following column headers (in this order):
|
ℹ Info - device_key is a unique identifier for a given device (virtual or physical). |
device_key,
device_name,
os,
device_manufacturer,
device_model,
asset_tag,
number_of_processors,
serial_number,
processor_manufacturer,processor_model,
domain,
number_of_cores,
ht_capable,
ht_enabled,
platform,
virtual_flag
Some columns may be empty but keeping this format is required for the validation template. As a minimum, the following columns should be populated device_key, device_name, os. For best results, the more data the better.
SCCM Software Inventory
Copy/Paste the following script in SCCM to extract the relevant software inventory data.
-- SCCM SOFTWARE INVENTORY
-- collects all software inventory from Add Remove Programs
-- output data can be processed with the Licenseware Microsoft Deployment App
-- https://licenseware.io
SELECT DISTINCT
isnull(Publisher0,'') + '_' + isnull(Replace(DisplayName0,'''',''),'') + '_' + isnull(version0, '') AS software_key,
isnull(Publisher0,'') AS software_publisher,
isnull(Replace(DisplayName0,'''',''),'') AS software_name,
isnull(version0, '') AS version
FROM
v_Add_Remove_Programs
WHERE
Publisher0 like '%microsoft%'
ORDER BY
software_key
The output should contain as a minimum the following column headers (in this order):
|
ℹ Info - software_key is a unique identifier for a given software product or product component. |
software_key,
software_publisher,
software_name,
version
Some columns may be empty but keeping this format is required for the validation template. As a minimum, the following columns should be populated software_key and software_name. For best results, the more data the better.
SCCM Installed Software
Copy/Paste the following script in SCCM to extract a mapping of installed software per device.
-- SCCM INSTALLED SOFTWARE
-- collects all installed software tiles for each device from Add Remove Programs
-- output data can be processed with the Licenseware Microsoft Deployment Manager (MDM) App
-- https://licenseware.io
SELECT DISTINCT
ResourceID AS device_key,
isnull(Publisher0,'') + '_' + isnull(Replace(DisplayName0,'''',''),'') + '_' + isnull(version0, '') AS software_key
FROM
v_Add_Remove_Programs
WHERE
Publisher0 LIKE '%microsoft%'
ORDER BY
software_key,
device_key
The output should contain as a minimum the following column headers (in this order):
|
ℹ Info
- software_key is a unique identifier for a given software product or product component. |
device_key,
software_key
Both columns are mandatory and must contain data.
Using a template
If your data has been exported from a different tool and/or is in an incompatible format, you may create a CVS template.
You’ll need to create 3 CSV files using this naming convention:
- “sccm_software_inventory” for the software inventory query;
- “sccm_installed_software” for the installed software query;
- “sccm_hardware_inventory” for the hardware query;
These CSV files need to contain the following data attributes keeping to the format described in the sections below.
|
ℹ Info - device_key is a unique identifier for a given device (virtual or physical). - software_key is a unique identifier for a given software product or product component. - these keys are required for mapping the installed software on each device. |
Hardware Inventory
The output should contain as a minimum the following column headers (in this order):
device_key,
device_name,
os,
device_manufacturer,
device_model,
asset_tag,
number_of_processors,
serial_number,
processor_manufacturer,processor_model,
domain,
number_of_cores,
ht_capable,
ht_enabled,
platform,
virtual_flag
Some columns may be empty but keeping this format is required for the validation template. As a minimum, the following columns should be populated device_key, device_name, os. For best results, the more data the better.
Software Inventory
The output should contain as a minimum the following column headers (in this order):
software_key,
software_publisher,
software_name,
version
Some columns may be empty but keeping this format is required for the validation template. As a minimum, the following columns should be populated software_key and software_name. For best results, the more data the better.
Installed Software
The output should contain as a minimum the following column headers (in this order):
device_key,
software_key
Both columns are mandatory and must contain data.
Download the queries here: