Notification

Icon
Error

Last user, login time and null entries - Report showing Last user, login time and null entries for last user

Posted: Wednesday, July 28, 2021 4:26:03 PM(UTC)
cross_eur

cross_eur

Member Original PosterPosts: 11
1
Like
Hello, I have searched the forums but I can't find anything that helps me create the report that I am trying to make. I am trying to identify all computers and their last logged in user , along with their full display name, not just username and login times. Also, in the same report, i would like to see the computers where no one has ever logged in, or the last person to log in (shown in the asset page) is no longer listed in the Config/UserInfo/Lastlogon list.

The idea is to identify all the computers that haven't been logged into for a while or ever, so that we can identify them as assets to physically remove.

Thank you,
RC62N
#1RC62N Member Posts: 575  
posted: 7/28/2021 9:41:41 PM(UTC)
Set up your base asset report of the characteristics you want to see, then add a LEFT JOIN to tblADUsers to retrieve the user AD info.
Code:
LEFT JOIN tblADUsers ON tblADUsers.Userdomain = tblAssets.Userdomain AND tblADUsers.Username = tblAssets.Username
cross_eur
#2cross_eur Member Original PosterPosts: 11  
posted: 7/29/2021 11:52:05 AM(UTC)
Quote:
Thanks, that helps, but it still does not show computers that no one has logged into, or that the user login information is no longer in the Config/UserInfo/Lastlogon list because they have not logged in for a really long time. Here is the code that I have so far.

Code:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblADUsers.Displayname,
  Max(tblCPlogoninfo.logontime) As LastLogon,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblAssetCustom.Custom2 As [ITSM User],
  tblAssetCustom.Custom3 As [ITSM Status],
  tblAssets.Lastseen,
  tblCPlogoninfo.Username
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID And
      tblCPlogoninfo.Username = tblAssets.Username
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join tblADUsers On tblADUsers.Userdomain = tblAssets.Userdomain
      And tblADUsers.Username = tblAssets.Username
Where tblAssets.AssetName Like 'z%'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblAssetCustom.Custom2,
  tblAssetCustom.Custom3,
  tblAssets.Lastseen,
  tblCPlogoninfo.Username,
  tsysAssetTypes.AssetTypename,
  tblAssets.Lasttried,
  tblADUsers.Displayname
Order By tblAssets.AssetName,
  LastLogon Desc
RC62N
#3RC62N Member Posts: 575  
posted: 7/29/2021 3:35:06 PM(UTC)
I think the problem is with your linking in tblCPlogoninfo. I wasn't thinking of the last logon time when I suggested the link against tblADUsers, so just had you link against tblAssets. Because you're using an INNER join against tblCPlogoninfo, you're filtering out any machines that have no recorded last logon info.

Since you're after that logon time, it makes sense to link in tblCPlogoninfo, but it's worth taking the time to trim it down to the most recent logon for each machine and to LEFT join rather than INNER join so you don't filter out machines with no logon history.

Give this a try:
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblADUsers.Displayname,
  LogonHistory.LastLogon,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblAssetCustom.Custom2 As [ITSM User],
  tblAssetCustom.Custom3 As [ITSM Status],
  tblAssets.Lastseen,
  LogonHistory.Username
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  LEFT Join (SELECT
               tblCPlogoninfo.AssetID,
               Max(tblCPlogoninfo.logontime) AS LastLogon,
               tblCPlogoninfo.Domain,
               tblCPlogoninfo.Username
             FROM tblCPlogoninfo
             GROUP BY
               tblCPlogoninfo.AssetID,
               tblCPlogoninfo.Domain,
               tblCPlogoninfo.Username) AS LogonHistory ON LogonHistory.AssetID = tblAssets.AssetID
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Left Join tblADUsers On tblADUsers.Userdomain = LogonHistory.Domain And tblADUsers.Username = LogonHistory.Username
Where tblAssets.AssetName Like 'z%'
Order By tblAssets.AssetName,
  LastLogon Desc

The inner SELECT pulls a list of just the most recent logon on each asset so you don't have to do grouping on the main body of the query. Doing a LEFT JOIN against that list ensures that you don't drop machines with no logon history from the result set.

I had previously suggested joining tblADUsers against tblAssets, but since you're pulling the last user info from tblCPlogoninfo, I've adjusted the join to link against that.
cross_eur
#4cross_eur Member Original PosterPosts: 11  
posted: 7/29/2021 3:42:41 PM(UTC)
I think I understand what you are saying, and i thank you for taking the time to explain it. I will have to do more reading on Left join and Inner join :-). But the report you sent no longer shows only the last user, but rather the last login time of all users that connected to the computer. So multiple computers are showing multiple users instead of just last user.

RC62N
#5RC62N Member Posts: 575  
posted: 7/29/2021 4:10:42 PM(UTC)
Drat. You're right: the logic is pulling the most recent logon for each user who's logged on to the machine. Let me rethink that...
cross_eur
#6cross_eur Member Original PosterPosts: 11  
posted: 7/29/2021 4:18:45 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
Drat. You're right: the logic is pulling the most recent logon for each user who's logged on to the machine. Let me rethink that...


Correct, where I am looking for only the last user and their logon time. Thank you for the time spent on this.
RC62N
#7RC62N Member Posts: 575  
posted: 7/29/2021 4:30:20 PM(UTC)
Alright, try this.

By rights. tblAssets.Userdomain/tblAssets.Username should reflect the most recent historical entry in tblCPlogoninfo, but I'm not going to assume it. I am including tblAssets.Username in the output so you can verify for yourself.

I've modified the join against tblCPlogoninfo to only pull the AssetID and the most recent logon timestamp. I've added a join against tblCPlogoninfo that links on AssetID and that last logon timestamp to identify the user recorded with that timestamp.

Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblADUsers.Displayname,
  LogonHistory.LastLogon,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblAssetCustom.Custom2 As [ITSM User],
  tblAssetCustom.Custom3 As [ITSM Status],
  tblAssets.Lastseen,
  tblCPlogoninfo.Username,
  tblAssets.Username AS [Assets Username]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  LEFT Join (SELECT
               tblCPlogoninfo.AssetID,
               Max(tblCPlogoninfo.logontime) AS LastLogon
             FROM
               tblCPlogoninfo
             GROUP BY
               tblCPlogoninfo.AssetID) AS LogonHistory ON LogonHistory.AssetID = tblAssets.AssetID
  Left Join tblCPlogoninfo ON tblCPlogoninfo.AssetID = tblAssets.AssetID AND tblCPlogoninfo.logontime = LogonHistory.LastLogon
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Left Join tblADUsers On tblADUsers.Userdomain = tblCPlogoninfo.Domain And tblADUsers.Username = tblCPlogoninfo.Username
Where tblAssets.AssetName Like 'z%'
Order By tblAssets.AssetName,
  LastLogon Desc


As far as joins, a simple explanation of LEFT vs INNER:

I want a list of records from table 1 (let's say Assets) joined against table 2 (let's say Users).

LEFT JOIN:
Show me a list of all Assets and, if there's a corresponding entry in Users, the user info. I want to see all assets regardless of whether or not there's a user.

INNER JOIN:
Show me a list of all Assets and Users where an asset has a corresponding user. If there is no user, I don't want to see the asset.
cross_eur
#8cross_eur Member Original PosterPosts: 11  
posted: 7/29/2021 5:44:36 PM(UTC)
What a work of art. Thank you infinitely for the work put into this script and the explanation on Joins. It works perfectly at first glance. I will work with it and get a better idea.

Active Discussions

Lansweeper History of relationship between pc and monitor
by  hfaddy  
Go to last post Go to first unread
Last post: Yesterday at 1:44:38 PM(UTC)
Lansweeper New Hire Login Request Report
by  Scott Davis   Go to last post Go to first unread
Last post: 9/16/2021 7:16:46 PM(UTC)
Lansweeper SCCM end of life
by  Esben.D  
Go to last post Go to first unread
Last post: 9/16/2021 2:56:23 PM(UTC)
Lansweeper Microsoft Patch Tuesday – September 2021
by  Esben.D  
Go to last post Go to first unread
Last post: 9/14/2021 8:27:06 PM(UTC)
Lansweeper Apple “FORCEDENTRY” Zero-Day Vulnerability
by  Esben.D   Go to last post Go to first unread
Last post: 9/14/2021 12:07:54 PM(UTC)
Lansweeper ALL laptops HP 340S G7 Notebook PC and Bios version
by  Carlos Montes  
Go to last post Go to first unread
Last post: 9/13/2021 3:17:19 PM(UTC)