Work the Shell - How Do People Find You on Google?

Getting back to Apache log analysis by ending with a cliffhanger.

I admit it. I got sidetracked last month talking about how you can use a simple shell script function to convert big scary numbers into more readable values that are understandable. Sidetracked because we were in the middle of looking at how shell scripts can help you dig through your Apache Web server logs and extract useful and interesting information.

This time, I show how you can ascertain the most common search terms that people are using to find your site—with a few invocations of grep and maybe a few lines of awk for good measure.

Understanding Google

For this to work, your log has to be saving referrer information, which Apache does by default. You'll know if you peek at your access_log and see lines like this:

195.110.84.91 - - [11/Oct/2006:04:04:19 -0600] "GET
↪/blog/images/rdf.png HTTP/1.0" 304 -
↪"http://www.askdavetaylor.com/date_math_in_linux_shell_script.html"
↪"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"

It's a bit hard to read, but this is a log entry for someone requesting the file /blog/images/rdf.png, and the referrer, the page that produced the request, is also shown as being date_math_in_linux_shell_script.html from my askdavetaylor.com site.

If we look at a log file entry for an HTML hit, we see a more interesting referrer:

81.208.53.251 - - [11/Oct/2006:07:32:32 -0600]
 ↪"GET /wicked/wicked-cool-shell-script-library.shtml
 ↪HTTP/1.1" 200 15656 "http://www.google.com/
↪search?q=Shell+Scripting+&hl=it&lr=&start=10&sa=N"
 ↪"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
 ↪.NET CLR 1.0.3705)"

Let me unwrap that just a bit too. The request here is for wicked-cool-shell-script-library.html on my site (intuitive.com), based on a Google search (the referrer is google.com/search). Dig into the arguments on the Google referrer entry, and you can see that the search was “Shell+Scripting”. Recall that + represents a space in a URL, so the search was actually for “Shell Scripting”.

(Bonus tip: because we're at start=10, this means they're on the second page of results. So, we know the match that led this person to my site is somewhere between #11 and #20.)

Okay, so now the question is, can we extract only these searches and somehow disassemble them so we can identify the search terms quickly? Of course we can!

Extracting Google Searches

For now, let's focus only on Google's search results, but it's easy to extend this to other search engines too. They all use the same basic URL structure, fortunately:

$ grep 'google.com/search' access_log | head -1
168.230.2.30 - - [11/Oct/2006:04:08:05 -0600]
 ↪"GET /coolweb/chap14.html HTTP/1.1" 200 31508
 ↪"http://www.google.com/search?q=%22important+Style+Sheet+
↪Attribute.%22&hl=en&lr=" "Mozilla/4.0 (compatible;
 ↪MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322;
 ↪.NET CLR 2.0.50727; InfoPath.1)"

Okay, that was simple. Now, extracting only the referrer field is easily done with a quick call to awk:

$ grep 'google.com/search' access_log | head -1 | awk '{print $11}'
"http://www.google.com/search?q=%22important+Style+Sheet
↪+Attribute.%22&hl=en&lr="

Okay, closer. The next step is to chop off the value at the ? and then at the & afterward. There are a bunch of ways to do this, but I use only two calls to cut, because, well, it's easy:

$ grep 'google.com/search' access_log | head -1 | awk
 ↪'{print $11}' | cut -d\? -f2 | cut -d\& -f1
q=%22important+Style+Sheet+Attribute.%22

Nice! Now, we need to strip out the q= artifact from the HTML form used on Google itself, replace all occurrences of + with a space, and (a little bonus task) convert %22 into a double quote so the search makes sense. This can be done with sed:

$ grep 'google.com/search' access_log | head -1 |
 ↪awk '{print $11}' | cut -d\? -f2 | cut
 ↪-d\& -f1 | sed 's/+/ /g;s/%22/"/g;s/q=//'
"important Style Sheet Attribute."

Let me unwrap this a bit so it's easier to see what's going on:

grep 'google.com/search' access_log | \
  head -1 | \
  awk '{print $11}' | \
  cut -d\? -f2 | cut -d\& -f1 | \
  sed 's/+/ /g;s/%22/"/g;s/q=//'

Obviously, the head -1 is only there as we debug it, so when we pour this into an actual shell script, we'll lose that line. Further, let's create a variable for the name of the access log to simplify things too:

#!/bin/sh

ACCESSLOG="/var/logs/httpd.logs/access_log"

grep 'google.com/search' $ACCESSLOG | \
  awk '{print $11}' | \
  cut -d\? -f2 | cut -d\& -f1 | \
  sed 's/+/ /g;s/%22/"/g;s/q=//'

We're getting there....

______________________

Dave Taylor has been hacking shell scripts for over thirty years. Really. He's the author of the popular "Wicked Cool Shell Scripts" and can be found on Twitter as @DaveTaylor and more generally at www.DaveTaylorOnline.com.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix