NPS Stored Procedure
USE [NPSODBC]
GO
GO
/****** Object: StoredProcedure [dbo].[report_event] Script Date: 02/06/2012 11:40:47 ******/
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
GO
CREATE PROCEDURE [dbo].[report_event]
@doc ntext
AS
@doc ntext
AS
SET NOCOUNT ON
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
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.
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'
)
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
GO
/****** Object: Table [dbo].[accounting_data] Script Date: 02/06/2012 13:04:58 ******/
SET ANSI_NULLS ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]
[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
转载于:https://blog.51cto.com/wang11/1138428