4

I have a server log file that contains a number of log entries in the following format:

193.1.172.46 - - [23/Mar/2008:03:57:38 +0000] "GET /robots.txt HTTP/1.0" 404 289 "-" "gsa-crawler (Enterprise; M2-N7RQ5RABCA2JT; unix@ucd.ie,fergal@ucd.ie)"

I've been asked to identify all of the entries that use Google's search engine and then take the query string from these, and only display the query string in the output.

So I've used the grep command to identify all entries that access the search engine like this:

 grep "http://www.google.com/search?" logs.txt 

which gives me a list of entries like this:

143.183.121.3 - - [23/Mar/2008:00:16:59 +0000] "GET /staff/jcarthy/home/2ndYearUnix/usefulcommands2col.pdf HTTP/1.0" 200 78866 "http://www.google.com/search?hl=en&q=frequently+used+unix+aliases&btnG=Google+Search"; "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; InfoPath.1)"

How do I now just display the list with only the hl=en&q=frequently+used+unix+aliases&btnG=Google+Search part of the entry displayed?

1

Here'a fairly readable sed approach

$ cat log.txt | grep "http://www.google.com/search?" | sed  s/^.*search?// | sed s/\"\;.*//

i.e.

Remove the start of the line with:

s/   # replace a match which is:
  ^       # from the start of the line
  .*      # any number of any characters
  search? # the text "search?"
//   # with nothing (remove it)

then remove the end of the line with

s/    # replace a match which is:
  \"    # a double quote (escaped with backslash)
  \;    # a semicolon (escaped with backslash)
  .*    # any number of characters
//    # with nothing (remove it)

leaving just the params

  • 1
    Thanks, this code works well. Would you be kind enough to explain the syntax you've used with sed? – Joe Nov 14 '16 at 12:50
  • 1
    Sure, I've updated the answer with a detailed explanation. – Michael Durrant Nov 15 '16 at 0:11
  • This approach does not verify that search? is actually in the referrer field and not in some other field of the log file such as the request or user agent. Also, it may prematurely truncate referrers containing the text "; (although most but not all user agents will urlencode these). This is aside from syntax problems with the command such as not escaping the special regex character ? and the shell glob character *, which is sloppy. My answer is much more technically correct, though I admit it is less readable. – codebeard Nov 16 '16 at 5:54
4

All of the other solutions here are likely to fail on some log entries e.g. ones with spaces inside the referrer field or extra quotes and backslashes, upper case domain names, https instead of http, or keywords inside the location field as well as the referrer field.

For example:

1.2.3.4 - - [23/Mar/2008:00:16:59 +0000] "GET /a b/ HTTP/1.0" 200 0 "http://www.google.com/search?..." "Mozilla/4.0"
1.2.3.4 - - [23/Mar/2008:00:16:59 +0000] "GET /i/love/http://www.google.com/search?ing HTTP/1.0" 200 0 "http://www.google.com/search?..." "Mozilla/4.0"
1.2.3.4 - - [23/Mar/2008:00:16:59 +0000] "GET / HTTP/1.0" 200 0 "http://www.google.com/search?spaces in referrer" "Mozilla/4.0"
1.2.3.4 - - [23/Mar/2008:00:16:59 +0000] "GET /nohttpver" 200 0 "http://www.google.com/search?spaces in referrer" "Mozilla/4.0"
1.2.3.4 - - [23/Mar/2008:00:16:59 +0000] "GET /" 200 0 "http://example.org/http://www.google.com/search?spaces in referrer" "Mozilla/4.0"
1.2.3.4 - - [23/Mar/2008:00:16:59 +0000] "GET /" 200 0 "http://WWW.GOOGLE.COM/search?spaces in referrer" "Mozilla/4.0"

To deal with these, we first need to properly extract the second double-quoted field. Note that Apache log files use backslashes to escape extra quotes or other special characters. This means naive regular expressions such as "[^"]*" aren't good enough.

Using grep to extract the referrer field (second double-quoted field):

grep -oP '^[^"]+"[^"\\]*(?:\\.[^"\\]*)*"[^"]+"\K[^"\\]*(?:\\.[^"\\]*)*(?=")' logfile.txt

Looks crazy! Let's break it down:

  • The o argument to grep means we just get the matching part of the line, not the rest of it
  • The P argument to grep tells it to use Perl-compatible regular expressions
  • The overall structure of the regular expression in use here, ...\K...(?=...), means we are checking the whole pattern but only the things between the \K and the (?=...) will be output

Breaking the regular expression down further:

  1. ^[^"]+ – Get everything between the start of the line and the first "
  2. "[^"\\]*(?:\\.[^"\\]*)*" – Get the entire first double-quoted string. See this answer https://stackoverflow.com/a/5696141/1764245
  3. [^"]+ – Get everything between the two strings
  4. "\K[^"\\]*(?:\\.[^"\\]*)*(?=") The same as above, but we have the \K after the first " to start matching data after that and the (?=") to stop matching data before the last ".

After this point the data will be much easier to process because you no longer have to worry about the quotes and extracting the field properly from the log file.

For example, you could pipe the output into another grep:

grep -oP ... logfile.txt | grep -oPi '^https?://www\.google\.com/search\?\K.*'

Here the i option to the second grep makes it case-insensitive.

Alternatively, you could add the check for the the start of the google.com referrer directly into the first regular expression and move the \K as appropriate, but I would recommend against this since it's better to run two regular expressions which both do one job and do it well than to combine them into one where its job is not clear.

Note that if you want to collect referrers from other Google domains you will need to modify your regular expression a fair bit. Google owns a lot of search domains.

If you didn't mind potentially catching a few non-Google sites, you could do:

... | grep -oPi '^https?://(www\.)?google\.[a-z]{2,3}(\.[a-z]{2})?/search\?\K.*'

Otherwise you would need to attempt to match only Google-owned search domains, which is a constantly moving target:

... | grep -oPi '^https?://(www\.)?google\.(a[cdelmstz]|b[aefgijsty]|cat|c[acdfghilmnvz]|co\.(ao|bw|c[kr]|i[dln]|jp|k[er]|ls|m[az]|nz|t[hz]|u[gkz]|v[ei]|z[amw])|com(\.(a[fgiru]|b[dhnorz]|c[ouy]|do|e[cgt]|fj|g[hit]|hk|jm|k[hw]|l[bcy]|m[mtxy]|n[afgip]|om|p[aeghkry]|qa|s[abglv]|t[jrw]|u[ay]|v[cn]))?|d[ejkmz]|e[es]|f[imr]|g[aefglmpry]|h[nrtu]|i[emoqst]|j[eo]|k[giz]|l[aiktuv]|m[degklnsuvw]|n[eloru]|p[lnst]|r[osuw]|s[cehikmnort]|t[dgklmnot]|us|v[gu]|ws)/search\?\K.*'

Also note if you want to include Google's image search and other search subdomains, you will need to change the (www\.)? in one of the above grep commands to something like ((www|images|other|sub|domains)\.)?.

3

a generic version

awk '$11 ~ /?/ { printf "%s\n",substr($11,1+index($11,"?")) ;}'

where

  • $11 ~ /\?/ search for ? in URL
  • substr($11,1+index($11,"?") search part after ?
  • note that argument are not parsed.
  • this will not unescape URL (e.g. space will be shown as %20 )

previous version

awk '$11 ~ /http:\/\/www.google.com\/search?/ { print substr($11,26) ;}' 

where

  • This has a number of significant problems. 1) it is unnecessarily case sensitive. 2) it doesn't terminate the regex. 3) it doesn't anchor the regex. 4) it doesn't escape the special regex characters . and ?. 5) it does not handle URLs which contain whitespace as awk will break it into the next field. 6) it is hard to tell at a glance that 26 is the correct parameter for substr, especially since it should be 31 not 26. 7) it includes the trailing double quote and semicolon from the logfile. 8) it only works for searches at www.google.com (not images etc or on other TLDs) – codebeard Nov 13 '16 at 23:59
  • @codebeard I fixed some point; however there are better written log tool parser tha a mere awk or grep/cut/sed – Archemar Nov 14 '16 at 6:36
  • You are right, it's not easy to do with awk et al., though it should be possible with a complex regular expression (i.e. one that handles any escaped double quotes). Note that spaces in Apache log files are not necessarily shown as %20, it entirely depends on what the client sends. If the client sends "GET /foo bar" then the space will appear verbatim in the log file. This makes awk's field splitting poorly suited to the task. – codebeard Nov 14 '16 at 7:55
  • My answer below should handle this properly. unix.stackexchange.com/a/323108/21989 – codebeard Nov 14 '16 at 9:26
0

I think I have figured it out.

grep "http://www.google.com/search?" logs.txt | cut -d" " -f11 |  sed -r 's/^.{30}//'

Does this seem like an acceptable solution?

  • I'd seek a regular expression match rather than use 30 characters so you can use the approach for different URL's as long as they say "; as a match as shown in my answer. – Michael Durrant Nov 14 '16 at 2:19
0
awk -F"[?|;]" '/google.com\/search/{print $2}' log.txt

awk -F? '/google.com\/search/{gsub(";.*","",$2);print $2}' log.txt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.