Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1968
  • Last Modified:

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

Dear Experts,

I need some help in providing me with a Visual Basic script or Power Shell for listing and automating user accounts to be expired within 10 days in Active Directory.

The Listing part:

I want the script to generate a report in excel (XLS) format that includes the following columns:

Display Name
sAmAccountName
e-mail address
Exchange Custom Attribute 1

Actually, i am using a value in the custom attr 1 field, this value is the employee staff number. (I call it also SAP ID because the value is populated by HR SAP app.)


The automation part:

I want the script to send this report (that is generated in the above step) to specific recipients. So, i assume that you will involve Exchange communication in the code.

The Goal:

The purpose and the goal from this request is to generate a weekly report about those accounts to be expired and send them to myself, my colleague and my boss automatically using a Windows sheduled task.

Ooooops! i almost forget ...

It is recommended to run this script against an OU of my choice not against the entire directory since there are specific user accounts located in certain OU that i need to run the script against them.

By the way, i am using Exchange 2k7.

Hope i clarified my point enough and Appreciate your fast and valuable response.

Regards,
A.Y
0
amyassein
Asked:
amyassein
  • 21
  • 16
  • 2
2 Solutions
 
KenMcFCommented:
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

Open in new window

0
 
KenMcFCommented:
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))
0
 
amyasseinAuthor Commented:
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
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
amyasseinAuthor Commented:
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
0
 
KenMcFCommented:
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"
0
 
amyasseinAuthor Commented:
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"
0
 
amyasseinAuthor Commented:
Sorry it should be

At C:\My Scripts & Tools\TestScript.ps1:2 char:157
0
 
KenMcFCommented:
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

Open in new window

0
 
amyasseinAuthor Commented:
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?
0
 
KenMcFCommented:
Does you server require authentication to relay mail through?
0
 
amyasseinAuthor Commented:
Yep
0
 
KenMcFCommented:
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.
0
 
amyasseinAuthor Commented:
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"?

0
 
KenMcFCommented:
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
0
 
KenMcFCommented:
0
 
KenMcFCommented:
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)

Open in new window

0
 
amyasseinAuthor Commented:
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.
0
 
amyasseinAuthor Commented:
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
0
 
KenMcFCommented:
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");
0
 
amyasseinAuthor Commented:
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
0
 
amyasseinAuthor Commented:
Yes i verified all ... no problems.
0
 
KenMcFCommented:
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.
0
 
amyasseinAuthor Commented:
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.
0
 
KenMcFCommented:
Try this one using outlook
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    

   $ol = New-Object -comObject Outlook.Application 
   $mail = $ol.CreateItem(0) 
   $Mail.Recipients.Add("TO@domain.local") 
   $Mail.Subject = "Account Expire" 
   $Mail.Body = "Account Expire" 
   $Mail.Attachments.Add("C:\users.csv") 
   $Mail.Send()

Open in new window

0
 
amyasseinAuthor Commented:
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
0
 
KenMcFCommented:
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.
0
 
amyasseinAuthor Commented:
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
0
 
KenMcFCommented:
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?
0
 
amyasseinAuthor Commented:
No problem ... take your time .

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

Thx for your help.

A.Y
0
 
amyasseinAuthor Commented:
any updates?
0
 
KenMcFCommented:
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
0
 
amyasseinAuthor Commented:
Do you want me to just run this or Add this in the existing script?
0
 
amyasseinAuthor Commented:
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.
0
 
amyasseinAuthor Commented:
Also, can you add additional column to show their expiry dates?

I really appreciate it.
0
 
KenMcFCommented:
Try this for the first line in the script.
get-qaduser -searchroot "OU=USERSOU,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

Open in new window

0
 
amyasseinAuthor Commented:
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.
0
 
inverted_2000Commented:
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!!!
0
 
inverted_2000Commented:
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.
0
 
amyasseinAuthor Commented:
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

Open in new window

0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 21
  • 16
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now