Report - List all users last logon for a specfic computer

[Kent_Champion]

Hi,

I'm trying to create a report that will list all users last login times for a specific computer.
In trying to achieve this I have tired to merge these two scripts together without success. I have added these below.
I am hoping that someone with a better understanding of SQL can help. Thank you in advance for your time and effort.

This script has everything that I want except for the users last login time on the specific computer
----------------------------------------------------------------------------------------------------
Select Distinct Top 30 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
SubQuery1.Username,
SubQuery1.Domain As Userdomain
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join (Select Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username
From tblCPlogoninfo
Order By tblCPlogoninfo.logontime Desc) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Where tblAssets.AssetName = 'lan-001' And tblAssetCustom.State = 1


This script has the last login time of the users that I want included in the script above
----------------------------------------------------------------------------------------
Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID

[brandon_jones]

Try this. This will show the logon history for 31 days. You can search the report by computer or by user.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime,
tsysOS.Image As icon,
tsysOS.OSname
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblCPlogoninfo.logontime > GetDate() - 31
Order By tblAssets.AssetName,
tblCPlogoninfo.logontime Desc

[laurentiun]

hello,

no news on this topic? I'm looking for the same report