Additional User Reports

[CBG-NK]

I noticed that there is a lack of reports for Users. I would like to generate reports for:

1. Inactive users: similar to how there are reports for computers not seen in the last x-days. This is extremely helpful for maintaining a clutter-free AD.
   
2. Newly created users: similar to reports for new computers found in the last x-days.
   
3. Recently deleted users.
   
4. Users who are members of the "Administrators" group (local machine).

These user reports are all very useful for network security and IT auditing purposes. So would appreciate your help in being able to create these reports. Thanks.

[CBG-NK]

Oh and might as well add AD deleted computer accounts to the wishlist as well! 😄

[Susan_A]

CBG-NK wrote:
Oh and might as well add AD deleted computer accounts to the wishlist as well! 😄

I've added this to the wish list as well. Thank you for your feedback!

[CBG-NK]

Thanks a bunch Susan! They worked like a charm.

I really appreciate adding the AD deleted users to the wishlist, because knowing when and which users were deleted, and who deleted them, can greatly assist in saving time during IT auditing and investigations.

[Susan_A · Accepted solution]

CBG-NK wrote:
Inactive users: similar to how there are reports for computers not seen in the last x-days. This is extremely helpful for maintaining a clutter-free AD.

You could build a report based on the tblCPlogoninfo database table to list users for which no recent logon events have been detected. (A logon event is added to the database when a user is logged into a computer during a computer scan.) I've posted a sample report below that lists AD users for which no logon events have been detected or whose last logon event is from more than 30 days ago. Instructions for adding reports to your Lansweeper installation can be found here.

Select Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  Max(tblCPlogoninfo.logontime) As LastLogon
From tblADusers
  Left Join tblCPlogoninfo On tblCPlogoninfo.Domain = tblADusers.Userdomain And
    tblCPlogoninfo.Username = tblADusers.Username
Group By tblADusers.Username,
  tblADusers.Userdomain
Having (Max(tblCPlogoninfo.logontime) Is Null) Or
  (Max(tblCPlogoninfo.logontime) = '') Or
  (Max(tblCPlogoninfo.logontime) < GetDate() - 30)
Order By tblADusers.Userdomain,
  tblADusers.Username

CBG-NK wrote:
Newly created users: similar to reports for new computers found in the last x-days.

The sample report below lists AD users created in the last 7 days.

Select Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Name,
  tblADusers.Displayname,
  tblADusers.whenCreated
From tblADusers
Where tblADusers.whenCreated > GetDate() - 7
Order By tblADusers.Userdomain,
  tblADusers.Username

CBG-NK wrote:
Recently deleted users.

If you mean deleted from Active Directory: this is not information we currently store in the database, but I've added the feature to the wish list. You can have users that are deleted or disabled in Active Directory removed from your Lansweeper database though, with the database cleanup options found under Configuration\Server Options.

CBG-NK wrote:
Users who are members of the "Administrators" group (local machine).

There is a built-in report in the Reports tab for this, called "Computer: Unauthorized Administrators". More information on how this report works can be found here.