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.

 

 

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, you MUST use the same row as the top of your target array. If you used A:G as your array (meaning column A through Column G), the top of the array is row 1. With MATCH=3, you would return 465 using A:G.

 

So we find the row we’re looking for based on the value input from cell B8 (forgot to include a screenshot, B8 value is 897)

=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 INDEX offset value:

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

Result = Accord (Done!)

NOTE: For this example, C1:G5 would work equally as well as A1:G5 because we’re only looking for the Model information. As an even shorter reference, A:G and C:G would be a viable replacement.

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!!!!

Blog at WordPress.com.