Custom Report for PC inventory

[almahmeed206]

Hi

I need report to get the following

Windows Name
Chassis Type
username
PC Name
IP
Manufacture

[Niko0]

Found a field which contains this data so it is easier:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblOperatingsystem.Caption As Windows,
  TsysChassisTypes.ChassisName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID,
  TsysChassisTypes
Where tblAssetCustom.State = 1

[Niko0 · Accepted solution]

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblSystemEnclosure.ChassisTypes,
  Case When (tblSystemEnclosure.ChassisTypes = 1) Then 'Other'
    When (tblSystemEnclosure.ChassisTypes = 2) Then 'Unknown'
    When (tblSystemEnclosure.ChassisTypes = 3) Then 'Desktop'
    When (tblSystemEnclosure.ChassisTypes = 4) Then 'Low Profile Desktop'
    When (tblSystemEnclosure.ChassisTypes = 5) Then '  Pizza Box'
    When (tblSystemEnclosure.ChassisTypes = 6) Then 'Mini Tower'
    When (tblSystemEnclosure.ChassisTypes = 7) Then 'Tower'
    When (tblSystemEnclosure.ChassisTypes = 😎 Then 'Portable'
    When (tblSystemEnclosure.ChassisTypes = 9) Then 'Laptop'
    When (tblSystemEnclosure.ChassisTypes = 10) Then '  Notebook'
    When (tblSystemEnclosure.ChassisTypes = 11) Then 'Hand Held'
    When (tblSystemEnclosure.ChassisTypes = 12) Then 'Docking Station'
    When (tblSystemEnclosure.ChassisTypes = 13) Then 'All in One'
    When (tblSystemEnclosure.ChassisTypes = 14) Then 'Sub Notebook'
    When (tblSystemEnclosure.ChassisTypes = 15) Then 'Space-Saving'
    When (tblSystemEnclosure.ChassisTypes = 16) Then 'Lunch Box'
    When (tblSystemEnclosure.ChassisTypes = 17) Then 'Main System Chassis '
    When (tblSystemEnclosure.ChassisTypes = 18) Then 'Expansion Chassis'
    When (tblSystemEnclosure.ChassisTypes = 19) Then 'Sub Chassis'
    When (tblSystemEnclosure.ChassisTypes = 20) Then '  Bus Expansion Chassis'
    When (tblSystemEnclosure.ChassisTypes = 21) Then '  Peripheral Chassis'
    When (tblSystemEnclosure.ChassisTypes = 22) Then '  Storage Chassis'
    When (tblSystemEnclosure.ChassisTypes = 23) Then 'Rack Mount Chassis'
    When (tblSystemEnclosure.ChassisTypes = 24) Then 'Sealed-Case PC'
    Else 'Unknown' End As [Chasis Type Name],
  tblOperatingsystem.Caption As Windows
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1