RRAS NPS and SQL Logging

22 Mar

If you use Routing and Remote Access Service (RRAS) along with Network Policy Server (NPS) and you then decided to log all authentication and accounting information in a SQL database then this might be of some use to you.

The data isn’t as easy to decipher as you might hope, as there are often extra rows of data in there.

I simply wanted to pull a log of who connected and disconnected and when.

The packet types represent the following:

  • 1 = Access-Request
  • 2 = Access-Accept
  • 3 = Access-Reject
  • 4 = Accounting-Request

I then noticed a pattern. When users connected, there was a packet type of 4, with their user_name. There was also a packet type of 1 for the same username less then a second before. When a user disconnected there was a packet type of 4 with their user_name and nothing else.

rras_sql_nps

So, behold the SQL that just about works for a quick report. Which basically checks for Status Type’s of on their own = Disconnected. Status Type of 4 where there was the name user with a status Type of 1 less than 2990 ms before hand means it ‘hopefully’ was a connect.

use RRAS
select timestamp , replace(user_name, 'Domain\',''),
iif((select top 1 id from accounting_data where packet_type = 1 and user_name = d.user_name and timestamp <= d.timestamp and timestamp >= dateadd(ms, -2990, d.timestamp)) >= 0, 'Connected', 'Disconnected') as 'Connection Status'
from dbo.accounting_data d
where packet_type = 4
order by timestamp desc

rras_sql_nps2

References: