Exporting to CSV from a batch file

  • Megallica
  • Novice
  • Novice
  • Megallica
  • Posts: 28
  • Loc: Scottsdale, AZ

Post 3+ Months Ago

So I am trying to create a file that will allow me to pull relevant data from a PC using a batch file and then export to a .CSV on a network share.

Here is the info I am calling:
wmic csproduct get name, vendor, version
wmic bios get serialnumber
getmac
Systeminfo | findstr /C:BIOS

Is there a way to export this from a batch? If not, how about VBScript or CScript?

Thanks in advance
Chris
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

Can you provide a sample of what you expect the CSV file to look like?
  • Megallica
  • Novice
  • Novice
  • Megallica
  • Posts: 28
  • Loc: Scottsdale, AZ

Post 3+ Months Ago

Hi Spork,
I'm just looking for an easy, no frills export into a table that will show
Hostname, Product Name, Product Vendor, Product Version, Device Serial Number, MAC Address, BIOS Version/Date, OS Name, OS Version, OS Architecture

hostname,name,vendor,version,serialnumber,macaddress,bios,osname,osversion,osarchitecture

I've made some additions to the info I am pulling. Here is how the batch currently looks:

@echo off
hostname
wmic csproduct get name, vendor, version
wmic bios get serialnumber
getmac
Systeminfo | findstr /C:BIOS
systeminfo | findstr /B /C:"OS Name" /C:"OS Version"
wmic os get osarchitecture
pause

Let me know if this makes sense or if you need more info.

Thanks
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

So you're expecting only one "entry" in your exported table, right? One row with all the columns accounted for?

Your best bet here is probably to use a combination of findstr and "for /f" loops to parse the appropriate tokens from the output of each command, stash them in local variables and then echo everything to a text file. The downside is that the for-loop syntax in Windows batch scripts is pretty whacky, but it gets the job done.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

Having said that, if you're able to do this in something more robust such as Python or Perl, I'd recommend that instead, since regular expressions will make your life easier.
  • Megallica
  • Novice
  • Novice
  • Megallica
  • Posts: 28
  • Loc: Scottsdale, AZ

Post 3+ Months Ago

Yep that is all I need. So I tried to do a simple test with
hostname
FOR /F "tokens=1 delims= " %%G IN ('%_hostname_cmd% ^find "FCRS"') DO echo Result is [%%G] in (C:\Users\Cesadmin\Desktop)
pause

The script runs and does not error, however, it does not create a text file. (The 'FCRS' that it's looking for is the prefix to every PC on my site.) Where did I go wrong?
This is my first time attempting this, so please bear with my ignorance :)
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

To direct output to a file, use the ">" operator to create/overwrite a file, and ">>" to append. A simple example using just "hostname" (you can modify the command to your liking):

Code: [ Select ]
for /F "tokens=1 delims= " %%G in ('hostname') do (
  echo Result is [%%G] > sample.txt
)
  1. for /F "tokens=1 delims= " %%G in ('hostname') do (
  2.   echo Result is [%%G] > sample.txt
  3. )


This will direct the output of the "echo" command to sample.txt.

edit: It looks like ozzu/Chrome is changing regular quotes to fancy quotes, so be sure to re-type them if copy/pasting my example.
  • Megallica
  • Novice
  • Novice
  • Megallica
  • Posts: 28
  • Loc: Scottsdale, AZ

Post 3+ Months Ago

Ok so I tested the command and it worked, but only once... I cannot get it to create another text file even if I change the name of the output doc. Any thoughts?

If I use:
Code: [ Select ]
for /F "tokens=* delims= " %%G in ('wmic csproduct get name, vendor, version') do (echo [%%G] > SystemInfo.txt

Can I pull the data from the same command or do I have to separate out the name/vendor/version?

Lastly,
if I made all of the commands in succession
EX:
Code: [ Select ]
for /F "tokens=1 delims= " %%G in ('hostname') do (echo [%%G] > SystemInfo.txt
for /F "tokens=* delims= " %%G in ('wmic csproduct get name, vendor, version') do (echo [%%G] > SystemInfo.txt
  1. for /F "tokens=1 delims= " %%G in ('hostname') do (echo [%%G] > SystemInfo.txt
  2. for /F "tokens=* delims= " %%G in ('wmic csproduct get name, vendor, version') do (echo [%%G] > SystemInfo.txt


Will this output to the same file or overwrite as each command runs?
Thanks again. Your assistance is really appreciated!
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

You're missing the closing parentheses for each loop; it helps to use multiple lines to improve readability. Also you're overwriting the file each time; use ">>" to append to an existing file after the first command.

Code: [ Select ]
for /F “tokens=1 delims= ” %%G in ('hostname') do (
  echo [%%G] > SystemInfo.txt
)
for /F “tokens=* delims= ” %%G in ('wmic csproduct get name, vendor, version') do (
  echo [%%G] >> SystemInfo.txt
)
  1. for /F “tokens=1 delims= ” %%G in ('hostname') do (
  2.   echo [%%G] > SystemInfo.txt
  3. )
  4. for /F “tokens=* delims= ” %%G in ('wmic csproduct get name, vendor, version') do (
  5.   echo [%%G] >> SystemInfo.txt
  6. )


Also, if you're pulling multiple tokens from a line, they'll be dumped into successive variables, starting with the one in the FOR loop, i.e. the first token will be in %%G, the second one in %%H, and so forth.
  • Megallica
  • Novice
  • Novice
  • Megallica
  • Posts: 28
  • Loc: Scottsdale, AZ

Post 3+ Months Ago

Thanks Spork! Got it all working correctly.
I do have one last question. When you run 'getmac' it pulls the MAC address and adapter state for all adapters on the system. What delims would I need to only export the active adapters?
This is what is currently exported:

Physical Address Transport Name
=================== ==========================================================
3C-97-0E-A9-7B-79 Media disconnected
6C-88-14-BB-84-1C \Device\Tcpip_{A1C49BAE-3AA2-4016-83FC-451E9B5F5AB3}
F8-2F-A8-E3-49-6B Media disconnected
00-60-73-53-B5-74 \Device\Tcpip_{44CF952C-B243-424E-92B3-3461D0A6A4E9}


I only want the Header and 2nd and 4th MAC addresses (Wired and Wireless NIC) Would it also be possible to export only the IP address from IP Config the same way?
Thanks
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

You need to split up the output as reasonably as possible, then use some conditional logic to eliminate the unwanted entries. On my machine, inactive adapters show up with "N/A" as the transport, so I included that check as well, but you can eliminate it if you don't need it.

I found this to be a little easier with EnableDelayedExpansion turned on (warning: batch script voodoo):

Code: [ Select ]
@echo off
setlocal enableextensions enabledelayedexpansion

echo physicaladdress,transportname
for /F "skip=3 tokens=1,2" %%G in ('getmac') do (
 set __mac=%%G
 set __transport=%%H
 if /i not "!__transport!"=="N/A" (
  if /i not "!__transport!"=="Media disconnected" (
   echo !__mac!,!__transport!
  )
 )
)

endlocal
  1. @echo off
  2. setlocal enableextensions enabledelayedexpansion
  3. echo physicaladdress,transportname
  4. for /F "skip=3 tokens=1,2" %%G in ('getmac') do (
  5.  set __mac=%%G
  6.  set __transport=%%H
  7.  if /i not "!__transport!"=="N/A" (
  8.   if /i not "!__transport!"=="Media disconnected" (
  9.    echo !__mac!,!__transport!
  10.   )
  11.  )
  12. )
  13. endlocal


I didn't bother parsing the headers since you can just echo those manually. The script above produces this for me:

Code: [ Select ]
physicaladdress,transportname
74-E3-D1-CA-4D-F5,\Device\Tcpip_{F1D51C7D-6552-4640-8D2C-55A3F8DD196B}
10-72-5D-4D-5D-82,\Device\Tcpip_{E4DF4431-3F5B-4E57-8E7A-83B1EB34BF71}
  1. physicaladdress,transportname
  2. 74-E3-D1-CA-4D-F5,\Device\Tcpip_{F1D51C7D-6552-4640-8D2C-55A3F8DD196B}
  3. 10-72-5D-4D-5D-82,\Device\Tcpip_{E4DF4431-3F5B-4E57-8E7A-83B1EB34BF71}


Extracting IP's from ipconfig would follow roughly the same logic -- you'll just have more data to deal with and conditions to check for.
  • Megallica
  • Novice
  • Novice
  • Megallica
  • Posts: 28
  • Loc: Scottsdale, AZ

Post 3+ Months Ago

You rock Spork! Everything is doing what I need. I have posted the script below if anyone else wants to save some time.


Code: [ Select ]
@echo off
Set Configout=\\YourNetworkShare\File.txt
SET ConfigOutCred=/USER:USERNAME@DOMAIN /Pass:PASSWORD
color 0c
echo.
echo Exporting System Info...
Date /T >>%Configout% %ConfigOutCred%
  for /F "tokens=1 delims= " %%G in ('hostname') do (
   echo "%%G" >> %Configout%
  )
(Systeminfo | findstr /C:BIOS)>>%Configout%
(systeminfo | findstr /B /C:"OS Name" /C:"OS Version")>>%Configout%
setlocal enableextensions enabledelayedexpansion
for /F "skip=3 tokens=1,2" %%L in ('getmac') do (
set __mac=%%L
set __transport=%%M
if /i not "!__transport!"=="N/A" (
 if /i not "!__transport!"=="Media disconnected" (
  if /i not "!__transport!"=="Media" (
  echo "%%L" >> %Configout%
  )
 )
)
)
endlocal
  for /F "tokens=2 delims=:" %%F in ('ipconfig^|find "IPv4 Address"') do (
   set __IP=%%F
   echo "%%F" >> %Configout%
  )
  for /F "tokens=1,2,3 delims= " %%H in ('wmic csproduct get name') do (
   echo "%%H" >> %Configout%
  )
  for /F "tokens=1,2,3 delims= " %%I in ('wmic csproduct get vendor') do (
   echo "%%I" >> %Configout%
  )
  for /F "tokens=1,2,3 delims= " %%J in ('wmic csproduct get version') do (
   echo "%%J" >> %Configout%
  )
  for /F "tokens=1,2,3 delims= " %%K in ('wmic bios get serialnumber') do (
   echo "%%K" >> %Configout%
  )
wmic os get osarchitecture>>%Configout%
echo.
echo System Info has been exported!
: notepad.exe \\YourNetworkShare\File.txt
pause
exit
  1. @echo off
  2. Set Configout=\\YourNetworkShare\File.txt
  3. SET ConfigOutCred=/USER:USERNAME@DOMAIN /Pass:PASSWORD
  4. color 0c
  5. echo.
  6. echo Exporting System Info...
  7. Date /T >>%Configout% %ConfigOutCred%
  8.   for /F "tokens=1 delims= " %%G in ('hostname') do (
  9.    echo "%%G" >> %Configout%
  10.   )
  11. (Systeminfo | findstr /C:BIOS)>>%Configout%
  12. (systeminfo | findstr /B /C:"OS Name" /C:"OS Version")>>%Configout%
  13. setlocal enableextensions enabledelayedexpansion
  14. for /F "skip=3 tokens=1,2" %%L in ('getmac') do (
  15. set __mac=%%L
  16. set __transport=%%M
  17. if /i not "!__transport!"=="N/A" (
  18.  if /i not "!__transport!"=="Media disconnected" (
  19.   if /i not "!__transport!"=="Media" (
  20.   echo "%%L" >> %Configout%
  21.   )
  22.  )
  23. )
  24. )
  25. endlocal
  26.   for /F "tokens=2 delims=:" %%F in ('ipconfig^|find "IPv4 Address"') do (
  27.    set __IP=%%F
  28.    echo "%%F" >> %Configout%
  29.   )
  30.   for /F "tokens=1,2,3 delims= " %%H in ('wmic csproduct get name') do (
  31.    echo "%%H" >> %Configout%
  32.   )
  33.   for /F "tokens=1,2,3 delims= " %%I in ('wmic csproduct get vendor') do (
  34.    echo "%%I" >> %Configout%
  35.   )
  36.   for /F "tokens=1,2,3 delims= " %%J in ('wmic csproduct get version') do (
  37.    echo "%%J" >> %Configout%
  38.   )
  39.   for /F "tokens=1,2,3 delims= " %%K in ('wmic bios get serialnumber') do (
  40.    echo "%%K" >> %Configout%
  41.   )
  42. wmic os get osarchitecture>>%Configout%
  43. echo.
  44. echo System Info has been exported!
  45. : notepad.exe \\YourNetworkShare\File.txt
  46. pause
  47. exit


Using this will pull the following info:
Current Date
PC Hostname
BIOS Version and Date
OS Name
OS Version and SP
MAC Addresses of active adapters
IP Addresses of active adapters
PC Model
PC Brand
PC Version
PC Serial Number
OS Architecture (32-Bit or 64-Bit)

After it grabs this data, it will create a text file in whichever network folder specified with fully qualified domain credentials. When it is run on subsequent machines, it will append the text file and add it to the bottom (With the OS Arch. and the Date making an easy separator to eyeball). Enjoy
*Note* I left the 'pause' and 'notepad' strings in for testing if anyone likes.
Thanks again!
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

Glad you got it all working. Windows batch scripting is pretty much the wonkiest, most non-intuitive thing when it comes to scripting.

Cheers! :beerchug:

Post Information

  • Total Posts in this topic: 13 posts
  • Users browsing this forum: No registered users and 41 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.