Apr 18 2024 05:45 AM
I've setup new NPS servers. When I setup accounting with an SQL DB connection the tables are not generated correctly. I have an old install and the tables have much more info.
I have tried to change to ODBC and IAS log formats, but I can't seem to get it to work. The tables are not generated unless I run the wizard. The wizard always sets things back to DTS compliant.
I have searched for days now on a fix.
What is the problem with NPS? Does anyone know how to fix this?
HELP
Apr 19 2024 05:03 AM
Just for reference:
NPS Table created:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [id]
,[timestamp]
,[Computer_Name]
,[Packet_Type]
,[User_Name]
,[Client_IP_Address]
,[Fully_Qualified_Machine_Name]
,[NP_Policy_Name]
,[MS_Quarantine_State]
,[MS_Extended_Quarantine_State]
,[System_Health_Result]
,[System_Health_ResultEx]
,[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 [NPSODBC].[dbo].[accounting_data]
NPS Table on my other NPS setup:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [id]
,[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]
,[MS_Quarantine_State]
FROM [NPSODBC].[dbo].[accounting_data]