Map Drive with Domain Password Masked (Windows XP)

'#############################################################
'Author: Scot Geer //
'Script Purpose: Script to map a drive
'Script Reason: Issue with end-users connecting to VPN that
'               cannot connect to an "already" mapped drive
'               on an active directory domain. The fix is
'               to manually map the drive (issue with cached
'               credentials?)
'Script Date: Aug 2006
'Script Updated: April 2008
'Script Actions: Prompts user for username/password, then
'                maps the drive to the domain share. It also
'                has error-handling built-in (checks for
'                network, bad password, etc.) and informs the
'                user as such.
'                *Observe the password is masked when the
'                user types it in*
'Script Requirements:
'         1. XP or above is required (thanks to
'            "ScriptPW.Password", it will *not* work
'            on Win2k or below). FYI, this does *not* work in Vista
'            or Windows 7 due to the lack of the scriptpw.dll file.
'            It *might* work if you copy over that file from an XP
'            machine
'         2. This script must be run in a command line
'            window (with the 'cscript' parameter):
'            "cscript scriptName.vbs"
'         3. To automate this script, create a batch
'            file with the line:
'            "start cmd /k cscript /nologo scriptName.vbs"
'            Batch file and vbs script should reside
'            in the same folder
'         4. Technician will have to edit parameters
'            "drvLetter", "ShareDir", & "domain" in
'            order for this script to work
'
'#############################################################
Option Explicit

Dim objShell,objNetwork,objPassword
Dim drvLetter,shareDir,domain,userName,userPswd
Dim driveAlreadyMapped,errorNOS
Dim error001,error002,error003

Set objShell = WScript.CreateObject("Wscript.Shell")
Set objNetwork = WScript.CreateObject("WScript.Network")
Set objPassword = CreateObject("ScriptPW.Password")

driveAlreadyMapped = False
errorNOS = False

drvLetter = "S:"    'change to any drive letter desire
shareDir = "\\serverName\shareName"   'change to your server share
domain = "domainName\"            'keep the backslash

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WScript.StdOut.Write "Please enter your username:"
userName = WScript.StdIn.ReadLine
WScript.Echo

If userName = "" Then
    WScript.Echo
    Wscript.StdOut.WriteLine String(60, "#")
    Wscript.Echo "No Username Entered or User Cancelled"
    quitscript
End If

Wscript.StdOut.Write "Please enter your password:"
userPswd = objPassword.GetPassword()
Wscript.Echo
WScript.Echo

mapDrive drvLetter,shareDir,domain,userName,userPswd
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


'Maps drive w/username, pswd. Invokes error handler if drive is already mapped
'******************************************************************************
Sub mapDrive(drv,share,dom,usr,pswd)

    On Error Resume Next

    objNetwork.MapNetworkDrive drv, share, "false", dom & usr, pswd

    errorHandler_mapDrive

    If Not(driveAlreadyMapped) And Not(errorNOS) Then
        WScript.Echo
        Wscript.StdOut.WriteLine String(60, "#")
        Wscript.StdOut.WriteLine "Success!"
        WScript.Echo
        Wscript.StdOut.WriteLine "You may now open your ""Shared"" files"
        Wscript.StdOut.WriteLine String(60, "#")
    End If

End Sub
'*******************************************************************|

'Notifies user if drive is already mapped, bad username/pswd, or off network
'******************************************************************************
Sub errorHandler_mapDrive

    If Err.Number<>0 Then

        error001 = InStr(1,Err.Description,"already",1)
        error002 = InStr(1,Err.Description,"found",1)
        error003 = InStr(1,Err.Description,"password",1)

        If (error001) Then
            driveAlreadyMapped = True
            WScript.Echo
            Wscript.StdOut.WriteLine String(60, "#")
            Wscript.StdOut.WriteLine "Your ""Shared"" drive is already mapped"
            WScript.Echo
                WScript.StdOut.WriteLine "Open your ""Shared"" files as normal"
                WScript.Echo
                WScript.StdOut.WriteLine "Notice001"
                Wscript.StdOut.WriteLine String(60, "#")
        ElseIf (error002) Then
            errorNOS = True
            WScript.Echo
            Wscript.StdOut.WriteLine String(60, "#")
            Wscript.StdOut.WriteLine "The path to the drive could not be found"
            WScript.Echo
            WScript.StdOut.WriteLine "Drive = " & shareDir
            WScript.Echo
            Wscript.StdOut.WriteLine "Check your network connection to ensure " _
                & "you are connected to the Internet"
            WScript.Echo
            Wscript.StdOut.WriteLine "Make sure you are connected to the VPN"
            WScript.Echo
            Wscript.StdOut.WriteLine "Notice002"
            Wscript.StdOut.WriteLine String(60, "#")
        ElseIf (error003) Then
            errorNOS = True
            WScript.Echo
            Wscript.StdOut.WriteLine String(60, "#")
            Wscript.StdOut.WriteLine "Wrong username or password"
            WScript.Echo
            Wscript.StdOut.WriteLine "Run this script again"
            WScript.Echo
            WScript.StdOut.WriteLine "Ensure you are typing your username " _
                & "& password correctly"
            WScript.Echo
            WScript.StdOut.WriteLine "Check your CAPS LOCK"
            WScript.Echo
            Wscript.StdOut.WriteLine "Notice003"
            Wscript.StdOut.WriteLine String(60, "#")
        End If

          Err.Clear

    End If

End Sub
'*******************************************************************|

'Ends Script with a notification to the user
'******************************************************************************
Sub quitscript

    WScript.Echo
    WScript.Echo "Quitting Now"
    Wscript.StdOut.WriteLine String(60, "#")
    Wscript.Quit

End Sub
'*******************************************************************|

Server Scheduled Tasks Script

'Author: Scot Geer ()

'Purpose: Reads all scheduled tasks listed, compiles their last run, last result, etc.

'         & then sends it to specified recipient(s) via text email

'Date Created: 07 Oct 2008

'Instructions: Modify only the parameters in the "modify" section below

'Tips: If this script cannot send an email to the SMTP server, it will fail silently

'      & write to the Event Log. Put a bad ip address in there, then run it, then check

'      your event log (application, ERROR, WSH)

'##########################################################################################

Option Explicit

Const ForReading = 1

Const ForWriting = 2

Const ForAppending = 8

Set objShell = WScript.CreateObject("WScript.Shell")

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objFSO, objDocument, objShell

Dim commandsArr, schTasksArr(), groupedTasksArr()

Dim myCmd, x, fullCommand, totalTaskLinesRequested

Dim reqSchTasksLinesArr, myTimeDateNow

myCmd = "cmd /c "

'                                 !!! Modify this section only !!!

'##########################################################################################

'commandsArr is an array of command line commands

'CLI Scheduled Tasks reference found here:

'http://technet.microsoft.com/en-us/library/cc772785.aspx

commandsArr = Array("schtasks /query /fo LIST /v")

'run this CLI command "schtasks /query /fo LIST /v" and pick which lines you want here:

reqSchTasksLinesArr = Array("TaskName:","Next Run Time:","Last Run Time:","Last Result:")

'Modify this section only for the 'sendEmail' function

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Dim eFrom,eTo,eSubject,eBody,eAttachment,eUsername,ePassword

Dim eSmtpServer,eSmtpPort,eSmtpUseSSL

Const eNetworkType = 1 'use '1' if script will run on machine with SMTP installed

'Const eNetworkType = 2 'use '2' if script will run on SMTP box somewhere on network

eFrom = "s@some-domain.com"

eTo = "me@some-email.com"

eSubject = "Daily Scheduled Tasks Report (Server-Name - 10.12.14.21)"

eBody = ""

'Type in full path and filename with extension here if you want an attachment

eAttachment = "%homepath%\Local Settings\Application Data\Microsoft\Windows NT\NTBackup\data\backup01.log"

'Note: use domain\username if going to exchange server

'Note: use username@domain.com if going to public Internet SMTP server

'Note: only use username/password if SMTP server is on separate machine

eUsername = "" 

ePassword = "" 

eSmtpServer = "Name-of-SMTP-Server"

eSmtpPort = 25

eSmtpUseSSL = False

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

'##########################################################################################

'                                  !!! Modify this section only !!!

totalTaskLinesRequested = ((UBound(reqSchTasksLinesArr))+1)

'Main Code

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

myTimeDateNow = FormatDateTime(Time,0) & ", " & Date

For x=0 To UBound(commandsArr)

fullCommand = myCmd & commandsArr(x)

'replace Null with fullPath if writing to file

runCommands fullCommand, Null 

Next

groupTaskResults

eBody = prettyUpResults 'builds results into body of email

sendEmail eFrom,eTo,eSubject,eBody,eAttachment,eUsername,ePassword, _

eNetworkType,eSmtpServer,eSmtpPort,eSmtpUseSSL

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'Action: runs commands via command line, reads each line or reads all

'Dependencies: 'appendTextToFile()' (only if you want to write to file)

'              global declaration of schTasksArr to enter results

'           global declaration of totalTaskLinesRequested

'              'groupTaskResults()' 

'Parameters: 'pCommand' --> command line (ex: "schtasks /query /fo LIST /v") 

'            'pPath' --> fullPath of text file to write info to (if applicable)

'Returns: Nothing

'******************************************************************************

Sub runCommands(pCommand,pPath)

Dim objExecObject, strLine, y, w

Set objShell = WScript.CreateObject("WScript.Shell")

Set objExecObject = objShell.Exec(pCommand)

y = 0

Do Until objExecObject.StdOut.AtEndOfStream

   strLine = objExecObject.StdOut.ReadLine

   For w=0 To UBound(reqSchTasksLinesArr)

   If InStr(strLine, reqSchTasksLinesArr(w)) Then

    ReDim Preserve schTasksArr(y)

    schTasksArr(y) = strLine

' appendTextToFile pPath,schTasksArr(y) 'write to text file

    y=y+1

   End If

Next

Loop

End Sub

'********************************************************************|

'Action: pulls 3 specific lines from the 'schTasksArr' array and groups them

'        into their own sections for easy display or printing to a file

'Dependencies: 'schTasksArr' --> lines from --> "schtasks /query /fo LIST /v"

'              global declaration of 'totalTaskLinesRequested'

'              'totalTaskLinesRequested' = # of lines wanted from schtasks cmd

'Parameters: none

'Returns: nothing returned but it does build the global groupedTasksArr array

'******************************************************************************

Function groupTaskResults

'making info into groups of 3 lines 

'we want 3 specific lines from each sch task

Dim m, w, groupedTasks, groupedTaskResults, tempPlaceHolder

w = 0

'get number of total task lines, divide by totalTaskLinesRequested 

'to get # of sections needed

groupedTasks = (UBound(schTasksArr) + 1)/totalTaskLinesRequested

'don't forget to subtract 1 (starts at zero)

For x=0 To groupedTasks - 1

'grouping each scheduled task sections

'don't forget to subtract 1 (starts at zero)

For m=0 To totalTaskLinesRequested - 1

tempPlaceHolder = tempPlaceHolder & schTasksArr(w) & vbCrLf

w = w + 1

Next

groupedTaskResults = tempPlaceHolder

tempPlaceHolder = Null 

ReDim Preserve groupedTasksArr(x)

groupedTasksArr(x) = groupedTaskResults

'Keep this echo line here for testing purposes

' WScript.Echo groupedTasksArr(x) & vbTab & "This is the grouped tasks"

Next

End Function

'*******************************************************************|

'Action: formats array results into a readable/printable form

'Dependencies: globally declared array of some kind

'Parameters: none

'Returns: 'myPrettyPrint' --> properly formatted results

'******************************************************************************

Function prettyUpResults

Dim myPrettyPrint

For x=0 To UBound(groupedTasksArr)

myPrettyPrint = myPrettyPrint & groupedTasksArr(x) _

& vbCrLf & "Current System Time" _

& String(9, ".") & myTimeDateNow _

& vbCrLf & String(40, "#") & vbCrLf & vbCrLf

Next

prettyUpResults = myPrettyPrint

End Function

'*******************************************************************|

'Action: sends email & attachment (attachment optional)

'Dependencies: global declaration of "modify" section below

'Parameters: see "modify" section below

'Returns: nothing

'******************************************************************************

Function sendEmail(pFr,pTo,pSu,pTe,pAtt,eUser,ePswd,pType,pSmtp,ePort,eSSL)

'Modify this section only for the 'sendEmail' function 

'Put this section in the global declaration section of this script

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

' Dim eFrom,eTo,eSubject,eBody,eAttachment,eUsername,ePassword

' Dim eSmtpServer,eSmtpPort,eSmtpUseSSL

'Const eNetworkType = 1 'use '1' if script will run on machine with SMTP installed

'Const eNetworkType = 2 'use '2' if script will run on SMTP box somewhere on network

' eFrom = "username@domain.com"

' eTo = "recipient@domain.com"

' eSubject = "Subject Message"

' eBody = "Body of email"

'Type in full path and filename with extension here if you want an attachment

' eAttachment = "c:\temp\logs\backup01.log"

'Note: use domain\username if going to exchange server

'Note: use username@domain.com if going to public Internet SMTP server

' eUsername = "domain\username" 'don't use this line if eNetworkType=1 

' ePassword = "password"        'don't use this line if eNetworkType=1

' eSmtpServer = "serverName or IP address"

' eSmtpPort = 25

' eSmtpUseSSL = False

'

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

On Error Resume Next

Dim objEmail, extraMessages

Set objEmail = CreateObject("CDO.Message")

extraMessages = "Check the parameters in the 'email modify' section" _

& " of the script" _

& vbCrLf & "Rob I. is a Hottie!  :D"

objEmail.From = pFr

objEmail.To = pTo

objEmail.Subject = pSu

objEmail.Textbody = pTe

objEmail.AddAttachment(pAtt)

objEmail.Configuration.Fields.Item _

   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = pType

objEmail.Configuration.Fields.Item _

   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = pSmtp 

objEmail.Configuration.Fields.Item _

   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = ePort

'Only use this section if running this script from a non-SMTP server, such as a 

'workstation. Simply remove the apostrophe's at the beginning of each line.

'##############################################################################

' objEmail.Configuration.Fields.Item _

'    ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 

' objEmail.Configuration.Fields.Item _

'    ("http://schemas.microsoft.com/cdo/configuration/sendusername") = eUser

' objEmail.Configuration.Fields.Item _ 

'    ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = ePswd

' objEmail.Configuration.Fields.Item _

'    ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = eSSL

' objEmail.Configuration.Fields.Item _

'    ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

'##############################################################################

objEmail.Configuration.Fields.Update

objEmail.Send

myErrHandler(extraMessages)

End Function

'*******************************************************************|

'Action: generic error handler module that writes to the system event log

'Dependencies: global declaration of 'objShell'

'Parameters: 'pMsg' = extra message you want written to the event log

'Returns: nothing

'******************************************************************************

Sub myErrHandler(pMsg)

Dim errMessage, eventLogMessage

If Err.Number<>0 Then

errMessage = Err.Description

eventLogMessage = WScript.ScriptFullName _

& vbCrLf & vbCrLf & errMessage _

& vbCrLf & pMsg

' WScript.Echo eventLogMessage

objShell.LogEvent 1, eventLogMessage

End If

Err.Clear

End Sub

'*******************************************************************|

'Action: quits the script and prompts the user "Quitting Now"

'Dependencies: none

'Parameters: nothing

'Returns: nothing

'******************************************************************************

Sub quitScript

WScript.Echo "Quitting Now"

Wscript.Quit

End Sub

'*******************************************************************|

MySQL Double Joins

devices table
device_id (PK) | device_name

site_contacts table
device_id (FK) | contact_name | contact_email

site_address table
device_id (FK) | address

Select specific info from 2 tables without having to pull all info from all 3 tables:
################################################
SELECT contact_name, contact_email, address
FROM site_contacts INNER JOIN devices
ON site_contacts.device_id = devices.device_id
INNER JOIN site_address
ON site_address.device_id = devices.device_id
WHERE devices.device_name = 'fake_router.mrn';
################################################


LEFT (aka OUTER) JOIN:
Returns all records from the "LEFT" table (the table listed after the "FROM" clause) even the records that have no matching row in the "RIGHT" table (the table listed after the "LEFT JOIN" clause).

LEFT JOIN NOTES:
If both tables in a left join have the same column name, you can simplify your query with the following:
################################################
SELECT *
FROM devices LEFT JOIN site_address
USING (device_id);
################################################

*Note: In this case, all records from the 'devices' table would be returned even if there were no corresponding record in the 'site_address' table.


INNER (aka CROSS) JOIN:
Only returns matching row records that exist in 2 or more tables
################################################
SELECT device_name, address
FROM devices, site_address
WHERE devices.device_id = site_address.device_id;
################################################

*Note: in this case, only records from the 'devices' table that have matching records in the 'site_address' table will be returned (or vice-versa). The order of the tables listed in your query will not matter (because you are performing an INNER JOIN).