a guest Feb 27th, 2020 86 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*Number Of Users*/
  2. SELECT COUNT(*) AS 'Number Of Users'
  3. FROM webuser;
  5. /* Number Of Dashboards */
  6. SELECT COUNT(*) AS 'Number Of Dashboards'
  7. FROM webdashboard;
  9. /* Number of Dashboards Per User */
  10. SELECT webuser.userId, webuser.firstName AS 'First Name', webuser.lastName AS 'Last Name', AS 'Email Address', COUNT(webdashboardaccess.dashboardId) AS 'Number Of Dashboards'
  11. FROM webdashboard
  12. INNER JOIN webdashboardaccess ON webdashboard.dashboardId=webdashboardaccess.dashboardId
  13. INNER JOIN webuser ON webdashboardaccess.userId=webuser.userId
  14. WHERE accessLevel IN('CREATOR')
  15. GROUP BY webuser.userId;
  17. /* Most Recent Use & Number of Times Accessed */
  18. SELECT webdashboard.dashboardId, webdashboard.domain, dashboardName, MAX(logTime) AS 'Most Recent Use', COUNT(*) AS 'Times Accessed'
  19. FROM weblog
  20. INNER JOIN webuser ON weblog.userId=webuser.userId
  21. INNER JOIN webdashboard ON weblog.dashboardId=webdashboard.dashboardId
  22. WHERE logType='access'
  23. AND weblog.logTime > (NOW() - INTERVAL 6 MONTH)
  24. GROUP BY dashboardId;
  26. /* Number of Dashboard Interactions */
  27. SELECT webuser.userId, webuser.firstName AS 'First Name', webuser.lastName AS 'Last Name', COUNT(*) AS 'Dashboards Interactions'
  28. FROM weblog
  29. INNER JOIN webuser ON weblog.userId=webuser.userId
  30. GROUP BY userId;
  32. /*Number of Schedules*/
  33. SELECT webdashboard.dashboardName, webdashboard.dashboardId, webschedule.scheduleName, webschedule.scheduleId, webemail.emailTo, webemail.emailFrom FROM webschedule
  34. INNER JOIN webemailschedule ON webemailschedule.scheduleId = webschedule.scheduleId
  35. INNER JOIN webemail ON webemail.emailId = webemailschedule.emailId
  36. INNER JOIN webdashboard ON webdashboard.dashboardId = webemail.dashboardId;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand