Solved

Add logging to my VBS script

Posted on 2007-11-28
11
631 Views
Last Modified: 2008-02-02
Hello everyone,

I have a VBS script that purges some tables. Long story short, while it is running through the multiple stages, first it gets some data from the database, then an archiving and so on. between the stages I get pop up windows which display which stage it has reached. My question is, I would like to automate this script, I tried to schedule it, it never stopped so I wonder if it can't run by the scheduler because of the pop up windows, is that right? if so how do I fix that?

Secondly, I want to log those pop up messages to a text file and have some way to write to the file whether or not it ended successfully. I tried writing some code but I'm too new at this, someone told me to use FSO but I have no idea what that is or how to use it. Please help and thank you!


'--------------------------------------------------------------------------
' ArchiveMessages.vbs
' This sample script is meant to run on the 
' BizTalk Message Box database (BizTalkMsgBoxDB).  
'
' This script allows the Sent and Received 
' Message Bodies to be saved off for Archive
' and later restored for future Analysis.
' 
' This script connects assuming the logged on
' user has permissions to the SQL Server DB.
'
' To point to a different database, please change
' g_localServerName to the database server name and
' dbName to the database name.
'
' The script requires the Purge_DTADB.sql script to
' be run beforehand against the message box database
' and Tracking_Fragments.fmt, Tracking_Parts.fmt and 
' Tracking_Spool.fmt files to be in the same directory.
'
'--------------------------------------------------------------------------
' This file is part of the Microsoft BizTalk Server 2004 SDK
'
' Copyright (c) Microsoft Corporation. All rights reserved.
'
' This source code is intended only as a supplement to Microsoft BizTalk
' Server 2004 release and/or on-line documentation. See these other
' materials for detailed information regarding Microsoft code samples.
'
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
' KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
' PURPOSE.
'--------------------------------------------------------------------------
 
Set objWshShell = CreateObject("WScript.Shell")
set env = objWshShell.Environment("Process")
g_localServerName = "uatserver"
dbName = "BizTalkMsgBoxDB"
 
' ====================================================
' Figure out which Tracking Spool is currently inactive 
' (Not being actively inserted against..)
' ====================================================
wscript.echo "Attempting to connect to server """ & g_localServerName & """, database """ & dbName & """..."
dim rs : set rs = CreateObject("ADODB.Recordset")
dim cnString : cnString = "Driver={SQL Server}; Trusted_Connection=yes; Server=" & g_localServerName & "; Initial Catalog=" & dbName
dim conn : set conn = GetConnection(cnString)
dim query : query = "SELECT TOP 1 nvcTableQualifier FROM TrackingSpoolInfo WHERE nActive=0 ORDER BY dtDateModified ASC"
rs.Open query, conn
wScript.Echo "The inactive spool table is " & "Tracking_Spool" & rs.Fields(0).Value
 
dim cmd : set cmd = CreateObject("ADODB.Command")
 
BCPFileNamePrefix=""
Set objArgs= WScript.Arguments
if objArgs.Count >= 1 then
   WScript.Echo "Overriding BCP File Name Prefix : " & objArgs(0)
   BCPFileNamePrefix=ObjArgs(0)
End if
 
BCPTableID = FALSE
if objArgs.Count >= 2 then
   WScript.Echo "Turning BCP File Name Table ID " 
   BCPTableID = TRUE
End if
 
 
' ====================================================
' Dump Inactive tables
' ====================================================
DumpTable "BizTalkMsgBoxDB.dbo", "Tracking_Spool", rs.Fields(0).Value
DumpTable "BizTalkMsgBoxDB.dbo", "Tracking_Parts", rs.Fields(0).Value
DumpTable "BizTalkMsgBoxDB.dbo", "Tracking_Fragments", rs.Fields(0).Value
 
' ====================================================
'  Perform a purge
' ====================================================
wscript.echo "Purging tracking spool.."
set cmd.ActiveConnection = conn
cmd.CommandText = "exec bts_PurgeTrackingSpool"
cmd.Execute
 
 
set cmd = Nothing
set rs = nothing
Set conn = Nothing
 
 
' ===============================================================
'  DumpTable(DbPrefix, TableName, Qualifier)
'
'  This subroutine dumps the contents of a table into
'  a file by using the SQL utility tool BCP.exe.
'
'  It is designed to work specifically on the Tracking_Spool*,
'  Tracking_Parts* and Tracking_Fragments* tables. It accepts
'    1) Prefix for the table: Database Name + Owner
'    2) Table Name
'    3) Qualifier: The id for the inactive table set. 1 or 2
' ===============================================================
Sub DumpTable(DbPrefix, TableName, Qualifier)
  if BCPFileNamePrefix="" Then
    CurrentDateTime = now ' Snapshot time
    CurrentYear = Year(CurrentDateTime)
    CurrentMonth = Month(CurrentDateTime)
    CurrentDay = Day(CurrentDateTime)
    CurrentHour = Hour(CurrentDateTime)
    CurrentMinute = Minute(CurrentDateTime)
    CurrentSecond = Second(CurrentDateTime)
    ' BCP Data File name is decorated with Computer name & Time.
    BCPFileNamePrefix = g_localServerName &  CurrentYear & _
              CurrentMonth & CurrentDay & CurrentHour & CurrentMinute & _
              CurrentSecond 
  End if
 
  if BCPTableID=FALSE then
    BcpFileName= BCPFileNamePrefix & "_" & TableName & ".dat"
  else
     BcpFileName= BCPFileNamePrefix & "_" & TableName & Qualifier & ".dat"
  end if
 
  FullyQualifiedTable = DbPrefix & "." & TableName & Qualifier
  wscript.echo "Dump " & FullyQualifiedTable & " to file " & BcpFileName & ".."
  Set objExec = objwshShell.Exec("bcp.exe " & FullyQualifiedTable & " out " & BcpFileName & " -T -f " & TableName & ".fmt")
  Do While objExec.Status = 0
     WScript.Sleep 100
  Loop
  if objExec.ExitCode <> 0 then
    errMsg = trim(objExec.StdOut.ReadAll)
    wscript.echo "An error occurred while archiving messages. The error returned from bcp.exe was:" & chr(10) & errMsg 
    Wscript.Quit ( objExec.ExitCode )  
  end if
End Sub
 
' ===============================================================
'  GetConnection(cnString)
'  This function accepts a connection string and opens
'  a connection by using it. It returns the connection
'  back
' ===============================================================
Function GetConnection(cnString)
    dim conn : set conn = CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 30
    conn.Provider = "MSDASQL"
    conn.Open cnString
    Set GetConnection = conn
End Function

Open in new window

0
Comment
Question by:Samooramad
11 Comments
 
LVL 14

Expert Comment

by:canali
ID: 20368512
You need to schedule a command like this:
cmd /c C:\WINDOWS\system32\cscript.exe "C:\script\ArchiveMessages.vbs" >c:\temp\log.txt
cmd /c C:\WINDOWS\system32\cscript.exe "C:\script\ArchiveMessages.vbs" >c:\temp\log.txt

Open in new window

0
 

Author Comment

by:Samooramad
ID: 20369026
Sorry for the stupid question but how do I schedule it. I tried and I can only schedule applications not commands?

Also how will I know if it succeeded or failed?

Thanks
0
 
LVL 31

Expert Comment

by:merowinger
ID: 20372042
first define the application...close the task...edit it and then write down the parameters!!
see examble here: http://www.codinghorror.com/blog/images/defrag-task-screenshot.png

When this doesn't work, then take the whole command in a batch file and start this with the task
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:deadite
ID: 20372477
Open notepad, then enter the following Text:

@echo off
C:\WINDOWS\system32\cscript.exe "C:\script\ArchiveMessages.vbs" >c:\temp\log.txt


Now save that file as launchscript.bat

Go to Control Panel, schedule tasks.  Now when it asks what program to launch, select your launchscript.bat file.  This should do the trick
0
 

Author Comment

by:Samooramad
ID: 20376157
ok I will be back in office on Saturday, I will try it and post back

Thanks to all!
0
 

Author Comment

by:Samooramad
ID: 20388088
canali and deadite

The notepad file didn't work, I tried to schedule and run while logged off and on, I saw a black screen flash but nothing happened (the output files that are supposed to be created are not)
0
 
LVL 14

Expert Comment

by:canali
ID: 20401565
Go to Control Panel, schedule tasks, and schedule a task (what u want ex notepad) then right click on the new task created, choose properties, and change the "run" with the line:
 cmd.exe /c C:\WINDOWS\system32\cscript.exe "C:\script\ArchiveMessages.vbs" >c:\temp\log.txt
exec the task and look in c:\temp for the log file

for debug try the line above or the batch in a command prompt an look if everything is correct...
Gas
0
 

Author Comment

by:Samooramad
ID: 20606646
Sorry I forgot to get back on this issue with the holidays, it never did work. I tried the scheduler
0
 
LVL 15

Accepted Solution

by:
sr75 earned 250 total points
ID: 20792490
Here is a script I have used to ping servers every minute (scheduled task).  It has a logging feature that you should be able to adapt to your needs.
'########################################################################################
'#											#
'#	Name:		PingServers.vbs							#
'#	Version:	1.1.0								#
'#	Created:	August 3rd, 2007						#
'#	Modified:	August 20th, 2007						#
'#											#
'#	Description:	This script will test connectivity to an array of servers and 	#
'#			send a notification when a server is unreachable.		#
'#											#
'#	Notes:		This script uses PING to test for connectivity.  It will also 	#
'#			log any failures and create a "cookie" so that the email 	#
'#			notifications are only sent every 15 minutes or so.  When a  	#
'#			failure does occur, the script will wait 15 seconds before 	#
'#			making a second attempt.  If that fails, then a notification 	#
'#			will be sent.							#
'#											#
'#				Sub Function		Description			#
'#			---------------------------------------------------------------	#
'#			func_ReadFile()		This function will read a text file and	#
'#						fill an array with each line from the 	#
'#						file becoming an element of the array.	#
'#											#
'#			ping()			This function will Ping a computer and 	#
'#						output a TRUE for a successful ping, or #
'#						FALSE for after two failures.		#
'#											#
'#			func_Cookie()		This function will create a "cookie" 	#
'#						if the ping connectivity test fails. It #
'#						will also check the cookie so that a 	#
'#						notification is sent every 15 minutes.	#
'#											#
'#			func_Log()		This function will write the product	#
'#						information to a network share. It will #
'#						also rotate out any data that is older 	#
'#						than 90 days.				#
'#											#
'#			func_TimeStamp()	This function will generate a timestamp	#
'#						in this format:  YYYYMMDD HH:MM:SS AM	#
'#											#
'#			func_Notify()		This function will send an notification	#
'#						via email stating that the server has	#
'#						failed its test.			#
'#											#
'########################################################################################
'---------------------------------------------------------------------------
'
'				Main Function
'
'---------------------------------------------------------------------------
Option Explicit
 
DIM FSO
DIM SrvFile
DIM arrServers
DIM Server
DIM TimeStamp
 
set FSO = CreateObject("Scripting.FileSystemObject")
 
SrvFile = "Servers.txt"
 
arrServers = func_ReadFile(SrvFile)
 
For Each Server in arrServers
	If ping(Server) = FALSE then
		TimeStamp = func_TimeStamp()
		func_Log TimeStamp,Server
	End If
Next
 
set FSO = Nothing
set SrvFile = Nothing
set arrServers = Nothing
set Server = Nothing
set TimeStamp = Nothing
 
wscript.quit
 
 
'---------------------------------------------------------------------------
'
'				Sub Functions
'
'---------------------------------------------------------------------------
 
Function func_ReadFile(strFile)
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'
	'  This function will read a file and generate an array 
	'  with each element being a line from the file
	'
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
	DIM rFile
	DIM rLine
	DIM arrFile
 
	set rFile = FSO.OpenTextFile(strFile)
 
	Do until rFile.AtEndOfStream
		rLine = rFile.ReadLine
		if rLine <> "" then
			arrFile = arrFile & rLine & ";"
		End If
	Loop
 
	rfile.Close
 
	arrFile = ucase(arrFile)
	arrFile = Left(arrFile, len(arrFile) -1)
	arrFile = split(arrFile, ";")
 
	func_ReadFile = arrFile
 
	set rFile = Nothing
	set rLine = Nothing
	set arrFile = Nothing
 
End Function
 
Function ping(Server)
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'
	'  This function will Ping a computer and output a TRUE 
	'  for a successful ping, or FALSE for after two failures.
	'
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
	DIM WShell,objExec,strPingResults
 
 
	set WShell = CreateObject("WScript.Shell")
	set objExec = WShell.Exec("ping -n 2 -w 1000 " & Server)
	strPingResults = LCase(objExec.StdOut.ReadAll)
	If InStr(strPingResults, "reply from") then
		ping = TRUE
	else
		wscript.sleep 15000
	
		set objExec = WShell.Exec("ping -n 2 -w 1000 " & Server)
		strPingResults = LCase(objExec.StdOut.ReadAll)
		If InStr(strPingResults, "reply from") then
			ping = TRUE
		else
			
			ping = FALSE
		End If
	End If
	
	set WShell = Nothing
	set objExec = Nothing
	set strPingResults = Nothing
 
End Function
 
 
Function func_Cookie(Server, test)
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'
	'  This function will create a "cookie" should the ping 
	'  test fail for the server.  It will also check the 
	'  cookie so that a notification is sent every 15 minutes.
	' 
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
	DIM strCFold,strCook,blCookie
	DIM rFile,rLine,rDate,cFile,nTime
 
	strCFold = "\\ILI_IT01_SRV\Result$\Cookies\"
	strCook = strCFold & "Ping_" & Server & ".Cookie"
 
	If FSO.FileExists(strCook) then
		Set rFile = FSO.OpenTextFile(strCook)
	
		Do Until rFile.AtEndOfStream
			rLine = rFile.ReadLine
			If rLine <> "" then
				rDate = CDate(split(rLine, vbtab)(0))
				nTime = Now()
				If DateDiff("N", rDate, nTime) < 15 then
					blCookie = FALSE
				Else
					blCookie = TRUE
				End If
			End If
		Loop
	Else
		blCookie = TRUE
	End If
 
	If blCookie = TRUE then
		set cFile = FSO.OpenTextFile(strCook, 2, True)
		cFile.Write func_TimeStamp() & vbtab & Server
		cFile.close
		func_Cookie = TRUE
	Else
		func_Cookie = FALSE
	End If		
 
	set strCFold = Nothing
	set strCook = Nothing
	set blCookie = Nothing
	set rFile = Nothing
	set rLine = Nothing
	set rDate = Nothing
	set cFile = Nothing
	set nTime = Nothing
		
End Function
 
 
Function func_Log(TimeStamp,Server)
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'
	'  This function will Log the failed pings to a file on 
	'  the network.  It will also check to ensure the data is
	'  not duplicated in the log.
	'
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
	DIM strFold,strFile,blTest,wFile
	DIM rFile,rLine,rLog,rDate,nTime
 
	strFold = "\\ILI_IT01_SRV\Result$"
	strFile = strFold & "\Server_Ping.Log"
 
	If FSO.FileExists(strFile) then
		Set rFile = FSO.OpenTextFile(strFile)
 
		Do Until rFile.AtEndOfStream
			rLine = rFile.ReadLine
			If rLine <> "" then
				rDate = CDate(split(rLine, vbtab)(0))
				nTime = Now()
				If DateDiff("D", rDate, nTime) < 90 then
					rLog = rLog & rLine & vbcrlf
				End If
			End If
		Loop
		rFile.close
	End If 
 
	blTest = func_Cookie(Server,rLog)
 
	set wFile = FSO.OpenTextFile(strFile, 2, True)
	wFile.write TimeStamp & vbtab & Server & vbtab & "PING failure" & vbcrlf
 
	If blTest = TRUE then
		wFile.Write TimeStamp & vbTab & Server & vbTab & "**** Email Sent ****" & vbcrlf 
		func_Notify(Server)
	End If
 
	wFile.Write rLog
	wFile.Close 
 
	set strFold = Nothing
	set strFile = Nothing
	set blTest = Nothing
	set wFile = Nothing
	set rFile = Nothing
	set rLine = Nothing
	set rLog = Nothing
	set rDate = Nothing
	set nTime = Nothing
 
End Function
 
 
Function func_TimeStamp()
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'
	'  This function will generate a timestamp in this format:
	'
	'	YYYYMMDD HH:MM:SS AM/PM
	'
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
	DIM strYear
	DIM strMonth
	DIM strDay
	DIM strTime
	DIM strStamp
 
	strYear = Year(Now)
	strMonth = Month(Now)
	strDay = Day(Now)
	strTime = Time()
	If len(strMonth) < 2 then
		strMonth = "0" & strMonth
	End If
	If len(strDay) < 2 then
		strDay = "0" & strDay
	End If
	
	strStamp = strYear & "-" & strMonth & "-" & strDay & " " & strTime
 
	if len(strStamp) <> 22 then
		Do Until len(strStamp) > 21
			strStamp = strStamp & " "
		Loop
	end If
		
	func_TimeStamp = strStamp
 
	set strYear = Nothing
	set strMonth = Nothing
	set strDay = Nothing
	set strTime = Nothing
	set strStamp = Nothing
 
End Function
 
 
Function func_Notify(Server)	
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'
	'  This function will send an email notification stating 
	'  that Server has failed it's ping test.
	'
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
	DIM strName,strRecip,strSubject,strMsg
	DIM strSrv,strSend,strServer,strPort
	DIM Msg
 
 
	strName = "PingFailure@Immunolabs.com"		'Sender's Email
	strSrv = "webmail.immunolabs.com"		'SMTP Server
	strSubject = Server & "-- FAILED PING!!!"	'Email Subject
	strRecip = "NetAdmins@immunolabs.com"		'Recipient's Email
 
	strMsg = "Failed to ping " & ucase(Server) & " at " & Now()
 
	strSend = "http://schemas.microsoft.com/cdo/configuration/sendusing"
	strServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
	strPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
	
	Set Msg = CreateObject("CDO.Message")
	Msg.Configuration.Fields.Item(strSend) = 2
	Msg.Configuration.Fields.Item(strServer) = strSrv
	Msg.Configuration.Fields.Item(strPort) = 25
	Msg.Configuration.Fields.Update
 
	Msg.Subject = strSubject
	Msg.From = strName
	Msg.To = strRecip
	Msg.TextBody = strMsg
	Msg.Send
 
	set strName = Nothing
	set strRecip = Nothing
	set strSubject = Nothing
	set strMsg = Nothing
	set strSrv = Nothing
	set strSend = Nothing
	set strServer = Nothing
	set strPort = Nothing
	set Msg = Nothing
 
End Function

Open in new window

0
 

Author Comment

by:Samooramad
ID: 20792581
Thank you
0
 
LVL 14

Assisted Solution

by:canali
canali earned 250 total points
ID: 20803975
I thin there is some problem in the default script engine, if u don't want the popup windows cscript must be used.
try your script in the command prompt: cscript //nologo C:\Temp\ArchiveMessages.vbs  if run correctly
u can use the next line to schedule it weekly on monday at 23:00 using the current user

SCHTASKS /Create   /SC WEEKLY  -d MON /ST 23:00:00 /TN ArchiveMessages /TR "cmd /c cscript.exe //nologo C:\Temp\ArchiveMessages.vbs >>c:\Temp\ArchiveMessages.LOG"

Bye Gas
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

AutoHotkey is an excellent, free, open source programming/scripting language for Windows. It started out as a keyboard/mouse macros product, but has expanded into a robust language. This article provides an introduction to it, with links to addition…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question