Reports and User Logon

[Neil43]

I am needing a report that, among other things, lists the last user that logged into the computer and the username. The report I have tried to put to together does everything I need except each login is multiplied 4 or 5 times, all with the same time and date stamp. This morning I just cant seem to figure out why each user name is listed several times for each device. Hopefully someone will see my problem. I am sure it is something obvious I am doing wrong. Here is what I have:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUsers.UserID,
tblUsers.Disabled,
tblUsers.Fullname,
tblUsers.Name,
tblAssets.Username,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Right Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUsers.UserID,
tblUsers.Disabled,
tblUsers.Fullname,
tblUsers.Name,
tblAssets.Username

Any help and advice is appreciated as I am pretty much a SQL beginner. Thanks!

[Neil43]

Thank you!!!!

[Daniel_B · Accepted solution]

tblUsers contains all local user accounts which were scanned on the computer. Your report list all of these which is not required in your case. The last logged on user is always stored in tblAssets.Username. You only need the logon date from tblCPlogoninfo.
Please note: Lansweeper adds an entry to tblCPlogoninfo after scanning a computer while a user was logged on. If another user logged on and off while the computer wasn't scanned, Lansweeper won't get to know this. If you would like to have precise logon information, you need to set up logon scripts on your computers which initiate a scan with LsPush after a user logged on.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Username,
  Max(tblCPlogoninfo.logontime) As LastLogon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID And
    tblCPlogoninfo.Username = tblAssets.Username
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Username
Order By tblAssets.AssetName,
  LastLogon Desc