Page tree
Skip to end of metadata
Go to start of metadata

This is a statistical summary report for details like active state of   user, login details, item completion details etc

 

Report NameCustomer Usage Data
REPORT_NAME_IDCustomerUsageData
REPORT_ID427
CATEGORY_IDSystemAdministration
GROUP_ID
WORKFLOW_IDRun Customer Usage Data Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

adminLoginDetails
SELECT admin_logins, 
       admin_unique_logins, 
       day_count, 
       ( CASE 
           WHEN m.day_count > 0 
                AND m.admin_logins > 0 THEN ( m.admin_logins / m.day_count ) 
           ELSE 0 
         END ) AS admin_avg_logins, 
       ( CASE 
           WHEN m.day_count > 0 
                AND m.admin_logins > 0 THEN ( 
           m.admin_unique_logins / m.day_count ) 
           ELSE 0 
         END ) AS admin_avgunique_logins 
FROM   (SELECT /*+INDEX (pa_login_session_info IX_LOGIN_SES_IN__LOGIN) */ Count( 
              login_id)             AS admin_logins, 
       /** ROUND(CAST( [SubmitDateTo] AS DATE)- CAST( [SubmitDateFrom] AS DATE)) AS day_count */,
       Count(DISTINCT( login_id )) AS admin_unique_logins 
        FROM   pa_login_session_info 
        WHERE  login_type = 'A' 
       /** AND login_time >= [SubmitDateFrom] AND NOT login_time > [SubmitDateTo] */ 
       )m 
concurrentAdminLoginDetails
SELECT ( CASE 
           WHEN c_count_sum > 0 
                AND c_count_count > 0 THEN (( c_count_sum / c_count_count )) 
           ELSE 0 
         END ) AS avg_admin_clogin, 
       ( CASE 
           WHEN max_admin_clogin > 0 THEN max_admin_clogin 
           ELSE 0 
         END ) AS max_admin_clogin 
FROM   (SELECT ( SUM(c_count) )   AS c_count_sum, 
               ( Count(c_count) ) AS c_count_count, 
               Max(c_count)       AS max_admin_clogin 
        FROM   (SELECT c_count 
                FROM   (SELECT /*+parallel(8) */ a.login_time, 
                                                 Count(a.login_time) AS c_count 
                        FROM   pa_login_session_info a, 
                               pa_login_session_info b 
                        WHERE  a.login_type = 'A' 
                               AND b.login_type = 'A' 
/** AND a.login_time >= [SubmitDateFrom] AND a.login_time <= [SubmitDateTo] */ 
                               /** AND b.login_time >= [SubmitDateFrom] AND b.login_time <= [SubmitDateTo] */
                               AND a.login_time >= b.login_time 
                               AND a.login_time <= b.logout_time 
                        GROUP  BY a.login_time))) 
concurrentUserLoginDetails
SELECT ( CASE 
           WHEN c_count_sum > 0 
                AND c_count_count > 0 THEN (( c_count_sum / c_count_count )) 
           ELSE 0 
         END ) AS avg_user_clogin, 
       ( CASE 
           WHEN max_user_clogin > 0 THEN max_user_clogin 
           ELSE 0 
         END ) AS max_user_clogin 
FROM   (SELECT ( SUM(c_count) )   AS c_count_sum, 
               ( Count(c_count) ) AS c_count_count, 
               Max(c_count)       AS max_user_clogin 
        FROM   (SELECT c_count 
                FROM   (SELECT /*+parallel(8) */ a.login_time, 
                                                 Count(a.login_time) AS c_count 
                        FROM   pa_login_session_info a, 
                               pa_login_session_info b 
                        WHERE  a.login_type = 'U' 
                               AND b.login_type = 'U' 
/** AND a.login_time >= [SubmitDateFrom] AND a.login_time <= [SubmitDateTo] */ 
                               /** AND b.login_time >= [SubmitDateFrom] AND b.login_time <= [SubmitDateTo] */
                               AND a.login_time >= b.login_time 
                               AND a.login_time <= b.logout_time 
                        GROUP  BY a.login_time))) 
itemCompletionDetails
SELECT student_count AS total_users, 
       ( CASE 
           WHEN total_items > 0 
                AND student_count > 0 THEN (( total_items / student_count )) 
           ELSE 0 
         END )       AS avg_item_complete, 
       ( CASE 
           WHEN total_items > 0 THEN total_items 
           ELSE 0 
         END )       AS total_item_complete 
FROM   (SELECT SUM(internal_completions + external_completions) AS total_items 
        FROM   (SELECT (SELECT SUM(c_count) 
                        FROM   (SELECT stud_id, 
                                       Count(*) AS c_count 
                                FROM   pa_cpnt_evthst 
                                /** WHERE compl_dte >= [SubmitDateFrom] AND NOT compl_dte > [SubmitDateTo] */
                                GROUP  BY stud_id)) AS internal_completions, 
                       (SELECT SUM(c_count) 
                        FROM   (SELECT stud_id, 
                                       Count(*) AS c_count 
                                FROM   pa_cpnt_evthst 
                                /** WHERE compl_dte >= [SubmitDateFrom] AND NOT compl_dte > [SubmitDateTo] */
                                GROUP  BY stud_id)) AS external_completions 
                FROM   dual)), 
       (SELECT Count(stud_id) AS student_count 
        FROM   (SELECT stud_id 
                FROM   (SELECT stud_id 
                        FROM   (SELECT /*+ NO_INDEX(ph IX_PH_STUD__TSTMP) */ 
                                stud_id 
                                FROM   ph_student ph 
                                /** WHERE PH.HIST_CREATE_TSTMP >= [SubmitDateFrom]                                        AND PH.HIST_CREATE_TSTMP <= [SubmitDateTo] */ 
                                UNION ALL 
                                SELECT stud_id 
                                FROM   (SELECT 
                                /*+ PARALLEL(8) NO_INDEX(a IX_PH_STUD__TSTMP) */ 
                                       a.* 
                                        FROM   ph_student a, 
                                               (SELECT 
                        /*+ NO_INDEX(ph_student IX_PH_STUD__TSTMP) */ 
                                               Max(hist_create_tstmp) AS 
                                               hist_create_tstmp, 
                                               stud_id 
                                                FROM   ph_student 
                                                /** WHERE hist_create_tstmp <= [SubmitDateTo] */
                                                GROUP  BY stud_id)b 
                                        WHERE  a.stud_id = b.stud_id 
                                               AND a.hist_create_tstmp = 
                                                   b.hist_create_tstmp) z 
                                WHERE  action IN ( 'I', 'M' ) 
                               /** and z.hist_create_tstmp < [SubmitDateFrom] */ 
                               ) 
                        UNION ALL 
                        SELECT /*+ NO_INDEX(PA_STUDENT IX_PA_STUD__TSTMP) */ 
                        stud_id 
                        FROM   pa_student 
                       /** WHERE lst_upd_tstmp < [SubmitDateFrom] */ 
                       /** AND [security:PA_NO_DOMAIN_SECURITY_2 pa]*/ 
                       ) a 
                GROUP  BY stud_id)) 
totalActiveUsers
SELECT Count(stud_id) AS ACTIVE_USERS 
FROM   (SELECT stud_id 
        FROM   (SELECT /*+ NO_INDEX(ph IX_PH_STUD__TSTMP) */ stud_id 
                FROM   ph_student ph 
                WHERE  notactive = 'N' 
                /** AND PH.HIST_CREATE_TSTMP >= [SubmitDateFrom]                            AND PH.HIST_CREATE_TSTMP <= [SubmitDateTo] */ 
                UNION ALL 
                SELECT stud_id 
                FROM   (SELECT /*+ PARALLEL(8) NO_INDEX(a IX_PH_STUD__TSTMP) */ 
                       a.* 
                        FROM   ph_student a, 
                               (SELECT 
                       /*+ NO_INDEX(PH_STUDENT IX_PH_STUD__TSTMP) */ 
                               Max(hist_create_tstmp) AS hist_create_tstmp, 
                               stud_id 
                                FROM   ph_student 
                                /** WHERE hist_create_tstmp <= [SubmitDateTo] */ 
                                GROUP  BY stud_id)b 
                        WHERE  a.stud_id = b.stud_id 
                               AND a.hist_create_tstmp = b.hist_create_tstmp) z 
                WHERE  action IN ( 'I', 'M' ) 
                       AND notactive = 'N' 
                /** and z.hist_create_tstmp < [SubmitDateFrom] */ 
                UNION ALL 
                SELECT /*+ NO_INDEX(PA_STUDENT IX_PA_STUD__TSTMP) */ stud_id 
                FROM   pa_student 
                WHERE  notactive = 'N' 
               /** and lst_upd_tstmp < [SubmitDateFrom] */ 
               ) 
        GROUP  BY stud_id) 
totalInactiveUsers
SELECT Count(stud_id) AS INACTIVE_USERS 
FROM   (SELECT stud_id 
        FROM   (SELECT /*+ NO_INDEX(S IX_PH_STUD__TSTMP) */ S.stud_id 
                FROM   ph_student S 
                WHERE  S.notactive = 'Y' 
                       /**  AND S.HIST_CREATE_TSTMP >= [SubmitDateFrom]                              AND S.HIST_CREATE_TSTMP <= [SubmitDateTo]  */ 
                       AND NOT EXISTS (SELECT 
                               /*+ NO_INDEX(S1 IX_PH_STUD__TSTMP) */ 
                                      stud_id 
                                       FROM   ph_student S1 
                                       WHERE  S1.notactive = 'N' 
                                              AND S1.stud_id = S.stud_id 
                                      /**       AND S1.HIST_CREATE_TSTMP >= [SubmitDateFrom]                                                  AND S1.HIST_CREATE_TSTMP <= [SubmitDateTo]  */
                                      ) 
                UNION ALL 
                SELECT stud_id 
                FROM   (SELECT /*+ PARALLEL(8) NO_INDEX(a IX_PH_STUD__TSTMP) */ 
                       a.* 
                        FROM   ph_student a, 
                               (SELECT 
                       /*+ NO_INDEX(ph_student IX_PH_STUD__TSTMP) */ 
                               Max(hist_create_tstmp) AS hist_create_tstmp, 
                               stud_id 
                                FROM   ph_student 
                                /** WHERE hist_create_tstmp <= [SubmitDateTo] */ 
                                GROUP  BY stud_id)b 
                        WHERE  a.stud_id = b.stud_id 
                               AND a.hist_create_tstmp = b.hist_create_tstmp) z 
                WHERE  action IN ( 'I', 'M' ) 
                       AND notactive = 'Y' 
                /** and z.hist_create_tstmp < [SubmitDateFrom] */ 
                UNION ALL 
                SELECT /*+ NO_INDEX(PA_STUDENT IX_PA_STUD__TSTMP) */ S.stud_id 
                FROM   pa_student S 
                WHERE  S.notactive = 'Y' 
               /**   AND LST_UPD_TSTMP < [SubmitDateFrom] */ 
               ) 
        GROUP  BY stud_id) 
userLoginDetails
SELECT user_logins, 
       user_unique_logins, 
       day_count, 
       ( CASE 
           WHEN m.day_count > 0 
                AND m.user_logins > 0 THEN ( m.user_logins / m.day_count ) 
           ELSE 0 
         END ) AS user_avg_logins, 
       ( CASE 
           WHEN m.day_count > 0 
                AND m.user_logins > 0 THEN 
           ( m.user_unique_logins / m.day_count ) 
           ELSE 0 
         END ) AS user_avgunique_logins 
FROM   (SELECT /*+INDEX (pa_login_session_info IX_LOGIN_SES_IN__LOGIN) */ Count( 
              login_id)             AS user_logins, 
                                                                          Count( 
       DISTINCT( login_id )) AS user_unique_logins, 
        /** ROUND(CAST( [SubmitDateTo] AS DATE)- CAST( [SubmitDateFrom] AS DATE)) AS day_count */
        FROM   pa_login_session_info a 
        WHERE  login_type = 'U' 
       /** AND login_time > [SubmitDateFrom] AND NOT login_time > [SubmitDateTo] */ 
       )m 

 

Link: http://wiki.scn.sap.com/wiki/x/koERG