-----------------------------------------------------------------
-- Drop report event stored procedure
--------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[report_event]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[report_event]
GO

--------------------------------------------------------------------------
-- Drop accounting data table
--------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[accounting_data]') AND type in (N'U'))
DROP TABLE [dbo].[accounting_data]
GO

--------------------------------------------------------------------------
-- Drop custom data types
--------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ipaddress' AND ss.name = N'dbo')
DROP TYPE [dbo].[ipaddress]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ipv6address' AND ss.name = N'dbo')
DROP TYPE [dbo].[ipv6address]
GO

-------------------------------------------------------------------------
-- Create custom data types
-------------------------------------------------------------------------
CREATE TYPE [dbo].[ipaddress] FROM [nvarchar](15) NOT NULL
GO

CREATE TYPE [dbo].[ipv6address] FROM [nvarchar](39) NOT NULL
GO

--------------------------------------------------------------------------- 
-- Create accounting data table
---------------------------------------------------------------------------
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[accounting_data](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[timestamp] [datetime] NOT NULL,
	[Computer_Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Packet_Type] [int] NOT NULL,
	[User_Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Client_IP_Address] [dbo].[ipaddress] NULL,
	[Fully_Qualified_Machine_Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NP_Policy_Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MS_Quarantine_State] [int] NULL,
	[MS_Extended_Quarantine_State] [int] NULL,
	[System_Health_Result] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[System_Health_ResultEx] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MS_Network_Access_Server_Type] [int] NULL,
	[Called_Station_Id] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MS_Quarantine_Grace_Time] [datetime] NULL,
	[MS_Quarantine_User_Class] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Client_IPv6_Address] [dbo].[ipv6address] NULL,
	[Not_Quarantine_Capable] [int] NULL,
	[AFW_Zone] [int] NULL,
	[AFW_Protection_Level] [int] NULL,
	[Quarantine_Update_Non_Compliant] [int] NULL,
	[MS_Machine_Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OS_Version] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MS_Quarantine_Session_Id] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT [PK_accounting_data] PRIMARY KEY CLUSTERED ([id] ASC) 
	WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-----------------------------------------------------------------------------------------
-- Create report event stored procedure
-----------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[report_event]
   @doc ntext
AS

SET NOCOUNT ON

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

/*

Combine multiple System-Health-Result string into one string with delimiter as ":"

*/

DECLARE @SystemHealthResult NVARCHAR(4000)
SELECT @SystemHealthResult = COALESCE(@SystemHealthResult+':','')+ISNULL(SHR.System_Health_Result,'') 
FROM (SELECT *
FROM OPENXML(@idoc, '/Event/System-Health-Result')
WITH (System_Health_Result NVARCHAR(4000) 'text()')) AS SHR

/*

Combine multiple System-Health-ResultEx string into one string

*/

DECLARE @SystemHealthResultEx NVARCHAR(MAX)
IF @SystemHealthResult IS NOT NULL SELECT @SystemHealthResultEx = COALESCE(@SystemHealthResultEx,'')+ISNULL(CAST(SHR.System_Health_ResultEx AS NVARCHAR(MAX)),'') 
FROM (SELECT *
FROM OPENXML(@idoc, '/Event/System-Health-ResultEx')
WITH (System_Health_ResultEx xml '.')) AS SHR


/*

All RADIUS attributes written to the ODBC format logfile are declared here. Refer to IAS ODBC Formatted Log Files in Online Help for information on interpreting these values.

*/

INSERT accounting_data
SELECT
	Timestamp,
	Computer_Name,
	Packet_Type,
	[User_Name],
	Client_IP_Address,
	Fully_Qualified_Machine_Name,
	NP_Policy_Name,
	MS_Quarantine_State,
	MS_Extended_Quarantine_State,        
	@SystemHealthResult,
	@SystemHealthResultEx,
	MS_Network_Access_Server_Type,
	Called_Station_Id,
	MS_Quarantine_Grace_Time,
	MS_Quarantine_User_Class,
	Client_IPv6_Address,
	Not_Quarantine_Capable,
	AFW_Zone,
	AFW_Protection_Level,
	Quarantine_Update_Non_Compliant,
	MS_Machine_Name,
    	OS_Version,
	MS_Quarantine_Session_Id
FROM OPENXML(@idoc, '/Event')
WITH (
	Timestamp datetime './Timestamp',
	Computer_Name nvarchar(255) './Computer-Name',
	Packet_Type int './Packet-Type',
	[User_Name] nvarchar(255) './User-Name',
	Client_IP_Address nvarchar(15) './Client-IP-Address',
	Fully_Qualified_Machine_Name nvarchar(255) './Fully-Qualified-Machine-Name', 
	NP_Policy_Name nvarchar(255) './NP-Policy-Name',
	MS_Quarantine_State int './MS-Quarantine-State',
	MS_Extended_Quarantine_State int './MS-Extended-Quarantine-State',
	System_Health_Result nvarchar(4000),
	System_Health_ResultEx nvarchar(MAX),
	MS_Network_Access_Server_Type int './MS-Network-Access-Server-Type',
	Called_Station_Id nvarchar(255) './Called-Station-Id',
	MS_Quarantine_Grace_Time datetime './MS-Quarantine-Grace-Time',
	MS_Quarantine_User_Class nvarchar(255) './MS-Quarantine-User-Class',
	Client_IPv6_Address nvarchar(32) './Client-IPv6-Address',
	Not_Quarantine_Capable int './Not-Quarantine-Capable',
	AFW_Zone int './AFW-Zone',
	AFW_Protection_Level int './AFW-Protection-Level',
	Quarantine_Update_Non_Compliant int './Quarantine-Update-Non-Compliant',
	MS_Machine_Name nvarchar(255) './MS-Machine-Name',
	OS_Version nvarchar(255) './Machine-Inventory',
	MS_Quarantine_Session_Id nvarchar(255) './MS-Quarantine-Session-Id'
	)

EXEC sp_xml_removedocument @idoc

SET NOCOUNT OFF
GO
