Database Properties
Database Type: PostgreSQL - 13.2
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| acknowledgeddevice | 0 | 1 | 5 | 0 | Table | Not used |
| attribute | 2 | 2 | 4 | 0 | Table | This table contains a list of attributes discovered in a zone. It is parent table that is partitioned over zone schema. Example: Attributes discovered in zone id 2 will reside in zone_0002.attribute. This table contains all the metadata that is collected for a device. For example, snmp attributes, ASName, dnsname, cifsName. This table is a normalized table hence, same value of attribute can be associated with multiple devices within a zone. This mapping is stored in device_attribute table. |
| attribute_cidr | 2 | 4 | 8 | 0 | Table | This table contains all the CIDRs for each collector that are either system defined or user configured (custom attributes). It is parent table that is partitioned over zone schema |
| attributeconfig | 0 | 0 | 5 | 0 | View | This view contains a list of user-defined (custom) attributes defined for all zones. |
| attributes | 0 | 0 | 8 | 0 | View | This view contains a list of system-defined attributes defined for all zones. |
| certificate | 1 | 3 | 14 | 0 | Table | This table contains all the certificates for a zone that have been discovered. It is parent table that is partitioned over zone schema. |
| certificatepath | 3 | 1 | 4 | 0 | Table | This table contains all the certificates for a zone that have been discovered. It is parent table that is partitioned over zone schema. |
| certificatepath_pattern | 0 | 2 | 3 | 0 | Table | This table contains mapping for certificatepath to pattern. It is parent table that is partitioned over zone schema. |
| cloudalias | 1 | 2 | 6 | 0 | Table | This table contains unique cloud credentials that are configured for a zone. It is the parent table that is partitioned over zone schema. |
| config | 0 | 2 | 5 | 2 | Table | This table contains configuration information for all collectors. It is parent table that is partitioned over zone schema. |
| device | 17 | 4 | 15 | 0 | Table | This table contains a list of devices discovered in a zone. It is parent table that is partitioned over zone schema. Example: Devices discovered in zone id 2 will reside in zone_0002.device table. |
| device_attribute | 0 | 4 | 7 | 0 | Table | This table contains a list of discovered attributes for a device. It is parent table that is partitioned over zone schema. Example: Attributes discovered for all devices for zone id 2 will reside in zone_0002.device_attribute. This table resolves many to many relationship between zone.device and zone.attribute. |
| device_certificate | 0 | 2 | 4 | 0 | Table | This table contains a mapping of all certificates discovered for all devices in a zone. It is parent table that is partitioned over zone schema. Example: Certificates discovered for devices for zone id 2 will reside in zone_0002.device_certificate. This table resolves many to many relationship between zone.device and zone.certificate. |
| device_cloudalias | 0 | 3 | 4 | 0 | Table | This table contains reference information for all the cloud aliases a device has responded to. It is parent table that is partitioned over zone schema. A device can have as many records as received unique responses for each profiledata type in this table. One device can have zero to many records. |
| device_match | 0 | 4 | 6 | 0 | Table | This table contains final profiling results for a device. It is parent table that is partitioned over zone schema. Example: Device profiles for all devices for zone id 2 will reside in zone_0002.device_match. This is the table that gets used to display profile information for a device. It contains a record for each of the profile type (Device Type, OS, Model, Version, Vendor) that contains a match. So, one device can have at most 5 records in this table if there is a match for all 5 profile types. Entries to this table will get added/updated/deleted as part of any profile response processing as well as when a new pattern file is imported into database. |
| device_pattern | 0 | 2 | 8 | 0 | Table | This table contains mapping of device to all the patterns that it matched. It contains a record for every response that matched any pattern in the database. It is parent table that is partitioned over zone schema. |
| device_ports | 0 | 2 | 6 | 0 | Table | This table contains list of all open and closed ports responses that collector interface received for a device. It holds one record for each device that responded to port discovery. It is parent table that is partitioned over zone schema. Once a port is found open or closed, it will only be removed from the list if we get a different response back from that port. For example, At time t1, device A received a response with open ports 22 and 23 and at time t2 received another response with open port 43, after time t2, it will contain all ports 22,23,43 for device A. This is due to the fact that this table contains responses aggregated at zone level. If collector one sees port 22 open and collector 2 sees port 43 open, both ports should be reported as open for device A, hence record contains all three ports. open_times for each port records time at which given port was reported as open. closed and closed times follow similar pattern and contains a list of closed ports reported at all times and time a port was reported as closed respectively. |
| device_profiledata | 0 | 3 | 5 | 0 | Table | This table contains reference information for all the profiledata/banner a device has responded along with profiledata type (snmp sysDesc, sysObjId, CIFS, http/certificates, tcp/p0f). It is parent table that is partitioned over zone schema. A device can have as many records as received Unique responses for each profiledata type in this table. |
| device_response | 0 | 5 | 8 | 0 | Table | This table contains information for all the responses a device has responded along with protocol and scantype it responded to. It is parent table that is partitioned over zone schema. A device can have as many records as received Unique responses for each profiledata type in this table. One device can have zero to many responses. If a device is discovered through multiple scan types and protocols, it will have multiple entries in device_response table. Likewise, if a device is discovered as layer 2 host or as an ARP table entries or interface host or addresses, it might not have any device_response associated with it. |
| device_snmpalias | 0 | 3 | 4 | 0 | Table | This table contains reference information for all the snmp aliases a device has responded to. It is parent table that is partitioned over zone schema. A device can have as many records as received Unique responses for each profiledata type in this table. One device can have zero to many records. |
| device_values | 0 | 2 | 23 | 0 | Table | This table contains additional information for devices that has been derived as a result of various responses. It is parent table that is partitioned over zone schema. Values for all the columns in this table can be calculated from other tables in the schema. Purpose of this table is to provide faster fetch results for reports as well as target queries. One device record will always have 1 corresponding record in this table. |
| device_vendor | 0 | 0 | 7 | 0 | View | View to provide vendor information for a device in one place |
| device_wmialias | 0 | 3 | 4 | 0 | Table | This table contains reference information for all the WMI aliases a device has responded to. It is parent table that is partitioned over zone schema. A device can have as many records as received unique responses for each profiledata type in this table. One device can have zero to many records. |
| edge | 0 | 1 | 7 | 0 | Table | This table is not being used. |
| firemon_risk_range | 0 | 0 | 3 | 3 | Table | |
| forwarders_range | 0 | 0 | 2 | 7 | Table | This is a static table that is used for reports Forwarders by Degree and Forwarders by Host. It contains start and end values for a range ( 1-2 or 6-10 or 11-20) to place a forwarder into a bucket based on their l3peers or l3hosts. For example, if a forwarder has 7 l3Peers connected to it, it would be placed in the range of 6-10 for Forwarders by Degree report. This static table contains all possible ranges a forwarder should be placed for those two reports. |
| iftable | 4 | 1 | 4 | 0 | Table | This table contains a list of all interface tables discovered in a zone. It also contains an entry for device level routes. It is parent table that is partitioned over zone schema. For example, interface tables discovered for zone id 2 will reside in zone_0002.iftable |
| iftable_vlan | 0 | 2 | 4 | 0 | Table | This table contains mapping of iftable to vlan information. It is parent table that is partitioned over zone schema |
| interface | 3 | 2 | 20 | 0 | Table | This table contains a list of all interfaces discovered in a zone. It is parent table that is partitioned over zone schema. Example: Interface records discovered for zone id 2 will reside in zone_0002.interface. Please note: for interface entry that is recorded as part of device level route processing gets an Interface Index of 0 and an interface description of "unassociated routes interface". |
| interface_address | 0 | 2 | 4 | 0 | Table | This table contains a list of all interface addresses discovered in a zone. It is parent table that is partitioned over zone schema. Example: Interface address records discovered for zone id 2 will reside in zone_0002.interface_address. Each entry for device->interface->addresses gets normalized into a record in this table. Same results could have been achieved by having this data point as a column in interface table so don't quite understand purpose of this table. An entry is inserted in this table each time a new interface table data is processed (as part of snmp response processing). Entry gets updated when interface response contains updated address. |
| interface_host | 0 | 3 | 4 | 0 | Table | This table contains a list of all interface hosts discovered in a zone. It is parent table that is partitioned over zone schema. For example, interface hosts discovered for zone id 2 will reside in zone_0002.interface_host. device->interface->switchInfo->hosts contains a list of MAC address along with its vlan. Each of these individual entries in the list gets normalized into a record in this table. If MAC address exists in device table, it uses that. If not, a new entry is created in device for this MAC with last update set to layer2-hosts. Entry gets updated when interface switchInfo hosts contains updated response. |
| interface_route | 0 | 5 | 13 | 0 | Table | This table contains route information for interfaces. It is parent table that is partitioned over zone schema. |
| link | 0 | 4 | 6 | 0 | Table | This table contains Link information for both layer2 and layer3. Layer3 links are discovered through pathDiscovery (traces) as well as OSPF discovery. Layer2 links are discovered through SNMP discovery. It is parent table that is partitioned over zone schema. |
| links | 0 | 0 | 9 | 0 | View | |
| linkset | 1 | 1 | 4 | 0 | Table | This table contains metadata for a link, whether a given link is host link or node link and/or whether a given link is layer2 or layer3. It is parent table that is partitioned over zone schema |
| listsize_by_zone_view | 0 | 0 | 3 | 0 | View | View that gives size of discovery list by zone and attribute type (known, target, trusted, avoid, stop). This view is created so that we can use it as external table from x15 side as there is an issue with pushing power function from x15 to Postgres side. |
| macvendor_pattern | 0 | 2 | 2 | 3940 | Table | This table contains mapping of a macvendor id to existing pattern id. This table gets populated as part of macvendor pattern upload. |
| map | 0 | 1 | 9 | 0 | Table | |
| mapnode | 0 | 1 | 14 | 0 | Table | This table contains layout and other custom information for all nodes of a saved map. It is parent table that is partitioned over zone schema. This table only gets populated if a Map is saved. This table is solely created to support Map display functionality and is not used for any device related processing. |
| nackcandidate | 0 | 1 | 6 | 0 | Table | This table will hold any device that has ever received a response, for non-host / non-path ScanTypes. Any time a response is received for non-host/non-path scantypes for a device, an entry will get inserted in this table for responding ip, collector_id, scantype_id, zone_id Any time a NACK response is received for a device, an entry will get deleted from this table that matches ip, collector_id, scantype_id and zone_id of incoming NACK response. All the matching entries for a collector_id will be deleted when a collector gets archived Table will be truncated when either a zone gets deleted, or the NACK configuration parameter gets turned off. |
| path | 0 | 2 | 8 | 0 | Table | Not being used. |
| portgroup | 0 | 0 | 2 | 2 | Table | This table contains static group with list of ports that are used in various port/profile discovery types. This table gets loaded on initial database load. |
| profile_translation | 0 | 4 | 7 | 0 | Table | It contains |
| profiledata | 2 | 2 | 7 | 0 | Table | This table contains a Unique copy of the profile data in a zone. That is to say that if we had 20 devices with the same banner or sysDescr we would have one copy here and it would be referenced from zone.device_profiledata. p0f, cifs, snmp and httpbanners responses go in this table. It is parent table that is partitioned over zone schema. |
| profiledata_pattern | 0 | 2 | 3 | 0 | Table | This table contains mapping of profile data to patterns. It is parent table that is partitioned over zone schema. |
| profiledatatype | 4 | 0 | 2 | 0 | Table | This is a static table that contains various profiledata types that gets saved into zone.profiledata table. |
| profiles | 0 | 0 | 11 | 0 | View | |
| protocol | 2 | 0 | 2 | 0 | Table | This table contains a list of protocols that all the devices across all the zones have responded so far. This table will not contain any records out of the box, but will insert protocols as and when device responses get processed. |
| protocol_numbers | 0 | 0 | 3 | 256 | Table | |
| response_decision | 0 | 2 | 12 | 0 | Table | This table contains the snmpDetails response decisions. Whether the response is from a new or existing device. It is parent table that is partitioned over zone schema |
| result | 0 | 0 | 2 | 0 | Table | Not being used. |
| route | 3 | 1 | 8 | 0 | Table | This table contains unique routes that have been discovered for a zone. It also stores metadata for routes indicating whether they are targeted, known, internal or eligible. It is parent table that is partitioned over zone schema. |
| routeprotocol | 2 | 0 | 2 | 0 | Table | This table contains unique protocols that are received as part of interface route processing. Valid values are local, other, netmgmt, ospf |
| routetype | 2 | 0 | 2 | 0 | Table | This table contains unique route types that are received as part of interface route processing. Valid values are local, remote, reject |
| scantype | 6 | 0 | 2 | 19 | Table | This is a static table that contains Unique scan/discovery types for Spectre systems. Valid values are pathDiscovery, hostDiscovery, inboundLeak, outboundLeak, epdLeak, snmpDetails, snmpDiscovery, cifs, tcpPorts, tcpProfile, broadcastDiscovery, httpDetails, ospfDiscovery, bgpDiscovery, dns, external. This table gets loaded as part of initial database load |
| scantype_alias | 0 | 0 | 3 | 19 | Table | This is a static table that contains mapping of internal scan/discovery types (Records that are in zone.scantype) into more generic discovery types. It is used for Discovery Statistics By Discovery Types report. Mapping from internal scan types to alias is as follows: hostDiscovery | Host snmpDetails | SNMP snmpDiscovery | SNMP snmpDetails | SNMP bgpDiscovery | BGP broadcastDiscovery | Broadcast dns | DNS cifs | CIFS epdLeak | EPD Leak httpDetails | HTTP inboundLeak | Inbound outboundLeak | Outbound ospfDiscovery | OSPF pathDiscovery | Path tcpPorts | Port tcpProfile | Profile |
| snmpalias | 1 | 2 | 6 | 0 | Table | This table contains Unique custom snmp credentials that are configured for a zone. It also stores alias for common credentials that have responses associated with them. It is parent table that is partitioned over zone schema. |
| snmpaliasgroup | 0 | 0 | 2 | 1 | Table | Contains static group with list of credentials that are used in various snmp discovery types. This table gets loaded on initial database load. |
| target | 1 | 6 | 13 | 0 | Table | This table contains the most up to date information on what all the scouts/collectors should be targetting. Any time a new device is discovered, queries are run to determine if this device needs to be targeted for a given collector (based on target, eligible, stop and avoid list) for a given scantype (based on collector configuration). Apart from calculating in real time, query is also run every 24 hours that goes through all devices for a zone and inserts/updates/deletes records in this table to make it up to date with curent discovery and configuration data. It is parent table that is partitioned over zone schema. Entries from this table are returned when collectors request their next set of targets |
| target_highpriority | 0 | 7 | 14 | 0 | Table | |
| target_hist | 0 | 0 | 14 | 0 | Table | Not being used. |
| target_shadow | 0 | 0 | 12 | 0 | Table | This table is used to run update target in verification mode and holds all the inserts and updates that would have been made to target table as part of nightly update target run. It is parent table that is partitioned over zone schema. |
| targetm | 0 | 0 | 9 | 0 | Table | This table has identical table structure/columns as zone.target. It is an intermediate table that gets used as a staging table before making final updates to zone.target table. This table gets populated each time update target is run and gets truncated after updates are made to target table. It is parent table that is partitioned over zone schema. |
| updates | 0 | 0 | 6 | 0 | Table | |
| updatetargetspace_log | 0 | 0 | 7 | 0 | Table | This table keeps a log for all update target runs and number of devices that got inserted/updated/deleted with that run. It is parent table that is partitioned over zone schema. |
| wmialias | 1 | 2 | 6 | 0 | Table | This table contains unique WMI credentials that are configured for a zone. It is the parent table that is partitioned over zone schema. |