VirtuallyUseful – Random Tech Babble

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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: