User Last Login (If Any)

[kevinoie]

Hi, I am trying to run a report based on AD Users and when was the LAST TIME, or if they have ever logged on.

I am using this code:

Select Top 1000000 tblADusers.Displayname,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Department,
tblADusers.Office,
tblADusers.Description,
tblADusers.whenCreated
From tblADusers

However, I cant see where I can specify the last login time, if any, that a user has logged on to any PC.

Any help appreciated.

Kev

[Daniel_B]

The following report lists only the last logon which was scanned by Lansweeper for each user.

Select Top 1000000 tblADusers.Displayname,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Department,
  tblADusers.Office,
  tblADusers.Description,
  tblADusers.whenCreated,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tLastLogon.[last logon]
From tblADusers
  Left Join (Select Max(tblCPlogoninfo.logontime) As [last logon],
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain
  From tblCPlogoninfo
  Group By tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain) tLastLogon On tblADusers.Username =
    tLastLogon.Username And tblADusers.Userdomain = tLastLogon.Domain
  Left Join (Select tblCPlogoninfo.logontime As [last logon],
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.AssetID
  From tblCPlogoninfo) tLogonAsset On tLogonAsset.[last logon] =
    tLastLogon.[last logon] And tLogonAsset.Username = tblADusers.Username And
    tLogonAsset.Domain = tblADusers.Userdomain
  Left Join tblAssets On tLogonAsset.AssetID = tblAssets.AssetID
Order By tblADusers.Displayname

[Argon0]

So I want this a bit the other way around...
I want to see per asset the last AD user from tblcplogininfo that logged into an asset, and the time they did so... Can you help?

Daniel.B wrote:
The following report lists only the last logon which was scanned by Lansweeper for each user.

Select Top 1000000 tblADusers.Displayname,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Department,
  tblADusers.Office,
  tblADusers.Description,
  tblADusers.whenCreated,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tLastLogon.[last logon]
From tblADusers
  Left Join (Select Max(tblCPlogoninfo.logontime) As [last logon],
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain
  From tblCPlogoninfo
  Group By tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain) tLastLogon On tblADusers.Username =
    tLastLogon.Username And tblADusers.Userdomain = tLastLogon.Domain
  Left Join (Select tblCPlogoninfo.logontime As [last logon],
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.AssetID
  From tblCPlogoninfo) tLogonAsset On tLogonAsset.[last logon] =
    tLastLogon.[last logon] And tLogonAsset.Username = tblADusers.Username And
    tLogonAsset.Domain = tblADusers.Userdomain
  Left Join tblAssets On tLogonAsset.AssetID = tblAssets.AssetID
Order By tblADusers.Displayname

[bwe_voi]

Hey this Report help us a lot.
Can you tell me how it can be shown that only the last Computer, on which each user has logged on, is listed?

Kind Regards

voi

[Daniel_B · Accepted solution]

In order to list this information, you need to add more tables to your reports. Each logon scanned by Lansweeper is stored in tblCPlogoninfo. Asset names are stored in tblAssets. The following report lists computers to which each user has logged on:

Select Top 1000000 tblADusers.Displayname,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Department,
  tblADusers.Office,
  tblADusers.Description,
  tblADusers.whenCreated,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tLastLogon.[last logon]
From tblADusers
  Left Join (Select Max(tblCPlogoninfo.logontime) As [last logon],
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.AssetID
  From tblCPlogoninfo
  Group By tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.AssetID) tLastLogon On tblADusers.Username =
    tLastLogon.Username And tblADusers.Userdomain = tLastLogon.Domain
  Left Join tblAssets On tLastLogon.AssetID = tblAssets.AssetID
Order By tblADusers.Displayname