cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
kevinoie
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
Daniel_B
Lansweeper Alumni
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
Champion Sweeper
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
Engaged Sweeper
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
Lansweeper Alumni
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