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.