Notification

Icon
Error

Report to find all computers with no logon data

Posted: Thursday, June 25, 2020 5:49:06 PM(UTC)
mravel

mravel

Member Original PosterPosts: 1
1
Like
I'm attempting to run a report to find all devices that either have Config > User Info > Last logon as blank/null or that have a logon time older than X days but i keep getting blank results. Would someone be able to assist? Thank you.

I've used this as a baseline (but this does not include assets with no logon data)

Code:

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
Andy.S
#1Andy.S Member Posts: 27  
posted: 7/2/2020 9:55:53 AM(UTC)
Hi ,

I think this should do what you need, I have set the Days to 90 :

Code:
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
Where (SubQuery.LastLogon <= DateAdd(day, -90, GetDate()) Or
    SubQuery.LastLogon Is Null)

Active Discussions

Lansweeper Help Desk Workflow
by  lswanson   Go to last post Go to first unread
Last post: Yesterday at 11:12:21 PM(UTC)
Lansweeper Changing to remote scanning due to COVID
by  FrankSc  
Go to last post Go to first unread
Last post: Yesterday at 9:35:26 PM(UTC)
Lansweeper Not giving hackers the Domain Admin password / account
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:27:13 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  lansweeper25t34  
Go to last post Go to first unread
Last post: Yesterday at 8:33:28 PM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 4:57:26 PM(UTC)
Lansweeper Merge Asset button
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/5/2020 9:27:54 PM(UTC)
Lansweeper Merge Two Assets or Update Based on Serial Number
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:25:16 PM(UTC)
Lansweeper Hyper-V guest assets
by  bgstein  
Go to last post Go to first unread
Last post: 8/5/2020 5:50:24 PM(UTC)