Solved

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

Posted on 2011-02-27
39
1,938 Views
Last Modified: 2012-05-11
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
Comment
Question by:amyassein
  • 21
  • 16
  • 2
39 Comments
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
Sorry it should be

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

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Does you server require authentication to relay mail through?
0
 
LVL 1

Author Comment

by:amyassein
Comment Utility
Yep
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
Yes i verified all ... no problems.
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Accepted Solution

by:
KenMcF earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
No problem ... take your time .

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

Thx for your help.

A.Y
0
 
LVL 1

Author Comment

by:amyassein
Comment Utility
any updates?
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
Do you want me to just run this or Add this in the existing script?
0
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
Also, can you add additional column to show their expiry dates?

I really appreciate it.
0
 
LVL 27

Assisted Solution

by:KenMcF
KenMcF earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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
 
LVL 2

Expert Comment

by:inverted_2000
Comment Utility
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
 
LVL 2

Expert Comment

by:inverted_2000
Comment Utility
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
 
LVL 1

Author Comment

by:amyassein
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now