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.
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;