Hey, Scripting Guy! How Can I Be Notified if an Access Database File Exceeds a Specific Size?

ScriptingGuy1

Hey, Scripting Guy! How can I trigger an alert if an Access database file exceeds a specific size?

— MA

Hey, MA. You know just the other day Microsoft.com posted 100 Reasons You’ll Be Speechless, a Web page listing 100 reasons why “Seeing Windows Vista for the first time may leave you searching for words.”

At any rate, this was such a good idea that we decided we would stea – uh, we decided we would … emulate … it. With that in mind, here are 100 Reasons You’ll Be Speechless after reading the Hey, Scripting Guy! column:

Oh, my: would you look at the time? Guess we better skip reasons 6 through 99 and just cut right to reason number 100:

In other words, you can find this script:

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
    (“SELECT * FROM __InstanceModificationEvent WITHIN 30 WHERE ” _
        & “TargetInstance ISA ‘CIM_DataFile’ and ” _
            & “TargetInstance.Name=’C:\\Scripts\\Test.mdb'”)
Do
    Set objLatestEvent = colMonitoredEvents.NextEvent
    If objLatestEvent.TargetInstance.FileSize > 204800 Then
        Wscript.Echo “The Access database file has exceeded its allowed size.”
    End If
Loop

OK, let’s see if we can figure out how this baby works. (Reason 74: You can never be sure whether the Scripting Guy who writes this column actually knows what he’s doing.) As you can see, we start out by connecting to the WMI service on the local computer. Is it possible to get this script to work against a remote computer? You bet it is, which is good: after all, a script like this would be, at best, only marginally useful if it could work against only the local machine. (Reason 16: The Scripting Guy who writes this column knows more about being marginally useful than any person alive.) You want to run this script against a remote computer? Then simply assign the name of that remote computer to the variable strComputer:

strComputer = “atl-dbs-01”

That brings us to this panic-inducing line of code:

Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
    (“SELECT * FROM __InstanceModificationEvent WITHIN 30 WHERE ” _
        & “TargetInstance ISA ‘CIM_DataFile’ and ” _
            & “TargetInstance.Name=’C:\\Scripts\\Test.mdb'”)

To begin with, don’t panic: this is nothing more than the line of code that enables us to “subscribe” to a specified WMI event. And what event are we subscribing to? Well, we want to be notified any time an new instance of the__InstanceModificationEvent class is created; as you might have guessed, a new instance of this class is created any time a WMI object (be it a process, a service, a disk drive, or, yes, a file) is modified in any way. Of course, we don’t want to be notified every time something gets modified on the computer; that could result in receiving scores of alerts every second. Instead, we want to be notified only if the TargetInstance (that is, the new instance of the __InstanceModificationEvent class) meets the following criteria:

Oh, and in case you’re wondering, Test.mdb just happens to be the Access database file we’re keeping a watchful eye on.

After we subscribe to the WMI event service we next set up a Do loop designed to run, well, forever. (Notice that there’s no exit criteria of any kind.) Inside that loop, we use this line of code to instruct the script to sit patiently and wait for the next instance modification event (or at least the specific such event we subscribed to) to occur:

Set objLatestEvent = colMonitoredEvents.NextEvent

What if no such event ever occurs? No problem; like we said, the script will sit here forever if need be. (Reason 56: Some Scripting Guy scripts just sit there and do nothing. Just like the Scripting Guys themselves.) As soon as a new instance modification event does occur, however, we use this line of code to check to see if the FileSize property is larger than 204,800 bytes:

If objLatestEvent.TargetInstance.FileSize > 204800 Then

Again, there’s nothing special about the value 204,800; we had to pick a target file size and so we picked this one. In case you’re wondering, 204,800 bytes is equal to 200 kilobytes: 200 * 1024. (Reason 29: We do the math so that you don’t have to … Well, unless it’s algebra. Then you’re on your own.)

And what if the file size does exceed 204,800 bytes? In that case, our sample script simply echoes back a message to that effect:

Wscript.Echo “The Access database file has exceeded its allowed size.”

And then we loop around and wait for the next such event to occur.

Just a few quick notes before we go. To begin with, this script works best when run in the command window under the CScript scrip host; that way you don’t have to deal with message boxes popping up, and you can terminate the script simply by closing that instance of the command window. In addition, you can easily modify this script to do something a little fancier than simply echoing back a message; for example, the script could write an event to the event log or send an email using SMTP mail. That’s entirely up to you. (Reason 91: We let our readers do most of the work.)

By the way, MA, we know that you’re using Microsoft Operations Manager (MOM). Hopefully you can take the code we’ve shown you today and modify it as needed to get it to trigger a MOM alert. If you run into any problems, drop us a line and we’ll see if we can find someone who can help you. We’d help you ourselves, but if there’s one thing that the Scripting Guys know very little about (and there definitely is at least one thing we know very little about) it’s MOM.

At any rate, we hope that helps, and we hope this gives people reason to continue reading Hey, Scripting Guy! each and every day. And if that isn’t reason enough, well, don’t worry: we’ll be bringing you plenty of useful information in the coming months. For example, we still haven’t told anyone the story of Scripting Guy Dean Tsaltas and the Killer Cows of Oxford, England. Peter Costantini, the oldest living Scripting Guy, continues to defy the laws of science and nature by getting even older. And, hey, the next high school baseball season is just around the corner. It should be great year, huh?

ScriptingGuy1

Follow