Tip: Using Excel To Speed Up Your PowerShell Scripting On Exchange

If you’ve run across a scenario where you need to create/edit/remove more than a few Exchange objects at a time, your best bet is to script the fix using PowerShell.  The biggest problem you’ll come across though is, “I have this list of users, so how can I pre-populate a PowerShell script with the correct variables?”

Here’s the quick-and-dirty method:

1) Fire up Excel

2) Arrange your columns in such a way that you can simply paste in columns from text files or other spreadsheets with the correct first name and last name values—as well as any other variables you might need—and have those columns fit into the general syntax of what you’re trying to script… here’s an example:


    As we can see above, I’m running the following command for each user on an individual line:

    New-MailContact –Name “Billy Bob (Mozambique)” –ExternalEMailAddress billybob@somewhere.mz

    I can also easily paste in the list of first & last names, as well as copy the standard contents of columns A and E all the way down to the end, as shown below:


    You can even get fancy with the cell values & tabs… by way of example, I’ve broken down my ultimate goal into three discrete steps, each of which is its own tab:


    …and on the additional tabs, just set the values for the First Name and Last Name columns based on the value in the first tab using a formula like the xample below:


3) With your spreadsheet complete, do a Ctrl-A (Select All) and paste the contents into Notepad:


4) Highlight one of the tab characters and hit Ctrl-C (or right-click and Copy):


5) On the Edit menu, go to Replace:


6) Paste in the tab character you copied earlier and click the Replace All button:


7) Now that the text document is correctly formatted and doesn’t have any additional spaces or tabs, you should just be able to paste it into PowerShell on the Exchange server directly:


Tip: I’d recommend only pasting in a few lines at a time to start with just to make sure your syntax is correct.

Hope this saves you some time!  :-)

How To: Troubleshoot Exchange 2007 Queues Being Overrun With "Undeliverable" Messages

Like any good MSP… or maybe unlike many MSPs? ;-)… we have a monitor set placed on Exchange servers for when their queues get “out of hand,” i.e. there are too many outbound mail queues –or- the queues that are present are too large and have too many messages bound for a single destination.

Sometimes, when this alarm trips, it’s hard to know where to begin.  Did User McUserton decide to send out a blast e-mail to thousands of recipients… again?  Has the SMTP server been compromised via SMTP AUTH attack?  Is the outbound intermediary server down?  Has the server been blacklisted?  There are many scenarios here, of which this is just one; however, the troubleshooting steps here are a good starting point to see what you’re dealing with in most situations.

For this example, I was asked to investigate the presence of many outbound queues consisting of a few messages each—all of which are from a blank sender and have a subject line beginning with “Undeliverable”:

1) Hop on the Exchange server and open the Exchange Management Console

2) In the Microsoft Exchange tree, go to Toolbox and double-click on Queue Viewer:


3) Notice the characteristics of the scenario I outlined earlier (pictured below)… but even if your scenario doesn’t match this one, you can continue investigation… just pick a queue that looks the most “interesting” (e.g. has the most messages or seems the most backed up):


4) When you double-click on the queue, you’ll see a message or two… double-click on one of them to look at its properties:


    Things to notice here:

    Subject: Undeliverable: [Spammy subject line]

    From Address: <>

    Source IP:

    Last Error: [Delivery delay, DNS query failed, other failures]

5) In the example above, I’ve highlighted and copied “Watches, Luxury Items and Handbags!” to the clipboard, leaving out the “Undeliverable” bit… and now, back in the Exchange Management Console, go to the Toolbox > Message Tracking:


6) Un-check all of the checkboxes except for “Subject,” paste in the subject line you just copied, and hit Next:


7) Now… what you’ll see here is too much to put in a screenshot… here’s the trick… read it like a narrative, from left-to-right and top-to-bottom.  This takes practice and patience… hang in there, buddy!


    Here’s an example of what reading this narrative might sound like, with each line being a bullet point:

·         On June 2nd, 2011, a connection was logged from IP and had a from address of <SpammyMcSpammerson@ILikeTacos.com> and a subject of “Watches, Luxury Items and Handbags!”, and this message was sent to <CEODistributionList@TheClient.com> and the result of this connection was “OK”

·         At the same date and a second later, [ExchangeServerName] tried to figure out who the <CEODistributionList@TheClient.com> distribution list should go to

·         At the same date and a second after that, the resolver figured out that the distribution list contains <BeckySue@TheClient.com> and <BillyBob@TheClient.com>

o    A second after that, the STOREDRV process attempts to deliver to <BeckySue@TheClient.com> and succeeds

o    At the same time, the STOREDRV process attempts to deliver to <BillyBob@TheClient.com>, but an error is logged saying that the recipient doesn’t exist

§  The [ExchangeServerName] then tries to send a message from <> (blank) to <SpammyMcSpammerson@ILikeTacos.com> with the subject “Undeliverable: Watches, Luxury Items and Handbags!”

§  A second after that, the transport driver says the recipient server said “4.4.0 – Unknown recipient” and rejected the message, so the transport driver put the queue into a retry state

§  Rinse, repeat, and presto change-o, you now have hundreds of queues stuck in a retry state!  Wooooo!


    Again, take your time and read slowly :-)

    Tip: You can select a message row and hit “Next” again to search for only that message ID.

          This will save you a lot of reading, especially if there are multiple messages with the same subject!

8) In my example above, we can see that the message originally hit the <CEODistributionList@TheClient.com> and then broke out to go to <BeckySue@TheClient.com> and <BillyBob@TheClient.com>, where the message to Billy Bob bounced (Billy Bob got a job down at Initech as one of the Bobs)… so two questions arise:

    Question 1: Why is Billy Bob still on the CEO’s distribution list?  We fired that guy!

    Solution 1: Remove Billy Bob from the CEO’s distribution list!  Disable his account!  Wipe hands on pants!

    Question 2: How in the heck did such an obvious piece of spam get through the spam filter?!?!

    Solution 2:  Check your spam filter!  In this case, we checked Postini… see if you can spot the problems:


Please use this knowledge for good and not for evil :-)

Fix: Configuring SonicPoint APs on a SonicWALL TZ on a **Shared Interface**

Here’s 3 hours of mine and another Engineer’s lives that we’ll never get back… so if you do run across this configuration, this should save you some time.  Here’s the scenario:

You have:

1 x SonicWALL TZ210

4 x SonicPoint wireless access points

1 x PoE switch, shared with both the SonicPoints and a few wired LAN clients


Due to having been completely locked out of all interfaces and all protocols by the previous IT company (morons) and because we didn’t have a console cable anywhere nearby (d’oh!), we had to factory reset the SonicWALL TZ210.  It was only at that point that we realized that the SonicWALL TZ210 also had four (4) SonicPoints that used to be bound to it (thanks to correct labeling in the MySonicWALL portal).  We attempted to get the TZ210 to recognize the SonicPoints (we even factory reset a SonicPoint), but they never showed up in the web UI:

So, here’s your problem… the SonicPoints will not talk to the TZ210 unless they are plugged into an interface designated as a WLAN (wireless LAN) interface.

If you were setting this up from scratch, you would want to design your network in such a way that the SonicPoints were on one PoE switch attached to an X2-X6 interface, and the LAN clients were on a different non-PoE switch connected to the X0 (LAN) interface.  You would then designate the interface to which your PoE switch and SonicPoints are connected as being in the WLAN zone.  Here’s a good site documenting that process: http://www.brandontek.com/networking/solution-to-your-sonicpoint-wlan-woes/

…but, since we didn’t have two switches, we were up a creek.  Oh, and did I mention that putting a SonicPoint into standalone mode is not supported by SonicWALL?  Major bummer, dude!  So, these were our choices:

If we plugged the PoE switch with the SonicPoints and the wired LAN clients to X0, the SonicPoints would not be recognized.

If we plugged the PoE switch with the SonicPoints into an X2-X6 interface which was designated as a WLAN, then the wired LAN clients would not be able to get out of that interface to the Internet.

One SonicWALL case and one undocumented setting later (correction: the Murphy is strong today (Friday the 13th?)… see the last paragraph for the link to the KB article), it’s working.  Here’s how:

1) Log into the TZ210, and, once logged in, substitute main.html in the address bar for diag.html, which brings you to this page:


2) Click the “Internal Settings” button, scroll down to the Wireless Settings section, and check the box for “Enable local wireless zone traffic to bypass gateway firewalling,” and then be sure to scroll back up and hit Apply:


    Don’t forget:


3) Hit the “Close” button on the diag.html page, which then takes you back to the normal interface… go to Network > Zones and edit your WLAN zone to match the following settings:


    Now, on the Wireless tab, you’ll have a new checkbox:



    Don’t forget:


4) Now, change an interface (in this case, X2) to the WLAN zone, and plug the uplink from your PoE switch (which, again, has the SonicPoints and some wired LAN clients attached) into said interface you just configured as follows:


…and Murphy’s law states that as soon as I put this together, I’d find a KB article that SonicWALL didn’t mention, even when I’d asked tech support, “Is there some sort of article or walkthrough I can follow?”  http://www.fuzeqna.com/sonicwallkb/consumer/kbdetail.asp?kbid=8334&formaction=faqalert

Anyway, their KB article doesn’t actually bridge the new interface to the X0 interface… mine does… and it still works.  Nyah.

Hope this helps save you some time when configuring non-optimal SonicWALL-based networks :-)