cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jperry
Engaged Sweeper III
I wanted a query to compare whether the user last logged onto a computer matched the user's last logon.



Select
tblAssets.AssetID [AssetID],
tblAssets.AssetName [AssetName],
CASE
WHEN((tblAssets.UserDomain = tblCPlogoninfo.Domain) AND (tblAssets.Username = tblCPlogoninfo.Username)) THEN 1
Else 0
END [Matches],
tblAssets.UserDomain [C-UserDomain],
tblAssets.Username [C-UserName],
tblCPlogoninfo.Domain [U-UserDomain],
tblCPlogoninfo.Username [U-UserName],
tblCPlogoninfo.logontime [logontime],
Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) [Computer OU]
from
( Select tblCPlogoninfo.Domain, tblCPlogoninfo.Username , MAX(logontime) As logontime
FROM tblCPlogoninfo
Group By tblCPlogoninfo.Domain, tblCPlogoninfo.Username
) As logoninfoRecent
Left Outer Join tblCPlogoninfo ON tblCPlogoninfo.Domain = logoninfoRecent.Domain AND tblCPlogoninfo.Username = logoninfoRecent.Username AND tblCPlogoninfo.logontime = logoninfoRecent.logontime
Full Join tblAssets ON tblAssets.AssetID = tblCPlogoninfo.AssetID
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
WHERE tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order by CASE
WHEN((tblAssets.UserDomain = tblCPlogoninfo.Domain) AND (tblAssets.Username = tblCPlogoninfo.Username)) THEN 1
Else 0
END,
tblAssets.AssetName Asc

0 REPLIES 0

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now