Hey, Scripting Guy! How Can I Extract Miscellaneous Computer Names From a Text File?
Hey, Scripting Guy! I have a text file containing thousands of events. I need to read each line in that file and look for the name of the computer where an operation failed. The computer names can – and will – vary in length; however, the name will always begin in character position 37, and be followed by the word failed. Oh, and there’s one other complication: some of my lines have less than 40 characters, and there’s no need to try to find a computer name in lines that small. How can I extract the appropriate computer names from this file?
Hey, DA. You know, yesterday morning when the Scripting Guy who writes this column left for work, he drove to the top of his hill and was immediately met by a deputy sheriff and a huge, snaking line of cars.
Now, admittedly, it isn’t all that unusual for the Scripting Guy who writes this column to find an angry mob congregating outside his house. (He really should learn to stop making fun of soccer, NASCAR racing, and the University of Oregon.) This time, however, the mob didn’t appear to be particularly angry, and they weren’t the least bit interested in the Scripting Guy who writes this column. Instead, a gas station about six blocks away, in conjunction with a local radio station, was having a Great Gas Giveaway: they were giving away $40 worth of free gas to each of the first 200 customers who pulled into the gas station, and even though the promotion wouldn’t begin for another 30 minutes or so, the cars were already lined up for miles.
Or at least for a third of a mile, anyway.
The Scripting Guy who writes this column didn’t stick around to watch the fun, or to try and get the free gas. He was amazed, however, at the fortitude and determination of the people who were planning to stick around and get the free gas: after all, the cars at the front of the line had undoubtedly been there for an hour or two (at least), and the cars at the back of the line were a good hour or two (at least) from getting their turn at the pump. And all just to get $40 worth of gas.
Which, considering the price of gas these days, might be just enough fuel for them to get from one gas station to the next.
Unless, of course, you drive an SUV.
Anyway, it’s amazing the lengths that people will go to just to receive a miniscule reward. Some people will sit in line for hours just to get $40 worth of gas; others will write scripts that can pick computer names out of a text file, just to get a measly little paycheck and to listen to people tell them, “You know, we absolutely love what you’re doing. In fact, we love it so much we’d like you to stop doing it and do something completely different.”
Not that we know anyone like that, mind you. As it turns out, a …friend … of ours put together the following script, a script that can pick computer names out of a text file:
Const ForReading = 1 Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading) Do Until objFile.AtEndOfStream strLine = objFile.ReadLine intLength = Len(strLine) If intLength > 39 Then strSubString = Right(strLine, intLength - 36) arrSubString = Split(strSubString, " ") If arrSubString(1) = "failed" Then Wscript.Echo arrSubString(0) End If End If Loop objFile.Close
Before we explain how this script works let’s take a look at the kind of text file DA has to deal with (OK, we’re guessing this isn’t exactly like the text DA has to deal with, but it’s close enough):
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxatl-ws-001 failed to respond. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxatl-fileserver-002 failed to install software. xxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxredmond-dc-003 succeeded. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxlondon-printserver-001 failed to respond.
As you can see in lines 1, 2, and 5 we have 36 characters (as indicated by the 36 x’s) followed by the name of a computer (a name that can be of any length) followed by a blank space and the word failed. These are the only three lines in the file that we care about. Why don’t we care about line 3? That’s easy: it has less than 40 characters. And what about line 4? Well, line 4 meets the minimum length requirements, and it does have a computer name beginning at character position 37. However, that computer name isn’t followed by the word failed. That’s why we don’t care about line 4.
Note. OK, that’s a little harsh: the truth is, the Scripting Guys care about each and every line in each and every text file in the world. We just have no immediate use for line 4, as much as we might – and do – care about it.
OK, so how are we going to pick out the desired lines and, more important, extract the computer name? Well, to begin with, we define a constant named ForReading and set the value to 1; we’ll need to use this constant when we open our text file. After defining the constant we create an instance of the Scripting.FileSystemObject, then use the following line of code, and the OpenTextFile method, to open the file C:\Scripts\Test.txt for reading:
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)
Once the file is open we set up a Do Until loop designed to run until we’ve read each and every line in the file. (Or, if you’re a stickler for technical accuracy, until the file’s AtEndOfStream property is True.) Inside that loop we use the ReadLine method to read a line from the file and store that value in a variable named strLine:
strLine = objFile.ReadLine
That means that, the first time through the loop, strLine will be equal to this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxatl-ws-001 failed to respond.
What good does that do us? Well, to tell you the truth, not a whole lot. So let’s see what we can do about that.
To begin with, we need to verify that strLine contains at least 40 characters. We can easily determine the number of characters in strLine by using the Len function:
intLength = Len(strLine)
So does strLine contain at least 40 characters? Beats the heck out of us. But this line of code will tell us:
If intLength > 39 Then
So what happens if intLength is not greater than 39? Nothing (literally): in that case we simply go back to the top of the loop and try again with the next line in the text file. If the length of the string is greater than 39 we then run this line of code:
strSubString = Right(strLine, intLength - 36)
What are we doing here? Well, we know that the computer name begins at character position 37; therefore, we can make our scripting life much easier if we simply discard the first 36 characters. That’s what this line of code is intended to do. In this line we’re using the Right function to start at the end of the string and, moving backwards, take the next x number of characters. And what is x supposed to be equal to? As it turns out, x is equal to the total number of characters in the string minus 36. For example, if strLine contains 65 characters we’ll take 29 characters: 65 – 36. And what happens if we start at the end of the string and, working backwards, take the next 29 characters? That’s effectively going to chop off the first 36 characters in strLine, leaving us with this:
atl-ws-001 failed to respond.
Now we’re getting somewhere, eh? And guess what? We’ll get even farther after we execute this line of code:
arrSubString = Split(strSubString, " ")
What we’re doing here is using the Split function to split strLine into an array, creating a new array item each time we encounter a blank space (“ “). What will that give us? That will give us an array with the following elements:
So what’s next? Well, next we check to see if the second item in the array (the item with the index number 1) is equal to the word failed:
If arrSubString(1) = "failed" Then
If it’s not, then this is another line we aren’t concerned with; as you might recall, we’re looking for a computer name followed by the word failed. If the second item in the array doesn’t equal failed we simply go back to the top of the loop and try again with the next line in the file.
Now, what if the second item in our array is the world failed? Well, in that case, the first item in the array (index number 0) must be the name of a computer where something failed. How do we extract the computer name? That’s really easy; we simply echo back the value of the first item in the array:
And then, again, it’s back to the top of the loop, where we repeat the process with the next line in Test.txt.
When we’ve finished reading all the lines in the file we call the Close method and call it a day. In turn, we should see a report similar to this onscreen:
atl-ws-001 atl-fileserver-002 london-printserver-001
And yes, that is just exactly what we hoping to see. Thanks for asking!
We think that should do the trick, DA; let us know if you have any further questions. In the meantime, the Scripting Guy who writes this column is intrigued by the large number of people who were attracted to the Great Gas Giveaway. As a matter of fact, he’s so intrigued that [Editor’s Note: Please disregard everything in the article from this point on. The Scripting Guy who writes this column is delusional again.] he’s offering $100 of free gas to everyone who visits the Script Center during the month of June! Come visit the Script Center in June: it’s a great way to learn about system administration scripting, and an even better way to get free gas! See you all back here in June!
Note. Has the Scripting Guy who writes this column figured out where he’s going to get all that gas, and how he’s going to pay for it? Well, no, not exactly; as the Scripting Editor points out – two or three times a day – planning isn’t really his forte. But how much could that be, anyway? After all, each month the Script Center attracts about 450,000 unique visitors; at $100 per visitor, that comes out to about $45 million. Do the Scripting Guys really have enough money in their budget to buy $45 million worth of gas during the month of June? Well, you would think so, wouldn’t you? Nevertheless, he’ll have to double-check that. In the meantime, you might want to go easy on the gas, and maybe even take the bus or something. You know, just in case he’s mistaken about the budget.