Lists BIOS, hardware and operating system information by company, manufacturer and model. Popovici Ioan @ SCCM-Zone.com 0 /ConfigMgr_A01/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} None 33f94ec3-1e39-4466-a6ee-65619da20a87 DataSource =Parameters!CollectionID.Value =Parameters!UserSIDs.Value =Parameters!ExcludeVirtualMachines.Value /* ********************************************************************************************************* * Requires | SQL, company AD attribute, Wi32_Computer_System_Product WMI class gathering * * ===================================================================================================== * * Modified by | Date | Revision | Comments * * _____________________________________________________________________________________________________ * * Octavian Cordos | 2018-01-18 | v1.0 | First version * * Ioan Popovici | 2018-08-08 | v1.1 | Added localizations, interactive sorting, new template, * * | | | element naming, windows 10 version * * Ioan Popovici | 2018-08-21 | v1.2 | Fixed duplicates minor formating fixes * * Ioan Popovici | 2018-08-23 | v1.3 | Removed company query * * ===================================================================================================== * * * ********************************************************************************************************* .SYNOPSIS This SQL Query is used to get the Hardware info of a Computer Collection. .DESCRIPTION This SQL Query is used to get the Hardware info of a Computer Collection including BIOS version and Computer model. .NOTES Part of a report should not be run separately. .LINK https://SCCM-Zone.com https://github.com/Ioan-Popovici/SCCMZone */ /*##=============================================*/ /*## QUERY BODY */ /*##=============================================*/ SELECT DISTINCT /* IMPORTANT! YOU NEED TO ENABLE THE COMPANY FIELD GATHERING FOR SYSTEM DISCOVERY OTHERWISE THIS COLUMN IS NOT AVAILABLE */ --System.Company0 as 'Company', /* CUSTOM FUNCTION LEAVE DISABLED */ ( SELECT [CM_Tools].[dbo].[ufn_GetCompany_by_ResourceID]([System].[ResourceID]) ) AS [Company], System.Manufacturer0 AS Manufacturer, CASE WHEN System.Model0 LIKE '10AA%' THEN 'ThinkCentre M93p' WHEN System.Model0 LIKE '10AB%' THEN 'ThinkCentre M93p' WHEN System.Model0 LIKE '10AE%' THEN 'ThinkCentre M93z' WHEN System.Model0 LIKE '10FLS1TJ%' THEN 'ThinkCentre M900' WHEN Product.Version0 = 'Lenovo Product' THEN ('Unknown ' + System.Model0) WHEN System.Manufacturer0 = 'LENOVO' THEN Product.Version0 ELSE System.Model0 END AS Model, System.Name0 AS DeviceName, System.UserName0 AS UserName, BIOS.Name0 AS BIOSName, BIOS.Version0 AS BIOSVersion, BIOS.SMBIOSBIOSVersion0 AS SMBIOSVersion, BIOS.SerialNumber0 AS BIOSSerialNumber, OperatingSystem.Caption0 AS OperatingSystem, OperatingSystem.CSDVersion0 AS OSServicePack, OperatingSystem.Version0 AS OSBuildNumber, OperatingSystem.InstallDate0 AS OSInstallDate, OSLocalizedNames.Value AS OSVersion FROM dbo.fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) AS System JOIN dbo.v_GS_OPERATING_SYSTEM OperatingSystem ON OperatingSystem.ResourceID = System.ResourceID LEFT JOIN dbo.vSMS_WindowsServicingStates AS OSServicingStates ON OSServicingStates.Build = OperatingSystem.Version0 LEFT JOIN vSMS_WindowsServicingLocalizedNames AS OSLocalizedNames ON OSLocalizedNames.Name = OSServicingStates.Name JOIN dbo.v_ClientCollectionMembers AS Collections ON Collections.ResourceID = System.ResourceID JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = System.ResourceID JOIN dbo.v_GS_COMPUTER_SYSTEM_PRODUCT AS Product ON Product.ResourceID = System.ResourceID WHERE Collections.CollectionID = @CollectionID AND System.Model0 NOT LIKE ( CASE @ExcludeVirtualMachines WHEN 'YES' THEN '%Virtual%' ELSE '' END ) ORDER BY Model, BIOSName, BIOSVersion /*##=============================================*/ /*## END QUERY BODY */ /*##=============================================*/ true Company System.String Manufacturer System.String Model System.String DeviceName System.String UserName System.String BIOSName System.String BIOSVersion System.String SMBIOSVersion System.String BIOSSerialNumber System.String OperatingSystem System.String OSServicePack System.String OSVersion System.String OSBuildNumber System.String OSInstallDate System.DateTime DataSource =Parameters!UserTokenSIDs.Value /* Get UserSID */ SELECT dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) AS UserSIDs true UserSIDs System.String DataSource =Parameters!UserSIDs.Value /* Get Device Collections */ SELECT CollectionID, Name FROM dbo.fn_rbac_Collection(@UserSIDs) WHERE CollectionType = 2 ORDER BY Name; true CollectionID System.String Name System.String DataSource =Parameters!ReportName.Value /* ReportDescription Dataset */ SELECT DISTINCT Description FROM ReportServer.dbo.Catalog WHERE Name = @ReportName Description System.String 34.11198mm 32.01458mm 34.71333mm 30.77741mm 34.90492mm 33.3375mm 61.36325mm 24.58617mm 25mm 25.4mm 40.21667mm 19.31458mm 6.80433mm true =Fields!BIOSName.Value true =SrsResources.Localization.GetString("BIOS Name", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!BIOSVersion.Value true =SrsResources.Localization.GetString("BIOS Version", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!SMBIOSVersion.Value true =SrsResources.Localization.GetString("SMBIOS Version", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!BIOSSerialNumber.Value true =SrsResources.Localization.GetString("SerialNumber", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!DeviceName.Value true =SrsResources.Localization.GetString("DeviceName", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!UserName.Value true =SrsResources.Localization.GetString("User", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!OperatingSystem.Value true =SrsResources.Localization.GetString("OperatingSystem", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!OSServicePack.Value true =SrsResources.Localization.GetString("Service Pack", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!OSBuildNumber.Value true =SrsResources.Localization.GetString("Build", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!OSVersion.Value true =SrsResources.Localization.GetString("Version", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!OSInstallDate.Value true =SrsResources.Localization.GetString("InstallDate", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Sum(Count(Fields!DeviceName.Value)) Model true =SrsResources.Localization.GetString("Total", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 6.80433mm true true =Fields!BIOSName.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!BIOSVersion.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!SMBIOSVersion.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!BIOSSerialNumber.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!DeviceName.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!UserName.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!OperatingSystem.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!OSServicePack.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!OSBuildNumber.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true OS_Version =Fields!OSVersion.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!OSInstallDate.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true Textbox8 #e6eefc Middle 2pt 2pt 2pt 2pt 6.80433mm true true Computers #c6daf8 2pt 2pt 2pt 2pt true true #c6daf8 2pt 2pt 2pt 2pt true true #c6daf8 2pt 2pt 2pt 2pt 9 true true =Count(Fields!DeviceName.Value) #c6daf8 2pt 2pt 2pt 2pt 6.35mm true true #c6daf8 2pt 2pt 2pt 2pt true true #c6daf8 2pt 2pt 2pt 2pt true true #c6daf8 2pt 2pt 2pt 2pt 9 true true =CountDistinct(Fields!Model.Value) #c6daf8 2pt 2pt 2pt 2pt 6.80433mm true true SkyBlue 2pt 2pt 2pt 2pt true true SkyBlue 2pt 2pt 2pt 2pt true true SkyBlue 2pt 2pt 2pt 2pt 9 true true =CountDistinct(Fields!Model.Value) SkyBlue 2pt 2pt 2pt 2pt 6.35mm true true #9eb6e4 2pt 2pt 2pt 2pt true true #9eb6e4 2pt 2pt 2pt 2pt true true #9eb6e4 2pt 2pt 2pt 2pt 9 true true =Count(Fields!DeviceName.Value) #9eb6e4 2pt 2pt 2pt 2pt 6.80433mm true true #7292cc Middle 2pt 2pt 2pt 2pt true true #7292cc Middle 2pt 2pt 2pt 2pt true true #7292cc Middle 2pt 2pt 2pt 2pt 9 true true =Count(Fields!DeviceName.Value) #7292cc Middle 2pt 2pt 2pt 2pt 26.9767mm true =Fields!Company.Value Company true =SrsResources.Localization.GetString("Company", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 35.57492mm true =Fields!Manufacturer.Value Manufacturer true =SrsResources.Localization.GetString("Manufacturer", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 37.57083mm true =Fields!Model.Value Model true =SrsResources.Localization.GetString("Model", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt After =Fields!Company.Value =Fields!Company.Value =Fields!Manufacturer.Value =Fields!Model.Value 26.9767mm true true =Fields!Company.Value #9eb6e4 Top 2pt 2pt 2pt 2pt true =Fields!Manufacturer.Value =Fields!Manufacturer.Value 35.57492mm true true =Fields!Manufacturer.Value SkyBlue Top 2pt 2pt 2pt 2pt =Fields!Model.Value =Fields!Model.Value 37.57083mm true true =Fields!Model.Value #c6daf8 Top 2pt 2pt 2pt 2pt =Fields!BIOSName.Value =Fields!BIOSVersion.Value =Fields!SMBIOSVersion.Value =Fields!BIOSSerialNumber.Value =Fields!DeviceName.Value =Fields!UserName.Value =Fields!OperatingSystem.Value =Fields!OSServicePack.Value =Fields!OSVersion.Value =Fields!OSBuildNumber.Value =Fields!OSInstallDate.Value true TV_Model true TV_Manufacturer 37.57083mm true true Models #c6daf8 2pt 2pt 2pt 2pt true TV_Company 35.57492mm true true Manufacturers SkyBlue 2pt 2pt 2pt 2pt 37.57083mm true true SkyBlue 2pt 2pt 2pt 2pt Before 35.57492mm true true Computers #9eb6e4 2pt 2pt 2pt 2pt 37.57083mm true true #9eb6e4 2pt 2pt 2pt 2pt Before 26.9767mm true true Total #7292cc Middle 2pt 2pt 2pt 2pt 35.57492mm true true #7292cc Middle 2pt 2pt 2pt 2pt 37.57083mm true true #7292cc Middle 2pt 2pt 2pt 2pt Before HardwareData 13.71357mm 0.9525mm 46.72165mm 495.86284mm 1.47018in 8.34071in 0.4in true true =SrsResources.Localization.GetString("Description", User!Language) 2pt 2pt 2pt 2pt true true =SrsResources.Localization.GetString(Fields!Description.Value, User!Language) 5pt 2pt 2pt 2pt ReportDescription 0.03157in 0.127cm 0.4in 9.81089in 1 0.45796in 0.0375in 0in 19.52216in 2 1.5pt White 1.5pt White 1.5pt White 1.5pt White 1.5pt 6.15408cm #6e7b8b 49.81607cm 2.5333cm true true Embedded Logo FitProportional 0.05723in 0.12701cm 0.49111in 1.47018in 0.6007in 0.0375in 0in 19.52216in 1 0.75pt White 0.75pt White 0.75pt White 0.75pt White 0.75pt true true =SrsResources.Localization.GetString(Globals!ReportName, User!Language) 1.63513cm 0.12701cm 0.89817cm 24.91965cm 2 Middle 2pt 2pt 2pt 2pt #6e7b8b 0.82022cm true true true true ="Page: " + Globals!PageNumber.ToString() + "of " + Globals!TotalPages.ToString() 0.03528cm 0.03175cm 0.63492cm 2.79292cm #6e7b8b 29.7cm 21cm 2.54cm 2.54cm 2.54cm 2.54cm 1.27cm