Category Archives: Windows

OpenVPN errors with “All TAP-Win32 adapters on this system are currently in use.”

OpenVPN may work for a while then suddenly not be able to connect. If you delve into the logs you may find this error:

All TAP-Win32 adapters on this system are currently in use.

OpenVPN uses a TAP interface which appears in the “Network Connections”. To fix this error try these steps:

  1. Go to control panel -> Network Connections (or Windows Key -> type network -> click Settings, then select Network Connections”
  2. Find the interface with the description “TAP-Windows Adapter”. Right click it and select Disable, then enable it again.
  3. If the interface is already disabled just enable it.
  4. Retry OpenVPN

 

 

 

Advertisements

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 ,

SQL Server Compressed backups fail with SetEndOfFile error

When performing a backup dump using compression you may get an error like this one:

Executing the query “BACKUP DATABASE [MyDatabase] TO  DISK = N’\\\\myserver\\myshare\\…” failed with the following error: “The operating system returned the error ’87(failed to retrieve text for this error. Reason: 15105)’ while attempting ‘SetEndOfFile’ on ‘\\\\myserver\\myshare\\\\MyDatabase_backup_2012_06_12_210001_7306331.bak’.
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 12767904 pages for database ‘MyDatabase’, file ‘MyDatabase_dat’ on file 1.
100 percent processed.
Processed 9009 pages for database ‘MyDatabase’, file ‘MyDatabase_log’ on file 1.”.
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

It doesn’t always happen every backup. The problem appears to be related to the way that SQL server creates the backup file. When using compression the SQL server pre-allocates space for the backup file and the data is then written into it. Since the data is compressed the backup takes less space than was allocated and SQL server instructs the filesystem to truncate the file (set the end of the file). On local disk this can happen quickly but over a network to a share it can take longer.

The function used to truncate the file is SetEndOfFile (http://msdn.microsoft.com/en-us/library/windows/desktop/aa365531%28v=vs.85%29.aspx).

When the backup file is written to a SMB share the truncation can take longer than expected, especially if the amount of data to be truncated is quite large (i.e. it was compressed quite alot) and the request can timeout.

The timeout relating to this on SMB shares is in the Workstation properties (LanManWorkStation). You can tweak the timeout by adding the following registry entry:

1. Click Start, click Run, type regedit.exe, and then click OK.
2. In Registry Editor, locate and then right-click the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters
3. On the Edit menu, point to New, and then click DWORD Value.
4. Type SessTimeout, and then press ENTER.
5. On the Edit menu, click Modify.
6. In the Edit DWORD Value dialog box, click Decimal under Base, type 300 under Value data, and then click OK.

The default SessTimeout setting is 50 seconds. This changes it to 300 seconds (5 minutes).

For more information check out:

http://support.microsoft.com/kb/961657
http://support.microsoft.com/kb/102067
http://support.microsoft.com/KB/97876

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 , , ,

Recording PC idle time

This script is written in AutoIT and checks for when the PC has been idle (no keyboard presses or mouse movements) for a specific length of time. Currently the script logs to screen but you should be able to easily get it to log to a file and database, although the latter will require more work from you to get it working.

Here’s the script:

#AutoIt3Wrapper_Au3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Timers.au3>

$data = InputBox("Threshold", "Record user input if the PC has been idle for (in seconds)")

; Mouse/Keyboard action during this 10 sec delay will change reported idle time
Global $SleepDuration = 10 * 1000 ; 10sec
Global $TotalIdleTime = 0
Global $Count = 0
Global $Threshold = $data*1000

; Create your database manually then add these
Global $MyDB="UserIdle"
Global $MyUser="Testuser"
Global $MyPwd="TestPass"
Global $MySvr="MyDBServer"

while 0 = 0
    Sleep($SleepDuration)
    Global $IdleTime = _Timer_GetIdleTime()

    ConsoleWrite("DEBUG: idle = " & $IdleTime & @LF)

    if $IdleTime > $SleepDuration-2 Then
        $TotalIdleTime = $IdleTime
    else 
        if $TotalIdleTime > $Threshold then
            ConsoleWrite ("Machine returned from idle after " & int(($TotalIdleTime + ($SleepDuration - $IdleTime))/1000) & " seconds" & @LF)
        EndIf
        $TotalIdleTime = $IdleTime
    endif
wend

func write_to_db()
    $conn = ObjCreate( "ADODB.Connection" )  
    $DSN = "DRIVER={SQL Server};SERVER=MySvr;DATABASE=MyDB;UID=MyUser;PWD=MyPwd;"  
    $conn.Open($DSN)  
    $rs = ObjCreate( "ADODB.RecordSet" )  
    ;You'll need to change this insert to match the name of the table and the columns in your database
    $rs.Open( "INSERT some data into my database", $conn )  
    $conn.close 
EndFunc

On running the script prompts with a dialogue box for the number of minutes to wait before treating the PC as idle and logging the event.

Compiling

Grab and install AutoIT from their website. Use the AutoIT editor to tweak the script how you’d like. To compile open “AutoIT Script to exe converter” from the AutoIT start menu group. Choose the options as you see fit and compile. There are compilers for x64 and x32 which you’ll find in the <program files>\AutoIt3\Aut2Exe directory.

Heres the source and binaries to download:

Rename from doc to zip

Tagged , ,