• VBA读取网络划分的数据

    　　对于目标检测，通过NMap可以得到IP地址信息，为XML文件，现在需要对应到具体的单位。

    　　这个工作很多单位都在做，比如搜索到一些存在高危端口或者漏洞的计算机，现在需要将信息下发到具体的单位方便整改，这就需要做一些准备工作。

    　　对于这个工作，想法很简单也最好实施：就准备一个数据表，准备单位名称、网络起始地址，具体对应的时候就可以通过IP网关信息和子网掩码找到对应的网络起始地址，这样就好对应了。想法是好的，问题是目前只有IP地址信息，没有网关地址和子网掩码就不能得到网络的首地址也就无法按这样的方法进行比较。

    　　可以按照下面的操作方法进行对比。

    　　⑴准备一个Vlan信息表
    　　　　包含信息：单位名称、网络地址前三位、IP起始地址、IP结束地址

    　　⑵准备一个数据表存储特殊例外的IP信息

    　　　　包括信息：单位名称、IP地址、MAC地址、主机名称、备注信息

    　　为了加快检索速度，需要将这些信息一次性读入内存，这样对比就很快，尽量减少重复信息，使用的方法是先定义结构体数组，然后在结构体数组中再定义结构体数组。

    　　声明：

    1. Option Explicit
    2. Public MyIPAddressIndex(5) As IPAddrIndex
    3. Public ArrCount(5) As Integer
    4. Public MyIPSpecialIP() As SpecialIP
    5. Public MyIPToHostName As IPToHostName
    6.  
    7. Type IPAddressRangInfo
    8.     unit As String    '单位名称
    9.     StartAddr As Integer    '起始地址
    10.     EndAddr As Integer    '结束地址
    11. End Type
    12.  
    13. Type SpecialIP
    14.     unit As String    '单位名称
    15.     IPAddr As String    'IP地址
    16.     MacAddr As String    'MAC地址
    17.     HostName As String    '主机名称
    18.     Memo As String    '备注
    19. End Type
    20.  
    21. Type IPAddrIndex
    22.     Prefix As String    '网络地址前三位
    23.     MyIPAddrRange() As IPAddressRangInfo    'IP地址范围信息
    24. End Type
    25.  
    26. Type IPToHostName
    27.     HostName As String    '主机名称
    28.     Unit As String    '单位名称
    29. End Type

    　　读取信息：

    1. Sub ReadyNetworkData()
    2.     Dim ws As Worksheet
    3.     Dim PrefixCol As Range
    4.     Dim UnitCol As Range
    5.     Dim StartAddrCol As Range
    6.     Dim EndAddrCol As Range
    7.     Dim MaxRows As Integer
    8.     Dim iFor As Integer
    9.     Dim StrPrefix As String
    10.     Dim StrCurrent As String
    11.     Dim ICount As Integer
    12.     Dim ISubCount As Integer
    13.     Dim ArrIndex As Integer
    14.     Dim StrAddrStart As String
    15.     Dim StrAddrEnd As String
    16.     Dim StrUnit As String
    17.     Dim StrTemp As String
    18.     Dim ITemp As Integer
    19.     
    20.     Dim UnitCol_B As Range
    21.     Dim IPAddrCol As Range
    22.     Dim MacAddrCol As Range
    23.     Dim HontNameCol As Range
    24.     Dim MemoCol As Range
    25.     
    26.     '读取Vlan信息表
    27.     Set ws = Worksheets("VlanMarkings")
    28.     MaxRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    29.     Set PrefixCol = ws.Range("H2:H" & Trim(str(MaxRows)))
    30.     Set UnitCol = ws.Range("B2:B" & Trim(str(MaxRows)))
    31.     Set StartAddrCol = ws.Range("I2:I" & Trim(str(MaxRows)))
    32.     Set EndAddrCol = ws.Range("J2:J" & Trim(str(MaxRows)))
    33.     StrPrefix = ""
    34.     '读取有多少段地址
    35.     ICount = 0
    36.     For iFor = 1 To MaxRows - 1
    37.         '读取IP地址
    38.         StrCurrent = UCase(Trim(PrefixCol(iFor).Value))
    39.         If StrPrefix <> StrCurrent Then
    40.             MyIPAddressIndex(ICount).Prefix = StrCurrent
    41.             StrPrefix = StrCurrent
    42.             ICount = ICount + 1
    43.         End If
    44.     Next
    45.     '每段有多少个细分范围
    46.     For iFor = 0 To UBound(MyIPAddressIndex)
    47.         StrTemp = MyIPAddressIndex(iFor).Prefix
    48.         ITemp = WorksheetFunction.CountIf(ws.Range("H2:H" & Trim(str(MaxRows))), "=" + StrTemp)
    49.         ArrIndex = GetArrIndex(StrTemp)
    50.         ReDim MyIPAddressIndex(ArrIndex).MyIPAddrRange(0 To ITemp)
    51.     Next
    52.     'GoTo L123
    53.     '读取每段地址范围
    54.     For iFor = 0 To 5
    55.         ArrCount(iFor) = 0
    56.     Next
    57.     For iFor = 1 To MaxRows - 1
    58.         '读取IP地址
    59.         StrPrefix = UCase(Trim(PrefixCol(iFor).Value))
    60.         ArrIndex = GetArrIndex(StrPrefix)
    61.         StrAddrStart = UCase(Trim(StartAddrCol(iFor).Value))
    62.         StrAddrEnd = UCase(Trim(EndAddrCol(iFor).Value))
    63.         StrUnit = UCase(Trim(UnitCol(iFor).Value))
    64.         MyIPAddressIndex(ArrIndex).MyIPAddrRange(ArrCount(ArrIndex)).StartAddr = StrAddrStart
    65.         MyIPAddressIndex(ArrIndex).MyIPAddrRange(ArrCount(ArrIndex)).EndAddr = StrAddrEnd
    66.         MyIPAddressIndex(ArrIndex).MyIPAddrRange(ArrCount(ArrIndex)).unit = StrUnit
    67.         ArrCount(ArrIndex) = ArrCount(ArrIndex) + 1
    68.     Next
    69.     '读取IP地址信息表
    70.     Set ws = Worksheets("SpecialMarkingsRes")
    71.     MaxRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    72.     ReDim MyIPSpecialIP(0 To MaxRows - 1)
    73.     Set UnitCol_B = ws.Range("B2:B" & Trim(str(MaxRows)))
    74.     Set IPAddrCol = ws.Range("C2:C" & Trim(str(MaxRows)))
    75.     Set MacAddrCol = ws.Range("D2:D" & Trim(str(MaxRows)))
    76.     Set HontNameCol = ws.Range("E2:E" & Trim(str(MaxRows)))
    77.     Set MemoCol = ws.Range("F2:F" & Trim(str(MaxRows)))
    78.     
    79.     For iFor = 1 To MaxRows - 1
    80.         MyIPSpecialIP(iFor - 1).HostName = UCase(Trim(HontNameCol(iFor).Value))
    81.         MyIPSpecialIP(iFor - 1).IPAddr = UCase(Trim(IPAddrCol(iFor).Value))
    82.         MyIPSpecialIP(iFor - 1).MacAddr = UCase(Trim(MacAddrCol(iFor).Value))
    83.         MyIPSpecialIP(iFor - 1).Memo = UCase(Trim(MemoCol(iFor).Value))
    84.         MyIPSpecialIP(iFor - 1).unit = UCase(Trim(UnitCol_B(iFor).Value))
    85.         ICount = ICount + 1
    86.     Next
    87. L123:
    88.     '验证读取的数据
    89.     'Dim Jfor As Integer
    90.     'For iFor = 0 To 5
    91.         'For Jfor = LBound(MyIPAddressIndex(iFor).MyIPAddrRange) To UBound(MyIPAddressIndex(iFor).MyIPAddrRange)
    92.             'Debug.Print Str(MyIPAddressIndex(iFor).MyIPAddrRange(Jfor).StartAddr) + "|" + Str(MyIPAddressIndex(iFor).MyIPAddrRange(Jfor).EndAddr) + "|" + MyIPAddressIndex(iFor).MyIPAddrRange(Jfor).Unit
    93.         'Next
    94.     'Next
    95.     'Debug.Print MaxRows - 1
    96.     'For iFor = 0 To UBound(MyIPSpecialIP) - 1
    97.     '    Debug.Print MyIPSpecialIP(iFor).HostName + "|" + MyIPSpecialIP(iFor).IPAddr + "|" + MyIPSpecialIP(iFor).MacAddr + "|" + MyIPSpecialIP(iFor).Memo + "|" + MyIPSpecialIP(iFor).unit
    98.     'Next
    99.     Debug.Print "完成"
    100. End Sub
    102. Function GetArrIndex(str As String) As Integer
    103.     Dim iFor As Integer
    104.     For iFor = 0 To 5
    105.         If MyIPAddressIndex(iFor).Prefix = str Then
    106.             GetArrIndex = iFor
    107.             Exit For
    108.         End If
    109.     Next
    110. End Function
    112. Sub PrintIPAddressRang(ByVal IArrIndex As Integer)
    113.     Dim JFor As Integer
    114.     Debug.Print "前缀：" + MyIPAddressIndex(IArrIndex).Prefix
    115.     For JFor = LBound(MyIPAddressIndex(IArrIndex).MyIPAddrRange) To UBound(MyIPAddressIndex(IArrIndex).MyIPAddrRange)
    116.         Debug.Print str(MyIPAddressIndex(IArrIndex).MyIPAddrRange(JFor).StartAddr) + "|" + str(MyIPAddressIndex(IArrIndex).MyIPAddrRange(JFor).EndAddr) + "|" + MyIPAddressIndex(IArrIndex).MyIPAddrRange(JFor).Unit
    117.     Next
    118. End Sub

    　　判断函数：

    1. Function GetArrIndex(ByVal str As String) As Integer
    2.     Dim iFor As Integer
    3.     For iFor = 0 To 5
    4.         If MyIPAddressIndex(iFor).Prefix = str Then
    5.             GetArrIndex = iFor
    6.             Exit For
    7.         End If
    8.     Next
    9. End Function
    10.  
    11. Function GetUserUnit(ByVal StrIpAddr As String) As String
    12.     GetUserUnit = "未知"
    13.     '获取IP地址的前三位
    14.     Dim LastDotPos As Integer
    15.     Dim Prefix As String
    16.     Dim LastIP As Integer
    17.     Dim iFor As Integer
    18.     Dim ArrIndex As Integer
    19.     
    20.     For iFor = Len(StrIpAddr) To 1 Step -1
    21.         If Mid(StrIpAddr, iFor, 1) = "." Then
    22.             LastDotPos = iFor
    23.             Exit For
    24.         End If
    25.     Next iFor
    26.     
    27.     '判断具体的使用单位
    28.     Prefix = Mid(StrIpAddr, 1, LastDotPos - 1)
    29.     LastIP = Val(Right(StrIpAddr, Len(StrIpAddr) - LastDotPos))
    30.     '根据前三位确定具体的网段
    31.     ArrIndex = GetArrIndex(Prefix)
    32.     
    33.     '在网段内进行比较
    34.     For iFor = LBound(MyIPAddressIndex(ArrIndex).MyIPAddrRange) To UBound(MyIPAddressIndex(ArrIndex).MyIPAddrRange) - 1
    35.         If LastIP >= MyIPAddressIndex(ArrIndex).MyIPAddrRange(iFor).StartAddr And LastIP <= MyIPAddressIndex(ArrIndex).MyIPAddrRange(iFor).EndAddr Then
    36.             GetUserUnit = MyIPAddressIndex(ArrIndex).MyIPAddrRange(iFor).Unit
    37.             'Debug.Print Prefix + "||" + str(LastIP) + "||" + str(ArrIndex) + "||" + GetUserUnit
    38.             Exit For
    39.         End If
    40.     Next
    41. End Function
    42.  
    43. Function GetHostName(ByVal StrIpAddr As String) As IPToHostName
    44.     Dim TempIPTOHostName As IPToHostName
    45.     TempIPTOHostName.HostName = ""
    46.     TempIPTOHostName.Unit = ""
    47.     Dim iFor As Integer
    48.     
    49.     For iFor = LBound(MyIPSpecialIP) To UBound(MyIPSpecialIP)
    50.         If MyIPSpecialIP(iFor).IPAddr = StrIpAddr Then
    51.             TempIPTOHostName.HostName = MyIPSpecialIP(iFor).HostName
    52.             TempIPTOHostName.Unit = MyIPSpecialIP(iFor).Unit
    53.             GetHostName = TempIPTOHostName
    54.             Exit For
    55.         End If
    56.     Next iFor
    57. End Function
    58.  
    59. Sub JuedeIpToUnit()
    60.     '根据IP地址得到单位名称
    61.     Dim iFor As Integer
    62.     Dim StrIpAddr As String
    63.     Dim Unit As String
    64.     Dim HostName As String
    65.     Dim IRecord As Integer
    66.     
    67.     ReadyNetworkData
    68.     StartRow = 2
    69.     EndRow = 200
    70.     Sheets("BeDetectedData").Activate
    71.     '读取IP地址数据并进行判断
    72.     For iFor = StartRow To EndRow
    73.         Range("B" & Trim(str(iFor))).Select
    74.         StrIpAddr = Trim(Selection.FormulaR1C1)
    75.         Unit = GetUserUnit(StrIpAddr)
    76.         HostName = GetHostName(StrIpAddr)
    77.         '存入表中
    78.         '所属单位
    79.         Range("C" & Trim(str(IRecord))).Select
    80.         Selection.FormulaR1C1 = Unit
    81.         '主机名称
    82.         Range("D" & Trim(str(IRecord))).Select
    83.         Selection.FormulaR1C1 = HostName
    84.     Next iFor
    85.     MsgBox "完成！"
    86. End Sub

    　　有了上面的信息，就可以对比检索NMap保存的XML文件，将IP地址和具体单位一一对应起来了，剩下的工作就比较好做了。

    　　下面的代码是根据给定的IP得到这个IP地址对应的单位以及是否是网关地址、网络打印机、视频设备等（这个数据可以根据需要添加）。

    1. Sub JudgeData()
    2.     Dim XmlFile As String
    3.     Dim CurrentFilePath As String
    4.     CurrentFilePath = Application.ActiveWorkbook.Path
    5.     Dim XmlDoc As DOMDocument60
    6.     Dim hostNodes As Object
    7.     Dim iFor As Integer
    8.     Dim hostnode As Object
    9.     Dim addressNode As Object
    10.     Dim StrIpAddr As String
    11.     Dim Unit As String
    12.     Dim HostName As String
    13.     Dim IRecord As Integer
    14.         
    15.     IRecord = 219
    16.     Set XmlDoc = New DOMDocument60
    17.     XmlFile = CurrentFilePath + "\20231018-B.xml"
    18.     ReadyNetworkData
    19.     Sheets("BeDetectedData").Activate
    20.     If XmlDoc.Load(XmlFile) = True Then
    21.         '读取数据并进行判断
    22.         Set hostNodes = XmlDoc.getElementsByTagName("host")
    23.         '遍历host节点集合，获取IP地址
    24.         For iFor = 0 To hostNodes.Length - 1
    25.             Set hostnode = hostNodes(iFor)
    26.             Set addressNode = hostnode.getElementsByTagName("address")(0)
    27.             If addressNode.getAttribute("addrtype") = "ipv4" Then
    28.                 StrIpAddr = addressNode.getAttribute("addr")
    29.                 Unit = GetUserUnit(StrIpAddr)
    30.                 '存入表中
    31.                 IRecord = IRecord + 1
    32.                 'IP地址
    33.                 Range("B" & Trim(str(IRecord))).Select
    34.                 Selection.FormulaR1C1 = StrIpAddr
    35.                 '所属单位
    36.                 Range("C" & Trim(str(IRecord))).Select
    37.                 Selection.FormulaR1C1 = Unit
    38.                 '主机名称
    39.                 MyIPToHostName.HostName = ""
    40.                 MyIPToHostName.Unit = ""
    41.                 MyIPToHostName = GetHostName(StrIpAddr)
    42.                 If MyIPToHostName.HostName <> "" And MyIPToHostName.Unit <> "" Then
    43.                     Range("D" & Trim(str(IRecord))).Select
    44.                     Selection.FormulaR1C1 = MyIPToHostName.HostName
    45.                     Range("C" & Trim(str(IRecord))).Select
    46.                     Selection.FormulaR1C1 = MyIPToHostName.Unit
    47.                 End If
    48.             End If
    49.             TempCount = TempCount + 1
    50.         MsgBox "完成！"
    51.     Else
    52.         MsgBox "检查XML文件内容！"
    53.     End If
    54. End Sub

    　　今天处理NMap输出的XML文件时遇到错误，我还以为是代码有问题，郁闷了很长时间，后面查明是NMap添加了无用信息导致，删除就好了。

  • 相关阅读:
    矩阵秩的定义和相关结论汇总
    EIP-3664合约研究笔记03--装备属性随机生成算法
    5G+北斗高精度定位系统适用于哪些行业领域？
    12月2日：thinkphp中数据库完结
    [PyTorch][chapter 60][强化学习-2-有模型学习2]
    人工智能轨道交通行业周刊-第21期（2022.10.31-11.6）
    2023平台工程崭露头角，AI 带来新机遇与挑战
    uniapp制作安卓原生插件踩坑
    mysql性能优化之数据类型(持续更新)
    【QML】QML中的动画总结
  • 原文地址：https://blog.csdn.net/dawn0718/article/details/133791583