Configuration manager report
This blog post talks about how to create a detailed configuration manager report, that can be considered a unique report in the internet. The reason why this report is unique, is the way it aggregate information, and help prevent duplicate records and filter orphan devices. To do that, I had to dig deep and understand the schema of configuration manager SQL database and the relationship between tables. After that, I would figure out the main records to query, and what columns to use for my filtering and aggregations. Sounds geeky I know!
If you are a beginner in configuration manager, you might be using the SCCM queries (WQL), which is based on WMI name spaces. I can argue that the best way to get rich queries and information from configuration manager, is by using the SQL report engine and native SQL queries. Usually the ResourceID is the main identifier of resources hosted inside the configuration manager SQL database.
Tip: I created a blog post to list all Windows update package size, so that you can have an idea of your patching software update group before downloading them and perhaps congest your network. Check my configuration manager update size post for more details.
Why this can be hard and challenging?
While most of you want to jump directory and try the report, I want to spend a little time talking about the challenges I faced. You can skip this section if you want to try the report right away in your environment and enjoy the wonderful results I hope.
I hope by explaining how I thought about this report, it would make it easier for you to go and tweak the report query to fit your needs, or perhaps to add more information that you might need in your environment.
The first and biggest challenge is to understand the configuration manager SQL database schema, and to find our where the needed data resides. There are many tables out there, but the built-in SQL views inside the configuration manager database helped me a lot in my JOIN statements.
I hate when the report comes back with duplicate records. This is when you have two records for the same device or machine appearing in your report. Each record for the same machine represents a certain meaning or goal.
Say for example that a machine has 4 GB of physical memory installed in four different memory slots, that is 1 GB physical item per slot. Now, if you do a JOIN into the memory table to get the memory information for a device, you will get four records for that machine, with each record representing a slot number. I do not believe you want four records for the same device, just because it contains four memory slots with 1 GB physical memory installed in each.
Another good example for duplicate records is duplicates due to Windows versions. If you have a device with Windows 7 operating system, and then you upgraded that device to Windows 10, then there is a good chance you will see two records for the same device, one with Windows 7 in the O.S column, and one with Windows 10. Each record will have different ResourceID value because you formatted the device before installing a new Windows 10 on it.
It took me long time to investigate duplicate records, and how to sort out each duplicate occurrence. One of the techniques I used is looking at every duplicate record for the same ResourceID and take the one that last one seen online based on the last seen online time value.
In case of duplicates due to memory slots, like the one shown in the previous table, I would aggregate those records belonging to the same ResourceID into one record and do a simple addition of the physical memory by adding the memory value in each physical slot. So, in the previous table, I would show one device with 4 GB total RAM in the Physical Memory column.
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.
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.
What information the report will provide?
There is no limit on the information that can be pulled here. As long as the information exists in the configuration manager database, it can be pulled easily. I made a choice to collect the following items that can become handy for every configuration manager administrator. You can easily extend the items to be pulled by doing a simple JOINT statement to the SQL table that contains that information, and add them to the SELECT and GROUP BY lines.
- 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’
Have a look at the SQL query powering this configuration manager report?
Well, by now you can do directly to download the SQL query powering this configuration manager report and enjoy the results. I want though to write down here the SQL query I used for reference. You can go to your configuration server, open SQL management studio console, connect to the SQL server for your configuration manager, and copy and paste the below query and hit execute. Just make sure you are querying the CM database.
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
Well, here is how it looks like when you run it from the SQL management studio console. Of course the picture does not show everything, as you can scroll to the right and see a lot of device properties as described previously.
The best way to consume this report of course is by building a SQL report based on this query using SQL reporting services and configuration manager reporting point role. The result will look beautiful like shown below:
Get the SQL report now
You can copy and paste the query that was mention in the configuration manager report blog post, or you can download the SQL query file directly from Microsoft TechNet Gallery here. I posted it there so that you can see how many other people are using this configuration manager report and feel the impact. I am getting a lot of good feedback after posting this report, and this makes my day.
Another bonus for you is another similar SQL query for configuration manager, that looks exactly as this configuration manager report. The only difference is that this report will show you detailed memory slot information for each machine in case you want to have such detailed memory view. For each machine, you will get a record for each memory slot with a memory chip installed on it. If you have a machine with 2 memory slots, each with 1 GB physical memory, you will get two records for that machine, one with [Slot_Number = 1, RAM = 1 GB] and one with [Slot_Number = 2, RAM = 1 GB]. You can download this report from here.
Filter by Collection ID
New Update May 2018
As requested by a lot of people, I am adding another inner Join to dbo.v_FullCollectionMembership table to filter by Collection ID. The new query looks like this. Please replace CM100017 with your own collection ID.
DECLARE @Today AS DATE DECLARE @CollectionID nvarchar(8) SET @Today = GETDATE() DECLARE @BackInTime AS DATE SET @BackInTime = DATEADD(DAY, -30, @Today ) SET @CollectionID = 'CM100017' SELECT DISTINCT dfc.CollectionID, 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 inner join dbo.v_FullCollectionMembership dfc on dfc.ResourceID = sys.ResourceID WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND dfc.CollectionID = @CollectionID and CH.LastOnline BETWEEN @BackInTime AND GETDATE() GROUP BY dfc.CollectionID, 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
Send a feedback
If you like this configuration manager report, please share with me what do you think about it, and perhaps share it. I will do my best to read all your feedback and answer any questions you might have. You can also refer to Microsoft documentation about reporting in configuration manager, and get information about list of built-in reports in configuration manager here.
this truly is the most awesome sccm hardware inventory report i have seen. next i will be working on creating a sql report with the sql report builder that u also have another blog on…….great stuff indeed.
i was curious to know if it is possible to tweak the query with the below:
show only server os
add column to indicate if the server is sql, exchange, sharepoint, terminal server, etc, etc
This so specialized functionality, kind of what SCOM do when it do agent discovery to detect roles. This script is just trying to get the information that is already stored in the CM database and aggregate it in away that no duplicates appear. Thanks for the suggestion though!
Hi Ammar, Do you have a copy of this report that can be limited by collection?
I have updated the blog post to include Collection ID filter. hope you will like it
Thank you! Excellent job. Interestingly, we have a tablet computer here that returns ChassisType 30 – undefined in your query.
That ChassisType is nowhere to be found in any Microsoft documentation… 😉
Thanks for your feedback really.
Thanks! 🙂
Gr8 Work
Thanks!
This is really something amazing, it almost fits my needs, but I’m not a DBA (or have any SQL skills) I’m struggling to edit this report to get information from the hardware inventory to bring also some installed applications.
My Idea is kinda of use this report to check my baseline, for instance I wish to see office version, antivirus solution version and so on.
Hi, this looks like an awesome hardware report for SCCM.
You may want to add 3 new Chassis types to your report (30, 31, & 32). Ive been tearing my hair out recently trying to find out why some of our laptop like devices from the work fleet havent been reporting when doing reports by Chassis number, and Ive since found out its because with the SMBIOS 3.0 changes, theres 3 new chassis types added…
30 – Tablet
31 – Convertible
32 – Detachable
(Source: https://thinkdeploy.blogspot.com/2017/04/new-enclosure-types-for-convertible.html)
What a feedback, let me look at this and see ! Thanks for sharing !
Another point I would probably make, is that Lenovo stores like their computer model string seperately than the other manufacturers.
While HP and Dell store their model display name under v_GS_COMPUTER_SYSTEM.Model0, Lenovo store the model display name under v_GS_COMPUTER_SYSTEM_PRODUCT. Version0 and store a Machine Type build number in the usual CS.Model value.
Where on a HP CS.Model0 might be ‘HP Compaq Elite 8300 All-in-One PC’, a Lenovo might display ’10F4S05100′
That ’10F4S05100′ is useful when you are trying to determine driver packs for the 2 or 3 different builds of Lenovo X1 Carbon’s, but doesnt seem to look as pretty to people who then need to manage that data.
On a Lenovo v_GS_COMPUTER_SYSTEM.Model0 might be ’10F4S05100′ while v_GS_COMPUTER_SYSTEM_PRODUCT. Version0 = ‘ThinkCentre M900z’.
Using a copy of your table Im playing tinkering with, I have added an extra column for CSP.Version0, as for now, I think I want both versions shown, but I see else where some people are merging the two values using CASE or IIF.
Overall though so far, I like this report, it looks good!
There are several new Chassis types.
My version of this query now has the following additions
WHEN ’25’ THEN ‘Space-Saving’
WHEN ’26’ THEN ‘Compact’
WHEN ’27’ THEN ‘Advanced’
WHEN ’28’ THEN ‘Blade’
WHEN ’29’ THEN ‘Blade Enclosure’
WHEN ’30’ THEN ‘Tablet’
WHEN ’31’ THEN ‘Convertible’
WHEN ’32’ THEN ‘Detachable’
WHEN ’33’ THEN ‘IoT Gateway’
WHEN ’34’ THEN ‘Embedded PC’
When ’35’ THEN ‘Mini PC’
When ’36’ THEN ‘Stick PC’
THAT IS AWESOME DAVE !!
Hi Ammar
That is excellent job.
I am new to sql queries.
when I run the queries on Management studio I get the following error
Msg 208, Level 16, State 1, Line 7
Invalid object name ‘v_R_System’.
Make sure in the SQL management Studio that you are selecting the configuration manager database please. Hope this will work for you!
I’ve been using this for some time now, so first thanks a lot for sharing it!
with Windows 10 it become important to see which version / build it is, is there any way to add this in the report?
thanks a lot.
Thanks for your feedback. I will look at this next week!
Hi Ammar,
Nice work and thank you for sharing !!
I used it as a basis for my SCCM report. I removed few stuffs and added Build information and few customization. In case you are interested I shared my report on Technet (few minutes ago 🙂 ): https://gallery.technet.microsoft.com/SCCM-Report-device-66219cc7
Thanks again
Cheers
Gregory B
Hi Again Amar,
For your information, since SCCM 1710, the view “v_GS_LOGICAL_DISK” is now in GB according to documentation (https://docs.microsoft.com/en-us/sccm/core/plan-design/changes/whats-new-in-version-1710#cloud-distribution-point-support-for-azure-government-cloud——sms491428—).
“As devices now include hard drives with sizes in the gigabyte (GB), terabyte (TB) and larger scales, this release changes the default unit (SMS_Units) used in many views from megabytes (MB) to GB. For example, the v_gs_LogicalDisk.FreeSpace value now reports GB units.”
Meaning line 30 “SUM(D.Size0) / 1024 AS ‘Disk Size GB’,” you should remove “/1024” for those who are using latest versions of SCCM.
Cheers
Gregory B
Will do . THANKS !
Hi again,
Can you please confirm your value for memory and disk size are true? I can see some value that are not good (i.e.: memory 4096GB, 2*slot memory, result in the query momory size = 8192). Same for the Disk size.
Cheers
Gregory B
The question is , is that data in the SQL database correct or not. Remember, the script only reads data from SCCM database, so try to see if the data there is correct first.
I can confirm that some (actually quite many) values for RAM size, RAM slots and Disk size are reported incorrect by this query. Some are 2 times the real value (real value = stored in the database), some are 4 times, and some are as much as 20.
I’m yet to see a pattern for those numbers, but I’m now investigating it.
Hi Martynas. Did you ever figure out why the RAM sizes were incorrect? I’m getting similar results and can’t work it out. Adding a \2 to the sum(isnull(ram.capacity0,0)) ‘Memory (MB)’ was not really a valid fix!
Also curious if anyone ever figured this out, I also get some machines reported 2x their total and free disk space.
Great work! Any chance you can add CPU type? I need i3, i5 and i7s reported
Will try to look at this when I back from my travel.
Hi,
Firstly, thanks for a fantastic query!
I am seeing some inconsistencies between the original query (TechNet gallery) and the new one May 2018.
The original one brings back all object in the SCCM DB, whereas the new one seems to be missing a few hundred machines which is strange.
Any ideas why this would be?
hmmmm, let me look at this.
Hi Ammar.
Very very very nice sql query. I need to add ipaddresses and mac addresses but for me is very difficult.
Thanx in advance.
Valerio
Hi, I had previous requests to add IP and MAC, but this will add a lot of complexity as it will break the one to one relation between a device and a record, as a device can have multiple IPs and MAC, and then we need to aggregate that to total number of IPs or MAC for example, which defeats the purpose of showing such records. Else, we will have duplicate records which defeats the purpose of the query which is to ensure there is one truth about each device.
Hi Ammar, great query.
We have run into an interesting situation where lastonline is null for a number of devices even though they are active. We have switched the filter to use LastActiveTime but I was interested if you might have any ideas why lastonline wasn’t being populated?
Thanks,
Adam
Hi Ammar,
Thanks for the great query, it is very useful.
We recently ran into an interesting problem where LastOnline was returning null for a number of devices. We changed the filter to use LastActiveTime but were wondering if you have any idea why some devices would have null for LastOnline?
Thanks,
Adam
hmmmm, strange. No idea I am afraid ! But LastActiveTime worked?
Great Post. Can you add CPU type i3, i5 or i7 in this report. Also do you have Internet explorer versions query report for all systems.
I will post the script at Github so everyone can contribute there. Stay Tuned
Awesome report! Possible to have processor name, graphics card, free disk space left in the report?
I will post the script at Github so everyone can contribute there. Stay Tuned
Hello Ammar,
this is very useful and I’d suggest to post this query on GitHub so that anyone could help improve it and have it versioned.
Hello Ammar Hasaen.
Thank you very much for the work done on the script.
Is it possible to add additional information to this report? Monitor model, processor model, printer model. This report would replace all the data collection reports in the SCСM, except for the programs 🙂
Fantastic report, Thank you!
Is it possible to add the full name of the user and not only the username in the report?
Hi, thank you for this great report!
I was wondering, how can I edit this script, so it will display also Free Disk Space?
Thank you!
For some reason report is only producing 86 results where as i have over 1600 machines in my database, any reason why it will be like that ? the information for 86 machine is absolutely fine with no issues.
can not figure out why its like this.
Please advise if anyone can ?
Hello,
I would like to know if you. Could you help me with a query about the SCCM quickfixengineering table, since it only captures the operating system KB’s and not those of Office (2013-2016). Do you know any way to solve this ???
I will appreciate any guidance you can give me.
Beforehand thank you very much.
Ammar continue working this script?
Thank you so much, Sir
This is a really great query very usefull