Hey, Scripting Guy! How Can I Read Computer Names From a Text File and Then Verify Whether or Not Those Computers Still Exist?

ScriptingGuy1

Hey, Scripting Guy! How can I read a list of computer names from a text file, and then check to see if those computers still exist in Active Directory?

— CL

You know, that’s a good question, CL, we – hold on a second. Some of you out there look so sad; why the long faces? After all, the 2008 Winter Scripting Games start in less than two weeks (February 15^th). Shouldn’t you be happy and excited?

Ah, we see. As it turns out, many of you are Perl scripters, and you’re feeling a little left out. Granted, Perl has been added to the Scripting Games, but there’s no doubt that many of the prizes we’re giving away are aimed at Windows PowerShell and VBScript users. Dr. Scripto bobblehead dolls and copies of Windows Vista are great (and they are, by the way), but why don’t Perl scripters have a chance to win some Perl-related software? Does mean that – gasp! – the Scripting Guys are prejudiced against Perl users?

No, of course not; it just means that we haven’t had a chance yet to announce a major addition to the Scripting Games prize list. As it turns out, the good people at ActiveState – people who make absolutely dynamite software, including the version of Perl that we’ll use to test Scripting Games scripts – have generously donated a pair of prize packages consisting of:

We thought that would bring a smile to your face. These prizes would bring a smile to anyone’s face.

What’s that? What about that evil villain in the old Batman comic book who doesn’t even have a face? Is he smiling, too? You know, we’ll have to get back you on that. Keep in mind, however, that the Scripting Guys don’t cater to evil villains. If we did, we’d do more articles on Linux scripting.

Hey, we’re just kidding.

At any rate, the Scripting Games has picked up some really great prizes from a first-rate company; ActiveState has a well-earned reputation for producing outstanding software. Now, as for today’s question, we – what’s that? How did we know that Perl people were feeling down? For that matter, how did we know that they all had long faces? Does this mean that each copy of Windows contains some secret Microsoft software the enables us to monitor everything you do and everything you feel?

Well, of course it does; everyone knows that Microsoft – uh, no, of course not. We aren’t secretly monitoring everything you do; that’s just crazy.

Although TW in Omaha, you really should see a doctor about your cholesterol level. You don’t want to take chances with your health, you know.

Now, where were we? Oh, that’s right: today’s question. How can you read a list of computer names from a text file and then check to see if those computers still exist in Active Directory? Well, here’s one way:

On Error Resume NextConst ADS_SCOPE_SUBTREE = 2Const ForReading = 1Set objConnection = CreateObject("ADODB.Connection")Set objCommand = CreateObject("ADODB.Command")objConnection.Provider = "ADsDSOObject"objConnection.Open "Active Directory Provider"Set objCommand.ActiveConnection = objConnectionobjCommand.Properties("Page Size") = 1000objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE Set objFSO = CreateObject("Scripting.FileSystemObject")Set objFile = objFSO.OpenTextFile("C:\Scripts\Computers.txt")Do Until objFile.AtEndOfStream    strComputer = objFile.ReadLine    objCommand.CommandText = "SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' " & _        "WHERE objectCategory='computer' AND Name = '" & strComputer & "'"    Set objRecordset = objCommand.Execute    If objRecordset.RecordCount = 0 Then        Wscript.Echo strComputer & " does not exist."    Else        Wscript.Echo strComputer & " still exists."    End IfLoop

So how exactly does this script work? Well, to begin with, we define a pair of constants: ADS_SCOPE_SUBTREE, which we’ll use to tell the script to search the entire domain; and ForReading, which we’ll need when we open our text file for reading. We then use this block of code to create instances of the ADODB.Connection and the ADODB.Command objects, and prepare our script to do a search of Active Directory:

Set objConnection = CreateObject("ADODB.Connection")Set objCommand = CreateObject("ADODB.Command")objConnection.Provider = "ADsDSOObject"objConnection.Open "Active Directory Provider"Set objCommand.ActiveConnection = objConnectionobjCommand.Properties("Page Size") = 1000objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Aren’t we going to explain what all this code is for? Sorry, but the answer is no; that’s far more than we can explain in the daily Hey, Scripting Guy! column. But hey, don’t look so down in the dumps; if you need more background information about Active Directory search scripts we have a two-part Tales From the Script series designed to give you that very information.

At this point we’re ready to open our text file. To do that, we create an instance of the Scripting.FileSystemObject, then use this line of code to open the file C:\Scripts\Computers.txt for reading:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Computers.txt")

Once the file is open we set up a Do loop designed to run until the file’s AtEndOfStream property is True. (In other words, we’re going to read the file, line-by-line, until there’s nothing left to read.) Inside that loop, we read the first line in the text file and assign it to a variable named strComputer:

strComputer = objFile.ReadLine

Some of you look like – um, some of you might be wondering why we’re reading a single line from the text file. That’s because we’re assuming the text file looks like this, with each line representing the name of a computer:

atl-fs-001atl-fs-002atl-fs-003atl-fs-004atl-fs-005

Do youhave to structure your text file in this manner? No. But, trust us: it will make your life much easier if you do. Reading the file line-by-line is way easier than, say, having to parse a comma-separated values or tab-separated values file.

That brings us to this line of code:

objCommand.CommandText = "SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' " & _        "WHERE objectCategory='computer' AND Name = '" & strComputer & "'"

What we’re doing here is configuring our Active Directory search query: we’re simply asking to get back a recordset consisting of all the objects in the domain fabrikam.com that have an objectCategory equal to computer and a Name equal to the name we just read in from the text file. Because computer names must be unique within a domain, the recordset we get back will contain one of two possible values: 0 if no such computer can be found, or 1 if the computer name can be found.

After configuring the query we then call the Execute method to execute the query and return the recordset:

Set objRecordset = objCommand.Execute

The rest is easy: we simply check the number of items in the recordset (something we can do using the RecordCount property), and then echo back the appropriate message:

If objRecordset.RecordCount = 0 Then    Wscript.Echo strComputer & " does not exist."Else    Wscript.Echo strComputer & " still exists."End If

And then it’s back to the top of the loop, where we repeat the process with the next line in the text file.

When all is said and done, we should get back a report similar to this:

atl-fs-001 does not exist.atl-fs-002 still exists.atl-fs-003 still exists.atl-fs-004 does not exist.atl-fs-005 still exists.

That should do it, CL; we hope that helps. And we hope all of you will join us for the 2008 Winter Scripting Games, February 15^th through March 3^rd. Did we mention that ActiveState has donated two really cool prize packages to the Games? Well, it’s worth mentioning again. The Games are fun, the Games are challenging, and now the Games give you even more of a chance to win some really great prizes. What more could you ask for?

We’ll see you all on February 15^th. And, needless to say, when we say “We’ll see you,” well, we mean it.

ScriptingGuy1

Follow