IVR Schema

The tables in this section provide detailed information about IVR schema.

Table 1. IVR.schemaThe global_computer_values tables provides the information on the schema version.
Name Type Description
version nvarchar(10) This is the version of the schema.
Table 2. IVR.findingsThe IVR.findings table stores the number of findings based on a device and vulnerability mapping.
Name Type Description
port int Part of unique key for a finding.
protocol nvarchar(32) Part of unique key for a finding.
global_computer_id (FK) bigint The unique computer ID assigned by Insights to a machine. References: dbo.datasource_devices
vulnerability_id (FK) nvarchar(50) ID of the vulnerability as assigned by IVR (This will be defined as: QID, Plugin_Id or CVE). In the case of a CVE being used as a Vulnerability_ID, the ParentID should be a QID or PluginId. References: IVR.vulnearbilities
source_hash (PK) nvarchar(8) Hash of the adapter configuration, which allows for purging of records based upon configuration change.
custom_severity int Nullable
device_correlation_score int Calculated Coorelation Confidence score (This is represented as a weighted score, the higher the score the higher the confidence is around the coorelation of the associated computer). Value calculated based on cvss_base*applicable_computers as 'weighted score'.
first_detected datetime Time stamp for first detection.
last_detected datetime Time stamp for last detection.
last_fixed datetime Time stamp when the vulnerability was last confirmed as not vulnerable.
results nvarchar(1024) This is provided by the IVR datasource and may contain a brief description as to why the vulnerability has been identified.
sequence rowID/timestamp Generate System generated unique number to identified delta changes.
Table 3. IVR.VulnerabilitiesThe IVR.vulnerabilities table stores vulnerability details.
Name Type Description
vulnerability_id nvarchar(50) ID of the vulnerability. Possible ID references include: Tenable Plugin ID, Qualys ID (QID), or CVE ID. In the case that a vulnerability_id is a CVE, the ParentID field should be a Tenable Plugin ID or QID that maps to the vulnerability_id. References: IVR.vulnerability_fixlet_nexus
parent_id nvarchar(50) Parent_ID facilitates the hierarchy between QID/PluginID and CVEs. It will be a QID or PluginID, when a CVE is represented by the Vulnerability ID.
source_hash nvarchar(8) Hash of the adapter's configuration, which allows for purging of records based upon configuration change.
cve nvarchar(50) CVE ID of a given vulnerability. (Tenable: note plugin id's are not singularly represented by a CVE, there is an individual record for CVEs as well.) Qualys: provides vulnerabilty ID.
title nvarchar(150) Vulnerability title as defined by Tenable or Qualys.
type nvarchar(50) Vulnerability Type. Possible values for Qualys include: "potential vulnerability", or "information". Tenable: no data represented.
vendor_rating nvarchar(50) Represents the external rating of the vulnerability to facilitate prioritization. If the data source is Tenable, this field will refer to the Tenable Vulnerability Priority Rating (VPR). If the data source is Qualys, this field will refer to the Qualys Severity rating.
severity nvarchar(50) The Fixlet severity. Possible Qualys values: 1- minimal, 2 - medium, 3 - serious, 4 - critical, 5 - urgent. For reference, please see: https://qualysguard.qg2.apps.qualys.com/qwebhelp/fo_portal/knowledgebase/severity_levels.htm

Tenable values: 0 - information, 1-4 Low, 5-6 Medium, 7-9 High, 10 - Critical

cvss_base float(50) Common Vulnerability Scoring System (CVSS) base score calculated according to the formula defined here: https://nvd.nist.gov/vuln-metrics/cvss/v2-calculator. Note that this field is based on CVSS v2.
cvss_temporal float(50) Common Vulnerability Scoring System (CVSS) temporal score metric calculated according to the formula defined here: https://nvd.nist.gov/vuln-metrics/cvss/v2-calculator. Note that this field is based on CVSS v2.
cvss_severity nvarchar(50) Severity ratings for CVSS v2 base score ranges. Possible values include "low", "medium", "high". Please refer to the following link for more information: https://nvd.nist.gov/vuln-metrics/cvss/v2-calculator. Note that this field is based on CVSS v2.
cvss3_base float(50) Common Vulnerability Scoring System (CVSS) base score calculated according to the formula defined here: https://nvd.nist.gov/vuln-metrics/cvss. Qualys: no data represented.
cvss3_temporal float(50) Common Vulnerability Scoring System (CVSS) temporal score value calculated according to the formula defined here: https://nvd.nist.gov/vuln-metrics/cvss. Note that this field is based on CVSS v3.
cvss3_severity nvarchar(50) Severity ratings for CVSS v3 base score ranges. Possible values include "none", "low", "medium", "high", "critical". Please refer to the following link for more information: https://nvd.nist.gov/vuln-metrics/cvss/v3-calculator, https://nvd.nist.gov/vuln-metrics/cvss. Note that this field is based on CVSS v3.
impact nvarchar(MAX) Impact includes a description of the possible consequences that may occur if the vulnerability is successfully exploited. Note, this value is truncated to 150 characters in the Insights database.
solution nvarchar(MAX) The Solution section provides a suggested solution to fix the vulnerability or a reference defering to the vendor.
exploitability nvarchar(MAX) Exploitability information correlates with detected vulnerabilities when known exploits are pulished.
category varchar(MAX) The value of the category.Every vulnerability is mapped to one vulnerability category, typically this represents the type of software or operating system that is affected.
description nvarchar(MAX) Summarized description of the vulnerability as defined by Tenable or Qualys.
date_published datetime The date when the vulnerability is published.
last_updated datetime Date record was last updated.
sequence timestamp Internal field. Database-generated row identifier. Changes to the row results in a default change to this value.
Table 4. IVR.vulnerability_fixlet_nexusThe IVR.vulnerability_fixlet_nexus table stores vulnerability fixlet mapping with weighted score.
Name Type Description
vulnerability_id nvarchar(50) ID of the vulnerability as assigned by UVM (This will be defined as: QID, Plugin_Id or CVE). In the case of a CVE being used as a Vulnerability_ID, the ParentID should be a QID or PluginId.
global_fixlet_id bigint The fixlet id coorelates to the insights datasource fixlet id. References: dbo.datasource_fixlets, dbo.fixlet_dimensions
source_hash nvarchar(8) This is the sourcehash of the adapter to confirm ingestion parameters.
parent_id nvarchar(50) Parent_ID facilitates the hierarchy between QID/PluginID and CVEs. It will be a QID or PluginID, when a CVE is represented by the Vulnerability ID.
weighted_score bigint Value calculated based on cvss_base*applicable_computers as 'weighted score'.
Table 5. IVR.analysis_calendarThe IVR.analysis_calendar table provides the infmation about the dates and time details.
Name Type Description
DateKey int Join key for updates (YYYYMMDD).
Date date Date (YYYY-MM-DD).
Day int IDay number.
DaySuffix char(2) Suffix for day value.
Weekday int Numeric day of week (1-Monday).
WeekDayName varchar(10) Day of week text.
IsWeekend bit Flag for weekend date. Valid value 1: 0, valid value 2:1.
IsHoliday bit Flag for holiday. Valid value 1: 0, valid value 2:1.
HolidayText varchar(64) Nullable.
DOWInMonth tinyint Day of the week in month - numeric value.
DayOfYear smallint Day number in year.
WeekOfMonth tinyint Week in month - numeric value.
WeekOfYear tinyint Week of year - numeric value.
ISOWeekOfYear tinyint Number of year - numeric value (ISO definition).
Month tinyint Month number.
MonthName varchar(10) Month name.
Quarter tinyint Quarter number.
QuarterName varchar(6) Quarter name.
Year int Year - numeric value.
MMYYYY char(6) Month and year (MMYYYY)
MonthYear char(8) Month and year (mm YYYY).
FirstDayOfMonth date Date value for first day of month (YYYY-MM-DD).
LastDayOfMonth date Date value for last day of month (YYYY-MM-DD).
FirstDayOfQuarter date Date value for first day of quarter (YYYY-MM-DD).
LastDayOfQuarter date Date value for last day of quarter (YYYY-MM-DD).
FirstDayOfYear date Date value for first day of year (YYYY-MM-DD).
LastDayOfYear date Date value for last day of year (YYYY-MM-DD).
FirstDayOfNextMonth date Date value for first day of next month (YYYY-MM-DD).
FirstDayOfNextYear date Date value for first day of next year (YYYY-MM-DD).
MonthNameShort char(3) Three-character month name (e.g. Jan).
YearMonth varchar(10) Year and month (YYYY/MM)
Figure 1. Vulnerability Remediation Schema