/*--------------------------------------------------------------------------------
  
  <Copyright file="CreateBaseTables.sql" company="Microsoft">
    Copyright (c) Microsoft Corporation.  All rights reserved.
  </Copyright>

  <Comments>
    
    The following is the new implementation of the Application
    Compatability Toolkit Client Database. This is the creation
    script that is used to drop all existing database objects and
    create a fresh database from start. All individual
    objects are documented in-situ and the script segmented into
    
    -   OS Related Tables 
    -   Type definitions
    -   Common tables
    -   Machine Related Tables
    -   Device Related Tables
    -   Application Related Tables
    -   DCP Related Tables
    -   Sync Related Tables
    -   Website Related Tables
    -   Tagging Related Tables
    -   Categories and Subcategories Related Tables
    -   Issues and Solutions Related Tables
    -   Deployment Status Related Tables
    -   UI Report Related Tables
    -   Mitigation Related Tables

  </Comments>
 
  <Version> 6.0.0.0 </Version>
  
  <@owner>
         padmav
  </@owner>
  
--------------------------------------------------------------------------------*/


--***************************************************
-- DELETION ROUTINES FOR DATABASE OBJECTS
-- -------------------------------------------------
-- Routines for dropping existing database objects for
-- purposes of recreation. We first drop the
-- existing database and re-create a new one
-- All old database objects are automatically dropped
-- when the database is dropped.
--***************************************************

GO

USE [master]
GO

IF EXISTS (SELECT * FROM dbo.sysdatabases WHERE name = N'ACT50')
    DROP DATABASE [ACT50]
GO

CREATE DATABASE [ACT50] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [ACT50]
GO

--*********************************************************
--  DEFINITIONS OF BASE TABLES REPRESENTING ENTITIES
-- ********************************************************

-- 
-- Create types for consistency
-- 
CREATE TYPE [dbo].[ApplicationId]       FROM NVARCHAR (32);
CREATE TYPE [dbo].[ProgramId]           FROM NVARCHAR (44);
CREATE TYPE [dbo].[AppIdentity]         FROM INT NOT NULL;
CREATE TYPE [dbo].[GroupId]             FROM NVARCHAR(40) NOT NULL;
CREATE TYPE [dbo].[PnpId]               FROM NVARCHAR (256) NOT NULL;
CREATE TYPE [dbo].[ManifestId]          FROM NVARCHAR (32) NOT NULL;
CREATE TYPE [dbo].[InstanceId]          FROM NVARCHAR (40) NOT NULL;
CREATE TYPE [dbo].[FileId]              FROM NVARCHAR (44) NOT NULL;
CREATE TYPE [dbo].[IndicatorId]         FROM NVARCHAR (44) NOT NULL;
CREATE TYPE [dbo].[PropertyId]          FROM NVARCHAR (40) NOT NULL;
CREATE TYPE [dbo].[OperatingSystemId]   FROM NVARCHAR (32) NOT NULL;
CREATE TYPE [dbo].[IssueId]             FROM NVARCHAR (36) NOT NULL;
CREATE TYPE [dbo].[SolutionId]          FROM NVARCHAR (36) NOT NULL;
CREATE TYPE [dbo].[UrlId]               FROM INT NOT NULL;
CREATE TYPE [dbo].[PackageId]           FROM NVARCHAR (32) NOT NULL;
CREATE TYPE [dbo].[FixId]               FROM NVARCHAR (32) NOT NULL;
CREATE TYPE [dbo].[OperatingSystemName] FROM NVARCHAR (50);
CREATE TYPE [dbo].[ServicePackName]     FROM NVARCHAR (32);
CREATE TYPE [dbo].[MacAddress]          FROM NVARCHAR (17) NOT NULL;
CREATE TYPE [dbo].[MachineName]         FROM NVARCHAR (50);
CREATE TYPE [dbo].[MachineId]           FROM NVARCHAR (44) NOT NULL;
CREATE TYPE [dbo].[PartialKeyHash]      FROM NVARCHAR (40);
CREATE TYPE [dbo].[AttribMatchString]   FROM NVARCHAR (1500);
CREATE TYPE [dbo].[IPAddress]           FROM NVARCHAR (32) NOT NULL;
CREATE TYPE [dbo].[DeviceId]            FROM NVARCHAR (32) NOT NULL;
CREATE TYPE [dbo].[DeviceTypeVal]       FROM NVARCHAR (100) NOT NULL;
CREATE TYPE [dbo].[Architecture]        FROM INT NOT NULL;
CREATE TYPE [dbo].[RatingType]          FROM INT NOT NULL;
CREATE TYPE [dbo].[RatingSource]        FROM INT NOT NULL;
CREATE TYPE [dbo].[SolutionType]        FROM INT NOT NULL;
CREATE TYPE [dbo].[Url]                 FROM VARCHAR (2083);
CREATE TYPE [dbo].[voteSource]          FROM INT NOT NULL;
CREATE TYPE [dbo].[EventTypeVal]        FROM NVARCHAR (35);
CREATE TYPE [dbo].[EventCategoryVal]    FROM NVARCHAR (35);
CREATE TYPE [dbo].[EventDescriptionVal] FROM NVARCHAR (2000);
CREATE TYPE [dbo].[IndicatorTypeVal]    FROM NVARCHAR (25) NOT NULL;
CREATE TYPE [dbo].[IssueTypeVal]        FROM NVARCHAR (25);
CREATE TYPE [dbo].[DCPName]             FROM NVARCHAR (50);
CREATE TYPE [dbo].[DataServiceName]     FROM NVARCHAR (50);
CREATE TYPE [dbo].[DeploymentStatus]    FROM NVARCHAR (40);
CREATE TYPE [dbo].[TimelineEventId]     FROM BIGINT;
CREATE TYPE [dbo].[UserName]            FROM NVARCHAR (256)
GO

--*******************************************
-- OS RELATED TABLES
--*******************************************

--***********************************************************
-- Table Name : OS
-- Description: Table to keep track of
-- all the operating systems we are worried about for ACT 50
-- osID included SP Major and Minor Versions
-- partial_key_hash contains only the major and minorversions
-- with the build number and this allows to roll up
-- on an OS regardless of the service pack
--************************************************************

CREATE TABLE [dbo].[OS]
(
    -- Columns
    [osID]                  [dbo].[OperatingSystemId] UNIQUE,
    [osName]                [dbo].[OperatingSystemName] CONSTRAINT [DF_OS_osName] DEFAULT '',
    [majorVersion]          int NOT NULL CONSTRAINT [DF_OS_majorVersion] DEFAULT 0,
    [minorVersion]          int NOT NULL CONSTRAINT [DF_OS_minorVersion] DEFAULT 0,
    [buildNumber]           int NOT NULL CONSTRAINT [DF_OS_buildNumber] DEFAULT 0,
    [servicePackName]       [dbo].[ServicePackName],
    [servicePackMajor]      int CONSTRAINT [DF_OS_servicePackMajor] DEFAULT 0,
    [servicePackMinor]      int CONSTRAINT [DF_OS_servicePackMinor] DEFAULT 0,
    [csdVersion]            nvarchar(50) CONSTRAINT [DF_OS_csdVersion] DEFAULT '',
    [productType]           int CONSTRAINT [DF_OS_productType] DEFAULT 0,
    [suite]                 int CONSTRAINT [DF_OS_suite] DEFAULT 0,
    [publishedDate]         datetime NOT NULL CONSTRAINT [DF_OS_publishedDate] DEFAULT GetDate()

    -- Constraints
    CONSTRAINT [OS_PK] PRIMARY KEY NONCLUSTERED
    (
        [osID]
    ) ON [PRIMARY]
)

GO

--********************************************
-- Table Name: Lps_Status
--********************************************
CREATE TABLE [dbo].[LPS_Status]
(
    -- Columns
    [logType]           [nvarchar](50),
    [totalSuccess]      [int] NOT NULL DEFAULT ((0)),
    [totalFailures]      [int] NOT NULL DEFAULT ((0)),
    [lastSuccessTime]   [datetime]  DEFAULT (((1)/(1))/(1900)),
    [lastFailureTime]   [datetime]  DEFAULT (((1)/(1))/(1900)),

    -- Constraints
    CONSTRAINT [LPS_Status_PK] PRIMARY KEY  
    (
          [logType]
    ) ON [PRIMARY]
)

GO

--********************************************
-- Table Name: Deployment_Enabled_OSes
--********************************************
CREATE TABLE [dbo].[Deployment_Enabled_OSes]
(
    -- Columns
    [osID]				[dbo].[OperatingSystemId] CONSTRAINT [Deployment_Enabled_OSes_OS_FK] REFERENCES [dbo].[OS] ([osID]),
    [enabled]			[bit] NOT NULL CONSTRAINT [DF_Deployment_Enabled_OSes_enabled]  DEFAULT ((1)),
    [lastSyncTime]		[datetime] NOT NULL CONSTRAINT [DF_Deployment_Enabled_OSes_lastSyncTime]  DEFAULT (((1)/(1))/(1900)),

    -- Constraints
    CONSTRAINT [Deployment_Enabled_OSes_PK] PRIMARY KEY  
    (
          [osID]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : Deployment_OS
-- Description: An implementation of the 
-- the Deployment Status Oses
--******************************************* 
GO 

CREATE TABLE [dbo].[Deployment_OS]
(
    [osID]				[dbo].[OperatingSystemId] CONSTRAINT [Deployment_OS_OS_FK] REFERENCES [dbo].[OS] ([osID]),
    [displayName]   nvarchar(50),
    [publishedDate] datetime NOT NULL CONSTRAINT [DF_Deployment_OS_publishedDate] DEFAULT GetDate(),

    -- Constraints
    CONSTRAINT [Deployment_OS_PK] PRIMARY KEY NONCLUSTERED
    (
        [osID]
    )ON [PRIMARY]
)

--*******************************************
-- TYPE RELATED TABLES
--*******************************************

--******************************************
-- Table Name  : DeviceType
-- Description : Implementation of a table
-- that tracks the various Device types
-- instead of hardcoding check constraints
-- for valid values
--*********************************************

CREATE TABLE [dbo].[DeviceType]
(
    -- Columns
    [type]    [dbo].[DeviceTypeVal] UNIQUE,

    -- Constraints
    CONSTRAINT [DeviceType_PK] PRIMARY KEY NONCLUSTERED
    (
        [type]
    ) ON [PRIMARY]
)

GO

--******************************************
-- Table Name  : EventType
-- Description : Implementation of a table
-- that tracks the various event types
-- instead of hardcoding check constraints
-- for valid values
--*********************************************

CREATE TABLE [dbo].[EventType]
(
    -- Columns
    [type]    [dbo].[EventTypeVal] NOT NULL,

    -- Constraints
    CONSTRAINT [EventType_PK] PRIMARY KEY NONCLUSTERED
    (
        [type]
    ) ON [PRIMARY]
)

GO

--******************************************
-- Table Name  : EventCategory
-- Description : Implementation of a table
-- that tracks the various event categories
-- instead of hardcoding check constraints
-- for valid values
--*********************************************

CREATE TABLE [dbo].[EventCategory]
(
    -- Columns
    [Category]    [dbo].[EventCategoryVal] NOT NULL,

    -- Constraints
    CONSTRAINT [EventCategory_PK] PRIMARY KEY NONCLUSTERED
    (
        [Category]
    ) ON [PRIMARY]
)

GO

--******************************************
-- Table Name  : IndicatorType
-- Description : Implementation of a table
-- that tracks the various indicator types
-- instead of hardcoding check constraints
-- for valid values
--*********************************************

CREATE TABLE [dbo].[IndicatorType]
(
    -- Columns
    [type]    [dbo].[IndicatorTypeVal],

    -- Constraints
    CONSTRAINT [IndicatorType_PK] PRIMARY KEY NONCLUSTERED
    (
        [type] 
    ) ON [PRIMARY]
)

GO

--******************************************
-- Table Name  : IssueType
--*********************************************

CREATE TABLE [dbo].[IssueType]
(
    -- Columns
    [type]    [dbo].[IssueTypeVal],

    -- Constraints
    CONSTRAINT [IssueType_PK] PRIMARY KEY NONCLUSTERED
    (
        [type] 
    ) ON [PRIMARY]
)

GO

--*******************************************
-- OTHER COMMON TABLES
--*******************************************

--*********************************************
-- Table Name  : Provider
-- Description : Implementation of a table
-- that tracks the various provider and 
-- sub-providers
--*********************************************

CREATE TABLE [dbo].[Providers]
(
    -- Columns
    [provider]     nvarchar(50) NOT NULL,
    [subProvider]  nvarchar(50) NOT NULL,

    -- Constraints
    CONSTRAINT [Providers_PK] PRIMARY KEY NONCLUSTERED
    (
        [provider],
        [subProvider]
    ) ON [PRIMARY]
)

GO

--********************************************
-- Table Name: [Architecture_Preferences]
--********************************************
CREATE TABLE [dbo].[Architecture_Preferences]
(
    [ID]					[int] NOT NULL,
    [x86enabled]   		    [int] NOT NULL,
    [x64enabled]   			[int] NOT NULL
)

GO


--*******************************************
-- UserInformation Related Tables
--*******************************************


--**************************************************************
-- Table Name : Users
-- Description: Saves the User Information Collected through RAP
--************************************************************** 
CREATE TABLE [dbo].[Users]
(
    -- Columns
    [userID]              int Identity(1,1),
    [userName]            [dbo].[UserName] NOT NULL,
    
    -- Constraints
    CONSTRAINT [Users_PK] PRIMARY KEY NONCLUSTERED
    (
        [userID]
    ) ON [PRIMARY]
)



--*******************************************
-- MACHINE RELATED TABLES
--*******************************************

--*******************************************************************
-- Table Name : Machines
-- Description: Used to keep track of machine information.
-- Physical and Logical machine information are condensed into
-- a single table. There will be multiple entries for collector
-- runs on different OSes in the same machine.
--********************************************************************

CREATE TABLE [dbo].[Machines]
(
    -- Columns
    [machineID]             [dbo].[MachineId],
    [osID]                  [dbo].[OperatingSystemId] CONSTRAINT [Machines_OS_FK] REFERENCES [dbo].[OS] ([osID]),
    [macAddress]            [dbo].[MacAddress],
    [servicePackMinor]      int NOT NULL,
    [machineName]			[dbo].[MachineName] CONSTRAINT [DF_Machines_machineName] DEFAULT '' ,
    [domainName]			nvarchar(50) CONSTRAINT [DF_Machines_domainName] DEFAULT '' ,
    [windowsDirectory]		nvarchar(100) CONSTRAINT [DF_Machines_windowsDirectory] DEFAULT '' ,
    [systemDirectory]		nvarchar(100) CONSTRAINT [DF_Machines_systemDirectory] DEFAULT '' ,
    [rootPath]				nvarchar(100) CONSTRAINT [DF_Machines_rootPath] DEFAULT '' ,
    [priority]              int CONSTRAINT [DF_Machines_priority] DEFAULT 0,
    [appCount]				int  NOT NULL CONSTRAINT [DF_Machines_appCount] DEFAULT 0,
    [deviceCount]			int  NOT NULL CONSTRAINT [DF_Machines_deviceCount] DEFAULT 0,
    [ram]                   numeric(20,1) NOT NULL,
    [pageFile]              numeric(20,1) NOT NULL,
    [virtualMem]            numeric(20,1) NOT NULL,
    [assetTag]              nvarchar(260)  NOT NULL CONSTRAINT [DF_Machines_assetTag] DEFAULT '',
    [chassisSerialNumber]   nvarchar(260)  NOT NULL CONSTRAINT [DF_Machines_chassisSerialNumber] DEFAULT '',  
    [chassisVendorName]     nvarchar(260)  NOT NULL CONSTRAINT [DF_Machines_chassisVendorName] DEFAULT '',
    [processorVendorName]   nvarchar(260)  NOT NULL CONSTRAINT [DF_Machines_processorVendorName] DEFAULT '',
    [processorName]         nvarchar(50)  NOT NULL CONSTRAINT [DF_Machines_processorName] DEFAULT '',
    [clockSpeed]            int NOT NULL,
    [processorArchitecture] nvarchar(128) NOT NULL,
    [processorFamily]       nvarchar(10) CONSTRAINT [DF_Machines_processorFamily] DEFAULT '',
    [latestLog]             int NOT NULL CONSTRAINT [DF_Machines_latestLog] DEFAULT -1,
    [latestLogTimestamp]    datetime,
    [smsGuid]               nvarchar(38),
    [smsHwId]               nvarchar(32),

    --Constraints
    CONSTRAINT [Machines_PK] PRIMARY KEY NONCLUSTERED
    (
        [machineID],
        [osID]
    ) ON [PRIMARY]
)

GO

    --********************************************
-- Table Name  : IPAddress_Machine
-- Description : Implementation of an IP-Adddress
-- Machine. An IP-Address Machine encapsulates
-- the IP-Address on a piece of named
-- hardware on which Applications run
--********************************************

CREATE TABLE [dbo].[IPAddress_Machine]
(
    -- Columns
    [IPAddress]          [dbo].[IPAddress],
    [machineID]          [dbo].[MachineId],
    [osID]               [dbo].[OperatingSystemId]

    -- Constraints
    CONSTRAINT [IPAddress_Machine_PK] PRIMARY KEY NONCLUSTERED
    (
        [IPAddress],
        [MachineID],
        [osID]
    ) ON [PRIMARY]
)

GO


--*****************************************************************************
-- Table Name : UserMachine
-- Description: Saves the User & Machine info Information Collected through RAP
--***************************************************************************** 
CREATE TABLE [dbo].[UserMachine]
(
    -- Columns
    [userMachineID]       int Identity(1,1),
    [userID]              int CONSTRAINT [UserMachine_User_FK] REFERENCES Dbo.[Users](UserID),
    [machineID]           [Dbo].[MachineID] NOT NULL,
    [osID]                [dbo].[OperatingSystemId] NOT NULL	 
    
    -- Constraints
    CONSTRAINT [UserMachine_PK] PRIMARY KEY NONCLUSTERED
    (
	[userMachineID]        
    ) ON [PRIMARY],
    
    CONSTRAINT [UserMachine_Machines_FK] FOREIGN KEY
    (
        [machineID],
        [osID]
    )
    REFERENCES [dbo].[Machines]
    (
        [machineID],
        [osID]
    )    
)
    
 GO

--*******************************************
-- DEVICE RELATED TABLES
--*******************************************

--*********************************************
-- Table Name  : Device
-- Description : Implementation of a table
-- that tracks the various devices that may be
-- installed on a given physical machine. This 
-- an inheritence implementation of the base
-- class and the corressponding sub-classes like
-- a) Disk
-- b) Video Card
--*********************************************

CREATE TABLE [dbo].[Devices]
(
    -- Identity Attribute
    [deviceID]              [dbo].[DeviceId],
    [deviceType]            [dbo].[DeviceTypeVal] CONSTRAINT [Devices_DeviceType_FK] REFERENCES [dbo].[DeviceType] ([type]),

    -- Base Attributes
    [deviceName]            nvarchar(200),
    [vendorName]            nvarchar(195),

    -- Harddisk Attributes
    [spaceAvailable]        bigint,
    [driveLetter]           nvarchar(3),
    [fileSystemFlags]       nvarchar(25),    
    [fileSystemMaxCompLen]  int,
    [fileSystemType]        nvarchar(20),
    [capacity]              bigint,
    [volumeName]            nvarchar(50),

    -- Video Card
    [chipType]              nvarchar(200),
    [videoRAM]              bigint,
    [driverVersion]         nvarchar(50),

    -- Constraints
    CONSTRAINT [Devices_PK] PRIMARY KEY NONCLUSTERED
    (
        [deviceID],
        [deviceType]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : Machines_Devices
-- Description : An implementation of the
-- N:M relationship between Physical Machines
-- and all the devices they contain
--******************************************* 

CREATE TABLE [dbo].[Machines_Devices]
(
    -- Columns
    [MachineID]     [dbo].[MachineId],
    [osID]          [dbo].[OperatingSystemId],
    [deviceID]      [dbo].[DeviceId],
    [deviceType]    [dbo].[DeviceTypeVal],

    -- Constraints
    CONSTRAINT [Machines_Devices_PK] PRIMARY KEY NONCLUSTERED
    (
        [MachineID],
        [osID],
        [deviceID],
        [deviceType]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : PnpDevice
-- Description : Contains list of Pnp Devices
-- found on target machine.
--******************************************* 

CREATE TABLE [dbo].[PnPDevice]
(
    -- Columns
    [deviceID]      [dbo].[DeviceId],
    [priority]		int NOT NULL CONSTRAINT [DF_PnPDevice_Priority] DEFAULT (2),

    -- Constraints
    CONSTRAINT [PnPDevice_PK] PRIMARY KEY NONCLUSTERED 
    (
        [deviceID]
    ) ON [PRIMARY] 
)

GO

--*******************************************
-- Table Name : PnpDevice_Machines
-- Description : Contains list of Pnp Devices
-- linked to physical machine information.
--*******************************************

CREATE TABLE [dbo].[PnPDevice_Machines]
(
    -- Columns
    [deviceID]      [dbo].[DeviceId],
    [MachineID]     [dbo].[MachineId],
    [osID]          [dbo].[OperatingSystemId]

    -- Constraints
    CONSTRAINT [PnPDevice_Machines_PK] PRIMARY KEY NONCLUSTERED 
    (
        [deviceID], 
        [MachineID],
        [osID]
    ) ON [PRIMARY],

    CONSTRAINT [PnpDevice_Machines_Machines_FK] FOREIGN KEY
    (
        [machineID],
        [osID]
    )
    REFERENCES [dbo].[Machines]
    (
        [machineID],
        [osID]
    )
)

GO

--*******************************************
-- Table Name : PnpDevice_Installed_Driver
--*******************************************

CREATE TABLE [dbo].[PnPDevice_Installed_Driver]
(
    -- Columns
    [deviceID]          [dbo].[DeviceId],
    [matchingID]        [dbo].[PnpId],
    [driverVerDate]     datetime,
    [driverVerVersion]  nvarchar(100),
    [class]             nvarchar(100),
    [manufacturer]      nvarchar(200),
    [provider]          nvarchar(200),
    [model]             nvarchar(1000),

    -- Constraints
    CONSTRAINT [PnPDevice_Installed_Driver_PK] PRIMARY KEY NONCLUSTERED 
    (
        [deviceID], 
        [matchingID]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : PnpDevice_PnPID
--*******************************************
CREATE TABLE [dbo].[PnPDevice_PnPID]
(
    -- Columns
    [deviceID]      [dbo].[DeviceId],
    [sequence]      tinyint CONSTRAINT [CHK_sequence] CHECK ([sequence] >= 0),
    [pnpID]         [dbo].[PnpId],

    -- Constraints
    CONSTRAINT [PnPDevice_PnPID_PK] PRIMARY KEY NONCLUSTERED 
    (
        [deviceID], 
        [sequence], 
        [pnpID]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- APPLICATION RELATED TABLES
--*******************************************

--**************************************************************
-- Table Name  : Application
-- Description : Application table to keep track of the all applications 
-- which have been inventoried by the toolkit agents.
-- Also includes support for prioratization & categorization
--***************************************************************

CREATE TABLE [dbo].[Applications]
(
    -- Columns
    appIdentity             [dbo].[AppIdentity] IDENTITY(1,1),
    appID                   [dbo].[ApplicationId],
    programID               [dbo].[ProgramId],
    [memberOf]              [dbo].[GroupId] NULL,
    [partial_key_hash]      [dbo].[PartialKeyHash],
    [attrib_match_string]   [dbo].[AttribMatchString],
    [Type]                  nvarchar(11) CONSTRAINT [DF_Applications_Type] DEFAULT 'Application',
    [appName]               nvarchar(260) NOT NULL CONSTRAINT [DF_Applications_appName] DEFAULT '',
    [componentType]         nvarchar(100) NOT NULL CONSTRAINT [DF_Applications_componentType] DEFAULT '',
    [vendorName]            nvarchar(260) CONSTRAINT [DF_Applications_vendorName] DEFAULT '',
    [version]               nvarchar(50) CONSTRAINT [DF_Applications_version] DEFAULT '',
    [language]              int,
    [osComponent]           nvarchar(10) CONSTRAINT [CHK_osComponent] CHECK([osComponent] = 0 OR [osComponent] = 1),
    [priority]              int CONSTRAINT [DF_Applications_priority] DEFAULT 0,
    [timeFound]             datetime CONSTRAINT [DF_Applications_timeFound] DEFAULT getdate(),
    [checksum]              nvarchar(100),
    [computerCount]         int NOT NULL CONSTRAINT [DF_Applications_computerCount] DEFAULT 0,
    [createdDate]           datetime NOT NULL CONSTRAINT [DF_Applications_createdDate] DEFAULT GetUTCDate(),
    [lastSyncTime]          datetime NOT NULL CONSTRAINT [DF_Applications_LastSyncTime] DEFAULT '1/1/1900',

    --Constraints
    CONSTRAINT [Applications_PK] PRIMARY KEY NONCLUSTERED
    (
        [appIdentity]
    ) ON [PRIMARY]
)

GO

--**************************************************************
-- Table Name  : Application_Groups
-- Description: An Application_Group defines a group of related applications.
--  These groups are created based on predefined grouping rules by the LPS
--***************************************************************

CREATE TABLE [dbo].[Application_Groups]
(
    -- Columns
    [groupID]                 [dbo].[GroupId],
    [headerApp]               int NULL CONSTRAINT [Application_Groups_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [computerCount]           int NOT NULL CONSTRAINT [DF_Application_Groups_computerCount] DEFAULT 0,
    [appCount]                int NOT NULL CONSTRAINT [DF_Application_Groups_appCount] DEFAULT 0,
    [sendMyIssues]            tinyint CONSTRAINT [DF_Application_Groups_sendMyIssues] DEFAULT 1 CONSTRAINT [CHK_sendMyIssues] CHECK( [sendMyIssues] = 0 OR [sendMyIssues] = 1),

    --Constraints
    CONSTRAINT [Application_Groups_PK] PRIMARY KEY NONCLUSTERED
    (
        [groupID]
    ) ON [PRIMARY]
)

--**************************************************************
-- Table Name  : Application_Instances
-- Description: An Application_Instance is uniquely identified
-- by the set of files associated with an application.
-- Thus it is a 1:N relationship between Applications
-- and Application Instances.
-- Ideally we would like the instanceID
-- to be a function of its file properties
-- for fast-matching
--***************************************************************

CREATE TABLE [dbo].[Application_Instances]
(
    -- Columns
    [instanceID]        [dbo].[InstanceId],
    [appIdentity]       [dbo].[AppIdentity] CONSTRAINT [Application_Instances_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [rootDirPath]       nvarchar(260) NOT NULL CONSTRAINT [DF_Application_Instances_rootDirPath] DEFAULT '',
    [installDate]       datetime,
    [count]             int NOT NULL CONSTRAINT [DF_Application_Instances_count] DEFAULT '1' CONSTRAINT [CHK_count] CHECK ([count] > 0),
    [timeFound]         datetime,

    -- Constraints
    CONSTRAINT [Application_Instances_PK] PRIMARY KEY NONCLUSTERED
    (
        [instanceID],
        [appIdentity]
    ) ON [PRIMARY]
)

GO


--***************************************************************************************
-- Table Name : Application_Indicators
-- Description: An implemetation of the 'Application_Indicator' entity type.
-- Application Indicators are those pieces of evidence which establish
-- the presence of an appliaction on a particular machine. More importantly
-- these are not used for the purposes of Issue-matching. The following 
-- inheritence implementation implements, the 
-- 1) 'App Indicator' Base Type
-- 2) 'AddRemovePrograms'
-- 3) 'Manifest'
-- 4) 'MSI'
-- 5) 'Direcotry'
-- 6) 'PathEnvironmentVariable'
-- 7) 'RegistryFileExtension'
-- 8) 'Shell'
-- 9) 'ServiceControlManager'
-- 10) 'Application_Path'
-- 11) 'Windows_Component' 
--***************************************************************************************

CREATE TABLE [dbo].[Application_Indicators]
(
    [indicatorID]           [dbo].[IndicatorId],
    [type]                  [dbo].[IndicatorTypeVal] CONSTRAINT [Application_Indicators_IndicatorType_FK] REFERENCES [dbo].[IndicatorType] ([type]),

    -- Common Properties
    [indicatorGUID]         nvarchar(50),
    [indicatorOsComponent]  nvarchar(50),
    [indicatorUniqueId]     int,
    [indicatorName]         nvarchar(50),
    [timeCreatedModified]   datetime,

    -- AddRemovePrograms
    [arpCompanyName]        nvarchar(260),
    [arpProductVersion]     nvarchar(100),    
    [arpLastUsedDate]       datetime,
    [arpRegistryPath]       nvarchar(260),
    [arpUninstallString]    nvarchar(260),
    [arpDisplayName]        nvarchar(200),
    [arpTimesUsed]          int,
    [arpPath]               nvarchar(260),
    [arpRegistrySubKey]     nvarchar(260),
    [arpHotfix]             nvarchar(10),

    -- Manifest
    [mnftProductVersion]    nvarchar(100),    
    [mnftDescription]       nvarchar(2048),
    [mnftProcessorArch]     nvarchar(215),
    [mnftProductName]       nvarchar(260),    
    [mnftPath]              nvarchar(260),    
    [mnftCompanyName]       nvarchar(260),    
    [mnftFileName]          nvarchar(260),    
    [mnftPublicKeyToken]    nvarchar(260),    
    [mnftType]              nvarchar(50),    
    [mnftCreated]           datetime,
    [mnftModified]          datetime,
    [mnftFusionType]        nvarchar(50),    

    -- MSI
    [msiCompanyName]        nvarchar(260),    
    [msiProductVersion]     nvarchar(260),    
    [msiInstallDate]        datetime,
    [msiProductGUID]        nvarchar(260),    
    [msiLanguageID]         nvarchar(260),
    [msiPackageGUID]        nvarchar(260),    
    [msiProductName]        nvarchar(260),    

    -- Directory
    [dirPath]               nvarchar(260),    
    [dirName]               nvarchar(260),
    [dirCreated]            datetime,
    [dirModified]           datetime,

    -- Registry File Extension
    [regRegistryPath]       nvarchar(260),    
    [regFileName]           nvarchar(100),    
    [regPath]               nvarchar(260),    
    [regName]               nvarchar(260),
    [regCommandLine]        nvarchar(260),
    [regRegistryRun]        nvarchar(20),
    [regexExtension]        nvarchar(260),    

    -- Shell
    [shellParentName]       nvarchar(200),    
    [shellPath]             nvarchar(260),    
    [shellTargetPath]       nvarchar(260),    
    [shellLinkPath]         nvarchar(260),    
    [shellName]             nvarchar(260),    
    [shellTargetArg]        nvarchar(200),    
    [shellLinkDirectory]    nvarchar(260),
    [shellFileName]         nvarchar(260),
    [shellHasFolderSibling] nvarchar(10),

    -- Application Path
    [appRegistryPath]       nvarchar(260),    
    [appPath]               nvarchar(260),    
    [appFilePath]           nvarchar(260),    
    [appName]               nvarchar(260),

    -- Service Control Manager
    [scmName]               nvarchar(260),    
    [scmPath]               nvarchar(260),    
    [scmDisplayName]        nvarchar(260),
    [scmDirectory]          nvarchar(260),
    [scmDll]                nvarchar(260),
    [scmCommandLine]        nvarchar(260),
    [scmStartType]          int,
    [scmServiceType]        int,
    [scmSvcHost]            nvarchar(10),
    [scmFileName]           nvarchar(260),    

    -- Windows Component
    [winProductName]        nvarchar(260),    
    [winInfo]               nvarchar(200),    
    [winDescription]        nvarchar(2048),    
    [winPath]               nvarchar(260),

    -- Path Env Var
    [pevPath]               nvarchar(260),
    [pevName]               nvarchar(260),
    [pevRegistryPath]       nvarchar(260),

    -- File Ext
    [fexPath]               nvarchar(260),
    [fexRegistryPath]       nvarchar(260),
    [fexFile]               nvarchar(260),
    [fexName]               nvarchar(260),
    [fexExtension]          nvarchar(260),

    -- Application ID for joining with Applications
    [appIdentity]           [dbo].[AppIdentity] CONSTRAINT [Application_Indicators_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    LastUpdatedDateTime     datetime CONSTRAINT [DF_Application_Indicators_LastUpdatedDateTime] DEFAULT getdate(),

    --Constraints
    CONSTRAINT [Application_Indicators_PK] PRIMARY KEY CLUSTERED
    (
        [indicatorID],
        [appIdentity]
    ) ON [PRIMARY]
)

GO

--******************************************************************
-- Table Name  : Unmatched_AppIds
-- Description : Contains the list of applications
-- and associated details for applications with only
-- AppIds and no ProgramIds. 
-- The details include NVVL from both MSI as well as ARP evidence.
-- The Match Logic is as follows:
-- Input is N, V, V', L. 
-- If the name matches, then V, V', L are matched against 
-- {V1, V2}, {V'1, V'2}, {L1, L2}.
-- When the name matches, it forms a very small subset to search for 
-- other matching information.
-- Hence we have two nonclustered indices based on Name and AltName
--****************************************************************** 
CREATE TABLE [dbo].[Unmatched_AppIds]
(
    -- Columns
    [appIdentity]       [dbo].[AppIdentity],
    [msiIndicatorId]    [dbo].[IndicatorId],
    [arpIndicatorId]    [dbo].[IndicatorId],
    [name]              nvarchar(260),
    [version]           nvarchar(260),
    [vendor]            nvarchar(260),
    [language]          nvarchar(260),
    [altName]           nvarchar(260),
    [altVersion]        nvarchar(260),
    [altVendor]         nvarchar(260),
    [altLanguage]       nvarchar(260),
    
    -- Constraints
    CONSTRAINT [Unmatched_AppIds_PK] PRIMARY KEY CLUSTERED
    (
        [appIdentity], [msiIndicatorId], [arpIndicatorId]
    )
)

GO

CREATE NONCLUSTERED INDEX [Unmatched_AppIds_Name] ON [dbo].[Unmatched_AppIds] (Name)
    INCLUDE (Version, AltVersion, Vendor, AltVendor, Language, AltLanguage)
GO

CREATE NONCLUSTERED INDEX [Unmatched_AppIds_AltName] ON [dbo].[Unmatched_AppIds] (AltName)
    INCLUDE (Version, AltVersion, Vendor, AltVendor, Language, AltLanguage)
GO

--*************************************************
-- Table Name  : App_Installed_On_Machine    
-- Description : The implementation of the relation
-- which tracks the various applications installed
-- on each logical machine
--************************************************** 

CREATE TABLE [dbo].[App_Installed_On_Machine]
(
    -- Columns
    [appIdentity]       [dbo].[AppIdentity] CONSTRAINT [App_Machine_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [osID]              [dbo].[OperatingSystemId],
    [machineID]         [dbo].[machineID],
    [servicePackMinor]  int NOT NULL,

    -- Constraints
    CONSTRAINT [App_Installed_On_Machine_PK] PRIMARY KEY NONCLUSTERED
    (
        [appIdentity],
        [machineID],
        [osID]
    ) ON [PRIMARY],

    CONSTRAINT [App_Machine_Machines_FK] FOREIGN KEY
    (
        [machineID],
        [osID]
    )
    REFERENCES [dbo].[Machines]
    (
        [machineID],
        [osID]
    )
)

GO

--*******************************************
-- DCP RELATED TABLES
--*******************************************


--*******************************************
-- Table Name : Data_Collection_Package
-- Description: An implementation of the 
-- the data collection packages generated by
-- the agents
--******************************************* 

CREATE TABLE [dbo].[Data_Collection_Package]
(
    -- Columns
    [name]          [dbo].[DCPName] NOT NULL,
    [lpsPath]       nvarchar(250),
    [type]          smallint NOT NULL,
    [tag]           nvarchar(64),
    [lastUpdated]   datetime,
    [hidden]        tinyint,
    -- Constraints
    CONSTRAINT Data_Collection_Package_PK PRIMARY KEY NONCLUSTERED
    (
        [name]
    )ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : Data_Service
-- Description: An implementation of the 
-- the data services that run agents
--******************************************* 

CREATE TABLE [dbo].[Data_Service]
(
    -- Columns
    [name]     [dbo].[DataServiceName] NOT NULL,
    [path]     nvarchar(260),
    [deleted]  bit CONSTRAINT [DF_Data_Service_deleted] DEFAULT 0,

    -- Constraints
    CONSTRAINT Data_Service_PK PRIMARY KEY NONCLUSTERED
    (
        [name]
    )ON [PRIMARY]
)

GO

--************************************************************
-- Table Name: DCP_Data_Service
-- Description: The M:N:L relationship between 
-- Data Collection Packages and Data Services
--************************************************************

CREATE TABLE [dbo].[DCP_Data_Service]
(
    -- Columns
    [dataCollectionPackage]    [dbo].[DCPName] NOT NULL CONSTRAINT [Agent_DCP_DS_DCP_FK] REFERENCES [dbo].[Data_Collection_Package] ([name]),
    [dataService]              [dbo].[DataServiceName] NOT NULL CONSTRAINT [Agent_DCP_DS_DS_FK] REFERENCES [dbo].[Data_Service] ([name]),

    -- Constraints
    CONSTRAINT [DCP_Data_Service_PK] PRIMARY KEY NONCLUSTERED
    (
        [dataCollectionPackage],
        [dataService]
    ) ON [PRIMARY]
)

GO

--************************************************************
-- Table Name: DCP_Status
-- Description: An implementation of the messages generated
-- by Agents for a particular Data Collection Package on a 
-- Data Service
--************************************************************

CREATE TABLE DCP_Status
(
    -- Columns
    [dataCollectionPackage]    [dbo].[DCPName] NOT NULL CONSTRAINT [DCP_Status_DCP_FK] REFERENCES [dbo].[Data_Collection_Package] ([name]),
    [dataService]              [dbo].[DataServiceName] NOT NULL CONSTRAINT [DCP_Status_DS_FK] REFERENCES [dbo].[Data_Service] ([name]),
    [userMachineID]             int CONSTRAINT [DCP_Status_UserMachine_FK] REFERENCES Dbo.[userMachine](userMachineID),
    [computerName]             nvarchar(50) NOT NULL,
    [timestamp]                nvarchar(50) NOT NULL,
    [status]                   int NOT NULL CONSTRAINT [CHK_Status_Type] CHECK ( [status] >= 0 AND [status] <= 4),
    [type]                     nvarchar(50) NOT NULL,
    [message]                  nvarchar(500) NOT NULL,

    -- Constraints
    CONSTRAINT [DCP_Status_PK] PRIMARY KEY NONCLUSTERED
    (
        [dataCollectionPackage],
        [dataService],
        [computerName],
        [timestamp]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- SYNC RELATED TABLES
--*******************************************

--*******************************************
-- Table Name : Application_Rating
--*******************************************

CREATE TABLE [dbo].[Application_Rating] (
    -- Columns
    [appIdentity]       [dbo].[AppIdentity],
    [osID]              [dbo].[OperatingSystemId],
    [architecture]      [dbo].[Architecture] NOT NULL,
    [ratingSource]      [dbo].[RatingSource] NOT NULL,
    [ratingType]        [dbo].[RatingType]   NOT NULL,
    [solutionType]      [dbo].[SolutionType] NOT NULL,
    [solutionUrl]       [dbo].[Url],          
    [publishedDate]     DATETIME NOT NULL,
    [applicabilityDate] DATETIME,
        
    -- Constraints
    CONSTRAINT [Application_Rating_PK] PRIMARY KEY CLUSTERED 
    (   [appIdentity], 
        [osID], 
        [architecture],
        [ratingSource]
    ) ON [PRIMARY]
)
        
GO

--*******************************************
-- Table Name : Device_Rating
--*******************************************

CREATE TABLE [dbo].[Device_Rating] (
    -- Columns
    [pnpId]             [dbo].[pnpId],
    [osID]              [dbo].[OperatingSystemId],
    [architecture]      [dbo].[Architecture]      NOT NULL,
    [ratingSource]      [dbo].[RatingSource]      NOT NULL,
    [ratingType]        [dbo].[RatingType]        NOT NULL,
    [solutionType]      [dbo].[SolutionType]      NOT NULL,
    [solutionUrl]       [dbo].[Url],
    [publishedDate]     DATETIME                  NOT NULL,
    [applicabilityDate] DATETIME,
        
    -- Constraints
    CONSTRAINT [PK_Device_Rating] PRIMARY KEY CLUSTERED 
    (   [pnpId], 
        [osID], 
        [architecture],
        [ratingSource]
    ) ON [PRIMARY]
);         
GO
    
--*******************************************
-- Table Name : Application_Votes
--*******************************************

CREATE TABLE [dbo].[Application_Votes] (
    -- Columns
    [appIdentity]        [dbo].[AppIdentity],
    [osID]               [dbo].[OperatingSystemId],
    [architecture]       [dbo].[Architecture] NOT NULL,
    [voteSource]         INT NOT NULL,
    [doesNotWorkVote]    INT NOT NULL,
    [partiallyWorksVote] INT NOT NULL,
    [worksVote]          INT NOT NULL,
    [publishedDate]      DATETIME NOT NULL,
    [lastSyncedVote]     INT NOT NULL CONSTRAINT [DF_Application_Votes_LastSyncedVote] DEFAULT 0,

    -- Constraints
    CONSTRAINT [PK_Application_Votes] PRIMARY KEY CLUSTERED 
    (   [appIdentity], 
        [osID], 
        [architecture],
        [voteSource]
    ) ON [PRIMARY] 
);

GO


--****************************************************
-- Website Related Tables
--****************************************************

--******************************************
-- Table Name: File_Opens_Url
--**************************************
CREATE TABLE [dbo].[File_Opens_Url]
(
    -- Columns
    [ieceUrlId]         [dbo].[UrlId] Identity(1,1),
    [ieceUrlPath]       [dbo].[Url] NOT NULL,
    [priority]          int NOT NULL CONSTRAINT [DF_File_Opens_Url_priority] DEFAULT 0,

    -- Constraints
    CONSTRAINT [File_Opens_Url_PK] PRIMARY KEY NONCLUSTERED
    (
        [ieceUrlId]
    ) ON [PRIMARY]
)

GO

--*******************************************
-- TAGGING RELATED TABLES
--*******************************************

--*******************************************
-- Table Name : Tagged_Applications
-- Description: An implementation of the 
-- the various tags which have
-- been assigned to various applications
--******************************************* 

CREATE TABLE [dbo].[Tagged_Applications]
(
    -- Columns
    [appIdentity]	[dbo].[AppIdentity] CONSTRAINT [Tagged_Applications_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [tag]			[nvarchar](64) NOT NULL,

    -- Constraints
    CONSTRAINT [Tagged_Applications_PK] PRIMARY KEY NONCLUSTERED
    (
        [appIdentity],
        [tag]
    )ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : Tagged_Machines
-- Description: An implementation of the 
-- the various tags which have
-- been assigned to various applications
--******************************************* 
CREATE TABLE [dbo].[Tagged_Machines]
(
    -- Columns
    [machineID]      [dbo].[MachineId],
    [osID]           [dbo].[OperatingSystemId],
    [tag]	    	 [nvarchar](64) NOT NULL,

    --Constraints
    CONSTRAINT [Tagged_Machines_PK] PRIMARY KEY NONCLUSTERED
    (
        [machineID],
        [osID],
        [tag]
    )ON [PRIMARY],

    CONSTRAINT [Tagged_Machine_Machines_FK] FOREIGN KEY
    (
        [machineID],
        [osID]
    )
    REFERENCES [dbo].[Machines]
    (
        [machineID],
        [osID]
    )
)

GO

--*******************************************
-- CATEGORIES RELATED TABLES
--*******************************************

--********************************************
-- Table Name: Categories
--********************************************

CREATE TABLE [dbo].[Categories]
(
    -- Columns
    [categoryId]        [INT] identity(1,1) NOT NULL,
    [category]          nvarchar(100) NOT NULL UNIQUE,

    -- Constraints
    CONSTRAINT Categories_PK PRIMARY KEY NONCLUSTERED
    (
        [categoryId]
    ) ON [PRIMARY]

)

GO

--**************************************************************
-- Table Name  : SubCategories
-- Description : Defines all the sub-categories
-- seperately. This is a weak entity identified 
-- by the categories
--**************************************************************

CREATE TABLE [dbo].[SubCategories]
(
    -- Columns
    [categoryId]        [INT] NOT NULL CONSTRAINT [SubCategories_Categories_FK] REFERENCES [dbo].[Categories] ([categoryId]) ON DELETE CASCADE,
    [subCategoryId]     [INT] identity(1,1) NOT NULL,
    [subCategory]       nvarchar(100) NOT NULL,

    -- Constraints
    CONSTRAINT SubCategories_PK PRIMARY KEY NONCLUSTERED
    (
        [categoryId],
        [subCategoryId]
    ) ON [PRIMARY],
    CONSTRAINT SubCategories_UniqueKey UNIQUE
    (
        [categoryId],
        [subCategory]
    ) ON [PRIMARY]
)

GO


--*******************************************
-- Table Name : Categorized_Applications
-- Description: An implementation of the 
-- the various categorizaton which has
-- been assigned to various objects
--******************************************* 

CREATE TABLE [dbo].[Categorized_Applications]
(
    -- Columns
    [appIdentity]    [dbo].[AppIdentity] CONSTRAINT [Categorized_Apps_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [categoryId]     [INT] NOT NULL,
    [subCategoryId]  [INT] NOT NULL,
    [publishedDate]  datetime CONSTRAINT [DF_Categorized_Apps_publishedDate] DEFAULT GetUTCDate(),

    -- Constraints
    CONSTRAINT [Categorized_Apps_PK] PRIMARY KEY NONCLUSTERED
    (
        [appIdentity],
        [categoryId],
        [subCategoryId]
    )ON [PRIMARY],

    CONSTRAINT [Categorized_Apps_SubCategories_FK] FOREIGN KEY
    (
        [categoryId],
        [subCategoryId]
    )
    REFERENCES [dbo].[SubCategories]
    (
        [categoryId],
        [subCategoryId]
    )
)

GO

--*******************************************
-- Table Name : Categorized_Machines
-- Description: An implementation of the 
-- the various categorizaton which has
-- been assigned to various Machines
--******************************************* 

CREATE TABLE [dbo].[Categorized_Machines]
(
    -- Columns
    [machineID]      [dbo].[MachineId],
    [osID]           [dbo].[OperatingSystemId],
    [categoryId]     [INT] NOT NULL,
    [subCategoryId]  [INT] NOT NULL,

    -- Constraints
    CONSTRAINT [Categorized_Machines_PK] PRIMARY KEY NONCLUSTERED
    (
        [machineID],
        [osID],
        [categoryId],
        [subCategoryId]
    )ON [PRIMARY],

    CONSTRAINT [Categorized_Machines_SubCategories_FK] FOREIGN KEY
    (
        [categoryId],
        [subCategoryId]
    )
    REFERENCES [dbo].[SubCategories]
    (
        [categoryId],
        [subCategoryId]
    ),

    CONSTRAINT [Categorized_Machines_Machines_FK] FOREIGN KEY
    (
        [machineID],
        [osID]
    )
    REFERENCES [dbo].[Machines]
    (
        [machineID],
        [osID]
    )
)

GO

--*******************************************
-- Table Name : Categorized_Devices
-- Description: An implementation of the 
-- the categorization of devices
--******************************************* 

CREATE TABLE [dbo].[Categorized_Devices]
(
    [deviceID]       [dbo].[DeviceId],
    [osID]           [dbo].[OperatingSystemId] CONSTRAINT [Categorized_Devices_OS_FK] REFERENCES [dbo].[OS] ([osID]),
    [categoryId]     [INT] NOT NULL,
    [subCategoryId]  [INT] NOT NULL,

    -- Constraints
    CONSTRAINT [Categorized_Devices_PK] PRIMARY KEY NONCLUSTERED
    (
        [deviceID],
        [osID],
        [categoryId],
        [subCategoryId]
    )ON [PRIMARY],

    CONSTRAINT [Categorized_Devices_SubCategories_FK] FOREIGN KEY
    (
        [categoryId],
        [subCategoryId]
    )
    REFERENCES [dbo].[SubCategories]
    (
        [categoryId],
        [subCategoryId]
    )
)

GO

--*******************************************
-- Table Name : Categorized_WebSites
-- Description: An implementation of the 
-- the various categorizaton which has
-- been assigned to various WebSites
--******************************************* 

CREATE TABLE [dbo].[Categorized_WebSites]
(
    [ieceUrlId]      [dbo].[UrlId] CONSTRAINT [Categorized_Websites_File_Opens_URL_FK] REFERENCES [dbo].[File_Opens_Url] ([ieceUrlId]),
    [categoryId]     [INT] NOT NULL,
    [subCategoryId]  [INT] NOT NULL,

    -- Constraints
    CONSTRAINT [Categorized_Websites_PK] PRIMARY KEY
    (
        [ieceUrlId],
        [categoryId],
        [subCategoryId]
    )ON [PRIMARY],

    CONSTRAINT [Categorized_Websites_SubCategories_FK] FOREIGN KEY
    (
        [categoryId],
        [subCategoryId]
    )
    REFERENCES [dbo].[SubCategories]
    (
        [categoryId],
        [subCategoryId]
    )
)

GO

--*******************************************
-- ISSUES AND SOLUTIONS RELATED TABLES
--*******************************************

--********************************************
-- Table Name  : Issues
-- Description : Implementation of a table
-- to encapsulate all the issues that may
-- exist. This table implements inheritence
-- across different types of issues like
-- a) Application Issues
-- b) HotFix Issues
-- c) IECE Issues (?)
-- Most of the issue properties are present 
-- in the 'Issue' base class and only the type
-- attributes signify each specialized issue
-- , this is implemented using an 'issueType'
-- attribute. Also this table is on the 1-side
-- of a 1:N relationship with Localized Issue 
-- and hence we collaps 'Localized Issue' as 
-- attributes to form a single table for 
-- both the entity types.
--********************************************

CREATE TABLE [dbo].[Issues]
(
    -- Key Attributes
    [issueID]               [dbo].[IssueId],
    [issueType]             [dbo].[IssueTypeVal] NOT NULL CONSTRAINT [DF_Issues_issueType] DEFAULT 'Application' CONSTRAINT [Issues_IssueType_FK] REFERENCES [dbo].[IssueType] ([type]),

    -- Matching Attributes
    [identity_hash]         nvarchar(32),
    [appIdentity]           [dbo].[AppIdentity] NULL,
    [partial_key_hash]      [dbo].[PartialKeyHash],
    [attrib_match_string]   [dbo].[AttribMatchString],

    --Providers
    [provider]              nvarchar(50)NOT NULL,
    [subProvider]           nvarchar(50)NOT NULL,

    -- Issue Details
    [severity]              int NOT NULL,
    [priority]              int NOT NULL,
    [symptom]               int NOT NULL,
    [cause]                 int NOT NULL,
    [publishedDate]         datetime NOT NULL,
    [deleteMarker]          tinyint NOT NULL CONSTRAINT [DF_Issues_deleteMarker] DEFAULT 0 CONSTRAINT [CHK_deleteMarker] CHECK ([deleteMarker] = 0 OR [deleteMarker]=1),
    [isStateDependent]      tinyint NOT NULL CONSTRAINT [DF_Issues_IsStateDependent] DEFAULT 1 CONSTRAINT [CHK_isStateDependent] CHECK ([isStateDependent] = 0 OR [isStateDependent]=1),
    [dateCreated]           datetime NOT NULL,
    [myIssue]               tinyint NOT NULL CONSTRAINT [DF_Issues_myIssue] DEFAULT 0 CONSTRAINT [CHK_myIssue] CHECK ([myIssue] = 0 OR [myIssue]=1), 

    -- Constraints
    CONSTRAINT [Issues_PK] PRIMARY KEY NONCLUSTERED
    (
        [issueID],
        [issueType]
    ) ON [PRIMARY]
)

GO

--******************************************
-- Table Name: AttachedFile
--******************************************
CREATE TABLE [dbo].[AttachedFile]
(
    -- Columns
    [attachedFileID]       nvarchar(260) NOT NULL,
    [attachedFileBlob]     varbinary(max) NOT NULL,

    -- Constraints
    CONSTRAINT [AttachedFile_PK] PRIMARY KEY NONCLUSTERED
    (
        [attachedFileID]
    ) ON [PRIMARY]
)

GO

--******************************************
-- Table Name: GeneralFeedback_Report
--******************************************
CREATE TABLE [dbo].[GeneralFeedback_Report]
(
    -- Columns
    [timeLineEventID]       [dbo].[TimelineEventId] NOT NULL,
    [eventTimeStamp]        datetime NOT NULL, 
    [userMachineId]         int NOT NULL,
    [userName]              [dbo].[UserName] NOT NULL,
    [machineName]           [dbo].[MachineName] NOT NULL,
    [osID]                  [dbo].[OperatingSystemId] NOT NULL,
    [title]                 nvarchar(500),
    [detail]                nvarchar(2000),
    [attachedFile]          nvarchar(260),

    -- Constraints
    CONSTRAINT [GeneralFeedback_Report_PK] PRIMARY KEY NONCLUSTERED
    (
        [timeLineEventID]
    ) ON [PRIMARY],

    CONSTRAINT [GeneralFeedback_AttachedFile_FK] FOREIGN KEY
    (
        [attachedFile]
    )
    REFERENCES [dbo].[AttachedFile]
    (
        [attachedFileID]
    )
)

GO

--******************************************
-- Table Name: TimeLineEvents
--******************************************
CREATE TABLE [dbo].[TimeLineEvents]
(
    --  Key Properties--
    timeLineEventID                     [dbo].[TimelineEventId] Identity(1,1),
    
    -- TimeLine event type (Issues,Contextual and general feedback)  
    [timeLineCategory]                  [dbo].[EventCategoryVal] NOT NULL CONSTRAINT [TimeLineEvents_EventCategory_FK] REFERENCES [dbo].[EventCategory] ([category]), 

    -- Event Type with in the category
    [timeLineEventType]                 [dbo].[EventTypeVal] NOT NULL CONSTRAINT [TimeLineEvents_EventType_FK] REFERENCES [dbo].[EventType] ([type]),   

    -- Application mapping
    [appIdentity]                       [dbo].[AppIdentity] NULL CONSTRAINT [TimeLineEvents_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),

    -- Event Description
    [timeLineEventDescription]          [dbo].[EventDescriptionVal] NULL,

    -- Link with issues table
    [issueID]                           [dbo].IssueID NULL,
    [issueType]                         [dbo].[IssueTypeVal] NULL,
    
    -- UserMachine Mapping
    [userMachineId]                     int CONSTRAINT [TimeLineEvents_UserMachine_FK] REFERENCES Dbo.[UserMachine](UserMachineID),	
	
    -- Commmon Properties --
    [propertyDateModified]              datetime,
    [eventTimeStamp]                    datetime NOT NULL, 

    -- IECE Properties --
    [ieceUrlName]                       nvarchar(260),
    [ieceUrlPath]                       [dbo].[Url],
    [ieceUrlZone]                       nvarchar(10),
    [ieceMitigationCode]                nvarchar(30),
    [ieceCategoryID]                    int,    
    [ieceFileName]                      nvarchar(260),
    [ieceCacheFileName]                 nvarchar(260),
    [ieceHttpHeadCT]                    nvarchar(50),
    [ieceHttpHeadCD]                    nvarchar(50),
    [ieceCalcMimeType]                  nvarchar(50),
    [ieceCFExtProgID]                   nvarchar(50),
    [ieceCLSIDMimeH]                    nvarchar(50),
    [ieceCLSIDExtH]                     nvarchar(50),
    [ieceMHProgId]                      nvarchar(50),
    [ieceWindowCommand]                 nvarchar(260),
    [ieceWindowsParam]                  nvarchar(260),
    [ieceTargetURL]                     nvarchar(260),
    [ieceTargetZone]                    nvarchar(50),
    [ieceFailureMode]                   nvarchar(10),
    [ieceLoadingUrl]                    nvarchar(260),
    [ieceBhvrUrl]                       nvarchar(260),
    [ieceRestrictBeh]                   nvarchar(50),
    [ieceControlURL]                    nvarchar(2080),
    [ieceBlockReason]                   nvarchar(50),
    [ieceCLSID]                         nvarchar(50),
    [ieceSignedBy]                      nvarchar(50),
    [ieceSignature_Error]               nvarchar(50),
    [iecePublisher]                     nvarchar(100),
    [ieceLocal_Control_Path]            nvarchar(260),
    [ieceLocal_Control_Signature]       nvarchar(50),
    [ieceControlZone]                   nvarchar(50),
    [iecePopupUrl]                      nvarchar(260),
    [iecePopupFlags]                    nvarchar(50),
    [ieceDLFile]                        nvarchar(260),
    [ieceRestrictedAction]              nvarchar(50),
    [ieceReason]                        nvarchar(50),
    [ieceNewURI]                        nvarchar(50),
    [ieceOldURI]                        nvarchar(50),
    [iecePunyCodeHost]                  nvarchar(50),
    [ieceBlockedURL]                    nvarchar(260),
    [ieceSecurityProblem]               nvarchar(50),
    [ieceScriptURL]                     nvarchar(260),
    [ieceModuleName]                    nvarchar(260),
    [ieceVirtualizationAction]          nvarchar(50),
    [ieceObjectType]                    nvarchar(50),
    [ieceAPIName]                       nvarchar(50),
    [ieceReqObjectPath]                 nvarchar(260),
    [ieceNewObjectPath]                 nvarchar(260),
    [ieceAPIResult]                     nvarchar(50),
    [ieceLastError]                     nvarchar(50),
    [ieceControlGuid]                   nvarchar(50),
    [ieceControlPublisher]              nvarchar(50),
    [ieceControlName]                   nvarchar(50),
    [ieceTargetFrameName]               nvarchar(50),
    [ieceCSSFix]                        nvarchar(50),
    [ieceCSSInformation]                nvarchar(50),
    [ieceExtensionType]                 nvarchar(50),
    [ieceInfo]                          nvarchar(50),
    [ieceError]                         nvarchar(50),
    [ieceAXKill]                        nvarchar(50),
    [ieceSignature]                     nvarchar(50),
    [ieceControlUpgrade]                nvarchar(50),
    [ieceControlUpgradePath]            nvarchar(260),
    [ieceControlUpgradePublisher]       nvarchar(100),
    [ieceControlUpgradeSignature]       nvarchar(50),    
    
    -- PCA Issue related columns
    [pcaExePath]                        nvarchar(260),
    [pcaScenarioId]                     nvarchar(50),
    [pcaUserAction]                     nvarchar(100),
    [pcaCompatibilityLayer]             nvarchar(100),
    
    -- WER fields
    [werExePath]                        nvarchar(260),

    -- SUA fields
    [suaExePath]                        nvarchar(260),
    [suaApiName]                        nvarchar(50),
    [suaMessage]                        nvarchar(4000),
    [suaShimName]                       nvarchar(100),
    [suaShimParam]                      nvarchar(500),
    [suaShimEnabled]                    nvarchar(20),
    
    -- Shim Fields
    [shimExePath]                       nvarchar(260),
    [shimFixName]                       nvarchar(100),
    [shimFixId]                         nvarchar(100),
    [shimFlags]                         nvarchar(50),
    [shimParams]                        nvarchar(4000),
    [shimFileVersion]                   nvarchar(50),
    [shimProductVersion]                nvarchar(50),
    [shimCompanyName]                   nvarchar(200),
    [shimProductName]                   nvarchar(200),
    [shimLanguage]                      nvarchar(100),
    [shimExeBitness]                    nvarchar(10),
    
    -- Feedback Fields
    [feedbackRating]                    nvarchar(100),
    [feedbackTitle]                     nvarchar(200),
    [feedbackDetails]                   nvarchar(2000),
    [feedbackAttachedFile]              nvarchar(260),

    -- PSR Fields
    [psrDescription]                    nvarchar(500),
    [psrExePath]                        nvarchar(260),

    --Timestamps
    [LastUpdatedDateTime]               datetime CONSTRAINT [DF_TimeLineEvents_LastUpdatedDateTime] DEFAULT getdate()

    -- Constraints
    CONSTRAINT timeLineEvents_PK PRIMARY KEY
    (
        [timeLineEventId]
    ) ON [PRIMARY],
    
    CONSTRAINT [timeLineEvents_Issues_FK] FOREIGN KEY
    (
        [issueID],
        [issueType]
    )
    REFERENCES [dbo].[Issues]
    (
        [issueID],
        [issueType]
    ),

    CONSTRAINT [timeLineEvents_AttachedFile_FK] FOREIGN KEY
    (
        [feedbackAttachedFile]
    )
    REFERENCES [dbo].[AttachedFile]
    (
        [attachedFileID]
    )
)

GO


--********************************************
-- Table Name  : Localized_Issue
-- Description : Implementation of a table
-- which contains the localized issue
--********************************************

CREATE TABLE [dbo].[Localized_Issue]
(
    -- Key Attributes
    [issueID]           [dbo].[IssueId],
    [issueType]         [dbo].[IssueTypeVal] NOT NULL,
    [locale]            int NOT NULL ,
    [title]             nvarchar(260),
    [Details]           nvarchar(2048),
    [linkHref]          nvarchar(260),
    [linkTitle]         nvarchar(260),
    [reproSteps]        nvarchar(2048),
    [expectedResult]    nvarchar(500),
    LastUpdatedDateTime datetime CONSTRAINT [DF_Localized_Issue_LastUpdatedDateTime] DEFAULT getdate(),

    -- Constraints
    CONSTRAINT [Localized_Issue_PK] PRIMARY KEY NONCLUSTERED
    (
        [issueID],
        [issueType],
        [locale]
    ) ON [PRIMARY],

    CONSTRAINT [Localized_Issue_Issues_FK] FOREIGN KEY
    (
        [issueID],
        [issueType]
    )
    REFERENCES [dbo].[Issues]
    (
        [issueID],
        [issueType]
    )
)

GO

--*********************************************
-- Table Name  : Solution
-- Description : Implementation of a table
-- that carries solutions for various 'Issues'
-- This table also incorporates localization
-- of the issues, since 
--*********************************************

CREATE TABLE [dbo].[Solution]
(
    -- Key Attributes
    [solutionID]        [dbo].[SolutionId],
    [solutionType]      int NOT NULL CONSTRAINT [DF_Solution_SolutionType] DEFAULT 0 ,

    -- Providers
    [provider]          nvarchar(50) NOT NULL,
    [subProvider]       nvarchar(50) NOT NULL,

    -- Other properties
    [publishedDate]     datetime NOT NULL,
    [dateModified]      datetime NOT NULL,

    -- Mitgation Related properies
    [isMitigable]       tinyint CONSTRAINT [DF_Solution_isMitigable] DEFAULT 0,
    [mitigationType]    nvarchar(50),
    [deleteMarker]      tinyint NOT NULL CONSTRAINT [DF_Solution_deleteMarker] DEFAULT 0 CONSTRAINT [CHK_Solution_DeleteMarker] CHECK ([deleteMarker] = 0 OR [deleteMarker] = 1),

    -- Constraints
    CONSTRAINT [Solution_PK] PRIMARY KEY NONCLUSTERED
    (
        [solutionID]
    ) ON [PRIMARY]
)

GO

--********************************************
-- Table Name: Localized_Solution
-- Description: Localized Soltions are stored
-- in this table
--*********************************************

CREATE TABLE [dbo].[Localized_Solution]
(
    -- Columns
    [solutionID]        [dbo].[SolutionId] CONSTRAINT [Localized_Solution_Solution_ID_FK] REFERENCES [dbo].[Solution] ([SolutionID]),
    [solutionType]      int NOT NULL CONSTRAINT [DF_Localized_Solution_solutionType] DEFAULT 0,
    [locale]            int NOT NULL,
    [title]             nvarchar(260),
    [Details]           nvarchar(2048),
    [linkHref]          nvarchar(260),
    [linkTitle]         nvarchar(260),
    LastUpdatedDateTime datetime CONSTRAINT [DF_Localized_Solution_LastUpdatedDateTime] DEFAULT getdate(),

    -- Constraints
    CONSTRAINT [Localized_Solution_PK] PRIMARY KEY NONCLUSTERED
    (
        [solutionID],
        [locale]
    ) ON [PRIMARY]
)

GO


--*********************************************
-- Table Name  : Issue_Affects_OS
-- Description : Issue_Affects_OS is a table 
-- representing the M:N relationship between
-- localized Issues and the Operating System 
-- to which the issue applies
--**********************************************

CREATE TABLE [dbo].[Issue_Affects_OS]
(
    [issueID]           [dbo].[IssueId],
    [issueType]         [dbo].[IssueTypeVal] NOT NULL,
    [osID]              [dbo].[OperatingSystemId] CONSTRAINT [Issue_Affects_OS_OS_FK] REFERENCES [dbo].[OS] ([osID]),

    -- Constraints
    CONSTRAINT [Issuue_Affects_OS_PK] PRIMARY KEY NONCLUSTERED
    (
        [issueID],
        [issueType],
        [osID]        
    ) ON [PRIMARY],

    CONSTRAINT [Issue_Affects_OS_Issues_FK] FOREIGN KEY
    (
        [issueID],
        [issueType]
    )
    REFERENCES [dbo].[Issues]
    (
        [issueID],
        [issueType]
    )
)

GO

--*********************************************
-- Table Name  : IssueSolution
-- Description : IssueSolution is a table 
-- representing the M:N relationship between
-- localized Issues and localized Solutions
-- contigent to the Operating System to which
-- the issue applies
--**********************************************

CREATE TABLE [dbo].[IssueSolution]
(
    [issueID]           [dbo].[IssueId],
    [issueType]         [dbo].[IssueTypeVal] NOT NULL,
    [solutionID]        [dbo].[SolutionId] CONSTRAINT [IssueSolution_Solution_FK] REFERENCES [dbo].[Solution] ([SolutionID]),
    [solutionType]      int NOT NULL CONSTRAINT [DF_IssueSolution_solutionType] DEFAULT 0,

    -- Constraints
    CONSTRAINT [IssueSolution_PK] PRIMARY KEY NONCLUSTERED
    (
        [issueID],
        [issueType],
        [solutionID]        
    ) ON [PRIMARY],

    CONSTRAINT [IssueSolution_Issues_FK] FOREIGN KEY
    (
        [issueID],
        [issueType]
    )
    REFERENCES [dbo].[Issues]
    (
        [issueID],
        [issueType]
    )
)

GO


--*************************************************
-- Table Name  : Issues_Associated_With_App
-- Description :Issues_Associated_With_App is a table
-- which associates all Issues associated with a
-- particular application.
--************************************************** 

CREATE TABLE [dbo].[Issues_Associated_With_App]
(
    -- Columns
    [appIdentity]    [dbo].[AppIdentity] CONSTRAINT [Issues_App_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [issueID]        [dbo].[IssueId],
    [issueType]      [dbo].[IssueTypeVal] NOT NULL,
    [isResolved]     tinyint CONSTRAINT [DF_Issues_App_isResolved] DEFAULT 0 NOT NULL,
    [isExcluded]     tinyint CONSTRAINT [DF_Issues_App_isExcluded] DEFAULT 0 NOT NULL CONSTRAINT [CHK_isExcluded] CHECK ([isExcluded] = 0 OR [isExcluded]=1),

    -- Constraints
    CONSTRAINT [Issues_App_PK] PRIMARY KEY NONCLUSTERED
    (
        [appIdentity],
        [issueID],
        [issueType]      
    ) ON [PRIMARY],

    CONSTRAINT [Issue_App_Issues_FK] FOREIGN KEY
    (
        [issueID],
        [issueType]
    )
    REFERENCES [dbo].[Issues]
    (
        [issueID],
        [issueType]
    )
)

GO


--*************************************************
-- Table Name  : Issues_Associated_With_Url
--*************************************************

CREATE TABLE [dbo].[Issues_Associated_With_Url] (
    [urlID]       [dbo].[UrlId] CONSTRAINT [Issues_Associated_With_Url_URL_FK] REFERENCES [dbo].[File_Opens_Url] ([ieceUrlId]),
    [issueID]     [dbo].[IssueId],
    [issueType]   [dbo].[IssueTypeVal] NOT NULL,
    [isExcluded]  tinyint NOT NULL CONSTRAINT [DF_Issues_Url_isExcuded] DEFAULT 0 CONSTRAINT [CHK_Issues_Url_isExcluded] CHECK ([isExcluded] = 0 OR [isExcluded]=1),
    [isResolved]  tinyint CONSTRAINT [DF_Issues_Url_isResolved] DEFAULT 0 CONSTRAINT [CHK_Issues_Url_isResolved] CHECK ([isResolved] = 0 OR [isResolved]=1),

    -- Constraints    
    CONSTRAINT [Issues_Url_PK] PRIMARY KEY NONCLUSTERED
    (
        [urlID],
        [issueID],
        [issueType]      
    ) ON [PRIMARY],

    CONSTRAINT [Issue_Url_Issues_FK] FOREIGN KEY
    (
        [issueID],
        [issueType]
    )
    REFERENCES [dbo].[Issues]
    (
        [issueID],
        [issueType]
    )
)

GO

--****************************************************
-- HOUSE-KEEPING TABLES FOR THE ACT CLIENT DATABASE
--****************************************************

--********************************************
-- Table Name: Version
--********************************************

CREATE TABLE [dbo].[Version] 
(
    [VersionCreatedDate] [datetime] NOT NULL CONSTRAINT [DF_Version_VersionCreatedDate] DEFAULT getdate(),
    [DBVersion]          [varchar] (24) NOT NULL ,
    [UIVersion]          [varchar] (24) NOT NULL,
    [LPSVersion]         [varchar] (24) NOT NULL,
    [DBGUID]             [int] NOT NULL,
    [VersionCreatedBy]   [varchar] (30) NOT NULL CONSTRAINT [DF_Version_VersionCreatedBy] DEFAULT suser_sname(),
    [LastSchemaUpdate]   [datetime] NOT NULL CONSTRAINT [DF_Version_LastSchemaUpdate] DEFAULT getdate(),
    [Upgrade]            [int] NOT NULL CONSTRAINT [DF_Version_Upgrade] DEFAULT 0
)

GO

--********************************************
-- Table Name: Client_State_Details
--********************************************

CREATE TABLE [dbo].[Client_State_Details] 
(
    [ID]                           [char] (1) NOT NULL ,
    [Last_New_Issue_Match]         [datetime] NOT NULL ,
    [Last_State_Ind_Match]         [datetime] NOT NULL ,
    [Last_Server_Sync_Time]        [datetime] NOT NULL,
    [Last_New_Prop_Issue_Match]    [datetime] NOT NULL,
    [Last_Client_Sync_Time]        [datetime] NOT NULL,
    [Last_Server_Sync_Time_Client] [datetime] NOT NULL
) ON [PRIMARY]

GO

--********************************************
-- Table Name: Trace_tbl
--********************************************

CREATE TABLE [dbo].[Trace_tbl] 
(
    [traceId]     [bigint] IDENTITY (1, 1) NOT NULL ,
    [spid]        [int] NOT NULL CONSTRAINT [DF_Trace_tbl_spid] DEFAULT @@spid,
    [traceTime]   [datetime] NOT NULL CONSTRAINT [DF_Trace_tbl_traceTime] DEFAULT getdate(),
    [cpuBusy]     [int] NOT NULL CONSTRAINT [DF_Trace_tbl_procId] DEFAULT @@cpu_busy,
    [msg]         [nvarchar] (400) NOT NULL CONSTRAINT [DF_Trace_tbl_msg] DEFAULT '',
    [user]        [char] (30) NOT NULL CONSTRAINT [DF_Trace_tbl_user] DEFAULT user_name(),
    [errNum]      [int] NULL 
) 

GO

--****************************************************
-- Deployment Status Related Tables
--****************************************************

--*******************************************
-- Table Name : Deployment_Status
-- Description: An implementation of the 
-- the Deployment Status 
--******************************************* 

CREATE TABLE [dbo].[Deployment_Status]
(
    [deployment_status]     [dbo].[DeploymentStatus] NOT NULL,

    -- Constraints
    CONSTRAINT Deployment_Status_PK PRIMARY KEY NONCLUSTERED
    (
        [deployment_status]
    )ON [PRIMARY]
)

GO

--*******************************************
-- Table Name : Apps_Deployment_Status
-- Description: An implementation of the 
-- Apps and their deployment status 
-- on different OSes
--******************************************* 

CREATE TABLE [dbo].[Apps_Deployment_Status]
(
    [appIdentity]          [dbo].[AppIdentity],
    [deployment_status]    [dbo].[DeploymentStatus] NOT NULL,
    [osID]                 [dbo].[OperatingSystemId],

    -- Constraints
    CONSTRAINT Apps_Deployment_Status_PK PRIMARY KEY NONCLUSTERED
    (
        [appIdentity],
        [osID]
    )ON [PRIMARY]
)

GO

--****************************************************
-- UI Reports Related Tables
--****************************************************

--*******************************************
-- Table Name : Device_Rport
--*******************************************
CREATE TABLE [dbo].[Device_Report]
(
    -- Columns
    [osID]              [dbo].[OperatingSystemId],
    [deviceID]          [dbo].[DeviceId],
    [model]             nvarchar(1000),
    [manufacturer]      nvarchar(200),
    [class]             nvarchar(100),
    [CompatibilityRating32] TINYINT CONSTRAINT [DF_Device_Report_CompatibilityRating32] DEFAULT 0,
    [CompatibilityRating64] TINYINT CONSTRAINT [DF_Device_Report_CompatibilityRating64] DEFAULT 0,
    [status]            nvarchar(1),
    [computers]         int,

    -- Constraints
    CONSTRAINT Device_Report_PK PRIMARY KEY NONCLUSTERED 
    (
        [osID], 
        [deviceID]
    ) ON [PRIMARY]
)

GO

--***********************************************************
-- Table Name : Computer_Report
-- Description: Table to keep track of summary statistics for a 
-- computer, including issue counts and app/device install counts
--************************************************************

CREATE TABLE [dbo].[Computer_Report]
(
    [osID]              [dbo].[OperatingSystemId],
    [depOsID]           [dbo].[OperatingSystemId],
    [machineID]         [dbo].[MachineId],
    [macAddress]        [dbo].[MacAddress],
    [appIssueCount]     int CONSTRAINT [DF_Computer_Report_appIssueCount] DEFAULT 0,
    [deviceIssueCount]  int CONSTRAINT [DF_Computer_Report_deviceIssueCount] DEFAULT 0,

    -- Constraints
    CONSTRAINT [Computer_Report_PK] PRIMARY KEY NONCLUSTERED  
    (
        [osID],
        [depOsID],
        [machineID]
    ) ON [PRIMARY]
)

GO

--***********************************************************
-- Table Name : Application_Report
-- Description: Table to keep track of
-- the 'status' of an application including ratings, 
-- deployment status and issue counts
--************************************************************

CREATE TABLE [dbo].[Application_Report]
(
    [osID]                  [dbo].[OperatingSystemId],
    [appIdentity]           [dbo].[AppIdentity],
    [myRating32]            int  NOT NULL CONSTRAINT [DF_Application_Report_myRating32] DEFAULT 0,
    [myRating64]            int  NOT NULL CONSTRAINT [DF_Application_Report_myRating64] DEFAULT 0,
    [CompatibilityRating32] TINYINT NOT NULL CONSTRAINT [DF_Application_Report_compatibilityRating32] DEFAULT 0,
    [SolutionType32]        int NOT NULL CONSTRAINT [DF_Application_Report_solutionType32] DEFAULT 0,
    [CompatibilityRating64] TINYINT NOT NULL CONSTRAINT [DF_Application_Report_compatibilityRating64] DEFAULT 0,
    [SolutionType64]        int NOT NULL CONSTRAINT [DF_Application_Report_solutionType64] DEFAULT 0,
    -- rating from R4 site
    -- 0  - Unknown
    -- 1  - Pledge - Intent to support
    -- 20 - Does not work
    -- 80 - Works As IS
    -- 90 - Vendor Signed Support
    -- 99 - Games for Windows Logo
    -- 100 - Certified

    [activeIssueCount]      int NOT NULL CONSTRAINT [DF_Application_Report_ActiveIssueCount] DEFAULT 0,
    [resolvedIssueCount]    int NOT NULL CONSTRAINT [DF_Application_Report_ResolvedIssueCount] DEFAULT 0,
    [Works32]               int NOT NULL CONSTRAINT [DF_Application_Report_Works32] DEFAULT 0,
    [PartiallyWorks32]      int NOT NULL CONSTRAINT [DF_Application_Report_PartiallyWorks32] DEFAULT 0,
    [DoesNotWork32]         int NOT NULL CONSTRAINT [DF_Application_Report_DoesNotWork32] DEFAULT 0,
    [Works64]               int NOT NULL CONSTRAINT [DF_Application_Report_Works64] DEFAULT 0,
    [PartiallyWorks64]      int NOT NULL CONSTRAINT [DF_Application_Report_PartiallyWorks64] DEFAULT 0,
    [DoesNotWork64]         int NOT NULL CONSTRAINT [DF_Application_Report_DoesNotWork64] DEFAULT 0,
    [deploymentStatus]      int NOT NULL CONSTRAINT [DF_Application_Report_deploymentStatus] DEFAULT 0,
    [version]               int NOT NULL CONSTRAINT [DF_Application_Report_version] DEFAULT 0,
    [solutionCount]         int NOT NULL CONSTRAINT [DF_Application_Report_solutionCount] DEFAULT 0,

    -- User assessment ratings from RAP client
    [UAWorks32]               int NOT NULL CONSTRAINT [DF_Application_Report_UAWorks32] DEFAULT 0,
    [UAPartiallyWorks32]      int NOT NULL CONSTRAINT [DF_Application_Report_UAPartiallyWorks32] DEFAULT 0,
    [UADoesNotWork32]         int NOT NULL CONSTRAINT [DF_Application_Report_UADoesNotWork32] DEFAULT 0,
    [UAWorks64]               int NOT NULL CONSTRAINT [DF_Application_Report_UAWorks64] DEFAULT 0,
    [UAPartiallyWorks64]      int NOT NULL CONSTRAINT [DF_Application_Report_UAPartiallyWorks64] DEFAULT 0,
    [UADoesNotWork64]         int NOT NULL CONSTRAINT [DF_Application_Report_UADoesNotWork64] DEFAULT 0,

    -- Constraints
    CONSTRAINT [Application_Report_PK] PRIMARY KEY 
    (
        [osID],
        [appIdentity]
    ) ON [PRIMARY]
)

--***********************************************************
-- Table Name : ApplicationGroup_Report
-- Description: Table to keep track of
-- the 'status' of an application group including ratings, 
-- deployment status and issue counts
--************************************************************

CREATE TABLE [dbo].[ApplicationGroup_Report]
(
    [osID]                  [dbo].[OperatingSystemId],
    [groupID]               [dbo].[GroupId],

    [CompatibilityRating32] TINYINT NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_compatibilityRating32] DEFAULT 0,
    [CompatibilityRating64] TINYINT NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_compatibilityRating64] DEFAULT 0,

    [Works32]               int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_Works32] DEFAULT 0,
    [PartiallyWorks32]      int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_PartiallyWorks32] DEFAULT 0,
    [DoesNotWork32]         int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_DoesNotWork32] DEFAULT 0,
    [Works64]               int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_Works64] DEFAULT 0,
    [PartiallyWorks64]      int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_PartiallyWorks64] DEFAULT 0,
    [DoesNotWork64]         int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_DoesNotWork64] DEFAULT 0,

    [UAWorks32]               int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_UAWorks32] DEFAULT 0,
    [UAPartiallyWorks32]      int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_UAPartiallyWorks32] DEFAULT 0,
    [UADoesNotWork32]         int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_UADoesNotWork32] DEFAULT 0,
    [UAWorks64]               int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_UAWorks64] DEFAULT 0,
    [UAPartiallyWorks64]      int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_UAPartiallyWorks64] DEFAULT 0,
    [UADoesNotWork64]         int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_UADoesNotWork64] DEFAULT 0,

    [activeIssueCount]      int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_ActiveIssueCount] DEFAULT 0,
    [resolvedIssueCount]    int NOT NULL CONSTRAINT [DF_ApplicationGroup_Report_ResolvedIssueCount] DEFAULT 0,

    -- Constraints
    CONSTRAINT [ApplicationGroup_Report_PK] PRIMARY KEY 
    (
        [osID],
        [groupID]
    ) ON [PRIMARY]
)

--*************************************************
-- Table Name  : Url_Report
-- Description : Url_Report is a table
-- which associates the status depending on the OS
-- to a website (Url)
--************************************************** 

CREATE TABLE [dbo].[Url_Report]
(
    [osID]                [dbo].[OperatingSystemId],
    [ieceUrlId]           [dbo].[UrlId],
    [myRating]            int NOT NULL CONSTRAINT [DF_Url_Report_Works32] DEFAULT 0,
    [activeIssueCount]    int CONSTRAINT [DF_Url_Report_activeIssueCount] DEFAULT 0,
    [resolvedIssueCount]  int CONSTRAINT [DF_Url_Report_resolvedIssueCount] DEFAULT 0,
    [deploymentStatus]    int NOT NULL CONSTRAINT [DF_Url_Report_deploymentStatus] DEFAULT 0,
    [version]             int NOT NULL CONSTRAINT [DF_Url_Report_version] DEFAULT 0,

    -- Constraints
    CONSTRAINT [Url_Report_PK] PRIMARY KEY  
    (
        [osID],
        [ieceUrlId]
    ) ON [PRIMARY]
)

GO

--****************************************************
-- Mitigation Related Tables
--****************************************************

--**************************************************
-- Table Name  : Mitigation_Package
-- Description : Stored information about
-- mitigation packages which are created form the UI
--*************************************************** 

CREATE TABLE [dbo].[Mitigation_Package]
(
    [packageID]    [dbo].[PackageId],
    [packageName]  nvarchar(100),

    -- Constraints
    CONSTRAINT [Mitigation_Package_PK] PRIMARY KEY NONCLUSTERED
    (
        [packageID]
    ) ON [PRIMARY]
)

GO

--**************************************************
-- Table Name  : Application_Fix
-- Description : Stored information about
-- fixes related to Applications
--*************************************************** 

CREATE TABLE [dbo].[Application_Fix]
(
    [fixID]             [dbo].[FixId],
    [solutionID]        [dbo].[SolutionId] NOT NULL CONSTRAINT [Application_Fix_Solutions_FK] REFERENCES [dbo].[Solution] ([solutionID]),
    [appIdentity]       [dbo].[AppIdentity] CONSTRAINT [Application_Fix_Applications_FK] REFERENCES [dbo].[Applications] ([appIdentity]),
    [mitigationString]  nvarchar(2000) NOT NULL,

    -- Constraints
    CONSTRAINT [Application_Fix_PK] PRIMARY KEY NONCLUSTERED
    (
        [fixID]
    ) ON [PRIMARY]
)

GO

--**************************************************
-- Table Name  : Website_Fix
-- Description : Stored information about
-- fixes related to Websites
--*************************************************** 

CREATE TABLE [dbo].[Website_Fix]
(
    [fixID]             [dbo].[FixId],
    [solutionID]        [dbo].[SolutionId] NOT NULL CONSTRAINT [Website_Fix_Solutions_FK] REFERENCES [dbo].[Solution] ([solutionID]),
    [url]               [dbo].[Url] NOT NULL,

    --Constraints
    CONSTRAINT [Website_Fix_PK] PRIMARY KEY NONCLUSTERED
    (
        [fixID]
    ) ON [PRIMARY]
)

GO

--************************************************
-- View Name   : MitigationPkg_AppFix
-- Description : This view is used to provide
-- the association between Mitigation Packages
-- and the Application Fixes they contain.
--************************************************

CREATE TABLE [dbo].[MitigationPkg_AppFix]
(
    [packageID]         [dbo].[PackageId] CONSTRAINT [MitigationPkg_AppFix_MitigationPkg_FK] REFERENCES [dbo].[Mitigation_Package] ([packageID]),
    [fixID]             [dbo].[FixId] CONSTRAINT [MitigationPkg_AppFix_Application_Fix_PK] REFERENCES [dbo].[Application_Fix] ([fixID]),

    -- Constraints
    CONSTRAINT [MitigationPkg_AppFix_PK] PRIMARY KEY NONCLUSTERED
    (
        [packageID],
        [fixID]
    ) ON [PRIMARY]
)

GO

--************************************************
-- View Name   : MitigationPkg_WebsiteFix
-- Description : This view is used to provide
-- the association between Mitigation Packages
-- and the Website Fixes they contain.
--************************************************

CREATE TABLE [dbo].[MitigationPkg_WebsiteFix]
(
    [packageID]         [dbo].[PackageId] CONSTRAINT [MitigationPkg_WebsiteFix_MitigationPkg_FK] REFERENCES [dbo].[Mitigation_Package] ([packageID]),
    [fixID]             [dbo].[FixId] CONSTRAINT [MitigationPkg_WebsiteFix_Website_Fix_PK] REFERENCES [dbo].[Website_Fix] ([fixID]),

    -- Constraints
    CONSTRAINT [MitigationPkg_WebsiteFix_PK] PRIMARY KEY NONCLUSTERED
    (
        [packageID],
        [fixID]
    ) ON [PRIMARY]
)

GO


-- Count = 79 tables / 97 tables
-- Dropped Tables: App_Status, Physical_Machine, Logical_Machine, Physical_Logical_Machine, UIA, del_Issue_Affects_OS
--               : Issues_Linked_Static_Properties, Url_Status, No_Known_Issues, App_Risk_Rating, App_Instance_Files,
--               : App_Instance_Machines, UIA_File, UIA_Registry_Key, PnpID_Status, PnpID_Status_MEssage_Lookup, SystemSpec, Risk_Rating,
--               : App_Contain_Dynamic_Props
-- Dropped table Count: 19
-- Added Tables: Machines
-- Added table Count: 1


/*--------------------------------------------------------------------------------
  
  <Copyright file="CreateFunctions.sql" company="Microsoft">
    Copyright (c) Microsoft Corporation.  All rights reserved.
  </Copyright>

  <Comments>
    
    The following is the new implementation of the Application
    Compatability Toolkit Client Database. This is the creation
    script that is used to create functions on the required base
    tables.

  </Comments>
 
  <Version> 6.0.0.0 </Version>
  
  <@owner>
         padmav
  </@owner>
  
--------------------------------------------------------------------------------*/
GO

--**************************************
-- Function Name: TraceLevel 
-- Description : This function is
-- dynamically re-created everytime
-- the Trace level is set and this avoids
-- IOs.
--****************************************

CREATE  FUNCTION [dbo].[TraceLevel] ()
RETURNS int 
AS  
    BEGIN 
        RETURN(0) 
    END
GO


/*--------------------------------------------------------------------------------
  
  <Copyright file="CreateViews.sql" company="Microsoft">
    Copyright (c) Microsoft Corporation.  All rights reserved.
  </Copyright>

  <Comments>
    
    The following is the new implementation of the Application
    Compatability Toolkit Client Database. This is the creation
    script that is used to create views on the required base
    tables. This script is organized into the following sections:
    
    -   OS Related Tables_ 
    -   Type definitions
    -   Common tables
    -   Machine Related Tables
    -   Device Related Tables
    -   Application Related Tables
    -   DCP Related Tables
    -   Sync Related Tables
    -   Website Related Tables
    -   Tagging Related Tables
    -   Categories and Subcategories Related Tables
    -   Issues and Solutions Related Tables
    -   Deployment Status Related Tables
    -   UI Report Related Tables
    -   Mitigation Related Tables

  </Comments>
 
  <Version> 6.0.0.0 </Version>
  
  <@owner>
         padmav
  </@owner>
  
--------------------------------------------------------------------------------*/

--*******************************************
-- OS RELATED TABLES
--*******************************************

--******************************
-- Deployment_OS_vw
-- View returns the details of the deployment OS details.
--******************************

CREATE VIEW [dbo].[Deployment_OS_vw]
AS
    SELECT  depOs.[osID],
            ISNULL(depOs.[displayName], os.[osName]) [displayName],
            depEnabled.enabled [enabled]
    FROM [dbo].[Deployment_OS] depOs 
    JOIN [dbo].[Deployment_Enabled_OSes] depEnabled
        ON ( depEnabled.osID = depOs.osID )
    JOIN [dbo].[OS] os 
        ON ( os.osID = depOs.osID )

GO

--*******************************************
-- MACHINE RELATED TABLES
--*******************************************

--************************************************
-- View Name   : Application_Computers_Query
-- Description : This view provides the logical and physical
-- machine properties for a computer on which an app is installed
--************************************************

CREATE VIEW [dbo].[Application_Computers_Query]
AS
    SELECT installedApp.appIdentity,
           M.macAddress,
           M.servicePackMinor,
           M.machineName,
           M.domainName,
           M.windowsDirectory,
           M.systemDirectory,
           M.rootPath,
           M.priority,
           M.ram,
           M.assetTag,
           M.chassisSerialNumber,
           M.chassisVendorName,
           M.processorVendorName,
           M.processorName,
           M.clockSpeed,
           M.processorArchitecture,
           M.processorFamily
    FROM [dbo].[Machines] M
    JOIN [dbo].[App_Installed_On_Machine] installedApp 
        ON ( installedApp.machineID = M.machineID )
GO

--*******************************************
-- DCP RELATED TABLES
--*******************************************

--************************************************
-- View Name   : DCP_Status_Errors
-- Description : This view is used to provide all
-- the error messages from DCP_Status.
--************************************************

CREATE VIEW [dbo].[DCP_Status_Errors]
AS
    SELECT computerName, 
           dataService, 
           dataCollectionPackage, 
           timestamp, 
           type, 
           message
    FROM [dbo].[DCP_Status]
    WHERE (status = 2)
GO

--************************************************
-- View Name   : DCP_Status_Report
-- Description : This view is used to provide a 
-- report of the DCPs' processing.
--************************************************

CREATE VIEW [dbo].[DCP_Status_Report]
AS
    SELECT packageProgress.dataCollectionPackage, 
           sum(packageProgress.inError) AS numInError, 
           sum(packageProgress.completed) AS numCompleted, 
           sum(packageProgress.inProgress) AS numInProgress,
           sum(packageProgress.inError) + sum(packageProgress.completed) + sum(packageProgress.inProgress) AS numComputers
    FROM
    -- The case logic below looks messy, but it is done in this manner to make each of the 
    -- three columns explicitly exclusive.  The inError column is assigned first - if any
    -- error at all was received during the most recent DCP run on a computer, the package
    -- is in error.  The package is considered completed if it did not meet the error criteria
    -- and the last started message, if we received one, is before the last completed message.
    -- The package is then considered in progress if it does not meet the criteria for either
    -- of the previous 2 buckets.  Logic could be simplified by spelling out each set of criteria
    -- separately, but this ensures that the columns are exclusive and counts are not prone to logic error.
    (
        SELECT packageTimeline.computerName, 
               packageTimeline.dataCollectionPackage,
               CASE WHEN (NOT packageTimeline.lastErrorTime IS NULL AND 
                         (packageTimeline.lastCompletedTime IS NULL  OR
                         packageTimeline.lastErrorTime > packageTimeline.lastCompletedTime) AND
                         (packageTimeline.lastStartedTime IS NULL OR 
                         packageTimeline.lastErrorTime > packageTimeline.lastStartedTime)) THEN 1
                    ELSE 0
               END AS inError,
               CASE WHEN (NOT (NOT packageTimeline.lastErrorTime IS NULL AND 
                         (packageTimeline.lastCompletedTime IS NULL  OR
                         packageTimeline.lastErrorTime > packageTimeline.lastCompletedTime) AND
                         (packageTimeline.lastStartedTime IS NULL OR 
                         packageTimeline.lastErrorTime > packageTimeline.lastStartedTime)) AND 
                         NOT packageTimeline.lastCompletedTime IS NULL AND 
                         (packageTimeline.lastStartedTime IS NULL OR 
                         packageTimeline.lastCompletedTime > packageTimeline.lastStartedTime)) THEN 1
                    ELSE 0
               END AS completed,
               CASE WHEN (NOT (NOT (NOT packageTimeline.lastErrorTime IS NULL AND 
                         (packageTimeline.lastCompletedTime IS NULL  OR
                         packageTimeline.lastErrorTime > packageTimeline.lastCompletedTime) AND
                         (packageTimeline.lastStartedTime IS NULL OR 
                         packageTimeline.lastErrorTime > packageTimeline.lastStartedTime)) AND 
                         NOT packageTimeline.lastCompletedTime IS NULL AND 
                         (packageTimeline.lastStartedTime IS NULL OR 
                         packageTimeline.lastCompletedTime > packageTimeline.lastStartedTime)) AND
                         NOT (NOT packageTimeline.lastErrorTime IS NULL AND 
                         (packageTimeline.lastCompletedTime IS NULL  OR
                         packageTimeline.lastErrorTime > packageTimeline.lastCompletedTime) AND
                         (packageTimeline.lastStartedTime IS NULL OR 
                         packageTimeline.lastErrorTime > packageTimeline.lastStartedTime))) THEN 1
                    ELSE 0
               END AS inProgress
        FROM
        (
            SELECT status.computerName, status.dataCollectionPackage, lastStartedTime, lastCompletedTime, lastErrorTime
            FROM [dbo].[DCP_Status] status
            LEFT OUTER JOIN (SELECT computerName, dataCollectionPackage, max(timestamp) AS lastStartedTime FROM [dbo].[DCP_Status] WHERE (status=0) GROUP BY computerName, dataCollectionPackage) AS lastStarted
                ON (lastStarted.computerName = status.computerName AND lastStarted.dataCollectionPackage = status.dataCollectionPackage)
            LEFT OUTER JOIN (SELECT computerName, dataCollectionPackage, max(timestamp) AS lastCompletedTime FROM [dbo].[DCP_Status] WHERE (status=1) GROUP BY computerName, dataCollectionPackage) AS lastCompleted
                ON (lastCompleted.computerName = lastStarted.computerName AND lastCompleted.dataCollectionPackage = lastStarted.dataCollectionPackage)
            LEFT OUTER JOIN (SELECT computerName, dataCollectionPackage, max(timestamp) AS lastErrorTime FROM [dbo].[DCP_Status] WHERE (status=2) GROUP BY computerName, dataCollectionPackage) AS lastError
                ON (lastError.computerName = lastStarted.computerName AND lastError.dataCollectionPackage = lastStarted.dataCollectionPackage)
            GROUP BY status.dataCollectionPackage, status.computerName, lastStartedTime, lastCompletedTime, lastErrorTime
        ) AS packageTimeline
    ) AS packageProgress
    GROUP BY dataCollectionPackage

GO

--*******************************************
-- CATEGORIES AND SUBCATEGORIES RELATED TABLES
--*******************************************

--************************************************
-- View Name   : Categorized_Applications_Query
-- Description : This view provides the category name, subcategory name,
-- app id, and published date for categorized apps
--************************************************

CREATE VIEW [dbo].[Categorized_Applications_Query]
AS
    SELECT catApp.appIdentity,
           catApp.publishedDate,
           subCat.subCategory,
           cat.category
    FROM [dbo].[categorized_applications] catApp
    JOIN [dbo].[subCategories] subCat 
        ON ( catApp.categoryId = subCat.categoryId AND catApp.subCategoryId = subCat.subCategoryId )
    JOIN [dbo].[categories] cat 
        ON ( catApp.categoryId = cat.CategoryId )

GO

--************************************************
-- View Name   : Categorized_Machines_Query
-- Description : This view provides the category name, subcategory name,
-- and machine ids for categorized machines
--************************************************

CREATE VIEW [dbo].[Categorized_Machines_Query]
AS
    SELECT catMac.osID,
           catMac.machineID,
           subCat.subCategory,
           cat.category
    FROM [dbo].[categorized_machines] catMac
    JOIN [dbo].[subCategories] subCat 
        ON ( catMac.categoryId = subCat.categoryId AND catMac.subCategoryId = subCat.subCategoryId )
    JOIN [dbo].[categories] cat 
        ON ( catMac.categoryId = cat.CategoryId )
GO

--************************************************
-- View Name   : Categorized_Devices_Query
-- Description : This view provides the category name, subcategory name,
-- and device ids for categorized devices
--************************************************

CREATE VIEW [dbo].[Categorized_Devices_Query]
AS
    SELECT catDev.osID,
           catDev.deviceID,
           subCat.subCategory,
           cat.category
    FROM [dbo].[categorized_devices] catDev
    JOIN [dbo].[subCategories] subCat 
        ON ( catDev.categoryId = subCat.categoryId AND catDev.subCategoryId = subCat.subCategoryId )
    JOIN [dbo].[categories] cat 
        ON  ( catDev.categoryId = cat.CategoryId )
GO


--************************************************
-- View Name   : Categorized_Websites_Query
-- Description : This view provides the category name, subcategory name,
-- and website id for categorized websites
--************************************************

CREATE VIEW [dbo].[Categorized_Websites_Query]
AS
    SELECT url.ieceUrlPath,
           url.ieceUrlId,
           subCat.subCategory,
           cat.category
    FROM [dbo].[categorized_websites] catWeb
    JOIN [dbo].[subCategories] subCat 
        ON ( catWeb.categoryId = subCat.categoryId AND catWeb.subCategoryId = subCat.subCategoryId )
    JOIN [dbo].[categories] cat 
        ON ( catWeb.categoryId = cat.CategoryId )
    JOIN [dbo].[File_Opens_Url] url 
        ON ( url.ieceUrlId = catWeb.ieceUrlId )
GO

--*******************************************
-- ISSUES AND SOLUTIONS RELATED TABLES
--*******************************************

--*********************************************************************************************************
-- View for App_Issues_Linked_TimeLineEvents
--*********************************************************************************************************
CREATE VIEW [dbo].[App_Issues_Linked_TimeLineEvents]
AS (
    SELECT a.appIdentity, a.issueID, a.issueType, c.timeLineEventID, a.isResolved, a.isExcluded
      FROM [dbo].[Issues_Associated_With_App] a
      JOIN [dbo].[TimeLineEvents] c
          ON c.appIdentity = a.appIdentity AND a.issueID = c.issueID AND a.issueType = c.issueType
)

GO

--************************************************
-- View Name   : Issue_Solutions_Query
-- Description : This view provides the solution data for the issue
--************************************************

CREATE VIEW [dbo].[Issue_Solutions_Query]
AS
    SELECT issue.issueID AS issueID,
           solution.solutionID AS solutionID,
           solution.provider AS provider,
           solution.subProvider AS subProvider,
           solution.publishedDate AS publishedDate,
           solution.dateModified AS dateModified,
           solution.solutionType AS solutionType
    FROM [dbo].[issuesolution] issue 
    JOIN [dbo].[solution] solution 
        ON ( issue.solutionID = solution.solutionID )
    WHERE solution.deleteMarker = 0

GO

--************************************************
-- View Name   : AppReport_Issues_Query
-- Description : This view provides the issues for an app report, given the appId
-- and the osID
--************************************************

CREATE VIEW [dbo].[AppReport_Issues_Query]
AS
    SELECT assoc.appIdentity AS appIdentity,
           issue.issueID AS issueID,
           os.osID AS osID,
           assoc.isResolved AS isResolved,
           issue.issueType AS issueType,
           issue.cause AS cause,
           issue.priority AS priority,
           issue.provider AS provider,
           issue.subProvider AS subProvider,
           issue.severity AS severity,
           issue.symptom AS symptom,
           issue.publishedDate AS publishedDate,
           issue.dateCreated AS dateCreated
    FROM [dbo].[Issues_Associated_With_App] assoc
    JOIN [dbo].[Issues] issue 
        ON (assoc.issueID = issue.issueID AND assoc.issueType = issue.issueType)
    JOIN [dbo].[Issue_Affects_OS] os 
        ON (issue.issueID = os.issueID AND issue.issueType = os.issueType)
    WHERE issue.deleteMarker = 0

GO

--************************************************
-- View Name   : Application_Issues_Os
-- Description : This view gives issues associated with application and OS
--************************************************
CREATE VIEW [dbo].[Application_Issues_Os]
AS
    SELECT app.appIdentity appIdentity,
           appIssue.issueID issueID,
           appIssue.isResolved isResolved,
           issueOS.osID osID 
    FROM [dbo].[Applications] app
    JOIN [dbo].[Issues_Associated_With_App] appIssue 
        ON	(app.appIdentity = appIssue.appIdentity)
    JOIN [dbo].[Issue_Affects_OS] issueOS 
        ON	(appIssue.issueID = issueOS.issueID)
    JOIN [dbo].[Issues] issue
        ON	issue.issueID = appIssue.issueID
    WHERE issue.deleteMarker = 0

GO

--************************************************
-- View Name   : UrlReport_Issues_Query
-- Description : This view provides the issues for a url report, given the urlId
-- and the osID
--************************************************

CREATE VIEW [dbo].[UrlReport_Issues_Query]
AS
    SELECT assoc.urlId AS urlId,
           issue.issueID AS issueID,
           os.osID AS osID,
           assoc.isResolved AS isResolved,
           issue.issueType AS issueType,
           issue.cause AS cause,
           issue.priority AS priority,
           issue.provider AS provider,
           issue.subProvider AS subProvider,
           issue.severity AS severity,
           issue.symptom AS symptom,
           issue.publishedDate AS publishedDate,
           issue.dateCreated AS dateCreated
    FROM [dbo].[Issues_Associated_With_Url] assoc
    JOIN [dbo].[Issues] issue 
        ON (assoc.issueID = issue.issueID AND assoc.issueType = issue.issueType)
    JOIN [dbo].[Issue_Affects_OS] os 
        ON (issue.issueID = os.issueID AND issue.issueType = os.issueType)
    WHERE issue.deleteMarker = 0

GO

--************************************************
-- View Name   : Computer_DeviceIssue_Count
-- Description : This view tracks device issues on a machine
--************************************************

CREATE VIEW [dbo].[Computer_DeviceIssue_Count] 
AS
    SELECT  COUNT(DISTINCT mcDevice.deviceID) as deviceIssueCount
            ,comp.osID
            ,comp.depOsID
            ,comp.macAddress
            ,comp.machineID
    FROM [dbo].[Computer_Report] comp
    LEFT OUTER JOIN [dbo].[PnPDevice_Machines] mcDevice 
        ON  comp.machineID = mcDevice.machineID
    LEFT OUTER JOIN [dbo].[Device_Report] device 
        ON  mcDevice.deviceID = device.deviceID AND comp.depOsId = device.osID
    WHERE (device.CompatibilityRating32 < 80  AND device.CompatibilityRating32 > 0) OR (device.CompatibilityRating64 < 80  AND device.CompatibilityRating32 > 0)
    GROUP BY comp.osID, comp.machineID, comp.depOsID, comp.macAddress
                  
GO

--*******************************************
-- UI Reports RELATED TABLES
--*******************************************

--************************************************
-- View Name   : Device_Report_vw
-- Description : This view provides the device report information.
--************************************************

CREATE VIEW [dbo].[Device_Report_vw]
AS
    SELECT report.class,
           report.computers,
           report.deviceID,
           report.manufacturer,
           report.model,
           report.osID,
           report.CompatibilityRating32,
           report.CompatibilityRating64,
           report.status,
           device.priority
    FROM [dbo].[Device_Report] report
    JOIN [dbo].[PnPDevice] device
        ON device.deviceID = report.deviceID

GO

--************************************************
-- View Name   : Summary_Reports
-- Description : This view provides the summary view information
--************************************************

CREATE VIEW [dbo].[Summary_Reports]
AS
    SELECT 'CompatibilityRating32' as type, 
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 20 THEN 1 
                  END) as notWorking,
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 50 THEN 1 
                  END) as minorIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 80 OR CompatibilityRating32 = 90 OR CompatibilityRating32 = 99 OR CompatibilityRating32 = 100 THEN 1 
                  END) as noKnownIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 0 OR CompatibilityRating32 = 1 OR CompatibilityRating32 IS NULL THEN 1 
                  END) as noData,
            COUNT(AG.groupID) as total,
            OsId as osid
    FROM [dbo].[ApplicationGroup_Report] AR
    JOIN [dbo].[Application_Groups] AG
    ON AR.groupID = AG.groupID
    JOIN [dbo].[Applications] A
        ON AG.headerApp = A.appIdentity AND Type='Application' 
    GROUP BY OsId           
    UNION ALL
    SELECT 'CompatibilityRating64' as type, 
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 20 THEN 1 
                  END) as notWorking,
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 50 THEN 1 
                  END) as minorIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 80 OR CompatibilityRating64 = 90 OR CompatibilityRating64 = 99 OR CompatibilityRating64 = 100 THEN 1 
                  END) as noKnownIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 0 or CompatibilityRating64 = 1 or CompatibilityRating64 IS NULL THEN 1
                  END) as noData,
            COUNT(AG.groupID) as total,
            OsId as osid
    FROM [dbo].[ApplicationGroup_Report] AR
    JOIN [dbo].[Application_Groups] AG
    ON AR.groupID = AG.groupID
    JOIN [dbo].[Applications] A
        ON AG.headerApp = A.appIdentity AND Type='Application' 
    GROUP BY OsId          
    UNION ALL
    SELECT 'MyRating32' as type, 
            COUNT(CASE 
                      WHEN myRating32 = 3 THEN 1 
                  END) as notWorking,
                  COUNT(CASE 
                      WHEN myRating32 = 2 THEN 1 
                  END) as minorIssue,
                  COUNT(CASE 
                      WHEN myRating32 = 1 THEN 1 
                  END) as noKnownIssue,
            COUNT(CASE 
                      WHEN myRating32 != 1 AND myRating32 != 2 AND myRating32 != 3 or myRating32 is NULL THEN 1 
                  END) as noData,
            COUNT(A.appIdentity) as total,
            OsId as osid
    FROM [dbo].[Application_Report] AR
    JOIN [dbo].[Applications] A 
        ON AR.appIdentity = A.appIdentity and Type='Application'  
    JOIN [dbo].[Application_Groups] AG
        ON AG.headerApp = A.appIdentity
    GROUP BY OsId         
    UNION ALL
    SELECT 'MyRating64' as type, 
        COUNT(CASE 
                  WHEN myRating64 = 3 THEN 1 
              END) as notWorking,
        COUNT(CASE 
                  WHEN myRating64 = 2 THEN 1 
              END) as minorIssue,
        COUNT(CASE 
                  WHEN myRating64 = 1 THEN 1 
              END) as noKnownIssue,
        COUNT(CASE 
                  WHEN myRating64 != 1 AND myRating64 != 2 AND myRating64 != 3 or myRating64 is NULL THEN 1 
              END) as noData,
        COUNT(A.appIdentity) as total,
        OsId as osid
    FROM [dbo].[Application_Report] AR
    JOIN [dbo].[Applications] A 
        ON AR.appIdentity = A.appIdentity and Type='Application'  
    JOIN [dbo].[Application_Groups] AG
        ON AG.headerApp = A.appIdentity
    GROUP BY OsId           
    UNION ALL
    SELECT 'Devices32' as type, 
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 20 THEN 1 
                  END) as notWorking,
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 50 THEN 1 
                  END) as minorIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 80 OR CompatibilityRating32 = 90 OR CompatibilityRating32 = 99 OR CompatibilityRating32 = 100 THEN 1 
                  END) as noKnownIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating32 = 0 OR CompatibilityRating32 = 1 OR CompatibilityRating32 IS NULL THEN 1 
                  END) as noData,
            COUNT(deviceID) as total,
            OsId as osid
    FROM [dbo].[Device_Report]
    GROUP BY OsId  
    UNION ALL
    SELECT 'Devices64' as type, 
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 20 THEN 1 
                  END) as notWorking,
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 50 THEN 1 
                  END) as minorIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 80 OR CompatibilityRating64 = 90 OR CompatibilityRating64 = 99 OR CompatibilityRating64 = 100 THEN 1 
                  END) as noKnownIssue,
            COUNT(CASE 
                      WHEN CompatibilityRating64 = 0 or CompatibilityRating64 = 1 or CompatibilityRating64 IS NULL THEN 1
                  END) as noData,
            COUNT(deviceID) as total,
            OsId as osid
    FROM [dbo].[Device_Report]
    GROUP BY OsId  
GO

--*******************************************
-- Views For SCCM Integration
--*******************************************
--************************************************
-- View Name   : DCP_vw
-- Description : This DCP view is provided for SCCM integration
--************************************************

CREATE VIEW [dbo].[DCP_vw]
AS
    SELECT [Name], lpsPath, [Type], LastUpdated
      FROM [dbo].[Data_Collection_Package]
GO

--************************************************
-- View Name   : Applications_vw
-- Description : This Application view is provided for SCCM integration
--************************************************

CREATE VIEW [dbo].[Applications_vw]
AS
       SELECT appIdentity as Identity_Hash, AppName, [Type], VendorName, Version, [Language], ComputerCount 
         FROM [dbo].[Applications]
GO

--************************************************
-- View Name   : Application_Report_vw
-- Description : This Application Report view is provided for SCCM integration
--************************************************

CREATE VIEW [dbo].[Application_Report_vw]
AS
    SELECT  A.AppName
           ,AR.OSID
           ,A.appIdentity
           ,CASE WHEN (AR.myRating32 = 0 OR AR.myRating32 IS NULL) THEN 4 
                 ELSE AR.myRating32 END AS  myRating32
           ,CASE WHEN (AR.myRating64 = 0 OR AR.myRating64 IS NULL) THEN 4 
                 ELSE AR.myRating64 END AS  myRating64
           ,CASE WHEN (AR.CompatibilityRating32 = 0 OR AR.CompatibilityRating32 IS NULL) THEN 100 -- Unknown - No data
                 WHEN (AR.CompatibilityRating32 = 1) THEN 100   --   Pledge Intent to support
                 WHEN (AR.CompatibilityRating32 = 10) THEN 60   --   can not Install  
                 WHEN (AR.CompatibilityRating32 = 20) THEN 60   --   Does not work 
                 WHEN (AR.CompatibilityRating32 = 30) THEN 60   --   No support
                 WHEN (AR.CompatibilityRating32 = 40) THEN 60   --   Significant issues
                 WHEN (AR.CompatibilityRating32 = 50) THEN 50   --   Minor Issues
                 WHEN (AR.CompatibilityRating32 = 60) THEN 40   --   Supported with fix
                 WHEN (AR.CompatibilityRating32 = 70) THEN 40   --   Windows Update
                 WHEN (AR.CompatibilityRating32 = 80) THEN 30   --   Works As IS 
                 WHEN (AR.CompatibilityRating32 = 90) THEN 20   --   Vendor Signed Support
                 WHEN (AR.CompatibilityRating32 = 99) THEN 10   --   Games for Windows Logo
                 WHEN (AR.CompatibilityRating32 = 100) THEN 10  --   Certified
                 ELSE 100
            END as vendorRating32
           ,CASE WHEN (AR.CompatibilityRating64 = 0 OR AR.CompatibilityRating64 IS NULL) THEN 100 -- Unknown - No data
                 WHEN (AR.CompatibilityRating64 = 1) THEN 100   --   Pledge Intent to support
                 WHEN (AR.CompatibilityRating64 = 10) THEN 60   --   can not Install  
                 WHEN (AR.CompatibilityRating64 = 20) THEN 60   --   Does not work 
                 WHEN (AR.CompatibilityRating64 = 30) THEN 60   --   No support
                 WHEN (AR.CompatibilityRating64 = 40) THEN 60   --   Significant issues
                 WHEN (AR.CompatibilityRating64 = 50) THEN 50   --   Minor Issues
                 WHEN (AR.CompatibilityRating64 = 60) THEN 40   --   Supported with fix
                 WHEN (AR.CompatibilityRating64 = 70) THEN 40   --   Windows Update
                 WHEN (AR.CompatibilityRating64 = 80) THEN 30   --   Works As IS 
                 WHEN (AR.CompatibilityRating64 = 90) THEN 20   --   Vendor Signed Support
                 WHEN (AR.CompatibilityRating64 = 99) THEN 10   --   Games for Windows Logo
                 WHEN (AR.CompatibilityRating64 = 100) THEN 10  --   Certified
                 ELSE 100
            END as vendorRating64
           ,0 as microsoftRating
           ,AR.Works32 as numCommGreen
           ,AR.PartiallyWorks32 as numCommYellow
           ,AR.DoesNotWork32 as numCommRed
           FROM [dbo].[Application_Report] AR WITH (NOLOCK)
           JOIN [dbo].[Applications] A WITH (NOLOCK)
               ON AR.appIdentity = A.appIdentity
GO

--************************************************
-- View Name   : Machine_Installed_App_vw
-- Description : This Application Installed on Machines view is provided for SCCM integration
--************************************************

CREATE VIEW [dbo].[Machine_Installed_App_vw]
AS
    SELECT A.AppName, A.appIdentity, M.machinename, M.smsguid, M.domainname -- do you really need OSID? There is already smsgmid
       FROM [dbo].[App_Installed_On_Machine] AIOM WITH (NOLOCK)
       JOIN [dbo].[Applications] A WITH (NOLOCK)
           ON A.appIdentity = AIOM.appIdentity
       JOIN [dbo].[Machines] M WITH (NOLOCK)
           ON AIOM.machineID = M.machineID
GO

--************************************************
-- View Name   : Machines_vw
-- Description : This Machine view is provided for SCCM integration
--************************************************
CREATE VIEW [dbo].[Machines_vw]
AS
    SELECT M.macaddress, 
           M.machinename, 
           M.domainname, 
           M.appcount, 
           M.servicepackminor, 
           M.smsguid -- do you really need OSID? There is already smsgmid

       FROM [dbo].[Machines] M
GO

--********************************************
--		COUNT RELATED VIEWS
--********************************************

--************************************************
-- View Name   : Application_Computer_Count
-- Description : This view provides computer count for applications.
--************************************************
CREATE VIEW [dbo].[Application_Computer_Count]
AS
    SELECT apps.appIdentity AS appIdentity,
           COUNT(machine.appIdentity) AS computerCount
    FROM [dbo].[Applications] apps
    LEFT JOIN 
    ( 
        SELECT DISTINCT mc.machineID, mc.osID, mc.appIdentity FROM [dbo].[App_Installed_On_Machine] mc
    ) machine            
        ON apps.appIdentity = machine.appIdentity
    GROUP BY apps.appIdentity

GO

--************************************************
-- View Name   : Group_Computer_Count
-- Description : This view provides computer count for applications_group.
--************************************************
CREATE VIEW [dbo].[Group_Computer_Count]
AS
  SELECT groupedApps.groupID as groupID, COUNT(DISTINCT distinctAppsMachines.machineID) as computerCount
  FROM
      (SELECT groups.groupID as groupID, apps.appIdentity as appIdentity
      FROM [dbo].[Application_Groups] groups
      JOIN [dbo].[Applications] apps
      ON groups.groupID = apps.memberOf) 
  groupedApps
  JOIN 
    (SELECT DISTINCT appsOnMachine.machineID as machineID, appsOnMachine.osID as osID, appsOnMachine.appIdentity as appIdentity
    FROM [dbo].[App_Installed_On_Machine] appsOnMachine
    )
  distinctAppsMachines
  ON groupedApps.appIdentity = distinctAppsMachines.appIdentity
  GROUP BY groupedApps.groupID

GO

--************************************************
-- View Name   : Group_Computer_Count
-- Description : This view provides computer count for applications_group.
--************************************************
CREATE VIEW [dbo].[Group_Application_Count]
AS
  SELECT groups.groupID as groupID, COUNT(apps.appIdentity) as appCount
      FROM [dbo].[Application_Groups] groups
      JOIN [dbo].[Applications] apps
      ON groups.groupID = apps.memberOf
	  GROUP BY groups.groupID 
GO

--************************************************
-- View Name   : App_Solution_Count
--************************************************

CREATE VIEW [dbo].[App_Solution_Counts]
AS
    SELECT app.appIdentity appIdentity,
           depOS.osID osID,
           COUNT(issues.issueID) solutionCount
    FROM [dbo].[Applications] app
    CROSS JOIN [dbo].[Deployment_OS] depOS
    LEFT OUTER JOIN 
    (
        SELECT	issues.appIdentity
                ,issues.osID
                ,issues.issueID
        FROM   [dbo].[Application_Issues_Os] issues 
        WHERE EXISTS
        (
            SELECT	'X' 
            FROM [dbo].[IssueSolution] issueSol
            JOIN [dbo].[Solution] sol 
                ON	sol.solutionID = issueSol.solutionID 
            WHERE	sol.deleteMarker = 0 AND issueSol.issueID = issues.issueID
        )
    ) issues
    ON (issues.appIdentity = app.appIdentity AND issues.osID = depOS.osID)
    GROUP BY app.appIdentity, depOS.osID 

GO

--************************************************
-- View Name   : App_Issues_Count
--************************************************

CREATE VIEW [dbo].[App_Issue_Counts]
AS
    SELECT aggregate.appIdentity,
           aggregate.osID,
           CASE
               WHEN aggregate.resolvedCount is NULL THEN 0
               ELSE aggregate.resolvedCount
           END AS resolvedIssuesCount,
           CASE
               WHEN aggregate.resolvedCount is NULL THEN 0
               ELSE aggregate.issueCount - aggregate.resolvedCount
           END AS activeIssuesCount
    FROM 
    ( 
        SELECT app.appIdentity appIdentity,
               depOS.osID osID,
               SUM(issues.isResolved) resolvedCount,
               COUNT(issues.issueID) issueCount
        FROM [dbo].[Applications] app
        CROSS JOIN [dbo].[Deployment_OS] depOS
        LEFT OUTER JOIN [dbo].[Application_Issues_Os] issues 
            ON (issues.appIdentity = app.appIdentity AND issues.osID = depOS.osID)
        GROUP BY app.appIdentity, depOS.osID 
    ) aggregate

GO

--************************************************
-- View Name   : Url_Issues_Count
--************************************************

CREATE VIEW [dbo].[Url_Issues_Count]
AS
    SELECT aggregate.urlID,
           aggregate.osID,
           CASE
               WHEN aggregate.resolvedCount is NULL THEN 0
               ELSE aggregate.resolvedCount
           END AS resolvedIssuesCount,
           CASE
               WHEN aggregate.resolvedCount is NULL THEN 0
               ELSE aggregate.issueCount - aggregate.resolvedCount
           END AS activeIssuesCount
    FROM 
    ( 
        SELECT url.ieceUrlId AS urlID,
               depOS.osID AS osID,
               SUM(issues.isResolved) resolvedCount,
               COUNT(issues.issueID) issueCount
        FROM [dbo].[File_Opens_Url] url
        CROSS JOIN [dbo].[Deployment_OS] depOS
        LEFT JOIN 
        (
            SELECT issueUrl.urlId, issueUrl.issueID, issueUrl.isResolved, issueOs.osID 
            FROM [dbo].[Issues_Associated_With_Url] issueUrl 
            JOIN [dbo].[Issues] issue 
                ON issue.issueID = issueUrl.issueID AND issue.deleteMarker = 0 
            JOIN [dbo].[Issue_Affects_OS] issueOS 
                ON issueOS.issueID = issue.issueID 
        ) issues
            ON issues.osID = depOs.osID AND issues.urlID = url.ieceUrlID
        GROUP BY url.ieceUrlId, depOS.osID 
    ) aggregate

GO

--************************************************
-- View Name   : Computer_Counts
--************************************************
CREATE VIEW [dbo].[Computer_Counts]
AS
    SELECT count1.osID osID,
            count1.macAddress macAddress,
            count1.machineID machineID,
            count1.appCount appCount,
            count2.deviceCount deviceCount
    FROM 
    ( 
        SELECT M.osID osID,
               M.machineID machineID,
               M.macAddress macAddress,
               COUNT(apps.appIdentity) appCount
        FROM [dbo].[Machines] M
        LEFT OUTER JOIN [dbo].[App_Installed_On_Machine] apps 
            ON (M.osID = apps.osID AND M.machineID = apps.machineID)
        GROUP BY M.osID, M.machineID, M.macAddress
    ) AS count1
    JOIN 
    ( 
        SELECT M.osID osID,
               M.machineID machineID,
               M.macAddress macAddress,
               COUNT(devices.deviceID) deviceCount
        FROM [dbo].[Machines] M
        LEFT OUTER JOIN [dbo].[PnPDevice_Machines] devices 
            ON (devices.machineID = M.machineID)
        GROUP BY M.osID, M.machineID, M.macAddress 
    ) AS count2 
        ON (count1.osID = count2.osID and count1.machineID = count2.machineID )

GO


--************************************************
-- View Name   : Computer_AppIssue_Counts
--************************************************
CREATE VIEW [dbo].[Computer_AppIssue_Counts]
AS
    SELECT M.osID osID,
           M.macAddress macAddress,
           M.machineID machineID,
           depOS.osID depOSID,
           COUNT(apps.appIdentity) appsWithIssues
    FROM [dbo].[Machines] M
    LEFT JOIN [dbo].[App_Installed_On_Machine] apps 
        ON (M.osID = apps.osID AND M.machineID = apps.machineID)
    CROSS JOIN [dbo].[Deployment_OS] depOS
    WHERE EXISTS
    (
        SELECT 'x' FROM [dbo].[Application_Issues_Os] AppIssueOs
        WHERE AppIssueOs.isResolved = 0 AND AppIssueOs.osID = depOs.osID AND AppIssueOs.appIdentity = apps.appIdentity
    )
    GROUP BY M.osID, M.machineID, M.macAddress, depOS.osID

GO

-- TODO: Have to change this Finally
-- Count = 37/ 43 views
-- Dropped Views: WS_Issue_Affects_OS, Application_Report_Complete_vw, App_Risk_Rating_Query, PnPID_OS_Architecture, iece_issue_data
-- Dropped Count = 5



-- Dropped Views : Application_Update_Computer_Count,
/*--------------------------------------------------------------------------------
  
  <Copyright file="CreateTriggers.sql" company="Microsoft">
    Copyright (c) Microsoft Corporation.  All rights reserved.
  </Copyright>

  <Comments>
    
    The following is the new implementation of the Application
    Compatability Toolkit Client Database. This is the creation
    script that is used to create triggers on the required base
    tables. This script is organized into the following sections:
    
    -	OS Related Tables 
    -   Type definitions
    -	Common tables
    -	Machine Related Tables
    -	Device Related Tables
    -	Application Related Tables
    -	DCP Related Tables
    -	Sync Related Tables
    -	Website Related Tables
    -	Tagging Related Tables
    -	Categories and Subcategories Related Tables
    -	Issues and Solutions Related Tables
    -	Deployment Status Related Tables
    -	UI Report Related Tables
    -	Mitigation Related Tables

  </Comments>
 
  <Version> 6.0.0.0 </Version>
  
  <@owner>
         padmav
  </@owner>
  
--------------------------------------------------------------------------------*/

--*******************************************
-- OS RELATED TABLES
--*******************************************

--******************************************
-- Trigger Name  : Deployment_OS_Trg
-- Description   : Trigger when adding a new OS.
--*********************************************

CREATE TRIGGER [dbo].[Deployment_OS_Trg] ON [dbo].[Deployment_OS]
FOR INSERT
AS
    --Insert the new deployment OS into enabled OSes table
    INSERT INTO [dbo].[Deployment_Enabled_OSes]
    (
        [osID],
        [enabled],
        [lastSyncTime]
    )
    SELECT inserted.osID,
           1,
           '1/1/1900'
    FROM inserted
            
    -- Insert new Application Report rows for the given OS
    INSERT INTO [dbo].[Application_Report]
    (
        [osID],
        [appIdentity]
    )
    SELECT inserted.osID,
           App.appIdentity
    FROM inserted
    CROSS JOIN [dbo].[Applications] App

    -- Insert new Group Report rows for the given OS
    INSERT INTO [dbo].[ApplicationGroup_Report]
    (
        [osID],
        [groupID]
    )
    SELECT inserted.osID,
           AppGroup.groupID
    FROM inserted
    CROSS JOIN [dbo].[Application_Groups] AppGroup

    -- Insert new Computer Report rows for the given OS
    INSERT INTO [dbo].[Computer_Report]
    (
        [osID],
        [depOsID],
        [machineID],
        [macAddress]
    )
    SELECT Mac.osID,
           inserted.osID,
           Mac.machineID,
           Mac.macAddress
    FROM inserted
    CROSS JOIN [dbo].[Machines] Mac

    INSERT INTO [dbo].[Issue_Affects_OS]
    (
        [issueID],
        [issueType],
        [osID]
    )
    SELECT issueID,
           issueType,
           newOsID
    FROM [dbo].[Issue_Affects_OS] Issue
    JOIN
    (
        SELECT OS.osID oldOsID,
               newOsID
        FROM [dbo].[OS],
        (
            SELECT inserted.osID newOsID,
                   majorVersion,
                   minorVersion
            FROM [dbo].[OS]
            JOIN inserted 
            ON inserted.osID = OS.osID
        ) DOS
        WHERE OS.majorVersion = DOS.majorVersion AND OS.minorVersion = DOS.minorVersion
    ) DOS ON DOS.oldOsID = Issue.osID
    WHERE Issue.issueType <> 'Application'

GO

--*******************************************
-- MACHINE RELATED TABLES
--*******************************************

--******************************************
-- Trigger Name  : Machines_Insert_Trg
-- Description   : Adds rows to Computer_Report
--*********************************************

CREATE TRIGGER [dbo].[Machines_Insert_Trg] ON [dbo].[Machines]
FOR INSERT
AS
    BEGIN

        --Initialize Computer Report
        INSERT INTO [dbo].[Computer_Report] (osID, depOsID, machineID, macAddress)
        SELECT  inserted.osID,
                depOs.osID,
                inserted.machineID,
                inserted.macAddress
        FROM inserted 
        CROSS JOIN [dbo].[Deployment_Os] depOS
    END

GO

--*******************************************
-- DEVICE RELATED TABLES
--*******************************************

--******************************************
-- Trigger Name  : Device_Report_vw_UpdateTrigger
-- Description   : Updates pnp device priority.
--*********************************************

CREATE TRIGGER [dbo].[Device_Report_vw_UpdateTrigger] ON [dbo].[Device_Report_vw] 
INSTEAD OF UPDATE
AS
    BEGIN
        IF(UPDATE(priority))
        BEGIN

            UPDATE device
            SET device.priority = ins.priority
            FROM inserted ins
            JOIN [dbo].[PnpDevice] device
            ON ins.deviceID = device.deviceID
        END
    END
GO

--*******************************************
-- APPLICATION RELATED TABLES
--*******************************************

--******************************************
-- Trigger Name  : Application_Insert_Trg
-- Description   : Adds an entry to Application_Report
-- for rows inserted in Application.
--*********************************************

CREATE TRIGGER [dbo].[Application_Insert_Trg] ON [dbo].[Applications]
FOR INSERT
AS
    BEGIN
    -- Insert new Application Report rows for the given Apps
    
        INSERT INTO [dbo].[Application_Report] (osID, appIdentity)
        SELECT  dpOS.osID
                ,inserted.appIdentity
        FROM    inserted
        CROSS JOIN [dbo].[Deployment_OS] dpOS
    END

GO

--******************************************
-- Trigger Name  : ApplicationGroup_Insert_Trg
-- Description   : Adds an entry to ApplicationGroup_Report
-- for rows inserted in Application_Groups.
--*********************************************

CREATE TRIGGER [dbo].[ApplicationGroup_Insert_Trg] ON [dbo].[Application_Groups]
FOR INSERT
AS
    BEGIN
    -- Insert new Application Report rows for the given Apps
    
        INSERT INTO [dbo].[ApplicationGroup_Report] (osID, groupID)
        SELECT  dpOS.osID
                ,inserted.groupID
        FROM    inserted
        CROSS JOIN [dbo].[Deployment_OS] dpOS
    END

GO

--******************************************
-- Trigger Name  : Application_Report_Update_Trg
-- Description   : Updates Application_Votes based on
-- new entries in Application_Report.
--*********************************************
CREATE TRIGGER [dbo].[Application_Report_Update_Trg] ON [dbo].[Application_Report]
FOR UPDATE
AS
    BEGIN
        IF (@@ROWCOUNT = 0) RETURN
    
        DECLARE @iDate datetime
        SET @iDate = GetUtcDate()    

        -- update votes whose source is = 1 user supplied vote
        IF (UPDATE(myRating32)) 
        BEGIN
            UPDATE v
            SET [worksVote] = CASE WHEN i.myRating32 = 1 THEN 1
                                ELSE 0
                              END,
                [doesNotWorkVote] = CASE WHEN i.myRating32 = 3 THEN 1
                                      ELSE 0
                                    END,
                [partiallyWorksVote] = CASE WHEN i.myRating32 = 2 THEN 1
                                         ELSE 0
                                       END
            FROM [dbo].[Application_Votes] v
            JOIN inserted i 
            ON i.appIdentity = v.appIdentity
            AND i.osID = v.osID 
            WHERE v.voteSource = 1  AND  v.architecture = 0           

            --
            -- insert new votes from user supplied assestment 32 
            --
        
            INSERT INTO [dbo].[Application_Votes]
            (
                osID
                ,appIdentity
                ,architecture
                ,voteSource
                ,doesNotWorkVote
                ,partiallyWorksVote
                ,worksVote
                ,publishedDate
                ,lastSyncedVote
            )
            SELECT  i.osID
                   ,i.appIdentity
                   ,0
                   ,1
                   ,CASE WHEN i.myRating32 = 3 THEN 1
                         ELSE 0
                    END
                   ,CASE WHEN i.myRating32 = 2 THEN 1
                         ELSE 0
                    END
                   ,CASE WHEN i.myRating32 = 1 THEN 1
                         ELSE 0
                    END
                   ,@iDate
                   ,0
            FROM inserted as i 
            WHERE NOT EXISTS 
            (
                SELECT 'x'
                FROM [dbo].[Application_Votes] as v
                WHERE v.osID = i.osID AND v.appIdentity = i.appIdentity
                AND v.architecture = 0
                AND v.voteSource = 1
            )
        END

        IF (UPDATE(myRating64)) 
        BEGIN
            UPDATE v
            SET [worksVote] = CASE WHEN i.myRating64 = 1 THEN 1
                                ELSE 0
                              END,
                [doesNotWorkVote] = CASE WHEN i.myRating64 = 3 THEN 1
                                      ELSE 0
                                    END,
                [partiallyWorksVote] = CASE WHEN i.myRating64 = 2 THEN 1
                                         ELSE 0
                                       END
            FROM [dbo].[Application_Votes] v
            JOIN inserted i 
            ON i.appIdentity = v.appIdentity AND i.osID = v.osID 
            WHERE v.voteSource = 1  AND  v.architecture = 1 --x64           

            --
            -- insert new votes from user supplied assestment 64 
            --
            INSERT INTO [dbo].[Application_Votes]
            (
                osID
                ,appIdentity
                ,architecture
                ,voteSource
                ,doesNotWorkVote
                ,partiallyWorksVote
                ,worksVote
                ,publishedDate
                ,lastSyncedVote
            )
            SELECT  i.osID
                   ,i.appIdentity
                   ,1
                   ,1
                   ,CASE WHEN i.myRating64 = 3 THEN 1
                         ELSE 0
                    END
                   ,CASE WHEN i.myRating64 = 2 THEN 1
                         ELSE 0
                    END
                   ,CASE WHEN i.myRating64 = 1 THEN 1
                        ELSE 0
                    END
                   ,@iDate
                   ,0
            FROM inserted as i 
            WHERE NOT EXISTS 
            (
                SELECT 'x'
                FROM [dbo].[Application_Votes] as v
                WHERE v.osID = i.osID  AND v.appIdentity = i.appIdentity
                AND v.architecture = 1
                AND v.voteSource = 1
            )
        END

        IF (UPDATE(activeIssueCount)) 
        BEGIN
            UPDATE g
            SET g.[activeIssueCount] = g.[activeIssueCount] + i.[activeIssueCount] - d.[activeIssueCount]
            FROM [dbo].[Applications] app
            JOIN inserted i 
            ON app.appIdentity = i.appIdentity
            JOIN deleted d
            ON app.appIdentity = d.appIdentity AND i.osID = d.osID
            JOIN [dbo].[ApplicationGroup_Report] g
            ON g.groupID = app.memberOf AND i.osID = g.osID 
        END

        IF (UPDATE(resolvedIssueCount)) 
        BEGIN
            UPDATE g
            SET g.[resolvedIssueCount] = g.[resolvedIssueCount] + i.[resolvedIssueCount] - d.[resolvedIssueCount]
            FROM [dbo].[Applications] app
            JOIN inserted i 
            ON app.appIdentity = i.appIdentity
            JOIN deleted d
            ON app.appIdentity = d.appIdentity AND i.osID = d.osID
            JOIN [dbo].[ApplicationGroup_Report] g
            ON g.groupID = app.memberOf AND i.osID = g.osID 
        END
    
    END
GO


--*******************************************
-- ISSUES AND SOLUTIONS RELATED TABLES
--*******************************************

--******************************************
-- Trigger Name  : Issue_Affects_OS_Trg
-- Description   : Trigger to avoid duplicates in
-- Issue_Affects_OS table.
--*********************************************

CREATE TRIGGER [dbo].[Issue_Affects_OS_Trg] ON [dbo].[Issue_Affects_OS]
INSTEAD OF INSERT
AS
    IF (@@ROWCOUNT = 0) RETURN

    INSERT INTO [dbo].[Issue_Affects_OS]
    SELECT *
    FROM inserted i
    WHERE NOT EXISTS
    (
        SELECT 'x' FROM [dbo].[Issue_Affects_OS] o
        WHERE o.issueID = i.issueID AND o.issueType = i.issueType AND o.osID = i.osID
    )

    UPDATE Iss
    SET Iss.[publishedDate] = GetUTCDate()
    FROM [dbo].[Issues] Iss
    JOIN inserted 
    ON (inserted.[issueID] = Iss.[issueID] AND inserted.[issueType] = Iss.[issueType])
    WHERE Iss.[provider] = N'My Issues'

GO

--******************************************
-- Trigger Name  : IssueSolution_Trg
-- Description   : Trigger to avoid duplicates
-- in IssueSolution table, and to update solutionType.
--*********************************************

CREATE TRIGGER [dbo].[IssueSolution_Trg] ON [dbo].[IssueSolution]
INSTEAD OF INSERT
AS
    BEGIN
    
        -- Insert the new IssueSolution records if any
        INSERT INTO [dbo].[IssueSolution]
        SELECT *
        FROM inserted i
        WHERE NOT EXISTS
        (
            SELECT 'x' FROM [dbo].[IssueSolution] o
            WHERE o.issueID = i.issueID AND o.issueType = i.issueType AND o.solutionID = i.solutionID
        )

        -- Set the solution type of the new record
        UPDATE isol
        SET isol.solutionType = Sol.solutionType
        FROM [dbo].[IssueSolution] isol
        JOIN inserted i 
        ON (i.issueID = isol.issueID AND i.issueType = isol.issueType AND i.solutionID = isol.solutionID)
        JOIN [dbo].[Solution] Sol 
        ON (Sol.solutionID = isol.solutionID)
    END

GO

--******************************************
-- Trigger Name  : Solution_Update_Trg
-- Description   : Trigger to update solutionType
-- in IssueSolution when updated in Solution.
--*********************************************
CREATE TRIGGER [dbo].[Solution_Update_Trg] ON [dbo].[Solution]
FOR UPDATE
AS
    BEGIN
        IF UPDATE(solutionType)
        BEGIN
            UPDATE isol
            SET isol.solutionType = i.solutionType
            FROM inserted i
            JOIN [dbo].[IssueSolution] isol 
            ON (isol.solutionID = i.solutionID)
        END
    END
GO

--******************************************
-- Trigger Name  : App_Issue_Status_Trg
--*********************************************

CREATE TRIGGER [dbo].[App_Issue_Status_Trg] ON [dbo].[Issues_Associated_With_App]
FOR UPDATE
AS
    BEGIN    
        IF( UPDATE(isResolved) )
        BEGIN
            UPDATE	app	
            SET		app.activeIssueCount = temp.activeIssuesCount,
                    app.resolvedIssueCount = temp.resolvedIssuesCount
            FROM [dbo].[Application_Report] app
            JOIN [dbo].[App_Issue_Counts] temp  
                ON	app.appIdentity = temp.appIdentity AND app.osID = temp.osID
            JOIN [dbo].[Issues_Associated_With_App] issueApp 
                ON	 issueApp.appIdentity = temp.appIdentity
            JOIN [dbo].[Issue_Affects_OS] issueOS
                ON	issueOS.issueID = issueApp.issueID AND issueOs.osID = temp.osID
            JOIN inserted 
                ON inserted.issueID = issueApp.issueID AND inserted.appIdentity = issueApp.appIdentity
        END
    END
GO

--******************************************
-- Trigger Name  : App_Issue_Status_Trg
--*********************************************

CREATE TRIGGER [dbo].[Url_Issue_Status_Trg] ON [dbo].[Issues_Associated_With_Url]
FOR UPDATE
AS
    BEGIN    
        IF( UPDATE(isResolved) )
        BEGIN		
            UPDATE	url
            SET		url.activeIssueCount = temp.activeIssuesCount,
                    url.resolvedIssueCount = temp.resolvedIssuesCount
            FROM [dbo].[Url_Report] url
            JOIN [dbo].[Url_Issues_Count] temp
                ON url.ieceUrlID = temp.UrlID AND url.OsID = temp.osID
            JOIN [dbo].[Issues_Associated_With_Url] issueUrl 
                ON	 issueUrl.urlID = temp.urlID
            JOIN [dbo].[Issue_Affects_OS] issueOS
                ON	issueOS.issueID = issueUrl.issueID AND issueOs.osID = temp.osID		
            JOIN inserted 
                ON inserted.issueID = issueUrl.issueID AND inserted.urlID = issueUrl.urlID
        END
    END
GO

--*******************************************
-- WEBSITE RELATED TABLES
--*******************************************

--******************************************
-- Trigger Name  : File_Opens_Url_Dup_Trg
-- Description   : Add rows to url_report.
--*********************************************

CREATE TRIGGER [dbo].[File_Opens_Url_Dup_Trg] ON [dbo].[File_Opens_Url]
FOR INSERT
AS
    BEGIN
        -- USE Url_Report
        -- Intialization of the Url_Report table
        INSERT INTO [dbo].[Url_Report] (osID,ieceUrlId)
        SELECT o.osID,i.ieceUrlId
        FROM inserted i
        CROSS JOIN [dbo].[Deployment_OS] o 
        WHERE NOT EXISTS
        (
            SELECT u.ieceUrlId,
                   u.osID
            FROM [dbo].[Url_Report] u
            WHERE u.ieceUrlId=i.ieceUrlId AND u.osID = o.osID
        ) 
    END

GO
-- Count = 18/ 24 views
-- Dropped Triggers: App_Risk_Rating_Duplicate_Trg, App_Risk_Rating_Report_Trg, Logical_Machine_Dflt_Pri_Trg 
--                   WS_Issue_Affects_OS_Insert_Trg, WS_Issue_Affects_OS_Update_Trg, Issue_Affects_OS_Delete_Trg
-- Dropped Count = 6


/*--------------------------------------------------------------------------------
  
  <Copyright file="CreateStoredProcs.sql" company="Microsoft">
    Copyright (c) Microsoft Corporation.  All rights reserved.
  </Copyright>

  <Comments>
    
    The following is the new implementation of the Application
    Compatability Toolkit Client Database. This is the creation
    script that is used to create stored procs on the required base
    tables. This script is organized into the following sections:
    
    -   OS Related Tables 
    -   Type definitions
    -   Common tables
    -   Machine Related Tables
    -   Device Related Tables
    -   Application Related Tables
    -   DCP Related Tables
    -   Sync Related Tables
    -   Website Related Tables
    -   Tagging Related Tables
    -   Categories and Subcategories Related Tables
    -   Issues and Solutions Related Tables
    -   Deployment Status Related Tables
    -   UI Report Related Tables
    -   Mitigation Related Tables

  </Comments>
 
  <Version> 6.0.0.0 </Version>
  
  <@owner>
         padmav
  </@owner>
  
--------------------------------------------------------------------------------*/

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 

GO

--****************************************************
-- TRACING RELATED STORED PROCEDURES
--****************************************************

CREATE PROCEDURE [dbo].[Trace_sp]
(
    @traceLevel int,
    @msg nvarchar(260)
)  
/*
<summary>Adds a message to the trace table, if the current TraceLevel is at or above the given value.</summary> 
<parameters> 
    <param required="yes" description="If the database TraceLevel value is at or above this parameter value, this
    call to Trace_sp will be recorded in the Trace_tbl."/> 
    <param required="yes" description="The message to be recorded."/>  
</parameters> 
<returns> 
    <return value="n" description="A binary(4) equating to the given hex string."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
    END

    IF dbo.TraceLevel() >= @traceLevel
    BEGIN
        INSERT INTO dbo.Trace_tbl(msg)
        VALUES(@msg)
    END
GO

CREATE PROCEDURE [dbo].[SetTraceLevel_sp]
(
    @TraceLevel int
)
/*
<summary>Sets the trace level for the database.  Any trace statemnets specifying the given level or above will be recorded in the Trace_tbl.
NOTE that the caller must be in the sysadmin or db_owner role for this procedure to succeed.</summary> 
<parameters> 
    <param required="yes" description="The level to which tracing should be set."/> 
</parameters> 
<returns> 
    <return value="0" description="The trace level was successfully set."/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
    END

    DECLARE @err int,
            @traceMsg nvarchar(100),
            @errMsg nvarchar(260)

    SET @traceMsg = N'SetTraceLevel - Begin, new level = ' + cast(@TraceLevel AS nvarchar)
    EXEC Trace_sp @traceLevel = 1, @msg = @traceMsg

    IF @TraceLevel < 1 or @TraceLevel > 3
    BEGIN    
        SELECT @errMsg = 'SetTraceLevel_sp' + ' ' + 'TraceLevel out of bounds (Only 1-3) Valid'
        GOTO ErrorHandler
    END

    DECLARE @updateSql nvarchar(100)

    -- Dynamically Create the function to return the new Trace Level
    SET @updateSql = '
        ALTER  FUNCTION [dbo].[TraceLevel] ()
        RETURNS int AS  
            BEGIN 
                RETURN(' + cast(@TraceLevel AS nvarchar) + ') 
            END
        '

    -- Execute the alter sql script
    EXEC sp_executesql @updateSql

    SET @err = @@error
    IF @err != 0
    BEGIN
        SELECT @errMsg = 'SetTraceLevel_sp' + ' ' + 'Update of SQL Script to alter TraceLevel failed'
        GOTO ErrorHandler    
    END

    EXEC Trace_sp @traceLevel = 1, @msg = N'SetTraceLevel - Complete'
    RETURN @@error

    --handle errors
    DECLARE @errNum int
ErrorHandler:
    RAISERROR(@errMsg,18,127)

GO

--****************************************************
-- LPS RELATED STORED PROCEDURES
--****************************************************

CREATE PROCEDURE [dbo].[LogPostProcessing_sp]
/*
<summary>
Wrapper procedure which implements calls to all log post processing routines.
</summary> 
<parameters />  
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON

        BEGIN TRAN 

            DECLARE @err int
            DECLARE @newPropMatch datetime 			-- This will be the new matching time.
            DECLARE @curPropMatch datetime 			-- This isthe current matching time.

            DECLARE @errMsg nvarchar(260)
            DECLARE @dDelta int             --  Delta count dynamic app properties.
            DECLARE @dDeltaOrphan int       --  Delta count Orphan issues/General Feedback.
            DECLARE @dDeltaAppFeedback int  --  Delta count Application Feedback.

            EXEC Trace_sp @traceLevel = 2, @msg = N'[LogPostProcessing] - Started'

            -- Keep track of the latest match time
            SELECT @newPropMatch = getdate();

            -- Create the delta table
            SELECT @dDelta = (SELECT COUNT(*) FROM [dbo].[TimeLineEvents]
                               WHERE appIdentity is not NULL AND timeLineCategory = 'Issue' AND lastUpdatedDateTime > 
                                (SELECT MAX(Last_New_Prop_Issue_Match) FROM [dbo].[CLIENT_STATE_DETAILS]));	

                        -- Create the delta table
            SELECT @dDeltaOrphan = (SELECT COUNT(*) FROM [dbo].[TimeLineEvents]
                                     WHERE appIdentity is NULL 
                                     AND (timeLineCategory = 'Issue' OR timeLineEventType = 'GeneralFeedback')
                                     AND lastUpdatedDateTime > (SELECT MAX(Last_New_Prop_Issue_Match) FROM [dbo].[CLIENT_STATE_DETAILS]));	

                        -- Create the delta Application Feedback table
            SELECT @dDeltaAppFeedback = (SELECT COUNT(*) FROM [dbo].[TimeLineEvents]
                                         WHERE timeLineEventType = 'ApplicationFeedback' 
                                         AND lastUpdatedDateTime > (SELECT MAX(Last_New_Prop_Issue_Match) FROM [dbo].[CLIENT_STATE_DETAILS]));	

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[LogPostProcessing] - Delta computed'

            -- Associate all new Issues against inventoried apps.
            IF (@dDelta > 0) 
            BEGIN
                EXEC [dbo].[AssociateIssueWithApps_sp]
    
                SET @err = @@error
                IF @err != 0
                BEGIN	
                    SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Error in associating issues with apps'
                    GOTO ErrorHandler
                END

                -- Get the current matching time.	
                SELECT @curPropMatch = Last_State_Ind_Match FROM [dbo].[CLIENT_STATE_DETAILS]
    
            END

            -- Only match if we actually have a set of new dynamic issues to match on.
            IF (@dDelta > 0)
            BEGIN
                -- Fill the file_Opens_Url table from the newly found iece issues
                EXEC [dbo].[FillUrlFromIeceIssues_sp]

                SET @err = @@error
                IF @err != 0
                BEGIN	
                    SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Failed to fill the url information from iece issues'
                    GOTO ErrorHandler
                END
            END

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[LogPostProcessing] - FillUrlFromIeceIssues  -- Finished'

            -- Now associate the iece issue to the url in issues_associated_with_url table.
            IF (@dDelta > 0)
            BEGIN
                -- Fill the file_Opens_Url table from the newly found iece issues
                EXEC [dbo].[AssociateIeceIssuesWithUrl_sp]

                SET @err = @@error
                IF @err != 0
                BEGIN	
                    SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Failed to associate iece issues with url'
                    GOTO ErrorHandler
                END
            END

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[LogPostProcessing] - AssociateIeceIssuesWithUrl  -- Finished'

            -- Insert Orphan issues and General feedback into GeneralFeedback_Report table.
            IF (@dDeltaOrphan > 0)
            BEGIN
                EXEC [dbo].[PopulateGeneralFeedbackReport_sp]

                SET @err = @@error
                IF @err != 0
                BEGIN	
                    SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Failed to populate general feedback report'
                    GOTO ErrorHandler
                END
            END

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[LogPostProcessing] - PopulateGeneralFeedbackReport_sp  -- Finished'

            -- Merge RAP User Ratings into the Application_Report.
            IF (@dDeltaAppFeedback > 0)
            BEGIN
                EXEC [dbo].[MergeRapUserRatings_sp]

                SET @err = @@error
                IF @err != 0
                BEGIN	
                    SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Failed to merge RAP user ratings in Application Report'
                    GOTO ErrorHandler
                END

                EXEC [dbo].[ComputeGroupUserRatings_sp]

                SET @err = @@error
                IF @err != 0
                BEGIN	
                    SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Failed to merge user ratings in Application groups Report'
                    GOTO ErrorHandler
                END
            END

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[LogPostProcessing] - MergeRapUserRatings_sp  -- Finished'


            EXEC [dbo].[GenUIReports_sp]

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = 'LogPostProcessing' + ' ' + 'Could not update client state details'
                GOTO ErrorHandler
            END

            -- Update Client State Details
            UPDATE [dbo].[Client_State_Details]
            SET Last_New_Prop_Issue_Match = @newPropMatch
            WHERE ID=N'1'

            EXEC Trace_sp @traceLevel = 2, @msg = N'[LogPostProcessing] - Ended'

            --EXEC disable_trace
        COMMIT TRAN

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'LogPostProcessing - Transaction aborted'
            RETURN(@@error)
        END
    END
GO

--****************************************************
-- SYNC RELATED STORED PROCEDURES
--****************************************************

CREATE PROCEDURE [dbo].[SyncPostProcessing_sp]

/*
<summary>
    Wrapper procedure which implements
    calls to all Sync post processing routines.
    Updated to use R4 WS
</summary> 
<parameters /> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2, @msg = N'[SyncPostProcessing_sp] - Started'

        BEGIN TRAN
            -- Transfer all solutions to issue and solution related tables
            EXEC [dbo].[AddWebServiceSolutions_sp]

            IF @@error != 0
                GOTO errorHandler
    
            -- Transfer all application ratings and community votes data to application report table.
            EXEC [dbo].[MergeRatingsAndVotes_sp]

            IF @@error != 0
                GOTO errorHandler

            -- Compute group ratings.
            EXEC [dbo].[ComputeGroupRatings_sp]

            IF @@error != 0
                GOTO errorHandler

            -- Generate Device Reports and truncate device rating table.
            EXEC [dbo].[GenDeviceReports_sp]

            IF @@error != 0
                GOTO errorHandler

            -- Generate counts for reports
            EXEC [dbo].[UpdateIssueCounts_sp]

            IF @@error != 0
                GOTO errorHandler

            -- Remove deleted issues
            EXEC [dbo].[RemoveDeletedIssues_sp]

            IF @@error != 0
                GOTO errorHandler

            -- Remove deleted solutions
            EXEC [dbo].[RemoveDeletedSolutions_sp]

            IF @@error != 0
                GOTO errorHandler

            -- Update Client State Details
            UPDATE [dbo].[Client_State_Details]
            SET Last_State_Ind_Match = getdate(),
                Last_Client_Sync_Time = getdate(),
                Last_Server_Sync_Time = getdate()
            WHERE ID=N'1'

            IF @@error != 0
                GOTO errorHandler

            -- update last sync for user application votes
            UPDATE v
            SET [lastSyncedVote] = CASE WHEN v.worksVote = 1 THEN 1
                                        WHEN v.partiallyWorksVote = 1 THEN 2
                                        WHEN v.doesNotWorkVote = 1 THEN 3
                                        ELSE 0
                                    END 
            FROM [dbo].[Application_Votes] v
            WHERE v.voteSource = 1  AND v.lastSyncedVote = 0

            IF @@error != 0
                GOTO errorHandler

            EXEC Trace_sp @traceLevel = 2, @msg = N'[SyncPostProcessing_sp] - Ended'

            SELECT *
            FROM [dbo].[Application_Votes] v
            WHERE v.voteSource = 1  

        COMMIT TRAN
--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[SyncPostProcessing_sp] - Transaction aborted'
            RETURN(@@error)
        END
    END
GO

CREATE PROCEDURE [dbo].[GetDatabaseGUID_sp]
(
    @dbGUID int OUTPUT
)
/*
<summary>
    Stored procedure to return the database GUID.
    USed by R4 web service.
</summary> 
<parameter /> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON

        SELECT TOP 1 @dbGUID = DBGUID
        FROM [dbo].[Version]
        ORDER BY VersionCreatedDate DESC

        RETURN @@ERROR
    END
GO

--*****************************************************************
-- ISSUES RELATED STORED PROCEDURES CALLED IN LogPostProcessing_sp
--*****************************************************************

CREATE PROCEDURE [dbo].[AssociateIssueWithApps_sp]
/*
<summary>
    Associate the new issues with the inventoried apps.
    Called by LogPostProcessing_sp.
</summary> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2, @msg = N'[AssociateIssueWithApps_sp] - Started'

        /* Define all Constants */
        DECLARE @err int
        DECLARE @errMsg nvarchar(260)
        DECLARE @last_sync_time datetime
        SET @err = 0
        EXEC Trace_sp @traceLevel = 3, @msg = N'--[AssociateIssueWithApps_sp] - Issues_Associated_With_App'
        -- Obtain last sync time 
        SELECT @last_sync_time = MAX(Last_New_Prop_Issue_Match) from [dbo].[CLIENT_STATE_DETAILS]
        BEGIN TRAN
            -- Using the appidentity in the timeline table, populate the Issues_associated_with_app
            INSERT INTO [dbo].[Issues_Associated_With_App](issueID,issueType,appIdentity)
            SELECT DISTINCT issueID,
                            issueType,
                            apps.appIdentity
            FROM [dbo].[TimeLineEvents] timeLine,[dbo].[Applications] apps
            WHERE
            ( 
                timeline.appIdentity = apps.appIdentity
                AND timeline.timeLineCategory = 'Issue'
                AND timeline.LastUpdatedDateTime > @last_sync_time 
            ) 
            EXCEPT
            (
                SELECT issueID,issueType,appIdentity from [dbo].[Issues_Associated_With_App]
            )

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[AssociateIssueWithApps_sp] - Issues_Associated_With_App'
            SET @err = @@error
            IF @err != 0
            BEGIN    
                SELECT @errMsg = 'AssociateIssueWithApps' + ' ' + 'Could not associate new issues with apps'
                GOTO ErrorHandler
            END
        COMMIT TRAN
    
        EXEC Trace_sp @traceLevel = 2, @msg = N'[AssociateIssueWithApps_sp] - Finished'
        --Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'AssociateIssueWithApps - Transaction aborted'
            RETURN(@@error)
        END
    END
GO

CREATE PROCEDURE [dbo].[FillUrlFromIeceIssues_sp]
/*
<summary>
    Fill the file_opens_url table from the new iece issues.
    Called by LogPostProcessing_sp.
</summary> 
<parameters> 
</parameters> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET nocount on
        EXEC Trace_sp @traceLevel = 2, @msg = N'[FillUrlFromIeceIssues_sp] - Started'

        /* Define all Constants */
        DECLARE @err int
        DECLARE @errMsg nvarchar(260)
        DECLARE @last_sync_time datetime
        --Init and declare tracing information 
        SET @err = 0
        EXEC Trace_sp @traceLevel = 3, @msg = N'--[FillUrlFromIeceIssues_sp] - FillUrlFromIeceIssues_sp'
        -- Obtain last sync time 
        SELECT @last_sync_time = MAX(Last_New_Prop_Issue_Match) from [dbo].[CLIENT_STATE_DETAILS]
        BEGIN TRAN
            -- Insert all Apps which match against any new Application Issues 
            INSERT INTO [dbo].[File_Opens_Url](ieceUrlPath)
            SELECT DISTINCT timeline.ieceUrlPath
            FROM [dbo].[TimeLineEvents] timeLine
            WHERE timeline.ieceUrlPath is not null
            AND timeline.LastUpdatedDateTime > @last_sync_time 
            EXCEPT
            (
                SELECT ieceUrlPath from [dbo].[File_Opens_Url]
            )

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[FillUrlFromIeceIssues_sp] - FillUrlFromIeceIssues_sp'
            SET @err = @@error
            IF @err != 0
            BEGIN    
                SELECT @errMsg = 'FillUrlFromIeceIssues' + ' ' + 'Could not fill file_opens_url table from Iece issues'
                GOTO ErrorHandler
            END
        COMMIT TRAN

        EXEC Trace_sp @traceLevel = 2, @msg = N'[FillUrlFromIeceIssues_sp] - Complete'

ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[FillUrlFromIeceIssues_sp] - Transaction aborted'
            RETURN(@@error)
        END

    END
GO

CREATE PROCEDURE [dbo].[AssociateIeceIssuesWithUrl_sp]
/*
<summary>
    Associate new iece issues with Url in the issues_associated_with_url table.
    Called by LogPostProcessing_sp.
</summary> 
<parameters> 
</parameters> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET nocount on
        EXEC Trace_sp @traceLevel = 2, @msg = N'[AssociateIeceIssuesWithUrl_sp] - Started'

        /* Define all Constants */
        DECLARE @err int
        DECLARE @errMsg nvarchar(260)
        DECLARE @last_sync_time datetime
        --Init and declare tracing information 
        SET @err = 0
        EXEC Trace_sp @traceLevel = 3, @msg = N'--[AssociateIeceIssuesWithUrl_sp] - AssociateIeceIssuesWithUrl_sp'
        -- Obtain last sync time 
        SELECT @last_sync_time = MAX(Last_New_Prop_Issue_Match) from [dbo].[CLIENT_STATE_DETAILS]
        BEGIN TRAN
            -- Insert all Apps which match against any new Application Issues 
            INSERT INTO [dbo].[Issues_Associated_With_Url](urlID,issueID,issueType)
            SELECT DISTINCT url.ieceUrlId, timeline.issueID, timeline.issueType
            FROM [dbo].[TimeLineEvents] timeline, [dbo].[File_Opens_Url] url
            WHERE url.ieceUrlPath = timeline.ieceUrlPath
            AND timeline.LastUpdatedDateTime > @last_sync_time 
            EXCEPT
            (
                SELECT urlID,issueID,issueType from [dbo].[Issues_Associated_With_Url]
            )

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[AssociateIeceIssuesWithUrl_sp] - AssociateIeceIssuesWithUrl_sp'
            SET @err = @@error
            IF @err != 0
            BEGIN    
                SELECT @errMsg = 'AssociateIeceIssuesWithUrl' + ' ' + 'Could not associate Iece issues with url'
                GOTO ErrorHandler
            END
        COMMIT TRAN

        EXEC Trace_sp @traceLevel = 2, @msg = N'[AssociateIeceIssuesWithUrl_sp] - Complete'

ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[AssociateIeceIssuesWithUrl_sp] - Transaction aborted'
            RETURN(@@error)
        END

    END
GO

CREATE PROCEDURE [dbo].[PopulateGeneralFeedbackReport_sp]
/*
<summary>
    Populate GeneralFeedback_Report with the newly inserted general feedback and orphan issues.
    Called by LogPostProcessing_sp.
</summary> 
<parameters> 
</parameters> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET nocount on
        EXEC Trace_sp @traceLevel = 2, @msg = N'[PopulateGeneralFeedbackReport_sp] - Started'

        /* Define all Constants */
        DECLARE @err int
        DECLARE @errMsg nvarchar(260)
        DECLARE @last_sync_time datetime

        DECLARE @pcaTitle nvarchar(100)
        DECLARE @pcaDetail nvarchar(200)
        DECLARE @compatibilityLayer nvarchar(100)
        DECLARE @exePath nvarchar(100)
        DECLARE @appCrashTitle nvarchar(100)
        DECLARE @appHangTitle nvarchar(100)
        DECLARE @appCrashDetail nvarchar(100)
        DECLARE @appHangDetail nvarchar(100)
        DECLARE @shimTitle nvarchar(100)
        DECLARE @shimDetail nvarchar(100)
        DECLARE @shimFixInformation nvarchar(100)
        DECLARE @newline nvarchar(5)

        SELECT @newline = CHAR(13) + CHAR(10)
        SELECT @pcaTitle = 'Pca issue detected in '
        SELECT @pcaDetail = 'The Program Compatibility Assistant was invoked to correct a compatibility problem.'
        SELECT @compatibilityLayer = @newline + 'Compatibility Layer: '
        SELECT @exePath = @newline + 'Executable Path: '
        SELECT @appCrashTitle = 'Application crash detected in '
        SELECT @appHangTitle = 'Application hang detected in '
        SELECT @appCrashDetail = 'Windows detected an application crash.'
        SELECT @appHangDetail = 'Windows detected an application hang.'
        SELECT @shimTitle = 'Shim applied to '
        SELECT @shimDetail = 'Compatibility fix applied to '
        SELECT @shimFixInformation = @newline + 'Fix Information: '


        --Init and declare tracing information 
        SET @err = 0
        EXEC Trace_sp @traceLevel = 3, @msg = N'--[PopulateGeneralFeedbackReport_sp] - PopulateGeneralFeedbackReport_sp'
        -- Obtain last sync time 
        SELECT @last_sync_time = MAX(Last_New_Prop_Issue_Match) from [dbo].[CLIENT_STATE_DETAILS]
        BEGIN TRAN
            -- Insert all Apps which match against any new Application Issues 
            Insert into [dbo].[GeneralFeedback_Report] (timeLineEventID, eventTimeStamp,userMachineId, userName, machineName, osID, title, detail, attachedFile)
            Select timeline.timeLineEventID, timeline.eventTimeStamp , timeline.userMachineId , users.userName, machines.machineName, Machines.osID, timeline.feedbackTitle, timeline.feedbackDetails, timeline.feedbackAttachedFile
            from [TimeLineEvents] timeline, [Users] users , [Machines] machines, [UserMachine] userMachine
            Where timeLineEventType='GeneralFeedback'
            AND timeline.userMachineId = userMachine.userMachineID 
            AND userMachine.userID = users.userID
            AND userMachine.machineID = machines.machineID
            AND timeline.LastUpdatedDateTime > @last_sync_time 
            UNION
            (
                -- PCA Events
                -- PCAHelpedUserEvent ORPHAN Issues
                Select timeline.timeLineEventID, timeline.eventTimeStamp, timeline.userMachineId, users.userName, machines.machineName, Machines.osID, @pcaTitle + timeline.pcaExePath, @pcaDetail + @exePath + pcaExePath + @compatibilityLayer + pcaCompatibilityLayer, NULL
                from [TimeLineEvents] timeline, [Users] users , [Machines] machines, [UserMachine] userMachine
                WHERE timeLineEventType = 'PcaHelpedUserEvent'
                AND timeline.appIdentity is null
                AND timeline.userMachineId = userMachine.userMachineID 
                AND userMachine.userID = users.userID
                AND userMachine.machineID = machines.machineID
                AND timeline.LastUpdatedDateTime > @last_sync_time 
            )
            UNION
            (
                -- WER Events
                -- App crash ORPHAN Issues
                Select timeline.timeLineEventID, timeline.eventTimeStamp, timeline.userMachineId, users.userName, machines.machineName, Machines.osID, @appCrashTitle + timeline.werExePath, @appCrashDetail + @exePath + werExePath, NULL
                from [TimeLineEvents] timeline, [Users] users , [Machines] machines, [UserMachine] userMachine
                WHERE timeLineEventType = 'WerApplicationCrash'
                AND timeline.appIdentity is null
                AND timeline.userMachineId = userMachine.userMachineID 
                AND userMachine.userID = users.userID
                AND userMachine.machineID = machines.machineID
                AND timeline.LastUpdatedDateTime > @last_sync_time 
                Union
                (
                    -- App hang ORPHAN Issues
                    Select timeline.timeLineEventID, timeline.eventTimeStamp, timeline.userMachineId, users.userName, machines.machineName, Machines.osID, @appHangTitle + timeline.werExePath, @appHangDetail + @exePath + werExePath, NULL
                    from [TimeLineEvents] timeline, [Users] users , [Machines] machines, [UserMachine] userMachine
                    WHERE timeLineEventType = 'WerApplicationHang'
                    AND timeline.appIdentity is null
                    AND timeline.userMachineId = userMachine.userMachineID 
                    AND userMachine.userID = users.userID
                    AND userMachine.machineID = machines.machineID
                    AND timeline.LastUpdatedDateTime > @last_sync_time 
                )
            )
            UNION
            (
                -- Shim Events
                -- ShimCompatibilityFixEvent ORPHAN Issues
                Select timeline.timeLineEventID, timeline.eventTimeStamp, timeline.userMachineId, users.userName, machines.machineName, Machines.osID, @shimTitle + timeline.shimExePath, @shimDetail + @exePath + @shimFixInformation + shimFixName + ',' + shimFixId + ',' + shimFlags, NULL
                from [TimeLineEvents] timeline, [Users] users , [Machines] machines, [UserMachine] userMachine
                WHERE timeLineEventType = 'ShimCompatibilityFixEvent'
                AND timeline.appIdentity is null
                AND timeline.userMachineId = userMachine.userMachineID 
                AND userMachine.userID = users.userID
                AND userMachine.machineID = machines.machineID
                AND timeline.LastUpdatedDateTime > @last_sync_time 
            )


            EXEC Trace_sp @traceLevel = 3, @msg = N'--[PopulateGeneralFeedbackReport_sp] - PopulateGeneralFeedbackReport_sp'
            SET @err = @@error
            IF @err != 0
            BEGIN    
                SELECT @errMsg = 'PopulateGeneralFeedbackReport_sp' + ' ' + 'Could not populate the GeneralFeedback_Report'
                GOTO ErrorHandler
            END
        COMMIT TRAN

        EXEC Trace_sp @traceLevel = 2, @msg = N'[PopulateGeneralFeedbackReport_sp] - Complete'

ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[PopulateGeneralFeedbackReport_sp] - Transaction aborted'
            RETURN(@@error)
        END

    END
GO

CREATE PROCEDURE [dbo].[GenUIReports_sp]
/*
<summary>
    Generate fields needed by UI Reports.
    Called by LogPostProcessing_sp.
</summary> 
<parameters /> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2 , @msg = N'[GenUIReports_sp] - Started'
     
        BEGIN TRAN 
            DECLARE  @err INT    
            DECLARE  @errMsg NVARCHAR(260)    
               
            -- This updates app -- computer counts. We don't know delta of computers
            -- so we must execute this.
            EXEC [dbo].[UpdateStaticCounts_sp]
            EXEC Trace_sp @traceLevel = 2, @msg = N'--[GenUIReports_sp] - UpdateStaticCounts_sp -- Finished'

            EXEC [dbo].[UpdateIssueCounts_sp]
            EXEC Trace_sp @traceLevel = 3, @msg = N'--[GenUIReports_sp] - UpdateIssueCounts_sp -- Finished'

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[GenUIReports_sp]' + ' ' + 'Could not update Static/Issue Counts'
                GOTO ErrorHandler
            END

            -- Generate Device Reports (Only if we discover a new machine with device in it)
            EXEC [dbo].[GenDeviceReports_sp]

            EXEC Trace_sp @traceLevel = 3, @msg = N'--[GenUIReports_sp] - GenDeviceReports_sp -- Finished'
    
            -- Tracing and Error Handling	
            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[GenUIReports_sp]' + ' ' + 'Could not generate Device reports'
                GOTO ErrorHandler
            END
    
            SET @err = @@ERROR        
        COMMIT TRAN
    
        EXEC Trace_sp @traceLevel = 2 , @msg = N'[GenUIReports_sp] - Complete'
    
        --Error Handling Routine
ErrorHandler:    
        IF @@ERROR > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1 , @msg = @errMsg        
            RAISERROR (@errMsg,18,127)        
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN        
            EXEC Trace_sp @traceLevel = 1 , @msg = N'[GenUIReports_sp] - Transaction aborted'        
            RETURN (@@ERROR)
        END
    END
GO
  
--*****************************************************************
-- COUNT RELATED STORED PROCEDURES CALLED IN GenUIReports_sp
--*****************************************************************

CREATE PROCEDURE [dbo].[UpdateStaticCounts_sp]
/*
<summary>
    Wrapper procedure which implements
    updates the counts of Applications/Machines/Urls/Devices in the 
    reporting tables.
    Called by GenUIReports_sp
</summary> 
<parameters /> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2 , @msg = N'[UpdateStaticCounts_sp] - Started'

        BEGIN TRAN 
            DECLARE @err int
            DECLARE @errMsg nvarchar(260)

            -- Update Application Counts
            -- Computer Count for Applications
            UPDATE app 
            SET app.computerCount = temp.computerCount
            FROM [dbo].[Applications] app
            JOIN [dbo].[Application_Computer_Count] temp
                ON app.appIdentity = temp.appIdentity

            -- Update Group Counts
            -- Computer Count for Groups
            UPDATE appGroup 
            SET appGroup.computerCount = temp.computerCount
            FROM [dbo].[Application_Groups] appGroup
            JOIN [dbo].[Group_Computer_Count] temp
                ON appGroup.groupID = temp.groupID

            -- Application Count for Groups
            UPDATE appGroup 
            SET appGroup.appCount = temp.appCount
            FROM [dbo].[Application_Groups] appGroup
            JOIN [dbo].[Group_Application_Count] temp
                ON appGroup.groupID = temp.groupID

            -- Update Computer Counts
            -- Application/Device counts for Computers
            UPDATE M
            SET	M.appCount = temp.appCount,
                M.deviceCount = temp.deviceCount
            FROM [dbo].[Machines] M
            JOIN [dbo].[Computer_Counts] temp
                ON M.machineID = temp.machineID AND M.osID = temp.osID

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[UpdateStaticCounts_sp]' + ' ' + 'Could not updae computer summary'
                GOTO ErrorHandler
            END

            EXEC Trace_sp @traceLevel = 2, @msg = N'[UpdateStaticCounts_sp] - Ended'
        COMMIT TRAN

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[UpdateStaticCounts_sp] - Transaction aborted'
            RETURN(@@error)
        END
    END
GO

CREATE PROCEDURE [dbo].[FixIssueCounts_sp]
(
    @issueID	nvarchar(36)
)
/*
<summary>
This procedure updates all issue counts pertaining
to the Issue Id passed in. 
Note: This is called within a tran from the UI
</summary> 
<parameters> 
</parameters> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        BEGIN TRAN 
            DECLARE @err int
            DECLARE @errMsg nvarchar(260)

            -- Update Application Issue Counts in Report

            UPDATE app	
            SET	app.activeIssueCount = temp.activeIssuesCount,
                app.resolvedIssueCount = temp.resolvedIssuesCount
            FROM [dbo].[Application_Report] app
            JOIN [dbo].[App_Issue_Counts] temp  
            ON app.appIdentity = temp.appIdentity AND app.osID = temp.osID
            JOIN [dbo].[Issues_Associated_With_App] issueApp 
            ON issueApp.appIdentity = temp.appIdentity	
            WHERE issueApp.issueID = @issueID
        
            -- Update Url Report

            UPDATE url
            SET url.activeIssueCount = temp.activeIssuesCount,
                url.resolvedIssueCount = temp.resolvedIssuesCount
            FROM [dbo].[Url_Report] url
            JOIN [dbo].[Url_Issues_Count] temp
            ON url.ieceUrlID = temp.UrlID AND url.OsID = temp.osID
            JOIN [dbo].[Issues_Associated_With_Url] issueUrl 
            ON issueUrl.urlID = temp.urlID	
            WHERE issueUrl.issueID = @issueID

            -- Update Computer Report
 
            UPDATE comp
            SET comp.appIssueCount = temp.appsWithIssues
            FROM [dbo].[Computer_Report] comp
            JOIN [dbo].[Computer_AppIssue_Counts] temp
            ON	comp.osID = temp.osID 
            AND comp.machineID = temp.machineID
            AND comp.depOsID = temp.depOsID
            JOIN [dbo].[App_Installed_On_Machine] appMachine
            ON appMachine.osID = comp.osID
            AND appMachine.machineID = comp.machineID
            JOIN [dbo].[Issues_Associated_With_App] issueApp 
            ON issueApp.appIdentity = appMachine.appIdentity
            JOIN [dbo].[Issue_Affects_OS] issueOS
            ON issueOS.issueID = issueApp.issueID
            AND issueOs.osID = temp.depOsID		
            WHERE issueApp.issueID = @issueID
        

            -- Tracing and Error Handling

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[FixIssueCounts_sp]' + ' ' + 'Could not update Issue Counts'
                GOTO ErrorHandler
            END

            EXEC Trace_sp @traceLevel = 2, @msg = N'FixIssueCounts_sp - Ended'

         COMMIT TRAN
        --Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
           EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
           RAISERROR(@errMsg,18,127)
           IF @@trancount > 0
               ROLLBACK TRAN
           EXEC Trace_sp @traceLevel = 1, @msg = N'FixIssueCounts_sp - Transaction aborted'
           RETURN(@@error)
        END
    END
GO

CREATE PROCEDURE [dbo].[FixSolutionCounts_sp]
(
    @solutionID	nvarchar(36)
)
/*
<summary>
This procedure updates all solution counts pertaining
to the solution Id passed in. 
Note: This is called within a tran from the UI
</summary> 
<parameters> 
</parameters> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        BEGIN TRAN 
            DECLARE @err int
            DECLARE @errMsg nvarchar(260)

            EXEC Trace_sp @traceLevel = 2, @msg = N'[FixSolutionCounts_sp] - Started'

            -- Fix All Solutions Counts 
            -- pertaining to the argument
            -- passed

            UPDATE appReport
            SET	appReport.solutionCount = temp.solutionCount
            FROM [dbo].[IssueSolution] issueSol
            JOIN [dbo].[Issues_Associated_With_App] issueApp
            ON issueSol.issueID = issueApp.issueID
            JOIN [dbo].[Issue_Affects_Os] issueOS
            ON issueOS.issueID = issueSol.issueID
            JOIN [dbo].[Application_Report] appReport
            ON appReport.osID = issueOS.osID
            AND appReport.appIdentity = issueApp.appIdentity
            JOIN [dbo].[App_Solution_Counts] temp
            ON temp.appIdentity = appReport.appIdentity
            AND temp.osID = appReport.osID
            WHERE issueSol.solutionID = @solutionID

            -- Tracing and Error Handling

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[FixSolutionCounts_sp]' + ' ' + 'Could not update Solution Counts'
                GOTO ErrorHandler
            END

            EXEC Trace_sp @traceLevel = 2, @msg = N'[FixSolutionCounts_sp] - Ended'

        COMMIT TRAN

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[FixSolutionCounts_sp] - Transaction aborted'
            RETURN(@@error)
        END
    END
GO


CREATE PROCEDURE [dbo].[UpdateIssueCounts_sp]
/*
<summary>
    Wrapper procedure which updates the issue/solution counts
    reporting tables
    Called by GenUIReports_sp
</summary> 
<parameters /> 
<returns> 
    <return value="0" description="Success"/> 
    <return value="-n" description="Failure, where n is the error number."/> 
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2, @msg = N'[UpdateIssueCounts_sp] - Started'

        BEGIN TRAN 
            DECLARE @err int
            DECLARE @errMsg nvarchar(260)

            -- Update Application Report
            UPDATE	app	
            SET	app.activeIssueCount = temp.activeIssuesCount,
                app.resolvedIssueCount = temp.resolvedIssuesCount,
                app.solutionCount = sol.solutionCount
            FROM [dbo].[Application_Report] app
            JOIN [dbo].[App_Issue_Counts] temp
                ON app.appIdentity = temp.appIdentity AND app.osID = temp.osID
            JOIN [dbo].[App_Solution_Counts] sol
                ON sol.appIdentity = temp.appIdentity AND app.osID = sol.osID
        
            EXEC Trace_sp @traceLevel = 2, @msg = N'--[UpdateIssueCounts_sp]  Update Application Report - Ended'

            -- Update Url Report
            UPDATE	url
            SET	url.activeIssueCount = temp.activeIssuesCount,
                url.resolvedIssueCount = temp.resolvedIssuesCount
            FROM [dbo].[Url_Report] url
            JOIN [dbo].[Url_Issues_Count] temp
                ON url.ieceUrlID = temp.UrlID AND url.OsID = temp.osID

            EXEC Trace_sp @traceLevel = 2, @msg = N'--[UpdateIssueCounts_sp]  Update Url Report - Ended'

            -- Update Computer Report
            UPDATE	comp
            SET	comp.appIssueCount = temp.appsWithIssues
            FROM [dbo].[Computer_Report] comp
            JOIN [dbo].[Computer_AppIssue_Counts] temp
                ON	comp.osID = temp.osID AND comp.machineID = temp.machineID AND comp.depOsID = temp.depOsID

            EXEC Trace_sp @traceLevel = 2, @msg = N'--[UpdateIssueCounts_sp]  Update Computer Report - Ended'
            
            -- Tracing and Error Handling
            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[UpdateIssueCounts_sp]' + ' ' + 'Could not update client state details'
                GOTO ErrorHandler
            END

            EXEC Trace_sp @traceLevel = 2, @msg = N'[UpdateIssueCounts_sp] - Ended'
        COMMIT TRAN

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[UpdateIssueCounts_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

--****************************************************
-- DEVICE RELATED STORED PROCEDURES
--****************************************************

CREATE PROCEDURE [dbo].[GenDeviceReports_sp]
/*
<summary>
    Wrapper Stored procedure to generate device reports.
    Called by GenUIReports_sp, and SyncPostProcessing_sp.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2, @msg = N'[GenDeviceReports_sp] - Started'

        BEGIN TRAN
            DECLARE  @err INT    
            DECLARE  @errMsg NVARCHAR(260)    

            EXEC [dbo].[GenReportDeviceList_sp]
            DELETE FROM [dbo].[Device_Rating]

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[GenDeviceReports_sp]' + ' ' + 'Could not updae computer summary'
                GOTO ErrorHandler
            END
        COMMIT TRAN

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[GenDeviceReports_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

CREATE PROCEDURE [dbo].[GenReportDeviceList_sp]
/*
<summary>
    Stored procedure to generate device inventory list.
    Called by GenDeviceReports_sp
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        EXEC Trace_sp @traceLevel = 2, @msg = N'[GenReportDeviceList_sp] - Started'

        BEGIN TRAN
            DECLARE  @err INT    
            DECLARE  @errMsg NVARCHAR(260)    

            DELETE FROM [dbo].[Device_Report]

            INSERT INTO [dbo].[Device_Report] ( osID
                                                ,deviceID
                                                ,model
                                                ,manufacturer
                                                ,class
                                                ,CompatibilityRating32
                                                ,CompatibilityRating64
                                                ,computers )
            SELECT depos.osID,
                   device.deviceID, 
                   device.model,
                   device.manufacturer,
                   device.class, 
                   CompatibilityRating32, 
                   CompatibilityRating64, 
                   computers 
            FROM
            (
                (
                    SELECT osID
                    FROM Deployment_OS
                ) depos
                CROSS JOIN
                (
                    --Device info (selects every device)
                    SELECT d.deviceID,
                           d.class,
                           d.manufacturer,
                           d.model,
                           d.matchingID 
                    FROM [dbo].[PnPDevice_Installed_Driver] d
                    RIGHT JOIN
                    (
                        SELECT d1.deviceID, max(d1.matchingID) as matchingID 
                        FROM [dbo].[PnPDevice_Installed_Driver] d1
                        GROUP BY deviceID
                    )d2
                    ON d.deviceID = d2.deviceID and d.matchingID = d2.matchingID
                ) device
                LEFT JOIN
                (
                    --machine count (selects every device)
                    SELECT deviceID as Id,
                           COUNT(*) computers
                    FROM [dbo].[PnPDevice_Machines]
                    GROUP BY deviceID
                ) count
                    ON device.deviceID = count.Id		
                LEFT JOIN		
                (	
                    SELECT pnpID, osID, ratingType as CompatibilityRating32
                    FROM [dbo].[Device_Rating]
                    WHERE architecture = 0
                ) rating32
                    ON rating32.pnpId = device.matchingID AND rating32.osID = depos.osID
                LEFT JOIN
                (
                    SELECT pnpID, osID, ratingType as CompatibilityRating64
                    FROM [dbo].[Device_Rating]
                    WHERE architecture = 1
                ) rating64
                    ON rating64.pnpId = rating32.pnpId AND rating64.osID =  rating32.osID
            )
        
            -- Update device Issue count
            UPDATE comp
            SET comp.deviceIssueCount = temp.deviceIssueCount
            FROM  [dbo].[Computer_Report] comp
            JOIN [dbo].[Computer_DeviceIssue_Count] temp
                ON temp.osID = comp.osID AND temp.depOsID = comp.depOsID AND temp.macAddress = comp.macAddress

            --Set default values to 0
            UPDATE [dbo].[Device_Report]
            SET CompatibilityRating32=0 WHERE CompatibilityRating32 IS NULL

            UPDATE [dbo].[Device_Report]
            SET CompatibilityRating64=0 WHERE CompatibilityRating64 IS NULL

            SET @err = @@error
            IF @err != 0
            BEGIN	
                SELECT @errMsg = '[GenReportDeviceList_sp]' + ' ' + 'Could not update device list'
                GOTO ErrorHandler
            END
        COMMIT TRAN

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[GenReportDeviceList_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

--*****************************************************************
-- SYNC RELATED STORED PROCEDURES CALLED IN SyncPostProcessing_sp
--*****************************************************************

CREATE PROCEDURE [dbo].[RemoveDeletedIssues_sp]
/*
<summary>
    Stored procedure to remove deleted issues. 
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON    
        EXEC Trace_sp @traceLevel = 2, @msg = N'[RemoveDeletedIssues_sp] - Started'
    
        BEGIN TRAN
            DECLARE  @err INT    
            DECLARE  @errMsg NVARCHAR(260)    

            DELETE a
            FROM [dbo].[Issues_Associated_With_App] a
            WHERE EXISTS
            (
                SELECT 'x' FROM [dbo].[Issues] b
                WHERE b.issueID = a.issueID AND b.deleteMarker = 1
            )
    
            IF @@error != 0
                GOTO errorHandler

            DELETE a
            FROM [dbo].[Issue_Affects_OS] a
            WHERE EXISTS
            (
                SELECT 'x' FROM [dbo].[Issues] b
                WHERE b.issueID = a.issueID AND b.deleteMarker = 1
            )            

            IF @@error != 0
                GOTO errorHandler
            
            DELETE a
            FROM [dbo].[Localized_Issue] a
            WHERE EXISTS
            (
                SELECT 'x' FROM [dbo].[Issues] b
                WHERE b.issueID = a.issueID AND b.deleteMarker = 1
            )

            IF @@error != 0
                GOTO errorHandler

            DELETE a
            FROM [dbo].[IssueSolution] a
            WHERE EXISTS
            (
                SELECT 'x' FROM [dbo].[Issues] b
                WHERE b.issueID = a.issueID AND b.deleteMarker = 1
            )

            IF @@error != 0
                GOTO errorHandler

            DELETE FROM [dbo].[Issues]
            WHERE deleteMarker = 1
    
            IF @@error != 0
                GOTO errorHandler

            EXEC Trace_sp @traceLevel = 2, @msg = N'[RemoveDeletedIssues_sp] - Ended'
        
        COMMIT TRAN
--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[RemoveDeletedIssues_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

CREATE PROC [dbo].[RemoveDeletedSolutions_sp]
/*
<summary>
    Stored procedure to remove deleted solutions.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON    
        EXEC Trace_sp @traceLevel = 2, @msg = N'[RemoveDeletedSolutions_sp] - Started'
    
        BEGIN TRAN
            
            DELETE a
            FROM [dbo].[IssueSolution] a
            WHERE EXISTS 
            (
                SELECT 'x' FROM [dbo].[Solution] b
                WHERE b.solutionID = a.solutionID AND b.deleteMarker = 1
            )

            IF @@error != 0
                GOTO errorHandler

            DELETE a
            FROM [dbo].[Localized_Solution] a
            WHERE EXISTS 
            (
                SELECT 'x' FROM [dbo].[Solution] b
                WHERE b.solutionID = a.solutionID AND b.deleteMarker = 1
            )

            IF @@error != 0
                GOTO errorHandler

            DELETE FROM [dbo].[Solution]
            WHERE deleteMarker = 1

            IF @@error != 0
                GOTO errorHandler

            EXEC Trace_sp @traceLevel = 2, @msg = N'[RemoveDeletedSolutions_sp] - Ended'

        COMMIT TRAN
--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[RemoveDeletedIssues_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

CREATE PROCEDURE [dbo].[MergeRapUserRatings_sp]
/*
<summary>
    Stored procedure to merge rap user ratings into Application_Reports.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON;
        EXEC Trace_sp @traceLevel = 2, @msg = N'[MergeRapUserRatings_sp] - Started'

        BEGIN TRAN
    
            -- Update Application Report from data in Application_Rating and Application_Votes
            DECLARE @UserRatingValues table(
                        appIdentity [dbo].[AppIdentity], 
                        osID [dbo].[OperatingSystemId],
                        feedbackRating nvarchar(128), 
                        processorArchitecture nvarchar(128),
                        ratingCount int)

            INSERT INTO @UserRatingValues(
                appIdentity,
                osID ,
                feedbackRating,
                processorArchitecture,
                ratingCount)
            SELECT tableA.appIdentity,
                   machines.osID,
                   tableA.feedbackRating,
                   machines.processorArchitecture,
                   COUNT(*) as ratingCount
            FROM
            (
                SELECT DISTINCT  timeline.[timeLineEventType] as timeLineEventType,
                                 timeline.[appIdentity] as appIdentity,
                                 timeline.[userMachineId] as userMachineId,
                                 timeline.[eventTimeStamp] as eventTimeStamp,
                                 timeline.[feedbackRating] as feedbackRating
                FROM 
                (  -- Get the latest ApplicationFeedback per usermachine.
                    SELECT MAX(eventTimeStamp)as maxEventTimeStamp,
                           appIdentity, userMachineId
                    FROM [dbo].[TimeLineEvents]
                    WHERE timeLineEventType = 'ApplicationFeedback' and feedbackRating != 'None'
                    GROUP BY appIdentity, userMachineId
                )tableB , [dbo].[TimeLineEvents] as timeline
                WHERE  tableB.appIdentity = timeline.appIdentity and tableB.userMachineId = timeline.userMachineId 
                       and tableB.maxEventTimeStamp = timeline.eventTimeStamp and timeline.timeLineEventType = 'ApplicationFeedback'
                       and timeline.feedbackRating != 'None'
            ) as tableA
            INNER JOIN [dbo].[UserMachine] as usermachine on usermachine.userMachineID = tableA.userMachineId
            INNER JOIN [dbo].[Machines] AS machines on usermachine.machineID = machines.machineID
            GROUP BY appIdentity, feedbackRating, processorArchitecture, machines.osID
                          
            IF @@error != 0
                GOTO errorHandler

            -- Reset all the UserRatings in the Application Report.
            UPDATE [dbo].[Application_Report]
            SET UADoesNotWork64 = 0,
                UAPartiallyWorks64 = 0,
                UAWorks64 = 0,
                UADoesNotWork32 = 0,
                UAPartiallyWorks32 = 0,
                UAWorks32 = 0

            IF @@error != 0
                GOTO errorHandler

            -- Update x64 UserRating in Application Report.
            UPDATE [dbo].[Application_Report]
            SET UADoesNotWork64 = userRating.ratingCount
            FROM [dbo].[Application_Report] as appReport, @UserRatingValues as userRating
            WHERE SUBSTRING(appReport.osID,1,3) = SUBSTRING(userRating.osID,1,3) AND appReport.appIdentity = userRating.appIdentity AND userRating.processorArchitecture = 'x64'AND userRating.feedbackRating = 'Unusable'

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[Application_Report]
            SET UAPartiallyWorks64 = userRating.ratingCount
            FROM [dbo].[Application_Report] as appReport, @UserRatingValues as userRating
            WHERE SUBSTRING(appReport.osID,1,3) = SUBSTRING(userRating.osID,1,3) AND appReport.appIdentity = userRating.appIdentity AND userRating.processorArchitecture = 'x64'AND userRating.feedbackRating = 'PartiallyWorks'

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[Application_Report]
            SET UAWorks64 = userRating.ratingCount
            FROM [dbo].[Application_Report] as appReport, @UserRatingValues as userRating
            WHERE SUBSTRING(appReport.osID,1,3) = SUBSTRING(userRating.osID,1,3) AND appReport.appIdentity = userRating.appIdentity AND userRating.processorArchitecture = 'x64'AND userRating.feedbackRating = 'Works'

            IF @@error != 0
                GOTO errorHandler

            -- Update x86 UserRating in Application Report.
            UPDATE [dbo].[Application_Report]
            SET UADoesNotWork32 = userRating.ratingCount
            FROM [dbo].[Application_Report] as appReport, @UserRatingValues as userRating
            WHERE SUBSTRING(appReport.osID,1,3) = SUBSTRING(userRating.osID,1,3) AND appReport.appIdentity = userRating.appIdentity AND userRating.processorArchitecture = 'x86'AND userRating.feedbackRating = 'Unusable'

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[Application_Report]
            SET UAPartiallyWorks32 = userRating.ratingCount
            FROM [dbo].[Application_Report] as appReport, @UserRatingValues as userRating
            WHERE SUBSTRING(appReport.osID,1,3) = SUBSTRING(userRating.osID,1,3) AND appReport.appIdentity = userRating.appIdentity AND userRating.processorArchitecture = 'x86'AND userRating.feedbackRating = 'PartiallyWorks'

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[Application_Report]
            SET UAWorks32 = userRating.ratingCount
            FROM [dbo].[Application_Report] as appReport, @UserRatingValues as userRating
            WHERE SUBSTRING(appReport.osID,1,3) = SUBSTRING(userRating.osID,1,3) AND appReport.appIdentity = userRating.appIdentity AND userRating.processorArchitecture = 'x86'AND userRating.feedbackRating = 'Works'

            IF @@error != 0
                GOTO errorHandler

        COMMIT TRAN            
        EXEC Trace_sp @traceLevel = 2, @msg = N'[MergeRapUserRatings_sp] - Ended'

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[MergeRapUserRatings_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

CREATE PROCEDURE [dbo].[ComputeGroupUserRatings_sp]
/*
<summary>
    Stored procedure to compute application_groups user ratings obtained from RAP.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON;
        EXEC Trace_sp @traceLevel = 2, @msg = N'[ComputeGroupUserRatings_sp] - Started'

        BEGIN TRAN
    
            -- Update Application Report from data in Application_Rating and Application_Votes
            DECLARE @SyncValues table(
                        groupID [dbo].[GroupId], 
                        osID [dbo].[OperatingSystemId], 
                        doesNotWork32 int, 
                        partiallyWorks32 int, 
                        works32 int, 
                        doesNotWork64 int, 
                        partiallyWorks64 int, 
                        works64 int)    
                 
            INSERT INTO @SyncValues(
                osID,
                groupID,
                doesNotWork32,
                partiallyWorks32,
                works32,
                doesNotWork64,
                partiallyWorks64,
                works64)              
                Select SumWorks32.osID,
                 SumWorks32.groupID,
                 DoesNotWork32,
                 PartiallyWorks32,
                 Works32,
                 DoesNotWork64,
                 PartiallyWorks64,
                 Works64
                 from
                      (Select app.memberOf as groupID,
                      appReport.osID as osID, 
                      SUM([UAWorks32]) as Works32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity 
                      Group BY app.memberOf, osID) SumWorks32
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID, 
                      SUM([UAWorks64]) as Works64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity 
                      Group BY app.memberOf, osID) SumWorks64
                   ON SumWorks32.groupID = SumWorks64.groupID and SumWorks32.osID = SumWorks64.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([UAPartiallyWorks32]) as PartiallyWorks32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumPartiallyWorks32
                   ON SumWorks32.groupID = SumPartiallyWorks32.groupID and SumWorks32.osID = SumPartiallyWorks32.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([UAPartiallyWorks64]) as PartiallyWorks64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumPartiallyWorks64
                   ON SumWorks32.groupID = SumPartiallyWorks64.groupID and SumWorks32.osID = SumPartiallyWorks64.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([UADoesNotWork32]) as DoesNotWork32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumDoesNotWork32
                   ON SumWorks32.groupID = SumDoesNotWork32.groupID and SumWorks32.osID = SumDoesNotWork32.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([UADoesNotWork64]) as DoesNotWork64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumDoesNotWork64
                   ON SumWorks32.groupID = SumDoesNotWork64.groupID and SumWorks32.osID = SumDoesNotWork64.osID

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[ApplicationGroup_Report]
            SET UADoesNotWork32 = CASE WHEN sync.doesNotWork32 IS NULL THEN 0
                                     ELSE sync.doesNotWork32
                                END,		
                UADoesNotWork64 = CASE WHEN sync.doesNotWork64 IS NULL THEN 0
                                     ELSE sync.doesNotWork64
                                END,
                UAPartiallyWorks32 = CASE WHEN sync.partiallyWorks32 IS NULL THEN 0
                                        ELSE sync.partiallyWorks32
                                   END,
                UAPartiallyWorks64 = CASE WHEN sync.partiallyWorks64 IS NULL THEN 0
                                        ELSE sync.partiallyWorks64
                                   END,
                UAWorks32 = CASE WHEN sync.works32 IS NULL THEN 0
                               ELSE sync.works32
                          END,
                UAWorks64 = CASE WHEN sync.works64 IS NULL THEN 0
                               ELSE sync.works64
                          END
            FROM [dbo].[ApplicationGroup_Report] as groupReport, @SyncValues as sync
            WHERE groupReport.osID = sync.osID AND groupReport.groupID = sync.groupID

            IF @@error != 0
                GOTO errorHandler

        COMMIT TRAN            
        EXEC Trace_sp @traceLevel = 2, @msg = N'[ComputeGroupRatings_sp] - Ended'

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[ComputeGroupRatings_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO


CREATE PROCEDURE [dbo].[ComputeGroupRatings_sp]
/*
<summary>
    Stored procedure to compute application_groups ratings.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON;
        EXEC Trace_sp @traceLevel = 2, @msg = N'[ComputeGroupRatings_sp] - Started'

        BEGIN TRAN
    
            -- Update Application Report from data in Application_Rating and Application_Votes
            DECLARE @SyncValues table(
                        groupID [dbo].[GroupId], 
                        osID [dbo].[OperatingSystemId], 
                        compatRating32 int, 
                        compatRating64 int, 
                        doesNotWork32 int, 
                        partiallyWorks32 int, 
                        works32 int, 
                        doesNotWork64 int, 
                        partiallyWorks64 int, 
                        works64 int)    
                 
            INSERT INTO @SyncValues(
                osID,
                groupID,
                compatRating32,
                compatRating64,
                doesNotWork32,
                partiallyWorks32,
                works32,
                doesNotWork64,
                partiallyWorks64,
                works64)              
                Select MaxComp32.osID,
                 MaxComp32.groupID,
                 Comp32,
                 Comp64,
                 DoesNotWork32,
                 PartiallyWorks32,
                 Works32,
                 DoesNotWork64,
                 PartiallyWorks64,
                 Works64
                 from
                      (Select app.memberOf as groupID,
                      appReport.osID as osID, 
                      MAX([CompatibilityRating32]) as Comp32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) MaxComp32
                  JOIN 
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,   
                      MAX([CompatibilityRating64]) as Comp64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity 
                      Group BY app.memberOf, osID) MaxComp64
                  ON MaxComp32.groupID = MaxComp64.groupID and MaxComp32.osID = MaxComp64.osID
                  JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID, 
                      SUM([Works32]) as Works32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity 
                      Group BY app.memberOf, osID) SumWorks32
                   ON MaxComp32.groupID = SumWorks32.groupID and MaxComp32.osID = SumWorks32.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID, 
                      SUM([Works64]) as Works64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity 
                      Group BY app.memberOf, osID) SumWorks64
                   ON MaxComp32.groupID = SumWorks64.groupID and MaxComp32.osID = SumWorks64.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([PartiallyWorks32]) as PartiallyWorks32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumPartiallyWorks32
                   ON MaxComp32.groupID = SumPartiallyWorks32.groupID and MaxComp32.osID = SumPartiallyWorks32.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([PartiallyWorks64]) as PartiallyWorks64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumPartiallyWorks64
                   ON MaxComp32.groupID = SumPartiallyWorks64.groupID and MaxComp32.osID = SumPartiallyWorks64.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([DoesNotWork32]) as DoesNotWork32
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumDoesNotWork32
                   ON MaxComp32.groupID = SumDoesNotWork32.groupID and MaxComp32.osID = SumDoesNotWork32.osID
                   JOIN
                      (Select app.memberOf as groupID,
                      appReport.osID as osID,
                      SUM([DoesNotWork64]) as DoesNotWork64
                      FROM [dbo].[Application_Report] appReport
                      JOIN [dbo].[Applications] app 
                      ON app.appIdentity = appReport.appIdentity
                      Group BY app.memberOf, osID) SumDoesNotWork64
                   ON MaxComp32.groupID = SumDoesNotWork64.groupID and MaxComp32.osID = SumDoesNotWork64.osID

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[ApplicationGroup_Report]
            SET CompatibilityRating32 = CASE WHEN sync.compatRating32 IS NULL THEN 0
                                             ELSE sync.compatRating32
                                        END,
                CompatibilityRating64 = CASE WHEN sync.compatRating64 IS NULL THEN 0
                                             ELSE sync.compatRating64
                                        END,
                DoesNotWork32 = CASE WHEN sync.doesNotWork32 IS NULL THEN 0
                                     ELSE sync.doesNotWork32
                                END,		
                DoesNotWork64 = CASE WHEN sync.doesNotWork64 IS NULL THEN 0
                                     ELSE sync.doesNotWork64
                                END,
                PartiallyWorks32 = CASE WHEN sync.partiallyWorks32 IS NULL THEN 0
                                        ELSE sync.partiallyWorks32
                                   END,
                PartiallyWorks64 = CASE WHEN sync.partiallyWorks64 IS NULL THEN 0
                                        ELSE sync.partiallyWorks64
                                   END,
                Works32 = CASE WHEN sync.works32 IS NULL THEN 0
                               ELSE sync.works32
                          END,
                Works64 = CASE WHEN sync.works64 IS NULL THEN 0
                               ELSE sync.works64
                          END
            FROM [dbo].[ApplicationGroup_Report] as groupReport, @SyncValues as sync
            WHERE groupReport.osID = sync.osID AND groupReport.groupID = sync.groupID

            IF @@error != 0
                GOTO errorHandler

        COMMIT TRAN            
        EXEC Trace_sp @traceLevel = 2, @msg = N'[ComputeGroupRatings_sp] - Ended'

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[ComputeGroupRatings_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

CREATE PROCEDURE [dbo].[MergeRatingsAndVotes_sp]
/*
<summary>
    Stored procedure to merge ratings and votes into Application_Reports.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON;
        EXEC Trace_sp @traceLevel = 2, @msg = N'[MergeRatingsAndVotes_sp] - Started'

        BEGIN TRAN
    
            -- Update Application Report from data in Application_Rating and Application_Votes
            DECLARE @SyncValues table(
                        appIdentity [dbo].[AppIdentity], 
                        osID [dbo].[OperatingSystemId], 
                        compatRating32 int, 
                        compatRating64 int, 
                        doesNotWork32 int, 
                        partiallyWorks32 int, 
                        works32 int, 
                        doesNotWork64 int, 
                        partiallyWorks64 int, 
                        works64 int)    
                 
            INSERT INTO @SyncValues(
                osID,
                appIdentity,
                compatRating32,
                compatRating64,
                doesNotWork32,
                partiallyWorks32,
                works32,
                doesNotWork64,
                partiallyWorks64,
                works64)              
            SELECT depos.osID,
                   apps.appIdentity,
                   compatRating32,
                   compatRating64,
                   DNW32,
                   PW32,
                   W32,
                   DNW64,
                   PW64,
                   W64	
            FROM
            (
                (
                    SELECT osID
                    FROM Deployment_OS
                ) depos
                CROSS JOIN
                (
                    SELECT appIdentity
                    FROM [dbo].[Applications]    
                ) apps
                LEFT JOIN
                ( 
                    SELECT appIdentity, osID, ratingType as compatRating32
                    FROM [dbo].[Application_Rating]
                    WHERE architecture = 0
                ) rating32
                ON apps.appIdentity = rating32.appIdentity AND depos.osID = rating32.osID
                LEFT JOIN
                (   
                    SELECT appIdentity, osID, ratingType as compatRating64
                    FROM [dbo].[Application_Rating]
                    WHERE architecture = 1
                ) rating64
                ON apps.appIdentity = rating64.appIdentity AND depos.osID = rating64.osID    
                LEFT JOIN
                (
                    SELECT appIdentity, 
                           osID, 
                           doesNotWorkVote as DNW32, 
                           partiallyWorksVote as PW32, 
                           worksVote as W32
                    FROM [dbo].[Application_Votes]
                    WHERE architecture = 0 AND voteSource = 2
                ) votes32
                ON apps.appIdentity = votes32.appIdentity AND depos.osID = votes32.osID
                LEFT JOIN
                (
                    SELECT appIdentity, 
                           osID, 
                           doesNotWorkVote as DNW64, 
                           partiallyWorksVote as PW64, 
                           worksVote as W64
                    FROM [dbo].[Application_Votes]
                    WHERE architecture = 1 AND voteSource = 2
                ) votes64
                ON apps.appIdentity = votes64.appIdentity AND depos.osID = votes64.osID    
            )                

            IF @@error != 0
                GOTO errorHandler

            UPDATE [dbo].[Application_Report]
            SET CompatibilityRating32 = CASE WHEN sync.compatRating32 IS NULL THEN 0
                                             ELSE sync.compatRating32
                                        END,
                CompatibilityRating64 = CASE WHEN sync.compatRating64 IS NULL THEN 0
                                             ELSE sync.compatRating64
                                        END,
                DoesNotWork32 = CASE WHEN sync.doesNotWork32 IS NULL THEN 0
                                     ELSE sync.doesNotWork32
                                END,		
                DoesNotWork64 = CASE WHEN sync.doesNotWork64 IS NULL THEN 0
                                     ELSE sync.doesNotWork64
                                END,
                PartiallyWorks32 = CASE WHEN sync.partiallyWorks32 IS NULL THEN 0
                                        ELSE sync.partiallyWorks32
                                   END,
                PartiallyWorks64 = CASE WHEN sync.partiallyWorks64 IS NULL THEN 0
                                        ELSE sync.partiallyWorks64
                                   END,
                Works32 = CASE WHEN sync.works32 IS NULL THEN 0
                               ELSE sync.works32
                          END,
                Works64 = CASE WHEN sync.works64 IS NULL THEN 0
                               ELSE sync.works64
                          END
            FROM [dbo].[Application_Report] as appReport, @SyncValues as sync
            WHERE appReport.osID = sync.osID AND appReport.appIdentity = sync.appIdentity

            IF @@error != 0
                GOTO errorHandler

           DELETE FROM Application_Rating
           DELETE FROM Application_Votes WHERE voteSource = 2

            IF @@error != 0
                GOTO errorHandler

        COMMIT TRAN            
        EXEC Trace_sp @traceLevel = 2, @msg = N'[MergeRatingsAndVotes_sp] - Ended'

--Error Handling Routine
ErrorHandler:
        IF @@error > 0
        BEGIN
            IF @@trancount > 0
                ROLLBACK TRAN
                EXEC Trace_sp @traceLevel = 1, @msg = N'[MergeRatingsAndVotes_sp] - Transaction aborted'
            RETURN(@@error)
        END	
    END
GO

CREATE PROCEDURE [dbo].[AddWebServiceSolutions_sp]
/*
<summary>
    Stored procedure to add solutions synced from web service.
</summary> 
<parameters />
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON;
        EXEC Trace_sp @traceLevel = 2, @msg = N'[AddWebServiceSolutions_sp] - Started'

        BEGIN TRAN
            DECLARE @errMsg nvarchar(260)
            DECLARE @bit32Text nvarchar(260)
            DECLARE @bit64Text nvarchar(260)

            SET @bit32Text = 'Publisher has a solution for making this product work with 32 bit Windows'
            SET @bit64Text = 'Publisher has a solution for making this product work with 64 bit Windows'

            PRINT '[AddWebServiceSolutions_sp] - Started'

            DECLARE @solutionVals table(
                        appIdentity [dbo].[AppIdentity], 
                        osID [dbo].[OperatingSystemId], 
                        architecture int,
                        solutionid nvarchar(36),
                        solutiontype int,
                        solutionurl varchar(2083),
                        issueid nvarchar(36),
                        issuetype nvarchar(25),
                        provider nvarchar(50),
                        subprovider nvarchar(50),
                        publisheddate datetime)
            
            DECLARE @tempLink table(
                    solutionid nvarchar(36),
                    solutiontype int,
                    solutionurl varchar(2083),
                    architecture int,
                    provider nvarchar(50),
                    subprovider nvarchar(50),
                    publisheddate datetime)
                    
            -- Tables to filled include Issue_Affects_Os, Issue_Associated_With_App, Issues, 
            -- Localized_Issue, Solution, LocalizedSolution, IssueSolution

            -- First insert into Issues. Select all unique combinations of (appid, osid). There will
            -- be duplicates because there can be multiple architectures with the same appid and osid.
   
            INSERT INTO [dbo].[Issues]
                (issueID, issueType, appIdentity, attrib_match_string,
                severity, priority, provider, subProvider,
                symptom, cause, publishedDate, deleteMarker, isStateDependent,
                dateCreated, myIssue)
            SELECT CONVERT(nvarchar(36),NEWID()), 'Application', apprating.appIdentity, CONVERT(nvarchar(32),apprating.appIdentity) + apprating.osID,
                   2, 2, 'Vendor', '', 3, 7, publishedDate, 0, 0, publishedDate,  0
            FROM   
            (
                SELECT appIdentity, 
                       osID, 
                       solutionType, 
                       publishedDate, 
                       ROW_NUMBER() OVER 
                       (
                           PARTITION BY appIdentity, osID 
                           ORDER BY architecture
                       ) as rowNum 
                FROM [dbo].[Application_Rating]
            ) AppRating 
            WHERE AppRating.rowNum = 1 AND (AppRating.solutionType = 15 OR AppRating.solutionType = 30 OR AppRating.solutionType = 40)       
            AND NOT EXISTS 
            (
                SELECT 'x'
                FROM   [dbo].[Issues] a
                WHERE  a.appIdentity = AppRating.appIdentity
                   AND a.issueType = 'Application' AND a.myIssue = 0
                    AND a.attrib_match_string = CONVERT(nvarchar(32),AppRating.appIdentity) + AppRating.osID
            )

            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Error in insertion into Issues. Rolling back...'
                PRINT @errMsg
                GOTO ErrorHandler
            END    
           
            -- Create a temp table @solutionVals, which is an inner join of issues and application_rating.
            -- This will recreate the entire application_rating table with the associated issues where
            -- the solution type is a valid one.
           
            INSERT INTO @solutionVals
            SELECT ar.appIdentity, osID, architecture, NULL, solutionType, solutionUrl, issueID, issueType, provider, subProvider, ar.publishedDate
            FROM [dbo].[Issues] 
            INNER JOIN [dbo].[Application_Rating] ar
                ON attrib_match_string = CONVERT(nvarchar(32),ar.appIdentity) + ar.osID AND solutionType IN (15, 30, 40)
               
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Error in creating temp table. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END                                         

            -- Populate Issue_Affects_OS from @solutionVals. Select distinct (issueid, osid)
            -- combinations.

            INSERT INTO [dbo].[Issue_Affects_OS]
            SELECT DISTINCT issueid, issuetype, osID
            FROM @solutionVals sv
            WHERE NOT EXISTS 
            (
                SELECT issueID FROM [dbo].[Issue_Affects_OS] issueos 
                WHERE issueos.issueID = sv.issueid AND
                      issueos.issueType = sv.issuetype AND
                      issueos.osID = sv.osID
            )

            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Error in insertion into Issues_Affect_OS. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END    

            -- Populate Issue_Associated_With_Apps from @solutionVals.

            INSERT INTO [dbo].[Issues_Associated_With_App] (appIdentity, issueID, issueType, isExcluded, isResolved)
            SELECT DISTINCT sv.appIdentity, sv.issueid, sv.issuetype, 0, 0
            FROM @solutionVals sv
            WHERE NOT EXISTS 
            (
                SELECT appIdentity from [dbo].[Issues_Associated_With_App] issueapp
                      WHERE issueapp.appIdentity = sv.appIdentity AND
                            issueapp.issueID = sv.issueid AND
                            issueapp.issueType = sv.issuetype
            )
                      
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Error in insertion into Issues_Associated_With_app. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END    

            -- Populate Localized_Issue from @solutionVals.
        
            INSERT INTO [dbo].[Localized_Issue] (issueID, issueType, locale, title)
            SELECT DISTINCT sv.issueid, sv.issuetype, 1033, 'Action Recommended - Solution Available'
            FROM @solutionVals sv
            WHERE NOT EXISTS 
            (
                SELECT * FROM [dbo].[Localized_Issue] lissue
                WHERE lissue.issueID = sv.issueid AND
                      lissue.issueType = sv.issuetype AND
                      lissue.locale = 1033
            )
                      
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Error in insertion into Localized_Issues. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END              

            -- Update issue counts to be displayed in the UI.
            EXEC [dbo].[UpdateIssueCounts_sp]

            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not execute updateissuecounts_sp. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END                        

            -- Now create another temp table @tempLink, which just contains the unique combinations of
            -- (solutionid, solutionurl) that are not already present in localized_solution.

            INSERT INTO @tempLink (solutionid, solutiontype, solutionurl, architecture, provider, subprovider, publisheddate)
            (
                SELECT CONVERT(nvarchar(36),NEWID()), 0, sv.solutionurl, sv.architecture, provider, sv.subprovider, sv.publisheddate
                FROM 
                (
                    SELECT provider, 
                           subprovider, 
                           architecture, 
                           publisheddate, 
                           solutiontype, 
                           solutionurl, 
                           ROW_NUMBER() OVER 
                           (
                               PARTITION BY solutiontype,solutionurl,architecture 
                               ORDER BY publisheddate
                           ) as rowNum 
                    FROM @solutionVals
                ) sv
                WHERE sv.rowNum = 1 AND
                NOT EXISTS 
                (
                    SELECT 'x' FROM [dbo].[Localized_Solution] 
                    WHERE Details = sv.solutionurl
                          AND title = CASE 
                                          WHEN sv.architecture = 0 
                                          THEN @bit32Text 
                                          ELSE @bit64Text 
                                      END
                )
            )
                                               
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not insert into tempLink. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END            
        
            INSERT INTO [dbo].[Solution] (solutionID, solutionType, provider, subProvider, publishedDate, isMitigable, dateModified)
            SELECT tl.solutionid, tl.solutiontype, tl.provider, tl.subprovider, tl.publisheddate, 1, GETDATE()
            FROM @tempLink tl
                      
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not insert into solutions. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END                                  

            INSERT INTO [dbo].[Localized_Solution] (solutionID, solutionType, title, locale, Details)
            SELECT tl.solutionid, 
                   0, 
                   CASE WHEN tl.architecture = 0 
                       THEN @bit32Text
                       ELSE @bit64Text
                   END,
                   1033,
                   tl.solutionurl           
            FROM @tempLink tl
                          
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not insert into localized solutions. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END        

            UPDATE @solutionVals
            SET solutionid = 
            (
                SELECT TOP 1 solutionid FROM [dbo].[Localized_Solution] lsol
                WHERE lsol.solutiontype = solutiontype AND
                      lsol.Details = solutionurl AND
                    lsol.title = CASE WHEN architecture = 0
                                       THEN @bit32Text
                                       ELSE @bit64Text
                                 END
            )
                               
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not update @solutionVals with correct solution IDs. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END                                       
                  
            DELETE FROM [dbo].[IssueSolution]
            WHERE issueID IN (SELECT DISTINCT issueID FROM @solutionVals)

            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not truncate new issues from issuesolution. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END                                      

            INSERT INTO [dbo].[IssueSolution] (issueID, issueType, solutionID, solutionType)
            SELECT sv.issueid, sv.issuetype, sv.solutionid, sv.solutiontype
            FROM @solutionVals sv
            WHERE NOT EXISTS 
            (
                SELECT * FROM [dbo].[IssueSolution] issol
                WHERE issol.issueID = sv.issueid AND
                      issol.solutionID = sv.solutionid AND
                      issol.issueType = sv.issuetype
            )
                      
            IF @@ERROR != 0
            BEGIN
                SELECT @errMsg = N'[AddWebServiceSolutions_sp] - Could not insert into issuesolutions. Rolling back...'
                PRINT @errMsg    
                GOTO ErrorHandler
            END                 
                                                
        COMMIT TRAN
        RETURN

--Error Handling Routine
ErrorHandler:
        BEGIN
            PRINT @errMsg
            EXEC Trace_sp @traceLevel = 1, @msg = @errMsg
            RAISERROR(@errMsg,18,127)
            ROLLBACK TRAN
            EXEC Trace_sp @traceLevel = 1, @msg = N'[AddWebServiceSolutions_sp] - Transaction aborted'
            RETURN
        END
    END
GO

--*******************************************
-- Grouping Related  Stored Procedures
--*******************************************
CREATE PROCEDURE [dbo].[GroupApp_sp] 
(
    @IpName nvarchar(200),
    @IpVersion nvarchar(100),
    @IpVendor nvarchar(260),
    @IpLanguage nvarchar(260)    
) 
/*
<summary>Stored procedure to find the application group. </summary> 
<parameters>
           <param required="yes" description="Name to be matched"> @IpName </param>
           <param required="yes" description="Version to be matched"> @IpVersion </param>
           <param required="yes" description="Vendor to be matched"> @IpVendor </param>
           <param required="yes" description="Language to be matched"> @IpLanguage </param>
</parameters>
<returns>
       <return value="matching rows"/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        
        SELECT app.*
        FROM [dbo].[Applications] app
        WHERE 
        (
            (app.appName = @IpName)
             AND ((app.vendorName = @IpVendor) OR ((@IpVendor IS NULL) AND (app.vendorName IS NULL)))
             AND ((SUBSTRING(@IpVersion, 1, CASE WHEN CHARINDEX('.',@IpVersion) > 0 THEN (CHARINDEX('.',@IpVersion) -1) else LEN(@IpVersion) END) 
                   = SUBSTRING(app.version, 1, CASE WHEN CHARINDEX('.',app.version) > 0 THEN (CHARINDEX('.',app.version) -1) else LEN(app.version) END))
                  OR ((@IpVersion IS NULL) AND (app.version IS NULL)))
        )
    END
GO


--*******************************************
-- Matching Related  Stored Procedures
--*******************************************
CREATE PROCEDURE [dbo].[MatchNVVL_sp] 
(
    @IpName nvarchar(200),
    @IpVersion nvarchar(100),
    @IpVendor nvarchar(260),
    @IpLanguage nvarchar(260)    
) 
/*
<summary>Stored procedure to match NVVL against Match Set view. </summary> 
<parameters>
           <param required="yes" description="Name to be matched"> @IpName </param>
           <param required="yes" description="Version to be matched"> @IpVersion </param>
           <param required="yes" description="Vendor to be matched"> @IpVendor </param>
           <param required="yes" description="Language to be matched"> @IpLanguage </param>                                 
</parameters>
<returns>
       <return value="matching rows"/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON
        
        SELECT app.*
        FROM [dbo].[Applications] app
        JOIN [dbo].[Unmatched_AppIds] mset
        ON app.appidentity = mset.appidentity
        WHERE 
        (
            (mset.Name = @IpName OR mset.AltName = @IpName)
            AND (CHARINDEX(@IpVersion,mset.Version) = 1 OR CHARINDEX(@IpVersion,mset.altVersion) = 1 OR (@IpVersion IS NULL AND (mset.version IS NULL OR mset.altVersion IS NULL)))
            AND (mset.Vendor = @IpVendor OR mset.AltVendor = @IpVendor OR (@IpVendor IS NULL AND (mset.vendor IS NULL OR mset.altVendor IS NULL)))
            AND (mset.Language = @IpLanguage OR mset.AltLanguage = @IpLanguage)
        )
    END
GO        
        

--****************************************************
-- VERSION RELATED STORED PROCEDURES
--****************************************************

CREATE PROCEDURE [dbo].[GetDatabaseVersion_sp] 
(
    @versionString varchar(24) OUTPUT
) 
/*
<summary>Stored procedure to get the current database version string. </summary> 
<parameters>
           <param required="yes" description="Database Version string."> @versionString </param>
</parameters>
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON

        SELECT TOP 1 @versionString = DBVersion
        FROM [dbo].[Version]
        ORDER BY VersionCreatedDate DESC

        RETURN @@ERROR
    END
GO

CREATE PROCEDURE [dbo].[GetMinLPSVersion_sp] 
(
    @versionString varchar(24) OUTPUT
) 
/*
<summary>Stored procedure to get the current min LPS version string. </summary> 
<parameters>
           <param required="yes" description="LPS Version string."> @versionString </param>
</parameters>
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON

        SELECT TOP 1 @versionString = LPSVersion
        FROM [dbo].[Version]
        ORDER BY VersionCreatedDate DESC

        RETURN @@ERROR
    END
GO

CREATE PROCEDURE [dbo].[GetDatabaseVersionInfo_sp] 
(
    @dbVersionString varchar(24) OUTPUT,
    @uiVersionString varchar(24) OUTPUT,
    @lpsVersionString varchar(24) OUTPUT
)     
/*
<summary>Stored procedure to get the current database version information. </summary> 
<parameters>
           <param required="yes" description="Database Version string."> @dbVersionString </param>
           <param required="yes" description="UI Version string."> @uiVersionString </param>
           <param required="yes" description="LPS Version string."> @lpsVersionString </param>
</parameters>
<returns>
       <return value="0" description="Success."/>
       <return value="-n" description="Failure, where n is the error number."/>
</returns>
*/
AS
    BEGIN
        SET NOCOUNT ON

        SELECT TOP 1 @dbVersionString = DBVersion, @uiVersionString = UIVersion, @lpsVersionString = LPSVersion
        FROM [dbo].[Version]
        ORDER BY VersionCreatedDate DESC

        RETURN @@ERROR
    END
GO

--****************************************************
-- PERFORMANCE METRICS MEASUREMENTS
--****************************************************
--------
-- Listing 3-3: Creation Script for the sp_perfworkload_trace_start Stored Procedure
--------
SET NOCOUNT ON;
GO

CREATE TABLE [dbo].[trace_db] 
(
    [database_id] int not null,
    [trace_id]    int not null,

    --Constraints
    CONSTRAINT [trace_db_PK] PRIMARY KEY CLUSTERED
    (
        [database_id]
    )  ON [PRIMARY]
)
GO

CREATE PROC [dbo].[sp_perfworkload_trace_start]
(
    @dbid      AS INT,
    @tracefile AS NVARCHAR(254),
    @traceid   AS INT OUTPUT
)
AS
    -- Create a Queue
    DECLARE @rc          AS INT;
    DECLARE @maxfilesize AS BIGINT;

    SET @maxfilesize = 5;

    EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL 
    IF (@rc != 0) GOTO error;

    -- Client side File and Table cannot be scripted
    -- Set the events
    DECLARE @on AS BIT;
    SET @on = 1;
    EXEC sp_trace_setevent @traceid, 10, 15, @on;
    EXEC sp_trace_setevent @traceid, 10, 8, @on;
    EXEC sp_trace_setevent @traceid, 10, 16, @on;
    EXEC sp_trace_setevent @traceid, 10, 48, @on;
    EXEC sp_trace_setevent @traceid, 10, 1, @on;
    EXEC sp_trace_setevent @traceid, 10, 17, @on;
    EXEC sp_trace_setevent @traceid, 10, 10, @on;
    EXEC sp_trace_setevent @traceid, 10, 18, @on;
    EXEC sp_trace_setevent @traceid, 10, 11, @on;
    EXEC sp_trace_setevent @traceid, 10, 12, @on;
    EXEC sp_trace_setevent @traceid, 10, 13, @on;
    EXEC sp_trace_setevent @traceid, 10, 14, @on;
    EXEC sp_trace_setevent @traceid, 45, 8, @on;
    EXEC sp_trace_setevent @traceid, 45, 16, @on;
    EXEC sp_trace_setevent @traceid, 45, 48, @on;
    EXEC sp_trace_setevent @traceid, 45, 1, @on;
    EXEC sp_trace_setevent @traceid, 45, 17, @on;
    EXEC sp_trace_setevent @traceid, 45, 10, @on;
    EXEC sp_trace_setevent @traceid, 45, 18, @on;
    EXEC sp_trace_setevent @traceid, 45, 11, @on;
    EXEC sp_trace_setevent @traceid, 45, 12, @on;
    EXEC sp_trace_setevent @traceid, 45, 13, @on;
    EXEC sp_trace_setevent @traceid, 45, 14, @on;
    EXEC sp_trace_setevent @traceid, 45, 15, @on;
    EXEC sp_trace_setevent @traceid, 41, 15, @on;
    EXEC sp_trace_setevent @traceid, 41, 8, @on;
    EXEC sp_trace_setevent @traceid, 41, 16, @on;
    EXEC sp_trace_setevent @traceid, 41, 48, @on;
    EXEC sp_trace_setevent @traceid, 41, 1, @on;
    EXEC sp_trace_setevent @traceid, 41, 17, @on;
    EXEC sp_trace_setevent @traceid, 41, 10, @on;
    EXEC sp_trace_setevent @traceid, 41, 18, @on;
    EXEC sp_trace_setevent @traceid, 41, 11, @on;
    EXEC sp_trace_setevent @traceid, 41, 12, @on;
    EXEC sp_trace_setevent @traceid, 41, 13, @on;
    EXEC sp_trace_setevent @traceid, 41, 14, @on;

    -- Set the Filters
    DECLARE @intfilter AS INT;
    DECLARE @bigintfilter AS BIGINT;

    -- Application name filter
    EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';
    -- Database ID filter
    EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

    -- Print trace id and file name for future references
    PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10))
        + ', Trace File: ''' + @tracefile + '.trc''';

    INSERT INTO trace_db ([database_id],[trace_id])
    VALUES (@dbid, @traceid)

    GOTO finish;

error: 
    PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

finish: 
    GO

CREATE PROC [dbo].[enable_trace]
AS
    -- Set the trace status to start
    DECLARE @traceid int;

    SELECT @traceid = (SELECT trace_id FROM trace_db WHERE database_id = db_id())
    IF (@traceid IS NULL) RETURN(0)

    EXEC sp_trace_setstatus @traceid, 1;
GO

CREATE PROC [dbo].[disable_trace]
AS
    -- Set the trace status to start
    DECLARE @traceid int;

    SELECT @traceid = (SELECT trace_id FROM trace_db WHERE database_id = db_id())
    IF (@traceid IS NULL) RETURN(0)

    EXEC sp_trace_setstatus @traceid, 0;
GO

CREATE PROC [dbo].[close_trace]
AS
    -- Set the trace status to start
    DECLARE @traceid int;

    SELECT @traceid = (SELECT trace_id FROM trace_db WHERE database_id = db_id())
    IF (@traceid IS NULL) RETURN(0)

    EXEC sp_trace_setstatus @traceid, 2;

    DELETE FROM trace_db
    WHERE database_id = db_id()
GO

-- Count = 27
-- Dropped Stored Procedures:RemoveDeletedAppRiskRatings_sp, FixIssueCounts_sp, FixSolutionCounts_sp
-- Dropped Count = 3
-- Added Stored Procedures: MatchNVVL_sp
-- Added Count = 1


/*--------------------------------------------------------------------------------
  
  <Copyright file="AddInitialData.sql" company="Microsoft">
    Copyright (c) Microsoft Corporation.  All rights reserved.
  </Copyright>

  <Comments>
    
    The following is the new implementation of the Application
    Compatability Toolkit Client Database. This is the creation
    script that is used to create functions on the required base
    tables. This file is the same as ClientDBData.sql in ACT 5.6.

  </Comments>
 
  <Version> 6.1.0.0 </Version>
  
  <@owner>
         padmav
  </@owner>
  
--------------------------------------------------------------------------------*/

GO

USE [ACT50]

--****************************
-- DEFAULT VALUES
--****************************

-- Default Categories for LOB
INSERT INTO [dbo].[Categories] (category) VALUES ('Test Complexity');
INSERT INTO [dbo].[Categories] (category) VALUES ('Software Vendor');

-- Default subcategories
INSERT INTO [dbo].[Subcategories] (categoryId, subCategory) VALUES(1,'Low');
INSERT INTO [dbo].[Subcategories] (categoryId, subCategory) VALUES(1,'Medium');
INSERT INTO [dbo].[Subcategories] (categoryId, subCategory) VALUES(1,'High');
INSERT INTO [dbo].[Subcategories] (categoryId, subCategory) VALUES(2,'Custom');
INSERT INTO [dbo].[Subcategories] (categoryId, subCategory) VALUES(2,'Microsoft Corporation');
INSERT INTO [dbo].[Subcategories] (categoryId, subCategory) VALUES(2,'Third-party');


-- DEFAULT VALUES Deployment Status
INSERT INTO [dbo].[Deployment_Status] VALUES('Not looked at');
INSERT INTO [dbo].[Deployment_Status] VALUES('Testing');
INSERT INTO [dbo].[Deployment_Status] VALUES('Mitigating');
INSERT INTO [dbo].[Deployment_Status] VALUES('Ready to Deploy');

-- DEFAULT VALUES EventCategory

INSERT INTO [dbo].[EventCategory] VALUES('Issue');
INSERT INTO [dbo].[EventCategory] VALUES('Contextual');

-- DEFAULT VALUES EventType

INSERT INTO [dbo].[EventType] VALUES('GeneralFeedback');
INSERT INTO [dbo].[EventType] VALUES('ApplicationFeedback');

INSERT INTO [dbo].[EventType] VALUES('PcaHelpedUserEvent');
INSERT INTO [dbo].[EventType] VALUES('PcaDeprecatedComponent');
INSERT INTO [dbo].[EventType] VALUES('PcaInstallFailure');
INSERT INTO [dbo].[EventType] VALUES('PcaDWM');
INSERT INTO [dbo].[EventType] VALUES('PcaLaunch16BitApp');
INSERT INTO [dbo].[EventType] VALUES('PcaFaultTolerantHeap');
INSERT INTO [dbo].[EventType] VALUES('PcaPinDLL');
INSERT INTO [dbo].[EventType] VALUES('PcaQuarantine');
INSERT INTO [dbo].[EventType] VALUES('PcaLegacyControlPanelApplet');
INSERT INTO [dbo].[EventType] VALUES('PcaDllLoadFailure');
INSERT INTO [dbo].[EventType] VALUES('PcaQuickAppTermination');
INSERT INTO [dbo].[EventType] VALUES('PcaUnhandledException');
INSERT INTO [dbo].[EventType] VALUES('PcaWRPAccessError');
INSERT INTO [dbo].[EventType] VALUES('PcaElevateCreateProcess');
INSERT INTO [dbo].[EventType] VALUES('PcaRegSvr32Failure');

INSERT INTO [dbo].[EventType] VALUES('WerApplicationCrash');
INSERT INTO [dbo].[EventType] VALUES('WerApplicationHang');
INSERT INTO [dbo].[EventType] VALUES('SuaFileIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaRegistryIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaIniIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaTokenIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaPrivilegeIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaNameSpaceIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaOtherObjectIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaProcessIssue');
INSERT INTO [dbo].[EventType] VALUES('SuaMitigation');
INSERT INTO [dbo].[EventType] VALUES('ShimCompatibilityFixEvent');
INSERT INTO [dbo].[EventType] VALUES('PsrEvent');
INSERT INTO [dbo].[EventType] VALUES('IeceMimeHandling01');
INSERT INTO [dbo].[EventType] VALUES('IeceMimeHandling02');
INSERT INTO [dbo].[EventType] VALUES('IeceMimeHandling03');
INSERT INTO [dbo].[EventType] VALUES('IeceWindowRestrictions01');
INSERT INTO [dbo].[EventType] VALUES('IeceZoneElev01');
INSERT INTO [dbo].[EventType] VALUES('IeceBinaryBhvr01');
INSERT INTO [dbo].[EventType] VALUES('IeceObjCache01');
INSERT INTO [dbo].[EventType] VALUES('IeceControlBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceControlBlock02');
INSERT INTO [dbo].[EventType] VALUES('IeceControlBlock03');
INSERT INTO [dbo].[EventType] VALUES('IecePUBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceDownloadBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceLMZL01');
INSERT INTO [dbo].[EventType] VALUES('IeceURLCreationFailure01');
INSERT INTO [dbo].[EventType] VALUES('IeceIDNNavigation01');
INSERT INTO [dbo].[EventType] VALUES('IeceSSLNavBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceScriptUrlBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceScriptUrlBlock02');
INSERT INTO [dbo].[EventType] VALUES('IeceAntiphishingBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceManageAddOns01');
INSERT INTO [dbo].[EventType] VALUES('IeceProtectedMode01');
INSERT INTO [dbo].[EventType] VALUES('IeceSubframeNavigateBlock01');
INSERT INTO [dbo].[EventType] VALUES('IeceCreateURLMonikerDifference01');
INSERT INTO [dbo].[EventType] VALUES('IeceCSSFixes01');
INSERT INTO [dbo].[EventType] VALUES('IeceUIPIExtensionBlocked01');
INSERT INTO [dbo].[EventType] VALUES('IeceXSSFilterPageSanitize01');
INSERT INTO [dbo].[EventType] VALUES('IeceDEPNX01');
INSERT INTO [dbo].[EventType] VALUES('IeceStandardsMode01');
INSERT INTO [dbo].[EventType] VALUES('IeceFileUploadNoPath01');
INSERT INTO [dbo].[EventType] VALUES('IeceCodepageDisabled01');
INSERT INTO [dbo].[EventType] VALUES('IeceAjaxNavigation01');
INSERT INTO [dbo].[EventType] VALUES('IeceApplicationProtocolDialog01');
INSERT INTO [dbo].[EventType] VALUES('IeceFrameNavigationRestricted01');
INSERT INTO [dbo].[EventType] VALUES('IeceUIPICrossProcessWM01');

-- EnzoL: since the description of the next property is longer than the column allowed size, we trim it.
-- In the future might be worthy replacing it with the full name:  IeceImageNotUpgradedToXMLorHTML01, IeceCertFilteringDefaultChange01
INSERT INTO [dbo].[EventType] VALUES('IeceImageNotUpgradedToXMLorHTM01');
INSERT INTO [dbo].[EventType] VALUES('IeceCertFilteringDefaultChange01');
INSERT INTO [dbo].[EventType] VALUES('IeceContentFromProxyDiscarded01');
INSERT INTO [dbo].[EventType] VALUES('IeceCrossMIC01');



-- DEFAULT VALUES IndicatorType

INSERT INTO [dbo].[IndicatorType] VALUES('Msi');
INSERT INTO [dbo].[IndicatorType] VALUES('Manifest');
INSERT INTO [dbo].[IndicatorType] VALUES('WindowsComponent');
INSERT INTO [dbo].[IndicatorType] VALUES('ServiceControlManager');
INSERT INTO [dbo].[IndicatorType] VALUES('Directory');
INSERT INTO [dbo].[IndicatorType] VALUES('AddRemoveProgram');
INSERT INTO [dbo].[IndicatorType] VALUES('Shell');
INSERT INTO [dbo].[IndicatorType] VALUES('AppPath');
INSERT INTO [dbo].[IndicatorType] VALUES('Registry');
INSERT INTO [dbo].[IndicatorType] VALUES('PathEnvVar');
INSERT INTO [dbo].[IndicatorType] VALUES('FileExt');

-- DEFAULT VALUES OS

insert into dbo.OS values('4.0.7','Windows NT SP7',4,0,0,'Service Pack 7',7,0,'Service Pack 7',0,0,'20060620')
insert into dbo.OS values('4.0.6','Windows NT SP6',4,0,0,'Service Pack 6',6,0,'Service Pack 6',0,0,'20060620')
insert into dbo.OS values('4.0.5','Windows NT SP5',4,0,0,'Service Pack 5',5,0,'Service Pack 5',0,0,'20060620')
insert into dbo.OS values('4.0.4','Windows NT SP4',4,0,0,'Service Pack 4',4,0,'Service Pack 4',0,0,'20060620')
insert into dbo.OS values('5.0.1','Windows 2000 SP1',5,0,0,'Service Pack 1',1,0,'Service Pack 1',0,0,'20060620')
insert into dbo.OS values('5.0.2','Windows 2000 SP2',5,0,0,'Service Pack 2',2,0,'Service Pack 2',0,0,'20060620')
insert into dbo.OS values('5.0.3','Windows 2000 SP3',5,0,0,'Service Pack 3',3,0,'Service Pack 3',0,0,'20060620')
insert into dbo.OS values('5.0.4','Windows 2000 SP4',5,0,0,'Service Pack 4',4,0,'Service Pack 4',0,0,'20060620')
insert into dbo.OS values('5.1.0','Windows XP',5,1,0,'',0,0,'',0,0,'20060620')
insert into dbo.OS values('5.1.1','Windows XP SP1',5,1,0,'Service Pack 1',1,0,'Service Pack 1',0,0,'20060620')
insert into dbo.OS values('5.1.2','Windows XP SP2',5,1,0,'Service Pack 2',2,0,'Service Pack 2',0,0,'20060620')
insert into dbo.OS values('5.1.3','Windows XP SP3',5,1,0,'Service Pack 3',3,0,'Service Pack 3',0,0,'20060620')
insert into dbo.OS values('5.2.0','Windows Server 2003',5,2,0,'',0,0,'',3,0,'20060620')
insert into dbo.OS values('5.2.1','Windows Server 2003 SP1',5,2,0,'Service Pack 1',3,0,'Service Pack 1',3,0,'20060620')
insert into dbo.OS values('5.2.2','Windows Server 2003 R2',5,2,0,'Release 2',2,0,'Release 2',3,0,'20060620')
insert into dbo.OS values('6.0.0','Windows Vista',6,0,0,'',0,0,'',0,0,'20060620')
insert into dbo.OS values('6.0.1','Windows Vista SP1',6,0,0,'Service Pack 1',1,0,'',0,0,'20070703')
insert into dbo.OS values('6.0.2','Windows Vista SP2',6,0,0,'Service Pack 2',2,0,'',0,0,'20090109')
insert into dbo.OS values('6.0.3','Windows Vista SP3',6,0,0,'Service Pack 3',3,0,'',0,0,'19000101')
insert into dbo.OS values('6.0.4','Windows Vista SP4',6,0,0,'Service Pack 4',4,0,'',0,0,'19000101')
insert into dbo.OS values('6.0.1.SRV','Windows Server 2008 SP1',6,0,0,'Service Pack 1',1,0,'',3,0,'20080724')
insert into dbo.OS values('6.0.2.SRV','Windows Server 2008 SP2',6,0,0,'Service Pack 2',2,0,'',3,0,'20090109')
insert into dbo.OS values('6.0.3.SRV','Windows Server 2008 SP3',6,0,0,'Service Pack 3',3,0,'',3,0,'19000101')
insert into dbo.OS values('6.0.4.SRV','Windows Server 2008 SP4',6,0,0,'Service Pack 4',4,0,'',3,0,'19000101')
insert into dbo.OS values('6.1.0','Windows 7',6,1,0,'',0,0,'',0,0,'20080724')
insert into dbo.OS values('6.1.1','Windows 7 SP1',6,1,0,'Service Pack 1',1,0,'Service Pack 1',0,0,'20110222')
insert into dbo.OS values('6.1.2','Windows 7 SP2',6,1,0,'Service Pack 2',2,0,'Service Pack 2',0,0,'19000101')
insert into dbo.OS values('6.1.3','Windows 7 SP3',6,1,0,'Service Pack 3',3,0,'Service Pack 3',0,0,'19000101')
insert into dbo.OS values('6.1.4','Windows 7 SP4',6,1,0,'Service Pack 4',4,0,'Service Pack 4',0,0,'19000101')
insert into dbo.OS values('6.1.0.SRV','Windows Server 2008 R2',6,1,0,'',0,0,'',3,0,'20090109')
insert into dbo.OS values('6.1.1.SRV','Windows Server 2008 R2 SP1',6,1,0,'Service Pack 1',1,0,'Service Pack 1',3,0,'20110222')
insert into dbo.OS values('6.1.2.SRV','Windows Server 2008 R2 SP2',6,1,0,'Service Pack 2',2,0,'Service Pack 2',3,0,'19000101')
insert into dbo.OS values('6.1.3.SRV','Windows Server 2008 R2 SP3',6,1,0,'Service Pack 3',3,0,'Service Pack 3',3,0,'19000101')
insert into dbo.OS values('6.1.4.SRV','Windows Server 2008 R2 SP4',6,1,0,'Service Pack 4',4,0,'Service Pack 4',3,0,'19000101')

-- TODO: rename this once official name is out
insert into dbo.OS values('6.2.0','Windows 8',6,2,0,'',0,0,'',0,0,'19000101')
insert into dbo.OS values('6.2.1','Windows 8 SP1',6,2,0,'Service Pack 1',1,0,'',0,0,'19000101')
insert into dbo.OS values('6.2.2','Windows 8 SP2',6,2,0,'Service Pack 2',2,0,'',0,0,'19000101')
insert into dbo.OS values('6.2.3','Windows 8 SP3',6,2,0,'Service Pack 3',3,0,'',0,0,'19000101')
insert into dbo.OS values('6.2.4','Windows 8 SP4',6,2,0,'Service Pack 4',4,0,'',0,0,'19000101')
insert into dbo.OS values('6.2.0.SRV','Windows Server 2012',6,2,0,'',0,0,'',3,0,'19000101')
insert into dbo.OS values('6.2.1.SRV','Windows Server 2012 SP1',6,2,0,'Service Pack 1',1,0,'Service Pack 1',3,0,'19000101')
insert into dbo.OS values('6.2.2.SRV','Windows Server 2012 SP2',6,2,0,'Service Pack 2',2,0,'Service Pack 2',3,0,'19000101')
insert into dbo.OS values('6.2.3.SRV','Windows Server 2012 SP3',6,2,0,'Service Pack 3',3,0,'Service Pack 3',3,0,'19000101')
insert into dbo.OS values('6.2.4.SRV','Windows Server 2012 SP4',6,2,0,'Service Pack 4',4,0,'Service Pack 4',3,0,'19000101')
insert into dbo.OS values('6.3.0','Windows 8.1',6,3,0,'',0,0,'',0,0,'19000101')
insert into dbo.OS values('6.3.1','Windows 8.1 SP1',6,3,0,'Service Pack 1',1,0,'',0,0,'19000101')
insert into dbo.OS values('6.3.2','Windows 8.1 SP2',6,3,0,'Service Pack 2',2,0,'',0,0,'19000101')
insert into dbo.OS values('6.3.3','Windows 8.1 SP3',6,3,0,'Service Pack 3',3,0,'',0,0,'19000101')
insert into dbo.OS values('6.3.4','Windows 8.1 SP4',6,3,0,'Service Pack 4',4,0,'',0,0,'19000101')
insert into dbo.OS values('6.3.0.SRV','Windows Server 2012 R2',6,3,0,'',0,0,'',3,0,'19000101')
insert into dbo.OS values('6.3.1.SRV','Windows Server 2012 R2 SP1',6,3,0,'Service Pack 1',1,0,'Service Pack 1',3,0,'19000101')
insert into dbo.OS values('6.3.2.SRV','Windows Server 2012 R2 SP2',6,3,0,'Service Pack 2',2,0,'Service Pack 2',3,0,'19000101')
insert into dbo.OS values('6.3.3.SRV','Windows Server 2012 R2 SP3',6,3,0,'Service Pack 3',3,0,'Service Pack 3',3,0,'19000101')
insert into dbo.OS values('6.3.4.SRV','Windows Server 2012 R2 SP4',6,3,0,'Service Pack 4',4,0,'Service Pack 4',3,0,'19000101')

-- DEFAULT VALUES Deployment_OS

insert into dbo.Deployment_OS ( osID, publishedDate )				values ('6.1.0','20090109')
insert into dbo.Deployment_OS ( osID, publishedDate )				values ('6.2.0','20111101')
insert into dbo.Deployment_OS ( osID, publishedDate )				values ('6.3.0','20130319')

-- DEFAULT VALUES IssueType

INSERT INTO [dbo].[IssueType] VALUES('Iece');
INSERT INTO [dbo].[IssueType] VALUES('Pca');
INSERT INTO [dbo].[IssueType] VALUES('Wer');
INSERT INTO [dbo].[IssueType] VALUES('Sua');
INSERT INTO [dbo].[IssueType] VALUES('Shim');
INSERT INTO [dbo].[IssueType] VALUES('Application');

-- DEFAULT VALUE Providers

INSERT INTO [dbo].[Providers] VALUES('Vendor','Microsoft');
INSERT INTO [dbo].[Providers] VALUES('Internal','');
INSERT INTO [dbo].[Providers] VALUES('Community','');

-- DEFAULT VALUE DeviceType

INSERT INTO [dbo].[DeviceType] VALUES('Display');
INSERT INTO [dbo].[DeviceType] VALUES('Drive');

GO

-- Iece Issues
INSERT INTO [dbo].[Issues] VALUES('IeceBinaryBhvr01','Iece',NULL,0,'IeceBinaryBhvr01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceManageAddOns01','Iece',NULL,0,'IeceManageAddOns01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceMimeHandling01','Iece',NULL,0,'IeceMimeHandling01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceMimeHandling02','Iece',NULL,0,'IeceMimeHandling02',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceMimeHandling03','Iece',NULL,0,'IeceMimeHandling03',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceWindowRestrictions01','Iece',NULL,0,'IeceWindowRestrictions01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceZoneElev01','Iece',NULL,0,'IeceZoneElev01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceObjCache01','Iece',NULL,0,'IeceObjCache01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceControlBlock01','Iece',NULL,0,'IeceControlBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceControlBlock02','Iece',NULL,0,'IeceControlBlock02',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceControlBlock03','Iece',NULL,0,'IeceControlBlock03',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IecePUBlock01','Iece',NULL,0,'IecePUBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceDownloadBlock01','Iece',NULL,0,'IeceDownloadBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceLMZL01','Iece',NULL,0,'IeceLMZL01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceURLCreationFailure01','Iece',NULL,0,'IeceURLCreationFailure01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceIDNNavigation01','Iece',NULL,0,'IeceIDNNavigation01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceSSLNavBlock01','Iece',NULL,0,'IeceSSLNavBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceScriptUrlBlock01','Iece',NULL,0,'IeceScriptUrlBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceScriptUrlBlock02','Iece',NULL,0,'IeceScriptUrlBlock02',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceAntiPhishingBlock01','Iece',NULL,0,'IeceAntiPhishingBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceProtectedMode01','Iece',NULL,0,'IeceProtectedMode01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceSubframeNavigateBlock01','Iece',NULL,0,'IeceSubframeNavigateBlock01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceCreateURLMonikerDifference01','Iece',NULL,0,'IeceCreateURLMonikerDifference01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceCSSFixes01','Iece',NULL,0,'IeceCSSFixes01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceUIPIExtensionBlocked01','Iece',NULL,0,'IeceUIPIExtensionBlocked01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceUIPICrossProcessWM01','Iece',NULL,0,'IeceUIPICrossProcessWM01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)


-- New IECE Events for IE8

INSERT INTO [dbo].[Issues] VALUES('IeceXSSFilterPageSanitize01','Iece',NULL,0,'IeceXSSFilterPageSanitize01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceDEPNX01','Iece',NULL,0,'IeceDEPNX01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceStandardsMode01','Iece',NULL,0,'IeceStandardsMode01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceFileUploadNoPath01','Iece',NULL,0,'IeceFileUploadNoPath01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceCodepageDisabled01','Iece',NULL,0,'IeceCodepageDisabled01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceAjaxNavigation01','Iece',NULL,0,'IeceAjaxNavigation01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceApplicationProtocolDialog01','Iece',NULL,0,'IeceApplicationProtocolDialog01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceFrameNavigationRestricted01','Iece',NULL,0,'IeceFrameNavigationRestricted01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceCrossMIC01','Iece',NULL,0,'IeceCrossMIC01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceImageNotUpgradedToXMLorHTM01','Iece',NULL,0,'IeceImageNotUpgradedToXMLorHTM01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceContentFromProxyDiscarded01','Iece',NULL,0,'IeceContentFromProxyDiscarded01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('IeceCertFilteringDefaultChange01','Iece',NULL,0,'IeceCertFilteringDefaultChange01',NULL,'Rap','Iec Data Collector',2,2,3,0,'20060116 12:16PM',0,1,'20060116 12:16PM',1)

-- PCA Events
INSERT INTO [dbo].[Issues] VALUES('PcaHelpedUserEvent','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',3,3,3,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaDeprecatedComponent','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,4,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaInstallFailure','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,0,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaDWM','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaLaunch16BitApp','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',3,3,3,2,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaFaultTolerantHeap','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,2,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaPinDLL','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,2,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaQuarantine','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaLegacyControlPanelApplet','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaDllLoadFailure','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaQuickAppTermination','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,1,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaUnhandledException','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaWRPAccessError','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,5,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaElevateCreateProcess','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('PcaRegSvr32Failure','Pca',NULL,0,NULL,NULL,'RAP','Pca Data Collector',3,3,3,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)

-- WER Events
INSERT INTO [dbo].[Issues] VALUES('WerApplicationCrash','Wer',NULL,0,NULL,NULL,'RAP','Wer Data Collector',2,2,2,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('WerApplicationHang','Wer',NULL,0,NULL,NULL,'RAP','Wer Data Collector',2,2,2,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)

-- SUA Events
INSERT INTO [dbo].[Issues] VALUES('SuaFileIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaRegistryIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaIniIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaTokenIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaPrivilegeIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaNameSpaceIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaOtherObjectIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaProcessIssue','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',2,2,3,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)
INSERT INTO [dbo].[Issues] VALUES('SuaMitigation','Sua',NULL,0,NULL,NULL,'RAP','Sua Data Collector',3,3,7,1,'20060116 12:16PM',0,1,'20060116 12:16PM',1)

-- Shim Events
INSERT INTO [dbo].[Issues] VALUES('ShimCompatibilityFixEvent','Shim',NULL,0,NULL,NULL,'RAP','Shim Data Collector',3,3,7,7,'20060116 12:16PM',0,1,'20060116 12:16PM',1)


-- Default values for Localized_Issue

INSERT INTO dbo.Localized_Issue VALUES('PcaHelpedUserEvent','Pca',1033,'PCA was invoked to help the user with the application','The Program Compatibility Assistant (PCA) is a feature of Windows that detects common application compatibility issues and offers mitigations to users when possible.  Upon encountering an issue, the PCA will show a dialog to the user indicating that it has detected an issue and will offer to take action to resolve the problem.  To better understand the issue, check the runtime testing application issue log to see if any other issues or feedback correspond are present.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaDeprecatedComponent','Pca',1033,'The application tried to use a deprecated component','The application attempted to load or use a DLL, COM object, shell API, codec, or redistributable not present in Windows.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaInstallFailure','Pca',1033,'The application failed to install or uninstall','The application did not install or uninstall correctly. This may occur if the setup program is not aware of the User Account Control (UAC) feature of Windows and therefore does not run with the full privileges needed to make system changes to protected areas of Windows.  This may also occur if the setup program checks for the Windows version number and blocks itself from running if the version is higher than what it expects.  These issues are commonly mitigated through the use of the RunAsAdmin mode and the Windows XP, Windows Vista, or Windows 7 compatibility modes.  If enabled, the Program Compatibility Assistant will offer to apply these mitigations for the user automatically.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaDWM','Pca',1033,'The application tried to set an 8-bit or 16-bit color mode','In Windows 8, the Desktop Window Manager (DWM) will only support 32-bit colors, with lower color modes now being simulated.  Many older apps and games designed for Windows XP or before use 8-bit or 16-bit color modes and could fail to run properly on Windows 8.  The Program Compatibility Assistant, if enabled, will detect the enumeration or use of lower color modes and ensure that the app works properly with the simulated color mode. This mitigation can also be done manually with use of the DWM8And16BitMitigation shim.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaLaunch16BitApp','Pca',1033,'The application is a 16-bit process','The application is a 16-bit process and will not run on 64-bit Windows.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaFaultTolerantHeap','Pca',1033,'The application requires Fault Tolerant Heap','The application crashed due to a heap issue and the Fault Tolerant Heap was enabled to mitigate the issue.  The mitigation is applied automatically by Windows or can be manually applied using the FaultTolerantHeap shim.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaPinDLL','Pca',1033,'The application referenced an unloaded DLL','Some apps crash because the app dereferences a DLL from memory and then calls a function to execute code in the same DLL. While this is not a problem caused due to Windows 8 compatibility changes, this is a relatively common mistake made by app developers and is seen in a wide variety of apps. The Program Compatibility Assistant, if enabled, will detect this issue and disallow the DLL from being freed from memory to prevent the crash.  This mitigation can also be applied with the use of the IgnoreFreeLibrary compatibility mode.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaQuarantine','Pca',1033,'The application was installed by a legacy installer','The applications installer had an issue that caused the Program Compatibility Assistant to apply a compatibility mode.  After the first use, the user reported an issue with the application to the Program Compatibility Assistant, which applied the same mode to the application itself.  This implies that the application either was not installed correctly or the application has additional compatibility issues.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaLegacyControlPanelApplet','Pca',1033,'Control panel applet does not have a UAC manifest','Control panel applets generally change system settings and therefore need the ability to run as administrator. However, ones written before Windows Vista either do not have an EXE manifest or do not have the TrustInfo section which declares the privilege level they require. Applying the RunAsAdmin compatibility mode will mitigate this issue by forcing the applet to request elevation.  The Program Compatibility Assistant, if enabled, will offer to apply this mitigation automatically for the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaDllLoadFailure','Pca',1033,'The application failed to load a DLL','Some apps designed for Windows XP and prior would ship copies of Windows system DLLs along with their installers. When such an app is installed, the app has an older copy of the DLL in its own folder while the latest version of the DLL is in the Windows system folders.  This condition can cause the app to fail when it tries to load the local DLL.  This can be mitigated by the LoadLibraryRedirect compatibility mode which will redirect loading of locally shipped versions of system DLLs to the versions in the Windows system folders.  The Program Compatibility Assistant, if enabled, will automatically apply this mitigation for the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaQuickAppTermination','Pca',1033,'The application exits shortly after launching','The application exits shortly after launching.  This may be due to a version check by the application or a check for administrator privilege.  The issue can frequently be mitigated through the use of a version lie or the ForceAdminAccess shim.  If enabled, the Program Compatibility Assistant will offer to apply the appropriate mitigations for the user automatically.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaUnhandledException','Pca',1033,'Unhandled callback exception','Before Windows 7, an exception produced by a native 64-bit application would be lost if it transited a user-kernel-user boundary.  Starting with Windows 7, the exception is not lost and must be handled or the application will crash.   To fix the issue, either the application can be modified to properly handle the exception or the DisableUserCallbackException compatibility mode can be applied to return the behavior to as it was before Windows 7.  The Program Compatibility Assistant, if enabled, will automatically apply this mitigation for the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaWRPAccessError','Pca',1033,'The application failed to write or delete due to WRP','Some apps designed for Windows XP and prior assume that they usually run with full administrative privileges and may try to modify, delete or write Windows protected files (either in program files or Windows folders) or registry keys owned by Windows. When any of the write, delete, or modify operations for a file or a registry key fails many such apps can crash.  The issue can be mitigated by applying the WRPMitigation compatibility mode.  The Program Compatibility Assistant, if enabled, will automatically offer to apply this mitigation for the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaElevateCreateProcess','Pca',1033,'Child process failed to launch elevated','Apps may launch child processes that need to run as administrator, such as when launching an updater. The child program can fail to launch if the app itself did not have administrative privileges or if the child program was not properly marked for elevation with the UAC manifest.  Applying the ElevateCreateProcess mode will mitigate this problem and allow the child program to request elevation.  The Program Compatibility Assistant, if enabled, will automatically apply this mitigation for the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('PcaRegSvr32Failure','Pca',1033,'The application failed using regsvr32.exe','The application failed to register all of its COM objects successfully using regsvr32.exe.  This does not indicate that the app failed, but any dependent applications may not find the COM objects they need.','','','','','20060117')

INSERT INTO dbo.Localized_Issue VALUES('WerApplicationCrash','Wer',1033,'The application experienced a crash','An application crash occurs when an application ceases to work correctly and exits due to an error.  This can occur for a variety of reasons and may indicate a compatibility problem.  Given the variety of potential reasons, an application crash by itself may not be cause for further investigation, but repeated crashes alongside other issues would indicate a problem worth investigating.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('WerApplicationHang','Wer',1033,'The application experienced a hang','An application hang occurs when the application does not respond to inputs for an extended period of time.  This can occur for a variety of reasons and may indicate a compatibility problem.  Given the variety of potential reasons, an application hang by itself may not be cause for further investigation, but repeated hangs alongside other issues would indicate a problem worth investigating.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaFileIssue','Sua',1033,'Attempted access or write to restricted files','Standard User Analyzer identified issues with accessing the file system, such as writing to a file that can be accessed only by Administrators.  These issues would cause an application to either fail or not function correctly for a user running under a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaRegistryIssue','Sua',1033,'Attempted access or write to restricted registry keys','Standard User Analyzer identified registry keys in restricted locations that the application attempts to access.  An application attempting to write to a registry key under HKLM, which is a location that normally only administrators can access.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaIniIssue','Sua',1033,'The application attempted to use WriteProfile APIs','Standard User Analyzer identified usage of the WriteProfile APIs, a set of APIs originally used for 16-bit Windows.  For example,WriteProfile is used by Calc.exe in Windows XP to write to the Windows\Win.ini file, which is writeable only by Administrators and would therefore fail for a user running under a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaTokenIssue','Sua',1033,'Explicit check for access in the user access token','Standard User Analyzer identified access-token checking issues, such as an application explicitly checking for the Builtin\Administrators security identifier (SID) in the user access token. This typically indicates that an application will not work for a user running under a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaPrivilegeIssue','Sua',1033,'The application attempted to set restricted privileges','Standard User Analyzer identified privilege issues, such as an application explicitly enabling SeDebugPrivilege, that will not work for a user running under a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaNameSpaceIssue','Sua',1033,'Attempted to create system objects in restricted namespace','Standard User Analyzer identified  issues with the application in which it attempts to create a new system object, such as an event or a memory map, in a restricted namespace. This action will not function for a user running under a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaOtherObjectIssue','Sua',1033,'The application attempted to access restricted objects','Standard User Analyzer identified objects other than files or the registry that the application attempts to access, but would fail for a user running as a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaProcessIssue','Sua',1033,'The application has process elevation issues','Standard User Analyzer identified process elevation issues, such as the use of the CreateProcess API, that will fail for a user running under a Windows Standard User account.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('SuaMitigation','Sua',1033,'The application has recommended compatibility fixes','Standard User Analyzer identified a set of compatibility fixes, which can be used to mitigate compatibility issues.  To better understand the impact of these suggested mitigations, you can test the mitigations using Compatibility Administrator.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('ShimCompatibilityFixEvent','Shim',1033,'A compatibility fix was applied to the application','The application was run with a compatibility fix enabled, which can be used to work around compatibility issues.  The presence of an applied fix does not indicate a problem, but can be used to understand what fixes were used to resolve a problem.','','','','','20060117')

INSERT INTO dbo.Localized_Issue VALUES('IeceBinaryBhvr01','Iece',1033,'Binary Behaviors Restrictions','Internet Explorer contains dynamic binary behaviors: components attached to HTML elements, which encapsulate specific functionality. Internet Explorer security settings do not control binary behaviors, so the components can work on Web pages in the Restricted sites zone. The Binary Behavior Restriction security feature disables the binary behavior in the Restricted sites zone by default. In combination with the Local Machine Lockdown security feature, you require administrative approval for binary behaviors to run in the Local Machine zone by default.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceManageAddOns01','Iece',1033,'ActiveX Opt-In','To reduce the surface area of attacks in Internet Explorer that involve ActiveX controls, a user will have to opt-in to use an ActiveX control for the first time. IE will create a log into Windows Event Viewer when an ActiveX control is blocked and a user needs to enable it.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceMimeHandling01','Iece',1033,'MIME Handling Restrictions','Internet Explorer uses Multipurpose Internet Mail Extensions (MIME) type information to decide how to handle files sent by a Web server. For example, when Internet Explorer receives a .jpg file, the user sees the file in an Internet Explorer window. If Internet Explorer receives an executable (.exe) file, it generally prompts the user for a decision on how to handle the file. The MIME Handling Restriction security feature protects users from accidentally downloading or executing a dangerous file because of misleading MIME or file name extension information.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceMimeHandling02','Iece',1033,'MIME Handling Restrictions','Internet Explorer uses Multipurpose Internet Mail Extensions (MIME) type information to decide how to handle files sent by a Web server. For example, when Internet Explorer receives a .jpg file, the user sees the file in an Internet Explorer window. If Internet Explorer receives an executable (.exe) file, it generally prompts the user for a decision on how to handle the file. The MIME Handling Restriction security feature protects users from accidentally downloading or executing a dangerous file because of misleading MIME or file name extension information.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceMimeHandling03','Iece',1033,'MIME Handling Restrictions','Internet Explorer uses Multipurpose Internet Mail Extensions (MIME) type information to decide how to handle files sent by a Web server. For example, when Internet Explorer receives a .jpg file, the user sees the file in an Internet Explorer window. If Internet Explorer receives an executable (.exe) file, it generally prompts the user for a decision on how to handle the file. The MIME Handling Restriction security feature protects users from accidentally downloading or executing a dangerous file because of misleading MIME or file name extension information.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceWindowRestrictions01','Iece',1033,'Windows Restrictions','Internet Explorer 7 for Windows XP with Service Pack 2 and Windows Vista places some restrictions on windows to prevent hidden information and user-interface spoofing. Internet Explorer Window Restrictions are designed to prevent a scripted window from obscuring the Internet Explorer title bar, address bar, and status bar. Window Restrictions affect several Dynamic HTML (DHTML) scripting commands, for example open  with chrome (http://go.microsoft.com/fwlink?linkid=50837) and window.createPopup chromeless (http://go.microsoft.com/fwlink?linkid=50833) methods.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceZoneElev01','Iece',1033,'Zone Elevation Restrictions','Zone Elevation Restrictions prevent the overall security context of any link on a page from being higher than the security context of the root URL. This means, for example, that a page in the Internet zone cannot navigate to a page in the Local intranet zone, except as the result of a user-initiated action. A script cannot automatically make this sort of navigation without user interaction, such as a mouse click or a keystroke. Zone Elevation Restrictions also disables JavaScript navigation if there is no security context.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceObjCache01','Iece',1033,'Object Caching Protection','In Internet Explorer 6 for Windows XP with Service Pack 2 (SP2) and in Windows Internet Explorer 7, a reference to an object is no longer accessible when the user browses to a new domain. There is a new security context on all scriptable objects so that access to all cached objects is blocked. Additionally, access is blocked when browsing within the same domain (fully qualified domain name). A reference to an object is no longer accessible after the context has changed due to navigation.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceControlBlock01','Iece',1033,'Bad Certificate ActiveX Blocking','An ActiveX control is a reusable software component based on Microsoft ActiveX technology that is used to add interactivity and more functionality, such as animation or a pop-up menu, to a webpage, applications, and software development tools. In Internet Explorer 7 and Internet Explorer 6 for Windows XP with Service Pack 2 (SP2), the modal installation prompt for ActiveX controls is initially suppressed. Users can allow installation through a security user-interface (UI) element called the Information Bar. Internet Explorer also blocks controls that are unsigned, invalid, or explicitly distrusted by the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceControlBlock02','Iece',1033,'Bad Certificate ActiveX Blocking','An ActiveX control is a reusable software component based on Microsoft ActiveX technology that is used to add interactivity and more functionality, such as animation or a pop-up menu, to a webpage, applications, and software development tools. In Internet Explorer 7 and Internet Explorer 6 for Windows XP with Service Pack 2 (SP2), the modal installation prompt for ActiveX controls is initially suppressed. Users can allow installation through a security user-interface (UI) element called the Information Bar. Internet Explorer also blocks controls that are unsigned, invalid, or explicitly distrusted by the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceControlBlock03','Iece',1033,'Bad Certificate ActiveX Blocking','An ActiveX control is a reusable software component based on Microsoft ActiveX technology that is used to add interactivity and more functionality, such as animation or a pop-up menu, to a webpage, applications, and software development tools. In Internet Explorer 7 and Internet Explorer 6 for Windows XP with Service Pack 2 (SP2), the modal installation prompt for ActiveX controls is initially suppressed. Users can allow installation through a security user-interface (UI) element called the Information Bar. Internet Explorer also blocks controls that are unsigned, invalid, or explicitly distrusted by the user.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IecePUBlock01','Iece',1033,'Pop-Up Blocking','The Pop-up Blocking feature blocks pop-up (and pop-under) windows, initiated automatically from a Web site. Internet Explorer blocks Pop-up windows in the Internet and Restricted sites zones, by default; however, the Pop-up Blocker allows pop-up windows initiated by a user action. Users can configure Internet Explorer 7 for Windows XP with Service Pack 2 and Windows Vista to be more or less restrictive. Users can also turn off the Pop-up Blocker altogether.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceDownloadBlock01','Iece',1033,'Automatic Download Blocking','Automatic Download Blocking provides the automatic suppression of file download dialog boxes that are not the result of a user action, such as a mouse click or keystroke. When a dialog box is automatically blocked, an Information Bar appears at the top of the window. Users have the option to download the blocked dialog box by clicking the Information Bar.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceLMZL01','Iece',1033,'Local Machine Zone Lockdown (LMZL)','Local Machine zone lockdown secures the Local Machine zone by tightening restrictions on several URL actions. Any time one of these URL actions is attempted, a new security user interface (UI) element, called the Information Bar, appears. The user can click the Information Bar to remove the lockdown from the restricted content.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceURLCreationFailure01','Iece',1033,'Centralized URL Parsing (CURL): Illegal URL construction','To help stop exploits that involve fooling IE with an malformed URL, IE7 will parse URLs and make sure they meets RFC guidelines. URL construction failed because the URL entered (or that the browser is being asked to navigate to) does not conform to RFC guidelines.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceIDNNavigation01','Iece',1033,'International Domain Names (IDN) Support','IE7 supports internationalized domain names (IDN). IE will create a log each time a domain name is changed to a punycode hostname and will log the hostname.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceSSLNavBlock01','Iece',1033,'Secure Sockets Layer (SSL) certificate error','HTTPS uses encryption to secure your Internet traffic to protect it from snooping or tampering by others on the network. HTTPS uses either the Secure Sockets Layer (SSL) or the Transport Layer Security (TLS) protocols to protect data. Security improvements in the Secure Sockets Layer (SSL) have detected errors in the site`s security certificate.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceScriptUrlBlock01','Iece',1033,'Cross Domain Barrier: Redirection Blocking','IE7 will block a redirected navigation in DOM objects if there is a threat of cross-domain exploit. If a redirected navigation is blocked, IE7 will log the URL that was blocked.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceScriptUrlBlock02','Iece',1033,'Cross Domain Barrier: Script Blocking','IE7 has invested heavily in blocking cross-domain script execution. IE7 will block a script URL if there is a threat. When a script URL is blocked IE7 will log both the URL that was calling the script URL and the script URL itself.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceAntiPhishingBlock01','Iece',1033,'Anti-Phishing','For user protection, IE7 introduces a feature called "Phishing Filter." Phishing Filter can block a Web site if the site has been reported as a phishing site, or it can warn users of a site if it has characteristics that are common to phishing Web sites. IE7 will log whether a Web site is reported as being a suspected phishing Web site. If you find that your Web sites are flagged incorrectly as phishing sites you can go to the Tools menu and report that your site is not a phishing Web site.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceProtectedMode01','Iece',1033,'Protected Mode Blocking','On Windows Vista, IE runs at a lower integrity level to protect users against a variety of attacks. Protected mode IE will restrict writes to registry and file systems. IE7 will log information when a write access has been denied or has been virtualized to a different location.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceSubframeNavigateBlock01','Iece',1033,'Cross Frame Navigation','As of version 7, IE blocks navigations when one IE window/frame tries to access and navigate another frame and does not have access to it.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceCreateURLMonikerDifference01','Iece',1033,'Centralized URL Parsing (CURL): Resolution changes from IE6','To help stop exploits that involve fooling IE with a malformed URL, IE7 will parse URLs and make sure they meets RFC guidelines. The URL created or parsed in IE7 is different than it would have been in IE6. This is a warning - the URL was not blocked.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceCSSFixes01','Iece',1033,'Cascading Style Sheet (CSS) Fixes','IE7 offers a much-improved rendering engine due to fixes made for several major CSS issues that developers had been forced to work around with various hacks. IE7 eliminates the need for many of those workarounds and, consequently, any site that employs a workaround might experience some rendering or layout issues. To help developers discover these sites and their workarounds, IE7 will create a log whenever it discovers a workaround that has been rendered unnecessary by the improvements in IE7.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceUIPIExtensionBlocked01','Iece',1033,'Cross Domain Barrier: Redirection Blocking','IE7 will block a redirected navigation in DOM objects if there is a threat of cross-domain exploit. If a redirected navigation is blocked, IE7 will log the URL that was blocked.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceUIPICrossProcessWM01','Iece',1033,'UIPI Cross Process Window Message','User Interface Privilege Isolation (UIPI) prevents application processes running with lower privileges from using Windows messages to send information to a higher privilege process. For example, if you are running as a Limited User, Windows Internet Explorer 8 and Internet Explorer 7 prevent Web sites from sending messages to the Microsoft Management Console (MMC) or an Administrative Control Panel (CPL). Without this prevention, application processes can inject hostile information without requiring user interaction.','','','','','20060117')

INSERT INTO dbo.Localized_Issue VALUES('IeceXSSFilterPageSanitize01','Iece',1033,'Cross-Site Scripting Filter','IE8 will detect and mitigate a cross-site scripting (XSS) attack.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceDEPNX01','Iece',1033,'Data Execution Prevention/No Execute (DEP/NX)','IE8 will prevent code from running in non-executable memory.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceStandardsMode01','Iece',1033,'Standards Mode Engine','IE8 will include an update to the Standard Mode Engine.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceFileUploadNoPath01','Iece',1033,'File Name Restriction','IE8 form submission has been changed so that a FILE UPLOAD control only submits the file path to the server.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceCodepageDisabled01','Iece',1033,'Codepage Sniffing','IE8 will prevent certain codepages from participating in its Codepage Sniffing heuristic.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceAjaxNavigation01','Iece',1033,'AJAX Navigation','IE8 will have a new HTML5.0 AJAX navigation feature that allows sites to maintain and track changes in AJAX states by treating them as a navigation.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceApplicationProtocolDialog01','Iece',1033,'Application Protocol','IE8 will display a dialog before starting an application that is registered to handle an application protocol.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceFrameNavigationRestricted01','Iece',1033,'Windows Reuse Navigation Restriction','To help prevent spoofing attacks, IE8 will prevent a top-level frame owned by one Web site from being navigated by another Web site from a different security context.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceImageNotUpgradedToXMLorHTM01','Iece',1033,'MIME Sniffing Restrictions: no IMAGE elevation to HTML','IE8 will use Multipurpose Internet Mail Extensions (MIME) information to determine how to handle files sent by a Web server.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceCertFilteringDefaultChange01','Iece',1033,'Certificate Filtering','IE8 and IE7 use Certificate Filtering to select the appropriate certificate for client authentication. In IE8, this feature has been improved to remove certificates that are likely to be rejected by the server. For instance, explicitly untrusted certificate chains or certificates not associated with a private key will not show up in the list.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceContentFromProxyDiscarded01','Iece',1033,'Web Proxy Error Handling Changes','IE8 will block content returned by a proxy from a failed CONNECT command, or display the content in a context based on the hostname of the proxy rather than in the context of the origin server.','','','','','20060117')
INSERT INTO dbo.Localized_Issue VALUES('IeceCrossMIC01','Iece',1033,'Internet at Medium Integrity Level','IE8 helps protect users from attack by running an IEprocess with greatly restricted privileges on Windows Vista. In IE8, browsing intranet Web sites occurs at the medium integrity level. At this level, processes have user level system privileges and can write to user-specific areas of the registry. In IE7, browsing intranet Web sites operates with untrusted system privileges and writes only to specific low-integrity locations.','','','','','20060117')

INSERT INTO Issue_Affects_Os VALUES('PcaHelpedUserEvent','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDeprecatedComponent','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaInstallFailure','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaLaunch16BitApp','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaFaultTolerantHeap','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaPinDLL','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaQuarantine','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaLegacyControlPanelApplet','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDllLoadFailure','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaQuickAppTermination','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaUnhandledException','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaWRPAccessError','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaElevateCreateProcess','Pca','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('PcaRegSvr32Failure','Pca','6.1.0')

INSERT INTO Issue_Affects_Os VALUES('WerApplicationCrash','Wer','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('WerApplicationHang','Wer','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaFileIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaRegistryIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaIniIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaTokenIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaPrivilegeIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaNameSpaceIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaOtherObjectIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaProcessIssue','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('SuaMitigation','Sua','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('ShimCompatibilityFixEvent','Shim','6.1.0')

INSERT INTO Issue_Affects_Os VALUES('IeceBinaryBhvr01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceManageAddOns01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling02','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling03','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceWindowRestrictions01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceZoneElev01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceObjCache01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock02','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock03','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IecePUBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceDownloadBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceLMZL01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceURLCreationFailure01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceIDNNavigation01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceSSLNavBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceScriptUrlBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceScriptUrlBlock02','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceAntiPhishingBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceProtectedMode01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceSubframeNavigateBlock01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCreateURLMonikerDifference01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCSSFixes01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceUIPIExtensionBlocked01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceUIPICrossProcessWM01','Iece','6.1.0')


INSERT INTO Issue_Affects_Os VALUES('IeceXSSFilterPageSanitize01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceDEPNX01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceStandardsMode01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceFileUploadNoPath01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCodepageDisabled01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceAjaxNavigation01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceApplicationProtocolDialog01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceFrameNavigationRestricted01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceImageNotUpgradedToXMLorHTM01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceContentFromProxyDiscarded01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCertFilteringDefaultChange01','Iece','6.1.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCrossMIC01','Iece','6.1.0')

INSERT INTO Issue_Affects_Os VALUES('PcaHelpedUserEvent','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDeprecatedComponent','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaInstallFailure','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDWM','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaLaunch16BitApp','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaFaultTolerantHeap','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaPinDLL','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaQuarantine','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaLegacyControlPanelApplet','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDllLoadFailure','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaQuickAppTermination','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaUnhandledException','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaWRPAccessError','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaElevateCreateProcess','Pca','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('PcaRegSvr32Failure','Pca','6.2.0')

INSERT INTO Issue_Affects_Os VALUES('WerApplicationCrash','Wer','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('WerApplicationHang','Wer','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaFileIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaRegistryIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaIniIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaTokenIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaPrivilegeIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaNameSpaceIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaOtherObjectIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaProcessIssue','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('SuaMitigation','Sua','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('ShimCompatibilityFixEvent','Shim','6.2.0')

INSERT INTO Issue_Affects_Os VALUES('IeceXSSFilterPageSanitize01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceDEPNX01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceStandardsMode01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceFileUploadNoPath01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCodepageDisabled01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceAjaxNavigation01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceApplicationProtocolDialog01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceFrameNavigationRestricted01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceImageNotUpgradedToXMLorHTM01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceContentFromProxyDiscarded01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCertFilteringDefaultChange01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCrossMIC01','Iece','6.2.0')

INSERT INTO Issue_Affects_Os VALUES('IeceBinaryBhvr01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceManageAddOns01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling02','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling03','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceWindowRestrictions01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceZoneElev01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceObjCache01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock02','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock03','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IecePUBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceDownloadBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceLMZL01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceURLCreationFailure01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceIDNNavigation01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceSSLNavBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceScriptUrlBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceScriptUrlBlock02','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceAntiPhishingBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceProtectedMode01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceSubframeNavigateBlock01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCreateURLMonikerDifference01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCSSFixes01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceUIPIExtensionBlocked01','Iece','6.2.0')
INSERT INTO Issue_Affects_Os VALUES('IeceUIPICrossProcessWM01','Iece','6.2.0')

INSERT INTO Issue_Affects_Os VALUES('PcaHelpedUserEvent','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDeprecatedComponent','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaInstallFailure','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDWM','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaLaunch16BitApp','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaFaultTolerantHeap','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaPinDLL','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaQuarantine','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaLegacyControlPanelApplet','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaDllLoadFailure','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaQuickAppTermination','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaUnhandledException','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaWRPAccessError','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaElevateCreateProcess','Pca','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('PcaRegSvr32Failure','Pca','6.3.0')

INSERT INTO Issue_Affects_Os VALUES('WerApplicationCrash','Wer','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('WerApplicationHang','Wer','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaFileIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaRegistryIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaIniIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaTokenIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaPrivilegeIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaNameSpaceIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaOtherObjectIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaProcessIssue','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('SuaMitigation','Sua','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('ShimCompatibilityFixEvent','Shim','6.3.0')

INSERT INTO Issue_Affects_Os VALUES('IeceXSSFilterPageSanitize01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceDEPNX01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceStandardsMode01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceFileUploadNoPath01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCodepageDisabled01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceAjaxNavigation01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceApplicationProtocolDialog01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceFrameNavigationRestricted01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceImageNotUpgradedToXMLorHTM01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceContentFromProxyDiscarded01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCertFilteringDefaultChange01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCrossMIC01','Iece','6.3.0')

INSERT INTO Issue_Affects_Os VALUES('IeceBinaryBhvr01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceManageAddOns01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling02','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceMimeHandling03','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceWindowRestrictions01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceZoneElev01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceObjCache01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock02','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceControlBlock03','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IecePUBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceDownloadBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceLMZL01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceURLCreationFailure01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceIDNNavigation01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceSSLNavBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceScriptUrlBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceScriptUrlBlock02','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceAntiPhishingBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceProtectedMode01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceSubframeNavigateBlock01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCreateURLMonikerDifference01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceCSSFixes01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceUIPIExtensionBlocked01','Iece','6.3.0')
INSERT INTO Issue_Affects_Os VALUES('IeceUIPICrossProcessWM01','Iece','6.3.0')

--Default Architecture Preferences (enabled)
INSERT INTO [dbo].[Architecture_Preferences] VALUES (1, 0, 1)

-- Client State Details
INSERT INTO [dbo].[Client_State_Details] VALUES ('1','19000101 00:00:00.000','19000101 00:00:00.000','19000101 00:00:00.000','19000101 00:00:00.000','19000101 00:00:00.000','19000101 00:00:00.000')

-- ACT Database Version
INSERT INTO [dbo].[Version] VALUES ('20100324', '5.0.1.016', '6.1.0.0', '5.1.004', binary_checksum(@@servername + suser_sname() + db_name() + Convert(varchar(255), newid())), 'Microsoft', GetDate(), 0)

GO

