Solved

Add logging to my VBS script

Posted on 2007-11-28
11
622 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:deadite
Comment Utility
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
Comment Utility
ok I will be back in office on Saturday, I will try it and post back

Thanks to all!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Samooramad
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you
0
 
LVL 14

Assisted Solution

by:canali
canali earned 250 total points
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now