1 系统更新状态检查器
Sub 更新检测()
' 获取最近20条系统更新记录
Dim updateQuery As String
updateQuery = "SELECT HotFixID,InstalledOn FROM Win32_QuickFixEngineering ORDER BY InstalledOn DESC"
With ActiveSheet.QueryTables.Add( _
Connection:=Array("OLEDB;Provider=WinMgmts;"), _
Destination:=Range("A1"))
.CommandText = Array(updateQuery)
.RowNumbers = False
.Refresh BackgroundQuery:=False
End With
' 高亮三个月前的更新
Range("B:B").AutoFilter Field:=2, Criteria1:="<" & DateAdd("m", -3, Date)
ActiveSheet.AutoFilter.Range.Interior.Color = RGB(255, 240, 240)
End Sub
2 进程黑名单管理器
Sub 危险进程拦截()
' 自动终止高风险进程（需管理员权限）
Dim psList As Object
Set psList = GetObject("winmgmts:\\.\root\cimv2").ExecQuery( _
"SELECT * FROM Win32_Process WHERE Name LIKE '%cmd.exe%' OR Name LIKE '%powershell.exe%'")
For Each proc In psList
If proc.Name <> "EXCEL.EXE" Then ' 排除自身进程
proc.Terminate
Cells(Rows.Count, 1).End(xlUp).Offset(1) = "已终止：" & proc.Name & " PID:" & proc.ProcessId
End If
Next
MsgBox "共拦截" & psList.Count & "个可疑进程！", vbCritical
End Sub
3 网络连通性测试仪
Sub 网络诊断()
' 批量Ping服务器生成状态报告
Dim servers As Variant: servers = Array("192.168.1.1", "10.0.0.2", "gateway")
For i = 0 To UBound(servers)
result = CreateObject("WScript.Shell").Exec("ping -n 1 " & servers(i)).StdOut.ReadAll
Cells(i + 2, 1) = servers(i)
Cells(i + 2, 2) = IIf(InStr(result, "TTL"), "在线", "离线")
' 可视化信号强度
Cells(i + 2, 3).Formula = "=REPT(""█"", RANDBETWEEN(1,5))"
Next
Columns("C").Font.Color = RGB(0, 176, 80)
End Sub
4 防火墙端口管控器
Sub 端口管理()
' 自动开关指定防火墙端口（需管理员权限）
Dim portRule As String: portRule = "规则名=允许SQL端口 协议=TCP 端口=1433 操作=允许"
' 删除旧规则
Shell "netsh advfirewall firewall delete rule name=""允许SQL端口""", vbHide
If Range("B2").Value = "启用" Then
' 添加新规则
Shell "netsh advfirewall firewall add rule " & portRule, vbHide
Cells(2, 3).AddComment "最后配置：" & Now()
End If
MsgBox "1433端口策略已更新！", vbInformation
End Sub
5 系统事件日志分析器
Sub 日志分析()
' 抓取24小时内系统错误事件
Dim eventLog As Object
Set eventLog = CreateObject("WScript.Shell").Exec( _
"wevtutil qe System /q:*[System[Level=2 and TimeCreated[timediff(@SystemTime) <= 86400000]]]")
' 结构化输出到Excel
With Sheets("错误日志")
.Range("A2:D1000").Clear
logData = Split(eventLog.StdOut.ReadAll, "
For i = 1 To UBound(logData)
.Cells(i + 1, 1) = ExtractXML(logData(i), "EventID")
.Cells(i + 1, 2) = ExtractXML(logData(i), "TimeCreated")
.Cells(i + 1, 3) = ExtractXML(logData(i), "Computer")
.Cells(i + 1, 4) = ExtractXML(logData(i), "Message")
Next
End With
Columns("D").ColumnWidth = 60
End Sub
Function ExtractXML(strData As String, node As String)
' XML解析辅助函数
ExtractXML = Split(Split(strData, "<" node>")(1), "")(0)
End Function