root/branch/ggpolo/umitDB/Retrieve.py @ 1404

Revision 1404, 28.7 kB (checked in by ggpolo, 6 years ago)

Fixed a bug in umitDB/Retrieve
Line 
1# Copyright (C) 2007 Insecure.Com LLC.
2#
3# Author:  Guilherme Polo <ggpolo@gmail.com>
4#
5# This program is free software; you can redistribute it and/or modify
6# it under the terms of the GNU General Public License as published by
7# the Free Software Foundation; either version 2 of the License, or
8# (at your option) any later version.
9#
10# This program is distributed in the hope that it will be useful,
11# but WITHOUT ANY WARRANTY; without even the implied warranty of
12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13# GNU General Public License for more details.
14#
15# You should have received a copy of the GNU General Public License
16# along with this program; if not, write to the Free Software
17# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
18# USA
19
20from umitDB.Utils import empty
21from umitDB.Utils import debug
22from umitDB.Utils import normalize
23from umitDB.Connection import ConnectDB
24
25"""
26Missing methods for:
27    Traceroute retrieval.
28"""
29
30class RawRetrieve:
31    """
32    Retrieve raw data from database.
33    """
34   
35    def __init__(self, conn, cursor):
36        """
37        Expects a conn and cursor from database connection.   
38        """
39        self.conn = conn
40        self.cursor = cursor
41       
42       
43    def get_service_info_id_from_db(self, info, service_name_id):
44        """
45        Get service_info id based on data from info and service_name_id.
46        """
47        debug("Getting pk for service_info..")
48       
49        normalize(info)
50
51        info["ostype"] = empty() # ToFix: Parser isnt storing this
52
53        data = (info["service_product"], info["service_version"],
54                info["service_extrainfo"], info["service_method"],
55                info["service_conf"], service_name_id)
56       
57        s_id = self.cursor.execute("SELECT pk FROM service_info WHERE \
58                    product = ? AND version = ? AND extrainfo = ? AND \
59                    method = ? AND conf = ? AND fk_service_name = ?",
60                    data).fetchone()
61
62        if s_id:
63            return s_id[0]
64
65
66    def get_port_id_from_db(self, portid, fk_service_info, fk_protocol,
67                            fk_port_state):
68        """
69        Get port id from database.
70        """
71        debug("Getting pk for port..")
72
73        p_id = self.cursor.execute("SELECT pk FROM port WHERE portid = ? \
74                        AND fk_service_info = ? AND fk_protocol = ? AND \
75                        fk_port_state = ?", (portid, fk_service_info, 
76                                             fk_protocol, 
77                                             fk_port_state)).fetchone()
78
79        if p_id:
80            return p_id[0]
81
82
83    def get_service_name_id_from_db(self, service_name):
84        """
85        Get id from service_name for service_name.
86        """
87        debug("Getting pk for service_name..")
88
89        s_id = self.cursor.execute("SELECT pk FROM service_name \
90                            WHERE name = ?",  (service_name, )).fetchone()
91
92        if s_id:
93            return s_id[0]
94
95
96    def get_hostname_id_from_db(self, hostname):
97        """
98        Return hostname id from database based on type and name.
99        """
100        debug("Getting pk for hostname..")
101       
102        h_id = self.cursor.execute("SELECT pk FROM hostname WHERE \
103                             type = ? AND name = ?", (hostname["hostname_type"],
104                             hostname["hostname"])).fetchone()
105               
106        if h_id:
107            return h_id[0]
108   
109
110    def get_address_id_from_db(self, address, a_type, vendor):
111        """
112        Return address id from database based on address, type and vendor.
113        """
114        debug("Getting pk for address..")
115
116        a_id = self.cursor.execute("SELECT pk FROM address WHERE \
117                            address = ? AND type = ? AND fk_vendor = ?",
118                           (address, a_type, vendor)).fetchone()
119       
120        if a_id:
121            return a_id[0]
122       
123       
124    def get_address_id_for_address_from_db(self, address):
125        """
126        Return address id from database based on address only.
127        """
128        a_id = self.cursor.execute("SELECT pk FROM address WHERE \
129                                    address=?", (address, )).fetchone()
130       
131        if a_id:
132            return a_id[0]
133
134
135    def get_address_for_address_id_from_db(self, fk_address):
136        """
137        Return address from database based on address id.
138        """
139        addr = self.cursor.execute("SELECT address FROM address WHERE pk=?",
140                                   (fk_address, )).fetchone()
141
142        if addr:
143            return addr[0]
144       
145       
146    def get_address_pk_for_host_from_db(self, fk_host):
147        """
148        Return address pk from database based on host id.
149        """
150        self.cursor.execute("SELECT address.pk FROM address JOIN _host_address \
151                             ON (_host_address.fk_address=address.pk) WHERE \
152                             _host_address.fk_host=?", (fk_host, ))
153        addr = self.cursor.fetchone()
154       
155        if addr:
156            return addr[0]
157
158
159    def get_vendor_id_from_db(self, name):
160        """
161        Return vendor id from database based on name.
162        """
163        debug("Getting pk for vendor..")
164       
165        v_id = self.cursor.execute("SELECT pk FROM vendor WHERE \
166                        name = ?", (name, )).fetchone()
167       
168        if v_id:
169            return v_id[0]
170
171
172    def get_host_state_id_from_db(self, state):
173        """
174        Return state id from database based on state description.
175        """
176        debug("Getting pk for host_state..")
177       
178        h_id = self.cursor.execute("SELECT pk FROM host_state WHERE \
179                    state = ?", (state, )).fetchone()
180
181        if h_id:
182            return h_id[0]
183
184
185    def get_tcp_sequence_id_from_db(self, tcpseq_dict):
186        """
187        Return tcp_sequence id from database based on tcpsequence values.
188        """
189        debug("Getting pk for tcp_sequence..")
190       
191        t_id = self.cursor.execute("SELECT pk FROM tcp_sequence WHERE \
192                    tcp_values = ?", (tcpseq_dict["values"], )).fetchone()
193       
194        if t_id:
195            return t_id[0]
196
197
198    def get_tcp_ts_sequence_id_from_db(self, tcptsseq_dict):
199        """
200        Return tcp_sequence id from database based on tcptssequence
201        values,
202        """
203        debug("Getting pk for tcp_ts_sequence..")
204       
205        t_id = self.cursor.execute("SELECT pk FROM tcp_ts_sequence WHERE \
206                    tcp_ts_values = ?", (tcptsseq_dict["values"], )).fetchone()
207       
208        if t_id:
209            return t_id[0]
210
211
212    def get_ip_id_sequence_id_from_db(self, ipidseq_dict):
213        """
214        Return ip_id_sequence id from database based on ipidseq values.
215        """
216        debug("Getting pk for ip_id_sequence..")
217       
218        t_id = self.cursor.execute("SELECT pk FROM ip_id_sequence WHERE \
219                    ip_id_values = ?", (ipidseq_dict["values"], )).fetchone()
220       
221        if t_id:
222            return t_id[0]
223
224
225    def get_scan_type_id_from_db(self, name):
226        """
227        Return scan_type id from database based on name.
228        """
229        debug("Getting pk for scan_type..")
230       
231        s_id = self.cursor.execute("SELECT pk FROM scan_type \
232                                    WHERE name = ?", (name, )).fetchone()
233                                   
234        if s_id:
235            return s_id[0]
236
237
238    def get_port_state_id_from_db(self, state):
239        """
240        Return port_state id from database based on state..
241        """
242        debug("Getting pk for port_state..")
243       
244        p_id = self.cursor.execute("SELECT pk FROM port_state \
245                                WHERE state = ?", (state, )).fetchone()
246
247        if p_id:
248            return p_id[0]
249
250
251    def get_protocol_id_from_db(self, name):
252        """
253        Return protocol id from database based on name.
254        """
255        debug("Getting pk for protocol..")
256       
257        p_id = self.cursor.execute("SELECT pk FROM protocol \
258                                    WHERE name = ?", (name, )).fetchone()
259
260        if p_id:
261            return p_id[0]
262
263
264    def get_scanner_id_from_db(self, name, version):
265        """
266        Return scanner id from database based on scanner name and version
267        """
268        debug("Getting pk for scanner..")   
269       
270        s_id = self.cursor.execute("SELECT pk FROM scanner WHERE \
271                            name = ? AND  version = ?", (name, 
272                                                         version)).fetchone()
273       
274        if s_id:
275            return s_id[0]
276
277
278    def get_osgen_id_from_db(self, osgen):
279        """
280        Get id from osgen table for osgen.
281        """
282        debug("Getting pk for osgen..")
283       
284        o_id = self.cursor.execute("SELECT pk FROM osgen WHERE gen = ?", 
285                                   (osgen, )).fetchone()
286
287        if o_id:
288            return o_id[0]
289       
290
291    def get_osfamily_id_from_db(self, osfamily):
292        """
293        Get id from osfamily table for osfamily.
294        """
295        debug("Getting pk for osfamily..")
296       
297        o_id = self.cursor.execute("SELECT pk FROM osfamily \
298                                   WHERE family = ?", (osfamily, )).fetchone()
299
300        if o_id:
301            return o_id[0]
302
303
304    def get_osvendor_id_from_db(self, osvendor):
305        """
306        Get id from osvendor table for osvendor.
307        """
308        debug("Getting pk for osvendor..")
309       
310        o_id = self.cursor.execute("SELECT pk FROM osvendor \
311                                  WHERE vendor = ?",  (osvendor, )).fetchone()
312
313        if o_id:
314            return o_id[0]
315
316
317    def get_ostype_id_from_db(self, ostype):
318        """
319        Get id from ostype table for ostype.
320        """
321        debug("Getting pk for ostype..")
322       
323        o_id = self.cursor.execute("SELECT pk FROM ostype WHERE type = ?",
324                                   (ostype, )).fetchone()
325
326        if o_id:
327            return o_id[0]
328
329   
330    def get_inventory_id_from_db(self, inventory):
331        """
332        Get id from inventory for inventory.
333        """
334        debug("Getting pk for inventory..")
335
336        i_id = self.cursor.execute("SELECT pk FROM inventory WHERE name = ?",
337                                    (inventory, )).fetchone()
338
339        if i_id:
340            return i_id[0]
341
342
343class CompositeRetrieve(RawRetrieve):
344    """
345    Retrieve composite data from database.
346    """
347   
348    def __init__(self, conn, cursor):
349        """
350        Expects a conn and cursor from database connection.   
351        """
352        self.conn = conn
353        self.cursor = cursor
354       
355        RawRetrieve.__init__(self, self.conn, self.cursor)
356
357
358    def get_hosts_id_for_scan_from_db(self, fk_scan):
359        """
360        Get all hosts from database that are in fk_scan.
361        """
362        debug("Getting hosts from scan id %d.." % fk_scan)
363       
364        ids = self.cursor.execute("SELECT pk FROM host WHERE fk_scan = ?",
365                                  (fk_scan, )).fetchall()
366       
367        return ids
368   
369   
370    def get_addrtype_for_host_from_db(self, fk_host, addrtype):
371        """
372        Get IPV4, IPV6 or MAC for a host.
373        """
374        debug("Getting %s address for host id %d.." % (addrtype, fk_host))
375       
376        fk_address = self.cursor.execute("SELECT fk_address FROM _host_address \
377                                          WHERE fk_host = ?", 
378                                        (fk_host, )).fetchall()
379
380        address = None
381        for fk in fk_address:
382            address = self.cursor.execute("SELECT address FROM address WHERE \
383                                       type = ? AND pk = ?", 
384                                       (addrtype, fk[0], )).fetchone()
385            if address:
386                break
387           
388        if address:
389            return address[0]
390   
391       
392    def get_ipv4_for_host_from_db(self, fk_host):
393        """
394        Get IPv4 address for a host.
395        """
396        return self.get_addrtype_for_host_from_db(fk_host, "ipv4")
397   
398   
399    def get_ipv6_for_host_from_db(self, fk_host):
400        """
401        Get IPV6 address for a host.
402        """
403        return self.get_addrtype_for_host_from_db(fk_host, "ipv6")
404   
405   
406    def get_mac_for_host_from_db(self, fk_host):
407        """
408        Get MAC address associated with a host.
409        """
410        return self.get_addrtype_for_host_from_db(fk_host, "mac")
411       
412       
413    def get_hostnames_for_host_from_db(self, fk_host):
414        """
415        Get hostnames associated with a host.
416        """
417        debug("Getting hostnames for host id %d.." % fk_host)
418       
419        fk_hostname = self.cursor.execute("SELECT fk_hostname FROM \
420                                          _host_hostname WHERE fk_host = ?", 
421                                        (fk_host, )).fetchall()
422       
423        hostnames = [ ]
424        for fk in fk_hostname:
425            hostname = self.cursor.execute("SELECT name FROM hostname WHERE \
426                                       pk = ?", (fk[0], )).fetchone()
427            if hostname:
428                hostnames.append(hostname[0])
429           
430        return tuple(hostnames)
431       
432
433    def get_os_for_host_from_db(self, fk_host):
434        """
435        Get OS for a host from database.
436        """
437        os_name = self.cursor.execute("SELECT name FROM osmatch WHERE \
438                                       fk_host=?", (fk_host, )).fetchone()
439       
440        if os_name:
441            return os_name[0]
442
443
444    def get_osshort_for_host_from_db(self, fk_host):
445        """
446        Gets result from get_os_for_host_from_db and make it shorter.
447        """
448        os_name = self.get_os_for_host_from_db(fk_host)
449       
450        if os_name:
451            os_name = os_name.split()[0]
452
453        return os_name
454
455       
456    def get_finish_timestamp_for_scan_from_db(self, scan):
457        """
458        Get finish timestamp for a scan.
459        """
460        debug("Getting finish timestamp for scan id %d.." % scan)
461       
462        fts = self.cursor.execute("SELECT finish 'as finish [timestamp]' \
463                                   FROM scan WHERE pk = ?", 
464                                   (scan, )).fetchone()[0]
465       
466        return fts
467   
468   
469    def get_scan_details_for_scan_from_db(self, scan):
470        """
471        Get scan details for a scan.
472        """
473        debug("Getting scan details for scan id %d.." % scan)
474       
475        details = self.cursor.execute("SELECT args, xmloutputversion, verbose,\
476                   debugging, scanner.name, scanner.version FROM scan \
477                   JOIN scanner ON (scan.fk_scanner = scanner.pk) \
478                   WHERE scan.pk = ?", (scan, )).fetchone()
479       
480        return details
481
482   
483    """
484    (c1) Missing for port data retrieve: ostype in service_info.
485    Reason: NmapParser doesn't handle this yet.
486    """
487   
488    def get_portid_and_state_for_host_from_db(self, host):
489        """
490        Get only portid and port state from port table, for a host.
491        """
492        debug("Getting portid and state for host id %d from table \
493port.." % host)
494   
495        pst = self.cursor.execute("SELECT port.portid, port_state.state \
496                        FROM port \
497                        JOIN _host_port ON (_host_port.fk_port=port.pk) \
498                        JOIN port_state ON (port.fk_port_state=port_state.pk) \
499                        WHERE _host_port.fk_host=?", (host, )).fetchall()
500
501        return pst
502
503
504    def get_portid_and_fks_for_host_from_db(self, host):
505        """
506        Get portid and fks from port table, for a host.
507        """
508        debug("Getting portid and foreign keys for host id %d from \
509table port.." % host)
510       
511        pdata = self.cursor.execute("SELECT portid, fk_service_info, \
512                                    fk_protocol, fk_port_state FROM port \
513                                    JOIN _host_port ON \
514                                    (_host_port.fk_port=port.pk) \
515                                    WHERE _host_port.fk_host=?",
516                                    (host, )).fetchall()
517       
518        return pdata
519   
520   
521    def get_port_data_for_pdata_from_db(self, protocol_id, port_state_id,
522                                        service_info_id):
523        """
524        Get port data based on data returned from
525        get_portid_and_fks_for_host_from_db
526        """
527        debug("Getting port data for pdata..")
528       
529        fullpdata = self.cursor.execute("SELECT protocol.name as protocol, \
530                        port_state.state, service_info.product, \
531                        service_info.version, service_info.extrainfo, \
532                        service_info.method, service_info.conf, \
533                        service_name.name FROM protocol, port_state, \
534                        service_info, service_name WHERE protocol.pk = ? AND \
535                        port_state.pk = ? AND service_info.pk = ? AND \
536                        service_name.pk = service_info.fk_service_name",
537                        (protocol_id, port_state_id, 
538                         service_info_id)).fetchall()[0]
539       
540        return fullpdata
541   
542   
543    """
544    End (c1)
545    """
546   
547    def get_extraports_count_for_host_from_db(self, host_id):
548        """
549        Get extraports Count for host id.
550        """
551        debug("Getting extraports count for host id %d" % host_id)
552
553        epcount = self.cursor.execute("SELECT extraports.count \
554                            FROM extraports WHERE extraports.fk_host = ?",
555                            (host_id, )).fetchall()
556
557        return epcount
558                           
559
560    def get_extraports_data_for_host_from_db(self, host_id):
561        """
562        Get extraport data for host id (returns Count and State).
563        """
564        debug("Getting extraports data for host id %d" % host_id)
565       
566        epdata = self.cursor.execute("SELECT extraports.count, \
567                            port_state.state FROM extraports, port_state \
568                            WHERE extraports.fk_host = ? AND \
569                            port_state.pk = extraports.fk_port_state",
570                            (host_id, )).fetchall()
571
572        return epdata
573
574   
575    def get_fingerprint_info_for_host_from_db(self, host_id):
576        """
577        Get fingerprinto info for a host id.
578        """
579        debug("Getting fingerprinto info for host id %d" % host_id)
580       
581        # W: Not using signature field for now.
582
583        fpinfo = self.cursor.execute("SELECT uptime, lastboot, \
584                 tcp_sequence_class, tcp_sequence_index, tcp_sequence_value, \
585                 tcp_sequence_difficulty, tcp_ts_sequence_class, \
586                 tcp_ts_sequence_value, ip_id_sequence_class, \
587                 ip_id_sequence_value FROM fingerprint_info \
588                 WHERE fk_host = ?", (host_id, )).fetchone()
589       
590        return fpinfo
591   
592
593    def get_osmatch_for_host_from_db(self, host_id):
594        """
595        Get osmatch data for a host id.
596        """
597        debug("Getting osmatch for host id %d" % host_id)
598       
599        match = self.cursor.execute("SELECT name, accuracy, line FROM osmatch \
600                WHERE fk_host = ?", (host_id, )).fetchone()
601       
602        return match
603   
604    def get_osclasses_for_host_from_db(self, host_id):
605        """
606        Get osclasses for a host id.
607        """
608        debug("Getting osclasses for host id %d" % host_id)
609       
610        classes = self.cursor.execute("SELECT osclass.accuracy, osgen.gen, \
611                  osfamily.family, osvendor.vendor, ostype.type FROM osclass \
612                  JOIN osgen ON (osclass.fk_osgen = osgen.pk) \
613                  JOIN osfamily ON (osclass.fk_osfamily = osfamily.pk) \
614                  JOIN osvendor ON (osclass.fk_osvendor = osvendor.pk) \
615                  JOIN ostype ON (osclass.fk_ostype = ostype.pk) \
616                  WHERE osclass.fk_host = ?", (host_id, )).fetchall()
617       
618        return classes
619   
620
621class InventoryRetrieve(ConnectDB, CompositeRetrieve):
622    """
623    Retrieves inventory data from database.
624    """
625
626    def __init__(self, db):
627        """
628        Expects an umit database.
629        """
630        ConnectDB.__init__(self, db)
631        CompositeRetrieve.__init__(self, self.conn, self.cursor)
632
633
634    def get_inventories_ids(self):
635        """
636        Returns all inventories ids from database.
637        """
638        debug("Getting all inventories ids..")
639       
640        ids = self.cursor.execute("SELECT pk FROM inventory").fetchall()
641       
642        return ids
643   
644
645    def get_inventories_names(self):
646        """
647        Returns all inventories names from database.
648        """
649        debug("Getting all inventories name..")
650
651        names = self.cursor.execute("SELECT name FROM inventory").fetchall()
652
653        return names
654
655   
656    def get_inventories_ids_names(self):
657        """
658        Returns all inventories ids and names from database.
659        """
660        debug("Getting all inventories ids and names..")
661
662        id_names = self.cursor.execute("SELECT pk, name FROM \
663                            inventory").fetchall()
664
665        return id_names
666   
667
668    def get_inventory_name_for_id(self, inv_id):
669        """
670        Returns inventory name for id.
671        """
672        debug("Getting inventory name for id %d.." % inv_id)
673       
674        name = self.cursor.execute("SELECT name FROM inventory WHERE pk = ?", 
675                                    (inv_id, )).fetchone()
676
677        if name:
678            return name[0]
679
680
681    def get_inventory_id_for_name(self, name):
682        """
683        Returns inventory id for name.
684        """
685        debug("Getting inventory id for name %s.." % name)
686
687        i_id = self.cursor.execute("SELECT pk FROM inventory WHERE name = ?",
688                                    (name, )).fetchone()
689
690        if i_id:
691            return i_id[0]
692
693
694    def get_scan_args_for_inventory_id(self, fk_inventory):
695        """
696        Return scan arguments for an inventory id.
697        """
698        debug("Getting scan arguments for inventory id %d.." % fk_inventory)
699
700        args = self.cursor.execute("SELECT args FROM scan JOIN \
701                    _inventory_scan ON (scan.pk = _inventory_scan.fk_scan) \
702                    WHERE _inventory_scan.fk_inventory=? LIMIT 1", 
703                    (fk_inventory, )).fetchone()
704
705        if args:
706            return args[0]
707
708
709    def get_scans_id_for_inventory(self, fk_inventory):
710        """
711        Returns all pks from table scan, where scan is in an inventory.
712        """
713        debug("Getting scans for inventory id %d.." % fk_inventory)
714       
715        ids = self.cursor.execute("SELECT fk_scan FROM _inventory_scan \
716                                   WHERE fk_inventory = ?", 
717                                  (fk_inventory, )).fetchall()
718       
719        return ids
720
721
722    def get_hosts_id_for_scan_from_db(self, fk_scan):
723        """
724        Get all pks from table host, where host is in fk_scan and host in an
725        inventory.
726        """
727        debug("Getting hosts from scan id %d.." % fk_scan)
728       
729        ids = self.cursor.execute("SELECT pk FROM host WHERE fk_scan = ? \
730                                   AND fk_scan IN (SELECT fk_scan FROM \
731                                   _inventory_scan)",
732                                  (fk_scan, )).fetchall()
733       
734        return ids
735   
736
737    def get_hosts_base_data_for_inventory_from_db(self, host_address, 
738                                                  fk_inventory):
739        """
740        Get all pks and fk_scans from table host, where: host has an especified
741        host_address and host is in an especified fk_inventory.
742        """
743        debug("Getting pks in host with host_address %s and fk_inventory %d" % \
744              (host_address, fk_inventory))
745       
746        ids = self.cursor.execute("SELECT host.fk_scan, host.pk FROM host \
747                                   JOIN _host_address ON \
748                                   (host.pk = _host_address.fk_host) WHERE \
749                                   _host_address.fk_address = (SELECT \
750                                   address.pk FROM address WHERE address = ?) \
751                                   AND host.fk_scan IN (SELECT fk_scan FROM \
752                                   _inventory_scan WHERE fk_inventory = ?) \
753                                   ORDER BY host.pk DESC", 
754                                 (host_address, fk_inventory)).fetchall()
755       
756        return ids
757   
758   
759    def get_finish_data_for_inventory_from_db(self, fk_inventory):
760        """
761        Get all finish timestamps and scan id from scans that are in an
762        inventory.
763        """
764        debug("Getting finish timestamps for fk_inventory %d" % fk_inventory)
765       
766        data = self.cursor.execute("SELECT scan.pk, \
767                scan.finish as 'finish [timestamp]' FROM scan \
768                JOIN _inventory_scan ON (_inventory_scan.fk_scan = scan.pk) \
769                WHERE _inventory_scan.fk_inventory = ? ORDER BY scan.pk DESC", 
770                (fk_inventory, )).fetchall()
771       
772        return data
773   
774   
775    def get_inventory_change_category_id(self, name):
776        """
777        Get inventory_change_category pk based on category name.
778        """
779        debug("Getting change_category id for name '%s'" % name)
780       
781        pk = self.cursor.execute("SELECT pk FROM inventory_change_category \
782                                  WHERE name=?", (name, )).fetchone()
783       
784        if pk:
785            return pk[0]
786       
787       
788    def get_inventory_comparison(self, old_hid, new_hid, date, fk_inventory):
789        """
790        Returns entry in _inventory_changes if there is a comparison stored
791        for old_hid against new_hid in a date for fk_inventory.
792        """
793        debug("Checking if there is a comparison for old hostid %d against \
794new hostid %d @ %s for Inventory id %d" % (old_hid, new_hid, date, 
795                                           fk_inventory))
796       
797        pk = self.cursor.execute("SELECT pk FROM _inventory_changes WHERE \
798                                  old_hostid=? AND new_hostid=? AND \
799                                  entry_date=? AND \
800                                  fk_inventory=?", (old_hid, new_hid, date,
801                                                    fk_inventory)).fetchall()
802       
803        if pk:
804            return True
805       
806    def get_inventory_changes(self, fk_inventory, fk_address):
807        """
808        Returns changes in _inventory_changes for fk_inventory and
809        fk_address
810        """
811        debug("Getting changes for Inventory id %d and \
812Address id %d" % (fk_inventory, fk_address))
813       
814       
815        changes = self.cursor.execute("SELECT old_hostid, new_hostid, \
816                        entry_date, short_description, \
817                        inventory_change_category.name FROM _inventory_changes\
818                        JOIN inventory_change_category ON \
819                        (_inventory_changes.fk_category=\
820                        inventory_change_category.pk) WHERE fk_inventory=? \
821                        AND fk_address=? ORDER BY entry_date DESC",
822                        (fk_inventory, fk_address)).fetchall()
823       
824        return changes
825       
826    def get_inventory_changes_for_category(self, fk_inventory, fk_address,
827                                           fk_category):
828        """
829        Returns changes in _inventory_changes for fk_inventory, fk_address and
830        a especific fk_category.
831        """
832        debug("Getting changes for Inventory id %d, Address id %d and \
833Category id %d" % (fk_inventory, fk_address, fk_category))
834
835        changes = self.cursor.execute("SELECT old_hostid, new_hostid, \
836                        entry_date, short_description FROM _inventory_changes\
837                        WHERE fk_category=? and fk_inventory=? AND \
838                        fk_address=? ORDER BY entry_date DESC",
839                        (fk_category, fk_inventory, fk_address)).fetchall()
840
841        return changes
842   
843   
844    def get_inventory_changes_for_category_in_range(self, fk_inventory, 
845                                                    fk_address, fk_category,
846                                                    start, end):
847        """
848        Returns changes in _inventory_changes for fk_inventory, fk_address and
849        a especific fk_category in a time range.
850        """
851        debug("Getting changes for Inventory id %d, Address id %d and \
852Category id %d from %s to %s" % (fk_inventory, fk_address, fk_category, start,
853                                 end))
854
855        changes = self.cursor.execute("SELECT old_hostid, new_hostid, \
856                        entry_date, short_description FROM _inventory_changes\
857                        WHERE entry_date >= ? AND entry_date < ? AND \
858                        fk_category=? AND fk_inventory=? AND \
859                        fk_address=? ORDER BY entry_date DESC",
860                        (start, end, fk_category, fk_inventory, 
861                         fk_address)).fetchall()
862
863        return changes
864   
Note: See TracBrowser for help on using the browser.