USER ACTION DASHBOARD --->>>>>>>>>
Actions by day
SELECT DATE_FORMAT(AUDIT_TIME, '%H:00:00') AS hour, COUNT(*) AS total_count,
ACTION_TYPE AS actionType FROM USER_ACTION_AUDIT WHERE AUDIT_TIME >= NOW() -
INTERVAL 24 HOUR GROUP BY hour, ACTION_TYPE
Top user time spend
SELECT
USER_NAME AS column1,
COUNT(*) AS total_action_count,
SUM(ACTION_TYPE = 'UPDATE') AS update_count,
SUM(ACTION_TYPE = 'CREATE') AS create_count,
SUM(ACTION_TYPE = 'READ') AS viewed_count,
SUM(ACTION_TYPE = 'DELETE') AS delete_count
FROM
USER_ACTION_AUDIT
GROUP BY
USER_NAME
ORDER BY
total_action_count DESC
LIMIT 5;
Activity breakdown
SELECT DAYNAME(AUDIT_TIME) AS Day, HOUR(AUDIT_TIME) AS Hour, COUNT(*) AS
CountPerHour FROM USER_ACTION_AUDIT WHERE AUDIT_TIME BETWEEN DATE_SUB(CURDATE(),
INTERVAL 1 WEEK) AND CURDATE() GROUP BY Day, Hour ORDER BY FIELD(Day, 'Sunday',
'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'), Hour
role wise
SELECT ORG_ROLE AS orgRole_replaced, COUNT(*) AS count FROM USER_ACTION_AUDIT WHERE
DATE(AUDIT_TIME) = CURDATE() AND ORG_ROLE IS NOT NULL GROUP BY orgRole_replaced
ORDER BY count DESC LIMIT 5
action disctribution
SELECT CASE WHEN component_rank <= 10 THEN COMPONENT_NAME ELSE 'Others' END AS
component_group, SUM(action_count) AS total_count FROM (SELECT COMPONENT_NAME,
COUNT(*) AS action_count, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS
component_rank FROM USER_ACTION_AUDIT GROUP BY COMPONENT_NAME) AS component_counts
GROUP BY CASE WHEN component_rank <= 10 THEN COMPONENT_NAME ELSE 'Others' END
action success rate
SELECT CASE WHEN component_rank <= 5 THEN COMPONENT_NAME ELSE 'Others' END AS
component_group, SUM(component_count) AS total_count FROM (SELECT COMPONENT_NAME,
COUNT(*) AS component_count, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS
component_rank FROM USER_ACTION_AUDIT WHERE DATE(AUDIT_TIME) = CURDATE() GROUP BY
COMPONENT_NAME) AS component_counts GROUP BY CASE WHEN component_rank <= 5 THEN
COMPONENT_NAME ELSE 'Others' END ORDER BY total_count DESC LIMIT 5
recent user activity
SELECT t.USER_NAME AS username, t.MODIFICATION_TIME AS modificationtime,
t.ACTION_NAME AS actionname, t.ACTION_TYPE AS actionType, t.COMPONENT_NAME AS
componentname, [Link] AS id, t.ORG_ROLE AS orgRole FROM USER_ACTION_AUDIT t WHERE
ORG_ROLE IS NOT NULL AND ORG_ROLE NOT IN ('Others')
USAGE HISTORY->>>>>>>>>>
Page views
SELECT DAYNAME(CREATION_TIME) AS day_of_week, COUNT(*) AS count_of_records FROM
USER_HISTORY WHERE CREATION_TIME IS NOT NULL AND CREATION_TIME >= CURDATE() -
INTERVAL 6 DAY GROUP BY day_of_week ORDER BY FIELD(day_of_week, 'Sunday', 'Monday',
'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
Top 5 most used components
SELECT PARENT_COMPONENT_NAME AS column1, COUNT(*) AS count_of_records FROM
USER_HISTORY WHERE PARENT_COMPONENT_NAME IS NOT NULL GROUP BY PARENT_COMPONENT_NAME
ORDER BY count_of_records DESC LIMIT 5
Top 5 active users
SELECT
MAX([Link]) AS userName,
COUNT(uh.PARENT_COMPONENT_NAME) AS pageViews,
MAX(uh.PARENT_COMPONENT_NAME) AS mostUsedComponentName,
MAX(uh.CREATION_TIME) AS lastCreationTime
FROM
USER_HISTORY uh
JOIN
USER u ON uh.USERID_FK = [Link]
WHERE
uh.PARENT_COMPONENT_NAME IS NOT NULL
GROUP BY
uh.USERID_FK
ORDER BY
pageViews DESC
LIMIT
5
Top 5 componennts by action
SELECT ACTION_TYPE AS column1, PARENT_COMPONENT_NAME AS column2,
COUNT(PARENT_COMPONENT_NAME) AS totalComponentCount, SUM(CASE WHEN ACTION_TYPE =
'create' THEN 1 ELSE 0 END) AS createCount, SUM(CASE WHEN ACTION_TYPE = 'delete'
THEN 1 ELSE 0 END) AS deleteCount, SUM(CASE WHEN ACTION_TYPE = 'update' THEN 1 ELSE
0 END) AS updateCount, SUM(CASE WHEN ACTION_TYPE = 'Viewed' THEN 1 ELSE 0 END) AS
viewedCount FROM USER_HISTORY WHERE PARENT_COMPONENT_NAME IS NOT NULL AND
ACTION_TYPE IS NOT NULL GROUP BY ACTION_TYPE, PARENT_COMPONENT_NAME
Usage breakdown
SELECT DAYNAME(CREATION_TIME) AS Day, CONCAT(DATE_FORMAT(CREATION_TIME, '%H:00'))
AS Hour, COUNT(*) AS CountPerHour FROM USER_HISTORY WHERE DATE(CREATION_TIME)
BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND CURDATE() GROUP BY Day, Hour ORDER
BY FIELD(Day, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday'), Hour
AUDIT DASHBOARD---->>>>>>>>>>