{
   "templateGroup": {
      "uuid": "wso2-apim-analytics",
      "name": "WSO2 APIM Analytics",
      "description": "Real Time Analytics of APIM",
      "ruleTemplates": [
         {
            "uuid": "apim_frequent_tier_limit_hitting_alert",
            "name": "APIM Frequent Tier Limit Hitting Alert",
            "description": "Alerts when a user or an application goes beyond the subscribed quota frequently",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_frequent_tier_limit_hitting_alert_0\")\n@App:description(\"Alerts when a user or application goes beyond the subscribed quota frequently\")\n\n@source(type = 'wso2event', wso2.stream.id = 'org.wso2.apimgt.statistics.throttle:3.0.0', \n\t@map(type = 'wso2event'))\ndefine stream ThrottledOutStream(\n    meta_clientType string,\n    username string,\n    userTenantDomain string,\n    apiName string,\n    apiVersion string,\n    apiContext string,\n    apiCreator string,\n    apiCreatorTenantDomain string,\n    applicationId string,\n    applicationName string,\n    subscriber string,\t\n    throttledOutReason string,\n    gatewayType string,\n    throttledOutTimestamp long,\n hostname string \n);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails\tstring, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimTierLimitHittingAlert (subscriber string, apiCreator string, apiName string, applicationId string, applicationName string, apiCreatorTenantDomain string, userTenantDomain string, message string, severity int, alertTimestamp long);\n\n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type string, message string, alertTimestamp\tstring, emails\tstring);\n\ndefine stream TemporaryUserStream(username string, apiCreator string,apiName string, apiVersion string, applicationId string,applicationName string, apiCreatorTenantDomain string , userTenantDomain string, subscriber string, numHits long);\n\n--checks whether total throttled out count with the throttled out reson SUBSCRIPTION_LIMIT_EXCEEDED is higer than the specified value\n@info(name = 'TemporarySubscriberStream generating Query')\nfrom ThrottledOutStream[throttledOutReason == 'SUBSCRIPTION_LIMIT_EXCEEDED']#window.time(${countingTime})\nselect apiCreator, apiName, apiVersion, applicationId, applicationName,apiCreatorTenantDomain,userTenantDomain, subscriber, count() as numHits\ngroup by apiCreator,apiName,apiVersion, applicationId\nhaving numHits > ${numOfTierCrossing}\ninsert into TemporarySubscriberStream;\n\n--checks whether total throttled out count with the throttled out reson APPLICATION_LIMIT_EXCEEDED is higer than the specified value\n@info(name = 'TemporaryUserStream generating Query')\nfrom ThrottledOutStream[throttledOutReason == 'APPLICATION_LIMIT_EXCEEDED']#window.time(${countingTime})\nselect username, apiCreator, apiName, apiVersion, applicationId, applicationName, \napiCreatorTenantDomain,userTenantDomain, subscriber, count() as numHits\ngroup by username, apiName,apiVersion, applicationId\nhaving numHits > ${numOfTierCrossing}\ninsert into TemporaryUserStream;\n\n--this query ensures that the same type of alert is not generated for same apiCreator again for a time defined in #window.time(),\n@info(name = 'Query contains Suppression logic')\nfrom TemporarySubscriberStream#window.length(1) as a left outer join TemporarySubscriberStream#window.time(${alertSupression}) as b\non (a.apiCreator== b.apiCreator and a.apiVersion== b.apiVersion and a.applicationId==b.applicationId)\nselect ifThenElse(a.apiCreatorTenantDomain == 'carbon.super', str:concat(a.apiCreator, \"@carbon.super\"), a.apiCreator) as apiCreator, a.apiName, a.apiVersion, a.applicationId, a.applicationName, a.apiCreatorTenantDomain,a.userTenantDomain,\nifThenElse(a.userTenantDomain == 'carbon.super', str:concat(a.subscriber, \"@carbon.super\"), a.subscriber) as subscriber, a.numHits\nhaving b.apiCreator is null\ninsert into SuppressedTemporarySubscriberStream ;\n\n--this query ensures that the same type of alert is not generated for same user again for a time defined in #window.time()\n@info(name = 'query4')\nfrom TemporaryUserStream#window.length(1) as a left outer join TemporaryUserStream#window.time(${alertSupression}) as b\non (a.username== b.username and a.apiVersion== b.apiVersion and a.applicationId==b.applicationId)\nselect a.username, ifThenElse(a.apiCreatorTenantDomain == 'carbon.super', str:concat(a.apiCreator, \"@carbon.super\"), a.apiCreator) as apiCreator, a.apiName, a.apiVersion, a.applicationId, a.applicationName, a.apiCreatorTenantDomain, a.userTenantDomain,\nifThenElse(a.userTenantDomain == 'carbon.super', str:concat(a.subscriber, \"@carbon.super\"), a.subscriber) as subscriber, a.numHits\nhaving b.username is null\ninsert into SuppressedTemporaryUserStream;\n\n--send to the alert stream specific to this scenario\n@info(name = 'Query generates TierLimitHittingAlertStream')\nfrom SuppressedTemporarySubscriberStream\nselect subscriber, apiCreator, apiName, applicationId, applicationName, apiCreatorTenantDomain,userTenantDomain, str:concat(applicationName, \" Application frequently goes beyond the allocated quota when accessing \",apiName,' ',apiVersion) as message, ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into TierLimitHittingAlertStream;\n\n@info(name = 'Query generates TierLimitHittingAlertStream from supressedUserStream')\nfrom SuppressedTemporaryUserStream\nselect subscriber, apiCreator, apiName, applicationId, applicationName, apiCreatorTenantDomain, userTenantDomain, str:concat(\"User \", username, \" frequently crosses the limit set.\") as message, ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into TierLimitHittingAlertStream;\n\n@info(name='Query inserts data to ApimAllAlert Table') \nfrom TierLimitHittingAlertStream\nselect \"FrequentTierLimitHitting\" as type, userTenantDomain as tenantDomain,message,severity,alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='Query inserts data to ApimTierLimitHittingAlert table')\nfrom TierLimitHittingAlertStream\nselect *\ninsert into ApimTierLimitHittingAlert;\n\n@info(name='Email generation query') \nfrom TierLimitHittingAlertStream#window.length(1) as TL join ApimAlertStakeholderInfo as ASI\non ((TL.subscriber == ASI.userId and true == ASI.isSubscriber ) or true == ASI.isAdmin )\nselect 'FrequentTierLimitHitting' as type ,\nifThenElse(str:contains(TL.message, 'Application frequently goes beyond the allocated quota'),str:concat(\"The application \", TL.applicationName, \" owned by \", TL.subscriber, \" frequently goes beyond the allocated quota when accessing the \", TL.apiName,\" API.\"),\nstr:concat(TL.message , \" Using the \", TL.applicationName, \" application owned by \",TL.subscriber, \".\")) as message, time:dateFormat(TL.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp,ASI.emails\nhaving  str:contains(ASI.alertTypes, 'FrequentTierLimitHitting')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "numOfTierCrossing": {
                  "fieldName": "No of Tier Crossing",
                  "description": "Max Number of tier crossings for the given time window",
                  "defaultValue": "10"
               },
               "severityLevel": {
                  "fieldName": "Severity Level",
                  "description": " Severity level of the alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "3",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               },
               "countingTime": {
                  "fieldName": "Counting Time Interval",
                  "description": "Time period (day/hours/min/sec) of which the request count would be taken. ",
                  "defaultValue": "1 hours"
               },
               "alertSupression": {
                  "fieldName": "Alert Supression Period",
                  "description": "Time period to wait before resending the same alert ",
                  "defaultValue": "10 minute"
               }
            }
         },
         {
            "uuid": "apim_health_availability_alert",
            "name": "APIM Health Availability Alert",
            "description": "Monitors the Health of an API",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_health_availability_alert_0\")\n@App:description(\"Determines the availablity and health of an api\")\n\n@source(type ='inMemory', topic = 'APIM_REQUEST_ALERT_INFO')\ndefine stream RequestAlertInfoStream (apiContext string,apiName string, apiVersion string, apiResourceTemplate string, apiMethod string, apiCreator string, apiCreatorTenantDomain string, requestTimestamp long, responseTime long, backendTime long, thresholdBackendTime long, thresholdResponseTime long,responseCode int );\n\n--Table Definitions\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@primaryKey('tenantApiVersion')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertTableResponseInfo (tenantApiVersion string, count int);\n\n@primaryKey('apiName','apiVersion','tenantDomain')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimApiAvailabilityInfo (apiName string,apiVersion string, apiCreator string , tenantDomain string, status string);\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails string, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimApiHealthMonitorAlert(apiName string,apiVersion string,apiCreator string, apiCreatorTenantDomain string,\n message string, severity int, alertTimestamp long);\n\n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type string, message string, alertTimestamp\tstring, emails string);\n\ndefine stream ApimAllAlertSummary (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\npartition with (apiContext of RequestAlertInfoStream)\nbegin\n    -- checks whether the response time is higher than the threshold response time continously for 5 times\n    from every e1=RequestAlertInfoStream[responseTime>thresholdResponseTime and thresholdResponseTime>0],e2=RequestAlertInfoStream[responseTime>thresholdResponseTime and thresholdResponseTime>0],e3=RequestAlertInfoStream[responseTime>thresholdResponseTime and thresholdResponseTime>0], e4=RequestAlertInfoStream[responseTime>thresholdResponseTime and thresholdResponseTime>0],e5=RequestAlertInfoStream[responseTime>thresholdResponseTime and thresholdResponseTime>0]\n    select e1.apiName,e1.apiVersion,e1.apiContext,e1.apiCreator,e1.apiCreatorTenantDomain,e1.thresholdResponseTime\n    insert into TempResponseTimeAlertStream;\n\n    --checks whether the response code is between 500 - 600 to identify a server error\n     from every e1=RequestAlertInfoStream[responseCode>=500 and responseCode<600],e2=RequestAlertInfoStream[responseCode>=500 and responseCode<600],e3=RequestAlertInfoStream[responseCode>=500 and responseCode<600], e4=RequestAlertInfoStream[responseCode>=500 and responseCode<600],e5=RequestAlertInfoStream[responseCode>=500 and responseCode<600]\n    select e1.apiName,e1.apiVersion,e1.apiContext,e1.apiCreator,e1.apiCreatorTenantDomain\n    insert into TempResponseCodeAlertStream;\nend;\n\n@info(name='Query generates ApiHealthMonitorAlertStreamTemp with message for high response time alert')\nfrom TempResponseTimeAlertStream\nselect apiName,apiVersion, apiCreator, apiCreatorTenantDomain, str:concat('Response time is higher than ' ,thresholdResponseTime, ' ms continuously for 5 or more responses.') as message, ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApiHealthMonitorAlertStreamTemp;\n\n@info(name='Query generates ApiHealthMonitorAlertStreamTemp with message for server error alert')\nfrom TempResponseCodeAlertStream\nselect apiName, apiVersion, apiCreator, apiCreatorTenantDomain, 'Server error occurred continuously for 5 or more times.'  as message,${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApiHealthMonitorAlertStreamTemp;\n\n@info(name='Query generating TempallApimAlertStream')\nfrom ApiHealthMonitorAlertStreamTemp\nselect 'ApiHealthMonitor' as type, apiCreatorTenantDomain, str:concat('API:', apiName,' ',apiVersion, '-', message) as message , severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into TempallApimAlertStream;\n\n--inserts data into ApimAllAlertSummary, if the same alert was not generated for a time defined in #window.time\n@info(name='Query generates ApimAllAlertSummary')\nfrom TempallApimAlertStream#window.length(1) as TA left outer join ApimAllAlertSummary#window.time(${alertSupressTime}) as AAS\non TA.message == AAS.message\nselect TA.type,TA.apiCreatorTenantDomain as tenantDomain,TA.message,TA.severity,TA.alertTimestamp\nhaving AAS.message is null\ninsert into ApimAllAlertSummary;\n\n@info(name='Query inserts data to ApimApiHealthMonitorAler')\n from ApiHealthMonitorAlertStreamTemp#window.length(1) as AH left outer join ApimApiHealthMonitorAlert#window.time(${alertSupressTime}) as AM\non AH.apiName==AM.apiName and AH.apiVersion==AM.apiVersion and AH.message==AM.message\nselect AH.apiName,AH.apiVersion,AH.apiCreator,AH.apiCreatorTenantDomain,AH.message,AH.severity,AH.alertTimestamp\nhaving AM.apiName is null and AM.apiVersion is null and AM.message is null\ninsert into ApimApiHealthMonitorAlert;\n\n@info(name='Query inserts data to ApimAllAlert')\nfrom ApimAllAlertSummary\nselect type,tenantDomain,message as message,severity,alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='Query inserts data to ApimApiAvailability')\nfrom ApiHealthMonitorAlertStreamTemp\nselect apiName,apiVersion,apiCreator,apiCreatorTenantDomain as tenantDomain,message as status\nupdate or insert into ApimApiAvailabilityInfo\nset ApimApiAvailabilityInfo.status=status\non ApimApiAvailabilityInfo.apiName==apiName and ApimApiAvailabilityInfo.apiVersion==apiVersion and ApimApiAvailabilityInfo.tenantDomain==tenantDomain;\n\n@info(name='Query inserting data to ApimApiAvailability when there is no alert for a defined time')\nfrom RequestAlertInfoStream#window.length(1) as R left outer join ApiHealthMonitorAlertStreamTemp#window.time(${availablityChange}) as AHM\non R.apiName==AHM.apiName and R.apiVersion==AHM.apiVersion\nselect R.apiName as apiName,R.apiVersion as apiVersion,R.apiCreator as apiCreator,R.apiCreatorTenantDomain as tenantDomain,\"Available\" as status\nhaving AHM.apiName is null\nupdate or insert into ApimApiAvailabilityInfo\nset ApimApiAvailabilityInfo.status=status\non ApimApiAvailabilityInfo.apiName==apiName and ApimApiAvailabilityInfo.apiVersion==apiVersion and ApimApiAvailabilityInfo.apiCreator==apiCreator and ApimApiAvailabilityInfo.tenantDomain==tenantDomain;\n\n@info(name='Query inserts data to TempStream')\n from ApiHealthMonitorAlertStreamTemp#window.length(1) as AH left outer join ApiHealthMonitorAlertStreamTemp#window.time(${alertSupressTime}) as AM\non AH.apiName==AM.apiName and AH.apiVersion==AM.apiVersion and AH.message==AM.message\nselect AH.apiName,AH.apiVersion,AH.apiCreator,AH.apiCreatorTenantDomain,AH.message,AH.severity,AH.alertTimestamp\nhaving AM.apiName is null and AM.apiVersion is null and AM.message is null\ninsert into ApiHealthMonitorAlertEmailStream;\n\n@info(name='Query inserting data to Alert Stream')\nfrom ApiHealthMonitorAlertEmailStream#window.length(1) join ApimAlertStakeholderInfo\non ((ApiHealthMonitorAlertEmailStream.apiCreator == ApimAlertStakeholderInfo.userId and true == ApimAlertStakeholderInfo.isPublisher ) or true == ApimAlertStakeholderInfo.isAdmin )\nselect 'ApiHealthMonitor' as type , str:concat('API:', apiName,' ',apiVersion, '-', message) as message, time:dateFormat(ApiHealthMonitorAlertEmailStream.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp, ApimAlertStakeholderInfo.emails\nhaving str:contains(ApimAlertStakeholderInfo.alertTypes, 'ApiHealthMonitor')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "severityLevel": {
                  "fieldName": "Severity Level",
                  "description": "Severity level of the alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "2",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               },
               "alertSupressTime": {
                  "fieldName": "Alert Suppression period",
                  "description": "Same alert will be sent only once within this time period",
                  "defaultValue": "10 minute"
               },
               "availablityChange": {
                  "fieldName": "Time interval for API availability status change",
                  "description": "Time duration taken to recheck and change the availability of an API ",
                  "defaultValue": "5 minute"
               }
            }
         },
         {
            "uuid": "apim_unusual_ip_access_alert",
            "name": "APIM Unusual IP Access Alert",
            "description": "Monitoring API health",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_unusual_ip_access_alert_0\")\n@App:description(\"Alerts if an access from a strange ip is detected\")\n\n@source(type='inMemory' , topic='APIM_REQUEST')\ndefine stream Request (meta_clientType string, applicationConsumerKey string, applicationName string, applicationId string, applicationOwner string, apiContext string,apiName string, apiVersion string, apiResourcePath string, apiResourceTemplate string, apiMethod string, apiCreator string, apiCreatorTenantDomain string, apiTier string, apiHostname string, username string, userTenantDomain string, userIp string, userAgent string, requestTimestamp long, throttledOut bool, responseTime long, serviceTime long, backendTime long, responseCacheHit bool, responseSize long, protocol string, responseCode int, destination string, securityLatency long, throttlingLatency long, requestMedLat long, responseMedLat long, backendLatency long, otherLatency long, gatewayType string, label string);\n\n--Table Definitions\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimIPAccessAbnormalityAlert (type string, message string, severity int, ip string, applicationName string, applicationOwner string, username string,tenantDomain string, requestTimestamp long, alertTimestamp long);\n\n@PrimaryKey('applicationConsumerKey','ip')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimIPAccessSummary (username string, applicationConsumerKey string, ip string, lastAccessedDate long) ;\n\n@PrimaryKey('applicationConsumerKey','username')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimIPAccessAlertCount (username string, applicationConsumerKey string, requestCount long) ;\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails\tstring, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type string, message string, alertTimestamp\tstring, emails\tstring);\n\n@info(name = 'Query generates TempStream')\nfrom Request[applicationConsumerKey == ApimIPAccessSummary.applicationConsumerKey in ApimIPAccessSummary]#window.length(1) as api left outer join ApimIPAccessSummary as ais\non api.applicationConsumerKey == ais.applicationConsumerKey and api.userIp==ais.ip\nselect api.applicationConsumerKey as applicationConsumerKey, api.userTenantDomain as userTenantDomain, api.applicationName as applicationName , ifThenElse(api.userTenantDomain == 'carbon.super', str:concat(api.applicationOwner, '@carbon.super'), api.applicationOwner) as applicationOwner  ,  api.username as username, api.userIp as requestHostName, ais.ip as ip, api.requestTimestamp, ais.lastAccessedDate as lastAccessedDate, count() as count \ninsert into TempStream;\n\n@info(name = 'Query generates NewConsumerKeyStream')\nfrom Request[not (applicationConsumerKey == ApimIPAccessSummary.applicationConsumerKey in ApimIPAccessSummary)]\nselect username, applicationConsumerKey, userIp as ip, requestTimestamp as lastAccessedDate, count() as count\ninsert into NewConsumerKeyStream;\n\n--updates the IPAccessSummary table if is a new consumerKey\n@info(name = 'Query inserts data to DB Table : ApimIPAccessSummary')\nfrom NewConsumerKeyStream\nselect username, applicationConsumerKey, ip, lastAccessedDate\nupdate or insert into ApimIPAccessSummary\non ApimIPAccessSummary.applicationConsumerKey==applicationConsumerKey and ApimIPAccessSummary.ip==ip;\n\n-- Adding the alert count\n@info(name = 'Query inserts data to DB Table : ApimIPAccessAlertCount')\nfrom NewConsumerKeyStream\nselect username, applicationConsumerKey, count as requestCount\nupdate or insert into ApimIPAccessAlertCount\non ApimIPAccessAlertCount.username == username and ApimIPAccessAlertCount.applicationConsumerKey==applicationConsumerKey;\n\n/*\n    all fields will have value if the username and applicationConsumerKey already exists\n    exUsername and exConsumerKey will be null if it is a new user and newUserStream is derived based on that\n*/\n@info(name='Query generates ExistingUserStream') \nfrom TempStream as ts left outer join ApimIPAccessAlertCount as iaac\non ts.username==iaac.username and ts.applicationConsumerKey == iaac.applicationConsumerKey \nselect ts.applicationConsumerKey as applicationConsumerKey, ts.userTenantDomain, ts.applicationName , ts.applicationOwner , ts.username as username, ts.requestHostName as requestHostName, ts.ip as ip, ts.requestTimestamp, ts.lastAccessedDate as lastAccessedDate, (ts.count + iaac.requestCount) as requestCount,iaac.username as exUsername, iaac.applicationConsumerKey as exConsumerKey\ninsert into ExistingUserStream;\n\n--checks whether the username is new\n@info(name='query generates new user stream') \nfrom ExistingUserStream[exUsername  is null]\nselect ts.applicationConsumerKey as applicationConsumerKey, ts.userTenantDomain, ts.applicationName , ts.applicationOwner , ts.username as username, ts.requestHostName as requestHostName, ts.ip as ip, ts.requestTimestamp, ts.lastAccessedDate as lastAccessedDate, count() as requestCount\ninsert into NewUserStream;\n\n@info(name='query inserts data to DB table : ApimIPAccessAlertCount') \nfrom NewUserStream\nselect username, applicationConsumerKey, requestCount\nupdate or insert into ApimIPAccessAlertCount \nset ApimIPAccessAlertCount.requestCount = requestCount\non ApimIPAccessAlertCount.username == username and ApimIPAccessAlertCount.applicationConsumerKey==applicationConsumerKey;\n\n@info(name='query inserts data to DB table : ApimIPAccessSummary')\nfrom NewUserStream\nselect username, applicationConsumerKey, requestHostName as ip, requestTimestamp as lastAccessedDate\nupdate or insert into ApimIPAccessSummary\non ApimIPAccessSummary.applicationConsumerKey==applicationConsumerKey and ApimIPAccessSummary.ip==ip;\n\n/*\n    if the username  and applicatioConsumerkey already exists it increments the request count for the username.\n    exUsername will not be null if it is an existing user\n*/\n@info(name = 'query update or insert data to ApimIPAccessAlertCount')\nfrom ExistingUserStream[not (exUsername  is null)]\nselect username, applicationConsumerKey, requestCount\nupdate or insert into ApimIPAccessAlertCount\nset ApimIPAccessAlertCount.requestCount=requestCount\non ApimIPAccessAlertCount.username == username and ApimIPAccessAlertCount.applicationConsumerKey==applicationConsumerKey;\n\n@info(name = 'query update or insert data to ApimIPAccessSummary')\nfrom ExistingUserStream[not (exUsername  is null)]\nselect username, applicationConsumerKey, requestHostName as ip, requestTimestamp as lastAccessedDate\nupdate or insert into ApimIPAccessSummary\non ApimIPAccessSummary.applicationConsumerKey==applicationConsumerKey and ApimIPAccessSummary.ip==ip;\n\n\n/*\n    if a requestCount from particular user has reached the threshold limit \n    and if a new ip is detected from the particular username and applicationConsumerKey\n    Note: ip will be null if its a new ip from the user\n*/ \n@info(name = 'query generates NewIPDetectedStream')\nfrom ExistingUserStream[ip is null and requestCount > ${minCount}]\nselect *\ninsert into NewIPDetectedStream;\n\n@info(name = 'query generates AlertIPAccessAbnormalityStream')\nfrom NewIPDetectedStream\nselect \"UnusualIPAccess\" as type, \"A request from a new IP detected.\" as message,${severityLevel} as severity, requestHostName as ip, applicationName, applicationOwner, username, userTenantDomain as tenantDomain, requestTimestamp,(time:timestampInMilliseconds()) as alertTimestamp\ninsert into AlertIpAccessAbnormalityStream;\n\n@info(name = 'query inserts data to ApimIPAccessSummaryTable')\nfrom NewIPDetectedStream\nselect username, applicationConsumerKey, requestHostName as ip, requestTimestamp as lastAccessedDate\nupdate or insert into ApimIPAccessSummary\non ApimIPAccessSummary.applicationConsumerKey==applicationConsumerKey and ApimIPAccessSummary.ip==ip;\n\n-- Check if the request for the IP is received after the threshold time gap\n@info(name = 'query generates CheckAbnormalTimeBetweenAccessStream ')\nfrom ExistingUserStream[(not (ip is null)) and requestCount > ${minCount}]\nselect requestHostName, applicationConsumerKey, applicationName, applicationOwner, (requestTimestamp - lastAccessedDate) as timeBetweenAccess, requestTimestamp, username, userTenantDomain as tenantDomain, lastAccessedDate\ninsert into CheckAbnormalTimeBetweenAccessStream;\n\n@info(name = 'query genererates AbnormalTimeBetweenAccessStream')\nfrom CheckAbnormalTimeBetweenAccessStream[timeBetweenAccess > ${mdbLastAccess}]\nselect *\ninsert into AbnormalTimeBetweenAccessStream;\n\n@info(name = 'query generates AlertIpAccessAbnormalityStream')\nfrom AbnormalTimeBetweenAccessStream\nselect \"UnusualIPAccess\" as type, \"A request from an old IP detected.\" as message, ${severityLevel} as severity, requestHostName as ip, applicationName, applicationOwner, username,tenantDomain , requestTimestamp,(time:timestampInMilliseconds()) as alertTimestamp\ninsert into AlertIpAccessAbnormalityStream;\n\n-- Send all the alerts to the Global Alert Stream\n@info(name = 'query inserts data to DB table ApimAllAlert')\nfrom AlertIpAccessAbnormalityStream\nselect type, tenantDomain, str:concat(\"A request from a \", ifThenElse(str:contains(message, 'old'), 'old','new'), \" IP (\", ip, \") detected by user:\" , username,  \" using application:\", applicationName, \" owned by \", applicationOwner, \".\") as message, severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='query inserts data to DB table ApimIPAccessAbnormalityAlert') \nfrom AlertIpAccessAbnormalityStream\nselect *\ninsert into ApimIPAccessAbnormalityAlert;\n\n-- Update the request time for the lastAccessDate\n@info(name = 'query updates ApimIPAccessSummary Table')\nfrom CheckAbnormalTimeBetweenAccessStream[timeBetweenAccess > 1 day]\nselect username, applicationConsumerKey, requestHostName as ip, requestTimestamp as lastAccessedDate\nupdate or insert into ApimIPAccessSummary\nset ApimIPAccesSummary.lastAccessedDate=lastAccessedDate on\nApimIPAccessSummary.applicationConsumerKey == applicationConsumerKey and ApimIPAccessSummary.ip == ip;\n\nfrom AlertIpAccessAbnormalityStream#window.length(1) join ApimAlertStakeholderInfo as ASI\non ((AlertIpAccessAbnormalityStream.applicationOwner == ASI.userId and true == ASI.isSubscriber ) or true == ASI.isAdmin )\nselect 'UnusualIPAccess' as type , str:concat(\"A request from a \", ifThenElse(str:contains(message, 'old'), 'old','new'), \" IP (\", ip, \") detected by user:\" , AlertIpAccessAbnormalityStream.username,  \" using application:\", applicationName, \" owned by \", applicationOwner, \".\") as message, time:dateFormat(AlertIpAccessAbnormalityStream.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp, ASI.emails\nhaving  str:contains(ASI.alertTypes, 'UnusualIPAccess')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "minCount": {
                  "fieldName": "Minimum Number of Request",
                  "description": "Minimum number of request that has to be sent by user to send alert",
                  "defaultValue": "100"
               },
               "severityLevel": {
                  "fieldName": "Severity Level",
                  "description": "Severity level of the alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "2",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               },
               "mdbLastAccess": {
                  "fieldName": "Maximum Days Between Last Access",
                  "description": "Maximum days between Last access from the hostname to the latest",
                  "defaultValue": "30"
               }
            }
         },
         {
            "uuid": "apim_abnormal_response_time_alert",
            "name": "APIM  Abnormal Response Time",
            "description": "Detects requests with response time greater than a configured threshold value and triggers an alert",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_abnormal_response_time_alert_0\")\n@App:description(\"Identifies the API requests with abnormal response time and add to AllAlertStream and AbnormalResponseTimeAlertStream\")\n\n@source(type ='inMemory', topic = 'APIM_REQUEST_ALERT_INFO')\ndefine stream RequestAlertInfoStream (apiContext string,apiName string, apiVersion string, apiResourceTemplate string, apiMethod string, apiCreator string, apiCreatorTenantDomain string, requestTimestamp long, responseTime long, backendTime long, thresholdBackendTime long, thresholdResponseTime long,responseCode int );\n\ndefine stream AbnormalResponseTimeAlertStream( apiName string, apiVersion string, apiCreator string, apiCreatorTenantDomain string, apiResourceTemplate string, apiMethod string, responseTime long, thresholdResponseTime long, message string, severity int,alertTimestamp long);\n\n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type\tstring, message\tstring, alertTimestamp\tstring, emails\tstring);\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails\tstring, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAbnormalResponseTimeAlert( apiName string, apiVersion string, apiCreator string, apiCreatorTenantDomain string, apiResourceTemplate string, apiMethod string, \n    responseTime long,thresholdResponseTime long, message string, severity int, alertTimestamp long);\n\n@info(name = 'responseTimeExceedFilterQuery')\nfrom RequestAlertInfoStream[responseTime > thresholdResponseTime and thresholdResponseTime > 0]\nselect apiName, apiVersion, apiCreator, apiCreatorTenantDomain, apiResourceTemplate, apiMethod, responseTime, thresholdResponseTime , \n    'Abnormal response time detected.' as message, ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into AbnormalResponseTimeAlertStreamTemp;\n\n@info(name = 'repeatedAlertIgnoreQuery')\nfrom AbnormalResponseTimeAlertStreamTemp#window.length(1) as a left outer join AbnormalResponseTimeAlertStreamTemp#window.time(${suppressionPeriod} minute) as b\non (a.apiName == b.apiName and a.apiVersion== b.apiVersion and a.apiCreator== b.apiCreator and a.apiCreatorTenantDomain==b.apiCreatorTenantDomain and \n    a.apiResourceTemplate==b.apiResourceTemplate and a.apiMethod== b.apiMethod and a.message== b.message)\nselect a.apiName as apiName, a.apiVersion as apiVersion, a.apiCreator as apiCreator, a.apiCreatorTenantDomain as apiCreatorTenantDomain, a.apiResourceTemplate as apiResourceTemplate,\n    a.apiMethod as apiMethod, a.responseTime as responseTime, a.thresholdResponseTime as thresholdResponseTime, a.message as message, a.severity as severity, a.alertTimestamp as alertTimestamp\nhaving b.apiName  is null\ninsert into AbnormalResponseTimeAlertStream;\n\n@info(name = 'commonAlertDabaseInsertQuery')\nfrom AbnormalResponseTimeAlertStream\nselect 'AbnormalResponseTime' as type, apiCreatorTenantDomain as tenantDomain, str:concat('Abnormal response time detected for http ',apiMethod,' method of resource template:',apiResourceTemplate,' in api:', apiName,' of tenant domain:',apiCreatorTenantDomain,', threshold value:',thresholdResponseTime, 'ms.') as message, severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='dbInsertQuery') \nfrom AbnormalResponseTimeAlertStream\nselect *\ninsert into ApimAbnormalResponseTimeAlert;\n\n@info(name = 'customizeEmailMessageGenerateQuery')\nfrom AbnormalResponseTimeAlertStream#window.length(1) as A join ApimAlertStakeholderInfo as S\non ((A.apiCreator == S.userId and true == S.isPublisher ) or true == S.isAdmin )\nselect 'AbnormalResponseTime' as type , str:concat('Abnormal response time detected for http ',A.apiMethod,' method of resource template:',A.apiResourceTemplate,' in api:',A.apiName,' of tenant domain:',A.apiCreatorTenantDomain,', threshold value:',A.thresholdResponseTime, 'ms.') as message, time:dateFormat(A.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp, S.emails\nhaving str:contains(S.alertTypes, 'AbnormalResponseTime')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "severityLevel": {
                  "fieldName": "Severity Level of Abnormal Response Time",
                  "description": "Severity level of the abnormal response time alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "3",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               },
               "suppressionPeriod": {
                  "fieldName": "Alert Suppression Period in Minutes",
                  "description": "Time period to wait before resending the same alert",
                  "defaultValue": "10"
               }
            }
         },
         {
            "uuid": "apim_abnormal_backend_time_alert",
            "name": "APIM Abnormal Back End Time",
            "description": "Detects requests with backend time greater than a configured threshold value and triggers an alert",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_abnormal_backend_time_alert_0\")\n@App:description(\"Identifies the API requests with abnormal backend time and add to AllAlertStream and AbnormalBackendTimeAlertStream\")\n\n@source(type ='inMemory', topic = 'APIM_REQUEST_ALERT_INFO')\ndefine stream RequestAlertInfoStream (apiContext string,apiName string, apiVersion string, apiResourceTemplate string, apiMethod string, apiCreator string, apiCreatorTenantDomain string, requestTimestamp long, responseTime long, backendTime long, thresholdBackendTime long, thresholdResponseTime long,responseCode int );\n\ndefine stream AbnormalBackendTimeAlertStream( apiName string, apiVersion string, apiCreator string, apiCreatorTenantDomain string, apiResourceTemplate string, apiMethod string, backendTime long, thresholdBackendTime long, message string, severity int,alertTimestamp long);\n\n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type\tstring, message\tstring, alertTimestamp\tstring, emails\tstring);\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails\tstring, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAbnormalBackendTimeAlert (apiName string, apiVersion string, apiCreator string, apiCreatorTenantDomain string, apiResourceTemplate string, apiMethod string, backendTime long, thresholdBackendTime long,\n    message string, severity int, alertTimestamp long);\n\n@info(name = 'backendTimeExceedFilterQuery')\nfrom RequestAlertInfoStream[backendTime > thresholdBackendTime and thresholdBackendTime > 0]\nselect apiName, apiVersion, apiCreator, apiCreatorTenantDomain, apiResourceTemplate, apiMethod, backendTime, thresholdBackendTime , \n    'Abnormal backend time detected.' as message, ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into AbnormalBackendTimeAlertStreamTemp;\n\n@info(name = 'repeatedAlertIgnoreQuery')\nfrom AbnormalBackendTimeAlertStreamTemp#window.length(1) as a left outer join AbnormalBackendTimeAlertStreamTemp#window.time(${suppressionPeriod} minute) as b\non (a.apiName == b.apiName and a.apiVersion== b.apiVersion and a.apiCreator== b.apiCreator and a.apiCreatorTenantDomain==b.apiCreatorTenantDomain and \n    a.apiResourceTemplate==b.apiResourceTemplate and a.apiMethod== b.apiMethod and a.message== b.message)\nselect a.apiName, a.apiVersion, a.apiCreator, a.apiCreatorTenantDomain, a.apiResourceTemplate, a.apiMethod, a.backendTime, a.thresholdBackendTime, a.message as message, a.severity, a.alertTimestamp\nhaving b.apiName  is null\ninsert into AbnormalBackendTimeAlertStream;\n\n@info(name = 'commonAlertDabaseInsertQuery')\nfrom AbnormalBackendTimeAlertStream\nselect 'AbnormalBackendTime' as type, apiCreatorTenantDomain as tenantDomain, str:concat('Abnormal backend time detected for http ',apiMethod,' method of resource template:',apiResourceTemplate,' in api:', apiName,' of tenant domain:',apiCreatorTenantDomain,', threshold value:',thresholdBackendTime, 'ms.') as message, severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='dbInsertQuery') \nfrom AbnormalBackendTimeAlertStream\nselect *\ninsert into ApimAbnormalBackendTimeAlert;\n\n@info(name='customizeEmailMessageGenerateQuery') \nfrom AbnormalBackendTimeAlertStream#window.length(1) as A join ApimAlertStakeholderInfo as S\non ((A.apiCreator == S.userId and true == S.isPublisher ) or true == S.isAdmin )\nselect 'AbnormalBackendTime' as type , str:concat('Abnormal backend time detected for http ', A.apiMethod,' method of resource template:', A.apiResourceTemplate,' in api:',A.apiName,' of tenant domain:',A.apiCreatorTenantDomain,', threshold value:',A.thresholdBackendTime, 'ms.') as message, time:dateFormat(A.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp, S.emails\nhaving  str:contains(S.alertTypes, 'AbnormalBackendTime')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "severityLevel": {
                  "fieldName": "Severity Level of Abnormal Backend Time",
                  "description": "Severity level of the abnormal backend time alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "3",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               },
               "suppressionPeriod": {
                  "fieldName": "Alert Suppression Period in Minutes",
                  "description": "Time period to wait before resending the same alert",
                  "defaultValue": "10"
               }
            }
         },
         {
            "uuid": "apim_abnormal_request_count_alert",
            "name": "APIM Abnormal Request Count",
            "description": "Triggers an alert if the number of requests per minute for an application is greater than a configured threshold",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_abnormal_request_count_alert_0\")\n@App:description(\"Identifies the API requests with abnormal request count per minute and add to AllAlertStream and AbnormalRequestCountAlertStream\")\n\ndefine trigger MinTriggerEventStream at every 1 min;\ndefine stream MinTriggerEventStream (triggered_time long);\n\n-- This stream definition is only to define the Aggregation. It does not consume the actual API request.\ndefine stream Request (meta_clientType string, applicationConsumerKey string, applicationName string, applicationId string, applicationOwner string, apiContext string,apiName string, apiVersion string, apiResourcePath string, apiResourceTemplate string, apiMethod string, apiCreator string, apiCreatorTenantDomain string, apiTier string, apiHostname string, username string, userTenantDomain string, userIp string, userAgent string, requestTimestamp long, throttledOut bool, responseTime long, serviceTime long, backendTime long, responseCacheHit bool, responseSize long, protocol string, responseCode int, destination string, securityLatency long, throttlingLatency long, requestMedLat long, responseMedLat long, backendLatency long, otherLatency long, gatewayType string, label string);\n\n-- Table definitions\n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type\tstring, message\tstring, alertTimestamp\tstring, emails\tstring);\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails\tstring, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\n@PrimaryKey('applicationId','apiName', 'apiVersion')\ndefine table ApiSubAlertConf (applicationId string, subscriber string, apiName string, apiVersion string, thresholdRequestCountPerMin int);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAbnormalReqAlert(applicationName\tstring, applicationOwner string, tenantDomain string, apiName string,apiVersion string,requestCountPerMin long,   thresholdRequestCountPerMin\tint, message string,severity int, alertTimestamp long);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n-- This aggregation definition is only for retrieving data. No data is actually aggregated from this.\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\n@purging(enable='false')\ndefine aggregation ApiUserPerAppAgg\nfrom Request \nselect apiName, apiVersion, apiContext, apiCreator, apiCreatorTenantDomain, apiHostname, applicationId, applicationName, applicationOwner, username, userTenantDomain, count() as totalRequestCount, gatewayType, label,'default' as regionalID \ngroup by apiContext,apiHostname, applicationId, username, userTenantDomain\naggregate by requestTimestamp every seconds...years;\n\n-- Generate a event every one minute. The time period within which the abnormal request count should be considered is substracted from the current timestamp.\nfrom MinTriggerEventStream \nselect currentTimeMillis() as currentTimestamp, (currentTimeMillis() - 5*60*1000) as endTimestamp\ninsert into TriggerInfoStream;\n\n@info(name='requestsPerMinCountingQuery') \nfrom TriggerInfoStream as T join ApiUserPerAppAgg as A\nwithin T.endTimestamp, T.currentTimestamp\n  per \"minutes\"\nselect apiName, apiVersion, applicationId, applicationName, applicationOwner, userTenantDomain as tenantDomain, totalRequestCount as requestCountPerMin, gatewayType, label \ngroup by applicationId, apiContext, apiCreator, apiCreatorTenantDomain  \ninsert into RequestsPerMinStream;\n\n-- Filtering abnormal requests comparing with the in memory configuration table\n@info(name = 'configurationInfoRetrieveQuery')\nfrom RequestsPerMinStream as R join ApiSubAlertConf as T\non ( R.applicationId == T.applicationId and R.apiName == T.apiName and  R.apiVersion == T.apiVersion)\nselect R.applicationName, R.applicationOwner, R.tenantDomain, R.apiName, R.apiVersion, R.requestCountPerMin, T.thresholdRequestCountPerMin\ninsert into RequestCountInfoStream;\n\n@info(name = 'requestCountExceedFilterQuery')\nfrom RequestCountInfoStream[requestCountPerMin > thresholdRequestCountPerMin and thresholdRequestCountPerMin > 0]\nselect applicationName, applicationOwner,tenantDomain, apiName, apiVersion, requestCountPerMin, thresholdRequestCountPerMin,\n    'Abnormal request count detected during last minute.' as message , ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into AbnormalRequestsPerMinAlertStreamTemp;\n\n@info(name = 'repeatedAlertIgnoreQuery')\nfrom AbnormalRequestsPerMinAlertStreamTemp#window.length(1) as a left outer join AbnormalRequestsPerMinAlertStreamTemp#window.time(${suppressionPeriod} minute) as b\non (a.applicationName== b.applicationName and a.applicationOwner== b.applicationOwner and a.tenantDomain == b.tenantDomain and \n    a.apiName == b.apiName and a.apiVersion == b.apiVersion and a.message == b.message)\nselect  a.applicationName, ifThenElse(a.tenantDomain == 'carbon.super', str:concat(a.applicationOwner, \"@carbon.super\"), a.applicationOwner) as applicationOwner, \n    a.tenantDomain, a.apiName, a.apiVersion, a.requestCountPerMin, a.thresholdRequestCountPerMin, a.message, a.severity, a.alertTimestamp\nhaving b.apiName is null\ninsert into AbnormalRequestsPerMinAlertStream ;\n\n@info(name = 'commonAlertdatabaseInsertQuery')\nfrom AbnormalRequestsPerMinAlertStream\nselect 'AbnormalRequestsPerMin' as type, tenantDomain, str:concat('Abnormal request count detected during last minute using application ', applicationName,' owned by ', applicationOwner,' for api :', apiName,', abnormal requst count:', requestCountPerMin, \".\") as message, severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='dbInsertQuery') \nfrom AbnormalRequestsPerMinAlertStream\nselect *\ninsert into ApimAbnormalReqAlert;\n\n@info(name = 'query1')\nfrom AbnormalRequestsPerMinAlertStream#window.length(1) as A join ApimAlertStakeholderInfo as S\non ((A.applicationOwner == S.userId and true == S.isSubscriber ) or true == S.isAdmin )\nselect 'AbnormalRequestsPerMin' as type , str:concat('Abnormal request count detected during last minute using application ', A.applicationName,' owned by ', A.applicationOwner,' for api :', A.apiName,', abnormal request count:', A.requestCountPerMin, \".\") as message, time:dateFormat(A.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp, S.emails\nhaving str:contains(S.alertTypes, 'AbnormalRequestsPerMin')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "severityLevel": {
                  "fieldName": "Severity Level of Abnormal Request Count",
                  "description": "Severity level of abnormal request count per minute alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "2",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               },
               "suppressionPeriod": {
                  "fieldName": "Alert Suppression Period in Minutes",
                  "description": "Time period to wait before resending the same alert",
                  "defaultValue": "10"
               }
            }
         },
         {
            "uuid": "apim_abnormal_request_pattern_alert",
            "name": "APIM Abnormal Request Pattern",
            "description": "Triggers an alert if there is a change in the resource access pattern of a for a particular application",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_abnormal_request_pattern_alert_0\")\n\n@App:description(\"Identifies the API requests wchich are outside the normal access pattern and add to AllAlertStream and AbormalRequestPatternStream\")\n\n@source(type='inMemory' , topic='APIM_REQUEST')\ndefine stream Request (meta_clientType string, applicationConsumerKey string, applicationName string, applicationId string, applicationOwner string, apiContext string,apiName string, apiVersion string, apiResourcePath string, apiResourceTemplate string, apiMethod string, apiCreator string, apiCreatorTenantDomain string, apiTier string, apiHostname string, username string, userTenantDomain string, userIp string, userAgent string, requestTimestamp long, throttledOut bool, responseTime long, serviceTime long, backendTime long, responseCacheHit bool, responseSize long, protocol string, responseCode int, destination string, securityLatency long, throttlingLatency long, requestMedLat long, responseMedLat long, backendLatency long, otherLatency long, gatewayType string, label string);\n    \n@sink(type=\"inMemory\", topic=\"APIM_EMAIL_NOTIFICATION\", @map(type='passThrough'))\ndefine stream EmailAlertStream (type\tstring, message\tstring, alertTimestamp\tstring, emails\tstring);\n\n@PrimaryKey('userId', 'isSubscriber', 'isPublisher', 'isAdmin')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAlertStakeholderInfo(userId string, alertTypes\tstring, emails\tstring, isSubscriber bool, isPublisher\tbool, isAdmin\tbool);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimAllAlert (type string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimRequestPatternChangedAlert (username string, applicationName string, applicationOwner string, tenantDomain string, message string, severity int, alertTimestamp long);\n\n@info(name='stateStreamGenerateQuery') \nfrom Request\nselect  userTenantDomain as tenantDomain, username, str:concat(username,'@',applicationId) as appSpecificUsername,  applicationConsumerKey, applicationName, applicationOwner, str:concat(apiMethod,' ',apiContext,' ',apiResourceTemplate) as state,time:dateFormat(requestTimestamp, 'yyyy-MM-dd HH:mm:ss') as requestTimestamp\ninsert into StateStream;\n\n@info(name='markovChaniGenerateQuery') \nfrom StateStream#markov:markovChain(appSpecificUsername, state, ${durationToKeep} min, ${probabilityThreshold}, ${requestCount}, true)\nselect lastState as startState, transitionProbability, notify, state as endState, tenantDomain, username, applicationConsumerKey, applicationName, applicationOwner, requestTimestamp\ninsert into TransitionProbabilityStream;\n\n@info(name = 'abnormalPatternFilterQuery')\nfrom TransitionProbabilityStream [ notify ]\nselect username, requestTimestamp,  str:concat(startState,' to ',endState) as transition, transitionProbability, applicationConsumerKey, applicationName, applicationOwner, tenantDomain\ninsert into IntermediateFraudStream;\n\n@info(name = 'detailedAlertGenerateQuery')\nfrom IntermediateFraudStream\nselect username, applicationName, applicationOwner, tenantDomain, str:concat('Abnormal request pattern detected.' ,' Suspicious API transition is: ',transition) as message , transition, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into RequestPatternChangedStreamTemp;\n\n@info(name = 'repeatedAlertIgnoreQuery')\nfrom RequestPatternChangedStreamTemp#window.length(1) as a left outer join RequestPatternChangedStreamTemp#window.time(${suppressionPeriod} minute) as b\non ( a.username == b.username and a.applicationName == b.applicationName and a.applicationOwner == b.applicationOwner and a.tenantDomain == b.tenantDomain and a.message == b.message)\nselect a.username, a.applicationName, a.applicationOwner, a.tenantDomain, a.message, a.transition, a.alertTimestamp\nhaving b.username  is null\ninsert into RequestPatternChangedAlertStream;\n\n@info(name = 'commonAlertDabaseInsertQuery')\nfrom RequestPatternChangedAlertStream\nselect 'AbnormalRequestPattern' as type, tenantDomain, str:concat('Abnormal request pattern detected by user :',username,' using application : ',applicationName, ' owned by: ',applicationOwner, ' suspicious API transition is: ',transition, '.') as message, ${severityLevel} as severity, (time:timestampInMilliseconds()) as alertTimestamp\ninsert into ApimAllAlert;\n\n@info(name='dbInsertQuery') \nfrom RequestPatternChangedAlertStream\nselect username, applicationName, applicationOwner, tenantDomain, message, ${severityLevel} as severity, alertTimestamp\ninsert into ApimRequestPatternChangedAlert;\n\n@info(name = 'customizeEmailMessageGenerateQuery')\nfrom RequestPatternChangedAlertStream#window.length(1) as A join ApimAlertStakeholderInfo as S\non ((A.applicationOwner == S.userId and true == S.isSubscriber ) or true == S.isAdmin )\nselect 'AbnormalRequestPattern' as type , str:concat(A.message,  ' by user :',A.username,' using application : ',A.applicationName, ' owned by: ',A.applicationOwner, '.') as message, time:dateFormat(A.alertTimestamp,'yyyy-MM-dd HH:mm:ss') as alertTimestamp, S.emails\nhaving str:contains(S.alertTypes, 'AbnormalRequestPattern')\ninsert into EmailAlertStream;\n"
               }
            ],
            "properties": {
               "durationToKeep": {
                  "fieldName": "Duration to keep an event in minutes",
                  "description": "The maximum time duration to be considered for a continuous state change of a particular  user",
                  "defaultValue": "60"
               },
               "probabilityThreshold": {
                  "fieldName": "The alert threshold probability",
                  "description": "Alert is triggered if the probability is below this value. (0 < threshold < 1)",
                  "defaultValue": "0.2"
               },
               "requestCount": {
                  "fieldName": "Number of requests to train the model",
                  "description": "This number of requests is considered to train the Markov Matrix before generating alerts",
                  "defaultValue": "500"
               },
               "suppressionPeriod": {
                  "fieldName": "Alert Suppression Period in Minutes",
                  "description": "Time period to wait before resending the same alert",
                  "defaultValue": "10"
               },
               "severityLevel": {
                  "fieldName": "Severity Level of Abnormal Response Time",
                  "description": "Severity level of the abnormal response time alert:(1:severe,2:moderate,3:mild)",
                  "defaultValue": "1",
                  "options": [
                     "1",
                     "2",
                     "3"
                  ]
               }
            }
         },
         {
            "uuid": "apim_analytics_data_purging_app",
            "name": "APIM DATA PURGING",
            "description": "Enables Data Purging",
            "type": "template",
            "instanceCount": "one",
            "script": "",
            "templates": [
               {
                  "type": "siddhiApp",
                  "content": "@App:name(\"apim_analytics_data_purging_app_0\")\n@App:description(\"App purges data of Raw tables in APIM Analytics\")\n\ndefine trigger DataPurgingTrigger at every ${triggerTime};\n\n-- Table Used in Unusual IP Access Alert\n@PrimaryKey('applicationConsumerKey','ip')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimIPAccessSummary (username string, applicationConsumerKey string, ip string, lastAccessedDate long) ;\n\n-- Table Used in Unusual IP Access Alert\n@PrimaryKey('applicationConsumerKey','username')\n@store(type = 'rdbms', datasource = 'APIM_ANALYTICS_DB')\ndefine table ApimIPAccessAlertCount (username string, applicationConsumerKey string, requestCount long) ;\n\n-- Delete all Data from ApimIPAccessSummary when the trigger is triggered\nfrom DataPurgingTrigger\ndelete ApimIPAccessSummary on TRUE;\n\n-- Delete all Data from ApimIPAccessAlertCount when the trigger is triggered\nfrom DataPurgingTrigger\ndelete ApimIPAccessAlertCount on TRUE;"
               }
            ],
            "properties": {
               "triggerTime": {
                  "fieldName": "Time Interval For Trigger",
                  "description": "Time Interval to trigger Data purging",
                  "defaultValue": "1 years"
               }
            }
         }
      ]
   }
}