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 Unable to send to External Email
by  pryan67   Go to last post Go to first unread
Last post: Yesterday at 8:21:01 PM(UTC)
Lansweeper Lansweeper assets not being Scanned
by  Jordan  
Go to last post Go to first unread
Last post: Yesterday at 6:42:22 PM(UTC)
Lansweeper Database size growing too large
by  bladd   Go to last post Go to first unread
Last post: Yesterday at 4:26:44 PM(UTC)
Lansweeper Searching Specific File
by  mzipperer  
Go to last post Go to first unread
Last post: Yesterday at 4:23:29 PM(UTC)
Lansweeper Exchange 2010 information is not populating
by  Moe   Go to last post Go to first unread
Last post: Yesterday at 12:30:38 PM(UTC)
Lansweeper Routinely Exploited Vulnerabilities Query Report
by  pryan67  
Go to last post Go to first unread
Last post: Yesterday at 12:25:31 PM(UTC)
Lansweeper Creating a report for new devices every 2 hours to alert
by  Moe   Go to last post Go to first unread
Last post: 6/4/2020 7:26:11 PM(UTC)
Lansweeper Changes in the licensing model
by  BullGates  
Go to last post Go to first unread
Last post: 6/4/2020 6:27:23 PM(UTC)