cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GMFDE
Engaged Sweeper III
I have a report that lists the systems that have not logged on in the last 30 days. I need to find a way to limit this to show just the last log on event. Below is what I have so far. Any help would be appreciated.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblCPlogoninfo.logontime
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
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join (Select tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogonTime
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) T1 On T1.AssetID = tblAssets.AssetID
Where tblCPlogoninfo.logontime Is Not Null And tblCPlogoninfo.logontime <
GetDate() - 30 And tblAssetCustom.State = 1 And
tblADComputers.OU Like '%citrix%'
2 REPLIES 2
Esben_D
Lansweeper Employee
Lansweeper Employee
What if you remove the time constraint in the where clause and use the MAX in your select for tblCPlogoninfo.logontime?

I believe that should work.

https://www.w3schools.com/sql/func_sqlserver_max.asp
GMFDE
Engaged Sweeper III
bump
GMFDE wrote:
I have a report that lists the systems that have not logged on in the last 30 days. I need to find a way to limit this to show just the last log on event. Below is what I have so far. Any help would be appreciated.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblCPlogoninfo.logontime
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
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join (Select tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogonTime
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) T1 On T1.AssetID = tblAssets.AssetID
Where tblCPlogoninfo.logontime Is Not Null And tblCPlogoninfo.logontime <
GetDate() - 30 And tblAssetCustom.State = 1 And
tblADComputers.OU Like '%citrix%'


New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now