cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Kent_Champion
Engaged Sweeper
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
2 REPLIES 2
brandon_jones
Champion Sweeper III
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
Engaged Sweeper III
hello,

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