Report to generate physical asset list

[nagsen]

Hello,
I need help generating a report with below conditions
1. All physical assets including (workstation, laptop, PC, Servers, ESXI Hosts, Storage, etc.)
2. Do not include (Network devices such as Switches, Routers and Virtual Machines, Monitors, Printers etc.)
3. Do not list duplicate assets.

Display Items: AssetName, IP Address, Type, Domain, Description, Manufacturer, Model, Lastseen

Thank you in advance

Regards,
Nagsen

[moakyz03]

Hi, you can try this and see if it does what you need:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tAssettype.[Asset Type],
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.Image As icon,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On
      tblAssets.IPNumeric Between tsysIPLocations.StartIP And
      tsysIPLocations.EndIP
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
      TsysChassisTypes.Chassistype
  Left Join (Select tblAssets.AssetID,
      Case
        When tblAssets.Assettype = -1 Then Case
            When tblComputersystem.Domainrole > 1 Then 'Server'
            Else Case
                When (TsysChassisTypes.ChassisName = 'Notebook' Or
                  TsysChassisTypes.ChassisName = 'Laptop' Or
                  TsysChassisTypes.ChassisName = 'Portable') Then 'Laptop'
                Else 'Desktop'
              End
          End
        When tsysAssetTypes.AssetTypename = 'Media system' Then 'NAS'
        When tsysAssetTypes.AssetTypename = 'Battery' Then 'UPS'
        Else tsysAssetTypes.AssetTypename
      End As [Asset Type]
    From tblAssets
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Left Join tblComputersystem On tblComputersystem.AssetID =
          tblAssets.AssetID
      Left Join tblSystemEnclosure On
          tblAssets.AssetID = tblSystemEnclosure.AssetID
      Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
          TsysChassisTypes.Chassistype) tAssettype On tAssettype.AssetID =
      tblAssets.AssetID
Where tblAssetCustom.State = 1 And tAssettype.[Asset Type] In ('Desktop', 'Laptop', 
'All physical assets you want to include')
Order By tAssettype.[Asset Type], 
         tblAssets.AssetName