Integrating the output of 2+ programs?

May 23, 2007 11:47 AM

I am trying to find a way to generate a CSV table based on output from two or more command line programs (DOS on Windows XP).

I do miscellaneous technical tasks for my university, and the task I am currently working on involves doing an inventory of our computers. I work for the enrollment department, not IT, and IT is extremely uncooperative and bumbling. So we have to do things like inventory all on our own, without access to any central servers, any control panels, etc.

So we're looking for a low-tech way to do inventory, nothing official or fancy but simply a way to automate making a spreadsheet with some crucial info. I've written a batch file that runs systeminfo, passes it one IP at a time from a list, and then has it output CSV data into a file, called, let's say, foo.csv, which we can then open in Excel.

Unfortunately, systeminfo doesn't return some things--for instance, MAC addresses. I can use getmac to do that and output a CSV file, but then I have two tables. I also want some function to return other data (serial numbers in BIOS or something). My goal is to:
1. Make foo.csv have headings for all the programs--systeminfo column headings, getmac column headings, and other arbitrary column headings.
2. For each pass of the FOR loop, I want systeminfo to write its data to the file, then getmac to add its data to the end of the line, and then arbitrary function to add its data to the end of the line.

But somewhere, either at the end or the beginning of the systeminfo output, there's a blank line, and if I simply try to >> the getmac CSV output to the file, it starts on a new line, which means I don't have a functioning table.

Hope me! I've been looking through late-'80s DOS text editing utilities to no avail. I can handle regexes, but just barely, and I don't know any perl/python/etc. There has to be a simple way to do this!

posted by nasreddin to computers & internet (7 comments total)

Can you have both systeminfo and getmac write their data to separate variables (%systeminfo_output% and %getmac_output%), instead of to a file? Then it's easy to echo both variables to a file. Something like this (assuming you're using Win XP):

enabledelayedexpansion
for /f "tokens*" %%J in (list_of_IP_addresses.txt) do (
systeminfo_output=systeminfo(%%J)
getmac_output=getmac(%%J)
echo %systeminfo_output%,%getmac_output%>>foo.csv
)

Oh, and adding the headers is easy if you know what they are ahead of time -- just start the file by echoing a header string before you start filling it with output.
posted by harkin banks at 12:11 PM on May 23, 2007

Thanks, harkin banks!

Some questions--
1. Batch doesn't seem to have a way of setting a variable to the output of a program without using an intermediate file; on bash, I could do myvar=`systeminfo`, but that doesn't work here. Do I just not know how to do this in batch?

2. systeminfo outputs something like 3KB of information; I thought DOS only had 255 bytes of address space?

3. Doesn't simply concatenating the two preserve the automatic trailing LF/newline which breaks the table in the first place?
posted by nasreddin at 12:28 PM on May 23, 2007

Aha -- I see. It's been a while since I looked at the output of systeminfo. How are you storing it in your csv? Are you extracting certain parts of it and ditching others, or are you reading it all in?

To answer your questions:

1. for /f will allow you to store one line at a time of program output as a variable. for example

for /f "delims=*" %%F in ('type foo.csv') do set fooline=%%F;

will type out foo.csv and store each line in %fooline%, which you can then operate on as you wish. (Be careful when operating on %fooline% within the for loop though -- you're going to want to use enabledelayedexpansion and refer to it as !fooline!. I have to relearn the exact syntax of this every time I use it for some reason.) I don't know how to get for /f to ignore newlines though.

2. I'm not sure if that limitation is still an issue in NT and newer systems -- and anyway the initial environment size can be changed on older Windows systems to accommodate more variables (see command /? -- I think it's the :e switch.)

3. Yep, I see your problem. Again, what kind of output are you looking for here? And what OS are you using?
posted by harkin banks at 1:06 PM on May 23, 2007

Wow! I just tried your suggestion in #1 (with nested for loops), and it works!

Thank you so much!
posted by nasreddin at 1:39 PM on May 23, 2007

Ever consider using vbscript? I've had to do this in batch before too (when we had Novell, too bad I still don't have the DOS version it pretty much did the same as you described above).

For something that may appear at first to be more complicated looking, but requires less "workarounds" try starting at this thread for more info on how to use vbscript to inventory machines and create txt output.

To grab IP's you'd have to do something like this snippet of an audit script I've done recently:

'------
strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set IPConfigSet = objWMIService.ExecQuery ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

For Each IPConfig in IPConfigSet
If Not IsNull(IPConfig.IPAddress) Then
Dim i
For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)

CurrentAddress=IpConfig.IPAddress(i)
If Left(CurrentAddress, 7)="131.118" Then
sqlIPAddress = CurrentAddress
sqlMACAddress = IPConfig.MACAddress
sqlUsername = network.UserName
sqlComputerName = network.ComputerName
sqlHostName = WSHProcess("LogonServer")
sqlEvent = "testing"
End If
Next
End If
Next

'--------
posted by samsara at 1:53 PM on May 23, 2007

Hah nevermind, you have a solution! My suggestion is now just there for posterity. *grins*
posted by samsara at 1:55 PM on May 23, 2007

I've found that it's often difficult to debug nested FOR loops in NT command scripts, so I generally use an idiom like

for /f "usebackq delims=" %%I in ("list_of_IP_addresses.txt") do call :process %%I
goto nextbit

:process
rem Anything you like goes here; the IP adress
rem you passed in as %%I is available here as %1.
rem
rem If you're passing pathnames rather than IP addresses,
rem you can do Clever Things to them using "%~dpnx1" etc
goto :eof

:nextbit
rem carry on

The key technique is the intra-file "call :label" syntax, with "goto :eof" marking the end of a subroutine. Using it means there's less often a need to use the enabledelayedexpansion kludge, and you get to see each individual command echoed before it's performed.
posted by flabdablet at 8:34 PM on May 23, 2007