DB Reports for Deletions

Question

wondering if there is a way to get the below reports from DB (8.5)

1. Edits made by a user in CMS for a period of time (Advanced search can help here, but wondering if i can get more details from DB)

2. Login history of a user in CMS (i can track from Event viewer log, but wondering if there is an easier way)

3. Deletions made by a user in CMS

4. Published content by a user for a period of time

I have something like below for published output, but maybe you guys have a better one (or suggestions):

 SELECT TS.DESCRIPTION AS PUBLISHER_NAME, PT.DESCRIPTION AS PUB_TARGET, PS.STATE_CHANGE_DATE AS DATE_PUBLISHED, P.TITLE AS PUBLICATION, ITY.NAME AS TYPE, IT.TITLE AS PAGE_NAME, IT.MODIFICATION_DATE
FROM PUBLISH_STATES PS
JOIN TRUSTEES TS
  ON PS.TRUSTEE_ID = TS.ID
JOIN PUBLICATION_TARGETS PT
  ON PS.PUBLICATION_TARGET_ID = PT.ID
JOIN PUBLICATIONS P
  ON PS.PUBLICATION_ID = P.ID
JOIN ITEMTYPES ITY
  ON PS.ITEM_TYPE = ITY.ID
INNER JOIN ITEMS IT
  ON PS.REFERENCE_ID = IT.ITEM_REFERENCE_ID --AND PS.PUBLICATION_ID = IT.PUBLICATION_ID 
WHERE PS.TRUSTEE_ID IN (18, 150, 190) and trunc(PS.STATE_CHANGE_DATE) > TO_DATE(SYSDATE - 30,'dd-MON-yy')
ORDER BY IT.MODIFICATION_DATE DESC;

Please let me know.

Answer 1

I agree that all of these data are needed, and even I would like to have them, but please make note that they are not available in the database. This is because Tridion CM database is transactional database, not a data warehouse. It means that it stores latest data (plus version history for versioned items). So to answer your questions, no you cannot get this data from database, but you can implement mechanisms which capture these data and log them. It's a longer way, but it's achievable.

If you want to get deletions made by user, they are not accessible anywhere in the system, so you will have to create Event System which captures data that is being deleted (or unlocalized) and before deletion store the data in log (sort of a backup). There are numerous questions here on that topic.

If you want to track publishes by user, make note that publish queue has the info only on the latest publishes of an item. So if item was published first by person A, and then by person B in same publication to same target, you will see in queue only transaction for person B (second transaction overrides fist one). Again for this, you will have to create Event System.

So my general anser is utilize Event System. Capture ES events you need, log that data anywhere (even in custom database), and use it how you like :)