File Server Viewer
Views records from File Server Audit program
Introduction
File Audit Viewer solves the problem of viewing the output of File Server Audit. This article uses Microsoft SQL or MySQL to read output of File Server Audit and then displays it in a pivot table like interface.
Background
I wrote this to help my co-workers with reading the output to File Server Audit.
Screenshots
Using the Code
The code works by enumerating every record for a volume and then organizing it so it can be analyzed easier. Records can be expanded to see related information.
Imports
Imports MySql.Data.MySqlClient
Global Variables
'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
Setup for the Backgroundworker sub that enumerates the records.
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
Now we open MSSQL Connection and loop though all the parent nodes adding them to the 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)
Below, we loop though all of the 1st child nodes adding them to the parent node.
'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)
Now, we loop though all of the 2nd child nodes adding them to the 1st child nodes. We also add all of the remaining information.
'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
Close SQL connection and end 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
Here is the sub for adding the parent node to the root of the treeview. Also the function for adding the child nodes. These have to be called though a delegate and by using the invoke command.
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
Points of Interest
There are always bugs in code and input and you can never code around them all; but you can get it to work for what you want to do. Thanks to everyone that helps me learn.
History
Version 1.1.0
- Updated table layout to allow for MSSQL and MySQL
- Added Computer field to allow filtering of by computer
MSSQL Table Name: FileAudit
| Column Name | Data Type |
ID |
int |
FolderPath |
nvarchar(MAX) |
AccountSAMAccountName |
nvarchar(MAX) |
GroupSAMAccountName |
nvarchar(MAX) |
ManagedBy |
nvarchar(MAX) |
Inheritance |
nvarchar(MAX) |
IsInherited |
nvarchar(MAX) |
Rights |
nvarchar(MAX) |
Owner |
nvarchar(MAX) |
Computer |
nvarchar(MAX) |
RunDate |
bigint |
MySQL Table Name: FileAudit
| Column Name | Data Type |
ID |
int |
FolderPath |
LONGTEXT |
AccountSAMAccountName |
LONGTEXT |
GroupSAMAccountName |
LONGTEXT |
ManagedBy |
LONGTEXT |
Inheritance |
LONGTEXT |
IsInherited |
LONGTEXT |
Rights |
LONGTEXT |
Owner |
LONGTEXT |
Computer |
LONGTEXT |
RunDate |
bigint |
Version 1.0.0
MSSQL Table Name: FileAudit
| Column Name | Data Type |
ID |
int |
FolderPath |
nvarchar(MAX) |
AccountSAMAccountName |
nvarchar(MAX) |
GroupSAMAccountName |
nvarchar(MAX) |
ManagedBy |
nvarchar(MAX) |
Inheritance |
nvarchar(MAX) |
IsInherited |
nvarchar(MAX) |
Rights |
nvarchar(MAX) |
Owner |
nvarchar(MAX) |
RunDate |
bigint |