Create an event receiver script within your ActiveScriptEventConsumer with PowerEvents

I’ve had the pleasure of meeting Trevor Sullivan when I convinced him he should learn all about SQL Server.  Not really, but we did chat it up on Twitter and decided to both go to Cleveland for an intro on SQL Server 2008 R2.  Little did I know what he was working on.  Quite simply one of the coolest projects I’ve used from Codeplex.  Trevor has created PowerEvents!  I’ll admit, some of it is way over my head at the moment.  It’s directly related to how much I really know about WMI, but I feel that’s about to change drastically.

I posted a tweet earlier about what I’ve created with PowerEvents.  Actually, I feel as if it would be a best practice for the ActiveScriptEventConsumer.  You can be the judge of that .  Since it’s almost impossible to see what you’ve created as an Event Consumer, I’ve simply created only one: ActiveScriptEventConsumer.  That way I don’t have to worry about what has been added under the hood to WMI.  All I have to do is tweak the script that is fired when the event occurs.  So, I’ve built a basic script that looks for the arguments you have passed to it.  Based on these arguments, you can dynamically call different scripts or functions.  Pretty slick, eh?  Here’s a basic script that will email two different people based on what the WMI query results are.

Option Explicit
Const strFrom = "example@example.com"
Const strMailserver = "smtp.example.com"
Const strSchema = "http://schemas.microsoft.com/cdo/configuration/"
Dim objArgs, objEmail
Dim strProcessName, strSubject, strBody, strTo
'Get arguments from command line
set objArgs = WScript.Arguments
strProcessName = WScript.Arguments(0)
strSubject = WScript.Arguments(1)
strBody = Wscript.Arguments(2)
'Dynamically change the email recipient
'Or even change the function to be called
'Or call a completely different script: .bat, .vbs, .ps1
'Endless possibilities
If strProcessName = "NotePad.exe" Then
	strTo = "myboss@example.com"
ElseIf strProcessName = "Outlook.exe" Then
	strTo = "me@example.com"
End If
'Call to send email, but many different functions could be within this script and
'dynamically called based on arguments
Call SendEmail(strSubject, strBody)
'Function(s)
Sub SendEmail(Subject, Body)
	Set objEmail = CreateObject("CDO.Message")
	objEmail.From = strFrom
	objEmail.To = strTo
	objEmail.Subject = Subject
	objEmail.Textbody = Body
	objEmail.Configuration.Fields.Item _
	    (strSchema & "sendusing") = 2
	objEmail.Configuration.Fields.Item _
	    (strSchema & "smtpserver") = strMailserver
	objEmail.Configuration.Fields.Item _
	    (strSchema & "smtpserverport") = 25
	objEmail.Configuration.Fields.Update
	objEmail.Send
End Sub
'Clean up vars
set strProcessName = nothing
set strSubject = nothing
set strBody = nothing

 

I hope you find this useful.  I’m brand new to creating PowerEvents, but I do feel this is the best way to handle scripting based on events.  Feel free to post a comment if I’m an idiot and there is a much easier way…

VBA – String Manipulation

Sometimes you just have to not accept what is given to you.  I came across something interesting building a form within Excel today.  I have 7 combo boxes on this form and I needed to assign values to all of the choices.  So when a description of 20A Receptacle was chosen, I needed to assign a value of O to it.  The end result would be a string of these variables concatenated together to form one long string that resembled: (O X G OS S X S).

A few lines of code and were off:

Devices = Array(cmb1.Value, cmb2.Value, cmb3.Value, cmb4.Value, cmb5.Value, cmb6.Value, cmb7.Value)
NumOfDevices = 0
'Find all values for combobox
For i = 0 To UBound(Devices)
If Not Devices(i) = "" Then NumOfDevices = NumOfDevices + 1
'Match Description to Ganged
Select Case True
Case Devices(i) = "20A RECEPTACLE"
Ganged = Ganged & "O "
Case Devices(i) = "20A GFI"
Ganged = Ganged & "G "
Case Devices(i) = "20A GFI PROTECT DS"
Ganged = Ganged & "4G "
Case Devices(i) = "20A HALF SWITCHED DUPLEX"
Ganged = Ganged & "OS "
Case Devices(i) = "SINGLE POLE"
Ganged = Ganged & "S "
Case Else
Ganged = Ganged & "X "
End Select
Next i

Now that we have all of our variables assigned to the description picked, a problem has arose with my approach.  Sometimes, there are empty slots within these boxes that still need to be designated by an X, but only if they are contained within the string.  For example: (O X G OS S X S).  This is valid, but my code will assign X’s to every combo box that is blank or equal to “”.  I need this to happen to accommodate for the empty slots within the box.  The challenge comes when the box has less than 7 devices.  The string could look like this: (S X G X X X X).  Thinking outside of the box, I came up with this solution:

Do While UCase(Left(Right(Trim(Ganged), 1), 1)) = "X"
GangedLength = Len(Ganged)
Ganged = Left(Right(Trim(Ganged), GangedLength), GangedLength - 1)
Loop
Ganged = "(" & Trim(Ganged) & ")"

Seems to be working just fine now.  Any of these strings that end with X get cut off until I’m left with a value that doesn’t equal X.