<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
    <Name>APIM_LOGANALYZER_SCRIPT</Name>
    <Script>
        CREATE TEMPORARY TABLE CopyOfLoganalyzer USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER",
            schema "_class string -i, _content string -i, tenantID string -i, _level string -i, _trace string -i,
            _eventTimeStamp long -i",
            primaryKeys "_class, _content, _level, _eventTimeStamp");

        CREATE TEMPORARY TABLE loganalyzer_message_level_error_daily USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_MESSAGE_LEVEL_ERROR_DAILY",
            schema "tenantID string -i, message string -i, messageCount int -i, _timestamp long -i",
            incrementalParams "loganalyzer_message_level_error_daily, DAY",
            mergeSchema "false"
        );
        INSERT OVERWRITE table loganalyzer_message_level_error_daily
        SELECT
            temp.tenantID AS tenantID,
            temp.message AS logMessage,
            SUM(temp.messageCount) AS totalMessageCount,
            getDateStartingTime(temp.byYear, temp.byMonth, temp.byDay) AS _timestamp
        FROM (
            SELECT
                _content AS message,
                getYear(_eventTimeStamp) AS byYear,
                getMonth(_eventTimeStamp) AS byMonth,
                getDay(_eventTimeStamp) AS byDay,
                count(*) as messageCount,
                tenantID AS tenantID
            FROM CopyOfLoganalyzer
            WHERE _level LIKE 'ERROR'
            GROUP BY _content, _eventTimeStamp, tenantID
            ORDER BY _eventTimeStamp
        )temp
        GROUP BY temp.tenantID, temp.message, temp.byYear,temp.byMonth, temp.byDay
        ORDER BY temp.byYear, temp.byMonth, temp.byDay;
        INCREMENTAL_TABLE_COMMIT loganalyzer_message_level_error_daily;

        CREATE TEMPORARY TABLE loganalyzer_class_level_error_daily USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_CLASS_LEVEL_ERROR_DAILY",
            schema "tenantID string -i, class string -i, classCount int -i, _timestamp long -i",
            incrementalParams "loganalyzer_class_level_error_daily, DAY",
            mergeSchema "false"
        );
        INSERT OVERWRITE table loganalyzer_class_level_error_daily
        SELECT
            temp.tenantID AS tenantID,
            temp.class AS logClass,
            SUM(temp.classCount) AS totalClassCount,
            getDateStartingTime(temp.byYear, temp.byMonth , temp.byDay) AS _timestamp
        FROM (
            SELECT
                _class AS class,
                getYear(_eventTimeStamp) AS byYear,
                getMonth(_eventTimeStamp) AS byMonth,
                getDay(_eventTimeStamp) AS byDay,
                count(*) as classCount,
                tenantID AS tenantID
            FROM CopyOfLoganalyzer
            WHERE _level LIKE 'ERROR'
            GROUP BY _class, _eventTimeStamp, tenantID ORDER BY _eventTimeStamp
        )temp
        GROUP BY temp.tenantID, temp.class, temp.byYear, temp.byMonth, temp.byDay
        ORDER BY temp.byYear, temp.byMonth, temp.byDay;
        INCREMENTAL_TABLE_COMMIT loganalyzer_class_level_error_daily;

        CREATE TEMPORARY TABLE loganalyzer_class_level_error_monthly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_CLASS_LEVEL_ERROR_MONTHLY",
            schema "tenantID string -i, class string -i, classCount int -i, _timestamp long -i"
        );
        INSERT OVERWRITE table loganalyzer_class_level_error_monthly
        SELECT
            temp.tenantID AS tenantID,
            temp.class AS logClass,
            SUM(temp.classCount) AS totalClassCount,
            getMonthStartingTime(temp.byYear, temp.byMonth) AS _timestamp
        FROM (
            SELECT
                class AS class,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                first(classCount) as classCount,
                tenantID AS tenantID
            FROM loganalyzer_class_level_error_daily
            GROUP BY tenantID, class, _timestamp
            ORDER BY _timestamp
        )temp
        GROUP BY temp.tenantID, temp.class, temp.byYear, temp.byMonth
        ORDER BY temp.byYear, temp.byMonth;

        CREATE TEMPORARY TABLE loganalyzer_message_level_error_monthly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_MESSAGE_LEVEL_ERROR_MONTHLY",
            schema "tenantID string -i, message string -i, messageCount int -i, _timestamp long -i"
        );
        INSERT OVERWRITE table loganalyzer_message_level_error_monthly
        SELECT
            temp.tenantID AS tenantID,
            temp.message AS logMessage,
            SUM(temp.messageCount) AS totalMessageCount,
            getMonthStartingTime(temp.byYear, temp.byMonth) AS _timestamp
        FROM (
            SELECT
                message AS message,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                first(messageCount) as messageCount,
                tenantID AS tenantID
            FROM loganalyzer_message_level_error_daily
            GROUP BY tenantID, message, _timestamp
            ORDER BY _timestamp
        )temp
        GROUP BY temp.tenantID, temp.message, temp.byYear, temp.byMonth
        ORDER BY temp.byYear, temp.byMonth;

        CREATE TEMPORARY TABLE loganalyzer_class_level_error_weekly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_CLASS_LEVEL_ERROR_WEEKLY",
            schema "tenantID string -i, class string -i, classCount int -i, week long -i, _timestamp long -i"
        );
        INSERT OVERWRITE table loganalyzer_class_level_error_weekly
        SELECT
            temp.tenantID AS tenantID,
            temp.class AS logClass,
            SUM(temp.classCount) AS totalClassCount,
            temp.byWeek AS week,
            getWeekStartingTime(temp.byYear, temp.byMonth, temp.byWeek) AS _timestamp
        FROM (
            SELECT
                class AS class,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                getWeek(_timestamp) AS byWeek,
                first(classCount) as classCount,
                tenantID AS tenantID
            FROM loganalyzer_class_level_error_daily
            GROUP BY tenantID, class, _timestamp
            ORDER BY _timestamp
        )temp
        GROUP BY temp.tenantID, temp.class, temp.byYear, temp.byMonth, temp.byWeek
        ORDER BY temp.byYear, temp.byMonth, temp.byWeek;

        CREATE TEMPORARY TABLE loganalyzer_message_level_error_weekly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_MESSAGE_LEVEL_ERROR_WEEKLY",
            schema "tenantID string -i, message string -i, messageCount int -i, week long -i, _timestamp long -i"
        );
        INSERT OVERWRITE table loganalyzer_message_level_error_weekly
        SELECT
            temp.tenantID AS tenantID,
            temp.message AS logMessage,
            SUM(temp.messageCount) AS totalMessageCount,
            temp.byWeek AS week,
            getWeekStartingTime(temp.byYear, temp.byMonth, temp.byWeek) AS _timestamp
        FROM (
            SELECT
                message AS message,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                getWeek(_timestamp) AS byWeek,
                first(messageCount) AS messageCount,
                tenantID AS tenantID
            FROM loganalyzer_message_level_error_daily
            GROUP BY tenantID, message, _timestamp
            ORDER BY _timestamp
        )temp
        GROUP BY temp.tenantID, temp.message, temp.byYear, temp.byMonth, temp.byWeek
        ORDER BY temp.byYear, temp.byMonth, temp.byWeek;

        CREATE TEMPORARY TABLE loganalyzer_apim_artifact_deployed_daily USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIM_ARTIFACT_DEPLOYED_DAILY",
            schema "artifact string -i -f, tenantID string -i, artifactCount int -i, _timestamp long -i",
            incrementalParams "loganalyzer_apim_artifact_deployed_daily, DAY",
            mergeSchema "false"
        );
        INSERT OVERWRITE table loganalyzer_apim_artifact_deployed_daily
        SELECT
            temp.artifact AS logArtifact,
            temp.tenantID AS tenantID,
            SUM(temp.artifactCount) AS totalArtifactCount,
            getDateStartingTime(temp.byYear, temp.byMonth , temp.byDay) AS _timestamp
        FROM (
            SELECT
                SUBSTRING(_content,18,100) AS artifact,
                getYear(_eventTimeStamp) AS byYear,
                getMonth(_eventTimeStamp) AS byMonth,
                getDay(_eventTimeStamp) AS byDay,
                count(*) AS artifactCount,
                tenantID AS tenantID
            FROM CopyOfLoganalyzer
            WHERE _content LIKE "%Initializing API:%"
            GROUP BY tenantID, _content, _eventTimeStamp
            ORDER BY _eventTimeStamp
        )temp
        GROUP BY temp.tenantID, temp.artifact, temp.byYear, temp.byMonth, temp.byDay
        ORDER BY temp.byYear, temp.byMonth, temp.byDay;
        INCREMENTAL_TABLE_COMMIT loganalyzer_apim_artifact_deployed_daily;

        CREATE TEMPORARY TABLE loganalyzer_apim_artifact_deleted_daily USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIM_ARTIFACT_DELETED_DAILY",
            schema "artifact string -i -f, tenantID string -i, artifactCount int -i, _timestamp long -i",
            incrementalParams "loganalyzer_apim_artifact_deleted_daily, DAY",
            mergeSchema "false"
        );
        INSERT OVERWRITE table loganalyzer_apim_artifact_deleted_daily
        SELECT
            temp.artifact AS logArtifact,
            temp.tenantID AS tenantID,
            SUM(temp.artifactCount) AS totalArtifactCount,
            getDateStartingTime(temp.byYear, temp.byMonth , temp.byDay) AS _timestamp
        FROM (
            SELECT
                SUBSTRING(_content,17,100) AS artifact,
                getYear(_eventTimeStamp) AS byYear,
                getMonth(_eventTimeStamp) AS byMonth,
                getDay(_eventTimeStamp) AS byDay,
                count(*) AS artifactCount,
                tenantID AS tenantID
            FROM CopyOfLoganalyzer
            WHERE _content LIKE "%Destroying API:%"
            GROUP BY tenantID, _content, _eventTimeStamp
            ORDER BY _eventTimeStamp
        )temp
        GROUP BY temp.tenantID, temp.artifact, temp.byYear, temp.byMonth, temp.byDay
        ORDER BY temp.byYear, temp.byMonth, temp.byDay;

        CREATE TEMPORARY TABLE loganalyzer_apim_message_processing_daily USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIM_MESSAGE_PROCESSING_DAILY",
            schema "tenantID string -i, apiName string -i, fullApiName string -i, apiCount int -i, _timestamp long -i",
            incrementalParams "loganalyzer_apim_message_processing_daily, DAY",
            mergeSchema "false"
        );
        INSERT OVERWRITE table loganalyzer_apim_message_processing_daily
        SELECT
            temp.tenantID AS tenantID,
            temp.apiName AS logApiName,
            temp.fullApiName AS fullApiName,
            SUM(temp.apiCount) AS totalAPICount,
            getDateStartingTime(temp.byYear, temp.byMonth , temp.byDay) AS _timestamp
        FROM (
            SELECT
                getRegexMatch(_class, "(?&lt;=API_LOGGER.admin--)(.*)") AS apiName,
                _class AS fullApiName,
                getYear(_eventTimeStamp) AS byYear,
                getMonth(_eventTimeStamp) AS byMonth,
                getDay(_eventTimeStamp) AS byDay,
                count(*) AS apiCount,
                tenantID AS tenantID
            FROM CopyOfLoganalyzer
            WHERE _content LIKE "%Error while building message%" AND _class LIKE "%API_LOGGER.admin--%"
            GROUP BY tenantID, _class, _eventTimeStamp
            ORDER BY _eventTimeStamp
        )temp
        GROUP BY temp.tenantID, temp.apiName, temp.byYear, temp.byMonth, temp.byDay, temp.fullApiName
        ORDER BY temp.byYear, temp.byMonth, temp.byDay;
        INCREMENTAL_TABLE_COMMIT loganalyzer_apim_message_processing_daily;


        CREATE TEMPORARY TABLE loganalyzer_apim_message_processing_weekly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIM_MESSAGE_PROCESSING_WEEKLY",
            schema "tenantID string -i, apiName string -i, fullApiName string , apiCount int -i, _timestamp long -i,
            week long -i"
        );
        INSERT OVERWRITE table loganalyzer_apim_message_processing_weekly
        SELECT
            temp.tenantID AS tenantID,
            temp.apiName AS logApiName,
            temp.fullApiName AS fullApiName,
            SUM(temp.apiCount) AS totalAPICount,
            temp.byWeek AS week,
            getWeekStartingTime(temp.byYear, temp.byMonth, temp.byWeek) AS _timestamp
        FROM (
            SELECT
                apiName AS apiName,
                fullApiName AS fullApiName,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                getWeek(_timestamp) AS byWeek,
                first(apiCount) AS apiCount,
                tenantID AS tenantID
            FROM loganalyzer_apim_message_processing_daily
            GROUP BY tenantID, apiName, _timestamp, fullApiName
            ORDER BY _timestamp
        )temp
        GROUP BY temp.tenantID, temp.apiName, temp.byYear, temp.byMonth, temp.byWeek, temp.fullApiName
        ORDER BY temp.byYear, temp.byMonth, temp.byWeek;

        CREATE TEMPORARY TABLE loganalyzer_apim_message_processing_monthly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIM_MESSAGE_PROCESSING_MONTHLY",
            schema "tenantID string -i, apiName string -i, fullApiName string, apiCount int -i, _timestamp long -i"
        );
        INSERT OVERWRITE table loganalyzer_apim_message_processing_monthly
        SELECT
            temp.tenantID AS tenantID,
            temp.apiName AS logApiName,
            temp.fullApiName AS fullApiName,
            SUM(temp.apiCount) AS totalAPICount,
            getMonthStartingTime(temp.byYear, temp.byMonth) AS _timestamp
        FROM (
            SELECT
                apiName AS apiName,
                fullApiName AS fullApiName,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                first(apiCount) AS apiCount,
                tenantID AS tenantID
            FROM loganalyzer_apim_message_processing_daily
            GROUP BY tenantID, apiName, _timestamp, fullApiName
            ORDER BY _timestamp
        )temp
        GROUP BY temp.tenantID, temp.apiName, temp.byYear, temp.byMonth, temp.fullApiName
        ORDER BY temp.byYear, temp.byMonth;

        CREATE TEMPORARY TABLE loganalyzer_apikey_status USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIKEY_STATUS",
            schema "status string -i, content string -i, byYear int -i, byMonth int -i, byDay int -i, _timestamp long -i",
            incrementalParams "loganalyzer_apikey_status, DAY",
            mergeSchema "false"
        );
        INSERT OVERWRITE table loganalyzer_apikey_status
        SELECT
            "AUTH_FAIL_MISSING_INFO" AS status,
            _content AS message,
            getYear(_eventTimeStamp) AS byYear,
            getMonth(_eventTimeStamp) AS byMonth,
            getDay(_eventTimeStamp) AS byDay,
            _eventTimeStamp AS eventTimeStamp
        FROM CopyOfLoganalyzer
        WHERE _content LIKE '%API authentication failure due to Missing Credentials%' ;

        INSERT INTO TABLE loganalyzer_apikey_status
        SELECT
            "INVALID_KEY" AS status,
            _content AS message,
            getYear(_eventTimeStamp) AS byYear,
            getMonth(_eventTimeStamp) AS byMonth,
            getDay(_eventTimeStamp) AS byDay,
            _eventTimeStamp AS eventTimeStamp
        FROM CopyOfLoganalyzer
        WHERE _content LIKE '%API authentication failure due to Invalid Credentials%';

        INSERT INTO TABLE loganalyzer_apikey_status
        SELECT
            "REVOKED_KEY" AS status,
            _content AS message,
            getYear(_eventTimeStamp) AS byYear,
            getMonth(_eventTimeStamp) AS byMonth,
            getDay(_eventTimeStamp) AS byDay,
            _eventTimeStamp AS eventTimeStamp
        FROM CopyOfLoganalyzer
        WHERE _content LIKE '%API authentication failure due to Invalid Credentials%';
        INCREMENTAL_TABLE_COMMIT loganalyzer_apikey_status;

        CREATE TEMPORARY TABLE invalidLoginAttemptDaily USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_INVALID_LOGIN_ATTEMPT_DAILY",
            schema "tenantID string -i, InvalidLoginCount int -i, _timestamp long -i",
            incrementalParams "invalidLoginAttemptDaily, DAY",
            mergeSchema "false"
        );

        CREATE TEMPORARY TABLE invalidLoginAttemptWeekly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_INVALID_LOGIN_ATTEMPT_WEEKLY",
            schema "tenantID string -i, InvalidLoginCount int -i, _timestamp long -i, week long -i"
        );

        CREATE TEMPORARY TABLE invalidLoginAttemptMonthly USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_INVALID_LOGIN_ATTEMPT_MONTHLY",
            schema "tenantID string -i, InvalidLoginCount int -i, _timestamp long -i"
        );

        INSERT OVERWRITE table invalidLoginAttemptDaily
        SELECT
            temp.agent AS agent,
            SUM(temp.agentCount) AS totalAgentCount,
            getDateStartingTime(temp.byYear, temp.byMonth , temp.byDay) AS _timestamp
        FROM (
            SELECT
                tenantID AS agent,
                getYear(_eventTimeStamp) AS byYear,
                getMonth(_eventTimeStamp) AS byMonth,
                getDay(_eventTimeStamp) AS byDay,
                count(*) AS agentCount
            FROM CopyOfLoganalyzer
            WHERE _content LIKE '%login attempt%'
            GROUP BY tenantID, _eventTimeStamp
            ORDER BY _eventTimeStamp
        )temp
        GROUP BY temp.agent, temp.byYear, temp.byMonth, temp.byDay
        ORDER BY temp.byYear, temp.byMonth, temp.byDay;
        INCREMENTAL_TABLE_COMMIT invalidLoginAttemptDaily;

        INSERT OVERWRITE table invalidLoginAttemptMonthly
        SELECT
            temp.agent AS agent,
            SUM(temp.agentCount) AS totalAgentCount,
            getMonthStartingTime(temp.byYear, temp.byMonth) AS _timestamp
        FROM (
            SELECT
                tenantID AS agent,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                first(InvalidLoginCount) AS agentCount
                FROM invalidLoginAttemptDaily
            GROUP BY tenantID, _timestamp
            ORDER BY _timestamp
        )temp
        GROUP BY temp.agent, temp.byYear, temp.byMonth
        ORDER BY temp.byYear, temp.byMonth;

        INSERT OVERWRITE table invalidLoginAttemptWeekly
        SELECT
            temp.agent AS agent,
            SUM(temp.agentCount) AS totalAgentCount,
            temp.byWeek AS week,
            getWeekStartingTime(temp.byYear, temp.byMonth, temp.byWeek) AS _timestamp
        FROM (
            SELECT
                tenantID AS agent,
                getWeek(_timestamp) AS byWeek,
                getYear(_timestamp) AS byYear,
                getMonth(_timestamp) AS byMonth,
                first(InvalidLoginCount) as agentCount
            FROM invalidLoginAttemptDaily
            GROUP BY tenantID, _timestamp
            ORDER BY _timestamp
        )temp
        GROUP BY temp.agent, temp.byYear, temp.byMonth, temp.byWeek
        ORDER BY temp.byYear, temp.byMonth, temp.byWeek;

        CREATE TEMPORARY TABLE loganalyzer_apim_audit_log USING CarbonAnalytics
        OPTIONS (
            tableName "LOGANALYZER_APIM_AUDIT_LOG",
            schema "tenantID string -i, user string, action string -i, type string -i, info string,_timestamp long -i",
            mergeSchema "true"
        );

        INSERT OVERWRITE table loganalyzer_apim_audit_log
        SELECT
            tenantID AS tenantID,
            parseJson(_content, "performedBy") as user,
            parseJson(_content, "action") as action,
            parseJson(_content, "typ") as type,
            parseJson(_content, "info") as info,
            _eventTimeStamp as _timestamp
        FROM CopyOfLoganalyzer
        WHERE _class = 'AUDIT_LOG' AND _content LIKE "%performedBy%"
        ORDER BY _eventTimeStamp;
    </Script>
    <CronExpression>0 0/15 * 1/1 * ? *</CronExpression>

</Analytics>
