<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
    <Name>APIM_USER_AGENT_STATS</Name>
<Script>
        CREATE TEMPORARY TABLE APIUserBrowserData USING CarbonJDBC OPTIONS (dataSource "WSO2AM_STATS_DB", tableName "API_REQ_USER_BROW_SUMMARY",
    schema "api STRING ,
    version STRING ,
    apiPublisher STRING ,
    tenantDomain STRING ,
    total_request_count INTEGER ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    requestTime LONG ,
    os STRING ,
    browser STRING",
    primaryKeys "api,version,apiPublisher,year,month,day,os,browser,tenantDomain"
    );

    <!--incrementally processed per month request data table-->
    create temporary table APIMGT_PERDAY_REQUEST_DATA USING CarbonAnalytics OPTIONS(tableName "ORG_WSO2_APIMGT_STATISTICS_PERDAYREQUEST", schema "
    year INT, month INT, day INT,
    consumerKey STRING, context STRING, api_version STRING, api STRING, version STRING, maxRequestTime LONG, firstRequestTime LONG, userId STRING, hostName STRING,
    apiPublisher STRING, total_request_count_per_day LONG, resourceTemplate STRING, method STRING, applicationName STRING, tenantDomain STRING,
    userAgent STRING, resourcePath STRING, request INT, applicationId STRING, tier STRING, throttledOut BOOLEAN, clientIp STRING,
    applicationOwner STRING",
    primaryKeys "year, month, day, consumerKey, context, api_version, userId, hostName, apiPublisher, resourceTemplate, method, userAgent, clientIp",
    incrementalParams "USER_AGENT_APIMGT_PERDAY_REQUEST_DATA, DAY",
    mergeSchema "false");

    INSERT INTO TABLE APIUserBrowserData
    SELECT api,version,apiPublisher,tenantDomain,sum(total_request_count_per_day) as total_request_count,year,month,day,
    first(firstRequestTime) as requestTime,COALESCE(getOSFromUserAgent(userAgent),"UNKNOWN") os,COALESCE(getBrowserFromUserAgent(userAgent),"UNKNOWN") browser
    FROM APIMGT_PERDAY_REQUEST_DATA
    group by api,version,apiPublisher,userAgent,tenantDomain,year,month,day,COALESCE(getOSFromUserAgent(userAgent),"UNKNOWN"),COALESCE(getBrowserFromUserAgent(userAgent),"UNKNOWN");

    INCREMENTAL_TABLE_COMMIT USER_AGENT_APIMGT_PERDAY_REQUEST_DATA;
</Script>
    <CronExpression>0 0/5 * 1/1 * ? *</CronExpression>
</Analytics>