User report want to add last logged on computer

[kbek]

So far I have this basic report that shows user information, all I'm missing is the last computer used/logged on for the user

Select Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  SubQuery1.Lastlogon,
  SubQuery1.DaysSinceLogon,
  tblADusers.Lastname,
  tblADusers.Firstname,
  tblADusers.Description,
  tblADusers.City,
  tblADusers.Zip,
  tblADusers.Country,
  tblADusers.UPN,
  tblADusers.Title,
  tblADusers.Department,
  tblADusers.OU,
  tblADusers.email,
  tblADusers.Lastchanged,
  tblADusers.IpPhone,
  tblADusers.HomeDirectory,
  tblADusers.ProfilePath,
  tblADusers.LogonScript,
  tblADusers.whenCreated,
  tblADusers.whenChanged,
  tblADusers.EmployeeType,
  tblADusers.Info,
  tblADusers.Division,
  tblADusers.ADUserID,
  tblADusers.EmployeeID,
  tblADusers.EmployeeNumber
From tblADusers
  Left Join (Select Top 1000000 tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain As Userdomain,
    Max(tblCPlogoninfo.logontime) As Lastlogon,
    DateDiff(day, Max(tblCPlogoninfo.logontime), GetDate()) As DaysSinceLogon
  From tblCPlogoninfo
  Group By tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain) SubQuery1 On SubQuery1.Username = tblADusers.Username
    And SubQuery1.Userdomain = tblADusers.Userdomain
Where SubQuery1.Lastlogon Is Not Null And
  tblADusers.OU Not Like '%OU=Disabled Accounts%'
Order By tblADusers.Userdomain,
  tblADusers.Username

Thanks

[fjca]

And replying to myself, this is much more easy than I was expecintg, I just added the following to my Select clause:

tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen

And

Left Join lansweeperdb.dbo.tblAssets On tblADusers.Username = tblAssets.Username

To my From clause.

I'm still having some users listing two (or more) *LAST* machines but in my setup this is somewhat expected...

[fjca]

Hello,

Sorry for resurrecting such an old matter, but I'm having the same issue, I would like to have the last computer used by a user, not the last user on a specific computer.

[kbek]

Not to doubt your wisdom, but the field you're pointing to seems to give the last user that used the asset in question... so that means that a user could end up with his name associated with multiple assets.

What I really want is the last asset used by any given user not the last user on any given asset

it's a subtle difference but it makes all the difference

[Esben_D]

The last user that was logged onto an asset during a scan can be found in tblAssets.username. While the name of the column doesn't give it away, the database dictionary does mention it.

You can find instructions on how to find the database dictionary here: https://www.lansweeper.com/Forum/yaf_postsm38296_Lansweeper-database-dictionary.aspx#post38296