Drupal 7 Audit SQL Queries

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.

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;

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.

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.