Report - Chassis

[mdias_software]

Dear support,

Could you please help us with the report bellow? I want to insert the field "chassis" into it:

Select Top 1000000 tsysOS.Image As icon,

  tblAssets.AssetID,

  tsysOS.OSname,

  tblAssets.AssetName,

  tblAssets.Domain,

  tblAssets.Username,

  tblADusers.Department,

  tblADObjects.sAMAccountName As Manager,

  tblAssets.IPAddress,

  tsysIPLocations.IPLocation,

  tblADusers.OU,

  tblADusers.Title

From tblAssets

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Join tblADusers On tblADusers.Username = tblAssets.Username And

      tblADusers.Userdomain = tblAssets.Userdomain

  Left Join tblADObjects On

      tblADObjects.ADObjectID = tblADusers.ManagerADObjectId

  Inner Join tsysIPLocations On tsysIPLocations.LocationID =

      tblAssets.LocationID

Where tsysOS.OSname Not Like 'win 2%' And tblAssetCustom.State = 1

Group By tsysOS.Image,

  tblAssets.AssetID,

  tsysOS.OSname,

  tblAssets.AssetName,

  tblAssets.Domain,

  tblAssets.Username,

  tblADusers.Department,

  tblADObjects.sAMAccountName,

  tblAssets.IPAddress,

  tsysIPLocations.IPLocation,

  tblADusers.OU,

  tblADusers.Title

Order By tblAssets.Domain,

  tblAssets.AssetName

[Mister_Nobody]

You can use code from standard built-in report:

Select Top (1000000) tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  TsysChassisTypes.ChassisName,
  tblSystemEnclosure.LockPresent,
  tblSystemEnclosure.Manufacturer As ChassisManufacturer,
  tblSystemEnclosure.Version,
  tblSystemEnclosure.SMBIOSAssetTag,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSystemEnclosure On
      tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
      tblSystemEnclosure.ChassisTypes
  Left Outer Join tsysIPLocations On tblAssets.LocationID =
      tsysIPLocations.LocationID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[mdias_software]

Dear Mister_Nobody, This way is not good for me.... Currently my report is more complete! I just need a new column showing the type of asset if it is a "notebook, computer, laptop, virtual machine".

Could you help us please?

[Mister_Nobody · Accepted solution]

Try this:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tsysOS.OSname,
  tblAssets.AssetName,
  TsysChassisTypes.ChassisName,
  tblAssets.Domain,
  tblAssets.Username,
  tblADusers.Department,
  tblADObjects.sAMAccountName As Manager,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblADusers.OU,
  tblADusers.Title
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID And
      tblAssetCustom.State = 1
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode And
      tblAssets.oscode Not Like '%s'
  Inner Join tblSystemEnclosure On
      tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
      tblSystemEnclosure.ChassisTypes
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
      tblADusers.Userdomain = tblAssets.Userdomain
  Left Join tblADObjects On
      tblADObjects.ADObjectID = tblADusers.ManagerADObjectId
Order By tblAssets.Domain,
  tblAssets.AssetName

[mdias_software]

It is perfect! Thank you.