Calculating the Average Hits per minute en-mass for thousands of sites

So, I had a customer having some major MySQL woes, and I wanted to know whether the MySQL issues were query related, as in due to the frequency of queries alone, or the size of the database. VS it being caused by the number of visitors coming into apache, therefore causing more frequency of MySQL hits, and explaining the higher CPU usage.

The best way to achieve this is to inspect /var/log/httpd with ls -al,

First we take a sample of all of the requests coming into apache2, as in all of them.. provided the customer has used proper naming conventions this isn’t a nightmare. Apache is designed to make this easy for you by the way it is setup by default, hurrah!

[root@box-DB1 logparser]# time tail -f /var/log/httpd/*access_log > allhitsnow
^C

real	0m44.560s
user	0m0.006s
sys	0m0.031s

Time command prefixed here, will tell you how long you ran it for.

[root@box-DB1 logparser]# cat allhitsnow | wc -l
1590

The above command shows you the number of lines in allhitsnow file, which was written to with all the new requests coming into sites from all the site log files. Simples! 1590 queries a minute is quite a lot.

Setting X-Frame-Options HTTP Header to allow SAME or NON SAME ORIGINS

It’s possible to increase the security of a webserver running a website, by ensuring that the X-FRAME-OPTIONS header pushes a header to the browser, which enforces the origin (server) serving the site. It prevents the website then providing objects which are not local to the site, in the stream. An admirable option for those which wish to increase their server security.

Naturally, there are some reasons why you might want to disable this, and in proper context, it can be secure. Always be sure to discuss with your pentester or PCI compliance officer, such considerations before proceeding, especially making sure that if you do not want to use SAME ORIGIN you always use the most secure option for the required task. Always check if there is a better way to achieve what your trying to do, when making such changes to your server configuration.

Insecure X-Frame-Option allows remote non matching origins

Header always append X-Frame-Options ALLOWALL

Secure X-Frame-Option imposes on the browser to not allow non origin(al) connections for the domain, which can prevent clickjack and other attacks.

Header always append X-Frame-Options SAMEORIGIN

Moving a WordPress site – much ado about nothing !

Have you noticed, there is all kinds of advise on the internet about the best way to move WordPress websites? There is literally a myriad of ways to achieve this. One of the methods I read on
wp.com was:

Changing Your Domain Name and URLs

Moving a website and changing your domain name or URLs (i.e. from http://example.com/site to http://example.com, or http://example.com to http://example.net) requires the following steps - in sequence.

    Download your existing site files.
    Export your database - go in to MySQL and export the database.
    Move the backed up files and database into a new folder - somewhere safe - this is your site backup.
    Log in to the site you want to move and go to Settings > General, then change the URLs. (ie from http://example.com/ to http://example.net ) - save the settings and expect to see a 404 page.
    Download your site files again.
    Export the database again.
    Edit wp-config.php with the new server's MySQL database name, user and password.
    Upload the files.
    Import the database on the new server.

I mean this is truly horrifying steps to take, and I don’t see the point at all. This is how I achieved it for one my customers.

1. Take customer Database Dump
2. Edit the database searching for 'siteurl' with vi
vi mysqldump.sql
:?siteurl

And just swap out the values, confirming after editing the file;

[root@box]# cat somemysqldump.sql  | grep siteurl -A 2
(1, 'siteurl', 'https://www.newsiteurl.com', 'yes'),
(2, 'home', 'https://www.newsiteurl.com', 'yes'),
(3, 'blogname', 'My website name', 'yes'),

Job done, no stress https://codex.wordpress.org/Moving_WordPress.

There might be additional bits but this is certainly enough for them to access the wp-admin panel. If you have problems add this line to the wp-config.php file;

define('RELOCATE',true);

Just before the line which says

/* That’s all, stop editing! Happy blogging. */

And then just do the import/restore as normal;

mysql -u newmysqluser -p newdatabase_to_import_to < old_database.sql

Simples! I really have no idea why it is made to be so complicated on other hosting sites or platforms.

Plesk website running FastCGI Timeout Gateway Errors and Slow Loads

So my friend Paul shown me how to troubleshoot fastCGI on plesk boxes, very easy..

# pstree | grep cgi
     |       `-httpd---20*[php-cgi]

We can see that 20 php-cgi processes run. If we check the maximum in the configuration file set for fastCGI of Apache2..

# cat /etc/httpd/conf.d/fcgid.conf  | grep MaxProc
  FcgidMaxProcesses 20

We see 20 is the maximum, so it’s definitely hitting the FastCGI limit, we need to increase the limits, so we just edit the file and increase the limits for that variable;

vi /etc/httpd/conf.d/fcgid.conf

echo "FcgidMaxProcesses 50" >> /etc/httpd/conf.d/fcgid.conf

Setting a seperate memory limit for PhpMyAdmin to the rest of the sites

A common issue I see Rackspace customers with is there PhpMyAdmin not having enough memory, often I ‘ll see countless tickets where the memory_limit is increased for phpmyadmin, and when one of their virtualhosts falls over, it is then decreased for all of the sites, until someone wants to use phpmyadmin again.

not very good really is it? Actually, fixing this is quite easy. Lets provide a php.ini for phpmyadmin that only phpmyadmin uses;


# Copy original php configuration
cp /etc/php.ini /usr/share/phpMyAdmin/php.ini

# Modify /usr/share/phpMyAdmin/php.ini so that the following variable is set as a higher value
memory_limit = 256M

Naturally if you now goto the customerwebsite.com/phpmyadmin/php.ini you’ll see a nice php file waiting for you… not good… we need to protect the php.ini file as it can expose stuff we don’t want to be seen; lets make it as hard to find out the server configuration and hide php.ini altogether.

# File to edit may differ but it can be any file inside conf.d make one if you like call it phpini.conf or something
vi /etc/httpd/conf.d/php.conf
<Files php.ini>
          Order allow,deny
          Deny from all
</Files>

Dont’t forget the most important step

# Check apache syntax
apachectl -t

# Restart the apache process
apachectl graceful

Another pretty simples thing to do. That isn’t pretty simple until you do it.

Less Ghetto Log Parser for Website Hitcount/Downtime Analysis

Yesterday I created a proof of concept script, which basically goes off and identifies the hitcounts of a website, and can give a technician within a short duration of time (minutes instead of hours) exactly where hitcounts are coming from and where.

This is kind of a tradeoff, between a script that is automated, and one that is flexible.

The end goal is to provide a hitcount vs memory commit metric value. A NEW TYPE OF METRIC! HURRAH! (This is needed by the industry IMO).

And also would be nice to generate graphing and mean, average, and ranges, etc. So can provide output like ‘stat’ tool. Here is how I have progress

#!/bin/bash
#
# Author: 	Adam Bull, Cirrus Infrastructure, Rackspace LTD
# Date: 	March 20 2017
# Use:		This script automates the analysis of webserver logs hitcounts and
# 		provides a breakdown to indicate whether outages are caused by website visits
#		In correlation to memory and load avg figures


# Settings

# What logfile to get stats for
logfile="/var/log/httpd/google.com-access.log"

# What year month and day are we scanning for minute/hour hits
year=2017
month=Mar
day=9

echo "Total HITS: MARCH"
grep "/$month/$year" "$logfile" | wc -l;

# Hours
for i in 0{1..9} {10..24};

do echo "      > 9th March 2017, hits this $i hour";
grep "$day/$month/$year:$i" "$logfile" | wc -l;

        # break down the minutes in a nested visual way thats AWsome

# Minutes
for j in 0{1..9} {10..60};
do echo "                  >>hits at $i:$j";
grep "$day/$month/$year:$i:$j" "$logfile" | wc -l;
done

done

Thing is, after I wrote this, I wasn’t really happy, so I refactored it a bit more;

#!/bin/bash
#
# Author: 	Adam Bull, Cirrus Infrastructure, Rackspace LTD
# Date: 	March 20 2017
# Use:		This script automates the analysis of webserver logs hitcounts and
# 		provides a breakdown to indicate whether outages are caused by website visits
#		In correlation to memory and load avg figures


# Settings

# What logfile to get stats for
logfile="/var/log/httpd/someweb.biz-access.log"

# What year month and day are we scanning for minute/hour hits
year=2017
month=Mar
day=9

echo "Total HITS: $month"
grep "/$month/$year" "$logfile" | wc -l;

# Hours
for i in 0{1..9} {10..24};

do
hitsperhour=$(grep "$day/$month/$year:$i" "$logfile" | wc -l;);
echo "    > $day $month $year, hits this $ith hour: $hitsperhour"

        # break down the minutes in a nested visual way thats AWsome

# Minutes
for j in 0{1..9} {10..59};
do
hitsperminute=$(grep "$day/$month/$year:$i:$j" "$logfile" | wc -l);
echo "                  >>hits at $i:$j  $hitsperminute";
done

done

Now it’s pretty leet.. well, simple. but functional. Here is what the output of the more nicely refined script; I’m really satisfied with the tabulation.

[root@822616-db1 automation]# ./list-visits.sh
Total HITS: Mar
6019301
    > 9 Mar 2017, hits this  hour: 28793
                  >>hits at 01:01  416
                  >>hits at 01:02  380
                  >>hits at 01:03  417
                  >>hits at 01:04  408
                  >>hits at 01:05  385
^C

Migrating a Plesk site after moving keeps going to default plesk page

So today a customer had this really weird issue where we could see that the website domain that had been moved from one server to a new plesk server, wasn’t correctly loading. It actually turned out to be simple, and when trying to access a file on the domain like I would get the phpinfo.php file.

curl http://www.customerswebsite.com/info.php 

This suggested to me the website documentroot was working, and the only thing missing was probably the index. This is what it actually did turn out to me.

I wanted to test though that info.php really was in this documentroot, and not some other virtualhost documentroot, so I moved the info.php file to randomnumbers12313.php and the page still loaded, this confirms by adding that file on the filesystem that all is well, and that I found correct site, important when troubleshooting vast configurations.

I also found a really handy one liner for troubleshooting which file it comes out, this might not be great on a really busy server, but you could still grep for your IP address as well.

Visit the broken/affected website we will troubleshoot

curl -I somecustomerswebsite.com

Give all visitors to all apache websites occurring now whilst we visit it ourselves for testing

tail -f /var/log/httpd/*.log 

This will show us which virtualhost and/or path is being accessed, from where.

Give only visitors to all apache websites occurring on a given IP

tail -f /var/log/httpd/*.log  | grep 4.2.2.4

Where 4.2.2.4 is your IP address your using to visit the site. If you don’t know what your Ip is type icanhazip into google, or ‘what is my ip’, job done.

Fixing the Plesk website without a directory index

[root@mehcakes-App1 conf]# plesk bin domain --update somecustomerswebsite.com -nginx-serve-php true -apache-directory-index index.php

Simple enough… but could be a pain if you don’t know what your looking for.

QID 150004 : Path-Based Vulnerability

A customer of ours had an issue with some paths like theirwebsite.com/images returning a 200 OK, and although the page was completely blank, and exposed no information it was detected as a positive indicator of exposed data, because of the 200 OK.

more detail: https://community.qualys.com/thread/16746-qid-150004-path-based-vulnerability

Actually in this case it was a ‘whitescreen’, or just a blank index page, to prevent the Options +indexes in the apache httpd configuration showing the images path. You probably don’t want this and can just set your Option indexes.

Change from:

Options +Indexes
# in older versions it may be defined as
Options Indexes

Change to:

Options -Indexes

This explicitly forbids, but older versions of apache2 might need this written as:

Options Indexes

To prevent an attack on .htaccess you could also add this to httpd.conf to ensure the httpd.conf is enforced and takes precedence over any hacker or user that adds indexing incorrectly/mistakenly/wrongly;

<Directory />
    Options FollowSymLinks
    AllowOverride None
</Directory>

Simple enough.

/etc/apache2/conf.d/security – Ubuntu 12.04.1 – Default File exploitable

In Ubuntu 12.04.1 there were some rather naughty security updates in specific, /etc/apache2/conf.d/security file has important lines commented out:

#<Directory />
#        AllowOverride None
#        Order Deny,Allow
#        Deny from all
#</Directory>

These above lines set the policy for the /var/www/ directory to forbid all access, then being commented out means that the policy is not forbidding access by default.

This is not good. In our customers case, they also had A listen 443 directive in their ports.conf, however they hadn’t added any default virtualhosts using port 443 SSL. This actually means that the /var/www directory becomes the ‘/’ for default HTTPS negotiation to the site. NOT GOOD since if directory listing is also available it will list the contents of /var/www as well, as exposing files that can be directly accessed, the directory listing option will make it possible to see all files listed, instead of just opening up the files in /var/www/ for access via http://somehost.com/somefileinvarwww.sql naturally its much harder if the attacker has to guess the files, but still, not good!

NOT GOOD AT ALL. If the customer has a /var/www/vhosts/sites and is using /var/www for their database dumps or other files it means those files could be retrieved.

The fix is simple, remove these lines from /etc/apache2/ports.conf,

Change from

Listen 443
NameVirtualHost *:443 

Change to

#Listen 443
#NameVirtualHost *:443 

Also make sure that the secure file (/etc/apache2/conf.d/secure) doesn’t have these lines commented as Ubuntu 12.04.1 and 12.10 may suffer; this is actually the main issue

Change from:

#<Directory />
# AllowOverride None
# Order Deny,Allow
# Deny from all
#</Directory>

Change to:

<Directory />
 AllowOverride None
 Order Deny,Allow
 Deny from all
</Directory>

Restart your apache2

# Most other OS
service apache2 restart
/etc/init.d/apache2 restart

# CentOS 7/RHEL7
systemctl restart apache2

This exploitable/vulnerable configuration was changed in later updates to the apache configurations in Ubuntu, however it appears for some people there are packages being held back for a couple of reasons. First, it appears that this server was initially deployed on Ubuntu 12.10, which is a short-term release that reached end of life May 16, 2014. As the dist-upgrade path for Ubuntu 12.10 would be to the LTS Trusty Tahr release, which reaches end of life this May.

I suspect that a significant contributor to the issue was that the releases were unsupported by the vendor at the time of being affected. The customer also used the vulnerable ports.conf file it appears with a deployment of chef.

For more information see:

http://mixeduperic.com/downloads/org-files/ubuntu/etcapache2confdsecurity-ubuntu-12041-default-file.html

Site keeps on going down because of spiders

So a Rackspace customer was consistently having an issue with their site going down, even after the number of workers were increased. It looked like in this customers case they were being hit really hard by yahoo slurp, google bot, a href bot, and many many others.

So I checked the hour the customer was affected, and found that over that hour just yahoo slurp and google bot accounted for 415 of the requests. This made up like 25% of all the requests to the site so it was certainly a possibility the max workers were being reached due to spikes in traffic from bots, in parallel with potential spikes in usual visitors.

[root@www logs]#  grep '01/Mar/2017:10:' access_log | egrep -i 'www.google.com/bot.html|http://help.yahoo.com/help/us/ysearch/slurp' |  wc -l
415

It wasn’t a complete theory, but was the best with all the available information I had, since everything else had been checked. The only thing that remains is the number of retransmits for that machine. All in all it was a victory, and this was so awesome, I’m now thinking of making a tool that will do this in more automated way.

I don’t know if this is the best way to find google bot and yahoo bot spiders, but it seems like a good method to start.