Solved

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

Posted on 2011-02-27
39
1,954 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 16
  • 2
39 Comments
 
LVL 27

Expert Comment

by:KenMcF
ID: 34991536
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
ID: 34991559
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
ID: 34991667
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:amyassein
ID: 34991738
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
ID: 34991757
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
ID: 34991799
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
ID: 34991801
Sorry it should be

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

Expert Comment

by:KenMcF
ID: 34991814
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
ID: 34991836
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
ID: 34991842
Does you server require authentication to relay mail through?
0
 
LVL 1

Author Comment

by:amyassein
ID: 34991850
Yep
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 34991874
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
ID: 34991900
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
ID: 34991915
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
ID: 34991916
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 34991993
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
ID: 34991996
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
ID: 34992021
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
ID: 34992030
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
 
LVL 1

Author Comment

by:amyassein
ID: 34992031
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
ID: 34992033
Yes i verified all ... no problems.
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 34992045
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
ID: 34992065
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
ID: 34992073
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
ID: 34992117
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
ID: 34992131
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
ID: 34992177
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
ID: 34992239
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
ID: 34992270
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
ID: 35015192
any updates?
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 35016458
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
ID: 35053516
Do you want me to just run this or Add this in the existing script?
0
 
LVL 1

Author Comment

by:amyassein
ID: 35053559
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
ID: 35053603
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
ID: 35055149
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
ID: 35135565
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
ID: 35932333
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
ID: 35932441
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
ID: 35950404
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add a second combobox VBA Excel 8 34
DNS forwarders "unable to resolve" 1 62
using vb script 5 51
Get-ADComputer assistance (Computers joined to domain) 2 17
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article outlines the process to identify and resolve account lockout in an Active Directory environment.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 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