So I need to be able to query all users in exchange/AD to see what city they have listed and have that ran against our custom address lists in exchange 2007 to see if we need to create some new lists to accomodate these missisng cities. Can it be done in PS? If so, thanks for the help in advanced. Oh yeah I need the results to be emailed...so I can batch schedule it.

How well do the names of your address lists match up to the City field of the user objects?[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

And what version of Exchange are you running?[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

All the exchange 2007 address lists are recipient filter queries that include cities we add to them. Our company aquires a lot of companies each year and as new users get added to AD there is potential for new cities that are added in that we don't have custom address lists for. We want to know when a new city shows up in AD so we can create an address list for it.

Try this: $missing = @() $addr_lists = get-addresslist |% {$_.name} get-user -resultsize unlimited |% {if ($addr_lists -notcontains $_.city){$missing += $_.city}} $missing $missing | out-file missing_cities.txt [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

it shows up with a lot blank entries and duplicates as if its reporting out for each user. So it looks pretty close, but would be nice to go to csv and not have any blanks or dupes. is that possible?

it shows up with a lot blank entries and duplicates as if its reporting out for each user. So it looks pretty close, but would be nice to go to csv and not have any blanks or dupes. is that possible? I think that's do-able. Try this: $missing = @() $addr_lists = get-addresslist |% {$_.name} get-user -resultsize unlimited |% {if ($addr_lists -notcontains $_.city){$missing += $_.city}} $missing = $missing |? {$_} | select -unique $missing $missing | out-file missing_cities.txt[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

That's great! works well especially since the city name and the name of the address list are the same. However I have produce the same thing for the "Office" of each user, except the office name in AD does not equal the name of the address list. For example John has office listed as Atlanta, and the corresponding custom address list for the city works great, but the custom address list for the office reads "Company - Atlanta" and is created with a recipient filter that works off the company name and office name. Am I in a bind for trying to do the same search for offices if that info is only part of the address list name? Is there a way for the script to say "contains the word"? I realize you answered my first question, and I wil mark it as answered and can create a new forum post if need be for the office question. thanks

Is the city name always at the end of the Office entry string?[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

For the most part. A few don't but they can be changed to if need be.

If the city is always at the end of the office string, I think this will work. If it's going to be a arbitrary match anywhere in the string, it wlil be more comlicated, but do-able. $missing = @() $addr_lists = get-addresslist |% {$_.name} get-user -resultsize unlimited |% {if ($addr_lists -notcontains $_.office.split()[-1]){$missing += $_.office}} $missing = $missing |? {$_} | select -unique $missing $missing | out-file missing_office.txt[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

Doesn't appear to be working. It came back with results that had valid address lists. For example in the missing_office.txt file it had Atlanta show up even though we have a list named: Company - Atlanta Here is the filter for that list: ((RecipientType -eq 'UserMailbox') -and (((Company -eq 'Company') -and (Office -eq 'Atlanta'))))

Anyway to have it query based on the (office -eq 'Atlanta') piece of the filter instead of the name of the list?

I misunderstood your data. Is this any closer? $missing = @() $addr_lists = get-addresslist |% {$_.name} get-user -resultsize unlimited |% { $off_comp = $_.company + " - " + $_.office if ($addr_lists -notcontains $off_comp){$missing += $off_comp} } $missing = $missing |? {$_} | select -unique $missing $missing | out-file missing_office.txt [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

Still doesn't appear to be working...it still list Atlanta and some other ones that do indeed exist as Company - Office. Note there is a space between the dash. So no way to have it query just against the (office -eq ) part of the recipient filter?

There is a way. This is an exact match, so any embedded whitespace before or after one of the fields will throw it off. Will there only be one Office address list that would match a given City, or does it need to match both Company and City?[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

If it's listing just "Atlanta" it should mean that you have one or more users that have "Atlanta" in the office field, and the Company field is either blank or whitespace. [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

I see, yes most likely we have a user with Office = Atlanta yet is part of CompanyB instead of CompanyA, and we don't have an address list with the combo of companyB+office. Is there way to show which combo is coming up missing....ie have it report CompanyB+Atlanta combo as missing?

If CompanyB + Atlanta is missing, it should show up on the list. The output we're seeing right now seems to indicate that somewhere there is one or more "nothing + Atlanta" - ie, they have Atlanta in the Office field, but the Company field isn't filled in at all, so the combination of Company + Office just comes up "Atlanta"[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

How can I get the output to display comanyb + atlanta in the text file together? Since it's not a valid address list and is missing, I'd like to see that combo in the report, but the report is just showing cities.

It should be in the list. Here's the script, with some comments: $missing = @() $addr_lists = get-addresslist |% {$_.name} get-user -resultsize unlimited |% { #for each user, creat $off_comp from their company field and office field, separated by "space dash space" $off_comp = $_.company + " - " + $_.office #Check to see of the list of address list names contains that comibination. If not, add that combination to $missing. if ($addr_lists -notcontains $off_comp){$missing += $off_comp} } #remove blank entries from $missing, and de-dupe $missing = $missing |? {$_} | select -unique $missing $missing | out-file missing_office.txt [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

Awesome. nice work! really appreciate the help.

No problem. I may not get to mess with the other question until tomorrow, but someone else may be along with the answer before then.[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

how do I send this report in an email? I tried using send-mailmessage but had some issues.

What's your code look like, and what kind of issues are you having?[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "