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.
CAPTCHA
4 + 5 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Related Content

Drupal 10: Running Drupal Tests On GitHub Using Workflows

12th November 2023

There are a number of different tools that allow you to validate and test a Drupal site. Inspecting your custom code allows you to adhere to coding standards and ensure that you stamp our common coding problems. Adding tests allows you to make certain that the functionality of your Drupal site works correctly.

Read more

DrupalCon Lille 2023

29th October 2023

This year's DrupalCon Europe was hosted between the 17th and 20th of October, in the French city of Lille. My DrupalCon adventure began early on Monday morning when Chris Maiden picked me up to drive to France via the EuroStar train. We arrived in Lille a little after 4pm, which was really good going for a nearly 400+ mile trip.

Read more