Adventures in LogParser, HTA and charts

In my earlier post “Slicing and dicing with LogParser & VBA”  I had mentioned that LogParser is really a slick Microsoft utility that can be used to obtain information on files, event logs, IIS logs etc. Continuing on the journey in LogParser I came to realize that you can also create cool charts with output of LogParser which can be either a line graph, a pie chart , a 3D pie chart a 3D bar chart etc. The options are many. So I started to play around with the utility.

To create a chart you can run the command from a LogParser prompt. Some samples are shown below

LogParser “SELECT TOP  10 TO_LOWERCASE (Name) AS NewName, Size INTO .\chart.gif, Path, LastWriteTime FROM ‘” &   files & “‘ WHERE NOT Attributes LIKE ‘%D%’ AND NOT ATTRIBUTES LIKE ‘%H%’ ORDER BY Size DESC ” &   “-chartType:column3D -i:FS –chartTitle: “My chart”

This will create a gif file with a 3D bar chart with the top 10 files by size

Similarly you could also create a 3D pie chart as follows

LogParser “SELECT TOP 5 Name, Size INTO c:\Chart.gif FROM C:\*.* ORDER BY Size DESC” -chartType:PieExploded3D  -i:FS

However I wanted to create these charts in a HTA application and display it dynamically along with the output of LogParser.  Thankfully the procedure is very similar. Here is what you need to do for this.

To do this you need to set up the environment as below. I have used VBscript.

Set objLogParser = CreateObject(“MSUtil.LogQuery”)

Set objInputFormat =   CreateObject(“MSUtil.LogQuery.FileSystemInputFormat”)

Then you need to specify the chart options

Set objOutputChartFormat = CreateObject(“MSUtil.LogQuery.ChartOutputFormat”)

objOutputChartFormat.groupSize = “400×300”

objOutputChartFormat.fileType = “GIF”

objOutputChartFormat.chartType = “Column3D”

objOutputChartFormat.categories = “ON”

objOutputChartFormat.values = “ON”

objOutputChartFormat.legend = “ON”

Finally create a LogParser query and execute it as shown below where “topN” & the directory path “files” is taken as input from the user

strQuery1 = “SELECT TOP ” & topN & ” Name, Size INTO c:\tes\filesize.gif FROM ” &  files & ” WHERE NOT Attributes LIKE ‘%D%’ AND NOT ATTRIBUTES LIKE ‘%H%’ ORDER BY Size DESC ”

objOutputChartFormat.config = “c:\test\FileSize.js”

Set objRecordSet1 = objLogParser.ExecuteBatch(strQuery1,  objInputFormat , objOutputChartFormat )

To specify the chart title, the X axis & Y axis a javascript/VBscript file has to be created as  below (FileSize.js) which is specified in objOutputChartFormat.config

FileSize.js (contents)

// Set the title above the chart.

chart.HasTitle = true;

chart.Title.Caption = “Top N files by size”

 

// Set the border style for the chart.

chartSpace.Border.Color = “#000000”;

chartSpace.Border.Weight = 2;

 

// Change the background color for the plot area.

chart.PlotArea.Interior.Color = “#f0f0f0”;

 

// Set the font size for the chart values.

chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 6;

 

// Set the caption below the chart.

chartSpace.HasChartSpaceTitle = true;

chartSpace.ChartSpaceTitle.Caption =

    “This chart shows the Top N files by file sizes in the specified directory “;

 

chartSpace.ChartSpaceTitle.Font.Size = 10;

chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

 

// Set the style and caption for the Y axis.

chart.Axes(0).Font.Size = 8;

chart.Axes(0).HasTitle = true;

chart.Axes(0).Title.Caption = “File Name”;

chart.Axes(0).Title.Font.Size = 9;

 

// Set the style and caption for the X axis.

chart.Axes(1).Font.Size = 7;

chart.Axes(1).HasTitle = true;

chart.Axes(1).Title.Caption = “Size in bytes”;

chart.Axes(1).Title.Font.Size = 9;

 

 

Lastly to display the chart dynamically as it is created in the HTA file do the following

imagearea.innerHTML = “

where imagearea will be specified in the HTML portion as

where filesize.png is any  image prior to the creation of the chart through LogParser.

A sample output is shown below

LogParser charts are really cool and well worth the effort!

Also see
Brewing a potion with Bluemix, PostgreSQL, Node.js in the cloud
A Bluemix recipe with MongoDB and Node.js A Cloud medley with IBM Bluemix, Cloudant DB and Node.js
Rock N’ Roll with Bluemix, Cloudant & NodeExpress

You may also like
– A crime map of India in R: Crimes against women
– What’s up Watson? Using IBM Watson’s QAAPI with Bluemix, NodeExpress – Part 1
– Bend it like Bluemix, MongoDB with autoscaling – Part 1
– Analyzing cricket’s batting legends – Through the mirage with R
– Masters of spin: Unraveling the web with R

Find me on Google+

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+