Hey, Scripting Guy! Event 9 *Solutions* from Expert Commentators (Beginner and Advanced; the javelin throw)


(Note: These solutions were written for Event 9.) 

Beginner Event 9: The javelin throw

For the javelin throw event, you will soar as you write a time logger.

Guest commentator: Salvador Manaois III

 Image of guest commentator Salvador Manaois III

Salvador Manaois III is a senior systems engineer at Infineon Technologies Asia Pacific Pte Ltd. He currently holds the MCITP certification for both Enterprise Administrator and Server Administrator. He also holds the MCTS (x5), MCSE, and MCSA certifications. He actively evangelizes the use of automation (through scripts and other technologies) both at work and in the various IT user groups in which he is involved.  He is also a moderator for The Official Scripting Guys Forum and maintains the Bytes & Badz blog.


VBScript solution

The Beginner Event 9 javelin throw was made easier by the fact that I am an operations guy. Attending to users, troubleshooting problems, and project-related work are part and parcel of what I do.

The first thing to do is to define the way with which the user inputs the data. I was thinking of popping up input boxes for the user but this approach is not too efficient. I ended up using an argument passed via the command line. Using the command line offers both simplicity and speed.

The next thing to do is to validate the data that is entered via the command line and to store the input into an array. Some datafor example, the Category fieldare mapped to a predefined category; the date and time will default to the current date and time.  Additionally, if the Category field is greater than 4 (cannot be mapped to the predefined categories), it is reassigned the value of 4 (Others). By defining default values for values, it makes it easier for the busy IT pro to make an entry.

For the BeginnerEvent9Solution.vbs script, I used two subroutines. The first subroutine, ValidateParameter, accepts the command-line argument and is used to validate the command-line values as mentioned earlier. The second subroutine, ShowUsage, is used to provide command-line help.

The complete BeginnerEvent9Solution.vbs script is seen here.


Const ForWriting = 2
Const ForAppending = 8

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

If Wscript.Arguments.Count = 0 Then
    sParams = Wscript.Arguments(0)
End If

strNewText = Date
strNewText = Replace(strNewText,”/”, “-“)
OutputFile = strNewText & “_MyWork.csv”

If objFSO.FileExists(OutputFile) then
   Set oOutputFile = objFSO.OpenTextFile(OutputFile, ForAppending)
   oOutputFile.Writeline sParams
   Set oOutputFile = objFSO.CreateTextFile(OutputFile, ForWriting)
   oOutputFile.Writeline “Category,Time Completed,Time Used (in hours),Description,Status,Remarks”
   oOutputFile.Writeline sParams
End if

Sub ValidateParameter(Params)
  arrParams = Split(Params,”,”)
  If Ubound(arrParams)+1 <> 6 then
    Wscript.Echo “You have entered an invalid number of parameters. Please try again.”
    Exit Sub
  End if
  Select Case arrParams(0)
   Case 1 arrParams(0) = “Incident”
   Case 2 arrParams(0) = “Change Task”
   Case 3 arrParams(0) = “Project”
   Case 4 arrParams(0) = “Others”
   Case Else
        Wscript.Echo “You have entered an invalid category. Selecting “”Others””.”
        arrParams(0) = “Others”
  End Select
  if arrParams(1) = nul then
    arrParams(1) = Now
  end if
  sParams = Join(arrParams, “,”)
End Sub

Sub ShowUsage
  Wscript.Echo “LogMyWork.vbs Category, TimeCompleted, TimeSpent, Description, Status, Remarks”
  Wscript.Echo vbReturn
  Wscript.Echo “Description : This script is used to log daily activities to an Office Excel file.”
  Wscript.Echo vbReturn
  Wscript.Echo “Parameter List:”
  Wscript.Echo vbTab & “Category” & vbTab & “Specifies the category of the task: 1 for Incidents,”
  Wscript.Echo vbTab & vbTab & vbTab & “2 for Change Tasks/Requests, 3 for Projects, and”
  Wscript.Echo vbTab & vbTab & vbTab & “4 for Other”
  Wscript.Echo vbReturn
  Wscript.Echo vbTab & “TimeCompleted” & vbTab & “Specifies the date and time the task was completed,”
  Wscript.Echo vbTab & vbTab & vbTab & “stopped, or put to pending. Leave this field blank if current”
  Wscript.Echo vbTab & vbTab & vbTab & “date and time is to be used.”
  Wscript.Echo vbReturn
  Wscript.Echo vbTab & “TimeSpent” & vbTab & “Specifies the amount of time spent to complete”
  Wscript.Echo vbTab & vbTab & vbTab & “the task (in hours).”
  Wscript.Echo vbReturn
  Wscript.Echo vbTab & “Description” & vbTab & “A brief description of the task done.”
  Wscript.Echo vbReturn
  Wscript.Echo vbTab & “Status” & vbTab & vbTab & “Status of the task (Completed, Pending, Closed).”
  Wscript.Echo vbReturn
  Wscript.Echo vbTab & “Remarks” & vbTab & vbTab  & “Remarks or comments.”
  Wscript.Echo vbReturn
  Wscript.Echo “Example:”
  Wscript.Echo vbReturn
  Wscript.Echo vbTab & “LogMyWork.vbs ” & “””1,,1,Install Application A,Completed,None”””
End Sub

When the script is run, the information from the script is stored in a comma-separated values (.CSV) file. Each new entry is appended to previous entries in the file. The generated file name uses the current date as part of the file name (for example, 18-5-2009_MyWork.csv).

I would love to port this script to an ASP application, expand the CTI field to cover almost all IT-related stuff, and store the data in a back-end database. With this, I should be able to generate more comprehensive, management-friendly reports to better understand the strengths, weaknesses, and needs of the IT services.

When you open the CSV file in Microsoft Excel, a spreadsheet appears that is similar to this one:

Image of the spreadsheet that appears


Guest commentator: Daniele Muscetta

Daniele Muscetta’s journey with computers and software started when he began programming on a Commodore 64 at the age of ten. Daniele preferred to write programs on his Commodore 64 rather than playing games with it like the other kids did. Today, Daniele works at Microsoft Italy as a premier field engineer who specializes in both System Center Operations Manager and scripting technologies such as VBScript and Windows PowerShell. Daniele maintains a personal blog on his Web site.

Windows PowerShell solution

The most basic time logger would be a single command that writes the current date and time, and a description or name of the activity being logged to a text file.  There are multiple ways to log a datetime, and I tend to like a method I saw mentioned on the Windows PowerShell team blog.  One advantage of this method is it can be changed to some other format.

But the biggest challenge with the Beginner Event 9 scenario is the usability of such an application. I came up with two questions:

·         How do I tell the script I am starting to do something and supply the name of the new task?  I could use command-line parameters or the Read-Host cmdlet.  Both options seemed ugly to me. The script is not one that processes data in log filesit is a user application.

·         How do I tell the script when I am actually done doing something? I could end script execution with CTRL+C. I could require the user to enter another more data into a Read-Host cmdlet. But what if someone makes a mistake and presses the wrong key?

In the end I decided against all these. Thought it is true this is a script, this script challenge looked to me more like an application!

The basic task of writing time-stamped entries is the easy part of the problem. It is the usability of the tool that is the most important part of the task. At the same time I need to keep it simple.

I then thought of breaking loose from the command-line interface (CLI), and decided to use the power of the .NET Framework classes from Windows PowerShell. This is an approach I also used in other situations. It is not too difficult to build a simple Windows Form.

When BasicEvent9Solution.ps1 script is run, a text box appears:

Images of the text box that appears

It has a simple text box where you type the task name and then click Start. At this point, the script will write an entry in the log file saying that we started the task and initialize a System.Diagnostics.StopWatch .NET Framework object (I got the basic idea from a posting made by Thomas Lee who is a moderator for the Official Scripting Guys forum and a fellow 2009 Summer Scripting Games commentator).

At this point, according to my use case scenario, you minimize the application and keep doing your work.

When you are done with your work, bring the form back and click Stop. At that point the timer will stop, and another entry will be written in the log file saying that you have done that task and how long it took you to complete it. A sample of the output from the logfile.txt file is seen here:

Image of a sample of the ouput from logfile.txt

Of course the BeginnerEvent9Solution.ps1 script can be improved in a number of ways. Some of the things I have thought about doing to the script include the following:

·         Display the actual timer progress in the GUI while it runs. In this way, the script could double as a stop watch.

·         Use a better storage format. Instead of using a simple text file I could use a CSV file, an Office Excel spreadsheet, or even a database.

·         Use multiple timers and allowing for “multitasking.” In this approach, which might be useful in a consulting type of environment, you declare to the tool you are actually performing multiple tasks at once.

The BeginnerEvent9Solution.ps1 script is commented, so it should be easy to read. As with most scripts that create a graphical interface, a large part of the code generates the Form and the entire GUI, while very little of it is the actual “logging engine.” The compete BeginnerEvent9Solution.ps1 script is seen here.


#this is our logfile
$logfile = “c:scriptslogfile.txt”

#StopWatch Object we use to track elapsed time
$sw = new-object System.Diagnostics.StopWatch

Function CreateMainGUI
  # Loads useful .net Assemblies, required to create the GUI….

  #creates form and controls objects
  $form = New-Object System.Windows.Forms.Form
  $buttonStart = New-Object System.Windows.Forms.Button
  $buttonStop = New-Object System.Windows.Forms.Button
  $textBoxTask = new-object System.Windows.Forms.TextBox

  #pauses rendering until later

  # textBoxTask
  $textBoxTask.Location = new-object System.Drawing.Point(10, 10);
  $textBoxTask.Name = “textBoxTask”;
  $textBoxTask.Size = new-object System.Drawing.Size(350, 25);
  $textBoxTask.TabIndex = 7;
  $textBoxTask.Text = “Enter Task Name”;

  # buttonStart
  $buttonStart.Location = new-object System.Drawing.Point(370, 10);
  $buttonStart.Name = “buttonStart”;
  $buttonStart.Size = new-object System.Drawing.Size(50, 20);
  $buttonStart.TabIndex = 4;
  $buttonStart.Text = “Start”;
  $buttonStart.UseVisualStyleBackColor = $true;
           $taskname = $textBoxTask.Text
           StartLogTask $taskname

  # buttonStop
  $buttonStop.Location = new-object System.Drawing.Point(430, 10);
  $buttonStop.Name = “buttonStop”;
  $buttonStop.Size = new-object System.Drawing.Size(50, 20);
  $buttonStop.TabIndex = 4;
  $buttonStop.Text = “Stop”;
  $buttonStop.UseVisualStyleBackColor = $true;
  $buttonStop.Enabled = $false #this will start disabled – we want to START a task first!
           StopLogTask $taskname

  # Form1
  $form.ClientSize = new-object System.Drawing.Size(500, 50);
  $form.Name = “Form1″;
  $form.Text = “Time Logging Script”;

Function StartLogTask([string]$taskname)
         #reset the stopwatch in case it was previously running
         #start the stopwatch again
         #logs the entry
         “$(get-date -f o) Started Working on $taskname”>>$logfile
         #switches the buttons so that START is now disabled and STOP gets enabled
         $buttonStart.Enabled = $false
         $buttonStop.Enabled = $true

Function StopLogTask([string]$taskname)
         #gets the stopwatch’s elapsed time in milliseconds
         $ts = $sw.Elapsed
         #converts those milliseconds to a nicely (?) formatted string
         $ElapsedTime = [system.String]::Format(“{0:00}:{1:00}:{2:00}.{3:00}”, $ts.Hours, $ts.Minutes, $ts.Seconds, $ts.Milliseconds / 10)
         #logs the entry
         “$(get-date -f o) Stopped Working on $taskname – Time worked on this was: $ElapsedTime”>>$logfile

         #switches the buttons so that STOP is now disabled and START is enabled
         $buttonStop.Enabled = $false
         $buttonStart.Enabled = $true

# *** Entry Point to the Script ***


Advanced Event 9:  The javelin throw

In the javelin throw event, you will throw your heart into your work as you attempt to sort a tab-delimited text file based upon a particular column.

Guest commentator: Alex Angelopoulos

Image of guest commentator Alex Angelopoulos

Alex K. Angelopoulos is an IT consultant, an MCSE, and a contributing editor for Windows IT Pro magazine. An avid scripter, Alex writes about Windows task automation using Windows PowerShell and the Windows Script Host.

VBScript solution

You can minimize the effort for the javelin throw event if you realize that the data set is already designed for consumption from Microsoft Office Excel. If you choose to use Excel to solve the problem, you avoid low-level coding in VBScript to handle the rankings.

Even using Excel, the problem isn’t trivial, but you can apply most of the coding techniques to any problem where you may want to leverage Excel as a tool.