编程知识 cdmana.com

Windows NPs bookkeeping and auditing

This article mainly introduces through Windows NPS structure RADIUS Server accounting and post user traffic audit
On how to use NPS Integrate authentication with domain control , You can refer to the previous article
Windows Server NPS Service construction is based on AD Domain controlled radius authentication
The knowledge involved in this article :


1、NPS Bookkeeping function
2、Microsoft AD Integration certification
3、SQL Server Deploy
4、SQL Stored procedure modification
5、 Third floor equipment SNMP To configure
6、 Layer 3 routing device Snmp ARP obtain
7、Python Connect SQL Server
8、Python OS Packets get operating system instructions
9、Python string manipulation , Interception and splicing
10、 Behavior audit device operation
Why do you need to know so much about ghosts









This article uses to the knowledge point, the interdisciplinary is bigger , Just choose the point you are interested in .

@TOC

One 、 install SQL Server

1、 install SQL Server The server

The thief is simple , All the way Next Just fine , Let out from below MSDN Itell you The connection of
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、 Create a new database

function SQL Server 2016 Master Data Services Configuration Manager
![ Insert picture description here ](https://img-blog.csdnimg.cn/20200309203222648.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l0bHpxMDIyOA==,size_16,color_FFFFFF,t_70 =380x)
New database
 Insert picture description here


Two 、 install Microsoft SQL Server Management Studio

The thief is simple , All the way Next Just fine , Download link below
Microsoft | download SQL Server Management Studio (SSMS)

3、 ... and 、 To configure NPS Bookkeeping function

The official manual
Microsoft | Configure Network Policy Server Accounting

1、 Use the wizard to initialize the billing configuration

It is suggested to check and record at the same time SQL Server And local text files
 Insert picture description here
Configure billing database connection information
 Insert picture description here
Configure local file logging
 Insert picture description here
Initialize database , Click regenerate
 Insert picture description here






2、 change SQL Server Logging properties

choice Enable text file logging for failover , Avoidable cause SQL Server A fault causes RADIUS Deny Authentication
 Insert picture description here

Four 、 Learn to read the basic data of bookkeeping database

1、 know dbo.accounting_data surface

 Insert picture description here
This table contains the following fields by default
Most fields are literal , What needs to be explained is , Refer to the following documents
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、 know dbo.report_event stored procedure

NPS Format billing data as XML file , The document will be sent to you at NPS Specified in the SQL Server In the database report_event stored procedure . If you want to make SQL Server Logging is working ,SQL Server There must be a database named report_event Stored procedure , The stored procedure can receive and analyze NPS Medium XML file .
By modifying the dbo.report_event stored procedure , We can add fields of interest to the database and record them

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、 Add authentication device MAC Address , Record to database

3.1 to update dbo.accounting_data surface

 Insert picture description here
CREATE TALBE Join in [Calling_Station_Id] [nvarchar](255) NULL, Then click execute !
 Insert picture description here

3.2 to update dbo.report_event stored procedure

Certified equipment MAC The address is usually in RADIUS In the authentication message Calling Station ID Field to transfer .
however NPS Default bookkeeping does not record Calling Station ID Field , We need to change dbo.report_event stored procedure , Add database record fields .
 Insert picture description here
INSERT Add Calling_Station_Id,
 Insert picture description here
XML Explain add Calling_Station_Id nvarchar(255) './Calling-Station-Id', Then click execute !
 Insert picture description here





3.3 Verify the modification results

see dbo.accounting_data surface , You can see the user authentication terminal MAC Address , Recorded in the Calling_Station_Id Field
 Insert picture description here

5、 ... and 、SNMP obtain ARP surface

By doing this , We've got authenticated users MAC address---User_Name Correspondence of .
But many behavioral audit devices require access to IP Address---User_Name Correspondence of .
So we need some way , obtain MAC address---IP Address Correspondence of .
So this is not ARP Watch !


1、SNMP Basic explanation

I don't know the students who read this article , You are engaged in server operation and maintenance , It's a database business , It's still about the Internet . Some students don't know SNMP, A brief introduction
 Insert picture description here

2、SNMP ARP OID

Although different manufacturers of equipment OID There will be a big difference , Therefore, manufacturers usually provide MIB Library to retrieve OID
however , For getting ARP The watch has a universal OID:

OID 1.3.6.1.2.1.4.22.1.2
 Use walk Method can run out of all ports ARP surface 

3、 install snmpwalk Tools

linux and windows Both snmpwalk Erection sequence

Linux:yum install -y net-snmp net-snmp-utils

windows Lower installation net-snmp, We can go net-snmp Download on the official website .
http://www.net-snmp.org/download.html
Installation method CSDN A lot of , For example, other big guidance
https://blog.csdn.net/weixin_30752699/article/details/98688984


4、 Network device configuration SNMP

HUAWEI H3C CISCO Baidu search a lot of methods
give an example 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

give an example 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

give an example 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

give an example 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、 Use snmpwalk obtain ARP surface

With Linux For example

snmpwalk -v 2c -c snmp Secret key   equipment IP Address  1.3.6.1.2.1.4.22.1.2

It's straightforward enough ARP surface , As shown in the figure below

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

6、 ... and 、 Sync ARP Table to SQL Server database

1、 install python And related pip package

Find a channel linux The server ,Windows It's OK , whatever
install Python and pymssql package

yum install python
pip install pymssql

Particular attention Windows Next pip install pymssql need VC++ Running environment , I don't want to install it VC++ Words , You can do the following

1、 Go first https://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
Download the corresponding Windows Operating system version and python Version of the installation package
2、 Put it in the installation directory , Then use the command to enter this directory
3、 perform pip3 install --user pymssql-2.1.4-cp38-cp38-win_amd64.whl


2、 Write 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"
# Two main and two standby RADIUS The address of , If you're in one environment , Just delete the extra ones 
user = "sa"
password = " Database password "
database = "NPS"
# Database connection information 
conn = pymssql.connect(server1, user, password, database)
conn2 = pymssql.connect(server2, user, password, database)
cursor = conn.cursor()
cursor2 = conn2.cursor()
# Building databases connect and cursor

def execComand(command):
        # Carry out orders , Get the return result 
        data = commands.getoutput(command)
        return data
#-------------------------------------------------------------------------------
# Define a function , Handle SNMPWALK Returned data 
def format_result(result_txt):
    # The original format is like this :
    #IP-MIB::ipNetToMediaPhysAddress.828.10.0.21.253 = STRING: 0:17:1:10:9b:7f( Pay attention to this MAC There is no zero padding for the address )
    #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')
    # The above four sentences , What should be deleted , The replacement of the replacement 
    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 Tool returns MAC There is no zero padding for the address , Zero padding is required 
            #0:17:1:10:9b:7f  Transform into  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
#-------------------------------------------------------------------------------
# Define a function , Create... In the database ARP surface 
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()
#-------------------------------------------------------------------------------
# Define a function , stay ARP Insert data in the table 
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 perform snmp The script command for 
    # If it is windows platform , Use the following two sentences , And you don't have to def execComand function 
    #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)
    # Get the result returned by the command , And format it 
    now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    CreateTable()
    # Create a table 
    InsertData(now_time,"Update Time")
    # Insert current time , For monitoring platform monitoring ARP Table update status 
    for data_sub in data:
        InsertData(data_sub[0],data_sub[1])
    # write in ARP Table data 

if __name__ == '__main__':
    main()

3、 see SQL Server in dbo.arp Table content

select * from arp

You can see ARP The table is normally updated
The following statement is calculated by Update_Time The difference from the present time can be monitored ARP Whether the table is updated normally

select datediff(s,ip,GETDATE()) from arp where mac='Update Time'

 Insert picture description here

4、 Create a scheduled task

[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
#---------------------------------------------------------

7、 ... and 、 Joint query of billing tables and ARP surface

1、 Union query statement

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)
# Query the same Calling_Station_Id Last updated record id
and
replace(Calling_Station_Id,'-','')=mac
#HUAWEI and H3C It's from the equipment MAC The address will carry “-”, Need to get rid of 
)
order by timestamp

Fried chicken is easy , Find out the data
 Insert picture description here

2、 Create a query view

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')
# If there are other tables , have access to union all The joint query , If not , Ignore the above two sentences 

8、 ... and 、 Behavior audit device configuration

Take the behavior audit equipment of Shenxin as an example

1、 add to SQL Server Database as an external authentication database

![ Insert picture description here ](https://img-blog.csdnimg.cn/20200307232828193.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l0bHpxMDIyOA==,size_16,color_FFFFFF,t_70 =500x)
![ Insert picture description here ](https://img-blog.csdnimg.cn/20200307232903756.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l0bHpxMDIyOA==,size_16,color_FFFFFF,t_70 =500x)

2、 Add single sign on server configuration

Database server select the newly added database server
Query statement :select * from userip
 Insert picture description here
Test the effectiveness
 Insert picture description here
 Insert picture description here




3、 Configure single sign on Policy

The authentication scope is filled in the user terminal address field .
 Insert picture description here
In the authentication mode tab , The authentication method is single sign on . At the same time, users with single sign on failure are configured to use IP Address as user name .
 Insert picture description here
Once the configuration is complete , Current traffic to the behavioral audit facility , This authentication policy will be triggered to create users .
also , Will pass the previously associated authentication database , Automatically get the user name and create the user !!
 Insert picture description here





Get it done !

版权声明
本文为[osc_ 0m0d4mbq]所创,转载请带上原文链接,感谢

Scroll to Top