Bulk Processing WP Engine Usage Stats

WP Engine provides a downloadable CSV usage stats per site. This is great for seeing individual site activity however it’s not very useful for comparing sites. With a dedicated WP Engine server it would be extremely helpful to see all sites organized by their usage stats. This would help identify top sites which might affect overall server health. Well, good news! This can be scripted. 💥

WP Engine’s backend server processing hours

One of the usage stats backend_server_processing_hours is the total time a particular site was using resources on the server. This metric can be used to figure out which sites are having the most impact on the server’s resources. So let’s sort all sites by backend_server_processing_hours.

Fetching all WP Engine usage stats

While this can be done manually through WP Engine’s portal it’s not very practical when a server has hundreds of sites. In order to automate I added a command download_usage_for_all_installs to the ruby script wpengine_portal.rb. That will automatically fetch all usage stats CSVs for an entire server. It works by running ruby wpengine_portal.rb <account-name> download_usage_for_all_installs on the command line.

Processing all usage stats and organize sites by average backend server processing hours

The following PHP script will read through all of the usage CSVs and calculate the average backend_server_processing_hours per site. Then orders the sites from highest to lowest and output the results by running php wpengine_generate_stats.php.

Here is the script for wpengine_generate_stats.php.

<?php
#
#   Generates list of WP Engine sites sorted by backend_server_processing_hours from usage CSV files
#
#   php wpengine_generate_stats.php
#

/*

Headers of CSV files
 
0 = tstamp
1 = customer_id
2 = cluster
3 = visitors
4 = objects_served_directly
5 = objects_served_cdn
6 = gb_served_directly
7 = gb_served_cdn
8 = gb_total
9 = uncached_dynamic_hits
10 = backend_server_processing_hours

*/

// Directory with WP Engine usage CSVs
$dir = 'usage_stats';

// Loads all files
$usage_stats = scandir($dir);

// Empty array for Sites
$sites = array();

foreach ( $usage_stats as $file ) {

    // Skip if not .csv
    if ( strpos($file, '.csv') == false ) {
        continue;
    }

    // Extract site name from file name
    $site_name = str_replace( "_usage_stats.csv", "", $file );

    // Pull in file
    $csv = array_map('str_getcsv', file('usage_stats/'. $file));

    // Removes header column
    array_shift($csv); 

    // pull out backend_server_processing_hours
    $backend_server_processing_hours = array_column( $csv, 10 );

    // Calculate average
    $average_backend_server_processing_hours = round ( array_sum( $backend_server_processing_hours ) / count( $backend_server_processing_hours ) , 2 );

    // Add site to sites array
    $sites[$site_name] = $average_backend_server_processing_hours;

}

// Sort by backend_server_processing_hours
arsort($sites);

echo "Sites sorted by backend_server_processing_hours\n\n";

foreach ($sites as $value => $site) {
    echo "$site $value\n";
}