cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cross_eur
Engaged Sweeper II
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,
8 REPLIES 8
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
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