wondering if there is a way to get the below reports from DB (8.5)
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)
Login history of a user in CMS (i can track from Event viewer log, but wondering if there is an easier way)
Deletions made by a user in CMS
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.