http:\/\/httpd.apache.org\/<\/a>. You can read about the format of Apache log files at http:\/\/httpd.apache.org\/docs\/1.3\/logs.html#common.<\/p>\nIn this problem, you are taking on the role of a security analyst who is checking into some possible irregular traffic hitting your servers.<\/p>\n
<\/strong><\/p>\nBrowse the file to get sense of structure<\/p>\n
\n- IP address (or domain name)<\/li>\n
- date time<\/li>\n
- HTTP command (GET, POST, HEAD, …)<\/li>\n
- Filename\/URL<\/li>\n
- return code (2xx is good, 3xx is redirect, 4xx is error, 5xx is ?)<\/li>\n
- data size returned<\/li>\n<\/ul>\n
Part 3.1 \u2013 Manually import log file into Excel<\/strong><\/p>\nImport the access_log.txt file into Excel. Import it as a TAB DELIMITED file so that each record gets imported into Column A in your spreadsheet. Here\u2019s a screenshot of the top of my Excel file:<\/p>\n
<\/strong><\/p>\n<\/strong><\/p>\nSave your file as apache_log_functions.xlsm<\/strong> (yes, as a macro-enabled workbook). You\u2019ll be doing all the work for this problem in this file and you\u2019ll be turning it in as part of the assignment.<\/p>\n<\/strong><\/p>\nPart 3.2 \u2013 Creating a function to extract date and time from a log file line<\/strong><\/p>\nLet\u2019s assume you want to be able to quickly filter lines by dates or times of the log line. You\u2019ll see there is a datetime stamp right after a left bracket \u2013 \u201c[\u201c. To make this easy you are going to create a simple VBA function that returns the datetime value for a single line passed into the function. Then you could use this function in a worksheet formula in some column to the right of your data. Each row containing the string of interest will end up with a datetime value and then you could Autofilter the whole range and just see the records of interest. For lines which don\u2019t contain dates, we\u2019ll have our function return a zero.<\/p>\n
This function will be called GetApacheDateTime. It will take one input arguments. The first is a string representing an Apache log line. To help you out, here\u2019s a code skeleton. I STRONGLY SUGGEST YOU USE THIS CODE SKELETON.<\/strong><\/p>\n<\/strong><\/p>\nRemember, you can and should debug and test your user defined function using the Immediate Window, or the spreadsheet itself, or a \u201ctester Sub\u201d. See the Variables and Procedure Basics<\/strong> screencast <\/strong>which covers creating, using and testing user defined functions. <\/p>\nIMPORTANT<\/strong>: Remember, functions take input arguments and return values. As you can see from the screenshot above, the first input argument is a string. So, your function should NOT reference anything else in the workbook or worksheet other than the argument passed in. That\u2019s the whole point of functions. Also, as you can see from the function declaration, your function will simply return an Excel datetime value. It will NOT explicitly modify the spreadsheet. The spreadsheet will get modified when you use the function in a cell. A user defined function is just like any other Excel function. <\/p>\nOnce you have it working, use it in formulas in Column B and then filter so that only those records from March 9, 2004, are shown. Here\u2019s what the answer will look like.<\/p>\n
<\/strong><\/p>\n
<\/strong> <\/p>\n<\/strong><\/p>\nHacker Extra \u2013 Grabbing the HTTP command<\/strong><\/p>\nWhile you are pretty excited about your GetApacheDateTime() function, you decide you\u2019d like to also have a function that returns the HTTP command – e.g. GET or POST or whatever, along with the URL. For example, from a line that looks like this:<\/p>\n
195.246.13.119 – – [09\/Mar\/2004:01:48:28 -0800] “GET \/favicon.ico HTTP\/1.1” 200 1078<\/p>\n
you\u2019d like your function to return <\/p>\n
GET \/favicon.ico <\/p>\n
as a string. Notice we do NOT want the HTTP\/1.1. We just want the HTTP command and the URL. We\u2019ll call this function GetApacheHTTPCommand(). Just like the previous function, it takes a string representing the log line as its only input. As in the previous part, if you get this working, use it in Column C to show me that it works. Here\u2019s my output.<\/p>\n
Since this is a Hacker Extra, no screenshot. But, here\u2019s a few hints:<\/p>\n
\n- Obviously the HTTP command along with the URL appears between the double quotes.<\/li>\n
- There\u2019s a space between the HTTP command and the URL.<\/li>\n
- The URL ends with the last character before a space.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
I\u2019m trying to study for my Excel course and I need some help to understand this question. Problem 1: Super Coder [20 points] You\u2019ve just joined a new business analytics…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_wpscppro_dont_share_socialmedia":false,"_wpscppro_custom_social_share_image":0,"_facebook_share_type":"","_twitter_share_type":"","_linkedin_share_type":"","_pinterest_share_type":"","_linkedin_share_type_page":"","_instagram_share_type":"","_medium_share_type":"","_threads_share_type":"","_google_business_share_type":"","_selected_social_profile":[],"_wpsp_enable_custom_social_template":false,"_wpsp_social_scheduling":{"enabled":false,"datetime":null,"platforms":[],"status":"template_only","dateOption":"today","timeOption":"now","customDays":"","customHours":"","customDate":"","customTime":"","schedulingType":"absolute"},"_wpsp_active_default_template":true},"categories":[1061,1],"tags":[],"class_list":["post-245812","post","type-post","status-publish","format-standard","hentry","category-nursing-essays","category-nursing-paper"],"_links":{"self":[{"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/posts\/245812","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/comments?post=245812"}],"version-history":[{"count":3,"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/posts\/245812\/revisions"}],"predecessor-version":[{"id":274710,"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/posts\/245812\/revisions\/274710"}],"wp:attachment":[{"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/media?parent=245812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/categories?post=245812"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nursingstudy.org\/examples\/wp-json\/wp\/v2\/tags?post=245812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}