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 I 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: Customise Your Robots.txt File

A robots.txt file tells search engines spiders what pages or files they should or shouldn't request from your site. It is more of a way of preventing your site from being overloaded by requests rather than a secure mechanism to prevent access. It really shouldn't be used as a way of preventing access to your site, and the chances are that some search engine spiders will access the site anyway. If you do need to prevent access then think about using noindex directives within the page itself, or even password protecting the page.

Drupal 9: Get List Of Content Types

Getting a list of content types out of a Drupal 9 site is useful in a few situations. Mostly, I find that when creating a service of some kind that I will also create an administration form for that service to allow it to be restricted to certain content types. This list can be saved to configuration so that when the service is run it only effects certain content types, based on the saved configuration.

To get a an array containing a list of the content types on a Drupal site you can use the following code.

Drupal 9: Auto Tweeting From A Drupal Site When Content Is Published

Normally, when creating Tweets from Drupal 8 I use the Social Post Twitter module. This module is part of the Drupal Social Initiative and has been my go-to module when I've needed to read or send Tweets from a Drupal site. Since the release of Drupal 9, however, these modules have not been receiving the support needed and as of writing this article there is no easy way to install them on a Drupal 9 site.

Drupal 9: How To Tell If A Page Is First Published

I was writing some code on a Drupal site that detects if a page is being published and I realised that this state isn't as clear cut as you might expect. Drupal stores the published state of a page as the 'status', with 0 being unpublished and 1 being published. With revisions being turned on by default since Drupal 8 it is possible to see past states of the page when saving the page. The issue is that there is nothing in the current state of the page stating that this is the first time it is being published.

Drupal 9: Some Strategies For Developing Update Hooks

Drupal's update hook system is a powerful way of updating your site to introduce things that wouldn't be handled using the configuration system.

Modules will use update hooks to bring sites that have the old version of the module in line with the latest additions to the module. For example, if a new field is added to a table that the module uses then an update hook will be needed to add that field to all sites that are current using the old version. This update hook will be in addition to the install hook that would install the table with the added field in the first place.

Drupal 9: Cascading Ajax Select Forms

Tying together different select elements in a form is done with very little effort thanks to the ajax and states system built into Drupal 9. This means that any Drupal form can have a select element that shows and updates options into another select element within the same form. Using this system you can create a hierarchical system where one select will show and populate another select element with items dependent on the first. This is great for giving the user the ability to drill down into options that are dependent on each other.