Version 5 compliant report

[poweld1]

How can I make this report version 5 compliant?

Select Distinct Top 1000000 co1.Computername,
co1.ComputerUnique,
co1.Username,
cs1.Manufacturer,
cs1.Model,
csp1.IdentifyingNumber As [Serial Number],
cpu.Name As [Processor Model],
cpu.MaxClockSpeed As [CPU (MHz)],
cs1.NumberOfProcessors As Cores,
os1.TotalVisibleMemorySize / 1024 As [Memory (MB)],
os1.Caption As [Operating System],
os1.CSDVersion As [Service Pack],
co1.LastknownIP As [IP Address],
co1.Lastseen As [Last Seen]
From upgrade_tblComputers co1
Left Outer Join upgrade_tblOperatingsystem os1 On co1.Computername =
os1.Computername
Left Outer Join upgrade_tblComputerSystemProduct csp1 On co1.Computername =
csp1.Computername
Left Outer Join upgrade_tblComputersystem cs1 On co1.Computername =
cs1.Computername
Left Outer Join upgrade_tblPROCESSOR cpu On co1.Computername =
cpu.Computername
Left Outer Join upgrade_tblDiskDrives dsk On co1.Computername =
dsk.Computername
Inner Join upgrade_tblNetwork
On co1.Computername = upgrade_tblNetwork.Computername
Where (dsk.Caption Like 'C:' And dsk.Description Like '%fixed disk%' And
upgrade_tblNetwork.DefaultIPGateway Like '172.18.49.254') Or
(upgrade_tblNetwork.DefaultIPGateway Like '172.18.49.254')

[poweld1]

Thanks, it's working now.

[Hemoco]

Remove the line below.

Order By tblAssets.AssetUnique

[Hemoco]

Could you add a Distinct to the Select statement to list unique lines only once.

Select Distinct Top 1000000 tsysOS.Image As icon

[poweld1]

Lansweeper wrote:
Could you add a Distinct to the Select statement to list unique lines only once.

Select Distinct Top 1000000 tsysOS.Image As icon

I've done that but get this message.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

[poweld1]

Thanks for this but it's showing each asset three times in the report.

[Hemoco · Accepted solution]

Please use the report below.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblProcessor.Name As [Processor Model],
tblProcessor.MaxClockSpeed As [CPU (MHz)],
tblAssets.NrProcessors As Cores,
tblAssets.Memory As [Memory (MB)],
tblOperatingsystem.Caption As [Operating System],
tblOperatingsystem.ServicePackMajorVersion As [Service Pack],
tblAssets.IPAddress As [IP Address],
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where (tblDiskdrives.Caption Like 'C:' And tblDiskdrives.Description Like '%fixed disk%' And tblNetwork.DefaultIPGateway Like '172.18.49.254') or (tblNetwork.DefaultIPGateway Like '172.18.49.254')
Order By tblAssets.AssetUnique