Need poweshell script modified for last 14 days
-
Wednesday, May 09, 2012 6:45 PM
Hello
I'm a poweshell newbie so I need to ask how would I modify this script so that the export will only export the last 14 days from the current date based on the field 'whencreated' so in short we are trying to export all AD accounts created in the last 14 days with only those fields listed in the script below. Any help is appreciated.
Get-ADUser -Filter * -Properties * | Select-Object -Property Name,Description,EmailAddress,Manager,Title,Department,whenCreated | Sort-Object -Property Name | Export-Csv -NoType output.csv
All Replies
-
Wednesday, May 09, 2012 6:50 PM
Get-ADUser -Filter * -Properties * | Where {$_.WhenCreated -ge (Get-Date).AddDays(-14)} | Select-Object -Property Name,Description,EmailAddress,Manager,Title,Department,whenCreated | Sort-Object -Property Name | Export-Csv -NoType output.csvor using param filter
$date = (Get-Date).AddDays(-14) Get-ADUser -Filter {whencreate -ge $date} -Properties * | Select-Object -Property Name,Description,EmailAddress,Manager,Title,Department,whenCreated | Sort-Object -Property Name | Export-Csv -NoType output.csv
-
Wednesday, May 09, 2012 7:01 PMThanks that worked perfectly!
-
Wednesday, May 09, 2012 7:13 PM
I was trying to work on the -Filter parameter, like this:
-Filter "whencreated -gt '$date'"
But I couldn't get it working.
Grant Ward, a.k.a. Bigteddy
-
Wednesday, May 09, 2012 7:45 PM
I was trying to work on the -Filter parameter, like this:
-Filter "whencreated -gt '$date'"
But I couldn't get it working.
Grant Ward, a.k.a. Bigteddy
Get-ADUser -Filter 'whencreated -ge $date' -
Wednesday, May 09, 2012 8:05 PM
This is confusing. I'm used to variables being resolved in double quoted strings, but not in strings quoted with single quotes. Here the PowerShell filter works if it is enclosed in either curly braces or single quotes, but not if the filter is enclosed in double quotes. If instead you use the -LDAPFilter parameter, the filter should be enclosed in double quotes.
Richard Mueller - MVP Directory Services
-
Wednesday, May 09, 2012 8:14 PM
I'm finding it very confusing. It's almost the opposite of WQL, another "language" we need to know.
But I've been trying to use an LDAP filter to do the same thing, but I get no results:
$date = (Get-Date).AddDays(-140) $adDate = [system.management.managementdatetimeconverter]::ToDmtfDateTime($date) Get-ADUser -LDAPFilter "whenCreated>='$adDate'"
The help isn't very clear on how to use the -LDAPFilter, but the above isn't correct, as it returns nothing.Grant Ward, a.k.a. Bigteddy
-
Wednesday, May 09, 2012 8:19 PM
This works:
$date = (Get-Date).AddDays(-140) $adDate = [system.management.managementdatetimeconverter]::ToDmtfDateTime($date) Get-ADUser -LDAPFilter "whenCreated>=$adDate"
So I suppose the moral of the story is when using Get-Ad*, don't put quotes inside quotes, unlike WQL.Grant Ward, a.k.a. Bigteddy
-
Wednesday, May 09, 2012 8:34 PM
I didn't know about ToDmtfDateTime. That's useful. The whenCreated attribute has "GeneralizedTime" format, similar to "20120509131232.0Z" (YYYYMMDDhhmmss.0Z). I'm used to using code to create the string value (not easy).
Just my interpretation, but with -LDAPFilter, PowerShell doesn't know what's going on so it passes the string to AD. In a double quote quoted string, variables are not resolved and single quotes are part of the string, so AD sees
whenCreated>='20120509123412.0Z'
and the single quotes make no sense. With the -Filter parameter, the help only mentions using curly braces. I have no idea why single quote quoted strings work, but if you enclose it in double quotes PowerShell doesn't interpret it as a filter (maybe it's just a string, which is invalid).
Perhaps -LDAPFilter only accepts strings, which the cmdlet passes to AD, while -Filter only accepts something it interprets as a PowerShell filter, which the AD cmdlet then converts into the equivalent LDAP filter syntax. I know in VBScript if you use SQL syntax, the ADO provider converts the filter into LDAP syntax under the covers.
Richard Mueller - MVP Directory Services
- Edited by Richard MuellerMVP Wednesday, May 09, 2012 8:35 PM typo
- Edited by Richard MuellerMVP Wednesday, May 09, 2012 8:36 PM typo
-
Wednesday, May 09, 2012 8:49 PM
This is really crazy. Consider the following two queries. Both work, but just try changing the syntax on either one, and you will break it!
Get-ADUser -LDAPFilter '(name=Admin*)' Get-ADUser -LDAPFilter "whencreated>=$adDate"
Grant Ward, a.k.a. Bigteddy
-
Wednesday, May 09, 2012 8:58 PM
Both of the filters are strings, so that's OK. The only strange feature is the lack of parentheses in the second example. I didn't know that would work. The second filter must be enclosed in double quotes or the variable $adDate won't get resolved. I'm sure the first filter will work if in double quotes. What breaks?
Richard Mueller - MVP Directory Services
-
Wednesday, May 09, 2012 9:03 PM
Both of the filters are strings, so that's OK. The only strange feature is the lack of parentheses in the second example. I didn't know that would work. The second filter must be enclosed in double quotes or the variable $adDate won't get resolved. I'm sure the first filter will work if in double quotes. What breaks?
Richard Mueller - MVP Directory Services
1. Putting parentheses in the second example
2. Taking away the parentheses in the first example.
Changing to single or double quotes makes no difference.
Grant Ward, a.k.a. Bigteddy
-
Wednesday, May 09, 2012 9:26 PM
OK, I'm lost. First, $adDate is not in the GeneralizedTime format I expected. Rather, it is similar to "20120425162433.474745-360". Clearly, the parentheses make PowerShell handle the filter differently. I'm used to LDAP syntax filters always requiring them.
From my tests:
$Date = "20120501000000.0Z"
# This works:
Get-ADUser -LDAPFilter "(whenCreated>=$Date)"
# This does NOT work:
Get-ADUser -LDAPFilter '(whenCreated>=$Date)'
# This works:
Get-ADUser -LDAPFilter "whenCreated>=$Date"
# This works:
Get-ADUser -LDAPFilter "(name=Admin*)"
# This does NOT work:
Get-ADUser -LDAPFilter "name=Admin*"
$Date = (Get-Date).AddDays(-14)
$ADDate = [system.management.managementdatetimeconverter]::ToDmtfDateTime($Date)
# $ADDate is similar to 20120425162433.474745-360.
$ADDate
# This does NOT work:
Get-ADUser -LDAPFilter "(whenCreated>=$ADDate)"
# This works:
Get-ADUser -LDAPFilter "whenCreated>=$ADDate"
-----
Richard Mueller - MVP Directory Services
.png)
