<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
    <CronExpression>0 0 23 * * ?</CronExpression>
    <Editable>true</Editable>
    <Name>APIMAnalytics-AbnormalTierUsageAlert-AbnormalTierAvailabilityAlert-batch1</Name>
      <Script>
      CREATE TEMPORARY TABLE ORG_WSO2_API_REQUESTS
      USING CarbonAnalytics
      OPTIONS(tableName "ORG_WSO2_APIMGT_STATISTICS_PERMINUTEREQUEST");

      CREATE TEMPORARY TABLE ORG_WSO2_API_AVG_REQ_FOR_X_DAYS
      USING CarbonAnalytics
      OPTIONS (tableName "ORG_WSO2_ANALYTICS_APIM_AVG_REQ_FOR_X_DAYS_TBL",
         schema "applicationId STRING, api_version STRING, request_count INT"
      );

      CREATE TEMPORARY TABLE ORG_WSO2_API_ABNORMAL_ADITIONAL_DATA
      USING CarbonAnalytics
      OPTIONS (tableName "ORG_WSO2_ANALYTICS_APIM_ABNORMAL_ADITIONAL_DATA_TBL",
         schema "applicationId STRING, api_version STRING, tenantDomain STRING, applicationName STRING, applicationOwner STRING, api STRING, tier STRING"
      );

      CREATE TEMPORARY TABLE ORG_WSO2_API_PERCENTILE
      USING CarbonAnalytics
      OPTIONS (tableName "ORG_WSO2_ANALYTICS_APIM_PERCENTILE_TBL",
         schema "applicationId STRING, api_version STRING, requestsPerDayPercentile FLOAT"
      );

      CREATE TEMPORARY TABLE ORG_WSO2_API_ABNORMAL_USAGE_ALERT
      USING CarbonAnalytics
      OPTIONS (tableName "ORG_WSO2_ANALYTICS_APIM_ABNORMAL_USAGE_ALERT_TBL",
         schema "applicationId STRING, api_version STRING"
      );

      CREATE TEMPORARY TABLE ORG_WSO2_API_ABNORMAL_STREAM_TABLE
      USING org.wso2.carbon.analytics.spark.event.EventStreamProvider
      OPTIONS (streamName "org.wso2.analytics.apim.alert.abnormalTierUsageAlert",
               version "1.1.0",
               payload "applicationId STRING, applicationOwner STRING, api_version STRING, tenantDomain STRING, msg STRING, severity INTEGER, alertTimestamp LONG"
      );

      CREATE TEMPORARY TABLE ORG_WSO2_API_ALL_ALERT_STREAM_TABLE
      USING org.wso2.carbon.analytics.spark.event.EventStreamProvider
      OPTIONS (streamName "org.wso2.analytics.apim.allApimAlertsStream",
               version "1.0.0",
               payload "type STRING, tenantDomain string, msg STRING, severity INTEGER, alertTimestamp LONG"
      );

      INSERT OVERWRITE TABLE ORG_WSO2_API_PERCENTILE
      SELECT applicationId, api_version, getpercentileValue(cast(AVG(request_count) as double), cast(SQRT(AVG(request_count*request_count) - AVG(request_count)*AVG(request_count)) as double), cast(0.05 as double)) as percentile
      FROM
         (SELECT applicationId, api_version, SUM(total_request_count) as request_count
          FROM ORG_WSO2_API_REQUESTS
          WHERE context is not NULL  AND now('') > convertToTimestamp('12/2/2016') AND requestTime >= offsetInDays(-30)
          GROUP BY convertToDate(requestTime), applicationId, api_version) ORG_WSO2_API_REQUESTS_FILTERED
      GROUP BY applicationId, api_version;

     INSERT OVERWRITE TABLE ORG_WSO2_API_AVG_REQ_FOR_X_DAYS
     SELECT applicationId, api_version, SUM(total_request_count) as request_count
     FROM ORG_WSO2_API_REQUESTS
     WHERE context is not NULL  AND requestTime >= offsetInDays(-5)
     GROUP BY convertToDate(requestTime), applicationId, api_version;

     INSERT OVERWRITE TABLE ORG_WSO2_API_ABNORMAL_USAGE_ALERT
     SELECT S.applicationId, S.api_version
     FROM ORG_WSO2_API_AVG_REQ_FOR_X_DAYS S, ORG_WSO2_API_PERCENTILE D
     WHERE S.applicationId = D.applicationId AND S.api_version = D.api_version  AND D.requestsPerDayPercentile  > S.request_count
     GROUP BY S.applicationId, S.api_version
     HAVING COUNT(S.request_count) >= 5;

     INSERT OVERWRITE TABLE ORG_WSO2_API_ABNORMAL_ADITIONAL_DATA
     SELECT DISTINCT applicationId, api_version, tenantDomain, applicationName, applicationOwner, api, tier
     FROM ORG_WSO2_API_REQUESTS;

     INSERT OVERWRITE TABLE ORG_WSO2_API_ABNORMAL_STREAM_TABLE
     SELECT R.applicationId, R.applicationOwner, R.api_version, R.tenantDomain, concat(concat(concat(concat(concat(concat(concat(concat(concat('The ', R.applicationName), ' Application owned by '), R.applicationOwner), ' is consuming less than the allowed quota when accessing the '), R.api_version), ' API'), '. It currently uses a '), R.tier), ' subscription.'), 2, now('')
     FROM ORG_WSO2_API_ABNORMAL_USAGE_ALERT A, ORG_WSO2_API_ABNORMAL_ADITIONAL_DATA R
     WHERE A.api_version = R.api_version AND A.applicationId = R.applicationId;

     INSERT OVERWRITE TABLE ORG_WSO2_API_ALL_ALERT_STREAM_TABLE
     SELECT "AbnormalTierUsage", R.tenantDomain, concat(concat(concat(concat(concat(concat(concat(concat(concat('The ', R.applicationName), ' Application owned by '), R.applicationOwner), ' is consuming less than the allowed quota when accessing the '), R.api_version), ' API'), '. It currently uses a '), R.tier), ' subscription.'), 2, now('')
     FROM ORG_WSO2_API_ABNORMAL_USAGE_ALERT A, ORG_WSO2_API_ABNORMAL_ADITIONAL_DATA R
     WHERE A.api_version = R.api_version AND A.applicationId = R.applicationId;               
      
      </Script>
</Analytics>
