Community FAQ
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dlafleur
Champion Sweeper

Good morning Lansweeper folks.

I am looking for a report to show AD service accounts, that have not had their password reset in over 330 days.
There are some AD reports, but none with what I'm really looking for.  Has anyone used a report like this?  Does anyone have a report that comes close to what I am looking for?

Thanks for looking and for your time.

8 ACCEPTED SOLUTIONS
Mister_Nobody
Honored Sweeper III

Where do you store AD service accounts? OU-paths is needed.

View solution in original post

Mister_Nobody
Honored Sweeper III

Base report for services:

Select Top 1000000 Case
    When tblADusers.Displayname Is Null Or
      tblADusers.Displayname = '' Then tblADusers.Userdomain + '\' +
      tblADusers.Username
    Else tblADusers.Displayname
  End As Displayname,
  tblADusers.Firstname,
  tblADusers.MiddleName,
  tblADusers.Lastname,
  tblADusers.OU,
  Case
    When Coalesce(tblADusers.IsEnabled, 0) = 0 Then 'False'
    Else 'True'
  End As EnabledInAD,
  tblADusers.Name,
  'usersm.png' As Icon,
  tblADusers.Lastchanged,
  tblADusers.whenCreated,
  tblADusers.whenChanged,
  tblADusers.LastLogon,
  tblADusers.IsEnabled,
  tblADusers.ExpirationDate,
  tblADusers.LockoutDate,
  tblADusers.PasswordNeverExpires,
  tblADusers.PasswordLastSet,
  tblADusers.UserCannotChangePassword,
  tblADusers.PasswordRequired,
  tblADusers.PasswordExpirationDate,
  tblADusers.PasswordChangeableDate,
  tblADusers.UPN,
  tblADusers.Description,
  tblADusers.email
From tblADusers
Where (tblADusers.OU Like N'%OU=Network%resources,%DC=your,%DC=domain,%DC=local%' Or
    tblADusers.OU Like N'%OU=Services,%DC=your,%DC=domain,%DC=local%' Or
    tblADusers.OU Like N'%CN=Users,%DC=your,%DC=domain,%DC=local%')

View solution in original post

I attached what I see in AD.  I'm not an AD expert so how would I figure out the ou?

AD_ScreebShot.png

View solution in original post

change filter to this

Where (tblADusers.OU Like N'%Service Accounts%')

View solution in original post

That is getting me closer.  I hate to keep bothering you, as I would ask my DBA, however he is out of the office.  
Can you tell me how I would go about only getting results for passwords not set in 330 + days?

View solution in original post

Jacob_H
Lansweeper Employee
Lansweeper Employee

Try adding this to the WHERE clause:

And tblADusers.PasswordLastSet < GetDate() - 330

 

View solution in original post

DateDiff(day, tblADusers.PasswordLastSet, GetDate()) password_days,

View solution in original post

Jacob_H
Lansweeper Employee
Lansweeper Employee

Ah - to answer your OU question - in Active Directory Users and Computers (ADUAC)  >  to go 'view' > and enable 'advanced features'   Then right-click on the folder (OU) that you want to know about, go to 'properties' and then 'attribute editor'  >  the OU is in 'distinguishedName' attribute. 2025-07-17 09_40_32-dc1 - Remote Desktop Connection.png2025-07-17 09_41_25-dc1 - Remote Desktop Connection.png2025-07-17 09_42_33-dc1 - Remote Desktop Connection.png

View solution in original post

9 REPLIES 9
dlafleur
Champion Sweeper

Thank you everyone for your help and input.  I very much appreciate it and appreciate you.

Mister_Nobody
Honored Sweeper III

Base report for services:

Select Top 1000000 Case
    When tblADusers.Displayname Is Null Or
      tblADusers.Displayname = '' Then tblADusers.Userdomain + '\' +
      tblADusers.Username
    Else tblADusers.Displayname
  End As Displayname,
  tblADusers.Firstname,
  tblADusers.MiddleName,
  tblADusers.Lastname,
  tblADusers.OU,
  Case
    When Coalesce(tblADusers.IsEnabled, 0) = 0 Then 'False'
    Else 'True'
  End As EnabledInAD,
  tblADusers.Name,
  'usersm.png' As Icon,
  tblADusers.Lastchanged,
  tblADusers.whenCreated,
  tblADusers.whenChanged,
  tblADusers.LastLogon,
  tblADusers.IsEnabled,
  tblADusers.ExpirationDate,
  tblADusers.LockoutDate,
  tblADusers.PasswordNeverExpires,
  tblADusers.PasswordLastSet,
  tblADusers.UserCannotChangePassword,
  tblADusers.PasswordRequired,
  tblADusers.PasswordExpirationDate,
  tblADusers.PasswordChangeableDate,
  tblADusers.UPN,
  tblADusers.Description,
  tblADusers.email
From tblADusers
Where (tblADusers.OU Like N'%OU=Network%resources,%DC=your,%DC=domain,%DC=local%' Or
    tblADusers.OU Like N'%OU=Services,%DC=your,%DC=domain,%DC=local%' Or
    tblADusers.OU Like N'%CN=Users,%DC=your,%DC=domain,%DC=local%')
Mister_Nobody
Honored Sweeper III

Where do you store AD service accounts? OU-paths is needed.

I attached what I see in AD.  I'm not an AD expert so how would I figure out the ou?

AD_ScreebShot.png

change filter to this

Where (tblADusers.OU Like N'%Service Accounts%')

That is getting me closer.  I hate to keep bothering you, as I would ask my DBA, however he is out of the office.  
Can you tell me how I would go about only getting results for passwords not set in 330 + days?

DateDiff(day, tblADusers.PasswordLastSet, GetDate()) password_days,

Jacob_H
Lansweeper Employee
Lansweeper Employee

Try adding this to the WHERE clause:

And tblADusers.PasswordLastSet < GetDate() - 330

 

Jacob_H
Lansweeper Employee
Lansweeper Employee

Ah - to answer your OU question - in Active Directory Users and Computers (ADUAC)  >  to go 'view' > and enable 'advanced features'   Then right-click on the folder (OU) that you want to know about, go to 'properties' and then 'attribute editor'  >  the OU is in 'distinguishedName' attribute. 2025-07-17 09_40_32-dc1 - Remote Desktop Connection.png2025-07-17 09_41_25-dc1 - Remote Desktop Connection.png2025-07-17 09_42_33-dc1 - Remote Desktop Connection.png

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now