【逗老师带你学IT】Windows NPS记账与审计

2023-08-17 17:58:16

本文主要介绍通过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、行为审计设备操作
鬼知道为什么要用到这么多知识点

本文用的到知识点跨专业比较大，各位同学选择你感兴趣的点看就好。

@TOC

一、安装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
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20200309203222648.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l0bHpxMDIyOA==,size_16,color_FFFFFF,t_70 =380x)
新建数据库
【逗老师带你学IT】Windows NPS记账与审计

二、安装Microsoft SQL Server Management Studio

贼简单，一路Next就好，下方放出下载链接
Microsoft | 下载 SQL Server Management Studio (SSMS)

三、配置NPS记账功能

官方手册
Microsoft | Configure Network Policy Server Accounting

1、使用向导初始化记账配置

建议勾选同时记录到SQL Server和本地文本文件
【逗老师带你学IT】Windows NPS记账与审计
配置记账数据库连接信息
【逗老师带你学IT】Windows NPS记账与审计
配置本地文件日志记录
【逗老师带你学IT】Windows NPS记账与审计
初始化数据库，点击重新生成
【逗老师带你学IT】Windows NPS记账与审计

2、更改SQL Server日志记录属性

选择启用文本文件日志记录进行故障转移，避免因SQL Server故障导致RADIUS拒绝认证
【逗老师带你学IT】Windows NPS记账与审计

四、学习阅读记账数据库基础数据

1、认识dbo.accounting_data表

【逗老师带你学IT】Windows NPS记账与审计
此表默认包含以下字段
大部分字段都是字面意思，具体需要解释的，参阅以下文档
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表

【逗老师带你学IT】Windows NPS记账与审计
CREATE TALBE加入[Calling_Station_Id] [nvarchar](255) NULL,然后点执行！
【逗老师带你学IT】Windows NPS记账与审计

3.2更新dbo.report_event存储过程

认证设备MAC地址一般在RADIUS认证报文中以Calling Station ID字段进行传输。
但是NPS默认记账不记录Calling Station ID字段，我们需要修改dbo.report_event存储过程，添加数据库记录字段。
【逗老师带你学IT】Windows NPS记账与审计
INSERT语句添加Calling_Station_Id,
【逗老师带你学IT】Windows NPS记账与审计
XML解释添加Calling_Station_Id nvarchar(255) './Calling-Station-Id',然后点执行！
【逗老师带你学IT】Windows NPS记账与审计

3.3验证修改结果

查看dbo.accounting_data表，可以看到用户认证终端的MAC地址，记录在Calling_Station_Id字段中
【逗老师带你学IT】Windows NPS记账与审计

五、SNMP获取ARP表

通过上述操作，我们获取了认证后的用户MAC address---User_Name的对应关系。
但是很多行为审计设备要求获取IP Address---User_Name的对应关系。
因此我们需要一些办法，获取MAC address---IP Address的对应关系。
那这不就是ARP表么！

1、SNMP基础解释

我也不知道看这边文章的同学，你们是搞服务器运维的，还是搞数据库的，还是搞网络的。有些同学不知道SNMP，简单介绍一下
【逗老师带你学IT】Windows NPS记账与审计

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)O***rHANLf[(;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'

【逗老师带你学IT】Windows NPS记账与审计

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

炒鸡简单，查询出一下数据
【逗老师带你学IT】Windows NPS记账与审计

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_***_ip where ip<>'Update Time')
#如果还有其他表，可以使用union all联合查询，没有的话，忽略上面两句

八、行为审计设备配置

以深信服行为审计设备为例

1、添加SQL Server数据库作为外部认证数据库

![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20200307232828193.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l0bHpxMDIyOA==,size_16,color_FFFFFF,t_70 =500x)
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20200307232903756.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l0bHpxMDIyOA==,size_16,color_FFFFFF,t_70 =500x)

2、添加单点登录服务器配置

数据库服务器选择刚才新添加的数据库服务器
查询语句：select * from userip
【逗老师带你学IT】Windows NPS记账与审计
测试一下有效性
【逗老师带你学IT】Windows NPS记账与审计
【逗老师带你学IT】Windows NPS记账与审计

3、配置单点登录策略

认证范围填写用户终端地址段。
【逗老师带你学IT】Windows NPS记账与审计
认证方式选项卡内，认证方式选择单点登录。同时单点登录失败的用户配置使用以IP地址作为用户名。
【逗老师带你学IT】Windows NPS记账与审计
配置完成之后，目前到达行为审计设备的流量，会触发此条认证策略创建用户。
并且，会通过之前关联的认证数据库，自动获取用户名并创建用户！！
【逗老师带你学IT】Windows NPS记账与审计

搞定！

上一篇：不解释，分享这个base.css


下一篇：CentOS 7使用yum安装SNMP教程以及简单使用snmp(更实用,比pip靠谱多了!太草)