DataManagement

Easily document group policy objects in Microsoft Word using VBScript with the Microsoft.XMLDOM and Word.Application objects

OK, here is the posting I hinted at in  my previous posting this week entitiled, 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. Yeap, 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 pitty 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 Function

Now, 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 XML files and Generated Word Documents for you. You’ll find the code available for download from my website’s (www.webstemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!

Using Vbscript to create Word documents automatically with the Word.Application Object

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 tiddy 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 (Getting Started Writing VBScript to Administer a Windows 2000 or 2003 Network) on this blog… a Vbscript editor. (Notepad will do… but you’ll likely quickly want something a bit more powerful before long.) The scripts I discuss in this series are one exception, 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 Word.application, as you’ll see below, it’s pretty easy to create a word document from vbscript and then have autogenerating 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 infact 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 New/Microsoftw Word Document. Here are some common tasks in word I use:

Create a new (empty) Word Document and save it to c:\testdoc.doc
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
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
’save the Word Document
objDoc.SaveAs(”C:\testdoc.doc”)

Setting the Font type
objSelection.Font.Name = “Arial”

Setting the Font Size
objSelection.Font.Size = “18″

Adding Text
objSelection.TypeText “Add your text here”
objSelection.TypeParagraph()

Setting the typeface to bold
objSelection.Font.Bold = True

Closing the word document (generally done after saving)
objWord.Quit

These should get you started playing with Vbscript to create word documents. For more information on working with microsoft word in vbscript look here at Microsoft’s site, while it’s not related exactly to vbscript– it should give you enough information about the interface to get started.

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

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 for you. From here on out I will make the code available for download from my website’s (www.webstemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!

Explanation: Using Microsoft.XMLDOM to work with data feeds in VBScript

You may recall we recently worked with a cool little script that itself didn’t have anything to do with systems administration, the 360voice API script in a previous post, but the concepts can be used in many different areas since XML data feeds are ever becoming the standard way to communicate related data between applications and systems.

In this script, I used many topics we’ve previously discussed and added a powerful object, Microsoft.XMLDOM. Essentially, DOM stands for Document Object Model– which is a way for the document to describe and relate the data it contains. Here is an example XML document header for “mydata”

<?xml version=”1.0″?>
<!DOCTYPE Mydata SYSTEM “mydata.dtd”>

This essentially in the first line tells us the file is an XML document, Version 1.0 to be specific, and in the second line tells us the Document Type Definition file that we can have the parser verify the XML file against. DTD’s and XML Schemas are beyond the scope of what we’re discussing but can be very useful becasue they define the rules by which a XML document must conform to be considered valid– however they are not required to be in an XML file.

After these lines the XML file has nodes and child nodes in it. This is where the bulk of our work was done going through the nodes and pulling out child nodes and values. In this illustration we’ll use the following URL to my XML Game data at 360voice.com (note the data will change over time, but the datastructure should stay pretty much the same)

As of this moment, the file looks like:

<?xml version=”1.0″ encoding=”ISO-8859-1″ ?>

<api>
<info>
<version>1.2</version>
<gamertag>jlees</gamertag>
</info>
<gamerscore>
<score>
<value>23740</value>
<date>3/20/2008 4:42:02 AM</date>
</score>
<score>
<value>23740</value>
<date>3/19/2008 4:45:18 AM</date>
</score>
</gamerscore>
</api>

Notice how the main Node (The “document” Node) is titled api? The XML Parser automatically read that entire node in at the load of the document. After that we have nodes called info and gamerscore that both contain data and/or child nodes.

the info tag contains the version and gamertag values, you’ll notice I used the gamertag value in the script to pull the name of the gamertag with this line of the script code after checking to see if the currently looped through tag had the name “info”:

WScript.Echo GetTagValue(x,”gamertag”)

See how that works? This example was pretty simple since it was just off the root. The score value and date were a bit harder to get at, but not to bad.

They are contained in a child node called “score”, but they are a child of gamerscore– so you’ll see that I check to see if the tag is “gamerscore” and then loop through the score tag separately looking for each instance of the node called score and pulling the tag value and placing it into an array called gamerscore.

All pretty straightforward. There are certainly better ways to deal with XML than writing code tightly around the XML itself, but this illustrates the use of Microsoft.XMLDOM and explains the heirarchial nature of XML nicely the way it’s been coded.

See if you can play around with the code and make it better! Thanks again to the folks at 360voice for providing the API and it’s documentation!

Happy coding and have fun!

Explanation: Working with Subroutines and Functions in VBScript

I just realized in my haste to get out a piece of code celebrating the XBOX 360 contest, I negected to give a proper answer for our discussion on subroutines and functions in a vbscript.

In this installment, we’ll explain how to build a function and a subroutine a bit better. First, remember functions and subroutines are basically smaller pieces of your script — they essentially are the work horse of your script.

They both get passed information when they are called and preform their work according to what the information they have been given. These values passed can literally be anything you need to make the subroutine or function work but are generally only what the function needs to preform it’s work. You can reference the whole blog entry here.

Basically, you can build a subroutine like so:

Sub MySub(myvariable)

‘… your code here

End Sub

You would call this subroutine like so:

MySub XVariable
-Or-
MySub 10
-Or-
MySub “This Value”

Notice that I didn’t use Parenthesis, you don’t when calling a subroutine. The value you pass the subroutine can be variable (XVariable), a literal number (10), a string (”This Value”), or any other specific item you need to pass into the subroutine– even an array, providing you’re expecting the variable type!

Functions are the exact same as subroutines except in the label when you create them and in that they can return a value.

To create a function you:

Function MyFunction

‘… your code here

MyFunction = X

End Function

To call a function you use parenthesis, unlike a subroutine, like so:

X = MyFunction (XVariable)
-Or-
X = MyFunction (10)
-Or-
X = MyFunction (”This Value”)

Notice the line in red? This is the line of the function that does the magic of returning a value! It essentially works on the premise that the function name is a variable in the function that is always there. at the end of the function’s work the value is passed back to the calling routine as the return value– and it does this automatically.

Also notice how I changed the line to call a function? I placed a variable, X, in front of the function and set X equal the return of the function. You can also use a function in comparisons like so:

If Myfunction(10) = 1 then
‘your code here
end if

So, we’ve covered the creation and calling of a subroutine, the creation and calling of a function, how to assign a value to the return of a function, and how to use the return from the function.

From this point, you can use your imagination for when and where to use functions and subroutines!

Using XML in vbscript via Microsoft.XMLDOM to work with data feeds

In this installment I wanted to both celebrate and congratulate the winners of the ITKE Challenge that has been running over the last few months for an XBOX 360 ELITE system, among other prizes. I also wanted to write about XML use in vbscript as well so the challenge was how to accomplish both tasks…

Then I remembered a great community site, that I’ve been a member of for a little over a year or so, called 360voice. Basically, those guys have a truly awesome idea over there… I play games with my Xbox 360 and it blogs about the times we have—and I don’t have to do any work on my own. If you have a 360 and don’t have an account setup, go over and check it out, tell ‘em Jlees sent you. Heck, while you are at it watch my blog there and drop me a note.

At any rate, one of my challenges was to get everyone the same XML file, while not creating it myself because I wanted the script to be useful under certain circumstances. 360voice came through with their API Documentation! Think about it… now your scripting life has insite into your gaming life (and other’s as well) you could write a script to compare your achievements to your buddies, show the games you haven’t completed, and all sorts of other stuff. Why?? Because you can!

Consider this working code (that indecently pulls the XML directly from the website without saving it locally) that shows the last 10 days of gamerscore history for two gamertags in the output, in my case I chose mine, Jlees, and one of the owners of the site, changeagent, to do the comparison.. From this script alone you could graph your gamerscore and a buddies so you can compare and have irrefutable bragging rights about your gaming powers! (notice: that I put just about everything together we’ve talked about up to now in this one script, if your just joining us you might want to go back and brush up the last few weeks worth of blogs really quick. Also, I’ve noticed some cases where the sourcecode does not copy correctly, so I have uploaded the source to this article to my website here for you to download.)

Option Explicit

Dim GamerTag1,GamerTag2, GamerScore, Days, xmlDoc, x, y, Value, Count

GamerTag1 = “JLees” ‘change this to your gamertag
GamerTag2 = “ChangeAgent” ‘change this to your buddies gamer tag
‘ a quick note on the following line. It’s great the folks at www.360voice.com gave us this API
‘ While you likely can change this to something other than 10 below, I strongly encourage you To
‘ limit it to less than a month. The pulling of this data will no doubt have an impact on their servers
‘ and excessive pulls will likely get you blocked, the API removed, or restricted.
‘ Basically, be a good neighbor.
Days = 10 ‘ change this to the number of days to pull.

DisplayTagHistory(GamerTag1)
DisplayTagHistory(GamerTag2)

This line is the one where you would specify the path for the XML file, whether it is a local file or a file on the internet—like this rss feed to my blog. Also note that the DisplayTagHistory subroutine is highly focused to this XML feed and will likely need some work to get it to display another.

Enjoy!

Extra Credit: Can you modify the code to include the other owner of the 360voice site and compare his score with mine and changeagent’s? His Gamertag is Fatty Chubs.