Notification

Icon
Error

Limit report to only include last logon time - Need help eliminating multiple entries in report

Posted: Friday, November 1, 2019 5:40:08 PM(UTC)
GMFDE

GMFDE

Member Alpha Tester Original PosterPosts: 47
0
Like
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.

Code:
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%'
GMFDE
#1GMFDE Member Alpha Tester Original PosterPosts: 47  
posted: 11/25/2019 8:34:11 PM(UTC)
bump
Originally Posted by: GMFDE Go to Quoted Post
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.

Code:
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%'


Esben.D
#2Esben.D Member Administration Posts: 1,982  
posted: 11/26/2019 11:11:14 AM(UTC)
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.co...l/func_sqlserver_max.asp

Active Discussions

Lansweeper "add cc user" doesn't show the correct listings
by  JLPingree   Go to last post Go to first unread
Last post: Yesterday at 8:12:44 PM(UTC)
Lansweeper Default User Date Format
by  RickW99456  
Go to last post Go to first unread
Last post: Yesterday at 5:00:51 PM(UTC)
Lansweeper Duplicate assets (Servers)
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 3:20:28 PM(UTC)
Lansweeper Lansweeper load the disk subsystem
by  Alexey Gorbachev  
Go to last post Go to first unread
Last post: Yesterday at 3:13:17 PM(UTC)
Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Yesterday at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Yesterday at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: 7/5/2020 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)