Drupal 7 Audit SQL Queries

13th December 2013

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

  1. SELECT role.name as Role, COUNT(role.name) as Count
  2. FROM role AS role
  3. INNER JOIN users_roles AS users_roles ON role.rid = users_roles.rid
  4. GROUP BY role.name
  5. 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

  1. SELECT node_type.name AS Name, node_type.type AS 'Machine Name', count(node_type.type) AS Count
  2. FROM node AS node
  3. INNER JOIN node_type AS node_type on node.type = node_type.type
  4. GROUP BY node_type.type
  5. ORDER BY Count DESC;
NameMachine NameCount
Blog entryblog100
Forum topicforum14
Pagepage6
Pollpoll7
Webformwebform1

Vocabulary Breakdown

  1. SELECT vocabulary.name AS Name, COUNT( vocabulary.name ) AS Count
  2. FROM taxonomy_vocabulary AS vocabulary
  3. INNER JOIN taxonomy_term_data AS term_data ON term_data.vid = vocabulary.vid
  4. GROUP BY vocabulary.name
  5. ORDER BY Count DESC
  6. 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.

Add new comment

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