#!/usr/bin/python
# -*- encoding: utf-8; py-indent-offset: 4 -*-
# +------------------------------------------------------------------+
# |             ____ _               _        __  __ _  __           |
# |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
# |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
# |           | |___| | | |  __/ (__|   <    | |  | | . \            |
# |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
# |                                                                  |
# | Copyright Mathias Kettner 2014             mk@mathias-kettner.de |
# +------------------------------------------------------------------+
#
# This file is part of Check_MK.
# The official homepage is at http://mathias-kettner.de/check_mk.
#
# check_mk is free software;  you can redistribute it and/or modify it
# under the  terms of the  GNU General Public License  as published by
# the Free Software Foundation in version 2.  check_mk is  distributed
# in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
# out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
# PARTICULAR PURPOSE. See the  GNU General Public License for more de-
# tails. You should have  received  a copy of the  GNU  General Public
# License along with GNU Make; see the file  COPYING.  If  not,  write
# to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
# Boston, MA 02110-1301 USA.

# There are different types of information. Can we handle them in a
# general way? There are:
#  - Percentage values
#  - Size values in KB
#  - Counters
#  - Rate counters (per second)

# <<<mssql_counters>>>
# MSSQL_SQLEXPRESS:Buffer_Manager Buffer_cache_hit_ratio 12
# MSSQL_SQLEXPRESS:Databases master Data_File(s)_Size_(KB) 2304
# MSSQL_SQLEXPRESS:Databases master Transactions/sec 13733
# MSSQL_SQLEXPRESS:Databases master Percent_Log_Used 57
# MSSQL_SQLEXPRESS:Databases master Log_File(s)_Size_(KB)
# FOOBAR 170


inventory_mssql_counters_rules = []


#TODO if not counters: raise


def inventory_mssql_counters_generic(parsed, want_counters, dflt=None):
    want_counters = set(want_counters)
    for (obj, instance), node_data in parsed.iteritems():
        for counters in node_data.values():
            if not want_counters.intersection(counters.keys()):
                continue
            yield "%s %s" % (obj, instance), dflt


#   .--main----------------------------------------------------------------.
#   |                                       _                              |
#   |                       _ __ ___   __ _(_)_ __                         |
#   |                      | '_ ` _ \ / _` | | '_ \                        |
#   |                      | | | | | | (_| | | | | |                       |
#   |                      |_| |_| |_|\__,_|_|_| |_|                       |
#   |                                                                      |
#   +----------------------------------------------------------------------+
#   |                                                                      |
#   '----------------------------------------------------------------------'

# Previously there was no main check, but we need it because
# the sub checks
# - mssql_counters.transactions
# - mssql_counters.pageactivity
# - mssql_counters.locks
# will become cluster aware and thus all subchecks, too, because they share
# the same section. This main check is just a dummy with the benefit of the
# parse function.


def parse_mssql_counters_base(info):
    parsed = {}
    for line in info:
        node_name = line[0]
        line = line[1:]
        if len(line) < 3:
            continue

        obj, counter, instance = line[:3]
        if obj.endswith(':Databases'):
            obj = obj[:-10]

        values = line[3:]
        if len(values) == 1:
            values = values[0]
            try:
                values = float(values)
            except ValueError:
                try:
                    values = int(values)
                except ValueError:
                    pass

        if counter == "utc_time":
            # mssql returns localized format. great! let's try ...
            try:
                # ... iso 8601
                values = utc_mktime(time.strptime(" ".join(values).split(".")[0],
                                                    "%Y-%m-%d %H:%M:%S"))
            except ValueError:
                try:
                    # ... german
                    values = utc_mktime(time.strptime(" ".join(values),
                                                      "%d.%m.%Y %H:%M:%S"))
                except ValueError:
                    pass

        data = parsed.setdefault((obj, instance), {})\
                     .setdefault(node_name, {})
        data.setdefault(counter, values)
    return parsed


def inventory_mssql_counters_base(parsed):
    return []


def check_mssql_counters_base(item, params, parsed):
    return 3, 'Not implemented check plugin'


check_info['mssql_counters'] = {
    'parse_function'        : parse_mssql_counters_base,
    'inventory_function'    : inventory_mssql_counters_base,
    'check_function'        : check_mssql_counters_base,
    'service_description'   : "MSSQL",
    'node_info'             : True,
}

#.
#   .--Percentage based values---------------------------------------------.
#   |          ____                         _                              |
#   |         |  _ \ ___ _ __ ___ ___ _ __ | |_ __ _  __ _  ___            |
#   |         | |_) / _ \ '__/ __/ _ \ '_ \| __/ _` |/ _` |/ _ \           |
#   |         |  __/  __/ | | (_|  __/ | | | || (_| | (_| |  __/           |
#   |         |_|   \___|_|  \___\___|_| |_|\__\__,_|\__, |\___|           |
#   |                                                |___/                 |
#   |      _                        _              _                       |
#   |     | |__   __ _ ___  ___  __| | __   ____ _| |_   _  ___  ___       |
#   |     | '_ \ / _` / __|/ _ \/ _` | \ \ / / _` | | | | |/ _ \/ __|      |
#   |     | |_) | (_| \__ \  __/ (_| |  \ V / (_| | | |_| |  __/\__ \      |
#   |     |_.__/ \__,_|___/\___|\__,_|   \_/ \__,_|_|\__,_|\___||___/      |
#   |                                                                      |
#   '----------------------------------------------------------------------'


def inventory_mssql_counters_cache_hits(parsed, want_counters):
    add_zero_based_services = host_extra_conf_merged(host_name(), inventory_mssql_counters_rules)\
                              .get('add_zero_based_services', False)

    for (obj, instance), node_data in parsed.iteritems():
        for counters in node_data.values():
            for counter in counters.keys():
                if counter not in want_counters:
                    continue

                if counters.get('%s_base' % counter, 0.0) == 0.0 \
                   and not add_zero_based_services:
                    continue

                yield "%s %s %s" % (obj, instance, counter), None


def check_mssql_counters_cache_hits(item, params, parsed):
    obj, instance, counter = item.split()
    node_data = parsed.get((obj, instance), {})

    for node_name, counters in node_data.iteritems():
        value = counters.get(counter)
        base = counters.get("%s_base" % counter, 0)

        if value is None or base is None:
            # Assume general connection problem to the database, which is reported
            # by the "X Instance" service and skip this check.
            raise MKCounterWrapped("Failed to connect to database")

        if base == 0:
            base = 1
        perc = 100.0 * value / base

        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name
        infotext = "%s%s" % (node_info, get_percent_human_readable(perc))
        state = 0
        if params:
            #TODO: Previously params=None(=dflt) in inventory_mssql_counters
            warn, crit = params
            if perc <= crit:
                state = 2
            elif perc <= warn:
                state = 1
            if state:
                infotext += " (warn/crit below %s/%s)" % (warn, crit)
        yield state, infotext, [(counter, perc)]


check_info['mssql_counters.cache_hits'] = {
    'inventory_function'    : lambda parsed: inventory_mssql_counters_cache_hits(parsed,
                              ['cache_hit_ratio', 'log_cache_hit_ratio', 'buffer_cache_hit_ratio']),
    'check_function'        : check_mssql_counters_cache_hits,
    'service_description'   : "MSSQL %s",
    'has_perfdata'          : True,
    'node_info'             : True,
}

#.
#   .--Rates---------------------------------------------------------------.
#   |                       ____       _                                   |
#   |                      |  _ \ __ _| |_ ___  ___                        |
#   |                      | |_) / _` | __/ _ \/ __|                       |
#   |                      |  _ < (_| | ||  __/\__ \                       |
#   |                      |_| \_\__,_|\__\___||___/                       |
#   |                                                                      |
#   '----------------------------------------------------------------------'

#   ---transactions---------------------------------------------------------

def check_mssql_counters_transactions(item, params, parsed):
    obj, instance = item.split()
    node_data = parsed.get((obj, instance), {})

    if not any(node_data.values()):
        # Assume general connection problem to the database, which is reported
        # by the "X Instance" service and skip this check.
        raise MKCounterWrapped("Failed to connect to database")

    for node_name, counters in node_data.iteritems():
        now = counters.get('utc_time')
        if now is None:
            now = time.time()

        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name

        for counter_key, title in [
            ('transactions/sec', 'Transactions'),
            ('write_transactions/sec', 'Write Transactions'),
            ('tracked_transactions/sec', 'Tracked Transactions'),
        ]:
            value = counters.get(counter_key)
            if value is None:
                continue

            rate = get_rate("mssql_counters.transactions.%s.%s.%s" % (node_name, item, counter_key), now, value)
            infotext = "%s%s: %.1f/s" % (node_info, title, rate)
            node_info = ""
            yield 0, infotext, [(counter_key, rate)]


check_info['mssql_counters.transactions'] = {
    'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
                              ['transactions/sec', 'write_transactions/sec', 'tracked_transactions/sec']),
    'check_function'        : check_mssql_counters_transactions,
    'service_description'   : "MSSQL %s Transactions",
    'has_perfdata'          : True,
    'node_info'             : True,
}

#   ---locks----------------------------------------------------------------

def check_mssql_counters_locks(item, params, parsed):
    obj, instance = item.split()
    node_data = parsed.get((obj, instance), {})

    if not any(node_data.values()):
        # Assume general connection problem to the database, which is reported
        # by the "X Instance" service and skip this check.
        raise MKCounterWrapped("Failed to connect to database")

    for node_name, counters in node_data.iteritems():
        now = counters.get('utc_time')
        if now is None:
            now = time.time()

        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name

        for counter_key, title in [
            ('lock_requests/sec', 'Requests'),
            ('lock_timeouts/sec', 'Timeouts'),
            ('number_of_deadlocks/sec', 'Deadlocks'),
            ('lock_waits/sec', 'Waits'),
        ]:
            value = counters.get(counter_key)
            if value is None:
                continue

            rate = get_rate("mssql_counters.locks.%s.%s.%s" % (node_name, item, counter_key), now, value)
            infotext = "%s%s: %.1f/s" % (node_info, title, rate)
            node_info = ""

            state = 0
            warn, crit = params.get(counter_key, (None, None))
            if crit is not None and rate >= crit:
                state = 2
            elif warn is not None and rate >= warn:
                state = 1
            if state:
                infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)

            yield state, infotext, [(counter_key, rate, warn, crit)]


check_info['mssql_counters.locks'] = {
    'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
                              ['number_of_deadlocks/sec', 'lock_requests/sec', 'lock_timeouts/sec', 'lock_waits/sec'],
                              dflt={}),
    'check_function'        : check_mssql_counters_locks,
    'service_description'   : "MSSQL %s Locks",
    'has_perfdata'          : True,
    'group'                 : 'mssql_counters_locks',
    'node_info'             : True,
}

#   ---sql stats------------------------------------------------------------

def inventory_mssql_counters_sqlstats(parsed, want_counters, dflt=None):
    for (obj, instance), node_data in parsed.iteritems():
        for counters in node_data.values():
            for counter in counters:
                if counter not in want_counters:
                    continue
                yield "%s %s %s" % (obj, instance, counter), dflt


def check_mssql_counters_sqlstats(item, params, parsed):
    obj, instance, counter = item.split()
    node_data = parsed.get((obj, instance), {})

    if not any(node_data.values()):
        # Assume general connection problem to the database, which is reported
        # by the "X Instance" service and skip this check.
        raise MKCounterWrapped("Failed to connect to database")

    for node_name, counters in node_data.iteritems():
        value = counters.get(counter)
        if value is None:
            return

        now = counters.get('utc_time')
        if now is None:
            now = time.time()

        rate = get_rate("mssql_counters.sqlstats.%s.%s.%s" % (node_name, item, counter), now, value)
        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name
        infotext = "%s%.1f/sec" % (node_info, rate)

        state = 0
        warn, crit = params.get(counter, (None, None))
        if crit is not None and rate >= crit:
            state = 2
        elif warn is not None and rate >= warn:
            state = 1
        if state:
            infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)

        yield state, infotext, [(counter, rate, warn, crit)]


check_info["mssql_counters.sqlstats"] = {
    "inventory_function"    : lambda parsed: inventory_mssql_counters_sqlstats(parsed,
                              ["batch_requests/sec", "sql_compilations/sec", "sql_re-compilations/sec"],
                              dflt={}),
    "check_function"        : check_mssql_counters_sqlstats,
    "service_description"   : "MSSQL %s",
    "has_perfdata"          : True,
    "group"                 : "mssql_stats",
    'node_info'             : True,
}

#   ---page activity--------------------------------------------------------

def check_mssql_counters_pageactivity(item, params, parsed):
    obj, instance = item.split()
    node_data = parsed.get((obj, instance), {})

    if not any(node_data.values()):
        # Assume general connection problem to the database, which is reported
        # by the "X Instance" service and skip this check.
        raise MKCounterWrapped("Failed to connect to database")

    for node_name, counters in node_data.iteritems():
        now = counters.get('utc_time')
        if now is None:
            now = time.time()

        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name

        for counter_key, title in  [
            ("page_reads/sec", "Reads"),
            ("page_writes/sec", "Writes"),
            ("page_lookups/sec", "Lookups")
        ]:
            value = counters.get(counter_key)
            if value is None:
                continue

            rate = get_rate("mssql_counters.pageactivity.%s.%s.%s" % (node_name, item, counter_key), now, value)
            infotext = "%s%s: %.1f/s" % (node_info, title, rate)
            node_info = ""

            state = 0
            warn, crit = params.get(counter_key, (None, None))
            if crit is not None and rate >= crit:
                state = 2
            elif warn is not None and rate >= warn:
                state = 1
            if state:
                infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)

            yield state, infotext, [(counter_key, rate, warn, crit)]


check_info['mssql_counters.pageactivity'] = {
    'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
                              ["page_reads/sec", "page_writes/sec", "page_lookups/sec"],
                              dflt={}),
    'check_function'        : check_mssql_counters_pageactivity,
    'service_description'   : "MSSQL %s Page Activity",
    'has_perfdata'          : True,
    'group'                 : "mssql_page_activity",
    'node_info'             : True,
}

#   ---locks per batch------------------------------------------------------

def inventory_mssql_counters_locks_per_batch(parsed):
    db_names = [(obj.split(":")[0], node_name)
                for (obj, instance), node_data in parsed.iteritems()
                if ":" in obj for node_name in node_data]

    for db_name, node_name in db_names:
        if "lock_requests/sec" in parsed.get(("%s:Locks" % db_name, "_Total"), {})\
                                        .get(node_name, {}) \
           and "batch_requests/sec" in parsed.get(("%s:SQL_Statistics" % db_name, "None"))\
                                             .get(node_name, {}):
            yield db_name, {}


def check_mssql_counters_locks_per_batch(item, params, parsed):
    data_locks_data = parsed.get(("%s:Locks" % item, "_Total"), {})
    data_stats_data = parsed.get(("%s:SQL_Statistics" % item, "None"), {})

    if not any(data_locks_data.values() + data_stats_data.values()):
        # Assume general connection problem to the database, which is reported
        # by the "X Instance" service and skip this check.
        raise MKCounterWrapped("Failed to connect to database")

    for node_name in set(data_locks_data.keys() + data_stats_data.keys()):
        data_locks = data_locks_data[node_name]
        data_stats = data_stats_data[node_name]
        now = data_locks.get('utc_time', data_stats.get('utc_time'))
        if now is None:
            now = time.time()

        locks = data_locks["lock_requests/sec"]
        batches = data_stats["batch_requests/sec"]

        lock_rate = get_rate("mssql_counters_locks_per_batch.%s.%s.locks" % (node_name, item), now, locks)
        batch_rate = get_rate("mssql_counters_locks_per_batch.%s.%s.batches" % (node_name, item), now, batches)

        if batch_rate == 0:
            lock_per_batch = 0
        else:
            lock_per_batch = lock_rate / batch_rate

        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name
        infotext = "%s%.1f" % (node_info, lock_per_batch)
        state = 0

        warn, crit = params.get('locks_per_batch', (None, None))
        if crit is not None and lock_per_batch >= crit:
            state = 2
        elif warn is not None and lock_per_batch >= warn:
            state = 1

        if state:
            infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)

        yield state, infotext, [("locks_per_batch", lock_per_batch, warn, crit)]


check_info["mssql_counters.locks_per_batch"] = {
    "inventory_function"    : inventory_mssql_counters_locks_per_batch,
    "check_function"        : check_mssql_counters_locks_per_batch,
    "service_description"   : "MSSQL %s Locks per Batch",
    "has_perfdata"          : True,
    "group"                 : "mssql_stats",
    'node_info'             : True,
}

#.
#   .--File Sizes----------------------------------------------------------.
#   |                _____ _ _        ____  _                              |
#   |               |  ___(_) | ___  / ___|(_)_______  ___                 |
#   |               | |_  | | |/ _ \ \___ \| |_  / _ \/ __|                |
#   |               |  _| | | |  __/  ___) | |/ /  __/\__ \                |
#   |               |_|   |_|_|\___| |____/|_/___\___||___/                |
#   |                                                                      |
#   '----------------------------------------------------------------------'


def check_mssql_file_sizes(item, params, parsed):
    obj, instance = item.split()
    node_data = parsed.get((obj, instance), {})

    if not any(node_data.values()):
        # Assume general connection problem to the database, which is reported
        # by the "X Instance" service and skip this check.
        raise MKCounterWrapped("Failed to connect to database")

    if not params:
        params = {}

    for node_name, counters in node_data.iteritems():
        node_info = ""
        if node_name:
            node_info = "[%s] " % node_name

        for counters_key, key, title in [
            ("data_file(s)_size_(kb)",     "data_files",     "Data Files"),
            ("log_file(s)_size_(kb)",      "log_files",      "Log Files total"),
            ("log_file(s)_used_size_(kb)", "log_files_used", "Log files Used"),
        ]:
            val_bytes = counters.get(counters_key)
            if val_bytes is None:
                continue

            val_bytes = val_bytes * 1024
            infotext = "%s%s: %s" % (node_info, title, get_bytes_human_readable(val_bytes))
            node_info = ""

            state = 0
            warn, crit = params.get(key, (None, None))
            if crit is not None and val_bytes >= crit:
                state = 2
            elif warn is not None and val_bytes >= warn:
                state = 1
            if state:
                infotext += " (warn/crit at %s/%s)" % (
                            get_bytes_human_readable(warn),
                            get_bytes_human_readable(crit))

            yield state, infotext, [(key, val_bytes, warn, crit)]


check_info['mssql_counters.file_sizes'] = {
    'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
                              ['data_file(s)_size_(kb)', 'log_file(s)_size_(kb)', 'log_file(s)_used_size_(kb)'],
                              dflt={}),
    'check_function'        : check_mssql_file_sizes,
    'service_description'   : "MSSQL %s File Sizes",
    'has_perfdata'          : True,
    'group'                 : "mssql_file_sizes",
    'node_info'             : True,
}
