CREATE TABLE [dbo].[host] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[fqdn] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip] [varchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
	[os] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[first_seen] [datetime] NULL ,
	[last_seen] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hopper_host] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[fqdn] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[os] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[first_seen] [datetime] NULL ,
	[last_seen] [datetime] NULL 
) ON [PRIMARY]
GO




CREATE TABLE [dbo].[port] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[host_id] [int] NOT NULL ,
	[port] [int] NOT NULL ,
	[protocol] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[service] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[first_seen] [datetime] NULL ,
	[last_seen] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hopper_port] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[ip] [varchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
	[port] [int] NOT NULL ,
	[protocol] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[service] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[first_seen] [datetime] NULL ,
	[last_seen] [datetime] NULL 
) ON [PRIMARY]
GO






CREATE  PROCEDURE dbo.sp_tracer AS
DECLARE @group_insert INT
DECLARE @group_update INT
DECLARE @counter INT
DECLARE @host_id INT
DECLARE @hh_id INT
DECLARE @hh_ip VARCHAR(15) 
DECLARE @hh_fqdn VARCHAR(255) 
DECLARE @hh_os VARCHAR(255) 
DECLARE @hh_first_seen DATETIME
DECLARE @hh_last_seen DATETIME
DECLARE @port_id INT 
DECLARE @hh_port INT
DECLARE @hh_protocol CHAR(1)
DECLARE @hh_service VARCHAR(255)



-- populate host
SELECT @hh_id = MIN(id) FROM hopper_host
WHILE @hh_id IS NOT NULL 
BEGIN
	SELECT @hh_ip = ip, @hh_fqdn = fqdn, @hh_os = os, @hh_first_seen = first_seen, @hh_last_seen = last_seen FROM hopper_host WHERE id = @hh_id
	SELECT @host_id = id FROM host WHERE ip = @hh_ip
	
	IF @host_id IS NULL BEGIN
		INSERT INTO host (fqdn, ip, os, first_seen, last_seen) VALUES (@hh_fqdn, @hh_ip, @hh_os, @hh_first_seen, @hh_last_seen)
	END
	ELSE BEGIN
		UPDATE host SET fqdn = @hh_fqdn, os = @hh_os, last_seen = @hh_last_seen WHERE id = @host_id AND last_seen <= @hh_last_seen
	END
	SET @host_id = NULL

	SELECT @hh_id = MIN(id) FROM hopper_host WHERE id > @hh_id
END
DELETE FROM hopper_host

-- populate port
SELECT @hh_id = MIN(id) FROM hopper_port
WHILE @hh_id IS NOT NULL 
BEGIN
	SELECT @hh_ip = ip, @hh_port = port, @hh_protocol = protocol, @hh_service = service, @hh_first_seen = first_seen, @hh_last_seen = last_seen FROM hopper_port WHERE id = @hh_id
	SELECT @host_id = id FROM host WHERE ip = @hh_ip
	SELECT @port_id = id FROM port WHERE host_id = @host_id AND port = @hh_port
	
	
	IF @port_id IS NULL BEGIN
		INSERT INTO port (host_id, port, protocol, service, first_seen, last_seen) VALUES (@host_id, @hh_port, @hh_protocol, @hh_service, @hh_first_seen, @hh_last_seen)
	END
	ELSE BEGIN
		UPDATE port SET protocol = @hh_protocol, service = @hh_service, last_seen = @hh_last_seen WHERE id = @port_id AND last_seen <= @hh_last_seen
	END
	SET @port_id = NULL

	SELECT @hh_id = MIN(id) FROM hopper_port WHERE id > @hh_id
END
DELETE FROM hopper_port

GO