File Server Viewer

• Download source - 344.4 KB
• Download demo - 188.6 KB

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 1^st 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 2^nd child nodes adding them to the 1^st 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

MySQL Table Name: FileAudit

Version 1.0.0

MSSQL Table Name: FileAudit