→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
daigls
Engaged Sweeper III
Hi,

i use the following report to get the user name and logontime of assets.

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblCPlogoninfo.logontime,
tblCPlogoninfo.Username
From tblassets
Inner Join lansweeperdb.dbo.tblCPlogoninfo On tblassets.AssetID =
tblCPlogoninfo.AssetID And tblassets.Username = tblCPlogoninfo.Username
Where tblassets.AssetName Like 'TS-VNA%'

Problem: The result has duplicate User Accounts for each asset, because there are several logontimes using Terminal Server.

How is it possible to reduce duplicate usernames?

Can somebody modify the report?
1 REPLY 1
Andy_Sismey
Champion Sweeper III
Have you tried using Max on the Date so :
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
Max(tblCPlogoninfo.logontime) As Latest,
tblCPlogoninfo.Username
From tblassets
Inner Join lansweeperdb.dbo.tblCPlogoninfo On tblassets.AssetID =
tblCPlogoninfo.AssetID And tblassets.Username = tblCPlogoninfo.Username
Where tblassets.AssetName Like 'TS-VNA%'
Group By tblassets.AssetID,
tblassets.AssetName,
tblCPlogoninfo.Username