none
NPS SQL Logging Missing Fields and NULLs

    Question

  • Hi

    I have an NPS server which logs all the relevant information in the NPS Event viewer Logs. I also have this information recording to a local text file.

    So I can keep records for longer I have set up the  link to an SQL server to also store the data in there which I can then extract using a custom web search system on client MAC Addresses.

    However, the majority of information which is being transferred in coming over as NULL or some of the columns are missing. in SQL I have one column which shows the calling station ID with the AP Mac address and WIFI name, however there is no column for just the MAc address of the client.

    However, this information is recorded in the Event log viewer. Do not ALL the fields that show in the windows event log transfer over to SQL?

    Thanks.

    Friday, November 11, 2016 9:20 AM

All replies

  • Hi MartinL85,

    I'm afraid I could not find much related information about the issue.

    Not sure if NPS server not sending the MAC address or SQL server not logging the data.

    I suppose you could perform a network capture to analyze the issue. Check the packets sent by NPS server to see if the MAC address are sent.

    Here is the link for network monitor:

    http://www.microsoft.com/en-us/download/details.aspx?id=4865

    Best Regards,

    Leo


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Monday, November 14, 2016 2:48 AM
    Moderator
  • Hi MartinL85,

    Are there any updates on the issue?

    You could mark the reply as answer if it is helpful.

    Best Regards,

    Leo


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, November 25, 2016 1:17 AM
    Moderator
  • I have the same issue. If I create the accounting_data SQL table with the NPS wizzard (Windows Server 2012) at least the field "Calling-Station-Id" is missing on the SQL Server table. In the Network Monitor I can see the field. The NPS Server sends the field "Calling-Station-Id" to the MS SQL Server, but the table structur is incomplete.

    itpit

    Tuesday, May 16, 2017 6:58 AM
  • Bumping this thread,

    I have the same issue also, I have configured NPS to log to an SQL server as well as a text file locally on the box. When quering the dbo.accounting_data table I do not see all of the columns mentioned within the XML text log. As mentioned above I am specifically interested in the Calling-Station-Id.

    Can someone point us in the right direction on this please?

    Many thanks

    Tuesday, July 18, 2017 3:59 PM
  • Ok never mind,

    The stored proceure that is built by default by the NPS wizard needs amending to include all of the radius attributes required.

    https://msdn.microsoft.com/en-us/library/bb960723%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396

    I ended up butchering the above T-SQL to create a new stored procedure called dbo.report_event that created a new table and pulled my required attributes from XML and pushed them into the newly created dbo.test_data table.

    Many thanks

    Wednesday, July 19, 2017 9:41 AM
  • Thanks for finding this! SQL Logging now contains all the fields I need for Accounting, I first had to drop the database and then re-create but it is now logging everything!!!

    Jason

    Friday, March 16, 2018 2:43 PM
  • Hi Jason,

    Could you me more information on how you did this?

    I have the same issue as you, most of columns from dbo.accounting_data are NULL.

    Thanks

    Ced

    Thursday, March 22, 2018 3:33 PM
  • So the way I did it was delete the database completely then modify the script here to recreate with the same name I had previously had. Within the script below, copy everything and save it, then where it says CREATE DATABASE [NPSODBC] change that to the database name you want. Then do a search and replace for NPSODBC and replace it with your name. You then copy the everything and paste into a new quesry window and run it. It will recreate the database with all the correct tables. If you need help just let me know what you want the db name to be and I can paste it in and then drop it into a pastebin

    https://msdn.microsoft.com/en-us/library/bb960723%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396

    Wednesday, March 28, 2018 12:19 AM