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.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState