cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rwllr
Engaged Sweeper II
I'm trying to create a report showing all users that have logged in to all PC's. I've made it this far, but would like to narrow it down to show only the latest login to the PC of each user.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen,
tUserLogons.Username As [Logged on user account],
tblCPlogoninfo.logontime,
tblCPlogoninfo.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join (Select Distinct tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username
From tblCPlogoninfo) tUserLogons On tUserLogons.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
[Logged on user account]
2 REPLIES 2
Bruce_Garoutte
Engaged Sweeper II
Perfect!!!
This was exactly what I needed as well!
MikeMc
Champion Sweeper II
Perhaps something like this will work for you?

Select tblAssets.AssetID,
tblAssets.AssetName,
T1.Domain,
T1.Username,
T1.LastLogonTime
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join (Select tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogonTime
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) T1 On T1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
T1.LastLogonTime Desc