Drupal 7 Audit SQL Queries

13th December 2013 - 3 minutes read time

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.

RoleCount
Commenter20
Moderator9
Writer4
Editor2

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;
NameMachine NameCount
Blog entryblog100
Forum topicforum14
Pagepage6
Pollpoll7
Webformwebform1

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.

NameCount
Tags100
Categories30
Forums13

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

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;

Submitted by James Wilson on Thu, 07/16/2020 - 03:26

Permalink

Add new comment

The content of this field is kept private and will not be shown publicly.

Related Content

DrupalCamp England 2025

2nd March 2025

The first DrupalCamp in England was held this weekend (1st March 2025) in Cambridge. I drove down on the Friday with Chris Maiden to attend (and speak!) at the event.

We arrived early at the event and and started the day with a much needed coffee (or two) and started chatting with the other attendees.

Read more

Drupal 11: Creating Custom Queues

19th January 2025

Creating queues using the core queue classes in Drupal is fairly straightforward. You just need a mechanism of adding data to the queue and a worker to process that data.

Read more

Drupal 11: Using Data Transfer Objects With The Queue API

5th January 2025

When writing data to the queue database system Drupal will convert the information to a string using the PHP serialize() function. When the information is pulled out of the database the queue system will unserialize() the data to convert it back into the original information.

Read more

Drupal 11: The Queues API

22nd December 2024

I've talked a lot about the Batch API in Drupal recently, and I've mentioned that it is built upon the Queue API, but I haven't gone any deeper than that. I wrote about the Queues API in Drupal 7, but thought I would bring my understanding up to date.

Read more

Drupal 11: Batch Operations Built Into Drupal

27th October 2024

This is the sixth article in a series of articles about the Batch API in Drupal. The Batch API is a system in Drupal that allows data to be processed in small chunks in order to prevent timeout errors or memory problems.

Read more