How to do the data mining on the Gateway statistics using CDS view and AMDP?

[brian_zhu]

Recently i got a very interesting requirement from management team to do a POC based on the Gateway statistics to address the following business questions.

1. What are the Top 10 apps accessed within a certain period ?
2. Who are the top 10 users within a certain period?
3. Which hour of the day do the most users access ?
4. Which week day of the week do the most users access?
5. What are the most search criteria for all the services ?
6. According to the functions defined in the app, we could also know that how many times of the business functions are executed?
7. What are the average response time and maximum payload of the http requests?

i did some research on this topic and would like to share it within this community.

step 1: The Gateway performance statistics are stored in tables /iwfnd/i_med_srh and /wfnd/i_med_srt, which could be accessed via tcode /iwfnd/maint_service

   The Gateway performance statistics are stored in table /iwfnd/su_stats , which could be accessed via tcode /iwfnd/stats

step 2 CDS View Building

CDS View 1:

@AbapCatalog.sqlViewName: 'V_CDS_SERV'

define view /nsl/cdsv_gw_service

( service_name, service_description )

as select from /iwfnd/i_med_srh as srh

          association[0..1] to /iwfnd/i_med_srt as srt

          on srh.srv_identifier = srt.srv_identifier and

             srh.is_active = srt.is_active

{

    srh.service_name,

    srt.description

}

where

   srt.language = 'E' and

   srt.is_active = 'A'

CDS View 2

@AbapCatalog.sqlViewName: 'V_CDS_STATS'

define view cdsv_stats_basic

( namespace, service_name, userid, timestampl, service_description, operation, entity_type, expand_string, request_address )

as select from /iwfnd/su_stats as stats

  association[1..1] to v_cds_serv  as service on

  stats.service_name = service.service_name

{

  stats.namespace,

  stats.service_name,

  stats.userid,

  stats.timestampl,

  service[1:inner].service_description,

  stats.operation,

  stats.entity_type,

  stats.expand_string,

  stats.request_address

}

where

      (  stats.namespace = '/SAP/' )

Step 3: ABAP Managed Database Procedure

Create a structure - Overview

AMDP Class Defination:

AMDP Snippet
class /XXX/cl_gw_su_stats definition   public   final   create public .   public section.     interfaces: if_amdp_marker_hdb. methods:       get_su_stats_total         importing           value(iv_client)        type symandt           value(iv_start_time)    type timestampl           value(iv_end_time)      type timestampl         exporting           value(ev_user_num)      type /XXX/gw_su_basics_s-user_num           value(ev_apps_num)      type /XXX/gw_su_basics_s-apps_num           value(ev_apps_per_user) type /XXX/gw_su_basics_s-apps_per_user           value(ev_top_user)      type /XXX/gw_su_basics_s-top_user           value(ev_top_app)       type /XXX/gw_su_basics_s-top_app           value(ev_hot_hour)      type /XXX/gw_su_basics_s-hot_hour           value(ev_hot_day)       type /XXX/gw_su_basics_s-hot_day.   protected section.   private section. endclass.
method get_su_stats_total by database procedure                             for hdb language sqlscript                             options read-only                             using /XXX/v_cds_stats.     DECLARE v_servicename INT;     DECLARE v_user INT;     /* get the total number users */          select count(distinct userid) into ev_user_num from "/XXX/V_CDS_STATS"               where MANDT = :iv_client and timestampl between :iv_start_time and :iv_end_time ;     /* get the total number services */          select  count(distinct service_name) into ev_apps_num  from "/NSL/V_CDS_STATS"             where MANDT = :iv_client and timestampl between :iv_start_time and :iv_end_time ;     /* get the apps per user */ *     ev_apps_per_user = ev_apps_num / ev_user_num ;     /* get the top user name */           select top 1 userid,                           count (service_name ) as service_name into ev_top_user, v_servicename                           from "/XXX/V_CDS_STATS"                           where MANDT = :iv_client and timestampl between :iv_start_time and :iv_end_time                           group by userid                           order by service_name desc                           ;     /* get the top app name */     select top 1          service_name,          count(userid) as userid into ev_top_app, v_user          from  "/XXX/V_CDS_STATS"           where MANDT = :iv_client and timestampl between :iv_start_time and :iv_end_time          group by service_name          order by userid desc ; /* which the day of the week do the agents log in the most */ select top 1 to_char( utctolocal(to_timestamp (timestampl),'UTC+8'),'DAY')as Date,                count(userid) as userid into ev_hot_day, v_user       from "/XXX/V_CDS_STATS"           where MANDT = :iv_client and timestampl between :iv_start_time and :iv_end_time            group by to_char( utctolocal(to_timestamp (timestampl),'UTC+8'),'DAY')            order by userid desc; /* which Hour of the day do the agents log in the most*/ select top 1 hour( to_time( utctolocal(to_timestamp(timestampl),'UTC+8')))as Hour,                count(userid) as userid into ev_hot_hour, v_user       from "/XXX/V_CDS_STATS"           where MANDT = :iv_client and timestampl between :iv_start_time and :iv_end_time            group by hour( to_time( utctolocal(to_timestamp(timestampl),'UTC+8')))            order by userid desc;   endmethod.

Gateway service snippet
data: ls_entity            like line of et_entityset.     loop at  it_filter_select_options into data(ls_filter_select_option).       case ls_filter_select_option-property.         when 'SelectionDate'.           loop at ls_filter_select_option-select_options into data(ls_select_option).             if ls_select_option-low is initial.               lv_start_time_feeder = sy-datum.             else.               lv_start_time_feeder = ls_select_option-low.             endif.             if ls_select_option-high is initial.               lv_end_time_feeder = sy-datum.             else.               lv_end_time_feeder  = ls_select_option-high.             endif.           endloop.         when others.           raise exception type /iwbep/cx_mgw_busi_exception             exporting               textid = /iwbep/cx_mgw_busi_exception=>filter_not_supported.       endcase.     endloop.     if sy-subrc <> 0.       raise exception type /iwbep/cx_mgw_busi_exception         exporting           textid            = /iwbep/cx_mgw_busi_exception=>business_error_unlimited           message_unlimited = |Filter is required|.     endif.     convert date lv_start_time_feeder time sy-uzeit into time stamp lv_start_time time zone sy-zonlo .     convert date lv_end_time_feeder time  sy-uzeit into time stamp lv_end_time time zone sy-zonlo.     data(lo_overview) = new /nsl/cl_gw_su_stats( ).     try.         lo_overview->get_su_stats_total(           exporting             iv_client        = sy-mandt             iv_start_time    = lv_start_time             iv_end_time      = lv_end_time           importing             ev_user_num      =  ls_entity-user_num             ev_apps_num      = ls_entity-apps_num             ev_apps_per_user = ls_entity-apps_per_user             ev_top_user      = ls_entity-top_user             ev_top_app       = ls_entity-top_app             ev_hot_day       = ls_entity-hot_day             ev_hot_hour      = ls_entity-hot_hour         ).       catch cx_amdp_execution_failed into data(lv_error).     endtry.     append ls_entity to et_entityset.

last step to test the service in GW client

Finally the screenshots looks at below for several of the functions above.