Part 2 . . . ARRGGHH, Give me the map, Jim Boy.
In our last episode we left Jim facing the Pirate Peg Leg Joe, well maybe not. Last time we were able to read the file size of our mdf/ldf files. This time we need to watch the size of a folder be it the MS SQL data folder or a DTS import folder.
Real example - we have main frame files imported from a remote location to a folder - the files are processed into the database - and are deleted from the import folder after being moved into a date coded folder on the same server. The reason for the date coding and storage is that new reports get created and the database will not be able to process them until the report parameters are defined. We can then reprocess the files and all is well.
Shiver me timbers, these files can get very large. 20,000 page reports are not uncommon. We also might need to re-process older reports with different parameter settings to extract different data This third-party application is tied to an MS SQL database on the same server. We have to watch the size of this folder offloading the older folders to storage media.
The problem is we dont want to check the size of files but a folder. We need a map. Not a treasure map but a drive map. There are other ways to get folder specifics but they are somewhat more complex. Plus it is a good thing to learn another way to the treasured information.
As long as the threshold is not exceeded you will get an OK indication.
If the threshold is exceeded the alert is red and gives a critical warning.
By using visual indicators we have found that users who get numerical values will often miss the critical number change, but a red warning is heeded. If the situation is ignored and not addressed the user will also eventually ignore the red warning. We are writing to a html document, but could just as well send this to a text file. The text or csv file can be sent to a database where the detection of the word "critical" will send an email, invitation to a plank walk, or whatever.
yourFolderScript.vbs is below:
' *** Conversion codes Bites to Megabytes
' and 100 megabytes threshold ***
const CONVERSION_FACTOR = 1048576
const WARNING_THRESHOLD = 100
' Just the date and time
dtmDate = CStr(Now())
' basic object info the dot computer is the computer you are
' running this from and the wmi stuff is setting the objects
' needed for this to get the info you need.
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer)
Set colProcesses = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process")
Set objFSO = CreateObject("Scripting.FileSystemObject")
' This creates a file on your computer on the c drive root
' You can send this file wherever you want
Set objNewFile = objFSO.CreateTextFile("c:\folderDiscovery.htm")
' starts writing to the htm file
objNewFile.WriteLine "<HTML><HEAD>"
objNewFile.WriteLine "<TITLE>STATUS REPORT</TITLE></HEAD>"
objNewFile.WriteLine "<BODY BGCOLOR='IVORY'><CENTER><H3>STATUS REPORT - - DATE: " & dtmDate & "</H3>"
' here you will change the folder path you want to monitor
' ("R:\AsciiHold") is changed to ("X:\yourFolderLocation")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("R:\AsciiHold")
strSize = objFolder.Size/CONVERSION_FACTOR
strSize = Int(strSize)
' writes the collected data to htm file and closes the htm
objNewFile.WriteLine "<TABLE BORDERCOLOR='BLACK' BORDER='3' CELLPADDING='4' CELLSPACING='4'>"
objNewFile.WriteLine "<TR><TH ALIGN='CENTER'>PATH</TH><TH>STATUS</TH><TH>SIZE MB</TH>"
objNewFile.WriteLine "<TR><TD> " & objFolder.Path & " </TD>"
If strSize > WARNING_THRESHOLD Then
objNewFile.WriteLine "<TD BGCOLOR='RED'><FONT COLOR='WHITE' SIZE=+1><B>CRITICAL</B></FONT></TD>"
objNewFile.WriteLine "<TD BGCOLOR=WHITE ALIGN='RIGHT'><B> " & strSize & " </B></TD></TR>"
Else
objNewFile.WriteLine "<TD BGCOLOR=GREEN><FONT COLOR=WHITE>Size OK</FONT></TD>"
objNewFile.WriteLine "<TD BGCOLOR=CCFFCC ALIGN='RIGHT'> " & strSize & " </TD></TR>"
End If
objNewFile.WriteLine "</TABLE><BR>"
objNewFile.WriteLine "Warning threshold = " & WARNING_THRESHOLD & " Megabytes"
objNewFile.WriteLine "</CENTER></BODY></HTML>"
' *** IE CODE - opens Discovery.htm file with the browser ***
const MAXIMIZE_WINDOW = 3
Set objNetwork = Wscript.CreateObject("Wscript.Network")
Set objShell = Wscript.CreateObject("Wscript.Shell")
objShell.Run "c:\folderDiscovery.htm", MAXIMIZE_WINDOW
How does this work? Like in part 1 the conversion and threshold constants are set. In VBScript you will note that the "Const" expression is sometimes longer than the constant. Is this insane? Yes, but with good reason. The "Const ForReading = 1" is used to set the object to read mode. If you had a pile of code you would have to remember that the "1" value meant read. But the ForReading const tells you right away that this is set to read and sets the value to 1. Plus some of this values are really strange "h^&(($%^)Arggh" and so forth.
The WARNING_THRESHOLD is set to the value you determine to be critical. You can stack the script and have multiple folders being monitored and have different thresholds on each.
The second part of the code just creates the file "c:\folderDiscovery.htm".
The third part is different. It has to be run on a local machine. This is why you map a drive and set the code to:
Set objFolder = objFSO.GetFolder("X:\yourFolderLocation")
This is set to the Drive and Folder you want to monitor. Change the X to the drive on the server where you are mapped and the yourFolderLocation to the path or folder you want to monitor.
The last part is the same as in Part 1 of this series with slight variations to get you acquainted with how you can easily customize the script to your needs.
If you wanted to produce a csv file you could make the following changes:
' *** Conversion codes Bites to Megabytes
' and 100 megabytes threshold ***
const CONVERSION_FACTOR = 1048576
const WARNING_THRESHOLD = 100
' basic object info the dot computer is the computer you are
' running this from and the wmi stuff is setting the objects
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer)
Set colProcesses = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process")
Set objFSO = CreateObject("Scripting.FileSystemObject")
' This creates a file on your computer on the c drive root
' You can send this file wherever you want
Set objNewFile = objFSO.CreateTextFile("c:\folderDiscovery.csv")
' starts writing to the csv file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("R:\AsciiHold")
strSize = objFolder.Size/CONVERSION_FACTOR
strSize = Int(strSize)
' writes the collected data to csv file
If strSize > WARNING_THRESHOLD Then
objNewFile.WriteLine "AsciiHold,CRITICAL," & strSize
Else
objNewFile.WriteLine "AsciiHold,Size OK," & strSize
End If
The above script could be run locally on each SQL server. The csv file data could be collected and imported into a central SQL server into a small table. Remember that the above file "folderDiscovery.csv" will be overwritten each time the script is run.
Space consideration is rapidly becoming a major concern. We are storing Ghost images, more and more information, reports, there are multiple storage because of encryption requirements and on and on. The idea of server consolidation is forcing the sharing of space and processing. There was a time when I could not conceive of how I would ever fill a 12 Meg hard drive. Now I worry about filing 2 terabytes too quickly.
Next Part 3 the Magic of Excel.