→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
nagsen
Engaged Sweeper
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
1 REPLY 1
moakyz03
Engaged Sweeper II
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