文件服务器查看器
查看文件服务器审计程序中的记录
引言
文件审计查看器解决了查看文件服务器审计输出的问题。 这篇文章 使用 Microsoft SQL 或 MySQL 读取文件服务器审计的输出，然后以类似透视表界面的方式显示它。
背景
我编写这个是为了帮助我的同事阅读文件服务器审计的输出。
屏幕截图
Using the Code
该代码通过枚举卷上的每条记录，然后将其组织起来以便更容易地进行分析。可以展开记录以查看相关信息。
导入
Imports MySql.Data.MySqlClient
全局变量
'SQL Connection
Public objSQLConnection As Object
Dim objSQLDataAdapter As Object
'Global variables for communication between threads
Public strParentField As String
Public c1Field As String
Public c2Field As String
Public c3Field As String
Public c4Field As String
Public c5Field As String
Public c6Field As String
Public intParentkey As Integer
Public intc1key As Integer
Public intc2key As Integer
Public intc3key As Integer
Public intc4key As Integer
Public intc5key As Integer
Public intc6key As Integer
Public IsInherited As Boolean = False
Public StrRunDate As String
Public StrDriveLetter As String
Public StrComputerName As String
Public StrSelectedTreeView As String
Public StrDatabase As String
Public StrTableName As String = My.Settings.SQLTable
设置用于枚举记录的 Backgroundworker sub。
Private Sub BackgroundWorker1_DoWork(sender As System.Object, _
e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
'Setup for private varibles
Dim strSQL As String
Dim strTemp As String
Dim ArgDel(1) As Object
Dim StartTime As DateTime, EndTime As DateTime, ElapsedTime As TimeSpan
'SQL
Dim ObjDataSet As System.Data.DataSet
Dim objSQLCommand As Object
Dim parentrow As DataRow
Dim childrow1 As DataRow
Dim childrow2 As DataRow
Dim ptable As DataTable
Dim c1table As DataTable
Dim c2table As DataTable
'TreeView
Dim RootTree As New TreeNode
Dim pnode As TreeNode = New TreeNode
Dim cnode1 As TreeNode = New TreeNode
Dim cnode2 As TreeNode = New TreeNode
Dim cnode3 As TreeNode = New TreeNode
Dim cnode4 As TreeNode = New TreeNode
Dim cnode5 As TreeNode = New TreeNode
Dim cnode6 As TreeNode = New TreeNode
'Delegates
Dim UpdateTitleDel As UpdateTitleDelegate = New UpdateTitleDelegate(AddressOf UpdateTitle)
Dim UpdateTreeViewDel As UpdateTreeViewDelegate = _
New UpdateTreeViewDelegate(AddressOf UpdateTreeView)
Dim UpdateTreeViewDelC As UpdateTreeViewDelegateC = _
New UpdateTreeViewDelegateC(AddressOf UpdateTreeViewC)
'Updates title in form
Me.Invoke(UpdateTitleDel, "Working")
StartTime = Now
现在我们打开 MSSQL 连接并循环遍历所有父节点，将它们添加到 treeview 中。
'Updates title in form
Me.Invoke(UpdateTitleDel, "Working")
StartTime = Now
'Try to use parent data
Try
Select Case UCase(My.Settings.SQLServerType)
Case "MSSQL"
'Select parent data using strParentField
If Not IsInherited Then
strSQL = "SELECT distinct [" & strParentField & "]" _
& " FROM [" & My.Settings.SQLTable & "]" _
& " Where [RunDate] = '" & StrRunDate & "' _
And [IsInherited] = '" & IsInherited.ToString & "' _
And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "' _
And [Computer] = '" & StrComputerName & "'" _
& " ORDER BY [" & strParentField & "];"
Else
strSQL = "SELECT distinct [" & strParentField & "]" _
& " FROM [" & My.Settings.SQLTable & "]" _
& " Where [RunDate] = '" & StrRunDate & "' _
And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "' _
And [Computer] = '" & StrComputerName & "'" _
& " ORDER BY [" & strParentField & "];"
End If
If objSQLConnection Is Nothing Then
objSQLConnection = New System.Data.SqlClient.SqlConnection(My.Settings.SQLConnection)
StrDatabase = objSQLConnection.Database
If Not objSQLConnection.state = ConnectionState.Open Then objSQLConnection.Open()
End If
objSQLDataAdapter = New System.Data.SqlClient.SqlDataAdapter_
(strSQL, My.Settings.SQLConnection)
'Fill ptable
ObjDataSet = New System.Data.DataSet
objSQLDataAdapter.Fill(ObjDataSet, My.Settings.SQLTable)
ptable = ObjDataSet.Tables(My.Settings.SQLTable)
'Loops though all parent records
For Each parentrow In ptable.Rows
'checks for cancelation
If BackgroundWorker1.CancellationPending = True Then
Exit Try
End If
'Add parent node on to root node
pnode = New TreeNode(parentrow(strParentField))
Me.Invoke(UpdateTreeViewDel, pnode, StrSelectedTreeView)
下面，我们循环遍历所有第一级子节点，并将它们添加到父节点中。
'Start 1st child
'Select 1st child data using c1Field
If Not IsInherited Then
strSQL = "SELECT distinct [" & c1Field & "]" _
& " FROM [" & My.Settings.SQLTable & "]" _
& " Where [RunDate] = '" & StrRunDate & "' _
And [IsInherited] = '" & IsInherited.ToString & "' _
And [" & strParentField & "] = '" & parentrow(strParentField) & "' _
And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "'" _
& " ORDER BY [" & c1Field & "];"
Else
strSQL = "SELECT distinct [" & c1Field & "]" _
& " FROM [" & My.Settings.SQLTable & "]" _
& " Where [RunDate] = '" & StrRunDate & "' _
And [" & strParentField & "] = '" & parentrow(strParentField) & "' _
And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "'" _
& " ORDER BY [" & c1Field & "];"
End If
objSQLDataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSQL, My.Settings.SQLConnection)
'Try to use 1st child data
Try
'Fill c1table
ObjDataSet = New System.Data.DataSet
objSQLDataAdapter.Fill(ObjDataSet, My.Settings.SQLTable)
c1table = ObjDataSet.Tables(My.Settings.SQLTable)
'Loops though all 1st child records
For Each childrow1 In c1table.Rows
'Adds 1st child node to parent node
cnode1 = New TreeNode(childrow1(c1Field))
cnode1 = Me.Invoke(UpdateTreeViewDelC, pnode, cnode1, childrow1(c1Field), StrSelectedTreeView)
现在，我们循环遍历所有第二级子节点，并将它们添加到第一级子节点中。我们还添加所有剩余信息。
'Start 2nd child
'Select 2nd child data using c2Field
If Not IsInherited Then
strSQL = "SELECT distinct [" & c2Field & "],[" & c3Field & "],_
[" & c4Field & "],[" & c5Field & "],[" & c6Field & "]" _
& " FROM [" & My.Settings.SQLTable & "]" _
& " Where [RunDate] = '" & StrRunDate & "' And [IsInherited] = '" _
& IsInherited.ToString & "' And [" & strParentField & "] = '" _
& parentrow(strParentField) & "'" _
& " And [" & c1Field & "] = '" & childrow1(c1Field) & "' _
And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "'" _
& " ORDER BY [" & c2Field & "];"
Else
strSQL = "SELECT distinct [" & c2Field & "],[" & c3Field & "],_
[" & c4Field & "],[" & c5Field & "],[" & c6Field & "]" _
& " FROM [" & My.Settings.SQLTable & "]" _
& " Where [RunDate] = '" & StrRunDate & "' _
And [" & strParentField & "] = '" & parentrow(strParentField) & "'" _
& " And [" & c1Field & "] = '" & childrow1(c1Field) & "' _
And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "'" _
& " ORDER BY [" & c2Field & "];"
End If
objSQLDataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSQL, objSQLConnection)
'Try to use 2nd child data
Try
'Fill c2table
ObjDataSet = New System.Data.DataSet
objSQLDataAdapter.Fill(ObjDataSet, My.Settings.SQLTable)
c2table = ObjDataSet.Tables(My.Settings.SQLTable)
'Loops though all 2nd child records
For Each childrow2 In c2table.Rows
'Since we are expending only to the 2nd level
'we fill out the rest of the data here
strTemp = String.Format("{0,-50} {1,-50} {2,-50} {3,-50} {4,-50}", _
childrow2(c2Field), childrow2(c3Field), _
childrow2(c4Field), childrow2(c5Field), childrow2(c6Field))
'Adds 2nd child node to 1st child node
cnode2 = New TreeNode(strTemp)
cnode2 = Me.Invoke(UpdateTreeViewDelC, _
cnode1, cnode2, strTemp, StrSelectedTreeView)
Next childrow2
Catch ex As Exception
MsgBox("2nd Child Node Error: " & Err.Description)
End Try
Next childrow1
Catch ex As Exception
MsgBox("1st Child Node Error: " & Err.Description)
End Try
Next parentrow
Catch ex As Exception
MsgBox("Parent Node Error: " & Err.Description)
End Try
关闭 SQL 连接并结束 sub。
'close the connection
objSQLConnection.Close()
'Updates title in form
EndTime = Now
ElapsedTime = EndTime.Subtract(StartTime)
Me.Invoke(UpdateTitleDel, "Done; Total Time :" & vbTab & ElapsedTime.Hours & _
":" & ElapsedTime.Minutes & ":" & ElapsedTime.Seconds & "." _
& ElapsedTime.Milliseconds)
BackgroundWorker1.Dispose()
End Sub
这是将父节点添加到 treeview 根目录的 sub 。以及添加子节点的函数。这些必须通过委托并通过使用 invoke 命令来调用。
Delegate Sub UpdateTreeViewDelegate_
(ByVal ObjInput As TreeNode, ByVal StrTree As String)
Delegate Function UpdateTreeViewDelegateC(ByVal ObjInputP As TreeNode, _
ByVal ObjInputC As TreeNode, ByVal StrInput As String, _
ByVal StrTree As String) As TreeNode
Public Sub UpdateTreeView(ByVal ObjInput As TreeNode, ByVal StrTree As String)
'Adds new child node to root node.
Select Case UCase(StrTree)
Case UCase("FtoUTreeView")
FtoUTreeView.Nodes.Add(ObjInput)
FtoUTreeView.Update()
Case UCase("UtoFTreeView")
UtoFTreeView.Nodes.Add(ObjInput)
UtoFTreeView.Update()
Case UCase("GtoFTreeView")
GtoFTreeView.Nodes.Add(ObjInput)
GtoFTreeView.Update()
End Select
End Sub
Public Function UpdateTreeViewC(ByVal ObjInputP As TreeNode, _
ByVal ObjInputC As TreeNode, ByVal StrInput As String, _
ByVal StrTree As String) As TreeNode
'Needs to be a function otherwise the Treeview would not populate correctly
'Adds new child node to parent
Select Case UCase(StrTree)
Case UCase("FtoUTreeView")
ObjInputC = ObjInputP.Nodes.Add(StrInput)
ObjInputC.Tag = StrInput
Case UCase("UtoFTreeView")
ObjInputC = ObjInputP.Nodes.Add(StrInput)
ObjInputC.Tag = StrInput
Case UCase("GtoFTreeView")
ObjInputC = ObjInputP.Nodes.Add(StrInput)
ObjInputC.Tag = StrInput
End Select
Return ObjInputC
End Function
关注点
代码和输入中总会有错误，你不可能完全规避它们；但你可以让它适用于你想要做的事情。感谢所有帮助我学习的人。
历史
版本 1.1.0
- 更新了表格布局以支持 MSSQL 和 MySQL
- 添加了计算机字段以允许按计算机进行过滤
MSSQL 表名：FileAudit
| 列名 | 数据类型 |
ID |
int |
FolderPath |
nvarchar(MAX) |
AccountSAMAccountName |
nvarchar(MAX) |
GroupSAMAccountName |
nvarchar(MAX) |
ManagedBy |
nvarchar(MAX) |
继承 |
nvarchar(MAX) |
IsInherited |
nvarchar(MAX) |
权限 |
nvarchar(MAX) |
Owner |
nvarchar(MAX) |
电脑 |
nvarchar(MAX) |
RunDate |
bigint |
MySQL 表名：FileAudit
| 列名 | 数据类型 |
ID |
int |
FolderPath |
LONGTEXT |
AccountSAMAccountName |
LONGTEXT |
GroupSAMAccountName |
LONGTEXT |
ManagedBy |
LONGTEXT |
继承 |
LONGTEXT |
IsInherited |
LONGTEXT |
权限 |
LONGTEXT |
Owner |
LONGTEXT |
电脑 |
LONGTEXT |
RunDate |
bigint |
版本 1.0.0
MSSQL 表名：FileAudit
| 列名 | 数据类型 |
ID |
int |
FolderPath |
nvarchar(MAX) |
AccountSAMAccountName |
nvarchar(MAX) |
GroupSAMAccountName |
nvarchar(MAX) |
ManagedBy |
nvarchar(MAX) |
继承 |
nvarchar(MAX) |
IsInherited |
nvarchar(MAX) |
权限 |
nvarchar(MAX) |
Owner |
nvarchar(MAX) |
RunDate |
bigint |