Last user, login time and null entries

[cross_eur]

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,

[RCorbeil]

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.

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]

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.

[RCorbeil]

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]

RC62N wrote:
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.

[RCorbeil]

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:

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]

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.

[RCorbeil]

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.

LEFT JOIN tblADUsers ON tblADUsers.Userdomain = tblAssets.Userdomain AND tblADUsers.Username = tblAssets.Username

[cross_eur]

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.

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