List only one user

[JTempleton]

I am looking for a little help with my report. Currently, it will show multiple users for each computer. How can I get it only show the last user who logged onto that computer? Here is my report:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.AssetName As [Computer Name],
tblAssets.Username As [Last Logon User],
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Memory,
ProcCapacity.CPU,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress,
tblAssets.Lastseen
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tsysIPLocations.IPLocation Like '%'
Order By [Office Location],
[Computer Name]

Thanks for all the help!

[JTempleton]

Thank you so much!!

[Hemoco · Accepted solution]

Please use the following report:

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.AssetName As [Computer Name],
tblAssets.Username As [Last Logon User],
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Memory,
ProcCapacity.CPU,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress,
tblAssets.Lastseen
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tsysIPLocations.IPLocation Like '%'
Order By [Office Location],
[Computer Name]