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.
Giga thoughts ... 