Scripting VNX LUN creation with Excel

I'm currently working on putting the finishing touches on the design for a VNX 5300 (Unified) and was presented with a long list of LUN's the customer requires to be built on the new array.  On average, I'd say most of my array installs have 10-15 LUN's max so building them out by hand using the Unisphere GUI is no big deal.  But in this instance, the customer had already defined the LUN numbers and names so I thought it would be a good opportunity to figure out how to automate the LUN creation using simple Excel formulas and using the output to dump the commands into Unisphere CLI.  So, lets get to it!

First we need our raw LUN data  In my example you'll see 5 populated columns in an Excel sheet.  Column A is the VNX LUN number (ALU), B is the LUN name, C is the VNX storage pool we want the LUN to belong to, D is the LUN size, and E is the SP we want the LUN to be owned by.  A note on column E, This column needs to be defined because in Unisphere CLI, the lun -create command makes you specify a storage processor that owns the LUN.  This is a bit different than the GUI where by default, the "auto" placement is selected by default and you don't need to explicitly set the SP.  So column E is needed so the Excel function can pull from this column to balance the LUN's evenly across both SP's.

Now that we have our LUN data in Excel, we can simply build out the formula in Excel to build out the CLI command.  I used the CONCATENATE function in excel witch basically mashes a much of text and cell data together to create my command.  Starting in column G1 I began my formula to put the command together.

And here's the exact syntax of the command in cell G1:

=CONCATENATE("naviseccli"," ","-User"," ","sysadmin"," ","-Password"," ","sysadmin"," ","-scope"," ","0"," ","-h"," ","10.114.121.102"," ","lun"," ","-create"," ","-capacity"," ",D1," ","-sq"," ","gb"," ","-poolname"," ",C1," ","-sp"," ",E1," ","-l"," ",A1," ","-name"," ",B1)

Looks like voodoo right?  Let me explain what this is doing.  The formula is taking everything inside of parentheses and putting it all together in a single "sentence".  The quotes (" ") indicate this is text and quotes with a space in between them insert a space into the output.  Notice some of the quotes are calling cells, this is where the function pulls from our raw data table.  The result of the formula (if written correctly) should look like this:

Now that we've verified the output looks exactly like the CLI command needs to, we can use the auto fill feature in Excel to copy the formula down across all the rows and it will pull the data for all the following rows and put it into the format we need.  All you need to do is highlight cell G1 and you'll notice a tiny square in the lower right hand corner of the cell.  Grab this box with your pointer and drag the box down as many rows as you need to grab all the data in the table

And once you release your mouse click after dragging, the formula will populate the rest of the rows with the appropriate data.

Finally, we need to copy the data out of Excel into Notepad.  You only need to highlight and copy the G column since this is there the formula actually lives, it will take all the text output from the column and copy to clipboard.  Paste into Notepad and it should look like this:

Now you have all the text you need to input this into Unisphere CLI and create all the LUN's defined in one single shot.  To do this, highlight and copy all the text in the notepad document and paste it into the CLI window while in the Navi CLI directory on your local machine.  Remember you'll need Unisphere CLI installed on your machine for this command to work.  That's it!

A few other notes, there are additional switches that can be included in the command to define features like Thin LUN, offset, Tiering policy, etc.  The ones I showed in my example are the basic switches needed to create a LUN using CLI.  Refer to the Command Line Interface Reference for Block document on EMC powerlink for a comprehensive list of available CLI commands.