Notification

Icon
Error

User Last Login (If Any) - Report of user Login

Posted: Tuesday, May 5, 2015 12:29:19 PM(UTC)
Kev1969

Kev1969

Member Original PosterPosts: 40
0
Like
This issue has been solved! Click here to view the solution
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
#1Daniel.B Member Posts: 1,150  
posted: 5/5/2015 2:25:46 PM(UTC)
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:
Code:

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
bwe_voi
#2bwe_voi Member Posts: 1  
posted: 6/5/2015 10:41:35 AM(UTC)
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
#3Daniel.B Member Posts: 1,150  
posted: 6/5/2015 1:50:53 PM(UTC)
The following report lists only the last logon which was scanned by Lansweeper for each user.
Code:

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
#4Argon0 Member Posts: 43  
posted: 3/26/2020 5:18:03 PM(UTC)
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?

Originally Posted by: Daniel.B Go to Quoted Post
The following report lists only the last logon which was scanned by Lansweeper for each user.
Code:

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


Active Discussions

Lansweeper NOT doesn't work as expected
by  KevinA-REJIS   Go to last post Go to first unread
Last post: Yesterday at 5:49:29 PM(UTC)
Lansweeper Combined Vulnerability Report Q1 2019
by  Leslie Stroobant  
Go to last post Go to first unread
Last post: Yesterday at 3:27:42 PM(UTC)
Lansweeper All Devices with WINS Service Running on the Network
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 2:44:56 PM(UTC)
Lansweeper Physical Laptops and Desktops Only
by  pryan67  
Go to last post Go to first unread
Last post: Yesterday at 1:51:56 PM(UTC)
Lansweeper Assistance With AV & Bitlocker Report
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 12:50:47 PM(UTC)
Lansweeper Count of completed KB installation Report
by  jamie21  
Go to last post Go to first unread
Last post: 6/4/2020 11:49:57 AM(UTC)
Lansweeper local admin users of a specific device
by  Andy.S   Go to last post Go to first unread
Last post: 6/4/2020 10:42:23 AM(UTC)
Lansweeper Bitlocker Report not contain all computers
by  Andy.S  
Go to last post Go to first unread
Last post: 6/3/2020 6:20:53 PM(UTC)