~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
view=OAC_USAGE_TRACKING_VW
view=OAC_USAGE_TRACKING_CLEAN_VW
~~~END_MAIN_SECTION~~~


~~~BEGIN_VERSION_SECTION~~~
[VERSION]
version=2026-02-25

[SQL_SCRIPT___ADMIN]
--each OAC must have its own schema

DEFINE trackschema = 'OAC_TRACKING_PROD_NEW';
DEFINE trackschema = 'OAC_TRACKING_PROD';
DEFINE trackschema = 'OAC_TRACKING_DEV_OAC01';
DEFINE trackschema = 'USAGE_TRACKING'; --sandbox

CREATE OR REPLACE VIEW "&trackschema".OAC_USAGE_TRACKING_VW AS
(
select
    START_TS QUERY_TIMESTAMP
    ,REPLACE(SUBSTR(REPLACE(REPLACE(utl_url.unescape(case when INSTR(SAW_SRC_PATH,'"path":"')=0 then null else SUBSTR(SAW_SRC_PATH,INSTR(SAW_SRC_PATH,'"path":"')+8,INSTR(SAW_SRC_PATH,'"',INSTR(SAW_SRC_PATH,'"path":"')+8)-(INSTR(SAW_SRC_PATH,'"path":"')+8)) end),'/@Catalog/shared/'),'\/','0'),0,INSTR(REPLACE(REPLACE(utl_url.unescape(case when INSTR(SAW_SRC_PATH,'"path":"')=0 then null else SUBSTR(SAW_SRC_PATH,INSTR(SAW_SRC_PATH,'"path":"')+8,INSTR(SAW_SRC_PATH,'"',INSTR(SAW_SRC_PATH,'"path":"')+8)-(INSTR(SAW_SRC_PATH,'"path":"')+8)) end),'/@Catalog/shared/'),'\/','0'),'/',-1)-1),'0','/') DASHBOARD_PATH
    ,REPLACE(SUBSTR(REPLACE(REPLACE(utl_url.unescape(case when INSTR(SAW_SRC_PATH,'"path":"')=0 then null else SUBSTR(SAW_SRC_PATH,INSTR(SAW_SRC_PATH,'"path":"')+8,INSTR(SAW_SRC_PATH,'"',INSTR(SAW_SRC_PATH,'"path":"')+8)-(INSTR(SAW_SRC_PATH,'"path":"')+8)) end),'/@Catalog/shared/'),'\/','0'),INSTR(REPLACE(REPLACE(utl_url.unescape(case when INSTR(SAW_SRC_PATH,'"path":"')=0 then null else SUBSTR(SAW_SRC_PATH,INSTR(SAW_SRC_PATH,'"path":"')+8,INSTR(SAW_SRC_PATH,'"',INSTR(SAW_SRC_PATH,'"path":"')+8)-(INSTR(SAW_SRC_PATH,'"path":"')+8)) end),'/@Catalog/shared/'),'\/','0'),'/',-1)+1),'0','/') DASHBOARD_NAME
    ,REGEXP_SUBSTR(PRESENTATION_NAME,'''\.''([^\.'']*)''\)$',1,1,null,1) DATASET
    ,USER_NAME
    ,ERROR_TEXT
    ,case when SUCCESS_FLG=0 then 'OK' when SUCCESS_FLG=1 then 'Timed Out' when SUCCESS_FLG=2 then 'Failed (row limits)' when SUCCESS_FLG=3 then 'Failed (other)' end QUERY_RESULT
    ,(END_TS - START_TS)*3600*24 QUERY_DURATION
    ,RESP_TIME_SEC RESPONSE_TIME_SECONDS
    ,CUM_DB_TIME_SEC DB_RESPONSE_TIME_SECONDS
    ,CUM_NUM_DB_ROW ROW_COUNT
    --,LOGQ.*
    from "&trackschema".LOGICALQUERIES LOGQ
);

CREATE OR REPLACE VIEW "&trackschema".OAC_USAGE_TRACKING_CLEAN_VW AS
(
select
    min(round((sysdate-query_timestamp)*3600*24)) "AGE[s]"
    ,min(extract(day from (sysdate-to_timestamp(query_timestamp))) || '.' || lpad(extract(hour from (sysdate-to_timestamp(query_timestamp))),2,'0') || ':' || lpad(extract(minute from (sysdate-to_timestamp(query_timestamp))),2,'0') || ':' || lpad(extract(second from (sysdate-to_timestamp(query_timestamp))),2,'0')) "AGE"
    ,min(to_char(query_timestamp,'YYYY-MM-DD HH24:MI:SS')) "TIME_UTC"
    ,min(to_char(from_tz(cast(query_timestamp as timestamp),'+00:00') at time zone 'Europe/Prague','YYYY-MM-DD HH24:MI:SS')) "TIME_CZ"
    ,(to_char(query_timestamp,'YYYY-MM-DD HH24:MI')) "MINUTES"
    ,(to_char(query_timestamp,'YYYY-MM-DD HH24')) "HOURS"
    ,user_name "USER NAME"
    ,dashboard_name "DASHBOARD NAME"
    ,dataset "DATASET"
    ,query_result "QUERY RESULT"
    ,error_text "ERROR TEXT"
    ,sum(row_count) "ROW COUNT"
    ,sum(query_duration) "QUERY DURATION"
    ,sum(response_time_seconds) "RESPONSE TIME [s]"
    ,sum(db_response_time_seconds) "DB RESPONSE TIME [s]"
	,DASHBOARD_PATH "DASHBOARD PATH"
from "&trackschema".OAC_USAGE_TRACKING_VW
where query_timestamp>sysdate-1 and dataset != 'USAGE_TRACKING'
group by
    to_char(query_timestamp,'YYYY-MM-DD HH24:MI')
    ,to_char(query_timestamp,'YYYY-MM-DD HH24')
    ,user_name
    ,dashboard_name
    ,dataset
    ,query_result
    ,error_text
);

CREATE OR REPLACE SYNONYM "&trackschema".USAGE_TRACKING for "&trackschema".OAC_USAGE_TRACKING_VW;
CREATE OR REPLACE SYNONYM "&trackschema".USAGE_TRACKING_CLEAN for "&trackschema".OAC_USAGE_TRACKING_CLEAN_VW;


~~~END_VERSION_SECTION~~~