Have you ever needed to determined the uplink port that a particular IP connection uses within your VMware vSphere environment when using IP hash load balancing algorithm, but were discouraged by the time investment required to manually do the math?

Mike Da Costa describes the algorithm and troubleshooting techniques in detail in his blog post “Troubleshooting Network Teaming Problems with IP Hash” but doing the proposed calculations by hand is somewhat tedious due to various conversions and formulas.

Not to fear! I have developed a Microsoft Excel (2013) spreadsheet that will perform the calculations and display the uplink index and per-uplink sums. All you need are source IP address(es), destination IP address(es) and number of uplink ports.

My apologies for Excel. It’s not that I have particular love for Excel or proprietary Microsoft tools, it’s that these are the tools my current employer uses and are immediately available to me. It’s likely the spreadsheet can be recreated or converted to [Libre|Open]Office, if someone was motivated enough to try.

Sweet, just set out to create this myself in Excel when I thought I’d see if someone already had. Thanks for saving me some time!

Glad to have saved you some time!

great work, Just a quick one, does it need to be excel 2013 – I’m using 2010 but the XOR column function just seems to error “#name?” with whatever values I try (the other column formulaes are correctly working)

G: Good question. I didn’t test with anything other than Excel 2013 but it wasn’t intentional to make it 2013-specific either.

Looks like XOR and BITXOR might be 2013 features:

http://office.microsoft.com/en-us/excel-help/xor-function-HA102753272.aspx?CTT=1

http://office.microsoft.com/en-001/excel-help/bitxor-function-HA102753255.aspx

This blog post seems to provide an alternative syntax for XOR that works in 2010: http://chandoo.org/wp/2010/03/02/either-or-formula-in-excel/

But I don’t think that’s bitwise.

Cheers.,, editing online via sky drive works perfect.( excel 2013 ish)

Awesome. I’ve needed this on a few occasions when some servers cant ping a handle of servers, but others servers can ping those ones.

I don’t have Office 2013, but i’ll be sure to remember this for when i do!

Much appreciated.

Thanks for comment, Daunce!