How do i list and automate user accounts to be expired within 10 days?

I would use the quest ad cmdlets and powershell v2
This has not been tested.

http://www.quest.com/powershell/activeroles-server.aspx

get-qaduser -searchroot "OU=USERSOU,DC=DOMAIN,DC=LOCAL -AccountExpiresAfter ((Get-date).adddays(10)) -includedproperties Extensionattribute1 | Select Name, Samaccountname, email, Extensionattribute1 | Export-csv c:\users.csv
Send-mailmessage -to to@domain.com -from from@doamin.com -subject Users -smtpserver server.domain.com -attachement c:\users.csv

Select allOpen in new window

I think I made a mistake, you will want to use -AccountExpiresBefore instead of -accountexpiresafter
So replace
-AccountExpiresAfter ((Get-date).adddays(10))

with this

-AccountExpiresBefore ((Get-date).adddays(10))

Thanks KenMcF for your quick and valuable reply.

I'll give it a try and will let you know the status.

Thanks again.

A.Y

Hi KenMcF,

I executed your script but there are two issues as mention below:

1- The information in the report (user.csv) is not accurate as it lists accounts that are already expired.

2- I receive this error in PS when it tries to send an email i think:

Send-MailMessage : A positional parameter cannot be found that accepts argument
 'days'.
At C:\My Scripts & Tools\TestScript.ps1:2 char:17
+ Send-mailmessage <<<<  -to amyassein@local.com -from amyassein@local.com
 -subject 10Days Expiry -smtpserver exchsrv.local.com -attache
ment c:\users.csv
    + CategoryInfo          : InvalidArgument: (:) [Send-MailMessage], Paramet
   erBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell
   .Commands.SendMailMessage


From my side, i double checked the SMTP server in your code and it is properly configured.

Thanks

A.Y

Let me test in my lab, my have the syntax wrong.

In your send-mailmessage you will need to put the subject in quotes like this
-subject "10Days Expiry"

Hmmmm interesting ..

Ok i did put the quotes and i got this new error:

Send-MailMessage : A parameter cannot be found that matches parameter name 'attachement'.
At C:\My Scripts & Tools\TestScript.ps1:2 char:17
+ Send-mailmessage <<<<  -to amyassein@local.com -from amyassein@local.com
 -subject "10Days Expiry" -smtpserver exchsrv.local.com -attache
ment c:\users.csv
    + CategoryInfo          : InvalidArgument: (:) [Send-MailMessage], Paramet
   erBindingException
  + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Comm
ands.SendMailMessage

I put quotes also around the attachment "c:\users.csv"

Sorry it should be

At C:\My Scripts & Tools\TestScript.ps1:2 char:157

I had a typo it should be -Attachment, Try this one

get-qaduser -searchroot "OU=USERSOU,DC=DOMAIN,DC=LOCAL -AccountExpiresAfter (Get-Date) -AccountExpiresBefore ((Get-date).adddays(10)) -includedproperties Extensionattribute1 | Select Name, Samaccountname, email, Extensionattribute1 | Export-csv c:\users.csv  
Send-mailmessage -to to@domain.com -from from@doamin.com -subject "Users That will expire" -smtpserver server.domain.com -Attachment c:\users.csv

Select allOpen in new window

Ahhh!! ok it works but it is unable to connect to remote server (Exchange in our case) :(

While in VBScript i mentioned the same server and it works fine.

Do i need to do something in PS in order to connect to Exchange?

Does you server require authentication to relay mail through?

Yep

You can put -credential in the send-mailmessage cmdlet. Not sure if you can specify a password but if you can it would have to be in clear text in the script. It might be better to allow the IP of the server you are running this from to relay.

The switch doesn't allow me to put password, it is just a username as below.

local.com\amyassein

But it prompted me for the password and when i typed it, still says unable to connect.

What did you mean by "It might be better to allow the IP of the server you are running this from to relay"?

You can create a new recieive connector and only allow the IP of the server to relay through it without authentication. Let me look into another way without using the send-mailmessage cmdlet

forgot the link

http://msexchangeteam.com/archive/2006/12/28/432013.aspx

Try this one

get-qaduser -searchroot "OU=USERSOU,DC=DOMAIN,DC=LOCAL" -AccountExpiresAfter ((Get-date).adddays(10)) -includedproperties Extensionattribute1 | Select Name, Samaccountname, email, Extensionattribute1 | Export-csv c:\users.csv  
$emailattachment = "c:\users.csv"
$attachment = New-Object System.Net.Mail.Attachment($emailattachment, 'text/plain')
$mailmessage = New-Object system.net.mail.mailmessage 
$mailmessage.From = ("From@domain.local")
$mailmessage.To.add("TO@Domain.local") 
$mailmessage.Subject = "Users account Expire" 
$mailmessage.Body = "Notification of user accounts that will expire" 
$mailmessage.Attachments.Add($attachment)
$SMTPServer = "smtpserver.domain.local" 
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25) 
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("username", "password"); 
$SMTPClient.Send($mailmessage)

Select allOpen in new window

Actually, i don't have full access on Exchange servers, it is only managed by the Exchange guys and it is a long process cuz it requires approvals, MoC, ... etc equals headache.

If you find another way, i'll be glad.

I got this one:

Exception calling "Send" with "1" argument(s): "Failure sending mail."
At C:\My Scripts & Tools\TestScript.ps1:13 char:17
+ $SMTPClient.Send <<<< ($mailmessage)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Verify you changed all the fields

These are the lines that will need changed

$mailmessage.From = ("From@domain.local")
$mailmessage.To.add("TO@Domain.local")
$mailmessage.Subject = "Users account Expire"
$mailmessage.Body = "Notification of user accounts that will expire"
$SMTPServer = "smtpserver.domain.local"
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("username", "password");

Look, i have a vbscript that send an email without a problem. It may helps.

Sub SendEmailWithOutlook(strSubject, strTo, strBody, strAttachment)
      Const olMailItem = 0
      Const olFormatPlain = 1
      
    On Error Resume Next
    boolOutlookOpen = True
    Set objOutlook = GetObject(, "Outlook.Application")
    If objOutlook Is Nothing Then
            Set objOutlook = CreateObject("Outlook.Application")
            objOutlook.Visible = True
        boolOutlookOpen = False
    End If
    Err.Clear
    Set objNameSpace = objOutlook.GetNamespace("mapi")
    Set objMailItem = objOutlook.CreateItem(olMailItem)
    With objMailItem
        .BodyFormat = olFormatPlain
        .To = strTo
        .Subject = strSubject
        .Body = strBody
        If strAttachment <> "" Then .Attachments.Add strAttachment
        .Send
       
        If Err.Number <> 0 Then MsgBox "Failed to send message. Error " & Err.Number & ": " & Err.Description
    End With
    If Err.Number = 0 Then
        objOutlook.sendandreceive False
    End If
    If Not boolOutlookOpen Then
        objOutlook.Quit
    End If
    Err.Clear
    On Error GoTo 0
End Sub

Yes i verified all ... no problems.

your vbscript is using outlook to send not smtp. I will have to look into see how powershell can do that. You should also verfiy you are using the correct SMTP server with your exchange team.

Don't Worry, i am using the correct SMTP server, this is the server that my outlook connects to.

Yes if you can let the outlook to send the mail, it will be good.

Ooops! i got this :( ... BTW, mine is outlook 2k7

New-Object : Retrieving the COM class factory for component with CLSID {0006F03
A-0000-0000-C000-000000000046} failed due to the following error: 80080005.
At C:\My Scripts & Tools\TestScript.ps1:3 char:20
+    $ol = New-Object <<<<  -comObject Outlook.Application
    + CategoryInfo          : ResourceUnavailable: (:) [New-Object], COMExcept
   ion
    + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Comman
   ds.NewObjectCommand

You cannot call a method on a null-valued expression.
At C:\My Scripts & Tools\TestScript.ps1:4 char:26
+    $mail = $ol.CreateItem <<<< (0)
    + CategoryInfo          : InvalidOperation: (CreateItem:String) [], Runtim
   eException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\My Scripts & Tools\TestScript.ps1:5 char:24
+    $Mail.Recipients.Add <<<< ("amyassein@local.com")
    + CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeExcept
   ion
    + FullyQualifiedErrorId : InvokeMethodOnNull

Property 'Subject' cannot be found on this object; make sure it exists and is s
ettable.
At C:\My Scripts & Tools\TestScript.ps1:6 char:10
+    $Mail. <<<< Subject = "Account Expire"
    + CategoryInfo          : InvalidOperation: (Subject:String) [], RuntimeEx
   ception
    + FullyQualifiedErrorId : PropertyNotFound

Property 'Body' cannot be found on this object; make sure it exists and is sett
able.
At C:\My Scripts & Tools\TestScript.ps1:7 char:10
+    $Mail. <<<< Body = "Account Expire"
    + CategoryInfo          : InvalidOperation: (Body:String) [], RuntimeExcep
   tion
    + FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At C:\My Scripts & Tools\TestScript.ps1:8 char:25
+    $Mail.Attachments.Add <<<< ("C:\users.csv")
    + CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeExcept
   ion
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\My Scripts & Tools\TestScript.ps1:9 char:14
+    $Mail.Send <<<< ()
    + CategoryInfo          : InvalidOperation: (Send:String) [], RuntimeExcep
   tion
    + FullyQualifiedErrorId : InvokeMethodOnNull

Can you post the script you are running. I have tested these all and they work. I tested the last one on outlook 2010 and 2007.

It is the same code that i posted above but add these lines before it.

strSubject = "Group Members Report"
'strFrom = "amyassein@local.com"
strTo = "amyassein@local.com"
strBody = "Please see the attached file"
'strServer = "exchsrv.local.com"
strAttachment = strFilePath & ".xls"
'SendEmail strSubject, strFrom, strTo, strBody, strServer, strAttachment
SendEmailWithOutlook strSubject, strTo, strBody, strAttachment

WScript.Echo "This Script is now complete"

Sub SendEmailWithOutlook(strSubject, strTo, strBody, strAttachment)
      Const olMailItem = 0
      Const olFormatPlain = 1
     
    On Error Resume Next
    boolOutlookOpen = True
    Set objOutlook = GetObject(, "Outlook.Application")
    If objOutlook Is Nothing Then
            Set objOutlook = CreateObject("Outlook.Application")
            objOutlook.Visible = True
        boolOutlookOpen = False
    End If
    Err.Clear
    Set objNameSpace = objOutlook.GetNamespace("mapi")
    Set objMailItem = objOutlook.CreateItem(olMailItem)
    With objMailItem
        .BodyFormat = olFormatPlain
        .To = strTo
        .Subject = strSubject
        .Body = strBody
        If strAttachment <> "" Then .Attachments.Add strAttachment
        .Send
       
        If Err.Number <> 0 Then MsgBox "Failed to send message. Error " & Err.Number & ": " & Err.Description
    End With
    If Err.Number = 0 Then
        objOutlook.sendandreceive False
    End If
    If Not boolOutlookOpen Then
        objOutlook.Quit
    End If
    Err.Clear
    On Error GoTo 0
End Sub

I will do some additional testing tonight. I am not sure why it is not working for you. Are you abel to test on another computer?

No problem ... take your time .

Also, don't forget to fix the Listing part ;)

Thx for your help.

A.Y

any updates?

I have tested this on two computers, one with outlook 2007 and another with 2010. They both worked. Can you just run this and see if you get the same error

$ol = New-Object -com outlook.application
$ol.version

Do you want me to just run this or Add this in the existing script?

My friend, it is working Perfect now.

Can you fix the Listing part please? the report still shows few expired accounts.

I need the report to list only the accounts that are going to expire within 10 days.

Also, can you add additional column to show their expiry dates?

I really appreciate it.

It is working perfect now my friend.

Thanks for your valuable effort and keep up the good work.

This question is solved completely and closed.

I'd like this without the email part...but I can't get it to run.
I have the Quest CMDLETS installed...but I always receive errors around the -searchroot area.

I'm trying to look at all user accounts regardless of the OU.  Can this be done and if so please provide an example.

Thanks!!!

I fixed the OU thing...it was my typing...but no matter how many days I put...even 115 days, the output is blank.
What's up with that?  I know at least my own account has about 30 days left.

Use only the first line in the code. This will let you use the script without the email part.

If you want to search the entire directory regardless of OU, just remove the OU part from the line and use DC directly. I attached an example.

Not sure what you meant by 115 days? ... this script for the accounts that are going to be expired after certain days. You can modify the (Get-date).adddays() method to include the number of days.

get-qaduser -searchroot "DC=DOMAIN,DC=LOCAL" -AccountExpiresAfter (Get-Date) -AccountExpiresBefore ((Get-date).adddays(10)) -includedproperties Extensionattribute1 | Select Name, Samaccountname, email, Extensionattribute1, AccountExpires | Export-csv c:\users.csv

Select allOpen in new window