Notification

Icon
Error

Distinct user logins per PC

Posted: Thursday, July 28, 2016 9:48:17 PM(UTC)
rwllr

rwllr

Member Original PosterPosts: 3
2
Like
I'm trying to create a report showing all users that have logged in to all PC's. I've made it this far, but would like to narrow it down to show only the latest login to the PC of each user.
Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tUserLogons.Username As [Logged on user account],
  tblCPlogoninfo.logontime,
  tblCPlogoninfo.Username
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join (Select Distinct tblCPlogoninfo.AssetID,
    tblCPlogoninfo.Username
  From tblCPlogoninfo) tUserLogons On tUserLogons.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  [Logged on user account]
MikeMc
#1MikeMc Member Posts: 109  
posted: 8/4/2016 9:24:55 PM(UTC)
Perhaps something like this will work for you?

Code:
Select tblAssets.AssetID,
  tblAssets.AssetName,
  T1.Domain,
  T1.Username,
  T1.LastLogonTime
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join (Select tblCPlogoninfo.AssetID,
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    Max(tblCPlogoninfo.logontime) As LastLogonTime
  From tblCPlogoninfo
  Group By tblCPlogoninfo.AssetID,
    tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain) T1 On T1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  T1.LastLogonTime Desc
Bruce Garoutte
#2Bruce Garoutte Member Posts: 4  
posted: 2/17/2020 6:36:29 PM(UTC)
Perfect!!!
This was exactly what I needed as well!

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)