cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mdias_software
Engaged Sweeper III
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
Kind Regards,
Mateus Dias
1 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

 

 

View solution in original post

4 REPLIES 4
Mister_Nobody
Honored Sweeper II

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

 

 

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?

Kind Regards,
Mateus Dias

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

 

 

It is perfect! Thank you.

Kind Regards,
Mateus Dias