Provided here is a Client Asset Information SQL Query that will gather the following information:
Machine Name, Installed Site Code, Resource Domain, Account Domain, Login ID, Users Full Name, Asset Tag, Manufacturer, Model, Serial Number and Operating System
SQL Query:
Select
SD.Name0 'Machine Name',
SC.SMS_Installed_Sites0 'Site Code',
SD.Resource_Domain_OR_Workgr0 'Resource Domain',
SD.User_Domain0 'Account Domain',
SD.User_Name0 'Login ID',
UD.Full_User_Name0 'Full Name',
SE.SMBIOSAssetTag0 'Asset Tag',
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
SN.SerialNumber0 'Serial Number',
OS.Caption0 + Space(1) + OS.CsdVersion0 'Operating System',
Case SE.ChassisTypes0
When 1 Then 'Other'
When 2 Then 'Unknown'
When 3 Then 'Desktop'
When 4 Then 'Low Profile Desktop'
When 5 Then 'PizzaBox'
When 6 Then 'Mini-Tower'
When 7 Then 'Tower'
When 8 Then 'Portable'
When 9 Then 'Laptop'
When 10 Then 'Notebook'
When 11 Then 'Handheld Device'
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 'Sub-Chassis'
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 PC'
Else 'Unknown'
End 'Chassis Type',
Convert(VarChar(10), HS.LastHWScan, 101) 'Hardware Scan Date'
From v_R_System SD
Join V_R_User UD on SD.User_Name0 = UD.User_Name0
Join V_Ra_System_SmsInstalledSites SC on SD.ResourceID = SC.ResourceID
Join V_Gs_Workstation_Status HS On SD.ResourceID = HS.ResourceID
Join V_Gs_Computer_System CS On SD.ResourceID = CS.ResourceID
Join V_Gs_System_Enclosure SE On SD.ResourceID = SE.ResourceID
Join V_Gs_Pc_Bios SN On SD.ResourceID = SN.ResourceID
Join V_Gs_Operating_System OS On SD.ResourceID = OS.ResourceID
Where SD.Client0 = 1
And SD.Obsolete0 = 0
No Comments