Category Archives: vbscript

Virtualbox headless startup script in vbScript

I wanted a VM to start up every time I booted up my machine instead of waiting for me to start the machine manually. To do this I created a vbscript and added it to my Scheduled Tasks to run on startup:


strMyCommand = """C:\Program Files\Oracle\VirtualBox\VBoxHeadless.exe"" -startvm myservervm"
exec strMyCommand, 0, 0

function exec (tmpprogramme,intWindowStyle, boolWaitforFinish )
Set WshShell = WScript.CreateObject("WScript.Shell")
WSHShell.Run tmpprogramme, intWindowStyle, boolWaitforFinish
end function

Copy and paste the text into a vbs file and save it.

The command launches the command vboxheadless -startvm myservervm to boot the VM without showing it on the desktop taskbar. To stop the server load the Virtualbox Manager and shutdown/suspend/save it. Then you can start it using the Manager software if you wish as normal.

Tagged ,

Tail in vbScript

I was a little bored last week so I wrote a tail programme in vbScript. It probably isn’t the best language to write it in as it has no support for pointers within a file or the ability to read a file backwards. Unfortunately you have to read the file forwards from the beginning each time it updates which obviously doesn’t scale well for larger files. That said it seemed to work OK for files up to a few million lines off local disk.

The script uses WMI to monitor changes to the file under a folder object. When the file has been modified the script then checks for new lines and shows them to the screen. In this way it emulates tail with follow (tail -f).

'##############################################################################
'# Name: Tail.vbs
'# Description: Tail a file with follow
'# Version History:
'#
'# 1.0 Stuart Jordan Script creation
'#
'##############################################################################

'Grab the path of the file to be tailed from the command line
Set args = WScript.Arguments
if WScript.Arguments.Count <> 1 then
 wscript.echo vbcrlf
 wscript.echo "------------------------------------------------------------"
 wscript.echo " Tail.vbs syntax help "
 wscript.echo "------------------------------------------------------------"
 wscript.echo "You haven't correctly specified the filename to be tailed."
 wscript.echo vbcrlf
 wscript.echo "Syntax:"
 wscript.echo vbcrlf
 wscript.echo vbtab & "cscript /nologo tail.vbs c:\myfolder\myfilename.txt"
 wscript.echo vbcrlf
 wscript.echo "If there are spaces in your folder or filename please"
 wscript.echo "enclose the filename in double quotes"
 wscript.echo "------------------------------------------------------------"
 wscript.quit
end if
strLongFileName = args.Item(0)

arrArgsLine=split(strLongFileName,"\")
strDrive=arrArgsLine(0)
if ubound(arrArgsLine)>1 then
 for i=1 to ubound(arrArgsLine)-1
 strFolder=strFolder & "\\" & arrArgsLine(i)
 next
 strFolder=strFolder & "\\"
end if
strFileName=arrArgsLine(ubound(arrArgsLine))

if strDrive = "" OR strFilename = "" then
 wscript.echo "WARNING: Either the drive or filename was not specified. Please try again."
 wscript.quit
end if

wscript.echo vbcrlf
wscript.echo "----------------------------------------------------------------"
wscript.echo "This is the information that was detected:"
wscript.echo "Full path = " & strLongFileName
wscript.echo "Drive letter = " & strDrive
wscript.echo "File name = " & strFileName
wscript.echo "Folder path = " & strFolder
wscript.echo vbcrlf
wscript.echo "----------------------------------------------------------------"
wscript.echo vbcrlf

'This is the interval with which the WMI events are checked
intInterval = "2"
strComputer = "." 

intLastRunLineinFile=get_NumLinesInFile(strLongFileName)

Set objWMIService = GetObject( "winmgmts:" & _ 
 "{impersonationLevel=impersonate}!\\" & _ 
 strComputer & "\root\cimv2" )
if len(strFolder) then
 strQuery = _
 "Select * From __InstanceOperationEvent" _
 & " Within " & intInterval _
 & " Where Targetinstance Isa 'CIM_DataFile'" _
 & " And TargetInstance.Drive='" & strDrive & "'" _
 & " And TargetInstance.Path='" & strFolder & "'"
else
 strQuery = _
 "Select * From __InstanceOperationEvent" _
 & " Within " & intInterval _
 & " Where Targetinstance Isa 'CIM_DataFile'" _
 & " And TargetInstance.Drive='" & strDrive & "\\'"
end if

Set colEvents = objWMIService. ExecNotificationQuery (strQuery) 
WScript.Echo "Monitoring events...[Ctl-C] to end"
Do 
 Set objEvent = colEvents.NextEvent()
 Set objTargetInst = objEvent.TargetInstance
 Select Case objEvent.Path_.Class 
 Case "__InstanceDeletionEvent" 
 if 0=strComp(objTargetInst.Name,strLongFileName,1) then
 wscript.echo objTargetInst.Name & " has been deleted. Exiting."
 wscript.quit
 end if
 Case "__InstanceModificationEvent" 
 if 0=strComp(objTargetInst.Name,strLongFileName,1) then
 tailFile(objTargetInst.Name)
 end if
 End Select 
Loop

function tailFile(strFileName)
 Const FOR_READING = 1
 dim intCurrLine,intNewNumLines
 Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
 Set objFile = objFSO.OpenTextFile(strFileName, FOR_READING)

 intNewNumLines=0
 intCurrLine=0
 Do Until objFile.AtEndOfStream
 intCurrLine=intCurrLine+1
 if intCurrLine <= intLastRunLineinFile then
 objFile.skipline
 else
 wscript.echo objFile.ReadLine
 intNewNumLines=intNewNumLines+1
 end if
 Loop

 if intCurrLine < intLastRunLineinFile then 
 'The number of lines on this scan was less than the last run so resetting the num lines in file
 intLastRunLineinFile = intCurrLine
 else
 intLastRunLineinFile=intLastRunLineinFile+intNewNumLines
 end if
 'wscript.echo "(" & intLastRunLineinFile & ")"
 objFile.close
end function

function get_NumLinesInFile(strFileName)
 Const FOR_READING = 1
 Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
 Set objFile = objFSO.OpenTextFile(strFileName, FOR_READING)
 intTotalNumLines=0
 Do Until objFile.AtEndOfStream
 objFile.skipline
 intTotalNumLines=intTotalNumLines+1
 Loop
 objFile.close
 get_NumLinesInFile=intTotalNumLines
end function

Download the script here

Tagged

Command line mail VBscript (with attachments)

Batch scripting on windows provides no way of sending emails. There are paid-for solutions like febootimail and even free solutions like SendEmail. In the main you should probably try using the latter as I haven’t maintained this VBscript for a long time. That in itself is probably a good thing as I’ve used it alot and never needed to update it!

Anyway it may help someone who is adding the functionality into their scripts.

The script has the syntax:

cscript mail2.vbs [/A:attachmentpath] [/S:sender] [/R:recipient] [/C:Message Content] [/J:subject][/M:mailserver]

for example

cscript mail2.vbs /A:myfile.zip /S:me@mydomain.com /R:you@yourdomain.com /C:”Hi, Here’s that zip file you wanted. Thanks Stu” /J:”zip file” /M:smtp.mydomain.com

Here’s the script:

'********************************************************************
'*
'* File:           MAIL2.VBS
'* Created:        April 2004
'* Last Revision   May 2013
'* Version:        1.1
'*
'* Main Function: Emails an attachment to recipient from sender
'* Usage: MAIL.VBS [/A:attachmentpath] [/S:sender] [/R:recipient] 
'*        [/C:Message Content] [/J:subject] [/M:mailserver
'*
'*
'********************************************************************

'OPTION EXPLICIT
'ON ERROR RESUME NEXT

'Define constants
CONST CONST_ERROR                   = 0
CONST CONST_WSCRIPT                 = 1
CONST CONST_CSCRIPT                 = 2
CONST CONST_SHOW_USAGE              = 3
CONST CONST_PROCEED                 = 4

'Declare variables
Dim strsubject, intOpMode, i
Dim strattachmentpath, strsender, strmessagecontent,  strrecipient, strmailserver
ReDim strArgumentArray(0)

'Initialize variables
strArgumentArray(0) = ""
strattachmentpath = ""
strsender = ""
strmessagecontent = ""
strrecipient = ""
strsubject = ""
strmailserver = ""

'Get the command line arguments
For i = 0 to Wscript.arguments.count - 1
    ReDim Preserve strArgumentArray(i)
    strArgumentArray(i) = Wscript.arguments.Item(i)
Next

'Check whether the script is run using CScript
Select Case intChkProgram()
    Case CONST_CSCRIPT
        'Do Nothing
    Case CONST_WSCRIPT
        WScript.Echo "Please run this script using CScript." & vbCRLF & _
        WScript.Quit
    Case Else
        WScript.Quit
End Select

'Parse the command line
intOpMode = intParseCmdLine(strArgumentArray, strattachmentpath, strsender, strmessagecontent, strrecipient, _
            strsubject,strmailserver)
If Err.Number then
    Print "Error 0x" & CStr(Hex(Err.Number)) & " occurred in parsing the command line."
    If Err.Description <> "" Then
        Print "Error description: " & Err.Description & "."
    End If
    call showusage()
    WScript.Quit
End If

Select Case intOpMode
    Case CONST_SHOW_USAGE
        Call ShowUsage()
    Case CONST_PROCEED
        Call mail(strattachmentpath, strsender, strmessagecontent, _
             strrecipient,strsubject,strmailserver)
    Case CONST_ERROR
        'Do nothing.
    Case Else                    'Default -- should never happen
        Print "Error occurred in passing parameters."
End Select

'********************************************************************
'*
'* Function intChkProgram()
'* Purpose: Determines which program is used to run this script.
'* Input:   None
'* Output:  intChkProgram is set to one of CONST_ERROR, CONST_WSCRIPT,
'*          and CONST_CSCRIPT.
'*
'********************************************************************

Private Function intChkProgram()

    ON ERROR RESUME NEXT

    Dim strFullName, strCommand, i, j

    'strFullName should be something like C:\WINDOWS\COMMAND\CSCRIPT.EXE
    strFullName = WScript.FullName
    If Err.Number then
        Print "Error 0x" & CStr(Hex(Err.Number)) & " occurred."
        If Err.Description <> "" Then
            If Err.Description <> "" Then
                Print "Error description: " & Err.Description & "."
            End If
        End If
        intChkProgram =  CONST_ERROR
        Exit Function
    End If

    i = InStr(1, strFullName, ".exe", 1)
    If i = 0 Then
        intChkProgram =  CONST_ERROR
        Exit Function
    Else
        j = InStrRev(strFullName, "\", i, 1)
        If j = 0 Then
            intChkProgram =  CONST_ERROR
            Exit Function
        Else
            strCommand = Mid(strFullName, j+1, i-j-1)
            Select Case LCase(strCommand)
                Case "cscript"
                    intChkProgram = CONST_CSCRIPT
                Case "wscript"
                    intChkProgram = CONST_WSCRIPT
                Case Else       'should never happen
                    Print "An unexpected program is used to run this script."
                    Print "Only CScript.Exe or WScript.Exe can be used to run this script."
                    intChkProgram = CONST_ERROR
            End Select
        End If
    End If

End Function

'********************************************************************
'*
'* Function intParseCmdLine()
'* Purpose: Parses the command line.
'* Input:   strArgumentArray    an array containing input from the command line
'* Output:  strattachmentpath              Attachment path
'*          strsender                 Senders email
'*          strmessagecontent           Message Content
'*          strrecipient                Recipient Email
'*          strsubject               Subject
'*          intParseCmdLine         is set to one of CONST_ERROR, CONST_SHOW_USAGE, CONST_PROCEED.
'*
'********************************************************************

Private Function intParseCmdLine(strArgumentArray, strattachmentpath, strsender, _
        strmessagecontent, strrecipient, strsubject,strmailserver)

    'ON ERROR RESUME NEXT

    Dim strFlag, i

    strFlag = strArgumentArray(0)

    If strFlag = "" then                'No arguments have been received
        Call ShowUsage()
        intParseCmdLine = CONST_ERROR
        Exit Function
    End If

    For i = 0 to UBound(strArgumentArray)
        strFlag = LCase(Left(strArgumentArray(i), InStr(1, strArgumentArray(i), ":")-1))
        If Err.Number Then            'An error occurs if there is no : in the string
            Err.Clear
        Else
            Select Case strFlag
                Case "/a"
                    strattachmentpath= Right(strArgumentArray(i), Len(strArgumentArray(i))-3)
                Case "/s"
                    strsender = Right(strArgumentArray(i), Len(strArgumentArray(i))-3)
                Case "/c"
                    strmessagecontent = Right(strArgumentArray(i), Len(strArgumentArray(i))-3)
                Case "/r"
                    strrecipient = Right(strArgumentArray(i), Len(strArgumentArray(i))-3)
                Case "/j"
                    strsubject = Right(strArgumentArray(i), Len(strArgumentArray(i))-3)
        case "/m"
                    strmailserver = Right(strArgumentArray(i), Len(strArgumentArray(i))-3)
                Case else
                    Print "Invalid flag " & """" & strFlag & """" & "."
                    Print "Please check the input and try again."
                    intParseCmdLine = CONST_ERROR
                    call showusage()
                    exit function
                End Select
        End If
    Next

    intParseCmdLine = CONST_PROCEED

End Function

'********************************************************************
'*
'* Sub ShowUsage()
'* Purpose: Shows the correct usage to the user.
'* Input:   None
'* Output:  Help messages are displayed on screen.
'*
'********************************************************************

Private Sub ShowUsage()
    wscript.echo vbCrLf
    wscript.echo "USAGE:"
    wscript.echo "------"
    wscript.echo "cscript mail.vbs [/A:Attachment Path] [/S:Sender's Email] [/R:Recipient's Email] [/C:Message Content] [/J:Subject Line] [/M:Mail Server"
End Sub

'********************************************************************
'*
'* Sub mail()
'* Purpose: Emails based on inputs
'* Input:   strattachmentpath      attachmentpath of the root of the search
'*          strsender     the search sender
'*          strmessagecontent   messagecontent to be retrieved
'*          strrecipient        the search recipient
'*          strUserName     name of the current user
'*          strPassword     password of the current user
'*          strsubject   an output file name
'* Output:  Results of the search are either printed on screen or saved in strsubject.
'*
'********************************************************************

Private Sub mail(strattachmentpath, strsender, strmessagecontent, _
    strrecipient, strsubject,strmailserver)

    dim objEmail

    'ON ERROR RESUME NEXT
    if ("" <> strsender AND "" <> strrecipient) then 
    Set objEmail = CreateObject("CDO.Message")

    objEmail.From = strsender
    objEmail.To = strrecipient
    objEmail.Subject = strsubject
    objEmail.Textbody = strmessagecontent
    if "" <> strattachmentpath then
          objEmail.AddAttachment strattachmentpath
        end if
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
            strmailserver
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    wscript.echo "Sending email to "&strsender & " from " & strrecipient & " via " & strmailserver
        objEmail.Send
    else
        wscript.echo "ERROR: Sender or Recipient not specified"
    end if
    
End Sub

'********************************************************************
'*
'* Sub Print()
'* Purpose: Prints a message on screen.
'* Input:   strMessage      the string to print
'*
'********************************************************************

Sub Print(ByRef strMessage)
    wscript.Echo  strMessage
End Sub

You can download the script from the link below:

Download

Last edited on 31st May 2013 – Fixed bug where the sender and recipient strings were assigned incorrectly in the mail sub.

Tagged , ,

Using time and dates in your windows batch scripts

Using date and time in your batch scripts can be quite difficult. The current date and time can be easy enough but if you want anything more complex like yesterday’s date, you’re out of luck.

Here’s a simple VBscript that can create a batch file to use with your script that will set several environment variables that you can then use.

For example:

REM My Test script 
cscript /nologo envtime.vbs > envtime.bat 
call envtime.bat 
echo Yesterday's date was %YDOM%/%YMON%/%YYEAR%

which prints out the date yesterday taking into account months and years etc.

It also supports the date 2 and 3 days ago using %TWODOM% and %THREEDOM% respectively. See the script for more syntax.

If later in your script you want an updated time and date, e.g. at the end of the script, just rerun the lines to generate and call the batch file using the lines in the example above.

'#############################################################################################
'# VERSION HISTORY                                                                           #
'#                                                                                           #
'# Date          Version    Author          Details                                          #
'#                                                                                           #
'# 2007/04/19    1.0        Stuart Jordan   Creation                                         #
'# 2007/04/24    1.1       Stuart Jordan  Changed date/time funcs to use day/Month/Year   #
'# 2010/09/20    2.0       Stuart Jordan  Made the month/day/year functions be able to    #
'#                                          return any number of days before/after today.    #
'#                                                                                           #
'#############################################################################################

' ######################################
' # Main body
' #
wscript.echo "@echo off"
wscript.echo
wscript.echo "REM Four digit year"
wscript.echo "set YEAR=" & get_year(0)
wscript.echo
wscript.echo "REM Four digit previous year"
wscript.echo "set PYEAR=" & get_pyear()
wscript.echo
wscript.echo "REM Two digit year"
wscript.echo "set YR=" & get_yr(0)
wscript.echo
wscript.echo "REM Two digit previous year"
wscript.echo "set PYR=" & get_pyr()
wscript.echo
wscript.echo "REM Two digit month"
wscript.echo "set MON=" & get_mon(0)
wscript.echo
wscript.echo "REM Two digit day of the month"
wscript.echo "set DOM=" & get_dom(0)
wscript.echo
wscript.echo "REM Two digit hour"
wscript.echo "set HOUR=" & get_hour()
wscript.echo
wscript.echo "REM Two digi minute"
wscript.echo "set MIN=" & get_min()
wscript.echo
wscript.echo "REM Yesterday's day of the month"
wscript.echo "set YDOM=" & get_dom(-1)
wscript.echo
wscript.echo "REM Yesterday's two digit month"
wscript.echo "set YMON=" & get_mon(-1)
wscript.echo
wscript.echo "REM Yesterday's four digit year"
wscript.echo "set yyear=" & get_year(-1)
wscript.echo
wscript.echo "REM Yesterday's two digit year"
wscript.echo "set yyr=" & get_yr(-1)
wscript.echo
wscript.echo "REM Text month"
wscript.echo "set TXTMON=" & get_txtmon(0)
wscript.echo
wscript.echo "REM Yesterday's Text month"
wscript.echo "set YTXTMON=" & get_txtmon(-1)
wscript.echo
wscript.echo "REM 2 days ago two digit day of the month"
wscript.echo "set TWODOM=" & get_dom(-2)
wscript.echo
wscript.echo "REM 2 days ago two digit month"
wscript.echo "set TWOMON=" & get_mon(-2)
wscript.echo
wscript.echo "REM 2 days ago four digit year"
wscript.echo "set TWOYR=" & get_year(-2)
wscript.echo
wscript.echo "REM 2 days ago two digit year"
wscript.echo "set TWOYR=" & get_yr(-2)
wscript.echo
wscript.echo "REM 3 days ago two digit day of the month"
wscript.echo "set THREEDOM=" & get_dom(-3)
wscript.echo
wscript.echo "REM 3 days ago two digit month"
wscript.echo "set THREEMON=" & get_mon(-3)
wscript.echo
wscript.echo "REM 3 days ago four digit year"
wscript.echo "set THREEYEAR=" & get_year(-3)
wscript.echo
wscript.echo "REM 3 days ago two digit year"
wscript.echo "set THREEYR=" & get_yr(-3)
wscript.echo
wscript.echo "REM Digit Day of the week"
wscript.echo "set DOW=" & get_dow()
wscript.echo
wscript.echo "REM Text Day of the week"
wscript.echo "set TXTDOW=" & get_txtdow()
wscript.echo
wscript.echo "REM Yesterday's Text Day of the week"
wscript.echo "set TXTDOW=" & get_ytxtdow()
wscript.echo
wscript.echo "REM done"

' ######################################
' # Functions
' #
' Four digit year

private function get_ydateserial(intSelection,numDays)
    strtmpDate=dateserial(datepart("yyyy",date()),datepart("m",date()),datepart("d",date())+numDays)
    'wscript.echo "dateserial=" & strtmpDate
    arrtmpDate=split(strtmpDate, "/")
    get_ydateserial=arrtmpDate(intSelection)
end function

' Two digit day of the month
function get_dom(numDays)
    get_dom=fixuptwodigit(get_ydateserial(0,numDays))
end function

' Two digit month
function get_mon(numDays)
    'get_mon=fixuptwodigit(Month(date()))
    get_mon=fixuptwodigit(get_ydateserial(1,numDays))
end function

function get_year(numDays)
    get_year=get_ydateserial(2,numDays)
end function

' Two digit year
function get_yr(numDays)
    get_yr=right(get_year(0), len(get_year(0)) - 2)
end function

' Four digit previous year
function get_pyear()
    get_pyear=get_year(0) -1
end function

' Two digit prevous year
function get_pyr()
    get_pyr=right(get_year(0)-1, len(get_year(0)) - 2)
end function

' Two digit hour
function get_hour()
    get_hour=fixuptwodigit(Hour(now()))
end function

' Two digit minute
function get_min()
    get_min=fixuptwodigit(Minute(now()))
end function

' Today's month in text
function get_txtmon(numDaus)
    get_txtmon=get_montotxt(get_mon(numDays))
end function

' Convert the two digit month to text
function get_montotxt(intMon)
    select case intMon
        case 1
            get_montotxt="Jan"
        case 2
            get_montotxt="Feb"
        case 3
            get_montotxt="Mar"
        case 4
            get_montotxt="Apr"
        case 5
            get_montotxt="May"
        case 6
            get_montotxt="Jun"
        case 7
            get_montotxt="Jul"
        case 8
            get_montotxt="Aug"
        case 9
            get_montotxt="Sep"
        case 10
            get_montotxt="Oct"
        case 11
            get_montotxt="Nov"
        case 12
            get_montotxt="Dec"
    end select
end function

function get_dow()
    get_dow=WeekDay(date)
end function

function get_txtdow()
    get_txtdow=WeekDayName(get_dow())
end function

function get_ytxtdow()
    get_ytxtdow=WeekDayName(get_dow()-1)
end function

' Change 1 digit numbers to two (for months and days)
function fixuptwodigit(strtmp)
    tmpEntry=""
    if len(strtmp)<2 then
        tmpEntry="0"
    end if
    fixuptwodigit=tmpEntry & strtmp
end function

Click the link below to download the script

Download

Tagged ,

Querying other SQL databases using active script in WhatsUp

This is a script that allows you to query an SQL server other than the WhatsUp DB. It doesn’t require a DSN ODBC entry and is self contained.

Let’s assume we want to run this query:

SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'

against a server named mySQLserver. To do that we can use the ADODB object in VBScript:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'We're not using a DSN so specify the connection string
strConnectionString="DRIVER={SQL Server};SERVER=" & strDatabaseServer & ";UID=" & strUsername &_
";PWD=" & strPassword & ";DATABASE=" & strDatabaseName
context.logmessage strConnectionString

objConnection.Open strConnectionString
objRecordset.CursorLocation = adUseClient

This bit of the script creates a Connection object and using the connection string opens a connection to the SQL server. We then use that connection to perform our query:

'This is the query to run
strQuery="SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'"
'Connect to the DB server and run the query
objRecordset.Open strQuery, objConnection, adOpenStatic,adLockOptimistic

The rows returned populate the record set object. We’re only expecting one row for this one so we can get the result by:

If objRecordset.EOF Then
    'No rows returned
    context.logmessage "Record cannot be found."
Else
    'rows returned
    context.logmessage objrecordset("cur_latency") 
    context.setvalue int(cur_latency)
End If

and then close the objects sets.

objRecordset.Close
objConnection.Close

Here’s the full script:

'The database server is kept in an attribute named "DatabaseServerName"
strDatabaseServer="mySQLserver"

'The database that stores the replication configuration is "distribution"
strDatabaseName="distribution"

'This is the query to run
strQuery="SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'"

'Get the logon credentials
strUsername = Context.GetProperty("CredADO:Username")
strPassword = Context.GetProperty("CredADO:Password")

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'We're not using a DSN so specify the connection string
strConnectionString="DRIVER={SQL Server};SERVER=" & strDatabaseServer & ";UID=" & strUsername &_
";PWD=" & strPassword & ";DATABASE=" & strDatabaseName
context.logmessage strConnectionString

objConnection.Open strConnectionString
objRecordset.CursorLocation = adUseClient

'Connect to the DB server and run the query
objRecordset.Open strQuery, objConnection, adOpenStatic,adLockOptimistic

If objRecordset.EOF Then
    'No rows returned
    context.logmessage "Record cannot be found."
Else
    'rows returned
    context.logmessage objrecordset("cur_latency") 
    context.setvalue int(cur_latency)
End If

objRecordset.Close
objConnection.Close

Download the source from the link:

Download

Tagged , , , , , ,

Working with attributes in WhatsUp

About attributes in WhatsUp

You can find the attributes for a device when you go to device properties and then attributes. By default they don’t hold much but they can used as a useful store of information for that device or for storing variables between active monitor/performance monitor polls.

The easiest way to manipulate the attributes is by directly updating the DeviceAttribute table in the WhatsUp database. The table has the following columns:

nDeviceAttributeID The primary key for the table and an incremental integer (1,2,3,4,5 etc)
nDeviceID This is the nDeviceID of the device to which the attribute belongs. It is also an integer
sName The name of the attribute. In WhatsUp this appears as the “Name” column. It is a string.
sValue The value assigned to the specific attribute.  This is also a string.

Attributes can be added, removed and modified by inserting, deleting and updating rows in the table. For example:

UPDATE [WhatsUp].[dbo].[DeviceAttribute] SET sValue ='Windows Server 2003' WHERE (nDeviceID = '100') AND (sName = 'OS')

In this case the attribute named OS is set with the value “Windows Server 2003”.

Similarly to create (INSERT) and remove (DELETE):

INSERT INTO [WhatsUp].[dbo].[DeviceAttribute] (nDeviceID,sName,sValue) VALUES ('100','OS','Windows 2003')
DELETE FROM [WhatsUp].[dbo].[DeviceAttribute] WHERE (nDeviceID='100' AND sName='OS')
Using this in scripts

WhatsUp allows us to use the built-in database object to access the database. To use this we put this at the top of our scripts:

‘ Retrieve DB object for connecting to WhatsUp
Set oDb = Context.GetDB

and then we can use the oDb object when executing SQL code against the database, like this:

INSERT INTO [WhatsUp].[dbo].[DeviceAttribute] (nDeviceID,sName,sValue) VALUES ('100','OS','Windows 2003')
oDb.Execute(sqlQuery)  

If we want to get the output, say do a query and retrieve the result we can do it this way:

sqlCheck = "SELECT nDeviceAttributeID FROM DeviceAttribute WHERE (nDeviceID = '100') AND (sName = 'OS')"
Set oRecordset = oDb.Execute(sqlCheck) context.logmessage oRecordset("nDeviceAttributeID")

which will print the result of the query to the screen. If an array is returned then this can be handle similarly by specifying the oRecordset(“nDeviceID”), oRecordSet(“sValue”) etc.

Of course when you’re writing your scripts you won’t want to do this every time so it’s easier to put these into get/set functions:

function isAttribExist(tmpDeviceID,tmpAttributeName)
    boolReturn=0
    sqlCheck = "SELECT nDeviceAttributeID FROM DeviceAttribute WHERE (nDeviceID = " & tmpDeviceID & ") AND (sName = N'" & tmpAttributeName & "')"
    Set oRs1 = oDb.Execute(sqlCheck)
    if oRS1.EOF then
        boolReturn=0
    else
        boolReturn=1
    end if
    isAttribExist = boolReturn
end function

function updateAttribute(tmpDeviceID,tmpAttributeName,tmpValue)
    sqlQuery="UPDATE [WhatsUp].[dbo].[DeviceAttribute] SET sValue = '" & tmpValue & "' WHERE (nDeviceID = " & tmpDeviceID & ") AND (sName = N'" & tmpAttributeName & "')"
    context.logmessage sqlQuery
    oDb.Execute(sqlQuery)    
end function

function addNewAttribute (tmpDeviceID,tmpAttributeName,tmpValue)
    sqlQuery="INSERT INTO [WhatsUp].[dbo].[DeviceAttribute] (nDeviceID,sName,sValue) " & _
         "VALUES ('" & tmpDeviceID & "','" & tmpAttributeName & "','" & tmpValue & ")"
    context.logmessage sqlQuery
    oDb.Execute(sqlQuery)    
end function

function setAttribute(tmpDeviceID,tmpAttributeName,tmpValue)
    if isAttribExist(tmpDeviceID,tmpAttributeName) then
        updateAttribute tmpDeviceID,tmpAttributeName,tmpValue
    else
        addNewAttribute  tmpDeviceID,tmpAttributeName,tmpValue
    end if
end function

function getAttribute(tmpDeviceID,tmpAttributeName)
    strReturn=""
    sqlCheck = "SELECT sValue FROM DeviceAttribute WHERE (nDeviceID = " & tmpDeviceID & ") AND (sName = N'" & tmpAttributeName & "')"
    Set oRs1 = oDb.Execute(sqlCheck)
    if oRS1.EOF then
        strReturn=""
    else
        strReturn=oRs1("sValue")
    end if
    getAttribute = strReturn
end function

Get usage:

strResult=getAttribute(DeviceID,attribute name)

e.g.

strResult=getAttribute(100,”OS”)

Set usage:

setAttribute(DeviceID,attribute name,value to set)

e.g.

setAttribute(100,”OS”,”Windows Server 2003″)

Tagged , , , , , ,

Enable compression on a folder tree

Windows compression can save alot of space but can take a very long time if there’s alot of folders and files. In the past I’ve found that when there is alot of data the compression can stop part of the way through. To get around it I’ve written a script to essentially do the same thing.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objFSO = CreateObject("Scripting.FileSystemObject")

ShowSubFolders (objFSO.GetFolder("C:\Path\to\folder"))

on error  resume next

Sub ShowSubFolders(Folder)
    on error resume next
    strFunctionName="ShowSubFolders"

    For Each Subfolder in Folder.SubFolders
        compress_folder(Subfolder.Path)
        ShowSubFolders Subfolder
    Next
End Sub

function compress_folder(strPath)
    on error resume next
    strPath=replace(strPath,"\","\\")
    Set colFolders = objWMIService.ExecQuery _
        ("Select * from Win32_Directory where name = '" & strPath & "'")
    For Each objFolder in colFolders
        wscript.echo strPath
        objFolder.compress
    Next
end function

The script parses each folder under the folder you specify (replace the c:\Path\to\folder with your own path). ShowSubFolders is called again and again until the tree has been parsed.

The line that does the compression on a folder the files directly within is

objFolder.compress

To use open an administrator command prompt and type

cscript /nologo compress_tree.vbs

Download Script – (Right click -> Save As -> Rename from doc to vbs)

Tagged , , ,

An alternative logon script

Logon scripts can over time get complex and messy. Here’s an example of an alternative script that is a “one for all” script that can be used across a whole enterprise. The script uses group membership and the location of the user account in AD to work out which drive mappings and welcome window to give to the user.

Let’s assume there are two sites in the company to keep it simple, one in London and another in New York.

The OU structure is as follows:

John wants the following drives mapped:

P: drive mapped to \\nyserver1\Public
M: drive mapped to \\nyserver2\IT

Sarah wants the following drives mapped:

P: drive mapped to \\nyserver1\Public
M: drive mapped to \\lonserver2\IT

We create three groups:

A group named DriveMapping-nyserver1-Public and add both Sarah and John
The group description is set as: P,\\nyserver1.acme.corp\public
A group named DriveMapping-nyserver2-IT and add John
The group description is set as: M,\\nyserver2.acme.corp\IT
A group named DriveMapping-lonserver2-IT and add Sarah
The group description is set as: M,\\lonserver2.acme.corp\IT

On \\acme.corp\netlogon\images we save an image for each of the sites, London and New York: London.jpg and New York.jpg. These are used in the logon welcome window. There’s also one named default.jpg in case the script can’t work out which site they’re from.

Now this is how the script works:

  • When the user logs in the script runs
  • The script works out who that user is (their logon name)
  • The script queries active directory and looks at their user account
  • It looks at the location of the username in active directory and works out what site they belong to, i.e. Sarah is in London
  • It uses this information to select the correct welcome screen image
  • The welcome screen is then shown with this site-specific image
  • It then proceeds to start mapping drives. From the user account it gets the list of groups
  • If the group name they’re a member of matches the name “DriveMappings-*” then it looks at the description of the group and uses that information to map the drive
  • It does this for each group that the user is a member of
  • Once completed it waits 20 seconds and then closes automatically

The benefits of doing it this way?

  1. You don’t need to edit the logon script to map a new drive for a user. Either add them to an existing group or create a new one with the correct name (DriveMapping-lonserver1-IT for instance) and the description field filled out.
  2. It’s easy to add and remove users to groups to make sure they get the drives mapped they want
  3. You can see, just by looking at their group membership what drives they are getting mapped.
  4. You can create one logon script and give it to everyone in the company!
  5. It still works when you’re connecting remotely from offsite

Here’s the script:

'================================================================================
'=                                                                                =
'= NAME:             logon.vbs                                                    =
'=                                                                                =
'= DESCRIPTION:        Map user drives based on group membership                    =
'=                                                                                =
'= VERSION HISTORY:                                                                =
'=                                                                                =
'=    1.0        02/06/2011    Stuart Jordan    Script rewritten with specific per        =
'=                                        drive groups only used for mapping        =
'=                                                                                =
'================================================================================

'Don't prompt if there are any errors. Users don't want to see these!
On Error Resume Next

Dim WshNetwork, adsPath,strUser, strMappedDrives
Const ADS_READONLY_SERVER = 4
'This is the start path from which we look for the object in LDAP
strLDAPPath="dc=acme,dc=corp"
strDomainName="ACME"

'This is the drive letter we're using for the user's homedrive
strHomedriveDriveLetter="H"
'This just contains a list of drives mapped to be outputted to the screen
strMappedDrives="Drives mapped: "
'This is how long the IE window stays open in seconds after all the drives are mapped
intCloseWait=20

'This records the time at the start of the script
'We'll check at the end and then output the difference
startTime=Timer()

'Quit the logon script if we've logged onto a server
if isServer(".") then
    wscript.quit
end if

'Get the Username of the person logging in
Set WSHNetwork = WScript.CreateObject("WScript.Network")
strUser = ""
While strUser = ""
    strUser = UCase(WSHNetwork.UserName)
Wend

'Get the user object from Active Directory.
adsPath = "WinNT://" & strDomainName & "/" & strUser
Set dso = GetObject("WinNT:")
Set objUser = dso.OpenDSObject(adsPath,"","", ADS_READONLY_SERVER)

'Set the logon script image as the one that matches the user's
'Home site, e.g. London. We get this from the OU the user account
'is in. Move the user account and this will change.
strLogonScriptImage=getHomeSiteFromUserName(strUser) & ".jpg"

'Create a new internet explorer object
set InternetExplorer = new IE
'Create the Internet explorer window
InternetExplorer.Initialize
'Write something in the first line below the image
InternetExplorer.SetMessageLine1 "You are logged on as " & strDomainName & "\" & strUser

'For each group the user is a member of
For Each Group In objUser.groups
    'Check to see if this group is a drive mapping group
    if string_compare("DriveMappings-",Group.Name) then
        'If it is then lets process it.

        'The description field is of the form A,\\server\share
        'where A is the drive letter, server and share the destination
        'mapping. Split creates an array using a separator, in this case
        'a comma.
        arrDescField=split(Group.Description,",")

        'The first cell contains the drive letter to be mapped
        strTargetDrive=arrDescField(0) & ":"

        'The second cell contains the share to be mapped
        strTargetPath=arrDescField(1)

        'Check to see whether the drive specified is out homedrive letter (i.e. U)
        if string_compare(strHomedriveDriveLetter,strTargetDrive) then
            'Treat U drive as a special case as it's specific to the user logging in
            InternetExplorer.SetMessageLine3 "Drive " & strTargetDrive & " will be mapped to " & strTargetPath & strUser
            'Actually map the drive using the information we collected
            MapDrive strTargetDrive,strTargetPath & "\" & strUser
        'If it's not then process as normal
        else
            InternetExplorer.SetMessageLine3 "Drive " & strTargetDrive & " will be mapped to " & strTargetPath
            'Actually map the drive using the information we collected
            MapDrive strTargetDrive,strTargetPath
        end if
        InternetExplorer.SetMessageLine2 strMappedDrives
    end if
Next

'Get the time at this point
EndTime=Timer()
'Work out the durating between EndTime and StartTime in milliseconds
Duration=int((EndTime-StartTime)*1000)
'Output a completion status on line 3. Note the double minus in calculating the duration.
'This is so that we always round the value up so we don't return 0 seconds. Neat huh?
InternetExplorer.SetMessageLine3 "Completed in " & -int(-Duration/1000) & " seconds."

'This bit waits for a set period before closing the window automatically
do while intCloseWait > 0
    'A countdown appears on messageline4. When it reaches 0 seconds the "do while" exits
    InternetExplorer.SetMessageLine4 "This screen will close automatically in " & intCloseWait & " seconds."
    wscript.sleep 1000
    intCloseWait = intCloseWait - 1
loop

'Now exit IE
InternetExplorer.quit
'And clear our InternetExplorer object
set InternetExplorer=nothing
'Quit the script
wscript.quit

'==========================================================================
'=    Subs and functions

'This class object is used to create and manipulate our Internet Explorer window
class IE
    'These are our variables but since they're private we can only change them
    'by calling PUBLIC functions within the class
    private m_MessageLine1,m_MessageLine2,m_MessageLine3,m_MessageLine4
    private m_IE, m_objDocument

    'These public functions allow us to update the m_messageline1 variable which contains the
    'output in the IE window on messageline1
    public function SetMessageLine1(strNewMessageText)
        m_MessageLine1=strNewMessageText
        UpdateMessageLine1()
    end function

    public function SetMessageLine2(strNewMessageText)
        m_MessageLine2=strNewMessageText
        UpdateMessageLine2()
    end function

    public function SetMessageLine3(strNewMessageText)
        m_MessageLine3=strNewMessageText
        UpdateMessageLine3()
    end function

    public function SetMessageLine4(strNewMessageText)
        m_MessageLine4=strNewMessageText
        UpdateMessageLine4()
    end function

    'Private functions to update the variables
    private function UpdateMessageLine1()
        m_IE.document.all.MessageLine1.innerHTML=m_MessageLine1
    end function

    private function UpdateMessageLine2()
        m_IE.document.all.MessageLine2.innerHTML=m_MessageLine2
    end function

    private function UpdateMessageLine3()
        m_IE.document.all.MessageLine3.innerHTML=m_MessageLine3
    end function

    private function UpdateMessageLine4()
        m_IE.document.all.MessageLine4.innerHTML=m_MessageLine4
    end function

    'This is a public sub that creates the IE window inside which the HTML page will be shown
    public Sub initialize()
      On Error Resume Next
      Set m_IE = CreateObject("InternetExplorer.Application")
      With m_IE
        'These are the attributes of the window created by IE.
        .navigate "about:blank"
        .visible= True
        .resizable=0
        'Height of the IE window
        .height=543
        'Width of the IE window
        .width=620
        .toolbar=0
        .statusbar=0
        .menubar=0
        .visible=1
        'Position these number of pixels in from the top left of the screen
        .Left=20
        .Top=20
        .document.parentWindow.opener = "me"
      End With
      Do while m_IE.Busy
        ' wait for page to load
        Wscript.Sleep 100
      Loop
      'Call the private sub to load the html content in the window
      loadHTML()
    End Sub

    'This sub forms the HTML page shown in the IE window shown during logon
    private sub loadHTML()
        Set m_objDocument = m_IE.document
        m_objDocument.Open
        m_objDocument.Writeln "<html>"
        m_objDocument.Writeln "<body SCROLL='no' onclick=self.close() leftmargin='0' marginheight='0' marginwidth='0' topmargin='0'>"
        m_objDocument.Writeln "<table width='620' border='0' cellspacing='0' cellpadding='0'>"
        m_objDocument.Writeln "<tr>"
        'This is the image that's loaded in the IE window. Note that it loads an image that is particular to the site that
        'the user account is found in (or more strictly the branch OU)
        m_objDocument.Writeln "<td><img src='file://acme.corp/netlogon/images/" & strLogonScriptImage & "' alt='' height='457' width='620' border='0'></td>"
        m_objDocument.Writeln "</tr>"
        m_objDocument.Writeln "</table>"
        m_objDocument.Writeln "<table width='620' border='0' cellspacing='0' cellpadding='0'>"
        'These are the message lines referenced above when were outputting text to the IE window
        m_objDocument.Writeln "<tr><td><font face='Verdana' size='1' ID='MessageLine1'></font></td></tr>"
        m_objDocument.Writeln "<tr><td><font face='Verdana' size='1' ID='MessageLine2'></font></td></tr>"
        m_objDocument.Writeln "<tr><td><font face='Verdana' size='1' ID='MessageLine3'></font></td></tr>"
        m_objDocument.Writeln "<tr><td><font face='Verdana' color='blue' size='1' ID='MessageLine4'></font></td></tr>"
        m_objDocument.Writeln "</table>"
        m_objDocument.Writeln "</html>"
        m_objDocument.Close 
    end sub

    public sub quit()
        m_IE.quit
    end sub
end class

'Actually maps the network drives
Sub MapDrive(strDrive,strShare)
    On Error Resume Next
    WSHNetwork.MapNetworkDrive strDrive, strShare
    If Err.Number Then
        'wscript.echo Err.Number & " so removing drive first"
        err.clear
        WSHNetwork.RemoveNetworkDrive strDrive
        WSHNetwork.MapNetworkDrive strDrive, strShare
        If Err.Number Then
            'wscript.echo "Removing drive still failed with error " & Err.Number & ":" & Err.Description
        end if
    End If
    strMappedDrives = strMappedDrives & strDrive & " "
End Sub

'Checks to see if the machine we're running the logon script on is a "server" because
'we don't really want to map drives automatically on servers.
function isServer(strtmpComputer) 
    boolReturn=0
    on error resume next
    ' WMI connection to Root CIM
    Set objWMIService = GetObject("winmgmts:\\" & strtmpComputer & "\root\cimv2")
    'Get list of items (there is only one in this case)
    Set colItems = objWMIService.ExecQuery("Select DomainRole from Win32_ComputerSystem")
    for each objComputer in colItems
        'This returns a number depending on the role in the domain
        intRole=objComputer.DomainRole
        '0 (0x0) standalone Workstation
        '1 (0x1) Member Workstation
        '2 (0x2) Standalone Server
        '3 (0x3) Member Server
        '4 (0x4) Backup Domain Controller
        '5 (0x5) Primary Domain Controller
    next
    'If the system is 2,3,4 or 5 then it is a server. Otherwise we'll assume it's a workstation.
    select case intRole
        case 2,3,4,5
            boolReturn=1
        case else
            boolreturn=0
    end select

    isServer = boolReturn

end function

'This is used to check is a string is found inside another
'It's a bit like strcmp but works for regex and where
'the expression is found at the start of the string.
private function string_compare(expression,targetstring)
    set oreg= new regexp
    oReg.pattern=expression
    oReg.IgnoreCase = TRUE
    if ("" = expression OR "" = targetstring) then
        boolSearchResult=0
    end if
    if oReg.test (targetstring) then
        boolSearchResult=1
    else
        boolSearchResult=0
    end if
    string_compare=boolSearchResult
end function

'Do a LDAP lookup to find the location of the user account
'in active directory. Used to find the home site of the 
'particular user.
function getCanonicalNameFromUsername(strSamAccountName)
    dim strReturn,arrCN

    strSamAccountName=LCase(strSamAccountName)
    'Setup for the LDAP connection
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open "Provider=ADsDSOObject;"
    Set objCommand = CreateObject("ADODB.Command")
    objCommand.ActiveConnection = objConnection

    'Let's find the person
    strType="User"
    objCommand.CommandText = "<LDAP://" & strLDAPPath & ">;(&(objectCategory=" & strType & ")" & _
                       "(samAccountName=" & strSamAccountName & "));canonicalName;subtree"  
    'Run the query
    Set objRecordSet = objCommand.Execute

    if objRecordset.RecordCount = 1 then
        'For some reason the canonicalName object is returned as an array
        'with the first array cell containing the actual canonicalName
        arrCN=objRecordset.Fields("canonicalName")
        'Ubound is the reference of the last cell in the array (zero in this case)
        'but added for completeness
        strReturn=arrCN(Ubound(arrCN))
        'wscript.echo "Found " & strSamAccountName & " at " & strReturn
    else 
        wscript.echo "Failed to find " & strSamAccountName
    end if

    getCanonicalNameFromUsername=strReturn
end function

'Uses the canonicalName to work out the home site of the user so
'that the correct image can be shown in the logon script
'window
function getHomeSiteFromUserName(strtmpUsername)
    dim strReturn
    'The site name is contained in the third set of / slashes
    'acme.corp/Europe/London/OU1/OU2/username

    'So let's split the canonicalName using the /'s with an
    'level per cell
    arrCN=split(getCanonicalNameFromUsername(strtmpUsername),"/")
    if Ubound(arrCN) > 1 then
        strReturn=arrCN(2)
    else
        'Let's default to default if we can't work out what site the
        'user account is in
        strReturn="default"
    end if
    getHomeSiteFromUserName=strReturn
end function

Download (rename to a zip)

Tagged , , , ,