NPS Stored Procedure
USE [NPSODBC]
GO
/****** Object:  StoredProcedure [dbo].[report_event]    Script Date: 02/06/2012 11:40:47 ******/
SET ANSI_NULLS OFF
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
/*
    All RADIUS attributes written to the ODBC format logfile are declared here. 
    One additional attribute is added: @record_timestamp.
    The value of @record_timestamp is the UTC time the record was inserted in the database.
    Refer to IAS-Formatted Log Files in Online Help on www.technet.com for information on interpreting these values.
*/
DECLARE @record_timestamp datetime
SET @record_timestamp = GETUTCDATE()
INSERT accounting_data
SELECT
    @record_timestamp,
    Computer_Name,
    Packet_Type,
    [User_Name],
    F_Q_User_Name,
    Called_Station_Id,
    Calling_Station_Id,
    Callback_Number,
    Framed_IP_Address,
    NAS_Identifier,
    NAS_IP_Address,
    NAS_Port,
    Client_Vendor,
    Client_IP_Address,
    Client_Friendly_Name,
    Event_Timestamp,
    Port_Limit,
    NAS_Port_Type,
    Connect_Info,
    Framed_Protocol,
    Service_Type,
    Authentication_Type,
    NP_Policy_Name,
    Reason_Code,
    Class,
    Session_Timeout,
    Idle_Timeout,
    Termination_Action,
    EAP_Friendly_Name,
    Acct_Status_Type,
    Acct_Delay_Time,
    Acct_Input_Octets,
    Acct_Output_Octets,
    Acct_Session_Id,
    Acct_Authentic,
    Acct_Session_Time,
    Acct_Input_Packets,
    Acct_Output_Packets,
    Acct_Terminate_Cause,
    Acct_Multi_Session_Id,
    Acct_Link_Count,
    Acct_Interim_Interval,
    Tunnel_Type,
    Tunnel_Medium_Type,
    Tunnel_Client_Endpoint,
    Tunnel_Server_Endpoint,
    Acct_Tunnel_Connection,
    Tunnel_Pvt_Group_Id,
    Tunnel_Assignment_Id,
    Tunnel_Preference,
    MS_Acct_Auth_Type,
    MS_Acct_EAP_Type,
    MS_RAS_Version,
    MS_RAS_Vendor,
    MS_CHAP_Error,
    MS_CHAP_Domain,
    MS_MPPE_Encryption_Types,
    MS_MPPE_Encryption_Policy,
    Proxy_Policy_Name,
    Provider_Type,
    Provider_Name,
    Remote_Server_Address,
    MS_RAS_Client_Name,
    MS_RAS_Client_Version,
/*
    NAP-specific information, available from NPS starting with Windows Server 2008.
*/
    MS_Quarantine_State
FROM OPENXML(@idoc, '/Event')
WITH (
    Computer_Name nvarchar(255) './Computer-Name',
    Packet_Type int './Packet-Type',
    [User_Name] nvarchar(255) './User-Name',
    F_Q_User_Name nvarchar(255) './Fully-Qualifed-User-Name',
    Called_Station_Id nvarchar(255) './Called-Station-Id',
    Calling_Station_Id nvarchar(255) './Calling-Station-Id',
    Callback_Number nvarchar(255) './Callback-Number',
    Framed_IP_Address nvarchar(15) './Framed-IP-Address',
    NAS_Identifier nvarchar(255) './NAS-Identifier',
    NAS_IP_Address nvarchar(15) './NAS-IP-Address',
    NAS_Port int './NAS-Port',
    Client_Vendor int './Client-Vendor',
    Client_IP_Address nvarchar(15) './Client-IP-Address',
    Client_Friendly_Name nvarchar(255) './Client-Friendly-Name',
    Event_Timestamp datetime './Event-Timestamp',
    Port_Limit int './Port-Limit',
    NAS_Port_Type int './NAS-Port-Type',
    Connect_Info nvarchar(255) './Connect-Info',
    Framed_Protocol int './Framed-Protocol',
    Service_Type int './Service-Type',
    Authentication_Type int './Authentication-Type',
    NP_Policy_Name nvarchar(255) './NP-Policy-Name',
    Reason_Code int './Reason-Code',
    Class nvarchar(255) './Class',
    Session_Timeout int './Session-Timeout',
    Idle_Timeout int './Idle-Timeout',
    Termination_Action int './Termination-Action',
    EAP_Friendly_Name nvarchar(255) './EAP-Friendly-Name',
    Acct_Status_Type int './Acct-Status-Type',
    Acct_Delay_Time int './Acct-Delay-Time',
    Acct_Input_Octets int './Acct-Input-Octets',
    Acct_Output_Octets int './Acct-Output-Octets',
    Acct_Session_Id nvarchar(255) './Acct-Session-Id',
    Acct_Authentic int './Acct-Authentic',
    Acct_Session_Time int './Acct-Session-Time',
    Acct_Input_Packets int './Acct-Input-Packets',
    Acct_Output_Packets int './Acct-Output-Packets',
    Acct_Terminate_Cause int './Acct-Terminate-Cause',
    Acct_Multi_Session_Id nvarchar(255) './Acct-Multi-Session-Id',
    Acct_Link_Count int './Acct-Link-Count',
    Acct_Interim_Interval int './Acct-Interim-Interval',
    Tunnel_Type int './Tunnel-Type',
    Tunnel_Medium_Type int './Tunnel-Medium-Type',
    Tunnel_Client_Endpoint nvarchar(255) './Tunnel-Client-Endpt',
    Tunnel_Server_Endpoint nvarchar(255) './Tunnel-Server-Endpt',
    Acct_Tunnel_Connection nvarchar(255) './Acct-Tunnel-Connection',
    Tunnel_Pvt_Group_Id nvarchar(255) './Tunnel-Pvt-Group-Id',
    Tunnel_Assignment_Id nvarchar(255) './Tunnel-Assignment-Id',
    Tunnel_Preference int './Tunnel-Preference',
    MS_Acct_Auth_Type int './MS-Acct-Auth-Type',
    MS_Acct_EAP_Type int './MS-Acct-EAP-Type',
    MS_RAS_Version nvarchar(255) './MS-RAS-Version',
    MS_RAS_Vendor int './MS-RAS-Vendor',
    MS_CHAP_Error nvarchar(255) './MS-CHAP-Error',
    MS_CHAP_Domain nvarchar(255) './MS-CHAP-Domain',
    MS_MPPE_Encryption_Types int './MS-MPPE-Encryption-Types',
    MS_MPPE_Encryption_Policy int './MS-MPPE-Encryption-Policy',
    Proxy_Policy_Name nvarchar(255) './Proxy-Policy-Name',
    Provider_Type int './Provider-Type',
    Provider_Name nvarchar(255) './Provider-Name',
    Remote_Server_Address nvarchar(15) './Remote-Server-Address',
    MS_RAS_Client_Name nvarchar(255) './MS-RAS-Client-Name',
    MS_RAS_Client_Version nvarchar(255) './MS-RAS-Client-Version',
/*
    NAP-specific information, available from NPS starting with Windows Server 2008.
*/
    MS_Quarantine_State int './MS-Quarantine-State'
   )
EXEC sp_xml_removedocument @idoc
SET NOCOUNT OFF
GO
 
accounting_data表结构
USE [NPS]
GO
/****** Object:  Table [dbo].[accounting_data]    Script Date: 02/06/2012 13:04:58 ******/
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) NOT NULL,
    [Packet_Type] [int] NOT NULL,
    [User_Name] [nvarchar](255) NULL,
    [F_Q_User_Name] [nvarchar](255) NULL,
    [Called_Station_Id] [nvarchar](255) NULL,
    [Calling_Station_Id] [nvarchar](255) NULL,
    [Callback_Number] [nvarchar](255) NULL,
    [Framed_IP_Address] [dbo].[ipaddress] NULL,
    [NAS_Identifier] [nvarchar](255) NULL,
    [NAS_IP_Address] [dbo].[ipaddress] NULL,
    [NAS_Port] [int] NULL,
    [Client_Vendor] [int] NULL,
    [Client_IP_Address] [dbo].[ipaddress] NULL,
    [Client_Friendly_Name] [nvarchar](255) NULL,
    [Event_Timestamp] [datetime] NULL,
    [Port_Limit] [int] NULL,
    [NAS_Port_Type] [int] NULL,
    [Connect_Info] [nvarchar](255) NULL,
    [Framed_Protocol] [int] NULL,
    [Service_Type] [int] NULL,
    [Authentication_Type] [int] NULL,
    [NP_Policy_Name] [nvarchar](255) NULL,
    [Reason_Code] [int] NULL,
    [Class] [nvarchar](255) NULL,
    [Session_Timeout] [int] NULL,
    [Idle_Timeout] [int] NULL,
    [Termination_Action] [int] NULL,
    [EAP_Friendly_Name] [nvarchar](255) NULL,
    [Acct_Status_Type] [int] NULL,
    [Acct_Delay_Time] [int] NULL,
    [Acct_Input_Octets] [int] NULL,
    [Acct_Output_Octets] [int] NULL,
    [Acct_Session_Id] [nvarchar](255) NULL,
    [Acct_Authentic] [int] NULL,
    [Acct_Session_Time] [int] NULL,
    [Acct_Input_Packets] [int] NULL,
    [Acct_Output_Packets] [int] NULL,
    [Acct_Terminate_Cause] [int] NULL,
    [Acct_Multi_Session_Id] [nvarchar](255) NULL,
    [Acct_Link_Count] [int] NULL,
    [Acct_Interim_Interval] [int] NULL,
    [Tunnel_Type] [int] NULL,
    [Tunnel_Medium_Type] [int] NULL,
    [Tunnel_Client_Endpoint] [nvarchar](255) NULL,
    [Tunnel_Server_Endpoint] [nvarchar](255) NULL,
    [Acct_Tunnel_Connection] [nvarchar](255) NULL,
    [Tunnel_Pvt_Group_Id] [nvarchar](255) NULL,
    [Tunnel_Assignment_Id] [nvarchar](255) NULL,
    [Tunnel_Preference] [int] NULL,
    [MS_Acct_Auth_Type] [int] NULL,
    [MS_Acct_EAP_Type] [int] NULL,
    [MS_RAS_Version] [nvarchar](255) NULL,
    [MS_RAS_Vendor] [int] NULL,
    [MS_CHAP_Error] [nvarchar](255) NULL,
    [MS_CHAP_Domain] [nvarchar](255) NULL,
    [MS_MPPE_Encryption_Types] [int] NULL,
    [MS_MPPE_Encryption_Policy] [int] NULL,
    [Proxy_Policy_Name] [nvarchar](255) NULL,
    [Provider_Type] [int] NULL,
    [Provider_Name] [nvarchar](255) NULL,
    [Remote_Server_Address] [dbo].[ipaddress] NULL,
    [MS_RAS_Client_Name] [nvarchar](255) NULL,
    [MS_RAS_Client_Version] [nvarchar](255) NULL,
    [MS_Quarantine_State] [int] NULL
) ON [PRIMARY]
GO
reason code
00 = Success

01 = Internal error

02 = Access denied

03 = Malformed request

04 = Global catalog unavailable

05 = Domain unavailable

06 = Server unavailable

07 = No such domain

08 = No such user

16 = Authentication failure

17 = Password change failure

18 = Unsupported authentication type

19 = No reversibly encrypted password is stored for the user account

32 = Local users only

33 = Password must be changed

34 = Account disabled

35 = Account expired

36 = Account locked out

37 = Logon hours are not valid

38 = Account restriction

48 = Did not match network policy

49 = Did not match connection request policy

64 = Dial-in locked out

65 = Dial-in disabled

66 = Authentication type is not valid

67 = Calling station is not valid

68 = Dial-in hours are not valid

69 = Called station is not valid

70 = Port type is not valid

71 = Restriction is not valid

80 = No record

96 = Session timed out

97 = Unexpected request