<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
    <Name>IsAnalytics-SparkScript-SessionManagement</Name>
    <Script>
        CREATE TEMPORARY TABLE activeSessionTable USING CarbonAnalytics OPTIONS (tableName "ORG_WSO2_IS_ANALYTICS_STREAM_ACTIVESESSIONS", schema "meta_tenantId INT -i -f, sessionId STRING -i -f, startTimestamp LONG -i, renewTimestamp LONG -i, terminationTimestamp LONG -i, year INT, month INT, day INT, hour INT, minute INT, action INT -i -f, username STRING -i -f, userstoreDomain STRING -i -f, remoteIp STRING -i -f, region STRING -i -f, tenantDomain STRING -i -f, serviceProvider STRING -i -f, identityProviders STRING -i -f, rememberMeFlag BOOLEAN, userAgent STRING -i -f, usernameWithTenantDomainAndUserstoreDomain STRING -i -f, _timestamp LONG -i", primaryKeys "meta_tenantId, sessionId", mergeSchema "false");

        CREATE TEMPORARY TABLE rawSessionsTable USING CarbonAnalytics OPTIONS (tableName "ORG_WSO2_IS_ANALYTICS_STREAM_SESSIONINFO", schema "meta_tenantId INT -i -f, sessionId STRING -i -f, startTimestamp LONG -i, renewTimestamp LONG -i, terminationTimestamp LONG -i, endTimestamp LONG -i, year INT, month INT, day INT, hour INT, minute INT, duration LONG -f -sp, isActive BOOLEAN -i -f, username STRING -i -f, userstoreDomain STRING -i -f, remoteIp STRING -i -f, region STRING -i, tenantDomain STRING -i -f, serviceProvider STRING -i -f, identityProviders STRING -i -f, rememberMeFlag BOOLEAN -i -f, userAgent STRING -i -f, usernameWithTenantDomainAndUserstoreDomain STRING -i -f,  _timestamp LONG -i", primaryKeys "meta_tenantId, sessionId", mergeSchema "false");

        CREATE TEMPORARY TABLE sessionManagementMeta USING CarbonAnalytics OPTIONS (tableName "ORG_WSO2_IS_ANALYTICS_STREAM_SESSIONMETA", schema "id STRING, current LONG, previous LONG", primaryKeys "id", mergeSchema "false");

        CREATE TEMPORARY TABLE sessionManagementMetaDuplicate USING CarbonAnalytics OPTIONS (tableName "ORG_WSO2_IS_ANALYTICS_STREAM_SESSIONMETA");

        INSERT INTO TABLE sessionManagementMeta SELECT 'id', now(0), (CASE WHEN A.isExist=0 then 0 ELSE B.previous END) from (SELECT 'id' as id,  count(*) as isExist from sessionManagementMetaDuplicate where id='id') A LEFT OUTER JOIN (SELECT 'id' as id, previous from sessionManagementMetaDuplicate where id='id') B ON A.id = B.id;

        INSERT INTO TABLE rawSessionsTable SELECT meta_tenantId, sessionId, startTimestamp, renewTimestamp, terminationTimestamp, -1, year, month, day, hour, minute, now(0) - startTimestamp, true, username, userstoreDomain, remoteIp, region, tenantDomain, serviceProvider, identityProviders, rememberMeFlag, userAgent, usernameWithTenantDomainAndUserstoreDomain, -1 from activeSessionTable where terminationTimestamp > now(0);

        CREATE TEMPORARY TABLE sesssionEventsNotificationTable USING org.wso2.carbon.analytics.spark.event.EventStreamProvider OPTIONS (streamName "org.wso2.is.analytics.stream.OverallSession", version "1.0.0", payload "meta_tenantId INT, sessionId STRING, startTimestamp LONG, renewTimestamp LONG, terminationTimestamp LONG, action INT, username STRING, userstoreDomain STRING, remoteIp STRING, region STRING, tenantDomain STRING, serviceProvider STRING, identityProviders STRING, rememberMeFlag BOOLEAN, userAgent STRING, _timestamp LONG");

        INSERT INTO TABLE sesssionEventsNotificationTable select meta_tenantId, sessionId, startTimestamp, renewTimestamp, terminationTimestamp, 0, username, userstoreDomain, remoteIp, region, tenantDomain, serviceProvider, identityProviders, rememberMeFlag, userAgent, now(0) from (SELECT 'id' as id, meta_tenantId, sessionId, startTimestamp, renewTimestamp, terminationTimestamp, 0, username, userstoreDomain, remoteIp, region, tenantDomain, serviceProvider, identityProviders, rememberMeFlag, userAgent, now(0) from activeSessionTable) A LEFT OUTER JOIN (SELECT id, current, previous from sessionManagementMeta where id='id') B on A.id = B.id where A.terminationTimestamp &lt; B.current AND A.terminationTimestamp >= B.previous;

        INSERT INTO TABLE sessionManagementMeta SELECT 'id', current, current from  sessionManagementMetaDuplicate where id='id';
    </Script>
    <CronExpression>0 0/5 * * * ?</CronExpression>
</Analytics>