Drupal 7 Audit SQL Queries

13th December 2013 - 2 minutes read time

Note: This post is over a year old and so the information contained here might be out of date. If you do spot something please leave a comment and we will endeavour to correct.

When doing site audits on Drupal sites it’s always a good idea to get a feel of what sort of content types, users and taxonomy terms are available. Here are some SQL queries that I tend to use when starting out on a Drupal Audit.

User/Roles Breakdown

SELECT role.name as Role, COUNT(role.name) as Count
FROM role AS role
INNER JOIN users_roles AS users_roles ON role.rid = users_roles.rid
GROUP BY role.name
ORDER BY Count DESC;

This produces output similar to the following.

Role	Count
Commenter	20
Moderator	9
Writer	4
Editor	2

This produces output similar to the following.

Content Types Breakdown

SELECT node_type.name AS Name, node_type.type AS 'Machine Name', count(node_type.type) AS Count
FROM node AS node
INNER JOIN node_type AS node_type on node.type = node_type.type
GROUP BY node_type.type
ORDER BY Count DESC;

Name	Machine Name	Count
Blog entry	blog	100
Forum topic	forum	14
Page	page	6
Poll	poll	7
Webform	webform	1

Vocabulary Breakdown

SELECT vocabulary.name AS Name, COUNT( vocabulary.name ) AS Count
FROM taxonomy_vocabulary AS vocabulary
INNER JOIN taxonomy_term_data AS term_data ON term_data.vid = vocabulary.vid
GROUP BY vocabulary.name
ORDER BY Count DESC 
LIMIT 0 , 30;

This produces output similar to the following.

Name	Count
Tags	100
Categories	30
Forums	13

This only gives a very quick initial look at what soft of things are available within a Drupal setup. For a more complex analysis take a look at the Site Audit Drush module. That module will give a full breakdown of all of this information, as well as indications of problems of things to be wary of.

Drupal

Drupal 7

Comments

Permalink

Thanks for this!

One point is that the Count column on your Vocabulary breakdown only counts the number of terms in each Vocabulary.

It is also helpful to know the number of Nodes related to each Vocabulary:

SELECT vocabulary.name AS Name, COUNT( term_index.nid) as Nodes
FROM taxonomy_vocabulary AS vocabulary
INNER JOIN taxonomy_term_data AS term_data ON term_data.vid = vocabulary.vid
INNER JOIN taxonomy_index AS term_index ON term_data.tid = term_index.tid
GROUP BY vocabulary.name
ORDER BY Nodes DESC;

Add new comment

Related Content

Drupal 9: Configuring Drupal To Be An Identity Provider With SimpleSAMLphp

I have previously talked about configuring a Drupal site to authenticate against a remote SimpleSAMLphp install, but as Drupal is an excellent user management system I wanted to turn it around and use Drupal as the identity provider. This means that Drupal would allow users to log into other systems using their Drupal username and password by leveraging the power of SimpleSAMLphp.

Drupal 9: Configuring Drupal To Authenticate Against A Remote SimpleSAMLphp Identity Provider

I have previously talked about installing SimpleSAMLphp using composer, so the next step is setting up the system to actually provide authentication against a SimpleSAMLphp system. As I spend a lot of time using Drupal I wanted to set up the authentication with Drupal and SimpleSAMLphp in order to see how things worked.

First, let's define a couple of terms that are important to this setup.

Drupal 9: Creating A GET Form

I've been building Drupal forms for a number of years so I'm quite familiar with to putting together a Drupal form using the FormBase class and the form API. When I attempted to create a GET form this week I realised that there is actually quite a bit to think about. All forms are build using GET requests, it's the submission that I am specifically talking about. By default, forms in Drupal use POST requests to submit their data, and although it is possible to convert a form to use GET to submit data, it isn't well documented.

Drupal 9: Blocking Common Exploit Paths

If you run a Drupal site for any length of time you will quickly realise that a few paths that have nothing to do with Drupal will receive a lot of traffic. All of these paths result in page not found errors so the only impact is taking up your server resources. It's common to see paths like wp-login, xmlrpc.php, phpBB/page_header.php, postnuke/article.php, as well as a multitude of others. These requests are clearly bots probing the site to see what sort of CMS is in use and if they can exploit it or not.

Drupal 9: Changing Config Through Update Hooks

Drupal configuration is normally changed or removed through the configuration import and export process. For example, the process I follow is to make the change in the configuration locally, export the configuration into the source code, deploy the source code to a remote server and import the configuration. Using this mechanism, configuration changes that were exported locally are imported into the site and are ready to use.

Drupal 9: Setting Up Multilingual Content Views

Drupal Views is a powerful module. The ability to generate lists of things in Drupal means that it is used everywhere. It even has a powerful plugin system that allows other modules to interface with it and create more functionality.