Spring中的aware接口详情
452
2022-09-11
【逗老师带你学IT】Windows NPS记账与审计（我爱学IT）
【逗老师带你学IT】Windows NPS记账与审计（我爱学IT）
本文主要介绍通过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、新建一个数据库
二、安装Microsoft SQL Server Management Studio
贼简单，一路Next就好，下方放出下载链接Microsoft | 下载 SQL Server Management Studio (SSMS)
三、配置NPS记账功能
官方手册Microsoft | Configure Network Policy Server Accounting
1、使用向导初始化记账配置
2、更改SQL Server日志记录属性
四、学习阅读记账数据库基础数据
1、认识dbo.accounting_data表
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表
3.2更新dbo.report_event存储过程
3.3验证修改结果
五、SNMP获取ARP表
通过上述操作，我们获取了认证后的用户MAC address---User_Name的对应关系。但是很多行为审计设备要求获取IP Address---User_Name的对应关系。因此我们需要一些办法，获取MAC address---IP Address的对应关系。那这不就是ARP表么！
1、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官网进行下载。H3C CISCO百度搜索一下方法一大堆举例H3C:
举例HUAWEI
举例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、先去 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、添加单点登录服务器配置
3、配置单点登录策略
搞定！
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
发表评论
暂时没有评论，来抢沙发吧~