report to show service accounts, that have not had their password reset in over 330 days

[dlafleur]

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.

[Mister_Nobody · Accepted solution]

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

[Mister_Nobody · Accepted solution]

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%')

[dlafleur · Accepted solution]

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

 

[Mister_Nobody · Accepted solution]

change filter to this

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

[dlafleur · Accepted solution]

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?

[Jacob_H · Accepted solution]

Try adding this to the WHERE clause:

And tblADusers.PasswordLastSet < GetDate() - 330

[Mister_Nobody · Accepted solution]

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

[dlafleur]

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

[Mister_Nobody · Accepted solution]

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 · Accepted solution]

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

[dlafleur · Accepted solution]

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

 

[Mister_Nobody · Accepted solution]

change filter to this

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

[dlafleur · Accepted solution]

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?

[Mister_Nobody · Accepted solution]

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

[Jacob_H · Accepted solution]

Try adding this to the WHERE clause:

And tblADusers.PasswordLastSet < GetDate() - 330

[Jacob_H · Accepted solution]

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.