I will be talking about one of my favorite parts when it comes to configuring Configuration Manager 2012/R2 infrastructure. By now, you have the main SCCM roles configured including the reporting point, and you start collecting hardware inventory data from your machines.
The part that is so interesting is to take this raw data that exist in your Configuration Manager database, and transform it into a valuable information that you can look at, and have a good understanding of your network resources.
I was trying to generate a SQL report that gives me machines in my network with almost full hardware inventory in a very well formatted way. If you are using SCCM queries (WQL) to generate report, I may argue that queries are good for SCCM operations and collection membership. They are not the preferred way for reporting.
SQL reports using Reporting Point is the preferred way to generate reports in Configuration Manager using native SQL queries. To do that, you should have some knowledge of the SCCM database schema and start browsing using ResourceID as the identifier of resources inside the SCCM SQL database.
Challenges
One of the challenging parts is to understand the SQL database schema, and to find out where the data resides. There are many tables out there, and you will be surprised that using the built in SQL viewes might help in your SQL JOIN statements. You can always open the definition of existing built-in SQL reports and learn the most used tables, and get a good insight about the database structure.
The second challenge and the most difficult challenge is the duplication of records. a certain machine can appear multiple times in the database, each time with different meaning.
Say for example a machine has 4 GB physical memory, installed in four different hardware memory slots. If you do a JOIN into the memory table, you will get four records for that machine, each time with a different memory slot number.
You may also find two records for the same machine, once when it was running Windows 7, and one when it was upgraded to Windows 8, and so on.
You may also find the same machine name with two different ResourceID values. This happens if for example you format a machine with the same name, in this case a new ResourceID will be recorded for the same machine name !!
The duplicate records are the most difficult problem in SCCM queries. I handle it by inspecting every duplicate records and take the last seen online time and pickup the newest one. This way, i can eliminate any duplicate that could happen. More over, when the same resource ID appears with different memory slots, i aggregate this info and sum up the total memory in one slot.
Finally, i recently added a variable in the SQL query called (@BackInTime) that you can use to filter the results according to the machines who recently reported information. The value is set to 30 days initially, and this means that we will only get results from machines that reported data in the last 30 days.
Solution
To write a SQL query that solves all the previous challenges, I had to start with the v_R_System SYS view, and then eliminate duplication by using the MAX(Creation_Date0) as my criteria. From there, I started to JOIN other tables, and each time the JOIN is performed, I am eliminating duplication by a way or another. The GROUP BY statement has most of the logic that solves the duplication criteria
Report Data
- Machine Name : without any duplicates in the way.
- Active Directory Site
- User Name
- Top User : the most user appearing to log to this machine
- Windows Version: the last Windows version installed, removing any duplicates in the way/
- Windows Service Pack
- Machine Manufacturer
- Machine Model
- Serial Number
- BIOS Date
- BIOS Version
- Managed Date: the date in which SCCM started to manage this machine.
- Physical Memory: Aggregate memory installed in all memory slots, removing any duplicates in the way.
- Number of memory slots.
- Type of the machine : X64 or X86
- Logical Disk Size in GB
- CPU Information [New]:
- CPU Type
- Number of sockets
- Number of cores
- Number of logical processors
- Windows Update Scan Information [New]
- Last Windows Update scan.
- Last Windows Update scan error.
- Last location for Windows Update scan.
- PC Type: enumeration of all values of ChassisTypes0. This can be one of the following:
- 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 ’10’ 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 ’20’ 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 ‘Undefinded’
DECLARE @Today AS DATE
SET @Today = GETDATE()
DECLARE @BackInTime AS DATE
SET @BackInTime = DATEADD(DAY, -30, @Today )
SELECT DISTINCT
SYS.ResourceID,
SYS.Name0 'Name',
SYS.AD_Site_Name0 'ADSite',
CS.UserName0 'User Name',
CASE
WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
ELSE U.TopConsoleUser0
END AS TopUser,
REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS,
REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack',
CS.Manufacturer0 'Manufacturer',
CS.Model0 Model,
BIOS.SerialNumber0 'Serial Number',
CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate,
BIOS.SMBIOSBIOSVersion0 AS BIOSVersion,
(SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date',
SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)',
COUNT(RAM.ResourceID) '# Memory Slots',
REPLACE (cs.SystemType0,'-based PC','') 'Type',
SUM(D.Size0) / 1024 AS 'Disk Size GB',
CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time',
CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date',
CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory',
CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online',
SYS.Client_Version0 as 'SCCM Agent Version',
CPU.Manufacturer AS 'CPU Man.',
CPU.[Number of CPUs] AS '# of CPUs',
CPU.[Number of Cores per CPU] AS '# of Cores per CPU',
CPU.[Logical CPU Count] AS 'Logical CPU Count',
US.ScanTime AS ' Windows Updates Scan Time' ,
US.LastErrorCode AS ' Windows Updates Last Error Code' ,
US.LastScanPackageLocation AS ' Windows Updates Last Package Location' ,
CASE SE.ChassisTypes0
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 '10' 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 '20' 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 'Undefinded'
END AS 'PC Type'
FROM
v_R_System SYS
INNER JOIN (
SELECT
Name0,
MAX(Creation_Date0) AS Creation_Date
FROM
dbo.v_R_System
GROUP BY
Name0
) AS CleanSystem
ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
LEFT JOIN v_GS_COMPUTER_SYSTEM CS
ON SYS.ResourceID=cs.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS
ON SYS.ResourceID=bios.ResourceID
LEFT JOIN (
SELECT
A.ResourceID,
MAX(A.[InstallDate0]) AS [InstallDate0]
FROM
v_GS_OPERATING_SYSTEM A
GROUP BY
A.ResourceID
) AS X
ON SYS.ResourceID = X.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM OS
ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
ON SYS.ResourceID=ram.ResourceID
LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
ON SYS.ResourceID = U.ResourceID
LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
LEFT JOIN v_CH_ClientSummary CH
ON SYS.ResourceID = CH.ResourceID
LEFT JOIN (
SELECT
DISTINCT(CPU.SystemName0) AS [System Name],
CPU.Manufacturer0 AS Manufacturer,
CPU.ResourceID,
CPU.Name0 AS Name,
COUNT(CPU.ResourceID) AS [Number of CPUs],
CPU.NumberOfCores0 AS [Number of Cores per CPU],
CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
FROM [dbo].[v_GS_PROCESSOR] CPU
GROUP BY
CPU.SystemName0,
CPU.Manufacturer0,
CPU.Name0,
CPU.NumberOfCores0,
CPU.NumberOfLogicalProcessors0,
CPU.ResourceID
) CPU
ON CPU.ResourceID = SYS.ResourceID
LEFT JOIN v_UpdateScanStatus US
ON US.ResourceID = SYS.ResourceID
WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
CH.LastOnline BETWEEN @BackInTime AND GETDATE()
GROUP BY
SYS.Creation_Date0 ,
SYS.Name0 ,
SYS.ResourceID ,
SYS.AD_Site_Name0 ,
CS.UserName0 ,
REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'),
REPLACE (OS.CSDVersion0,'Service Pack','SP'),
CS.Manufacturer0 ,
CS.Model0 ,
BIOS.SerialNumber0 ,
REPLACE (cs.SystemType0,'-based PC','') ,
CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
CONVERT(VARCHAR(26), WS.LastHWScan, 101),
CASE
WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
ELSE U.TopConsoleUser0
END,
CPU.Manufacturer,
CPU.[Number of CPUs] ,
CPU.[Number of Cores per CPU],
CPU.[Logical CPU Count],
US.ScanTime ,
US.LastErrorCode ,
US.LastScanPackageLocation ,
CASE SE.ChassisTypes0
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 '10' 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 '20' 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 'Undefinded'
END ,
CONVERT (DATE,BIOS.ReleaseDate0) ,
BIOS.SMBIOSBIOSVersion0 ,
SYS.Client_Version0 ,
CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
ORDER BY SYS.Name0
You may also find two records for the same machine, once when it was running Windows 7, and one when it was upgraded to Windows 8, and so on.
You may also find the same machine name with two different ResourceID values. This happens if for example you format a machine with the same name, in this case a new ResourceID will be recorded for the same machine name !!
The duplicate records are the most difficult problem in SCCM queries. I handle it by inspecting every duplicate records and take the last seen online time and pickup the newest one. This way, i can eliminate any duplicate that could happen. More over, when the same resource ID appears with different memory slots, i aggregate this info and sum up the total memory in one slot.
Finally, i recently added a variable in the SQL query called (@BackInTime) that you can use to filter the results according to the machines who recently reported information. The value is set to 30 days initially, and this means that we will only get results from machines that reported data in the last 30 days.
No comments:
Post a Comment