Introduction

File Audit Viewer solves the problem of viewing the output of File Server Audit. http://www.codeproject.com/KB/system/FileServerAudit.aspx uses Microsoft SQL to read output of File Server Audit and then displays it in a pivot table like interface.

Background

I wrote this to help my coworkers with reading the output to File Server Audit.

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.

Global Variables

'SQL Connection
Public objSQLConnection As System.Data.SqlClient.SqlConnection = _
New System.Data.SqlClient.SqlConnection(My.Settings.SQLConnection)
'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 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 variables
	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 objSQLDataAdapter As System.Data.SqlClient.SqlDataAdapter
	Dim ObjDataSet As System.Data.DataSet
	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)

Now we open SQL Connection and loop though all the parent nodes adding them to the treeview. 

'Updates title in form
Me.Invoke(UpdateTitleDel, "Working")
StartTime = Now
'open the connection
objSQLConnection.Open()
'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 & "'" _
	 & " ORDER BY [" & strParentField & "];"
Else
	strSQL = "SELECT distinct [" & strParentField & "]" _
	 & " FROM [" & My.Settings.SQLTable & "]" _
	 & " Where [RunDate] = '" & StrRunDate & "'  _
	 And SUBSTRING([FolderPath],1,3) = '" & StrDriveLetter & "'" _
	 & " ORDER BY [" & strParentField & "];"
End If
objSQLDataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSQL, objSQLConnection)
'Try to use parent data
Try
	'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 cancellation
		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, objSQLConnection)
'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

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 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.0.0

  • Reads from MSSQL with table layout of:

    • Table Name: FileAudit

    Column Name Data Type
    ID int
    FolderPath nvarchar(MAX)
    AccountSAMAccountName nvarchar(MAX)
    GroupSAMAccountName nvarchar(MAX)
    ManagedBy nvarchar(MAX)
    Inheritance nvarchar(MAX)
    Rights nvarchar(MAX)
    Owner nvarchar(MAX)
    RunDate bigint
    IsInherited varchar(50)
关键字 file,server,viewer
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架
新浪微博粉丝精灵，刷粉丝、刷评论、刷转发、企业商家微博营销必备工具"