During the course of your career, you will be asked to do small miracles for the company that you work for. The worst part of this, is that you may be out with your family at Disney, Chuck-e-Cheese, or the local pizza place when you get the page, or phone call. Regardless of how you get notified, it becomes extremely difficult to suddenly leave your family when an emergency arises. Some emergencies can't be helped, others are likely nuisances that have developed through no fault of your own, but still need to be addressed.
What to do?
Like most folks, if you value your career, there is no question on what needs to be done. You have to leave and fix the issue. That is simply a fact of life. Many developers and DBA's simply go home to VPN into work, or go to work directly to resolve the problem. I have done both and continue to do so. With a few tools that I now employ, however, I can do this less often than I used to for specific small problems, and many times I can continue to be with my family.
Easy Scripting Sample
Most of us by now know that scripting using VBScript can automate some of our day to day tasks. I use it for repetitious tasks that need to be performed. Let's start with email. Sometimes I need to send out a simple email, and I am not around my computer to do so. Maybe I am at a server that doesn't have an email client. I log in to a shared drive and double click my email script file, and poof. Email is sent.
So what does the script do? How does it work? Does it use exchange? The email script simply executes a stored procedure in my personal SQL Server that does the heavy lifting, provides the security I need since I don't want anyone except for myself sending emails, etc.
What needs to be running on my computer for this to work? IIS, SMTP, and SQL Server.
My domain name also needs to have admin privileges on my personal SQL Server.
Email.vbs code: '''''''''''''''' 'Open connection Dim DBConn Set DBConn = OpenConn() 'Create a new DBF file named Persons.DBF DBConn.Execute "EXEC sp_SMTPemail 'MyEmail@Mycompany.com' , 'SomeBody@Mycompany.com', 'Subject message here','Body of message goes here'" set DBConn =nothing Function OpenConn() Dim Conn: Set Conn = CreateObject("ADODB.Connection") Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDATABASE;Data Source=MYCOMPUTER" Set OpenConn = Conn End Function '''''''''''''''' Stored Procedure code to create in MYDATABASE used by script: ----------------------------- CREATE PROCEDURE sp_SMTPemail ( @From as nvarchar(50), @To as nvarchar(50), @Subject as nvarchar(255), @Body as text ) AS -- Declare DECLARE @message int DECLARE @config int DECLARE @hr int DECLARE @src varchar(255), @desc varchar(255) EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object -- Configuration Object EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com' -- SMTP Server EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate EXEC sp_OAMethod @config, 'Fields.Update' -- Message Object EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config EXEC @hr = sp_OASetProperty @message, 'To', @To EXEC @hr = sp_OASetProperty @message, 'From', @From EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body EXEC sp_OAMethod @message, 'Send()' -- Destroys the objects EXEC @hr = sp_OADestroy @message EXEC @hr = sp_OADestroy @config -- Errorhandler IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END select @hr ----------------------------------------
The code above will have you sending emails from almost anywhere within your network without a problem. Because of windows security, you don't have to worry about others trying to run it. This is an example of a scripting tool. As you can see the stored procedure uses sp_OACreate to call objects outside of SQL Server to email your message, although the sp_OACreate is outside the scope of this article, I have to say that it is one of the most powerful tools available, and if you can copy-paste, you can easily modify the code within this article for your own use.
WMI Scripting Sample
So what is WMI? The Definition from Microsoft says "Windows Management Instrumentation or WMI is a component of the Windows operating system that provides management information and control in an enterprise environment. By using industry standards, managers can use WMI to query and set information on desktop systems, applications, networks, and other enterprise components. Developers can use WMI to create event monitoring applications that alert users when important incidents occur."
It's a mouthful, but the gist of it is that it can help you monitor, stop, start, and edit files and services across the network. Sounds nice!
So how can we use it? Below is a sample WMI Script that looks at a computer across the network and stops a process (executable or service).
KillProcess.VBS Script '''''''''''''''''' Const PROCESS_NAME = "MYPROCESS" Dim colResults, objProcess, objWMI, strWQL 'Set objWMI = GetObject("WinMGMTS:Root/CIMv2") 'Used only for local process set lctr = CreateObject("WbemScripting.SWBemLocator") set objWMI = lctr.connectServer("10.20.10.100", "root\cimv2",".\UserName", "Password") strWQL = "SELECT * FROM Win32_Process WHERE Name = '" & PROCESS_NAME & "'" Set colResults = objWMI.ExecQuery(strWQL) For Each objProcess In colResults objProcess.Terminate Next '''''''''''''''''''''
First, WMI scripts are powerful, but the drawback is that if you create a script to use across the network, you may need to show a domain username and password which is unacceptable. Especially if the script is put into a shared drive that you would like to access across the network, and want to give someone access to if you are out sick, or for whatever other reason. To secure the script(s) that you would like to use it is suggested that you first create a local folder on your machine that only you (your domain account) has access to. We will use "C:\LocalSecureDirectory" as our directory. Then as you create all your WMI scripts, simply place them into this one secure directory.
So how do I run them across the network, and maybe allow my fellow coworker access to run one or two of them when he/she needs to? The solution again, is to use SQL Server like we did above to send an email message. Below is a SQL Server stored procedure that allows you to run a VBS script in the secure directory "C:\LocalSecureDirectory":
sp_RunVBS Stored Procedure -------------------------- CREATE PROCEDURE sp_RunVBS(@cmd VARCHAR(255), @Wait INT = 0) AS --Create WScript.Shell object DECLARE @result INT, @OLEResult INT, @RunResult INT DECLARE @ShellID INT Declare @Folder varchar(255) Select @Folder = '"C:\LocalSecureDirectory\' Select @cmd = @Folder + @cmd + '"' --select @cmd --test EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult) EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult) If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult EXECUTE @OLEResult = sp_OADestroy @ShellID RETURN @result ------------------------------------------
What does this procedure give you? It allows you to run VBScript files using a stored procedure within SQL Server 2000. This actually is pretty powerful in itself, as you can simplify coding and calling external objects using VBScripts rather than the sp_OACreate stored procedure which is often used.
Once this stored procedure is compiled, you can go to your shared drive, which is accessible across the network, and create a VBScript file that runs this procedure using NT security. You can give this script out to your buddies, add them to your personal SQL Server as users, and control their access simply by adding or taking away execute privileges to the procedure under SQL Server.
The script below shows how to execute the WMI script by simply passing the script name to the procedure we just compiled.
RunKillProcess.vbs '------------------ Dim sScriptName, Log Log = "C:\LocalShare\RunKillProcess.txt" sScriptName = "KillProcess.vbs" 'Open connection Dim DBConn, RsError, x, Error Set DBConn = OpenConn() 'Executes VBScript in secure folder using windows security set RsError = CreateObject("ADODB.recordset") RsError.Open "EXEC sp_RunVBS '" & sScriptName & "',0", DBConn 'All items below this are used for logging errors to my normal share folder "C:\LocalShare" 'Error Stuff, All procs need at least a return value of 0 for each x in RsError.fields Error= x.value next 'Set File Logging dim oFS1 'Create FileSystem Object Set oFS1 = CreateObject("Scripting.FileSystemObject") 'Set Logging bLogging = true if bLogging Then Set oLog = oFS1.CreateTextFile(Log, true) End if If Error = "0" Then 'No error, do nothing LogMessage "No Errors." Else 'Wscript.Echo "Error " & Error & "." 'msgbox "Error " & Error & "." LogMessage "Error " & Error & "." End If 'Destroy components set oFS1 = nothing set DBConn =nothing set RsError =nothing Function OpenConn() Dim Conn: Set Conn = CreateObject("ADODB.Connection") Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Magic;Data Source=ALOERA1" Set OpenConn = Conn set Conn = nothing End Function Sub LogMessage(sMessage) 'WScript.Echo sMessage 'oLog.writeline "(" & Now() & ") " & sMessage oLog.writeline sMessage End Sub Sub ShowMessage(sMessage) WScript.Echo sMessage 'oLog.writeline Now() & " - " & sMessage End Sub '---------------------------------------------------------------
As you can see we have a powerful tool that we can make use for administrative tasks and small emergencies. We can run the script across the network, give it out without giving up security, and add a large piece of functionality to SQL Server. I use this method to provide myself tools that I can access anywhere. For more on WMI Scripting, go to Microsoft: http://msdn.microsoft.com/library/en-us/wmisdk/wmi/wmi_tasks_for_scripts_and_applications.asp
I believe we have barely touched the tip of what can be accomplished here. To show what I mean, I will give you one last sample that you may be interested in. Remember that toward the beginning of this article, we imagined getting a call, or a page from work while we were out with our family...
I personally have a sidekick phone from t-mobile that I use for email, but I am sure if you have a pager with email functionality or some such device, the following can work for you, too. In Outlook, you can setup a rule that allows you to run a VBA function when a message arrives with "@SomeThing" in the subject of the message. You would first add a piece of code to outlook by going to Tools->Macro->Visual Basic Editor
After you open the editor, you can cut -paste the following:
'VBA Code '-------------------------------------------------- Sub CustomMailMessageRule(Item As Outlook.MailItem) Select Case Item.Subject Case "@Something" RunScript "Something.vbs" Case "@otherthing" RunScript "otherthing.vbs" End Select End Sub Private Function RunScript(MYScript As String) 'Open connection Dim DBConn, RsError, x, Error Set DBConn = OpenConn() 'Executes VBScript in secure folder using windows security Set RsError = CreateObject("ADODB.recordset") RsError.Open "EXEC sp_RunVBS '" & sScriptName & "',0", DBConn Set RsError = Nothing Set DBConn = Nothing End Function Function OpenConn() Dim Conn: Set Conn = CreateObject("ADODB.Connection") Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDATABASE;Data Source=MYCOMPUTER" Set OpenConn = Conn End Function '----------------------------------------------------------
At this time we have not put in any code for errors or logging in this example, but they should also be added. We save this and close the VB Editor.
Next we go back to Outlook and select Tools-> Rules and Alerts. Here we create a new rule stating that any message with a subject of "@" should run the new script "CustomMailMessageRule". As you can see, this opens up lots of great possibilities for automation, as we can specify in our CustomMailMessageRule, specific scripts we can run when an email comes in. Of course this information cannot be shared, as it would allow people to simply send you an email to activate a script.
Lets get back to where I got paged over a minor item while I was at Chuck-e-Cheese. In this situation, I would simply send an email to myself to run a script, the script would run a process and email me back the results of the process after it has finished. The nice part is that I can do this all while I am looking at a big dancing mouse with my family.
About the Author
Anthony Loera has been developing for more than 10 years. He has developed database applications for Verizon, Merck pharmaceuticals, Americatel Telecom, Microsoft, IDS Telecom, and Vitas Hospice Healthcare. He can be reached at his email: Brainclone @ Gmail.com