Manage stale AD accounts with PS script

I have a Windows 2008 R2 environment

I need a powershell script to find  and process stale user accounts.  Here are the conditions I want to meet
1.  Look for user objects in a particular OU (No one is this OU should have 'PasswordNeverExpires' set)
2.  Identify users whose lastLogonTimeStamp is greater than 105 days (this value may be null) OR pwdLastSet is greater
     than 91 days (this value may be null), exclude accounts that have a particular prefix; i.e     z-
3.  Capture and export these accounts to a  path\filenaname.csv with headers of
      samAccountName,givenName,sn,lastLogonTimeStamp,pwsLastSet (don't know if headers in file can be more readable,
      like LogonID,First Name, Last Name, Last Logon, Pwd Last Set - not crucial to this effort)
4.  Import the csv file sorting by samAccountName and do the following:
     a.  Disable the account
     b.  Update the Description field to some text; i.e "Disabled by automated process on $date"
     c.  Move the account to a holding OU
5.  Finally email the CSV file to whomever (there is an smtp server) with subject like; i.e. 'Disabled user accounts for 90+ day non-use'

Clearly I'm a newbie to PS.
BigmacMcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will SzymkowskiSenior Solution ArchitectCommented:
Try the script below...
Import-module activedirectory
$OU = "ou=myou,dc=domain,dc=com"
$OUHold = "ou=holdou,dc=domain,dc=com"
$Date = Get-Date
Get-ADUser -Filter * -Properties Name, samAccountName, givenName, sn, LastLogonDate, pwsLastSet, DistinguishedName |
? { $_.LastLogonDate -gt $Date.AddDays(-105) -or $_.pwdLastSet -gt $Date.AddDays(-91) -and $_.Name -notlike "z-*" } |
Select samAccountName, givenName, sn, LastLogonDate, pwsLastSet, DistinguishedName |
Sort -Property samaccountname |
Export-csv "c:\filename.csv" -nti
$Import = import-csv "c:\filename.csv
ForEach ($obj in $Import) {
$obj.samaccountname
$obj.DistinguishedName
Set-ADUser -Identity $obj.samaccountname -Enable $false -Description "Disabled by automated process on $date"
Move-ADObject -Identity $obj.DistinguishedName -TargetPath $OUHold
}
Send-MailMessage -To someone@domain.com -Subject "'Disabled user accounts for 90+ day non-use" -Attachments "c:\filename.csv" -SmtpServer server.domain.com

Open in new window


I hvae not tested this but it should work. Let me know if there are any errors.  I also had to add the DistinguishedName parameter because this is a required property to move the ADObject.

Will.
BigmacMcAuthor Commented:
I see the variable $OU = "ou=myou,dc=domain,dc=com", but it's not used anywhere to actually search for users in the OU.

I'm thinking I can put a line like this
Get-ADUser -SearchBase $OU -Filter * -Properties Name, samAccountName, givenName, sn, LastLogonDate, pwsLastSet, DistinguishedName
Will SzymkowskiSenior Solution ArchitectCommented:
Sorry yes it should be in line 5 Get-ADUser -Filter * -SearchBase $OU.

Will.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BigmacMcAuthor Commented:
So I edited for my domain and I get this error message.  It's having an issue with the date format and calculating some time in the past.  This is  with line 6.  I'll post the entire expression and error.

Get-ADUser -Filter * -SearchBase $OU -Properties Name, samAccountName, givenName, sn, LastLogonDate, pwdLastSet, DistinguishedName |
? { $_.LastLogonDate -gt $Date.AddDays(-105) -or $_.pwdLastSet -gt $Date.AddDays(-91) -and $_.Name -notlike "z-*" }
__________________________________________
Error message

Could not compare "0" to "02/20/2015 16:57:27". Error: "Cannot convert value "2/20/2015 4:57:27 PM" to type
"System.Int64". Error: "Invalid cast from 'DateTime' to 'Int64'.""
At E:\tools\scripts\ex-12.ps1:6 char:5
+ ? { $_.LastLogonDate -gt $Date.AddDays(-105) -or $_.pwdLastSet -gt $Date.AddDays ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : ComparisonFailure
Will SzymkowskiSenior Solution ArchitectCommented:
I have corrected the issue in the script below. Rather than using pwdLastSet which is in Int64 conversion (un-readable) i have used the PasswordLastSet attribute.
Import-module activedirectory
$OU = "ou=myou,dc=domain,dc=com"
$OUHold = "ou=holdou,dc=domain,dc=com"
$Date = Get-Date
Get-ADUser -Filter * -SearchBase $OU -Properties Name, samAccountName, givenName, sn, LastLogonDate, PasswordLastSet, DistinguishedName |
? { $_.LastLogonDate -gt $Date.AddDays(-105) -or $_.passwordLastSet -gt $Date.AddDays(-91) -and $_.Name -notlike "z-*" } |
Select samAccountName, givenName, sn, LastLogonDate, passwordLastSet, DistinguishedName |
Sort -Property samaccountname |
Export-csv "c:\filename.csv" -nti
$Import = import-csv "c:\filename.csv
ForEach ($obj in $Import) {
$obj.samaccountname
$obj.DistinguishedName
Set-ADUser -Identity $obj.samaccountname -Enable $false -Description "Disabled by automated process on $date"
Move-ADObject -Identity $obj.DistinguishedName -TargetPath $OUHold
}
Send-MailMessage -To someone@domain.com -Subject "Disabled user accounts for 90+ day non-use" -Attachments "c:\filename.csv" -SmtpServer server.domain.com

Open in new window


Try that let me know if you run into anymore snags.

Will.
BigmacMcAuthor Commented:
Still seems to be a problem comparing dates.

Here's the line now:

Get-ADUser -Filter * -Properties Name, samAccountName, givenName, sn, LastLogonTimeStamp, passwordLastSet, DistinguishedName |
? { $_.LastLogonTimeStamp -gt $Date.AddDAys(-105) -or $_.passwordLastSet -gt $Date.AddDays(-91) -and $_.Name -notlike "z-*" } |

Here's the error:

Could not compare "130763658548239883" to "02/08/2015 12:43:16". Error: "Cannot convert value "2/8/2015 12:43:16 PM"
to type "System.Int64". Error: "Invalid cast from 'DateTime' to 'Int64'.""
At E:\tools\scripts\ex-21.ps1:48 char:5
+ ? { $_.LastLogonTimeStamp -gt $Date.AddDAys(-105) -or $_.passwordLastSet -gt $Da ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : ComparisonFailure
Will SzymkowskiSenior Solution ArchitectCommented:
There should be no issues with that portion because those attributes using the same date/time format.

Can you try running just that line of code by itself and see what you get? I have tested those lines and it works fine for me.

Will.
BigmacMcAuthor Commented:
I think I see the issue.  I had swapped LastLogonDate with LastLogonTimeStamp.  I did this because LastLogonDate indicates a particular DC, In this case the one I authenticated with when I logged in.  When I use LastLogonDate the command runs fine.  I would prefer 'LastLogonTimeStamp' because it is a  domain wide shared attribute using an algorithm roughly accurate to within 14 days.  When I use LastLogonTimeStamp I get the error if there is a lastlogontimestamp date associated with the user.  If the lastlogontimestamp date is null the script reports the account.

I've tried using ' ? {(((Get-Date) - ([datetime]::FromFileTime($_.lastlogontimestamp))).TotalDay -gt 105)  }'   However, it comes back with nothing.  

So the scenario is the user's lastlogontimestamp may be null or have date.  If its null I want to disable it, if it has a date ~105 days I want to disable it.

Is there a way of using lastlogontimestamp instead of lastlogondate?  If I run 'get-aduser -identity testuser -property lastlogontimestamp' I get the following:

DistinguishedName  : CN=Test User,OU=Users,DC=MyDomain,DC=com
Enabled            : True
GivenName          : Test
lastlogontimestamp : 130756701010406063
Name               : Test User
ObjectClass        : user
ObjectGUID         : ea1d9b92-1851-414b-ac1a-0d3f45ce056b
SamAccountName     : testuser
SID                : S-1-5-21-3143289785-2882062220-334169204-1644
Surname            : User
UserPrincipalName  : testuser@mydomain.com

As you can see 'lastlogontimestamp' is  a code that needs to be converted to something like dd-MMM-yyyy to be usable.

I really do appreciate your patience and input.  Thanks very much for you assistance.
Will SzymkowskiSenior Solution ArchitectCommented:
I think you are mistaken. I am not using LastLogn. LastLogonDate is a powershell attribute that converts the LastLogonTimeStamp into hunman readable date. So LastLogonDate = LastLogonTimeStamp.

If you are not getting any results back try using a different date range to see if that works for you. I have used LastLogonDate with no issues using the script I have provided above.

Will.
Will SzymkowskiSenior Solution ArchitectCommented:
See the screenshots below as i have run the same command and i do get the correct output.
User List with LastLogon and PasswordLastSet
powershell1.JPGResults After script was run
powershell2.JPG
Will.
BigmacMcAuthor Commented:
If I enter the following logic it runs without error and generates a file:

PS C:\Windows\system32> get-aduser -filter * -searchbase "ou=users,dc=mydomain,dc=com" -properties DisplayName,passwordLastSet,LastLogonTimeStamp | ? {(((Get-Date) - ([datetime]::FromFileTime($_.lastlogontimestamp))).TotalDays -gt 105 -or $_.passwordlastset.TotalDays -gt 91)} | select DisplayName,samaccountname,UserPrincipalName,passwordLa
stSet,@{Exp={([datetime]::FromFileTime($_.LastLogonTimeStamp))};label="Last Logon Time Stamp"} | export-csv "e:\admin\logfiles\inactive\users_not_logged_longer_than_90_days.csv" -NoTypeInformation -Delimiter ";"

I get the following output:

"DisplayName";"samaccountname";"UserPrincipalName";"passwordLastSet";"Last Logon Time Stamp"
"Test User1";"TUser1";"TUser1@mydomain.com";;"12/31/1600 7:00:00 PM"
"Test User10";"TUser10";"TUser10@mydomain.com";;"12/31/1600 7:00:00 PM"
"Test User2";"TUser2";"TUser2@mydomain.com";"4/20/2015 3:54:53 PM";"12/31/1600 7:00:00 PM"
"Test User3";"TUser3";"TUser3@mydomain.com";"4/20/2015 3:55:00 PM";"12/31/1600 7:00:00 PM"
"Test User4";"TUser4";"TUser4@mydomain.com";"4/20/2015 3:55:06 PM";"12/31/1600 7:00:00 PM"
"Test User5";"TUser5";"TUser5@mydomain.com";"4/20/2015 3:55:14 PM";"12/31/1600 7:00:00 PM"
"Test User6";"TUser6";"TUser6@mydomain.com";"4/20/2015 3:55:21 PM";"12/31/1600 7:00:00 PM"
"Test User7";"TUser7";"TUser7@mydomain.com";"4/20/2015 3:55:27 PM";"12/31/1600 7:00:00 PM"
"Test User8";"TUser8";"TUser8@mydomain.com";"4/20/2015 3:55:45 PM";"12/31/1600 7:00:00 PM"
"Test User9";"TUser9";"TUser9@mydomain.com";"4/20/2015 3:55:51 PM";"12/31/1600 7:00:00 PM"
"Test User11";"TUser11";"TUser11@mydomain.com";"12/10/2014 11:09:25 AM";"12/10/2014 11:10:03 AM"
"TestMFA0007";"TestMFA0007";"TestMFA0007@mydomain.com";;"12/31/1600 7:00:00 PM"

As you can see the 'PasswordLastSet' should not allow the account to added to the file since the password has been changed in less than 90 days though the lastlogontimestamp is null resulting in the '12/31/1600' date.

With your help I feel like I'm getting close.
BigmacMcAuthor Commented:
Thanks for the post showing the script run. seeing the output helps much.

So let's say scenario is if a user is:

1.  They've never logged in or haven't logged in for more that 105 days AND the password has never been set or is older than 91 days add them to a .csv file to be disabled
2.  They've logged in recently, but password has never been changed or not changed in more than 91 days add them to the .csv file to be disabled
3.  They've never logged in, but password has been reset in less than 91 days then DON'T add them to the file.  From your post Al Elser would be disabled and I want to exclude him from being disabled.  This is a scenario I have with users who don't have interactive or network logins, but use the account and do change the password.
Will SzymkowskiSenior Solution ArchitectCommented:
After looking at my results i was using the gt switch (greater than). If you use my script (most recent one) and change -gt to -lt. You will get your results.

Will.
Will SzymkowskiSenior Solution ArchitectCommented:
Because I am using -30 days you have to reverse the switches. Using negatives if you use -gt (greater than) it will get anything greater than -30 days so 29,28,27 etc. If you use -lt (less than) it will -31,32,33 etc. Give that a shot.

Will.
Will SzymkowskiSenior Solution ArchitectCommented:
So i have just done a complete test (entrie script) and the script works perfectly. This is the exact script i used in my environment.

Import-module activedirectory
$OU = "ou=DomainUsers,dc=a,dc=com"
$OUHold = "ou=disabled,dc=a,dc=com"
$Date = Get-Date
Get-ADUser -Filter * -SearchBase $OU -Properties Name, samAccountName, givenName, sn, LastLogonDate, PasswordLastSet, DistinguishedName |
? { $_.LastLogonDate -lt $Date.AddDays(-30) -or $_.passwordLastSet -lt $Date.AddDays(-60) -and $_.Name -notlike "z-*"  } |
Select samAccountName, givenName, sn, LastLogonDate, passwordLastSet, DistinguishedName |
Sort -Property samaccountname |
Export-csv "c:\output1.csv" -nti
$Import = import-csv "c:\output1.csv"
ForEach ($obj in $Import) {
$obj.samaccountname
$obj.DistinguishedName
Set-ADUser -Identity $obj.samaccountname -Enable $false -Description "Disabled by automated process on $date"
Move-ADObject -Identity $obj.DistinguishedName -TargetPath $OUHold
}
Send-MailMessage -To someone@domain.com -Subject "Disabled user accounts for 90+ day non-use" -Attachments "c:\filename.csv" -SmtpServer server.domain.com

Open in new window


I checked the dates in my script to work with some of the output results. Also see below for results...
Users in the Source OU
mation1.JPGOutput file created
mation2.JPGUsers in the Disabled OU
mation3.JPG
Will.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BigmacMcAuthor Commented:
Will, the solution works perfectly.  Thanks for your patience and I apologize for getting hung-up on lastlogondate vs lastlogontimestamp.
Will SzymkowskiSenior Solution ArchitectCommented:
Not a problem. Glad to have helped!

Will.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.