amyassein
asked on
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
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
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))
So replace
-AccountExpiresAfter ((Get-date).adddays(10))
with this
-AccountExpiresBefore ((Get-date).adddays(10))
ASKER
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
I'll give it a try and will let you know the status.
Thanks again.
A.Y
ASKER
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 : PositionalParameterNotFoun d,Microsof t.PowerShe ll
.Commands.SendMailMessage
From my side, i double checked the SMTP server in your code and it is properly configured.
Thanks
A.Y
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 : PositionalParameterNotFoun
.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"
In your send-mailmessage you will need to put the subject in quotes like this
-subject "10Days Expiry"
ASKER
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,Mic rosoft.Pow erShell.Co mm
ands.SendMailMessage
I put quotes also around the attachment "c:\users.csv"
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,Mic
ands.SendMailMessage
I put quotes also around the attachment "c:\users.csv"
ASKER
Sorry it should be
At C:\My Scripts & Tools\TestScript.ps1:2 char:157
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
ASKER
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?
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?
ASKER
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.
ASKER
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"?
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
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)
ASKER
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.
If you find another way, i'll be glad.
ASKER
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
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@Do main.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.NetworkCredenti al("userna me", "password");
These are the lines that will need changed
$mailmessage.From = ("From@domain.local")
$mailmessage.To.add("TO@Do
$mailmessage.Subject = "Users account Expire"
$mailmessage.Body = "Notification of user accounts that will expire"
$SMTPServer = "smtpserver.domain.local"
$SMTPClient.Credentials = New-Object System.Net.NetworkCredenti
ASKER
Look, i have a vbscript that send an email without a problem. It may helps.
Sub SendEmailWithOutlook(strSu bject, 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.Appl ication")
objOutlook.Visible = True
boolOutlookOpen = False
End If
Err.Clear
Set objNameSpace = objOutlook.GetNamespace("m api")
Set objMailItem = objOutlook.CreateItem(olMa ilItem)
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
Sub SendEmailWithOutlook(strSu
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.Appl
objOutlook.Visible = True
boolOutlookOpen = False
End If
Err.Clear
Set objNameSpace = objOutlook.GetNamespace("m
Set objMailItem = objOutlook.CreateItem(olMa
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
ASKER
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.
ASKER
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.
Yes if you can let the outlook to send the mail, it will be good.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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-000000000 046} 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,Micro soft.Power Shell.Comm an
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
New-Object : Retrieving the COM class factory for component with CLSID {0006F03
A-0000-0000-C000-000000000
At C:\My Scripts & Tools\TestScript.ps1:3 char:20
+ $ol = New-Object <<<< -comObject Outlook.Application
+ CategoryInfo : ResourceUnavailable: (:) [New-Object], COMExcept
ion
+ FullyQualifiedErrorId : NoCOMClassIdentified,Micro
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.
ASKER
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(strSu bject, 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.Appl ication")
objOutlook.Visible = True
boolOutlookOpen = False
End If
Err.Clear
Set objNameSpace = objOutlook.GetNamespace("m api")
Set objMailItem = objOutlook.CreateItem(olMa ilItem)
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
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(strSu
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.Appl
objOutlook.Visible = True
boolOutlookOpen = False
End If
Err.Clear
Set objNameSpace = objOutlook.GetNamespace("m
Set objMailItem = objOutlook.CreateItem(olMa
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?
ASKER
No problem ... take your time .
Also, don't forget to fix the Listing part ;)
Thx for your help.
A.Y
Also, don't forget to fix the Listing part ;)
Thx for your help.
A.Y
ASKER
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
$ol = New-Object -com outlook.application
$ol.version
ASKER
Do you want me to just run this or Add this in the existing script?
ASKER
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.
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.
ASKER
Also, can you add additional column to show their expiry dates?
I really appreciate it.
I really appreciate it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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 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.
What's up with that? I know at least my own account has about 30 days left.
ASKER
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.
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
This has not been tested.
http://www.quest.com/powershell/activeroles-server.aspx
Open in new window