All asset types Report

[s_subramanian]

Hi,

Using the below query from one of the posts here. However this only gives me the list of only Windows Asset Type. I want to include all the asset types in the query (Windows, Printer, Webserver, Linux etc.,) any help is appreciated


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes1.AssetTypename As [Asset Type],
tblAssets.Domain,
tsysOS.OSname As [OS name],
tblAssetCustom.Manufacturer,
Case
When tblAssetCustom.Manufacturer Like '%lenovo%' Then
tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
tblAssets.IPAddress As [IP address],
tsysIPLocations.IPLocation As [IP location],
tblAssets.Mac As [MAC address],
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Description,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Date],
tblAssets.FQDN,
tblAssetCustom.DNSName As [DNS name],
tblAssetCustom.LastPatched As [Last Patched],
tblAssetCustom.LastFullbackup As [Last Full Backup],
tblAssetCustom.LastFullimage As [Last Full Image],
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1 As [Asset Model],
tblAssetCustom.Custom2 As [Asset Category],
tblAssetCustom.Custom3 As Region,
tblAssetCustom.Custom4 As [FPR Unit],
tblAssetCustom.Custom5 As Location,
tblAssetCustom.Custom6 As [Sub Location],
tblAssetCustom.Custom7,
tblAssetCustom.Custom8,
tblAssetCustom.Custom9,
tblAssetCustom.Custom10,
tblAssetCustom.Custom11,
tblAssetCustom.Custom12,
tblAssetCustom.Custom13,
tblAssetCustom.Custom14,
tblAssetCustom.Custom15,
tblAssetCustom.Custom16,
tblAssetCustom.Custom17,
tblAssetCustom.Custom18,
tblAssetCustom.Custom19,
tblAssetCustom.Custom20,
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
Case When tblComputersystem.Domainrole > 1 Then 'server' Else 'workstation'
End As DomainRole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[putni]

Sorry to reopen this ticket, but i have a similar question.
I want to show all Cell phones, iPhones, Tablets and iPads. How to do that?

Thanks in regard!

[s_subramanian]

Thanks a lot Daniel. It worked

[Daniel_B · Accepted solution]

That report had an inner join to tblComputersystem and tblOperatingsystem which only contain entries for Windows computers. In order to list all assets, you need to change those to Left Joins (select all rows from tblAssets).

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tblAssets.Domain,
  tsysOS.OSname As [OS name],
  tblAssetCustom.Manufacturer,
  Case
    When tblAssetCustom.Manufacturer Like '%lenovo%' Then
    tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
  tblAssets.IPAddress As [IP address],
  tsysIPLocations.IPLocation As [IP location],
  tblAssets.Mac As [MAC address],
  tblADComputers.OU,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Description,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Date],
  tblAssets.FQDN,
  tblAssetCustom.DNSName As [DNS name],
  tblAssetCustom.LastPatched As [Last Patched],
  tblAssetCustom.LastFullbackup As [Last Full Backup],
  tblAssetCustom.LastFullimage As [Last Full Image],
  tblAssetCustom.Building,
  tblAssetCustom.Department,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.BarCode,
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.OrderNumber,
  tblAssetCustom.Custom1 As [Asset Model],
  tblAssetCustom.Custom2 As [Asset Category],
  tblAssetCustom.Custom3 As Region,
  tblAssetCustom.Custom4 As [FPR Unit],
  tblAssetCustom.Custom5 As Location,
  tblAssetCustom.Custom6 As [Sub Location],
  tblAssetCustom.Custom7,
  tblAssetCustom.Custom8,
  tblAssetCustom.Custom9,
  tblAssetCustom.Custom10,
  tblAssetCustom.Custom11,
  tblAssetCustom.Custom12,
  tblAssetCustom.Custom13,
  tblAssetCustom.Custom14,
  tblAssetCustom.Custom15,
  tblAssetCustom.Custom16,
  tblAssetCustom.Custom17,
  tblAssetCustom.Custom18,
  tblAssetCustom.Custom19,
  tblAssetCustom.Custom20,
  Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
  End As [not affected by cleanup options],
  tComputersystem.type
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblComputerSystemProduct On tblAssets.AssetID =
    tblComputerSystemProduct.AssetID
  Left Join (Select Case When tblComputersystem.Domainrole > 1 Then 'Server'
      Else 'Workstation' End As type,
    tblComputersystem.AssetID
  From tblComputersystem) tComputersystem On tblAssets.AssetID =
    tComputersystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName