cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
s_subramanian
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
putni
Engaged Sweeper
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
Engaged Sweeper III
Thanks a lot Daniel. It worked
Daniel_B
Lansweeper Alumni
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