root/branch/NetworkInventory/umit/db/Retrieve.py @ 4278

# Copyright (C) 2007 Adriano Monteiro Marques
#
# Author:  Guilherme Polo <ggpolo@gmail.com>
#
# This program 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; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
# USA

from umit.db.Utils import empty
from umit.db.Utils import log_debug
from umit.db.Utils import normalize
from umit.db.Connection import ConnectDB

"""
Missing methods for:
    Traceroute retrieval.
"""

debug = log_debug('umit.db.Retrieve')

class RawRetrieve:
    """
    Retrieve raw data from database.
    """

    def __init__(self, conn, cursor):
        """
        Expects a conn and cursor from database connection.
        """
        self.conn = conn
        self.cursor = cursor


    def get_service_info_id_from_db(self, info, service_name_id):
        """
        Get service_info id based on data from info and service_name_id.
        """
        debug("Getting pk for service_info..")

        normalize(info)

        info["ostype"] = empty() # ToFix: Parser isnt storing this

        data = (
            info["product"], info["version"],
            info["extrainfo"], info["method"],
            info["conf"], service_name_id)

        s_id = self.cursor.execute("SELECT pk FROM service_info "
            "WHERE product = ? AND version = ? AND extrainfo = ? AND "
            "method = ? AND conf = ? AND fk_service_name = ?", data).fetchone()

        if s_id:
            return s_id[0]


    def get_port_id_from_db(self, portid, fk_service_info, fk_protocol,
        fk_port_state):
        """
        Get port id from database.
        """
        debug("Getting pk for port..")

        p_id = self.cursor.execute("SELECT pk FROM port "
            "WHERE portid = ? AND fk_service_info = ? AND fk_protocol = ? AND "
            "fk_port_state = ?", (portid, fk_service_info, fk_protocol,
                fk_port_state)).fetchone()

        if p_id:
            return p_id[0]


    def get_service_name_id_from_db(self, service_name):
        """
        Get id from service_name for service_name.
        """
        debug("Getting pk for service_name..")

        s_id = self.cursor.execute("SELECT pk FROM service_name "
            "WHERE name = ?",  (service_name, )).fetchone()

        if s_id:
            return s_id[0]


    def get_hostname_id_from_db(self, hostname):
        """
        Return hostname id from database based on type and name.
        """
        debug("Getting pk for hostname..")

        h_id = self.cursor.execute("SELECT pk FROM hostname "
            "WHERE type = ? AND name = ?", (hostname["type"],
                hostname["name"])).fetchone()

        if h_id:
            return h_id[0]


    def get_address_id_from_db(self, address, a_type, vendor):
        """
        Return address id from database based on address, type and vendor.
        """
        debug("Getting pk for address..")

        a_id = self.cursor.execute("SELECT pk FROM address "
            "WHERE address = ? AND type = ? AND fk_vendor = ?", (address,
                a_type, vendor)).fetchone()

        if a_id:
            return a_id[0]


    def get_address_id_for_address_from_db(self, address):
        """
        Return address id from database based on address only.
        """
        a_id = self.cursor.execute("SELECT pk FROM address "
            "WHERE address=?", (address, )).fetchone()

        if a_id:
            return a_id[0]


    def get_address_for_address_id_from_db(self, fk_address):
        """
        Return address from database based on address id.
        """
        addr = self.cursor.execute("SELECT address FROM address WHERE pk=?",
            (fk_address, )).fetchone()

        if addr:
            return addr[0]


    def get_address_pk_for_host_from_db(self, fk_host):
        """
        Return address pk from database based on host id.
        """
        self.cursor.execute("SELECT address.pk FROM address "
            "JOIN _host_address ON (_host_address.fk_address=address.pk) "
            "WHERE _host_address.fk_host=?", (fk_host, ))
        addr = self.cursor.fetchone()

        if addr:
            return addr[0]


    def get_vendor_id_from_db(self, name):
        """
        Return vendor id from database based on name.
        """
        debug("Getting pk for vendor..")

        v_id = self.cursor.execute("SELECT pk FROM vendor WHERE name = ?",
            (name, )).fetchone()

        if v_id:
            return v_id[0]


    def get_host_state_id_from_db(self, state):
        """
        Return state id from database based on state description.
        """
        debug("Getting pk for host_state..")

        h_id = self.cursor.execute("SELECT pk FROM host_state "
            "WHERE state = ?", (state, )).fetchone()

        if h_id:
            return h_id[0]


    def get_tcp_sequence_id_from_db(self, tcpseq_dict):
        """
        Return tcp_sequence id from database based on tcpsequence values.
        """
        debug("Getting pk for tcp_sequence..")

        t_id = self.cursor.execute("SELECT pk FROM tcp_sequence "
            "WHERE tcp_values = ?", (tcpseq_dict["values"], )).fetchone()

        if t_id:
            return t_id[0]


    def get_tcp_ts_sequence_id_from_db(self, tcptsseq_dict):
        """
        Return tcp_sequence id from database based on tcptssequence values.
        """
        debug("Getting pk for tcp_ts_sequence..")

        t_id = self.cursor.execute("SELECT pk FROM tcp_ts_sequence "
            "WHERE tcp_ts_values = ?", (tcptsseq_dict["values"], )).fetchone()

        if t_id:
            return t_id[0]


    def get_ip_id_sequence_id_from_db(self, ipidseq_dict):
        """
        Return ip_id_sequence id from database based on ipidseq values.
        """
        debug("Getting pk for ip_id_sequence..")

        t_id = self.cursor.execute("SELECT pk FROM ip_id_sequence "
            "WHERE ip_id_values = ?", (ipidseq_dict["values"], )).fetchone()

        if t_id:
            return t_id[0]


    def get_scan_type_id_from_db(self, name):
        """
        Return scan_type id from database based on name.
        """
        debug("Getting pk for scan_type..")

        s_id = self.cursor.execute("SELECT pk FROM scan_type "
            "WHERE name = ?", (name, )).fetchone()

        if s_id:
            return s_id[0]


    def get_port_state_id_from_db(self, state):
        """
        Return port_state id from database based on state..
        """
        debug("Getting pk for port_state..")

        p_id = self.cursor.execute("SELECT pk FROM port_state "
            "WHERE state = ?", (state, )).fetchone()

        if p_id:
            return p_id[0]


    def get_protocol_id_from_db(self, name):
        """
        Return protocol id from database based on name.
        """
        debug("Getting pk for protocol..")

        p_id = self.cursor.execute("SELECT pk FROM protocol "
            "WHERE name = ?", (name, )).fetchone()

        if p_id:
            return p_id[0]


    def get_scanner_id_from_db(self, name, version):
        """
        Return scanner id from database based on scanner name and version
        """
        debug("Getting pk for scanner..")

        s_id = self.cursor.execute("SELECT pk FROM scanner "
            "WHERE name = ? AND  version = ?", (name, version)).fetchone()

        if s_id:
            return s_id[0]


    def get_osgen_id_from_db(self, osgen):
        """
        Get id from osgen table for osgen.
        """
        debug("Getting pk for osgen..")

        o_id = self.cursor.execute("SELECT pk FROM osgen WHERE gen = ?",
            (osgen, )).fetchone()

        if o_id:
            return o_id[0]


    def get_osfamily_id_from_db(self, osfamily):
        """
        Get id from osfamily table for osfamily.
        """
        debug("Getting pk for osfamily..")

        o_id = self.cursor.execute("SELECT pk FROM osfamily "
            "WHERE family = ?", (osfamily, )).fetchone()

        if o_id:
            return o_id[0]


    def get_osvendor_id_from_db(self, osvendor):
        """
        Get id from osvendor table for osvendor.
        """
        debug("Getting pk for osvendor..")

        o_id = self.cursor.execute("SELECT pk FROM osvendor "
            "WHERE vendor = ?",  (osvendor, )).fetchone()

        if o_id:
            return o_id[0]


    def get_ostype_id_from_db(self, ostype):
        """
        Get id from ostype table for ostype.
        """
        debug("Getting pk for ostype..")

        o_id = self.cursor.execute("SELECT pk FROM ostype WHERE type = ?",
            (ostype, )).fetchone()

        if o_id:
            return o_id[0]


    def get_inventory_id_from_db(self, inventory):
        """
        Get id from inventory for inventory.
        """
        debug("Getting pk for inventory..")

        i_id = self.cursor.execute("SELECT pk FROM inventory WHERE name = ?",
            (inventory, )).fetchone()

        if i_id:
            return i_id[0]


class CompositeRetrieve(RawRetrieve):
    """
    Retrieve composite data from database.
    """

    def __init__(self, conn, cursor):
        """
        Expects a conn and cursor from database connection.
        """
        RawRetrieve.__init__(self, conn, cursor)


    def get_hosts_id_for_scan_from_db(self, fk_scan):
        """
        Get all hosts from database that are in fk_scan.
        """
        debug("Getting hosts from scan id %d..", fk_scan)

        ids = self.cursor.execute("SELECT pk FROM host WHERE fk_scan = ?",
            (fk_scan, )).fetchall()

        return ids


    def get_addrtype_for_host_from_db(self, fk_host, addrtype):
        """
        Get IPV4, IPV6 or MAC for a host.
        """
        debug("Getting %s address for host id %d..", addrtype, fk_host)

        fk_address = self.cursor.execute("SELECT fk_address "
            "FROM _host_address WHERE fk_host = ?", (fk_host, )).fetchall()

        address = None
        for fk in fk_address:
            address = self.cursor.execute("SELECT address FROM address "
                "WHERE type = ? AND pk = ?", (addrtype, fk[0], )).fetchone()
            if address:
                break

        if address:
            return address[0]


    def get_ipv4_for_host_from_db(self, fk_host):
        """
        Get IPv4 address for a host.
        """
        return self.get_addrtype_for_host_from_db(fk_host, "ipv4")


    def get_ipv6_for_host_from_db(self, fk_host):
        """
        Get IPV6 address for a host.
        """
        return self.get_addrtype_for_host_from_db(fk_host, "ipv6")


    def get_mac_for_host_from_db(self, fk_host):
        """
        Get MAC address associated with a host.
        """
        return self.get_addrtype_for_host_from_db(fk_host, "mac")


    def get_hostnames_for_host_from_db(self, fk_host):
        """
        Get hostnames associated with a host.
        """
        debug("Getting hostnames for host id %d..", fk_host)

        fk_hostname = self.cursor.execute("SELECT fk_hostname "
            "FROM _host_hostname WHERE fk_host = ?", (fk_host, )).fetchall()

        hostnames = [ ]
        for fk in fk_hostname:
            hostname = self.cursor.execute("SELECT name FROM hostname "
                "WHERE pk = ?", (fk[0], )).fetchone()
            if hostname:
                hostnames.append(hostname[0])

        return tuple(hostnames)


    def get_os_for_host_from_db(self, fk_host):
        """
        Get OS for a host from database.
        """
        os_name = self.cursor.execute("SELECT name FROM osmatch WHERE "
            "fk_host=?", (fk_host, )).fetchone()

        if os_name:
            return os_name[0]


    def get_osshort_for_host_from_db(self, fk_host):
        """
        Gets result from get_os_for_host_from_db and make it shorter.
        """
        os_name = self.get_os_for_host_from_db(fk_host)

        if os_name:
            os_name = os_name.split()[0]

        return os_name


    def get_finish_timestamp_for_scan_from_db(self, scan):
        """
        Get finish timestamp for a scan.
        """
        debug("Getting finish timestamp for scan id %d..", scan)

        fts = self.cursor.execute("SELECT finish 'as finish [timestamp]' "
            "FROM scan WHERE pk = ?", (scan, )).fetchone()[0]

        return fts


    def get_scan_details_for_scan_from_db(self, scan):
        """
        Get scan details for a scan.
        """
        debug("Getting scan details for scan id %d..", scan)

        details = self.cursor.execute("SELECT args, xmloutputversion, "
            "verbose, debugging, scanner.name, scanner.version "
            "FROM scan JOIN scanner ON (scan.fk_scanner = scanner.pk) "
            "WHERE scan.pk = ?", (scan, )).fetchone()

        return details


    """
    (c1) Missing for port data retrieve: ostype in service_info.
    Reason: NmapParser doesn't handle this yet.
    """

    def get_portid_and_state_for_host_from_db(self, host):
        """
        Get only portid and port state from port table, for a host.
        """
        debug("Getting portid and state for host id %d from table "
            "port..", host)

        pst = self.cursor.execute("SELECT port.portid, port_state.state "
            "FROM port "
            "JOIN _host_port ON (_host_port.fk_port=port.pk) "
            "JOIN port_state ON (port.fk_port_state=port_state.pk) "
            " WHERE _host_port.fk_host=?", (host, )).fetchall()

        return pst


    def get_portid_and_fks_for_host_from_db(self, host):
        """
        Get portid and fks from port table, for a host.
        """
        debug("Getting portid and foreign keys for host id %d from "
            "table port..", host)

        pdata = self.cursor.execute("SELECT portid, fk_service_info, "
            "fk_protocol, fk_port_state FROM port JOIN _host_port ON "
            "(_host_port.fk_port=port.pk) WHERE _host_port.fk_host=?",
                (host, )).fetchall()

        return pdata


    def get_port_data_for_pdata_from_db(self, protocol_id, port_state_id,
        service_info_id):
        """
        Get port data based on data returned from
        get_portid_and_fks_for_host_from_db
        """
        debug("Getting port data for pdata..")

        fullpdata = self.cursor.execute("SELECT protocol.name as protocol, "
            "port_state.state, service_info.product, "
            "service_info.version, service_info.extrainfo, "
            "service_info.method, service_info.conf, service_name.name "
            "FROM protocol, port_state, service_info, service_name "
            "WHERE protocol.pk = ? AND port_state.pk = ? AND "
            "service_info.pk = ? AND "
            "service_name.pk = service_info.fk_service_name", (protocol_id,
                port_state_id, service_info_id)).fetchall()[0]

        return fullpdata


    """
    End (c1)
    """

    def get_extraports_count_for_host_from_db(self, host_id):
        """
        Get extraports Count for host id.
        """
        debug("Getting extraports count for host id %d", host_id)

        epcount = self.cursor.execute("SELECT extraports.count "
            "FROM extraports WHERE extraports.fk_host = ?",
                (host_id, )).fetchall()

        return epcount


    def get_extraports_data_for_host_from_db(self, host_id):
        """
        Get extraport data for host id (returns Count and State).
        """
        debug("Getting extraports data for host id %d", host_id)

        epdata = self.cursor.execute("SELECT extraports.count, "
            "port_state.state FROM extraports, port_state "
            "WHERE extraports.fk_host = ? AND "
            "port_state.pk = extraports.fk_port_state", (host_id, )).fetchall()

        return epdata


    def get_fingerprint_info_for_host_from_db(self, host_id):
        """
        Get fingerprinto info for a host id.
        """
        debug("Getting fingerprinto info for host id %d", host_id)

        # W: Not using signature field for now.

        fpinfo = self.cursor.execute("SELECT uptime, lastboot, "
            "tcp_sequence_class, tcp_sequence_index, tcp_sequence_value, "
            "tcp_sequence_difficulty, tcp_ts_sequence_class, "
            "tcp_ts_sequence_value, ip_id_sequence_class, "
            "ip_id_sequence_value FROM fingerprint_info "
            "WHERE fk_host = ?", (host_id, )).fetchone()

        return fpinfo


    def get_osmatch_for_host_from_db(self, host_id):
        """
        Get osmatch data for a host id.
        """
        debug("Getting osmatch for host id %d", host_id)

        match = self.cursor.execute("SELECT name, accuracy, line FROM osmatch "
            "WHERE fk_host = ?", (host_id, )).fetchone()

        return match

    def get_osclasses_for_host_from_db(self, host_id):
        """
        Get osclasses for a host id.
        """
        debug("Getting osclasses for host id %d", host_id)

        classes = self.cursor.execute("SELECT osclass.accuracy, osgen.gen, "
            "osfamily.family, osvendor.vendor, ostype.type FROM osclass "
            "JOIN osgen ON (osclass.fk_osgen = osgen.pk) "
            "JOIN osfamily ON (osclass.fk_osfamily = osfamily.pk) "
            "JOIN osvendor ON (osclass.fk_osvendor = osvendor.pk) "
            "JOIN ostype ON (osclass.fk_ostype = ostype.pk) "
            "WHERE osclass.fk_host = ?", (host_id, )).fetchall()

        return classes


class InventoryRetrieve(CompositeRetrieve):
    """
    Retrieves inventory data from database.
    """

    def __init__(self, conn, cursor):
        CompositeRetrieve.__init__(self, conn, cursor)


    def get_inventories_ids(self):
        """
        Returns all inventories ids from database.
        """
        debug("Getting all inventories ids..")

        ids = self.cursor.execute("SELECT pk FROM inventory").fetchall()

        return ids


    def get_inventories_names(self):
        """
        Returns all inventories names from database.
        """
        debug("Getting all inventories name..")

        names = self.cursor.execute("SELECT name FROM inventory").fetchall()

        return names


    def get_inventories_ids_names(self):
        """
        Returns all inventories ids and names from database.
        """
        debug("Getting all inventories ids and names..")

        id_names = self.cursor.execute("SELECT pk, name FROM \
                            inventory").fetchall()

        return id_names


    def get_inventory_name_for_id(self, inv_id):
        """
        Returns inventory name for id.
        """
        debug("Getting inventory name for id %d..", inv_id)

        name = self.cursor.execute("SELECT name FROM inventory WHERE pk = ?",
            (inv_id, )).fetchone()

        if name:
            return name[0]


    def get_inventory_id_for_name(self, name):
        """
        Returns inventory id for name.
        """
        debug("Getting inventory id for name %s..", name)

        i_id = self.cursor.execute("SELECT pk FROM inventory WHERE name = ?",
            (name, )).fetchone()

        if i_id:
            return i_id[0]


    def get_scan_args_for_inventory_id(self, fk_inventory):
        """
        Return scan arguments for an inventory id.
        """
        debug("Getting scan arguments for inventory id %d..", fk_inventory)

        args = self.cursor.execute("SELECT args FROM scan "
            "JOIN _inventory_scan ON (scan.pk = _inventory_scan.fk_scan) "
            "WHERE _inventory_scan.fk_inventory=? LIMIT 1",
                (fk_inventory, )).fetchone()

        if args:
            return args[0]


    def get_scans_id_for_inventory(self, fk_inventory):
        """
        Returns all pks from table scan, where scan is in an inventory.
        """
        debug("Getting scans for inventory id %d..", fk_inventory)

        ids = self.cursor.execute("SELECT fk_scan FROM _inventory_scan "
            "WHERE fk_inventory = ?", (fk_inventory, )).fetchall()

        return ids


    def get_hosts_id_for_scan_from_db(self, fk_scan):
        """
        Get all pks from table host, where host is in fk_scan and host in an
        inventory.
        """
        debug("Getting hosts from scan id %d..", fk_scan)

        ids = self.cursor.execute("SELECT pk FROM host WHERE fk_scan = ? "
            "AND fk_scan IN (SELECT fk_scan FROM _inventory_scan)",
                (fk_scan, )).fetchall()

        return ids


    def get_hosts_base_data_for_inventory_from_db(self, host_address,
        fk_inventory):
        """
        Get all pks and fk_scans from table host, where: host has an especified
        host_address and host is in an especified fk_inventory.
        """
        debug("Getting pks in host with host_address %s and "
            "fk_inventory %d", host_address, fk_inventory)

        ids = self.cursor.execute("SELECT host.fk_scan, host.pk "
            "FROM host "
            "JOIN _host_address ON (host.pk = _host_address.fk_host) "
            "WHERE _host_address.fk_address = (SELECT "
            "address.pk FROM address WHERE address = ?) "
            "AND host.fk_scan IN (SELECT fk_scan FROM "
            "_inventory_scan WHERE fk_inventory = ?) ORDER BY host.pk DESC",
                (host_address, fk_inventory)).fetchall()

        return ids


    def get_finish_data_for_inventory_from_db(self, fk_inventory):
        """
        Get all finish timestamps and scan id from scans that are in an
        inventory.
        """
        debug("Getting finish timestamps for fk_inventory %d", fk_inventory)

        data = self.cursor.execute("SELECT scan.pk, "
            "scan.finish as 'finish [timestamp]' FROM scan "
            "JOIN _inventory_scan ON (_inventory_scan.fk_scan = scan.pk) "
            "WHERE _inventory_scan.fk_inventory = ? ORDER BY scan.pk DESC",
                (fk_inventory, )).fetchall()

        return data


    def get_inventory_change_category_id(self, name):
        """
        Get inventory_change_category pk based on category name.
        """
        debug("Getting change_category id for name %r", name)

        pk = self.cursor.execute("SELECT pk FROM inventory_change_category "
            "WHERE name=?", (name, )).fetchone()

        if pk:
            return pk[0]


    def get_inventory_comparison(self, old_hid, new_hid, date, fk_inventory):
        """
        Returns entry in _inventory_changes if there is a comparison stored
        for old_hid against new_hid in a date for fk_inventory.
        """
        debug("Checking if there is a comparison for old hostid %d against "
                "new hostid %d @ %s for Inventory id %d",
                old_hid, new_hid, date, fk_inventory)

        pk = self.cursor.execute("SELECT pk FROM _inventory_changes "
            "WHERE old_hostid=? AND new_hostid=? AND entry_date=? AND "
            "fk_inventory=?", (old_hid, new_hid, date, fk_inventory)).fetchall()

        if pk:
            return True

    def get_inventory_changes(self, fk_inventory, fk_address):
        """
        Returns changes in _inventory_changes for fk_inventory and
        fk_address
        """
        debug("Getting changes for Inventory id %d and Address id %d",
                fk_inventory, fk_address)


        changes = self.cursor.execute("SELECT old_hostid, new_hostid, "
            "entry_date, short_description, inventory_change_category.name "
            "FROM _inventory_changes JOIN inventory_change_category ON "
            "(_inventory_changes.fk_category=inventory_change_category.pk) "
            "WHERE fk_inventory=? AND fk_address=? ORDER BY entry_date DESC",
                (fk_inventory, fk_address)).fetchall()

        return changes


    def get_inventory_changes_for_category(self, fk_inventory, fk_address,
        fk_category):
        """
        Returns changes in _inventory_changes for fk_inventory, fk_address and
        a especific fk_category.
        """
        debug("Getting changes for Inventory id %d, Address id %d and "
                "Category id %d", fk_inventory, fk_address, fk_category)

        changes = self.cursor.execute("SELECT old_hostid, new_hostid, "
            "entry_date, short_description FROM _inventory_changes "
            "WHERE fk_category=? and fk_inventory=? AND fk_address=? "
            "ORDER BY entry_date DESC", (fk_category, fk_inventory,
                fk_address)).fetchall()

        return changes


    def get_inventory_changes_for_category_in_range(self, fk_inventory,
        fk_address, fk_category, start, end):
        """
        Returns changes in _inventory_changes for fk_inventory, fk_address and
        a especific fk_category in a time range.
        """
        debug("Getting changes for Inventory id %d, Address id %d and "
                "Category id %d from %s to %s", fk_inventory, fk_address,
                fk_category, start, end)

        changes = self.cursor.execute("SELECT old_hostid, new_hostid, "
            "entry_date, short_description FROM _inventory_changes "
            "WHERE entry_date >= ? AND entry_date < ? AND fk_category=? AND "
            "fk_inventory=? AND fk_address=? ORDER BY entry_date DESC",
                (start, end, fk_category, fk_inventory, fk_address)).fetchall()

        return changes


class ConnectInventoryDB(ConnectDB, InventoryRetrieve):
    """
    This replaces the previous UpdateChanges class in InventoryChanges module.
    ToDo: Write a better doc =)
    """

    def __init__(self, db):
        ConnectDB.__init__(self, db)
        InventoryRetrieve.__init__(self, self.conn, self.cursor)