The VBScript Network and Systems Administrator's Cafe


May 23, 2008  8:00 PM

VBScript Statements: Explanation of the Const Statement



Posted by: Jerry Lees
Const, Const statement, VBScript, VBScript Statements, vbscriptstatements

The Const statement is useful when you want to reference a specific, unchanging, value in your code multiple times without typing it over and over.

For example, in order to use the Const Statement to create a reference to the value of pi to 50 decimal places, you use the following code:

Const pi = 3.14159265358979323846264338327950288419716939937510

The important thing to remember is that, constants can not be changed once set in your code– Doing so will result in an error being thrown.

May 23, 2008  2:39 PM

Sending e-mails with VBScript using the cdo.sys object



Posted by: Jerry Lees
cdo.message, cdo.sys, cdosys object, sending e-mails, sending emails

Writing text files, Word docs, Excel spreadsheets with VBScript is great and all– but sometimes you just need a process to run and notify you when something happens. (Without remembering to check a file every day.) In fact, sometimes it’s nice to walk in and receive an email in your inbox to remind you– or let you know all is well in the world.

 To accomplish this on windows 2003 and above the CDOSYS object is our best friend. There are a few caveats to using this script though. Note that (as I recall) CDOSYS is not available prior to Windows 2000, so if you’re running windows NT 4.0 you’ll need to use CDO.NTS instead, which is slightly different in syntax. Also, the object is installed as part of the SMTP service, so you will need the SMTP service installed on the system this script runs on to make it work. (Yes, it will work on Windows XP as well with the SMTP service installed.)

Using CDOSYS is really easy. Basically, you create the CDO.Message object and then set 4 properties (TO, SENDER, TEXTBODY, and SUBJECT) then call the SEND method to actually do the sending of emails. Literally, it’s that simple. If you don’t believe me, check out the following code:

      dim oMail
      set oMail = CreateObject(“CDO.Message”)
      oMail.To = “myemail@somedomain.com
      oMail.Sender = “
anotheremail@anotherdomain.com
      oMail.TextBody = “Your ticket has been submitted.”
      oMail.Subject = “CDO.SYS Test”
      oMail.Send        
      set oMail = nothing
      WScript.Echo “Message was sent.”        

Simple enough, right?!?! For further information about this object you can check out the following Google search.

As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files  from my final tests for you. You’ll find the code and other files available for download from my website’s (www.websystemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!


May 21, 2008  6:49 PM

VBScript Statements: Explanation of If… Then … Endif



Posted by: Jerry Lees
else, If Then, IF Then Else, Logical File, Then, VBScript, VBScript Statements, vbscriptstatements

The If/Then/Else statement in VBScript is very similar to the Select Case statement, except it only normally allows for 2 possibilities in your condition.  Basically, the If/Then statement says– It is or it isn’t.

It very useful for testing a specific value to see if it is equal to, greater than, or less than something else and doing something based on the outcome of the condition. The statement always evaluates the expression based of if the condition is true. For example, this code would always run, because the condition would always be true:

x=1
if x=1 then
     ‘do something
Endif

While this code would never run:

x=1
if x>1 then
‘do something
endif

The else option is a useful tool as well, it applies to all other conditions should the if test expression(s) not be true. This allows for code to run under only certain situations, depending on the value of the expression:

x=1
if x=1 then
‘do something
else
‘do something else for all other possibilities

endif

Optionally, there is a elseif component of the statement that can include another condition. However, if you start to need more than more than one of these a select/case statement is likely a better option. Consider this example:


if x=1 then
‘do something
elseif x=0
‘ do something else
elseif x=2
‘ do another something else
elseif x=3
‘ do something completely different
else
‘do something else for all other possibilities
endif

In this last case, the important thing to remember is the only part of the code that runs is the where the expression best fits and only ONE section is executed.


May 14, 2008  8:58 PM

Getting disk usage data with the filesystem object and the Excel.Application object in VBScript



Posted by: Jerry Lees
Disk usage, Documentation, Excel.Application, File System Object, FSO, Functions, Scripting.FileSystemObject, system trending, VBScript

In my previous posting, entitled Working with the Excel.Application object in VBScript to create Excel Spreadsheets, we worked with excel in VBScript to create a excel spreadsheet. The spreadsheet wasn’t going to win any awards for complexity or usefulness, but none the less it was a spreadsheet– and more importantly it was generated by a script! I also promised to bring you a script to help you chart disk space usage in my next posting in the series. This posting is the fulfilment of that promise! Also, please note that you will need Excel installed where the script runs for this script to operate correctly– but it does not need to be installed on the system you are pulling disk free space information from since I used the WMI object instead of the filesystem object.

The script below uses WMI’s Win32_LogicalDisk class to grab the drives in the target system, specifically the drive you specify through the use of a where clause in the SQL statement that pulls back the WMI data. (Through the “where deviceid like” section of the SQL statement in the code)

 Also, I didn’t put a lot of effort into the code where the for/next loop is that loops through getting and saving the free space because I didn’t want to create a lot of extra complexity and wanted to create a script that would run through to completion pretty quickly. Currently, the script takes 10 minutes to complete. To test the script while it’s running, just create files in a folder and delete them a number of times. I created a 10Mb and 20Mb file and made a series of copy/pastes during execution– with a smattering of deletes in the mix.

For further customization, you can look at the code from the posting I wrote a while back Reporting CPU usage by saving it to a file with the VBScript filesystem object to get a good feel for how to modify the loop to get what you want. In essence, change the number 2 in “Wscript.Sleep 2″ to a lager number to get a bigger gap between samples and change the 300 in the “For x = 1 to 300″ line to a larger number to get a longer sample period.

Here is the code:
Dim Freespace, CurrentRow, ServerName
Const xlSaveChanges = 1

‘the first row in excel is 1 (not 0)
CurrentRow = 1
ServerName=”.”

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = False
objExcel.Workbooks.Add
For x = 1 to 300 ‘ change 300 to increase your sample duration
     objExcel.Cells(CurrentRow, 1).Value = GetFreeSpace(“C:”,ServerName)
     CurrentRow = CurrentRow + 1
     Wscript.sleep 2 ‘change this to spread out your samples
next

objExcel.ActiveWorkbook.SaveAs (“C:\excelvbscript.xls”)
objExcel.Quit

Function GetFreeSpace(Drive,strComputer)
     Set objWMIService = GetObject(“winmgmts:{impersonationLevel=impersonate}!\\”_
         & strComputer & “\root\cimv2″)
     Set colDisks = objWMIService.ExecQuery (“Select * from Win32_LogicalDisk where deviceid like “_
         &chr(34) & Drive & chr(34))
     For Each objDisk in colDisks
         GetFreeSpace = (objDisk.freespace/1024)/1024 ‘ get MB free
     Next
End Function

As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files  from my final tests for you. You’ll find the code and other files available for download from my website’s (www.websystemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!


May 9, 2008  5:54 PM

VBScript Statements: Explanation of Select … Case



Posted by: Jerry Lees
Functions, VBScript, VBScript Statements, vbscriptstatements

The Select … Case VBScript statement is a very powerful way to easily preform specific actions based on a comparison of a variable to a series of cases you specify, plus it allows for the fact that NONE of the cases apply with an optional case else condition. The Select Case statement is often a better solution then a if…Then…Else if when you have more than 2 conditions that could apply.

Consider the following code snippet to check if a number is positive or negative:

Function TestNumber(Number)

Select case Number
Case Number > 0
TestNumber = “Positive”
Case Number < 0
TestNumber = “Negative”
Case Else
TestNumber = “Zero”
End Select


May 9, 2008  4:28 PM

Working with the Excel.Application object in VBScript to create Excel Spreadsheets



Posted by: Jerry Lees
Excel.Application, VBScript

In my previous two postings we discussed the Microsoft Word.Application object (here and here) to use as a logging mechanism and as a documentation mechanism for our scripts we’re writing.

But what about if you wanted to log numbers, like in the performance counter posts I made a few weeks back? I’ve also seen several questions about how to do use excel in VBScript. Well, it’s super easy, once you get the object understood. The next few postings I’ll focus on this great way to save data in a format that is easily shareable and can easily be made into pretty pie charts and such for the boss.

First off I’ll give you a simple example that basically creates the object (not visible), adds a new workbook to it, adds a string to cell 1,1, and finally saves it before it quits. Below is the example code for this scriptlet:

Const xlSaveChanges = 1

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = False
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = “Test value”
objExcel.ActiveWorkbook.SaveAs (“C:\excelvbscript.xls”)
objExcel.Quit

I’ll dig deeper right after I go back and explain the Select Case statement I used in a previous posting– which I neglected to mention previously or explain in the posting. (Bad, Jerry, Bad Bad.) However, after that I promise to come back with a more specific example of Excel.Application, but this should give you a little bit to play with in the mean time.

As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files  from my final tests for you. You’ll find the code and other files available for download from my website’s (www.websystemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!


May 2, 2008  4:15 PM

Document group policy objects in Microsoft Word using VBScript



Posted by: Jerry Lees
DataManagement, Documentation, Functions, Microsoft.XMLDOM, VBA, VBScript, Word.application, XML

OK, here is the posting I hinted at in my previous posting this week entitled, Using VBScript to create Word documents automatically with the Word.Application Object. In that posting I mentioned a project where I had to create a ton of documentation… but I didn’t say what it was that I had to document. Yeah, you guessed it from this article’s title… Group Policy Settings! I had to document settings in a series of policies I was proposing we implement at my employer. Here is the scenario:

I was asked “Can you document all the settings you are proposing in the new policies for the other members of IT? Also, Can you document the name of the setting, what you want it to be and what options are available to change it to? Oh, and what each value means when you set it?…. Do you think you’ll have time to also let us know for each setting what the supported or intended OS version is for the setting? I need it by Friday.”

Now, much like you might do, I said “I’ll try.” and mumbled under my breath later “If you bring me a pound of Lead I’ll turn it to Gold too!”… None the less, after my initial pity party, I began looking at the Information available to me in the Group Policy Management Console (Available here if you don’t have it already.), much to my surprise I found almost everything I needed when I looked at the individual settings… it just wasn’t in a format I could show to people or they could read easily. So I set out to try and copy and paste the information into word… for 20 seconds… and realized this is way to much work! And what do I always say?????? Be Lazy!

Now I looked at the options available to me on exporting the file and CSV was one of them, but unfortunately all the relationships were lost and I couldn’t make heads or tails of how to cobble it back together in a meaningful way after the export. Then I noticed XML and remembered my last post on Microsoft.XMLDOM (Using XML in VBScript via Microsoft.XMLDOM to work with data feeds) and thought, I really need to get back to that… so here we are!

First, Just like my last post– you’ll need Microsoft Word installed where you run this script for it to operate. Then you’ll need to use The Group Policy Management console to export the policy settings to XML. (hover over the icons along the top, it’s the one with the icon that looks like a page of paper with an arrow pointing right, one of the save as options is XML.) Save this file into the location where the script is located (I recommend a descriptive name, because it will be the heading for your document.) and with a single edit of the script you will have a word doc on the root of your C: drive with the same name— only documented in Word!

The script basically opens and reads in the XML Document and creates a Word.Application object to create a Microsoft Word Document at the root of the C: Drive, then writes formatted text to the document to make the data in the XML readable. But, enough of a introduction! On to the script! Here is the script:

set xmlDoc=CreateObject(“Microsoft.XMLDOM”)
Set objWord = CreateObject(“Word.Application”)
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection

‘This is the actual name of the XML document minus the path and the “.XML” extension, it becomes the word doc header
xmlfile = “Locked Down Desktop Policy”

objSelection.Font.Name = “Arial”
objSelection.Font.Size = “18″
objSelection.Font.Bold = True
objSelection.TypeText xmlfile & VbCrLf

objSelection.Font.Bold = False
objSelection.Font.Size = “10″

xmlDoc.async=”false”
xmlDoc.load(xmlfile &”.xml”)
for each x in xmlDoc.documentElement.childNodes
If x.nodename = “Computer” or x.nodename = “User” Then
For Each y In x.childnodes
if y.Nodename = “ExtensionData” then
For Each z In y.childnodes
If z.Nodename = “Extension” Then
For Each setting In z.childnodes
objSelection.TypeText “______________________________________” & vbCr
DocumentPolicy(Setting)
Next
End if
Next
End if
Next
End If
Next
objDoc.SaveAs(“C:\” & xmlfile & “.doc”)
objWord.Quit

Function DocumentPolicy(Setting)
‘this function basically cleans up the headers of the word document, so they are more human readable
Select Case setting.nodename
Case “q1:Policy”
replacestr = “q1:”
Case “q1:DropDownList”
replacestr = “q1:”
Case “q1:Name”
replacestr = “q1:”
Case “q1:Value”
replacestr = “q1:”
Case “q1:State”
replacestr = “q1:”
Case “q2:Audit”
replacestr = “q2:”
Case “q2:SecurityOptions”
replacestr = “q2:”
Case “q2:EventLog”
replacestr = “q2:”
Case “q2:RestrictedGroups”
replacestr = “q2:”
Case “q2:File”
replacestr = “q2:”
Case “q2:Display”
replacestr = “q2:”
Case “q3:General”
replacestr = “q3:”
Case “q3:HashRule”
replacestr = “q3:”
Case “q3:PathRule”
replacestr = “q3:”
Case “q3:InternetZoneRule”
replacestr = “q3:”
Case “q4:AutoEnrollmentSettings”
replacestr = “q4:”
Case “q4:AutoEnrollmentSettings”
replacestr = “q4:”
Case “q4:RootCertificateSettings”
replacestr = “q4:”
Case “q4:EFSSettings”
replacestr = “q4:”
Case “q5:PreferenceMode”
replacestr = “q5:”
Case “q2:PreferenceMode”
replacestr = “q2:”
Case “q2:ProxySettings”
replacestr = “q2:”
Case “q2:UseSameProxy:”
replacestr = “q2:”
Case “q2:HTTP:”
replacestr = “q2:”
Case “q2:NoProxyIntranet:”
replacestr = “q2:”
End Select
objSelection.Font.Bold = True
objSelection.TypeText VbCrLf & replace(setting.nodename, replacestr,”") & VbCrLf
objSelection.Font.Bold = False
For Each Value In Setting.Childnodes
NodeName = replace(Value.nodename,replacestr,”")
If NodeName = “Explain” Then
objSelection.Font.Bold = True
objSelection.TypeText Nodename & “: ” & vbcrlf
objSelection.Font.Bold = False
objSelection.TypeText vbTab & replace(value.text,”\n\n”, VbCrLf & VbCrLf & vbTab )& vbcrlf
Else
objSelection.Font.Bold = True
objSelection.TypeText Nodename & “: ”
objSelection.Font.Bold = False
objSelection.TypeText vbtab & value.text & vbcrlf
End if
Next
If isnull(Setting.childnodes) Then
For Each node In Setting.childnodes
DocumentPolicy(node)
next
End if
objSelection.TypeText VbCrLf
End FunctionNow, when you run this script agains your export there may be some XML tags I didn’t notice because a setting you set is one I didn’t set. Any time you see them in the document you can add a new Case statement in the select case followed by setting the replacestr to the string you want to replace with a null. The lines I’m talking about look similar to this:

Case “q2:xxxxxxxx”
replacestr = “q2:”

As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files from my final tests for you. You’ll find the code and other files available for download from my website’s (www.websystemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!

<!–a href=”http://itknowledgeexchange.techtarget.com/vbscript-systems-administrator/pinging-a-remote-computer-from-another-remote-computer-using-the-wmi-win32_pingstatus-class/trackback/” title=”Trackback URL”>Trackback URL–>


April 30, 2008  2:20 PM

Using VBScript to create Word documents automatically with the Word Application Object



Posted by: Jerry Lees
DataManagement, Developer documentation, Documentation, VBA, VBScript, Word.application

I recently had a project that required me to create a ton of documentation from data that was already stored in files elsewhere on the network– just not in the format that was required and readable by every day people within IT. (Basically, non-administrators) This project would have required a TON of copy and paste operations or a whole lot MORE reworking of a document, plus I was on a tight deadline. So I decided to use a little VBA (Visual Basic for Applications) knowledge I had picked up in the past to work with the Microsoft Word Application interface and do the job, with only minor reworking after the fact to really tidy up the document.

First, let me clarify. Most of my scripts I use here require nothing more than the items I mentioned in my first posting (<a title=”Permanent Link to Getting Started Writing VBScript to Administer a Windows 2000 or 2003 Network” rel=”bookmark” href=”http://itknowledgeexchange.techtarget.com/vbscript-systems-administrator/getting-started-writing-vbscript-to-administer-a-windows-2000-and-2003-network/”>Getting Started Writing VBScript to Administer a Windows 2000 or 2003 Network</a>) on this blog… a VBScript editor. <em>(Notepad will do… but you’ll likely quickly want something a bit more powerful before long.) </em>The scripts I discuss in this series are <strong><em>one exception</em></strong>, for these scripts we discuss in the next few articles we will need to make sure you have Microsoft Word installed where ever you run the script. However, for the purposes of this type of work your workstation should work fine and will likely already have Microsoft word installed! Bonus!

The “magic” component we need to call and create an object from is called <em><strong>Word.Application</strong>, </em>as you’ll see below, it’s pretty easy to create a word document from VBScript and then have auto generating documentation!

The next sections of code will show you how to do various tasks with in word. Many sections are not scripts on themselves, but in fact snippets that could be placed into the first script prior to the remark for testing. The first snippet, creates a word document and saves it to the root of your C: drive. When you run this it will run, but create a blank word document– much like if you right clicked somewhere and selected <em>New/Microsoft Word Document.</em> Here are some common tasks in word I use:

<strong>Create a new (empty) Word Document and save it to c:\testdoc.doc</strong>
<span style=”color: #15326b”>Set objWord = CreateObject(“Word.Application”)
‘note: if you don’t want word popping up and displaying as this is built– set this next line to False</span><span style=”color: #15326b”>
objWord.Visible = True
</span><span style=”color: #15326b”>Set objDoc = objWord.Documents.Add()
‘save the Word Document
objDoc.SaveAs(“C:\testdoc.doc”)
</span>

<span style=”color: #15326b”><span style=”color: #000000″><strong>Setting the Font type
</strong><span style=”color: #15326b”>objSelection.Font.Name = “Arial”
</span></span></span>

<span style=”color: #15326b”><span style=”color: #000000″><strong>Setting the Font Size
</strong><span style=”color: #15326b”>objSelection.Font.Size = “18″
</span></span></span>

<span style=”color: #15326b”><span style=”color: #000000″><strong>Adding Text
</strong><span style=”color: #15326b”>objSelection.TypeText “Add your text here”
objSelection.TypeParagraph()</span></span></span>

<span style=”color: #15326b”><strong><span style=”color: #000000″>Setting the typeface to bold
</span></strong></span><span style=”color: #15326b”>objSelection.Font.Bold = True</span>

<span style=”color: #15326b”><span style=”color: #000000″><strong>Closing the word document (generally done after saving)
</strong><span style=”color: #15326b”>objWord.Quit</span></span></span>

<span><span style=”color: #000000″>These should get you started playing with VBScript to create word documents. For more information on working with Microsoft word in VBScript look <a href=”http://msdn.microsoft.com/en-us/library/78whx7s6(VS.80).aspx” target=”_blank”>here</a> at Microsoft’s site, while it’s not related exactly to VBScript– it should give you enough information about the interface to get started.</span></span>

<span> Next time, I’ll share a script to document settings in Group policy with a word document through a combination of using <em>Word.Application</em> and <em>Microsoft.XMLDOM.</em></span>

<span style=”color: #0000ff”><span style=”color: #000000″>As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files  from my final tests for you. You’ll find the code and other files available for download from my website’s <a href=”http://www.websystemsadministration.com/”>www.websystemsadministration.com</a> File Depot under the <span style=”color: #0000ff”><em>ITKE Blog Scripts</em></span> category.<span style=”color: #0000ff”> </span><span style=”color: #0000ff”><span style=”color: #0000ff”><span style=”color: #000000″>Enjoy and happy scripting!</span></span></span></span></span>

&lt;!–a href=”http://itknowledgeexchange.techtarget.com/vbscript-systems-administrator/pinging-a-remote-computer-from-another-remote-computer-using-the-wmi-win32_pingstatus-class/trackback/” title=”Trackback URL”&gt;Trackback URL</a>–&gt;


April 25, 2008  3:19 PM

How VBScript variable types are represented as binary numbers



Posted by: Jerry Lees
Variable Types, VBScript

In a previous posting,  Converting variable types in VBScript from one type to another type, I discussed very briefly binary numbers and promised to write another entry with more detail. If you recall we we were discussing the size a number takes up to represent it in binary form.

Everyone is comfortable with the numbers 1 and 0, even in binary. And if you’re in IT, you’re probably even comfortable with binary 10, which isn’t Ten– it’s two. Normally we count in tens (with ten digits in each place), but computers count in twos (with only two digits in each place), which is base-2 instead of base-10 like we are used to counting.

 Consider this base-10 table with the column labels at the top and the number of each column below.

10000

1000

100

10

0

9

8

1

5

3

This might look a bit abstract the way it’s presented. But if you think back to elementary school the columns in a number represent the 10′s, 100′s, etc for the number. But the number is 98,153 or (90,000 +8000+ 100+50+3)

Now consider the following binary number, again with the column label at the top and the number of each column at the bottom:

16

8

4

2

1

1

0

1

1

1

This looks a bit more cryptic, but it’s actually simpler– we’ve just been trained to think in decimal. To do the first one we actually had to do multiplication in our head. It wasn’t (90,000 +8000+ 100+50+3)— it was actually(9* 10,000) + (8* 1000) + (1 * 100) + (5 * 10) + (3* 1)… That’s rather confusing when you spread it out, isn’t it? but with only 1′s and 0′s we don’t need to do multiplication. (Because 1 * anything is the number, and 0 times anything is 0)

So this is simply 16+4+2+1, or 23! It’s just a matter of getting to start thinking in powers of 2!

Now, Since I’ve shared with you previously that a double is larger than an integer, with respect to space, we’ll continue with that example. If you refer here you’ll notice that an integer is 2 bytes in Size where a double is 8 bytes in size. What’s 400% more space among friends, right? Quickly doubles can get out of control is you’re using bunches of them, or placing them in a Database one item at a time… This is sometimes where developers eat up all our space on the servers, in addition to the backup of the backups in the backups folder that’s stored in a Do not delete – Save this stuff folder. ;-)

The other thing that can be VERY bad! is using a variable type that is to small. Did you notice in the previous link that in Integer represents -32,768 to 32,768? What happens if you add 1 to 32,768? The answer for us is, of course, 32,769! The problem is computers can’t represent that number in an integer so you get a overflow and the number represented actually looks like -32768 to the computer!

 The moral of the story is to always try to use the right type of variable if a variant doesn’t work for you, and if you plan on using anything other than VBScript, VB, VB.NET, or VBA to create an admin tool– get accustomed to the different variable types.

Enjoy!

Extra credit: 10 in base-10 numbering is Ten, 10 in base-2 numbering is 2… what is 10 in Base-16 numbering and what is it commonly called?


April 25, 2008  1:26 AM

Reporting CPU usage by saving it to a file with the VBScript filesystem object



Posted by: Jerry Lees
Error control, File System Object, Functions, Scripting.FileSystemObject, VBScript

Last time, in my entry entitled VBScript to check CPU and Processor performance counter statistics using WMI, I asked you to modify the code I gave to include the access method type in the function. If you haven’t read the article, take a second to go back and look it over and digest the code because this article is identical to that one, except some added features.

Again I got a rather quick response to the extra credit item that I placed at the end if the article. Basically, you just need to change the code in three places to accomplish this task. We could then use this code in our previous example to create a CSV file for trending and graphing of CPU usage on a server! Here is an example of the code to do this task.

I encourage you to play around with the various methods of file access both with an existing file and a new file that doesn’t exist.

Below is a working example of the code:

Option Explicit
On Error Goto 0
Dim strSrv, strQuery,Errors
Dim Counter, Timeframe, Delay

Delay = 1000 ’1000 Milliseconds to a second
Timeframe = 300 ‘seconds (5 minutes)


strSrv = “.”
For Counter = 1 To Timeframe
 Errors = WriteTextFile (“c:\cpu-usagelog.csv”, GetFormattedCPU (strSrv), ForAppending)
 WScript.Sleep Delay
NextConst ForReading = 1, ForWriting = 2, ForAppending = 8Function GetFormattedCPU(strSrv)
    Dim objWMIService, Item, Proc, start
   
    start = Now()
  
    strQuery = “select * from Win32_PerfFormattedData_PerfOS_Processor”
    Set objWMIService = GetObject(“winmgmts:\\” & StrSrv & “\root\cimv2″)
    Set Item = objWMIService.ExecQuery(strQuery,,48)
    WScript.Echo strQuery
    For Each Proc In Item
       GetFormattedCPU = GetFormattedCPU & Proc.PercentProcessorTime & “, ”
       wscript.echo “Processor ” & Proc.Name & ” = ” & Proc.PercentProcessorTime
    Next
   
    GetFormattedCPU = GetFormattedCPU & start  
 
End Function
Function WriteTextFile (OutputFile, Data, AccessType)
    Dim wrtlog, fso
 
    On Error Resume Next
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set wrtlog = fso.OpenTextFile (OutputFile, AccessType, true)
 If Err.Number <> 0 Then
  Set wrtlog = fso.OpenTextFile (OutputFile, ForWriting, true)
 End If
    wrtlog.WriteLine(Data)
    WriteTextFile = Err.Number ‘You can use Err.Description here to debug.
    On Error Goto 0
End Function
You now have all the pieces for a fully functional performance logging script, so you’re well on your way to being able to troubleshoot problems deep in the night without getting up and turning on Perfmon. The creates a comma separated value file called cpu-usagelog.csv at the root of the C: drive while the output looks similar to the last time, except it is repeated and I put a time/date stamp in the code by using a new command, now().Also, pay attention to the error handling around the line that writes the data to the file, you’ll likely not see an error related to the file not existing and you using the append access methods, because I’ve checked for it while opening a file. Lastly, notice the for next loop around the function call to call it Timeframe times (Timeframe is multiplied time the sleep Delay to get how long it will capture data). Also notice another new command: Wscript.Sleep, which basically pauses the execution for the number of milliseconds you specify in Delay.

As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files  from my final tests for you. You’ll find the code and other files available for download from my website’s (www.websystemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!

Extra credit: Try and modify this script to monitor something else that is useful to you. Let us all know how you’ve used it.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: