Inactive user accounts?

[bdowell]

I'd like to be able to pull a report showing inactive Windows domain users (inactive longer than 90 days) Is this possible?

[bdowell]

Lansweeper wrote:
Lansweeper does not store an AD user's status (enabled/disabled) in the database, so you cannot include this information in your reports.

😞

That's a little disappointing since it would be so much more useful if we could filter out disabled users from the report. (We're supposed to be looking for enabled user accounts that are not being used and disabling those. Once we disable the account we don't really need to be concerned about the fact that the account is no longer being used.)

[Hemoco · Accepted solution]

You can try the report below to list users:
- For whom no logon events have been detected.
- For whom the most recently detected logon occurred more than 90 days ago.

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
SubQuery1.Lastlogon
From tblADusers
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
Max(tblCPlogoninfo.logontime) As Lastlogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery1 On SubQuery1.Username = tblADusers.Username
And SubQuery1.Userdomain = tblADusers.Userdomain
Where (SubQuery1.Lastlogon Is Null) Or
(SubQuery1.Lastlogon < GetDate() - 90)
Order By tblADusers.Userdomain,
tblADusers.Username

[bdowell]

Is there a way to refine that report slightly to ignore disabled users? And would there be an easy way to find users that are disabled (to compare this report against, as an example).

Thanks again!

Lansweeper wrote:
You can try the report below to list users:
- For whom no logon events have been detected.
- For whom the most recently detected logon occurred more than 90 days ago.

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
SubQuery1.Lastlogon
From tblADusers
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
Max(tblCPlogoninfo.logontime) As Lastlogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery1 On SubQuery1.Username = tblADusers.Username
And SubQuery1.Userdomain = tblADusers.Userdomain
Where (SubQuery1.Lastlogon Is Null) Or
(SubQuery1.Lastlogon < GetDate() - 90)
Order By tblADusers.Userdomain,
tblADusers.Username