Using VBScript to Add Hostnames in CSV Logs

Written By: Kevin Jordan

- 24 May 2006 -
















Description: Get your server logs ready for a database by using this VBScript to pull the hostname from a computer and add it to the beginning of each line in a .csv file.

  1. Creating the Logs and Calling the Script
  2. Helper Functions
  3. Adding to the CSV
  4. Pull it Together

GetHostName() Function

This is a simple function that returns the hostname of the computer the script is running on.

' // * Gets the current systems hostname * //
Function GetHostname()
        Dim objNTInfo
 
        Set objNTInfo = CreateObject("WinNTSystemInfo")
        GetHostName = Ucase(objNTInfo.ComputerName)
 
        Set objNTInfo = NOTHING
 
End Function ' GetHostName

The WinNTSystemInfo object stores various things about the computer, such as the domain, username, and obviously the computer name. After we pull this information we convert it to uppercase and then return it.

FileLen(strFilename) Function

I hate having to create two objects every time I need to perform a file operation. It’s just messy to me, so any time I need to do anything like read in a file, write to a file, or in this case, check the size of a file, I create a simple function.

' // * Returns the size of a file * //
Function FileLen(strFilename)
        Dim objFSO, objFile
 
        Set objFSO = CreateObject("Scripting.FileSystemObject")
 
        ' If the file exists return the file size, otherwise return 0
        If objFSO.FileExists(strFileName) Then
                Set objFile = objFSO.GetFile(strFileName)
                FileLen = objFile.Size
        Else
                FileLen = 0
        End If
 
        Set objFile = Nothing
        Set objFSO = Nothing
 
End Function ' FileLen

If you were to call objFSO.GetFile() on something that didn’t exist, your program errors out. So the first thing I did was verify the existence of our file with the objFSO.FileExists() function. If this is true then we create the new object and call the objFile.Size. Otherwise a size of 0 is returned.

Dateyyyymmdd(strDate) Function

If the comments didn’t give it a way, this takes a date from mm/dd/yyyy format and puts it into yyyymmdd format. Whenever you call the built in Date() function it’s return as mm/dd/yyyy. But since we named our files the other way so they’ll sort easier, we’ll need to convert them.

' // * Changes the date from mm/dd/yyyy to yyyymmdd * //
Function Dateyyyymmdd(strDate)
        Dim strDatePieces
 
        strDatePieces = Split(strDate, "/")
        If strDatePieces(0) < 10 Then
                strDatePieces(0) = "0" & strDatePieces(0)
        End If
 
        If strDatePieces(1) < 10 Then
                strDatePieces(1) = "0" & strDatePieces(1)
        End If
 
        Dateyyyymmdd = strDatePieces(2) & _
                strDatePieces(0) & _
                strDatePieces(1)
 
End Function ' Dateyyyymmdd

We start by splitting up the date anywhere there is a ‘/’, and storing those values into an array called strDatePieces.

We’ll also need the trailing 0s in front of single digit months and days, so next we look at strDatePieces(0), which is the month. If it’s less than 10, then it’ll be single digit and we’ll add that 0 in front. This is repeated form strDatePieces(1), which is the day of the month.

Finally, the reordered string is returned in the new format.

Stupid IIS 5 Logs

Sadly, some of our servers are still IIS 5.0 (meaning they’re running Windows 2000). The problem lies in the fact that IIS 5.0 doesn’t generate an HTTPERR log. So I could have either created two scripts (one for IIS 5.0 and one for IIS 6.0), or add an extra step to get rid of the results of Log Parser when it’s unable to run successfully. Since I didn’t want to mess with maintaining two different scripts, I picked the latter.

Whenever Log Parser fails to query a specific log, a “Task aborted.” message is returned. The KillTaskAborted(strFileName) function deletes any files that contain that string.

' // * Deletes files containing "Task Aborted" * //
Sub KillTaskAborted(strFileName)
        Dim objFSO, objFile, strText
 
        Set objFSO = CreateObject("Scripting.FileSystemObject")
 
        If objFSO.FileExists(strFileName) Then
                Set objFile = objFSO.OpenTextFile(strFileName, 1)
 
                strText = objFile.ReadAll
                objFile.Close
 
                If InStr(strText, "Task aborted.") Then
                        ObjFSO.DeleteFile strFileName
                End If
 
        End If
 
        Set objFSO = NOTHING
        Set objFile = NOTHING
 
End Sub ' KillTaskAborted

The function is pretty simple. First check if the file even exists, objFSO.FileExists(). If it does, then open it up, objFSO.OpenTextFile(), and then read in the contents with objFile.ReadAll.

         If InStr(strText, "Task aborted.") Then
                        ObjFSO.DeleteFile strFileName
                End If

Right here it checks if “Task aborted.” is anywhere within the file. If so, then objFSO. DeleteFile is used to get rid of it. Having our database filled with aborted messages, would have been meaningless and most likely a pain to sift through.

<< Previous

Next >>