VirtuallyUseful – Random Tech Babble

October 29, 2017

Blogtober Post 6 – Accessing data when you don’t know where it is, examples using INDIRECT and VBA

Filed under: Excel — Ray Olander @ 12:59 PM

In the Nimble Storage planning / script generator sheet I wrote, I whoever was using the sheet to modify the areas throughout the workbook that would be cleared when the “nuke it” button was pressed. I had defined all the data ranges that I had entered by default, but I allowed a lot of customization to all the sheets. For example,there were a number of data validation tables that I allowed the user to extend beyond my default values and I wanted the “nuke it” script to clear their new data as well. There have been other occasions where a cell or range reference might not be known when the sheet was opened, but needed to be used at some point during use. This capability has saved my bacon a couple times so I thought it would be a good addition to the Blogtober postings.

Using Excel formulas, we have the INDIRECT(ref_text,[style]) formula, where [style] is optional and is used as TRUE (A1) or FALSE (R1C1). The default is A1, which is almost all I use, so I never bother defining that portion of the formula.

The REALLY REALLY cool thing about INDIRECT is that it allows you to concatenate values into either a A1 or R1C1 reference and then use that.

For example, if I wanted to see what information was stored in a cell, but the column and row values were randomly generated (perhaps when generated random sample data from a large dataset), I can use the INDIRECT formula in place of the [reference] value in CELL(info_type,[reference]) as such:

 

Or, if I wanted to find out some information based on a range instead of a cell, I could concatenate two values into a range format as such:

 

In the planning sheet I referred to, the DATATOCLEAR sheet had the custom ranges the user could identify for the VBA script to clear

 

Here’s the code I used for clearing the TABLES sheet (NOTE: I used the “!” symbol to allow me to keep a range definition, but just skip it):

TABLES.Activate
For counter = 3 To 50 ' Read all 50 cells and act on any that are populated
    If DATATOCLEAR.Range("A" & counter).Value = "" Then
        GoTo JUMPSHIP_TABLES
    ElseIf Left(DATATOCLEAR.Range("A" & counter).Value, 1) = "!" Then
        GoTo JUMPSHIP_TABLES
    End If
    TABLES.Range(DATATOCLEAR.Range("A" & counter)).ClearContents
JUMPSHIP_TABLES:
Next
TABLES.Range("A3").Activate

 

For each cell that wasn’t blank:

If DATATOCLEAR.Range("A" & counter).Value = "" Then

Clear the data ranges specified in the DATATOCLEAR “A” column

TABLES.Range(DATATOCLEAR.Range("A" & counter)).ClearContents

 

Although this is pretty straight forward, the nice thing is that I was able to define the range as a string to be read, not as a value in the formula. Not exactly an INDIRECT equivalent since the range wasn’t concatenated, but the same in the sense that a string was used for A1 values.

 

 

Advertisements

Blogtober Post 5 – Replacing VLOOKUP with INDEX and MATCH

Filed under: Excel — Ray Olander @ 11:35 AM

There might be times when a VLOOKUP either won’t work, or when it takes a significant amount of resources/time to work (like when referencing large datasets). In that case, you can use a combination of INDEX and MATCH to do the same thing.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) searches the leftmost column of your dataset for a match to your lookup_value, then returns the data <x> columns away from that matched cell. The limit being that the data returned ALWAYS has to be to the right of the referenced column being matched on.

The formula INDEX(array,row_num,[column_num])allows you always reference the leftmost column of the dataset as your starting position. That means that your entire dataset is always available, once you’ve chosen your row. To get the row you’ll need, you use the MATCH(lookup_value,Lookup_array,match_type) formula which tells you the first row number that your data was found in.

VLOOKUP is basically the combination of INDEX and MATCH but with limits. By having the MATCH output acting as a substitute for ROW in the INDEX(array,row_num,[column_num]) formula, you would use INDEX(array,<MATCH_RESULTS>,[column_num]) to get the same result without those limits.

 

To demonstrate how this works, I’ll use the following table:

In this case, I want to find what model vehicle a person has. Because MATCH and VLOOKUP only return the position of the FIRST match found, you wouldn’t want to use these commands when you know you have multiple values that could match. For example, there could be many people named “George”, “Nelson”, or even “George Nelson”. You want to use these commands when there’s a unique piece of data in the table, such as the customer number, or invoice number. This wouldn’t be possible with VLOOKUP because the data we want to return is left of the reference column.

 

Let’s look a little closer at a couple nuances of the MATCH formula. First, note the values possible for match_type are slightly counter-intuitive (with -1 being greater than)

MATCH(lookup_value,Lookup_array,match_type) where match_type is one of the following:

 1 (Less than)
 0 (Exact match)
-1 (Greater than)

Second is that lookup_array is a SINGLE COLUMN ARRAY (A1:A5 vs A1:G5)

Last, and this is a big one, MATCH returns the row number RELATIVE TO THE TOP OF THE REFERENCED LOOKUP_ARRAY. That means that in the example above, if we referenced the array as A2:A5 and looked for invoice 897, it would return as row 3. When that MATCH output is used by INDEX, INDEX starts from the top and looks at the ACTUAL row number being given (3) and would return the customer data for invoice 465 instead.

 

So we find the row we’re looking for based on the value input from cell B8

=MATCH($B$8,$G$1:$G$5,0)

Result = 4

 

We know the model of the vehicle is in column “C”, which is column #3. So, we substitute that MATCH formula for the ROW variable in the INDEX formula and use 3 as our column value:

=INDEX($A$1:$G$5,MATCH($B$8,$G$1:$G$5,0),3)

Result = Accord (Done!)

 

If you wanted to return ALL values in the row based on a single input, there’s a neat trick to do that, which is to the ROW() formula. Like the MATCH formula, the ROW() formula will return the row in which your reference exists. It just doesn’t have the same flexibility as the MATCH formula does. What ROW() does allow for though is to auto-increment when you copy a cell over others below it in sequence. For example, both =ROW(G1) and =ROW(1:1) would return a 1 for the value. If you copied that cell to the cell below it, ROW(G1) would become ROW(G2) and ROW(1:1) would become ROW(2:2). This means you get a value incrementing from 1 upwards. By using that as your column_num value in the INDEX formula, you can now easily write all the formulas to pull the information from left to right in the row that has the matching invoice number. Here’s the example:

It’s as easy as that!

October 28, 2017

Blogtober Post 4 – Using Excel to visualize ping latencies

Filed under: Excel — Ray Olander @ 5:49 PM

I wanted to graph ping latencies in Excel to diagnose a problem I was having at the time.  I needed a timestamp in the ping output as well as a continuous output, plus I wanted the interval to be determined from the command launch, not hardcoded.

I wrote a quick batch file (tping.bat) that used %1 as the IP address to ping, and %2 being the interval option to specify. These two lines do it all:

:Ping
for /f "tokens=* skip=2" %%A in ('ping %1 -n %2') do (echo %time% %%A && GOTO Ping)

I launched the batch file like this:

tping 192.168.0.14 2 > tping_out.txt

The following is a sample of the output:

11:34:08.86 Reply from 192.168.0.14: bytes=32 time=126ms TTL=252
11:34:10.10 Reply from 192.168.0.14: bytes=32 time=209ms TTL=252
11:34:11.18 Reply from 192.168.0.14: bytes=32 time=95ms TTL=252
11:34:12.22 Reply from 192.168.0.14: bytes=32 time=9ms TTL=252
11:34:13.26 Reply from 192.168.0.14: bytes=32 time=3ms TTL=252
11:34:14.33 Reply from 192.168.0.14: bytes=32 time=140ms TTL=252
11:34:19.17 Reply from 192.168.0.14: bytes=32 time=34ms TTL=252
11:34:20.60 Reply from 192.168.0.14: bytes=32 time=149ms TTL=252
11:34:21.77 Reply from 192.168.0.14: bytes=32 time=99ms TTL=252

Then I wanted a spreadsheet where I could paste the above output into and have it visualize latencies using thresholds that could be modified to increase or decrease the resolution at which latency was evaluated. The result was this:

In this next version I’m looking for latencies under 70ms because anything over 70 is unacceptable and I just need to show it went over, not the value itself:

 

So, I need a formula to parse the latency, the time, and to display an “X” if the latency equaled or exceeded the specified threshold. I also want the formula to display nothing if there is no ping data pasted in, so we’ll have to add in error checking.

 

Let’s start with the latency

 I know that the latency always starts the first character after “time=” and ends right before “ms”. That turns out to be an easy formula:

We’ll use MID(cell,start,length) for this.

We know that the formula will start with

=MID(E4,

Now we need to calculate the start point. The FIND command will return the position value of the “t” in “time=”, so we need to adjust the start point to the right another 5 characters

FIND("time=",A4)+5,

Now we need to determine the length of the latency reading by subtracting where “time=” ends from where “ms” starts

FIND("ms",A4)-(FIND("time=",A4)+5)

So now we tie it all together and we get the following formula (adding TRIM to ensure no spaces)

=TRIM(MID(A4,FIND("time=",A4)+5,FIND("ms",A4)-(FIND("time=",A4)+5)))
Result = 126 (as desired)

Now we have to make sure that if the ping cell is blank, so is out display. We’ll wrap our MID formula up in an IF formula. If the ping cell is blank, then display a blank, otherwise run the MID formula. Now we move the TRIM out from the MID formula to the overall IF formula.

=TRIM(IF(A5="","",MID(A5,FIND("time=",A5)+5,FIND("ms",A5)-(FIND("time=",A5)+5))))

 

Now let’s look at time. We know that time always starts at the first character, and it displays in 24h format, so in this ping output it is always 11 chars long.

The easy way would be to use LEFT(A4,11)

However, since I am trying to write formulas without depending on absolute position, I’m going to base my find off of the fact that the time always preceeds the word “Reply” with a space in between the two.

Again, we start with MID and we know the start point

=MID(A4,1,

Now we calculate the length. We use FIND to get where “Reply” starts, then subtract two to get the last position of the time.

FIND("Reply",A4)-2

Putting it together, we get the following (adding TRIM of course):

=TRIM(MID(A4,1,FIND("Reply",A4)-2))
Result = 11:34:08.86 (as desired)

 

Lastly, we just need to know if we should display an “X” to the right or a blank. So we need to compare the latency we parsed to the threshold we listed at the top, and do that for each cell.

If there is no ping pasted, then display blank. Otherwise, if latency shown in B4 is equal to or more than that listed in the respective column header, then show an “X”, otherwise show blank.

Start with the first IF

=IF(A4="","",

Now we need to write the formula to compare latencies and add that as the FALSE part of the above IF formula.

=IF(VALUE(B4)>=D2,"X","")

NOTE: I use VALUE just in case the latency portion of the sheet was set to STRING format rather than NUMBER.

 

That’s it. A batch file + a few formulas and I’m able to visualize my ping outputs at any thresholds I want. It’s not fancy, but it works!

October 27, 2017

Blogtober Post 3 – Use Excel to parse IP and Port info from syslog outputs

Filed under: Excel — Ray Olander @ 6:44 PM

SCENARIO:

You have a multitude of syslog entries pasted into Excel that you want to break into separate fields so you can sort by SourceIP, SourcePort, DestinationIP and/or DestinationPort.

Option 1: If your data is all the same, you can use the Data\Text To Columns option   to separate the data, in two iterations:

Assume all your outputs are the same message (so that the delimited columns all contain the same information):

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

You start by choosing the “delimited” option

Then pick the separator(s). Since you can’t choose both “:” and “/”, you can’t completely separate IP’s and ports. You can either split out the vlan/interface data and have the ip address together with the port, or split  out the port leaving the vlan/interface together with the IP address. This is the FIRST pass.

The result of the second option:

Next, insert a column to the right of the H and K columns because we’ll do a second text to columns operation on those to split out the ip address. When doing that however, it will overwrite the column to the right, which is currently the port number.

Now do text-to-columns separation based on the colon character

Result:

 

 

Option 2: use formulas so that you can dump ANY data and still get the information you need

As in the previous posting, I will use this as an example:

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

 

For this type of syslog entry, I am always looking for this pattern (capitalizing keywords for emphasis):

“[ALLOW|DENY] <space> [TCP|UDP] <space> SRC <space> [VLAN|INTERFACE]:<IP_ADDRESS>/<PORT> <space> DST <space> [VLAN|INTERFACE]:<IP_ADDRESS>/<PORT> <space> BY”

 

Define in words what pattern I will use to get the information I want:

SOURCE IP starts after the first colon “:” found after the starting point of (position of first slash – 16)

SOURCE PORT is after the first slash “/” in the line, up to the first space found after the first slash “/”

DESTINATION IP appears after the first colon “:” located right before the second slash “/” in the line

DESTINATION PORT appears after the second slash and goes up to the space that follows it (which is after the second slash “/”).

 

Assumptions

[Cell A2] is where I will place our SOURCE IP formula

[Cell B2] is where I will place our SOURCE PORT formula

[Cell C2] is where I will place the DESTINATION IP formula

[Cell D2] is where I will place the DESTINATION PORT formula

[Cell E2] is where the actual syslog entry has been pasted

 

The formulas I chose (for those who like the answer first)

[Cell A2]          =TRIM(MID(E2,FIND(“:”,E2,(FIND(“/”,E2)-16))+1,((FIND(“/”,E2)-1)- FIND(“:”,E2,(FIND(“/”,E2)-16)))))

[Cell B2]           =TRIM(MID(E2,FIND(“/”,E2)+1,FIND(” “,E2, FIND(” “,E2,FIND(“/”,E2)) – FIND(“/”,E2))))

[Cell C2]           =TRIM(MID(E2,FIND(“:”,E2,(FIND(“/”,E2,FIND(“/”,E2)+1)-16))+1,(FIND(“/”,E2,FIND(“/”,E2)+1)-1)-FIND(“:”,E2,(FIND(“/”,E2,FIND(“/”,E2)+1)-16))))

[Cell D2]          =TRIM(MID(E2, (FIND(“/”,E2,FIND(“/”,E2)+1)+1), FIND(” “,E2,FIND(“/”,E2,FIND(“/”,E2)+1)) – (FIND(“/”,E2,FIND(“/”,E2)+1)+1)))

 

FORMULAS EXPLAINED

Note: while I could use the words “src” and “dst” as anchors, I’m going to use only the “:” and “/” characters. That way even if src and dst were to be replaced by something else in a different syslog entry, I’d still correctly parse out the information I’m looking for.

 

[Cell A2]  – Source IP – starts after the first colon “:” found after the starting point of (position of first slash – 16)

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

So, we’re looking for data between the “:” that occurs before the first “/” in the line, up to the first “/” in the line

We know that an IP address will never exceed 15 char’s (xxx.xxx.xxx.xxx). That will be important for our formula

 

Commands I’ll use

Mid (cell, where in the string to start, how many characters to select)

Find (What text to find, in what cell to look, starting at what position)

 

Let’s start by breaking this into little pieces. Let’s find the first slash “/” in the line

=FIND(“/”,E2)

Result = 55 (so we were able to find the character without an error and it is in position 55)

Now let’s find the colon “:” before the first slash “/”. Remember, with FIND, we get to specify a start point, which will be 55-16 (the max IP address length plus the one position the “:” will occupy)

=FIND(“:”,E2,(FIND(“/”,E2)-16))

Result = 47 (so we know the IP address covers positions 48-54 in this example)

 

FORMULA GOAL: MID(E2, start from the colon, length = position of “/” minus position of “:”)

 

=MID(E2,FIND(“:”,E2,(FIND(“/”,E2)-16)),(FIND(“/”,E2)- FIND(“:”,E2,(FIND(“/”,E2)-16))))

Result =   :1.1.1.1 (so we need to adjust the START position to be +1, see the next formula)

 

=MID(E2,FIND(“:”,E2,(FIND(“/”,E2)-16))+1,(FIND(“/”,E2)- FIND(“:”,E2,(FIND(“/”,E2)-16))))

Result =   1.1.1.1/ (so we need to adjust the position of the slash “/” to be one less so it doesn’t factor into our length calculations. See the next formula)

 

=MID(E2,FIND(“:”,E2,(FIND(“/”,E2)-16))+1,((FIND(“/”,E2)-1)- FIND(“:”,E2,(FIND(“/”,E2)-16))))

Result =   1.1.1.1  Perfect!

 

[Cell B2] – SOURCE PORT is after the first slash “/” in the line, up to the first space found after the first slash “/”

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

 

We’ll be inspecting E2 with the MID function

=MID(E2,

 

We start at the first slash “/” in the line, but add one character so we don’t get the actual slash “/” in the output

FIND(“/”,E2)+1

 

For the last part of the mid string, which is the length of the string we want to grab, we subtract the position of the “/” symbol FIND(“/”,E2) from the position of the first space that is found after that “/” symbol FIND(” “,E2,FIND(“/”,E2))

FIND(” “,E2, FIND(” “,E2,FIND(“/”,E2)) – FIND(“/”,E2))

 

So the whole formula will be (including the cautionary TRIM)

=TRIM(MID(E2,FIND(“/”,E2)+1,FIND(” “,E2, FIND(” “,E2,FIND(“/”,E2)) – FIND(“/”,E2))))

Result = 23456. Perfect.

 

[Cell C2] – DESTINATION IP – appears after the first colon “:” located right before the second slash “/” in the line

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

 

Looking in E2 again with the MID function

=MID(E2,

 

Using what we learned for the source IP, we’ll start 16 char’s before the slash “/”, but we need to find the SECOND slash this time. We’ll use the FIND offset again and say to start looking one position AFTER the FIRST slash “/” is found

 

First, let’s just find the second slash:

FIND(“/”,E2,FIND(“/”,E2)+1)

 

Now, continuing to use offsets, find the colon “:” that is less than 16 characters before the second slash “/” to give us our ip address starting point

FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16))

 

The length of the ip address is the number of characters between the colon “:” that preceeds the IP address and the slash “/” that follows it.

FIND(“/”,E2,FIND(“/”,E2)+1) – FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16))

 

To put it all together

=MID(E2,  FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16)),  FIND(“/”,E2,FIND(“/”,E2)+1) – FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16)))

Result = :2.2.2.2

 

Just like before, we’ll need to adjust the start position by +1 to get rid of the colon “:”

=MID(E2,  FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16))+1,  FIND(“/”,E2,FIND(“/”,E2)+1) – FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16

Result = 2.2.2.2/

 

Again like before, now we need to adjust the position of the second slash “/” by -1 so it doesn’t factor into our length calculations

=MID(E2,  FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16))+1,  (FIND(“/”,E2,FIND(“/”,E2)+1)-1) – FIND(“:”,E2,( FIND(“/”,E2,FIND(“/”,E2)+1)-16

Result = 2.2.2.2 Perfect

 

 

[Cell D2] – DESTINATION PORT – appears after the second slash and goes up to the space that follows it (which is after the second slash “/”).

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

 

Looking in E2 again with the MID function

=MID(E2,

 

The start of the MID string will be 1 character past the second slash “/”. We know from cell C2, the second slash can be found using this formula, then add 1

(FIND(“/”,E2,FIND(“/”,E2)+1)+1)

 

The length of the port is “(space following) – (second slash)”.

Space following second slash = FIND(” “,E2,FIND(“/”,E2,FIND(“/”,E2)+1))

Second slash = (FIND(“/”,E2,FIND(“/”,E2)+1)+1)

Length = FIND(” “,E2,FIND(“/”,E2,FIND(“/”,E2)+1)) – (FIND(“/”,E2,FIND(“/”,E2)+1)+1)

 

Entire formula, including TRIM

=TRIM(MID(E2, (FIND(“/”,E2,FIND(“/”,E2)+1)+1), FIND(” “,E2,FIND(“/”,E2,FIND(“/”,E2)+1)) – (FIND(“/”,E2,FIND(“/”,E2)+1)+1)))

Result = 22 Perfect!

 

Here we see the results:

 

So, as long as you have “:<ip_address>/port_number” the syslog output, you can apply these formulas to a wide variety of syslog outputs and get the data you need.

October 21, 2017

Blogtober Post 2 – A demo of how to build, and breakdown, an Excel formula

Filed under: Excel — Ray Olander @ 4:09 PM

Before I get into examples of how I break text down from things such as log entries, I want to cover how to read formulas in Excel that have nested functions. These can often times get to be long enough that they are difficult to understand without breaking apart.

 

I will continue to use the example syslog entry from the previous posting:

Oct 5 10:41:23 MyFirewall1: Deny udp src VLAN2:1.1.1.1/23456 dst outside:2.2.2.2/22 by access-group “Can’t Touch That” [0x0, 0x0]

 

Here is the word version of the formula:

We want to indicate all the line entries in our log export that are from the Sophos firewall. To do that, for each line, we will extract the firewall name from the syslog entry and check what kind of firewall it is from a vlookup table. If it is a Sophos firewall, display the word “Sophos”, if that firewall is not in the lookup table, we’ll need to add it so we’ll display “add”. Otherwise, we’ll show nothing. This way we can sort all the entries by firewall type.

 

Note: You can use “DATA > TEXT TO DATA”, and selected delimited by space and ( / or : ) to break up the log entries into columns to make some searching easier for structured outputs, but using this capability on windows event log outputs from syslog is a total mess. If you can get the data in a way that you always control and put it in a cell you designate, then you know your outputs will always work and give you what you need the way you need it. So, for these postings, I’m going to stick to using formulas to getting the data.

 

Ok, let’s start with slowly building the formula so you can see how it gets put together, so it will be easier to break back down.

 

First, let’s start with the firewall name.

  • We’ll focus on the first part of the entry
    • Oct 5 10:41:23 MyFirewall1:
  • If we break down the format and character counts, we see that
    • month=4 char’s
    • space=1 char
    • day=1-2 char’s
    • space=1 char
    • hour=2 char’s
    • colon “:”=1 char
    • minute=2char’s
    • colon “:”=1 char
    • seconds=2 char’s
    • space=1 char
    • firewall name=<?> char’s
    • colon “:”
  • The FIND and MID functions are the main tools when doing these searches. To use those, we need to evaluate at what position will we find our data and what fixed patterns can we anchor off of to do the FINDs.
  • We know that the firewall name starts after space that is found past the second “:”, and is always at the 14th or 15th position depending if the “day” is one or two char’s long.
  • We know that the firewall name is at least 1 char long, and therefore starts at position 16 or 17 depending if the day is one or two char’s long, and it ends with the 3rd
  • Alternatively, if it were the case that we didn’t have a reliable known position for our data, we could write formulas to look for “the third : after the second : after the first :”. That way, no matter where is appears in the string position wise, we’ll always find the text we’re looking for.

Here are the steps I took to accomplish my goal

Now to break the formula back down, let’s start with the first IF function:

=IF(ISERROR(VLOOKUP(TRIM(MID(A1,FIND(” “,A1,14),FIND(“:”,A1,16)-FIND(” “,A1,14))),E2:F3,2,FALSE)),”add”,IF(VLOOKUP(TRIM(MID(A1,FIND(” “,A1,14),FIND(“:”,A1,16)-FIND(” “,A1,14))),E2:F3,2,FALSE)<>”sophos”,””,VLOOKUP(TRIM(MID(A1,FIND(” “,A1,14),FIND(“:”,A1,16)-FIND(” “,A1,14))),E2:F3,2,FALSE)))

 

Break it into pieces:

=IF    (

ISERROR    (

VLOOKUP (

TRIM      (

MID      (

A1,     This is the cell being examined by the MID(cell,start,length) function

FIND(” “,A1,14),    this is the start portion of the MID(cell,start,length)

FIND(“:”,A1,16)-FIND(” “,A1,14)  this is the length portion of the MID(cell,start,length) function

)    close the MID function

),   close the TRIM function

E2:F3,2,FALSE     this is the matrix,offset,exact/similar portion of the vlookup function

)    close the VLOOKUP function

),    close the ISERROR function and close the condition check of the if statement

“add”    the “if true” portion of the IF statement – if the VLOOKUP caused an error, display “add”

 

Then do the same thing again for the nested (second) if statement

=IF(ISERROR(VLOOKUP(TRIM(MID(A1,FIND(” “,A1,14),FIND(“:”,A1,16)-FIND(” “,A1,14))),E2:F3,2,FALSE)),”add”,IF(VLOOKUP(TRIM(MID(A1,FIND(” “,A1,14),FIND(“:”,A1,16)-FIND(” “,A1,14))),E2:F3,2,FALSE)<>”sophos”,””,VLOOKUP(TRIM(MID(A1,FIND(” “,A1,14),FIND(“:”,A1,16)-FIND(” “,A1,14))),E2:F3,2,FALSE)))

 

IF         (

VLOOKUP (   begin the condition check of the if statement

TRIM      (

MID    (

A1,    This is the cell being examined by the MID(cell,start,length) function

FIND(” “,A1,14),      this is the start portion of the MID(cell,start,length)

FIND(“:”,A1,16)-FIND(” “,A1,14)      this is the length portion of the MID(cell,start,length) function

)     close the MID function

),     close the TRIM function

E2:F3,2,FALSE      this is the matrix,offset,exact/similar portion of the vlookup function

)      close the VLOOKUP function

<>”sophos”,     close the comparison check for condition portion of the IF(condition,true,false) function

“”,      if the condition is TRUE (in the table but not sophos, then return blank

VLOOKUP (       if the condition check is false (equals “Sophos”) then return the VLOOKUP value

TRIM      (

MID    (

A1,      This is the cell being examined by the MID(cell,start,length) function

FIND(” “,A1,14),      this is the start portion of the MID(cell,start,length)

FIND(“:”,A1,16)-FIND(” “,A1,14)       this is the length portion of the MID(cell,start,length) function

)       close the MID function

),      close the TRIM function

E2:F3,2,FALSE       this is the matrix,offset,exact/similar portion of the vlookup function

)        close the VLOOKUP function

)      close the SECOND IF function

)       close the FIRST IF function

 

Although formulas can get VERY long, if you break them down function by function, they get much easier to understand

Stay tuned for Blogtober post 3 where I apply a number of the above commands to the same syslog entry use in the previous examples to parse out the source and destination IP’s and ports.

October 19, 2017

Blogtober Post 1 – Recommended functions to start learning Excel with

Filed under: Excel — Ray Olander @ 6:52 PM

With all the possible things to learn in Excel, people often wonder where to even start. I have been using Excel for a very long time now, and the following are the functions I use to do probably a good 90% of all my work. If you can learn these, you’ll be amazed at how many things you’ll be able to use Excel for. I wouldn’t say learning these will SAVE your job, but they can most definitely get you noticed.

This is a list of the functions that I use on a daily basis:

String operations: LEFT, RIGHT, MID, LEN, TRIM, FIND, VLOOKUP, and CONCATENATE (the short version I prefer is simply the & symbol)

Logical operations: IF, NOT, ISERROR, AND, OR

Sum/count operations: SUM, COUNTA, COUNTIF

Here are examples and explanations for each of the functions:

LEFT(cell, number of characters to select from the beginning of the line)

Returns a specified number of characters from the left-most character in a string

RIGHT(cell, number of characters to select from the end of the line)

Returns a specified number of characters from the right-most character in a string

MID(cell, where in the string to start, how many characters to select)

Returns a specified number of characters from a given starting position within a string

LEN(cell you want to find the length of the text in)

Returns the length of a string

Example:

TRIM(the output of one of the above functions)

Removes white spaces from the beginning and end of a string

Example:

FIND(What text to find, in what cell to look, starting at what position)

Return the position value of a character or string found within another string

Example:

VLOOKUP(what test to find, where to look for it, offset from located value to read, return exact (false) or similar (true) results)

Return a separate value in a table offset by a specified amount in the same row as the index value found within the table

Example:

CONCATENATE(text1,text2,…) (or & & etc… which I’ll show later)

Combine two or more values

Example:

IF(condition(s) to evaluate, what to do if true, what to do is false)

Take different actions based on the result of one or more conditional statements

Example:

NOT(whatever is being evaluated)

Invert the results

Example:

ISERROR(whatever is being evaluated)

Returns a true or false based on a wide array of possible errors you might receive

Example:

AND(condition1,condition2,…)

All conditions evaluated must be true in order for the outcome to be true

OR(condition1,condition2,…)

Any condition being true causes the outcome to be true

Example:

SUM(number/range1,number/range2,…)

Adding up numbers to get a value

COUNTA(number/range1,number/range2,…)

Check how many cells in a range are not empty

Opposite of countblank

COUNTIF(range, criteria)

Checks how many cells match one given criteria

Countifs is the formula to use to evaluate multiple criteria

Example:

 

Stay tuned for Blogtober post 2 where I apply a number of the above commands to the same syslog entry use in the previous examples to parse out the firewall name,

and to show how to breakdown that formula, or any formula, to better understand what it is doing.

October 15, 2017

Blogtober Intro

Filed under: Excel, Uncategorized — Ray Olander @ 4:13 PM

Once again it has been proven that time waits for no man. Blogtober is already almost half over and I am only now getting started on my writeups. I have a lot of information I’d like to share, it’s just the same problem everyone has – a lack of available time to do so. I really hope though to make this the year that I actually start writing and keep writing :)

I’m going start this Blogtober by discussing tips and tricks that I’ve learned in Excel that make many things I do on a regular basis easier and/or faster. The bulk of Excel work that I do currently is parsing syslog entries from network devices and Servers looking for information, and also generating code of some type based on a list of inputs (for example, converting a list of values into formatted XML). That said, I’ve needed to go farther than cell formulas will take me, so I’ve also dabbled in writing macros/VBA code in Excel to extend its capabilities even farther.

A few examples of things I’ve done in Excel that made a big difference for me:

  • I built a tool for our Nimblestorage deployments that would allow you to enter information about the environment, then push a button to generate a list of properly sequenced commands that would build what you had specified, and the code also generated the scripts to remove the configuration as well. Word had of the tool spread to corporate through our SE and it wound up being promoted throughout Nimblestorage internally for use globally by all SE’s. In fact, I was approached by several individuals at Nimblestorage about possible job opportunities. All because of an Excel sheet that filled a need.
  • I was leading a project that had multiple engineers working on it. We used one main Excel sheet to plan all the actions for the project, but needed to break that very long list down into tech-specific assignments. Using VBA, I was able program a single button on the main page to parse the entire list and send it to separate tabs per tech so everyone could print out just their assignments whenever they needed it. That was very helpful in us getting the project done on time.
  • I wrote a utility which determines the necessary non-functional requirements (NFRs) for a solution being evaluated based on its resulting security profile, which is determined from the answers given in a short questionnaire. Each answer represents a different aspect of the overall security profile. It evaluates the answers against a security matrix for each item and derives a final security rating for whatever you are evaluating. It then generates a table of all the matching NFRs needed in each category in a single output, which is in a form that can then be edited to show the controls implemented per line item to satisfy each requirement. This had not only the benefit of giving the security team a working tool that translated the complexity of this new system into little more than a simple questionnaire (enabling quick user adoption) but it also made it much easier for anyone having to determine the requirements for multiple scenarios so each could be evaluated.

There are of course many people out there that have done way cooler things with Excel, but the idea I’m trying to convey is that even with just a basic understanding of a few key commands, Excel can be used in many ways to get meaningful work done and even get you noticed. Even though it’s not as elegant as most other solutions, it is typically accessible now days to most people which makes it an easy and perpetually relevant platform to learn.

As for the upcoming posts, I haven’t decided yet if I’ll make all 5 blogtober posts about Excel or not. I’d very much like to write about a monitoring solution called NMSaaS and also Microsoft’s StorSimple product, two projects I’ve been wrapped up in lately. Stay Tuned!

Note: Check out http://tcwd.net/vblog/ for a list of other Blogtober participants and the number of posts each has submitted. Matt’s done a lot of work to rally the community around this effort, which has been pretty impressive. Check out his page!!!!

October 30, 2016

Disable beeps in pfSense

Filed under: Firewalls — Ray Olander @ 2:17 PM

pfSense 2.3.2-RELEASE-p1 (i386)

FreeBSD 10.3-RELEASE-p9

Despite the articles I found saying these worked, these settings were not persisting between reboots for me:

/etc/rc.conf

kern.vt.enable_bell=0

allscreens_kbdflags=”-b quiet.off”

/etc/sysctl.conf

hw.syscons.bell=0

cmdline

kbdcontrol -b quiet.off

sysctl hw.syscons.bell=0

Instead, I deleted the above entries and added the commands to the global profiles config file. This worked for me:

/etc/profiles

sysctl hw.syscons.bell=0

kbdcontrol -b quiet.off

June 10, 2015

IPv4 Subnetting Tricks

Filed under: Cisco, Networking — Ray Olander @ 1:50 PM

 


For those of you familiar with numbering systems, or who just want to get to the shortcuts, skip to the section below titled “SHORTCUTS”


 

Some quick and easy things to learn to help you with subnetting (sorry for the lack of graphics and nice formatting, I need to spend some time learning wordpress):

 

First, know what binary is.

Binary is a number system based on 2 numbers (0 and 1). Because it only has 2 numbers allowed, it’s called Base2.

In our everyday lives, we use Base10 (where the allowed digits are 0-9).

 

Second, remember these three rules

ANY number to the power of zero equals 1  (10^0 = 1)

ANY number to the power of one equals itself (10^1 = 10)

A POWER means the number of times a number is multiplied by itself. Ten the the second power (10^2) = 10 x 10 = 100. Two to the third power (2^3) = 2 x 2 x 2 = 8

 

Third, you need to know that every number in ANY base system gets its value based on its position. It does that because every position has a value.

First, let’s look at position values

Starting from the decimal point and working your way to the left, each position’s VALUE is equal to the DIGIT multiplied by (the BASE to the POWER of the POSITION LEFT OF THE DECIMAL minus 1)

Example1: The number 5 (in Base10).

Following the formula above, we see that “5” is actually equal to 5 x (the BASE to the POWER of the POSITION LEFT OF THE DECIMAL minus 1)

= 5 x (Base10 to the power of “1 position left of the decimal minus 1”)

= 5 x (10 ^ (1 – 1))

= 5 x 10^0

= 5 x 1

=5

 Example2: Let’s look at a bigger number: 1234 (one thousand, two hundred and thirty four)

We know that 1234 = 1000 + 200 + 30 + 4

1 is 4 positions left of the decimal, 2 is three positions left of the decimal, 3 is two positions left of the decimal and finally 4 is one position left of the decimal

So what we really have is 1×10^(4-1) + 2×10^(3-1) + 3×10^(2-1) + 4×10^(1-1)

= (1×10^3) + (2×10^2) + (3×10^1) + (4×10^0)

= (1×1000) + (2×100) + (3×10) + (4×1)

= 1000 + 200 + 30 + 4

= 1234

 

Now let’s apply what we learned about Base10 numbers to Base2 numbers

Ok, so remember, each position’s VALUE is equal to the DIGIT multiplied by (the BASE to the POWER of the POSITION LEFT OF THE DECIMAL minus 1)

Example: 0101 (Base2)

= (0 x 2^(4-1)) + (1 x 2^(3-1)) + (0 x 2^(2-1)) + (1 x 2^(1-1))

= (0 x 2^3) + (1 x 2^2) + (0 x 2^1) + (1 x 2^0)

=(0 x 8) + (1 x 4) + (0 x 2) + (1 x 1)

= 0 + 4 + 0 + 1

=5

 

A LITTLE more background on number values

Subnetting is done in octets (groups of 8 bits)

The positional values of 8 bits in Base2 = 128, 64, 32, 16, 8, 4, 2, 1

1100 0000 = 128 + 64 = 192

1110 0000 = 128 + 64 + 32 = 224

1111 0000 = 128 + 64 + 32 + 16 = 240 (and going further we get 248, 252, 254 and finally 255 which is the LARGEST value 8 bits can represent in Base2)

You’ll find that the numbers you get when adding like this are the same numbers you’ll see in subnetting

 

And finally, the last of the rules…

Class “A” = 0.0.0.0 – 127.255.255.255.255

Class “B” = 128.0.0.0 – 191.255.255.255

Class “C” = 192.0.0.0 – 223.255.255.255

We know that 255 is the LARGEST number in any given octet

We know that although we write IPv4 using Base10 numbers (192.168.0.1), the computer only sees them as binary 11000000.101010000.00000000.00000001 (192.168.0.1)

We know that a subnet mask is what tells us which of the IP ADDRESS bits represent the NETWORK and which represent the HOST.

We know that the NETWORK address has ALL HOST BITS SET TO ZERO

We know that the BROADCAST address has ALL HOST BITS SET TO ONE

We know that a VALID HOST ADDRESS must have AT LEAST ONE NON-ZERO BIT

So 255.255.255.252 is the smallest network possible since it consists of a network address, a broadcast address and two IP’s for a device at either end. This is very typical of WAN connections.

We know that a VALID HOST FOR A GIVEN NETWORK is determined by a combination of the NETWORK ADDRESS and the SUBNET MASK

We know that 255 Base10 = 1111 1111 in Base2 (so ALL 8 bits in the octet are set to ONES)

If the subnet mask bit is a ONE, then that portion of the IP ADDRESS represents the NETWORK and if the subnet bit is a ZERO, then that bit of the IP ADDRESS represents the HOST.

Example: 192.168.1.248 with a subnet mask of 255.255.255.240

192 matches the first 255, so it entirely represents the NETWORK portion of the IP ADDRESS

168 matches the second 255, so it entirely represents the NETWORK portion of the IP ADDRESS

1 matches the third 255, so it entirely represents the NETWORK portion of the IP ADDRESS

Now the fun part.

192.168.001.248 (248 in Base10)   = 1111 1000 (Base2)

255.255.255.240 (240 in Base10)   = 1111 0000 (Base2)

So we see that the left four bits of the value 248 represent the NETWORK and that the remaining four bits (1000) represent the host

Therefore, the NETWORK address = 192.168.1 plus all HOST bits zeroed out = 192.168.1.11110000 = 192.168.1.240

The BROADCAST address = 192.168.1 plus all HOST bits set to ONE = 192.168.1.11111111 = 192.168.1.255

The VALID RANGE OF HOSTS = 192.168.1.11110001 (192.168.1.241) through 192.168.1.11111110 (192.168.2.254)

 

SHORTCUTS (see the examples section below for full explanations) (I’ve changed all formulas to use 256 rather than mixing 256 and 255):

1)In any octet, the max decimal value is 255 (1111 1111 in base 2, or FF in base 16). That means that you can use 256 to quickly calculate class C subnetting since class C networks are all determined in the last octet.

2) The NETWORK BOUNDARY = 256 – NETMASK in that octet. (256-240 = 16 address network)

3) The usable hosts in a class “c” subnet = 256 – 2 – NETMASK (minus 2 for the network and broadcast; 256-2-240 = 14 hosts)

a) The BROADCAST ADDRESS also equals ONE LESS that the next NETWORK ADDRESS (192.168.0.16/28 broadcast address is 192.168.0.15)

4) The wildcard mask = the inverse of the subnet mask (aka netmask) which also equals 256 – 1 – NETMASK in that octet

a) A netmask of 240 = 1111000 in binary, the inverse of that binary value is 00001111 = 15. So, taking 256 – 1 -240 = 15

5) The VLSM (variable length Subnet Mask) = a slash and the number of bits used in the NETMASK (255.255.255.240 = 8+8+8+4 = /28)

a) This one takes math. Know that 11110000 = 240 (I start from the middle so quicker to add or subtract). Know which bit represents which decimal value (128,64,32,16,8,4,2,1)

b) By knowing 11110000 = 240 = /28 = 16 networks with 14 hosts each, I can quickly determine that a /29 = 11111000 = 240+8 = 248 = 32 networks of 6 hosts each

6) TOTAL POSSIBLE HOSTS PER NETWORK = (2 ^ #host bits,or the bits that are zeros) -2  [the minus 2 is to account for the network and broadcast IP’s]

a) 255.255.255.0 = 11111111.11111111.11111111.00000000, which has 8 zero bits, therefore possible hosts = (2^8)-2 = 256-2 = 254

b) 255.255.255.240 = 11111111.11111111.11111111.11111000, which has 3 zero bits, therefore possible hosts = (2^3)-2 = 8-2 = 6

7) TOTAL POSSIBLE NETWORKS = (2 ^ #network bits for that class,or the bits that are ones)

a) Class C: 255.255.255.0 = 11111111.11111111.11111111.00000000, which has 0 one bits, therefore possible networks = (2^0) = 1

b) Class C: 255.255.255.240 = 11111111.11111111.11111111.11111000, which has 5 one bits, therefore possible networks = (2^5) = 32

SHORTCUT EXAMPLES:

1) The NETWORK BOUNDARY = 256 – NETMASK in that octet

EXAMPLE 1 – CLASS C

Let’s use 192.168.1.0/28

Subnet mask = 255.255.255.240

NETWORK BOUNDARY = 256-240=16; that means that a new network starts at every multiple of 16

VALID NETWORKS = 192.168.1.0, 1.16, 1.32, 1.48, 1.64, 1.80, 1.96, 1.112, 1.128, 1.144, 1.160, 1.176, 1.192, 1.208, 1.224, and finally 1.240 (remember we can only go to 255 and 240+16=256)

EXAMPLE 2 – CLASS B

Let’s use 172.16.7.55

Subnet mask = 255.255.240.0

NETWORK BOUNDARY = 256 – 240 = 16

VALID NETWORKS = 172.16.0.0, 172.16.16.0, 172.32.0, … , 172.16.240.0

EXAMPLE 3

What network does 192.168.193.74/255.255.255.192 belong to?

256 – 192 = network boundary of 64

valid network are 192.168.193.0, 193.64, 193.128, and 193.192, so the answer is the 192.168.193.64 network (74 is between 64 and 128)

2) The usable hosts in subnet = 256 – 2 – NETMASK (class “C” only for this rule of thumb, see example 5 for the formula for all classes)

EXAMPLE

You have a workstation with the address 192.168.193.74/255.255.255.192. What other IP’s are in the same network as your workstation?

256 – 192 = network boundary of 64 (so we know automatically that there are 62 usable IP addresses total in this network)

valid networks are 192.168.193.0, 193.64, 193.128, and 193.192. That means this address is part of the 192.168.193.64 network (74 is between 64 and 128)

The next network = 192.168.193.128, so we know the BROADCAST address is 192.168.193.127

Answer: that leaves 192.168.193.65 through 192.168.193.73 AND ALSO 192.168.193.75 through 192.168.193.126 (remember you’re .74 and you want the OTHER IP’s)

3) The wildcard mask = the inverse of the subnet mask (aka netmask) which also equals 256 – 1 – NETMASK in that octet

EXAMPLE1

Subnet mask = 255.255.255.192  (option 1: (256 – 1 – that octet) -or- option 2: invert the binary equivalent)

wildcard (option 1) = (256 – 1 – 255).(256 – 1 – 255).(256 – 1 – 255).(256 – 1 – 192) = 0.0.0.63

wildcard (option 2) = we know the 1st THREE numbers are zero (255 always inverts to 0) then tack on 192 in binary and invert it: [0.0.0].11000000 = 0.0.0.00111111 = 0.0.0.63

EXAMPLE2

Subnet mask = 255.255.192.0   (option 1: (256 – 1 – that octet) -or- option 2: invert the binary equivalent)

wildcard (option 1) = (256 – 1 – 255).(256 – 1 – 255).(256 – 1 – 192).(256 – 1 – 0) = 0.0.63.255

wildcard (option 2) = we know the 1st TWO numbers are 0.0 (255 always inverts to 0), we know the last number is 255 (0 always inverts to 255), so for the third octet we do the same calculations: list out the last two octets in binary and invert them: [0.0].11000000.[255] = 0.0.00111111.11111111 = 0.0.63.255

4) The VLSM (variable length Subnet Mask) = a slash and the number of bits used in the NETMASK (255.255.255.240 = 8+8+8+4 = /28)

EXAMPLE

255.255.255.224

To start, we know that 255.255.255 = 8bits + 8bits + 8bits so we’re already at 24 bits. Now we just look at the last octet

Option1: We know that .240 is always 4 bits, we know that .224 is one position left of .240 (number position values), so 3 bits, therefore we know the answer is 24+3 = /27

Option2: We know that the values of the octet are 128, 64, 32, 16, 8 ,4 ,2 ,1 and we know that ADDING the values of each set bit in the octet (1110 0000) gives 128+64+32 = 224

Option3: Memorize decimal values for bit counts. As each bit gets added from the left, the values will always be 128, 192, 224, 240, 248, 252, 254, and 255. Since 224 is always 3rd from the left, we know the answer is 24+3 = /27

5) Find the total possible networks and hosts given a defined class and subnet

Other than to determine which bits by default are used to identify the network, the IP address is irrelevant for this example since the total values are based solely on the subnet mask

EXAMPLE 1

Class network = C

Subnet mask = 255.255.255.0

Notes

For a standard class C address, we know that the first THREE octets are the “normal” subnet mask bits.

We know that 255 = 8 bits so we have a total of 8+8+8 = 24 bits defining the network

There are 32 bits in an IPv4 address, so there are 32 – 24 = 8 bits defining the hosts

Calculate possible networks

Using the formula ( 2 ^ # network bits) = total networks possible (assuming your gear also handles “zero networks”, otherwise [(2^n)-1] )

2 ^ 24 = 16,777,216 possible networks

Calculate possible hosts

Using the formula ( 2 ^ # host bits ) – 2 = (2 ^ 8) – 2 = 256 – 2 = 254

We subtract 2 to account for the network and the broadcast values

To verify, use the class “c” shortcut of 254 – (last octet of the netmask) = 254 – 0 = 254 hosts per network

EXAMPLE 2

Class network = B

Subnet mask = 255.255.0.0

Notes

For a standard class B address, we know that the first TWO octets are the “normal” subnet mask bits.

We know that 255 = 8 bits so we have a total of 8+8 = 16 bits defining the network

There are 32 bits in an IPv4 address, so there are 32 – 16 = 16 bits defining the hosts

Calculate possible networks

Using the formula ( 2 ^ # network bits) = total networks possible (assuming your gear also handles “zero networks”, otherwise [(2^n)-1] )

2 ^ 16 = 65,536 possible networks

Calculate possible hosts

Using the formula ( 2 ^ # host bits ) – 2 = (2 ^ 16) – 2 = 65,536 – 2 = 65,534 hosts per network

We subtract 2 to account for the network and the broadcast values

EXAMPLE 3

Class network = A

Subnet mask = 255.0.0.0

Notes

For a standard class A address, we know that the FIRST octet is the “normal” subnet mask bits.

We know that 255 = 8 bits so we have a total of 8 bits defining the network

There are 32 bits in an IPv4 address, so there are 32 – 8 = 24 bits defining the hosts

Calculate possible networks

Using the formula ( 2 ^ # network bits) = total networks possible (assuming your gear also handles “zero networks”, otherwise [(2^n)-1] )

2 ^ 8 = 256 possible networks

Calculate possible hosts

Using the formula ( 2 ^ # host bits ) – 2 = (2 ^ 24) – 2 = 16,777,216 – 2 = 16,777,214 hosts per network

We subtract 2 to account for the network and the broadcast values

I hope you found this useful. Perhaps at a later date I will have time to add graphics of this process. There are many sites on the Internet that deal with this topic. A quick search brought up this site that was quite informational (and formatted much nicer than mine!): http://www.subnetting.net/Tutorial.aspx

September 15, 2014

Nimblestorage array planning sheet and script generator

Filed under: SAN, Scripting — Ray Olander @ 10:55 PM

UPDATE TO THIS ARTICLE: I’ve recently published a tool for firmware version 2.1.x. You can find both the 1.4.x and 2.1.x workbooks at https://connect.nimblestorage.com/thread/1769

Let’s say you purchase a new Nimble array and you determine you need to create and configure like 150 volumes, each of which will need to be associated with a volume collection, which in turn needs to be defined with a schedule and credentials and so on. And of course, you’ll need to define initiator groups, possibly some performance profiles, and maybe even set the array from fresh out of the box. I know all that can be done with the GUI, but who really wants to do that? Before there was a way to interface with the array via powershell (which I have yet to get to try), the way I came up with to “automate” generating the scripts I needed was to use Excel.

The Excel workbook I’ve posted on the Nimble Connection site is a much refined version of what I initially built to deploy our first Nimblestorage array. That sheet worked so well that I just had to see how far I could take it. Little by little in my “free time” I’ve added capabilities until it has finally reached what I consider to be presentable to others. Once I was ready to let people see it, I showed it to a select few of the Nimblestorage team and I have received very positive feedback, so I am hoping it will be useful for others as well. This updated link is for v5.13 of the sheet for both 1.4.x code and 2.1.x code.

This can be used as a planning tool, which effectively results in generating documentation of the array configuration. However, the real value is that it generates ALL the BUILD commands associated with your inputs AND it generates all the DELETE commands to undo what you configured as well (since there’s no “factory reset” option on the array). I create the code locally on each planning page (in case you just need a quick command) as well as having an “all-in-one” sheet for both create and delete code where I scrape the code from the entire workbook and organize it (for the full copy/paste array build).

The first sheet in the workbook has a lot of information about general items as well as about specific pages. Please be sure to read through that entirely before working with the sheet, I think you’ll find that it helps a lot. The workbook is organized into sections and in steps. Complete the sheets in their numbered order, each sheet in the workbook has information that is used by drop-down boxes in later sheets. For example, when you fill in volume names in the planning sheet, those names automatically appear in the drop-down boxes of higher numbered sheets such as the volume collection sheet. For data that is a little more static, or is not Nimblestorage-based (such as naming conventions or volume size standards you may want to define), there is a TABLES tab that you update in section one. Those too will automatically appear in drop-downs elsewhere. When you finish entering data on the last page, go to the “FINAL CREATE CODE” sheet and click the GENERATE “CREATE” CODE button and all the code will appear.

 

 

October 2, 2013

Cisco IOS tips and tricks

Filed under: Cisco — Ray Olander @ 11:12 PM

Cisco command line options that I’ve found useful (I will add to this little by little):

  • Reset port gi1/0/1 to default:
    • conf t
    • default interface gi1/0/1
  • Reset ports 1-48 to default:
    • conf t
    • default int range gi1/0/1-48
  • Edit ports 1-48 at the same time
    • conf t
    • int range gi1/0/1-48
    • <enter config change>
  • Apply a config change to a non-contiguous range of ports
    • conf t
    • int range gi1/0/1 – 5 , gi1/0/10 – 15
    • <enter config change>
  • Do multiple steps in a single command when provisioning a port for an end-device
    • conf t
    • int gi1/0/1
    • switchport host
      • sets switch port mode to access + enables spanning tree Port Fast + disables channel grouping.
  • Use macros to run a script by entering a single command AND use variables
    • Create the macro
      • conf t
      • macro name SetToTrunk
      • shut
      • description TRUNK PORT
      • switchport trunk encapsulation dot1q
      • switchport mode trunk
      • switchport trunk native vlan $NATIVE
      • switchport trunk allowed vlan $ALLOWED
      • mls qos trust dscp
      • no shut
      • @
    • Now apply the macro to a port you want to create as a trunk, set the native VLAN to 123 and define all VLANs as being allowed
      • conf t
      • int gi1/0/1
      • macro apply Trunk $NATIVE 123 $ALLOWED all
  • Show all interfaces and the CRC count for each
    • show interface | i protocol|CRC
      • GigabitEthernet1/0/5 is up, line protocol is up (connected)
      • 0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored
  • Find what ports are not currently connected
    • sh int status | i notconnect
  • Show what ports are down due to BPDUguard
    • sh int status | i err
    • or
    • show log | I BPDU|bpdu
      • %SPANTREE-2-BLOCK_BPDUGUARD: Received BPDU on port GigabitEthernet1/0/16 with BPDU Guard enabled. Disabling port.
      • %PM-4-ERR_DISABLE: bpduguard error detected on Gi1/0/16, putting Gi1/0/16 in err-disable state
  • Modify an access-list that is referenced with a class-map and policy-map (basically, you need to delete from the top down and rebuild from the bottom up):
    • Steps explained
      • conf t
        • remove the policy within the policy map that references the class-map that references the access-list (access-group)
        • Remove the class-map that references the access-list (access-group)
        • do a no access-list to remove the ACL
        • Enter in the ACL statements
        • Enter in the class-map statements
        • change to policy-map
        • Enter in the policy-map statements
    • Steps performed
      • For this setup we’ll use this configuration
        • policy map MARK-LAN-DSCP
          • class EF-105-CLASS
            • set dscp ef
        • class map match-any EF-105-CLASS
          • match access-group 105
        • Access-list 105 permit ip host 192.168.1. any
        • Access-list 105 permit ip host 192.168.2.1 any
      • GOAL: To edit access-list 105 and change one of the source IP’s without the switch choking on itself
        • conf t
          • policy-map MARK-LAN-DSCP
          • no class EF-105-CLASS
          • exit (this takes you out of the pmap context)
          • no class-map match-any EF-105-CLASS
          • no access-list 105
          • access-list 105 permit ip host 10.0.0.1 any
          • access-list 105 permit ip host 192.168.2.1 any
          • class-map match-any EF-105-CLASS
          • match access-group 105
          • exit (gets you out of the cmap context)
          • policy-map MARK-LAN-DSCP
          • class EF-105-CLASS
          • set dscp ef
          • exit (leaves EF-105-CLASS context)
          • exit (leaves pmap context)
          • exit (leaves edit mode)
          • wr
  • Delete a directory
    • del /force /recursive directory_name
  • For all switches in a stack, define both a primary AND a secondary boot image
    • all flash:c3750-ipbasek9-mz.122-55.SE8.bin;flash:c3750-ipbasek9-mz.122-55.SE7.bin
  • Get extended details about the packets crossing an interface
    • show int gi2/0/39 controller (regular SH INT stats are also displayed, but are omitted here)
    Transmit GigabitEthernet2/0/39           Receive
    542890630 Bytes                        800171957 Bytes
      2196943 Unicast frames                 4155021 Unicast frames
        53197 Multicast frames                 26677 Multicast frames
         4615 Broadcast frames                  5009 Broadcast frames
            0 Too old frames               776174135 Unicast bytes
            0 Deferred frames               23587201 Multicast bytes
            0 MTU exceeded frames             410621 Broadcast bytes
            0 1 collision frames                   0 Alignment errors
            0 2 collision frames                   0 FCS errors
            0 3 collision frames                   0 Oversize frames
            0 4 collision frames                   0 Undersize frames
            0 5 collision frames                   0 Collision fragments
            0 6 collision frames
            0 7 collision frames              124415 Minimum size frames
            0 8 collision frames              161085 65 to 127 byte frames
            0 9 collision frames              104558 128 to 255 byte frames
            0 10 collision frames              67226 256 to 511 byte frames
            0 11 collision frames             170088 512 to 1023 byte frames
            0 12 collision frames            3559335 1024 to 1518 byte frames
            0 13 collision frames                  0 Overrun frames
            0 14 collision frames                  0 Pause frames
            0 15 collision frames
            0 Excessive collisions                 0 Symbol error frames
            0 Late collisions                      0 Invalid frames, too large
            0 VLAN discard frames                  0 Valid frames, too large
            0 Excess defer frames                  0 Invalid frames, too small
      1683840 64 byte frames                       0 Valid frames, too small
       251639 127 byte frames
        15423 255 byte frames                      0 Too old frames
        32565 511 byte frames                      0 Valid oversize frames
         8936 1023 byte frames                     0 System FCS error frames
       262352 1518 byte frames                     0 RxPortFifoFull drop frame
            0 Too large frames
            0 Good (1 coll) frames
            0 Good (>1 coll) frames
  • Show receive and transmit utilization for all ports
    • sh controller utilization
Port       Receive Utilization  Transmit Utilization
Gi1/0/1            0                    0
Gi1/0/2           10                    0
Gi1/0/3            0                    0
Gi1/0/4            0                   37
Gi1/0/5           20                    0
Gi1/0/6            0                    0
Gi1/0/7            0                    0
Gi1/0/8            0                    0
...
Gi2/0/52           0                    0
Total Ports : 104
Switch Receive Bandwidth Percentage Utilization  : 0
Switch Transmit Bandwidth Percentage Utilization : 0
Stack Ring Percentage Utilization : 0

January 22, 2012

Limiting vCenter Tomcat Usage

Filed under: vCenter, VMware — Tags: , , — Ray Olander @ 1:33 PM

Information obtained from from http://www.virtualserverguy.com/blog/2011/2/11/limiting-vcenter-tomcat-usage.html

LIMITING VCENTER TOMCAT USAGE

Here is how to limit the memory usage for VMware’s vCenter…

First vCenter relies on a customized Apache Tomcat to run its vCenter product (mostly graphs, Web Interface and other items), while limiting the memory hasn’t proven to have a negative effect this procedure should be done with caution. This can cause slowness in portions of the application since it will no longer be able to store large amounts of data in RAM and will have to access the database for this information.

1. Open Regedit on the vCenter server

2. Navigate to: HKLM\Software\Wow6432Node\Apache Software Foundation\Procrun 2.0\vctomcat\Paramaters\Java

3. Change JvmMS, JvmMX and JvmSs to ‘0’

4. Restart the VMware vCenter WebAccess Service in Windows and enjoy a smaller footprint of the Tomcat process.  The average RAM usage for Tomcat after this change should be around 256MB (plus or minus the amount of VM’s being managed).

Note: This procedure should only be done in test or small environments

###

My experience is that when I did this in my lab, memory usage went from 1.2GB to 170MB (3 ESXi hosts and 1 vCenter server). That’s a lot of savings when you only have a small amount of memory available to start with!

January 21, 2012

Converting a Cisco 1242AG lightweight AP to Standalone

Filed under: Cisco, Networking — Ray Olander @ 8:39 PM

So, it turns out that when you have an Cisco access point that starts with AIR-LAP instead of AIR-AP, it is configured to attach to a wireless controller on your network before it does anything useful. If you don’t happen to have a Cisco Wireless Controller configured and on your network to handle that, your access point is essentially a very expensive paper weight…or is it?!

It turns out that you can convert the AP from lightweight mode to standalone mode (and vice-versa actually).  I am going to list the steps I took to do this because I didn’t find any articles on the Internet to tell me how, but don’t let this fool you – I am not a Cisco guy. If I didn’t write it down here, I likely don’t have any additional answers on this topic other than “if it doesn’t work, get a bigger hammer” :)

So here we go:

PC Setup

Download TFTPD (TFTP server) and configure to point to some directory (which will be your “base” directory) and to allow it to operate through your firewall.

Download IOS software for the 1242 (I used c1240-k9w7-mx.124-25d.JA1 for this experiment)

Make a copy of the IOS file named “c1240-k9w7-tar.default” in the TFTP base directory (The AP will look for this exact file name)

Download and run Putty. Create a session to COM1 9600/8-N-1 (I turn on logging and set the scrollback buffer to 9999 lines)

Configure a PC for 10.0.0.2/255.0.0.0 and either connect to a switch dedicated for this purpose or use a crossover cable (the AP will default to 10.0.0.1)

Connect the Cisco blue console cable between COM1 on the PC and the console port on the AP

Open the Putty COM1 session to monitor the AP when it comes up

AP setup

Connect 2GHz (cone shaped) and 5GHz (flat) antennas to the appropriate connectors on the AP

Connect the access point to a switch or crossover cable

Load new IOS on AP

Press and hold the MODE button and plug in the power

When you see “Button is pressed, wait for button to be released” in your Putty session, let go of the button

The AP will startup, default to 10.0.0.1 and look for a TFTP server that has “c1240-k9w7-tar.default” ready to serve

The AP will automatically download the new IOS and delete the lightweight image (mine was “c1240-rcvk9w8-mx”)

You should see something like “Download default IOS tar image tftp://10.0.0.2/c1240-k9w7-tar.default”

Once the AP has fully started, do the following to get the Ethernet interface activated

ap>en

Password: Cisco (this is case sensitive)

ap# config t

ap(config)# interface FastEthernet 0

ap(config)# ip addr 10.0.0.1 255.0.0.0 (or perhaps what IP it will eventually have on your production network)

ap(config)# interface BVI1

ap(config)# ip addr 10.0.0.3 255.0.0.0 (same network as the Ethernet interface)

press ctrl+z to exit config

type wr to write the config

Go to your PC and open http://10.0.0.1 to get to the web interface of the AP to finish configuring the device.

The username is Cisco and the password is Cisco (both are case-sensitive)

If you changed the IP address to something other than 10.0.0.1 & 10.0.0.3 above, be sure to change your PC IP address too before trying to access the configuration webpage.

I hope this helped someone!

Create a free website or blog at WordPress.com.