Slicing and dicing with LogParser & VBA


LogParser  is probably one of the most understated utilities from Microsoft. LogParser 2.2. Log parser is a powerful and a versatile tool that provides a SQL like query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows such as the Event Log, the Registry, the file system, and Active Directory. There are so many things that you can do to search and collate data on Windows with LogParser. The nice thing about it is that the query is SQL like and fairly intuitive.

On a UNIX/Linux system we would have to run a shell command for e.g. (ls –lrt) and pipe it to “sort”,”awk” or a “sed” utility. LogParser is all this rolled into one. If you have been reading my earlier post “Building a respectable VBA with Excel Application” will realize that using LogParser with VBA is a fairly potent combination and you can build nifty applications quickly.

Display files in directories

For e.g. to display file sizes in specific directories or under all directories we can create VBA GUI as below

The logParser commands are to display all files in the directory with Size, LastWriteTime & name is below

Set objLogParser = CreateObject(“MSUtil.LogQuery”)

Set objInputFormat = _

CreateObject(“MSUtil.LogQuery.FileSystemInputFormat”)

If OptionButton1.value = True Then

objInputFormat.recurse = -1

End If

If OptionButton2.value = True Then

objInputFormat.recurse = 0

End If

strQuery = “SELECT TO_LOWERCASE (Name) AS NewName,  Size, Path, LastWriteTime FROM ‘” &   files & “‘ ORDER BY LastWriteTime ASC”

Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

Do While Not objRecordSet.AtEnd

Set objRecord = objRecordSet.GetRecord

strPath = objRecord.GetValue(“Path”)

fileSize = objRecord.GetValue(“Size”)

lastWriteTime = objRecord.GetValue(“LastWriteTime”)

objRecordSet.MoveNext

Loop

Top N files

You can display the Top N files by size, lastWriteTime or name with a suitable VBA GUI as below

This can be written in logParser as

Set objLogParser = CreateObject(“MSUtil.LogQuery”)

Set objInputFormat = _

CreateObject(“MSUtil.LogQuery.FileSystemInputFormat”)

If OptionButton7.value = True Then

strQuery = “SELECT TOP ” & topN & ” TO_LOWERCASE (Name) AS NewName, Size, Path, LastWriteTime FROM ‘” & _

files & “‘ WHERE NOT Attributes LIKE ‘%D%’ ORDER BY Size DESC”

ElseIf OptionButton8.value = True Then

strQuery = “SELECT TOP ” & topN & ” TO_LOWERCASE (Name) AS NewName, Size, Path, LastWriteTime FROM ‘” & _

files & “‘ WHERE NOT Attributes LIKE ‘%D%’ ORDER BY lastWriteTime ASC”

ElseIf OptionButton9.value = True Then

strQuery = “SELECT TOP ” & topN & ” TO_LOWERCASE (Name) AS NewName, Size, Path, LastWriteTime FROM ‘” & _

files & “‘ WHERE NOT Attributes LIKE ‘%D%’ ORDER BY NewName ASC”

End If

The SpinButtons subroutines can be updates as follows

Private Sub SpinButton1_SpinDown()

If TextBox7.value <= 1 Then

MsgBox (“Cannot decrement below 1”)

TextBox7.value = 1

Exit Sub

Else

TextBox7.value = TextBox7.value – 1

End If

End Sub

Private Sub SpinButton1_SpinUp()

TextBox7.value = TextBox7.value + 1

End Sub

Extension based disk management

logParser can also be used to select all files with specified extensions for e.g. tmp,.log etc

Set objLogParser = CreateObject(“MSUtil.LogQuery”)

Set objInputFormat = _

CreateObject(“MSUtil.LogQuery.FileSystemInputFormat”)

files = baseDirectory & “\” & fileExt

strQuery = “SELECT TO_LOWERCASE (Name) AS NewName,  Size ,Path, LastWriteTime FROM ‘” & _   files & “‘ ORDER BY LastWriteTime ASC”

Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

You can also search Event Logs with LogParser

Event Logs

Set objLogParser = CreateObject(“MSUtil.LogQuery”)

Set objInputFormat = _

CreateObject(“MSUtil.LogQuery.EventLogInputFormat”)

If OptionButton1.Value = True Then

strQuery = “SELECT TimeGenerated, EventID, EventTypeName,Message, Strings, SourceName FROM Application WHERE EventID IN ” & str

ElseIf OptionButton2.Value = True Then

strQuery = “SELECT TimeGenerated, EventID, EventTypeName, Message, Strings, SourceName FROM System WHERE EventID IN ” & str

ElseIf OptionButton3.Value = True Then

strQuery = “SELECT TimeGenerated, EventID, EventTypeName, Message, Strings, SourceName FROM Security WHERE EventID IN ” & str

ElseIf OptionButton4.Value = True Then

strQuery = “SELECT TimeGenerated, EventID, EventTypeName, Message, Strings, SourceName FROM Setup WHERE EventID IN ” & str

End If

Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

LogParser is really a cool utility and when combined with VBA can really help in developing nifty applications.

Find me on Google+

Advertisements

6 thoughts on “Slicing and dicing with LogParser & VBA

  1. Pingback: Adventures in LogParser, HTA and charts « Giga thoughts …

  2. Pingback: Tips for building a decent HTA/HTML application | Giga thoughts …

  3. Pingback: Stir fry a VBA with Excel application quickly | Giga thoughts …

  4. Pingback: Building a respectable VBA with Excel Application | Giga thoughts …

  5. Pingback: Get your feet wet with Powershell GUI | Giga thoughts …

  6. Pingback: Powershell GUI – Adding bells and whistles | Giga thoughts …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s