本文主要介绍通过Windows NPS构建RADIUS服务器的记账及后期用户流量审计 关于如何使用NPS与域控集成认证，可以参照前一篇文章 Windows Server NPS服务构建基于AD域控的radius认证 本文涉及的知识点：
1、NPS记账功能 2、Microsoft AD集成认证 3、SQL Server部署 4、SQL 存储过程修改 5、三层设备SNMP配置 6、三层路由设备Snmp ARP获取 7、Python 连接SQL Server 8、Python OS包获取操作系统指令 9、Python字符串处理，截取和拼接 10、行为审计设备操作 鬼知道为什么要用到这么多知识点
本文用的到知识点跨专业比较大，各位同学选择你感兴趣的点看就好。
(目录)
一、安装SQL Server
1、安装SQL Server服务器
贼简单，一路Next就好，下方放出MSDN Itell you的连接 SQL Server 2016 Developer with Service Pack 2 (x64) - DVD (Chinese-Simplified) ed2k://|file|cn_sql_server_2016_developer_with_service_pack_2_x64_dvd_12195013.iso|3217154048|AC379F2A852760E54316A2CDAEFCB42C|/
2、新建一个数据库
运行SQL Server 2016 Master Data Services Configuration Manager
新建数据库
二、安装Microsoft SQL Server Management Studio
贼简单，一路Next就好，下方放出下载链接 Microsoft | 下载 SQL Server Management Studio (SSMS)
三、配置NPS记账功能
官方手册 Microsoft | Configure Network Policy Server Accounting
1、使用向导初始化记账配置
建议勾选同时记录到SQL Server和本地文本文件
配置记账数据库连接信息
配置本地文件日志记录
初始化数据库，点击重新生成
2、更改SQL Server日志记录属性
选择启用文本文件日志记录进行故障转移，避免因SQL Server故障导致RADIUS拒绝认证
四、学习阅读记账数据库基础数据
1、认识dbo.accounting_data表
此表默认包含以下字段
大部分字段都是字面意思，具体需要解释的，参阅以下文档
Microsoft | Interpret NPS Database Format Log Files
Timestamp,
Computer_Name,
Packet_Type,
User_Name,
Client_IP_Address,
Fully_Qualified_Machine_Name,
NP_Policy_Name,
MS_Quarantine_State,
MS_Extended_Quarantine_State,
SystemHealthResult,
SystemHealthResultEx,
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
2、认识dbo.report_event存储过程
NPS 将记帐数据格式化为 XML 文档，该文档将其发送到你在 NPS 中指定的 SQL Server 数据库中的report_event存储过程。 若要使 SQL Server 日志记录正常工作，SQL Server 数据库中必须有一个名为report_event的存储过程，该存储过程可接收和分析 NPS 中的 XML 文档。 通过修改dbo.report_event存储过程，我们可以将我们感兴趣的字段加入数据库并进行记录
USE [NPS]
GO
/****** Object: StoredProcedure [dbo].[report_event] Script Date: 2020/3/9 20:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[report_event]
@doc ntext
AS
SET NOCOUNT ON
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
/*
Combine multiple System-Health-Result string into one string with delimiter as ":"
*/
DECLARE @SystemHealthResult NVARCHAR(4000)
SELECT @SystemHealthResult = COALESCE(@SystemHealthResult+':','')+ISNULL(SHR.System_Health_Result,'')
FROM (SELECT *
FROM OPENXML(@idoc, '/Event/System-Health-Result')
WITH (System_Health_Result NVARCHAR(4000) 'text()')) AS SHR
/*
Combine multiple System-Health-ResultEx string into one string
*/
DECLARE @SystemHealthResultEx NVARCHAR(MAX)
IF @SystemHealthResult IS NOT NULL SELECT @SystemHealthResultEx = COALESCE(@SystemHealthResultEx,'')+ISNULL(CAST(SHR.System_Health_ResultEx AS NVARCHAR(MAX)),'')
FROM (SELECT *
FROM OPENXML(@idoc, '/Event/System-Health-ResultEx')
WITH (System_Health_ResultEx xml '.')) AS SHR
/*
All RADIUS attributes written to the ODBC format logfile are declared here. Refer to IAS ODBC Formatted Log Files in Online Help for information on interpreting these values.
*/
INSERT accounting_data
SELECT
Timestamp,
Computer_Name,
Packet_Type,
[User_Name],
Client_IP_Address,
Fully_Qualified_Machine_Name,
NP_Policy_Name,
MS_Quarantine_State,
MS_Extended_Quarantine_State,
@SystemHealthResult,
@SystemHealthResultEx,
MS_Network_Access_Server_Type,
Called_Station_Id,
Calling_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 OPENXML(@idoc, '/Event')
WITH (
Timestamp datetime './Timestamp',
Computer_Name nvarchar(255) './Computer-Name',
Packet_Type int './Packet-Type',
[User_Name] nvarchar(255) './User-Name',
Client_IP_Address nvarchar(15) './Client-IP-Address',
Fully_Qualified_Machine_Name nvarchar(255) './Fully-Qualified-Machine-Name',
NP_Policy_Name nvarchar(255) './NP-Policy-Name',
MS_Quarantine_State int './MS-Quarantine-State',
MS_Extended_Quarantine_State int './MS-Extended-Quarantine-State',
System_Health_Result nvarchar(4000),
System_Health_ResultEx nvarchar(MAX),
MS_Network_Access_Server_Type int './MS-Network-Access-Server-Type',
Called_Station_Id nvarchar(255) './Called-Station-Id',
Calling_Station_Id nvarchar(255) './Calling-Station-Id',
MS_Quarantine_Grace_Time datetime './MS-Quarantine-Grace-Time',
MS_Quarantine_User_Class nvarchar(255) './MS-Quarantine-User-Class',
Client_IPv6_Address nvarchar(32) './Client-IPv6-Address',
Not_Quarantine_Capable int './Not-Quarantine-Capable',
AFW_Zone int './AFW-Zone',
AFW_Protection_Level int './AFW-Protection-Level',
Quarantine_Update_Non_Compliant int './Quarantine-Update-Non-Compliant',
MS_Machine_Name nvarchar(255) './MS-Machine-Name',
OS_Version nvarchar(255) './Machine-Inventory',
MS_Quarantine_Session_Id nvarchar(255) './MS-Quarantine-Session-Id'
)
EXEC sp_xml_removedocument @idoc
SET NOCOUNT OFF
3、添加认证设备MAC地址，记录至数据库
3.1更新dbo.accounting_data表
CREATE TALBE加入[Calling_Station_Id] [nvarchar](255) NULL,然后点执行！
3.2更新dbo.report_event存储过程
认证设备MAC地址一般在RADIUS认证报文中以Calling Station ID字段进行传输。
但是NPS默认记账不记录Calling Station ID字段，我们需要修改dbo.report_event存储过程，添加数据库记录字段。
INSERT语句添加Calling_Station_Id,
XML解释添加Calling_Station_Id nvarchar(255) './Calling-Station-Id',然后点执行！
3.3验证修改结果
查看dbo.accounting_data表，可以看到用户认证终端的MAC地址，记录在Calling_Station_Id字段中
五、SNMP获取ARP表
通过上述操作，我们获取了认证后的用户MAC address---User_Name的对应关系。 但是很多行为审计设备要求获取IP Address---User_Name的对应关系。 因此我们需要一些办法，获取MAC address---IP Address的对应关系。 那这不就是ARP表么！
1、SNMP基础解释
我也不知道看这边文章的同学，你们是搞服务器运维的，还是搞数据库的，还是搞网络的。有些同学不知道SNMP，简单介绍一下
2、SNMP ARP OID
虽然不同的厂商设备OID会相差很大，因此厂商通常会提供MIB库来检索OID 但是，对于获取ARP表有一个通用的OID：
OID 1.3.6.1.2.1.4.22.1.2
使用walk方法可以跑出全部端口的ARP表
3、安装snmpwalk工具
linux和windows均有snmpwalk安装程序
Linux：yum install -y net-snmp net-snmp-utils
windows下安装net-snmp，我们可以去net-snmp官网进行下载。 http://www.net-snmp.org/download.html 安装方法CSDN上一大堆，比如其他大大们的指导 https://blog.csdn.net/weixin_30752699/article/details/98688984
4、网络设备配置SNMP
HUAWEI H3C CISCO百度搜索一下方法一大堆 举例H3C:
<H3C>dis cu | in snmp
snmp-agent
snmp-agent local-engineid 800063A28008688D43D19600000001
snmp-agent community read wulalalwulalala
snmp-agent sys-info version v2c v3
snmp-server arp-sync enable
举例HUAWEI
<HUAWEI>dis cu | in snmp
snmp-agent
snmp-agent local-engineid 800007DB03A4BE2B3B80A0
snmp-agent community read cipher %^%#Myph,aG8/Ke]v1'gt@G#+W]EVCBrGQ]:>YDShMB#QjT`Phq'R8)OvPNrHANLf[(;L_<^-:o0/nJ*S5rE%^%#
snmp-agent sys-info version v2c v3
举例Aruba
(Aruba-AC) *[mynode] #show running-config | include snmp
Building Configuration...
netservice svc-snmp udp 161
netservice svc-snmp-trap udp 162
ipv6 any user svc-snmp permit
ipv6 user any svc-snmp-trap permit
any user svc-snmp permit
user any svc-snmp-trap permit
snmp-server community "jiubugaosuniwodemima"
snmp-server enable trap
snmp-server trap source 0.0.0.0
举例CISCO
Router# configure terminal
Router(config)# snmp-server community aishashajiushibushuo ro
Router(config)# snmp-server trap link ietf
Router(config)# snmp-server enable traps snmp
5、使用snmpwalk获取ARP表
以Linux为例
snmpwalk -v 2c -c snmp秘钥 设备IP地址 1.3.6.1.2.1.4.22.1.2
够直白的ARP表，如下图所示
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.12 = STRING: 94:e1:ac:74:5b:2c
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.13 = STRING: 94:e1:ac:74:5d:18
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.14 = STRING: 94:e1:ac:60:49:74
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.15 = STRING: 58:3:fb:5b:fd:81
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.16 = STRING: 94:e1:ac:74:5a:e6
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.17 = STRING: 94:e1:ac:74:58:cd
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.18 = STRING: 94:e1:ac:74:5a:f5
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.19 = STRING: 64:db:8b:65:c1:f2
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.20 = STRING: 64:db:8b:65:c3:a1
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.21 = STRING: 64:db:8b:65:bd:95
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.22 = STRING: 64:db:8b:65:c3:a7
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.23 = STRING: 64:db:8b:65:c2:3
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.24 = STRING: 68:6d:bc:25:85:57
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.25 = STRING: 68:6d:bc:25:83:ac
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.26 = STRING: 68:6d:bc:25:83:c7
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.27 = STRING: 68:6d:bc:25:83:b7
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.28 = STRING: 68:6d:bc:25:85:df
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.29 = STRING: 68:6d:bc:25:85:da
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.32 = STRING: 60:23:a4:5e:fc:a4
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.34 = STRING: 0:50:56:8b:58:74
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.35 = STRING: 0:c:29:f9:98:7c
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.37 = STRING: 60:23:a4:7c:73:8e
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.39 = STRING: 60:23:a4:7c:65:e
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.45 = STRING: b8:27:eb:c5:83:28
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.150 = STRING: 58:3:fb:cc:52:5d
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.151 = STRING: b4:a3:82:db:f6:3
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.251 = STRING: 0:17:61:11:f1:de
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.252 = STRING: 0:17:61:10:f2:ad
IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.253 = STRING: 0:17:61:10:9b:7f
六、同步ARP表至SQL Server数据库
1、安装python及相关pip包
找台linux服务器，Windows也行，随便你 安装Python和pymssql包
yum install python
pip install pymssql
特别注意Windows下pip install pymssql需要VC++运行环境，嫌麻烦不想安装VC++的话，可以如下操作
1、先去 https://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql 上面下载对应Windows操作系统版本和python版本的安装包 2、放到安装目录下，然后使用命令进入这个目录 3、执行pip3 install --user pymssql-2.1.4-cp38-cp38-win_amd64.whl
2、写Python
# encoding=utf-8
import pymssql
import commands
import re
import os
import sys
import datetime
server1 = "10.0.20.28"
server2 = "10.0.20.29"
#主备两台RADIUS的地址，如果你所在的环境就一个的话，删除掉多余的就行
user = "sa"
password = "数据库密码"
database = "NPS"
#数据库连接信息
conn = pymssql.connect(server1, user, password, database)
conn2 = pymssql.connect(server2, user, password, database)
cursor = conn.cursor()
cursor2 = conn2.cursor()
#构建数据库connect和cursor
def execComand(command):
#执行命令，获取返回结果
data = commands.getoutput(command)
return data
#-------------------------------------------------------------------------------
#定义一个函数，处理SNMPWALK返回的数据
def format_result(result_txt):
#原格式是这样的：
#IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.253 = STRING: 0:17:1:10:9b:7f（注意这个MAC地址没有补零）
#IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.28 = STRING: 68:6d:bc:25:85:df
result_out=result_txt.replace("IP-MIB::ipNetToMediaPhysAddress.","")
result_out=result_out.replace("STRING:","")
result_out=result_out.replace(" ","")
result_out=result_out.split('\n')
#以上四句，该删的删，该替换的替换
i=0
for result_line in result_out:
result_line=result_line[4:]
result_line=result_line.split('=')
mac_addr=result_line[1].split(':')
i2=0
for mac in mac_addr:
mac_addr[i2]=mac_addr[i2].zfill(2)
#snmpwalk工具返回的MAC地址没有补零，需要进行补零操作
#0:17:1:10:9b:7f 变换成 00:17:01:10:9b:7f
i2=i2+1
result_line[1]="".join(mac_addr)
result_out[i]=result_line
i=i+1
return result_out
#-------------------------------------------------------------------------------
#定义一个函数，在数据库里创建ARP表
def CreateTable():
sql = """
IF OBJECT_ID('arp', 'U') IS NOT NULL DROP TABLE arp
CREATE TABLE arp (ip VARCHAR(100),mac VARCHAR(100))
"""
cursor.execute(sql)
conn.commit()
cursor2.execute(sql)
conn2.commit()
#-------------------------------------------------------------------------------
#定义一个函数，在ARP表里插入数据
def InsertData(ip,mac):
sql = "INSERT INTO arp(ip,mac) VALUES ('"+ip+"', '"+mac+"')"
cursor.execute(sql)
conn.commit()
cursor2.execute(sql)
conn2.commit()
def main():
command='snmpwalk -v 2c -c snmpsharekeyshshsh 10.0.250.1 1.3.6.1.2.1.4.22.1.2'
#linux下执行snmp的脚本命令
#如果是windows平台，使用下面这两句，并且不需要def execComand函数
#command=os.popen(" C:/usr/bin/snmpwalk.exe -v 2c -c snmpsharekeyshshsh 10.1.1.1 1.3.6.1.2.1.4.22.1.2")
#data=command.read()
data=execComand(command)
data=format_result(data)
#获取命令返回的结果，并且进行格式化处理
now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
CreateTable()
#创建一个表
InsertData(now_time,"Update Time")
#插入当前时间，用于监控平台监控ARP表更新状态
for data_sub in data:
InsertData(data_sub[0],data_sub[1])
#写入ARP表数据
if __name__ == '__main__':
main()
3、查看SQL Server中dbo.arp表内容
select * from arp
可以看到ARP表正常更新 以下语句通过计算Update_Time与现在时间差值可以监控ARP表是否正常更新
select datediff(s,ip,GETDATE()) from arp where mac='Update Time'
4、创建定时任务
[root@sz_radius_10 ~]# crontab -l
*/1 * * * * /root/AC-SQL-ARP/ARP-SQL.sh
[root@sz_radius_10 ~]#
[root@sz_radius_10 ~]# cat /root/AC-SQL-ARP/ARP-SQL.sh
#!/bin/bash
cd /root/AC-SQL-ARP/
python ARP-TO-SQL.py
#---------------------------------------------------------
七、联合查询记账数据表和ARP表
1、联合查询语句
select timestamp,id,User_Name,mac,ip from accounting_data,arp where
(
id in
(SELECT max(id) from accounting_data group by Calling_Station_Id)
#查询同一个Calling_Station_Id最后更新的记录id
and
replace(Calling_Station_Id,'-','')=mac
#HUAWEI和H3C设备带过来的MAC地址会携带“-”，需要去除
)
order by timestamp
炒鸡简单，查询出一下数据
2、创建查询视图
CREATE VIEW [dbo].[userip]
AS
select User_Name,ip from accounting_data,arp where
(
id in
(SELECT max(id) from accounting_data group by Calling_Station_Id)
and
replace(Calling_Station_Id,'-','')=mac
)
union all
(select user_name collate Chinese_PRC_CI_AI_WS,ip from user_vpn_ip where ip<>'Update Time')
#如果还有其他表，可以使用union all联合查询，没有的话，忽略上面两句
八、行为审计设备配置
以深信服行为审计设备为例
1、添加SQL Server数据库作为外部认证数据库
2、添加单点登录服务器配置
数据库服务器选择刚才新添加的数据库服务器
查询语句：select * from userip
测试一下有效性
3、配置单点登录策略
认证范围填写用户终端地址段。
认证方式选项卡内，认证方式选择单点登录。同时单点登录失败的用户配置使用以IP地址作为用户名。
配置完成之后，目前到达行为审计设备的流量，会触发此条认证策略创建用户。
并且，会通过之前关联的认证数据库，自动获取用户名并创建用户！！
