Measuring SDL Tridion Usage with SQL

If you can’t manage what you can’t measure, then it makes sense to start measuring any SDL Tridion environment so that you better understand what you are doing with your environment and help predict the future. I’ll get onto measuring what you publish and how often at a later date but I recently looked at measurement of content for a customer so I thought I would quickly share that.

At the base level I want to know something about how many creates or updates I do on a given SDL Tridion implementation. Now, doing this via the API will be troublesome because the API does not lend itself to doing operations over the entire content repository in one call and if I want to report I don’t want to end up with expensive reporting activities. By far and away the best way to do this is directly on the database and below I have listed out some queries that will give you some ideas on what information you could report on.

You could easily take these queries and knock them into a nice ASP.NET page to display your results or even include them in a bona fide reporting tool like Crystal Reports. The queries are all read only so they are safe to run on any environment.
Getting the number of publications; easy to see in the Tridion interface but why not start with a basic query:

SELECT count(*) FROM publications;

Not too hard I would say…

Components created for a given day with Schema Title; this was my main aim of the reporting, show what components are created and what schema they are based upon. This query gave me some headaches because the Schema (title) is actually a row in the same table that you are querying for your component information. You could take the easy way out and just retrieve the ID of the schema and look it up later but why take the easy way out?

SELECT DISTINCT itm1.title, COUNT(*) total  
FROM items itm1
JOIN
(
  SELECT itm3.title, itm3.schema_reference_id, itm3.item_reference_id, itm3.publication_id, itm3.version
  FROM items itm3
  WHERE TRUNC(itm3.modification_date)=TO_DATE('26-11-2009','DD-MM-YYYY')
  AND itm3.version = 1
  AND itm3.item_type=16
) itm2
ON (itm2.schema_reference_id = itm1.item_reference_id)
GROUP BY itm1.title
ORDER BY total DESC

So the crucial parts to this query are:

The JOIN, the sub query is the list of components that were created on the date in the WHERE clause. If you want those items they were updated you can better put “AND itm3.version > 1” or just leave it out completely if you want both created & updated in the same result. For a nice report you would want probably want to parameterize the date so you can report any given day but if you are just running the SQL you’ll need to update by hand. The “item_type = 16” refers to components and it is worth noting this query will not work for pages (item type of 64) because pages don’t have schemas, they have templates and they can also inherit them rather than having them set explicitly so it makes discovery of what template a page was created against a mind bender I did not feel inclined to take a look at (yes, I took the easy route).

Total new components for a given day by user; a good way to check who is doing what is looking, by day, who created content. It says nothing about how many meetings the users attended or even how many coffees they made for the team, just how much they created (i.e. made a version 1 component):

SELECT trs.name, count(*) total_new_items
FROM items itm, trustees trs
WHERE TRUNC(itm.modification_date)=TO_DATE('26-11-2009','DD-MM-YYYY')
AND itm.version = 1
AND itm.item_type=16
AND trs.id=itm.trustee_id
GROUP BY trs.name
ORDER BY total_new_items DESC;

As for the first query, updates can be found by using “> 1” for the version.

Total updates/creates of items per day; this query will show you, over time, what the total of items (e.g. components, pages, folders, publications) were created per day is.

SELECT modification_date, COUNT(*) total
FROM items
GROUP BY modification_date
ORDER BY total DESC

Hopefully that gives you a little insight into what you could report on your own environment. Collecting statistics is key to a successful environment and without it you do not know if your investment is well spent or if your project is really being successful.