{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "**The Queries below are used to datamine event information through triggered conditions.** \n",
                "\n",
                "- Note that by default SQL Sentry retains a years worth of alert data. So it is recommended that you limit your time range to say the last 30-90 days.\n",
                "- Also make sure as you go through cycles of alert tuning, that you set the start times for after each tuning cycle, as the older alert data will still be there."
            ],
            "metadata": {
                "azdata_cell_guid": "d00daccc-ebb3-4dde-8e2e-78f5cef5cb7b"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "<b>Global General Condition counts with ActionType of SendEmail</b>"
            ],
            "metadata": {
                "azdata_cell_guid": "5fd122e8-f168-42da-b310-e91a67e4a3e6"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT conditiontypename AS Condition, \r\n",
                "       Count(*) AS TheCount \r\n",
                "FROM   objectconditionactionhistory ocah \r\n",
                "       JOIN conditiontype ct \r\n",
                "         ON ocah.conditiontypeid = ct.id \r\n",
                "       JOIN conditiontypecategory ctc \r\n",
                "         ON ct.conditiontypecategoryid = ctc.id \r\n",
                "WHERE  conditiontypecategoryid != '89AFED32-B625-4A6E-BE8D-9CEBBDE16A76' \r\n",
                "       AND conditiontypecategoryid != 'E2AAD766-0B60-4E5D-90A8-2AE62135E99E' and ActionTypeName = 'Send Email'\r\n",
                "\t   AND EventStartTime > dateadd(day,-90, getdate())\r\n",
                "GROUP  BY conditiontypename \r\n",
                "ORDER  BY thecount DESC \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "fe4fcb69-0d96-49e7-bed2-95c63ea9824a",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**General Condition counts by target**"
            ],
            "metadata": {
                "azdata_cell_guid": "fa509488-ebbb-42b7-a0df-a428dd5053af"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT parentobjectname AS ParentObject, \r\n",
                "       conditiontypename AS Condition, \r\n",
                "       Count(*) AS TheCount \r\n",
                "FROM   objectconditionactionhistory ocah \r\n",
                "       JOIN conditiontype ct \r\n",
                "         ON ocah.conditiontypeid = ct.id \r\n",
                "       JOIN conditiontypecategory ctc \r\n",
                "         ON ct.conditiontypecategoryid = ctc.id \r\n",
                "WHERE  conditiontypecategoryid != '89AFED32-B625-4A6E-BE8D-9CEBBDE16A76' \r\n",
                "       AND conditiontypecategoryid != 'E2AAD766-0B60-4E5D-90A8-2AE62135E99E' and ActionTypeName = 'Send Email'\r\n",
                "\t   AND EventStartTime > DATEADD(day, -90, GETDATE())\r\n",
                "GROUP  BY parentobjectname, \r\n",
                "          conditiontypename \r\n",
                "ORDER  BY thecount DESC \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "46e5d669-6c09-4d21-8591-b11e4cbc8cba",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Global Advisory Condition counts - Send to Alerting Channels/Health Score**"
            ],
            "metadata": {
                "azdata_cell_guid": "dae31c37-33de-4ad2-9997-3c1095d5bf79"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT dcd.NAME AS Condition,\r\n",
                "\tCount(*) AS TheCount\r\n",
                "FROM   DynamicConditionDefinition dcd \r\n",
                "       JOIN AlertingChannelLog acl \r\n",
                "         ON dcd.ID = acl.DynamicConditionID \r\n",
                "WHERE acl.NormalizedStartTimeUtc > DATEADD(day, -90, GETDATE())\r\n",
                "--AND ActionTypeName = 'Send Email'\r\n",
                "--AND ObjectName = '%<ServerName>%' \r\n",
                "--AND dcd.name = '%<ConditionName>%' \r\n",
                "GROUP  BY  dcd.NAME \r\n",
                "ORDER  BY thecount DESC "
            ],
            "metadata": {
                "azdata_cell_guid": "dcf4a6dc-1237-4995-af47-ff90800a3602",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Global Advisory Condition counts - Send Email**\n",
                "\n",
                "- Be sure to adjust the WHERE clause if you want to target a specific Server or Condition name"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "57a1c227-e964-4c7a-812f-537167220aa7"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT dcd.NAME AS Condition,\r\n",
                "\tCount(*) AS TheCount\r\n",
                "FROM   DynamicConditionDefinition dcd \r\n",
                "       JOIN ObjectConditionActionHistory ocah \r\n",
                "         ON dcd.ConditionID = ocah.ConditionTypeID\r\n",
                "WHERE ActionTypeName = 'Send Email'\r\n",
                "AND EventStartTime > DATEADD(day, -90, GETDATE())\r\n",
                "--AND ObjectName = '%<ServerName>%' \r\n",
                "--AND dcd.name = '%<ConditionName>%' \r\n",
                "GROUP  BY  dcd.NAME \r\n",
                "ORDER  BY thecount DESC "
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "ed0ca013-aeff-4ca8-af7c-d53724b4285e"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Advisory Condition counts by target - Send to Alerting Channels/Health Score**\n",
                "\n",
                "- Be sure to adjust the WHERE clause if you want to target a specific Server or Condition name"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "048c4b6f-857d-4245-92f5-a3e29d80d2e0"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT ObjectName AS Target,\r\n",
                "\tdcd.NAME AS Condition, \r\n",
                "\tCount(*) AS TheCount\r\n",
                "FROM   DynamicConditionDefinition dcd \r\n",
                "       JOIN AlertingChannelLog acl \r\n",
                "         ON dcd.id = acl.DynamicConditionID\r\n",
                "WHERE acl.NormalizedStartTimeUtc > DATEADD(day, -90, GETDATE())\r\n",
                "--AND ObjectName = '%<ServerName>%' \r\n",
                "--AND dcd.name = '%<ConditionName>%' \r\n",
                "GROUP  BY ObjectName, \r\n",
                "          dcd.NAME \r\n",
                "ORDER  BY thecount DESC "
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "f5ebc6ad-5a58-449e-a711-d50366f0e8f7"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Advisory Condition counts by target - Send Email**\n",
                "\n",
                "- Be sure to adjust the WHERE clause if you want to target a specific Server or Condition name"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "0cadd212-eec9-401f-a288-255cb8a972d6"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT ObjectName AS Target,\r\n",
                "\tdcd.NAME AS Condition,\r\n",
                "\tCount(*) AS TheCount\r\n",
                "FROM   DynamicConditionDefinition dcd \r\n",
                "       JOIN ObjectConditionActionHistory ocah \r\n",
                "         ON dcd.ConditionID = ocah.ConditionTypeID\r\n",
                "WHERE ActionTypeName = 'Send Email'\r\n",
                "AND EventStartTime > DATEADD(day, -90, GETDATE())\r\n",
                "--AND ObjectName = '%<ServerName>%' \r\n",
                "--AND dcd.name = '%<ConditionName>%' \r\n",
                "GROUP  BY ObjectName, \r\n",
                "          dcd.NAME \r\n",
                "ORDER  BY thecount DESC"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "f45e16ad-8701-40af-baed-8d6e7ab883da"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}