Batch file help - adding output from command to csv file

Question

I currently have a batch file that reads a list of computer names and pings each of these and outputs the ones that reply to a csv file with the computer name and ip address.

I now need to edit this to also find out the user of the machine. I need to contact users which are online to arrange some work done to their computer. Their can be over a hundred machines in the batch file so to manually find out each user takes time. Is there a way to do this?

`IF EXIST C:\test\new.csv (del C:\test\new.csv)
 IF EXIST C:\test\final.csv (del C:\test\final.csv)
 set ComputerList=C:\test\ClientList.txt
 Echo Computer Name,IP Address>Final.csv
 setlocal enabledelayedexpansion
 echo please wait...
 for /f "usebackq tokens=*" %%A in ("%ComputerList%") do (
 for /f "tokens=3" %%B in ('ping -n 1 -l 1 %%A ^|findstr Reply') do (
 set IPadd=%%B
 echo %%A,!IPadd:~0,-1!>>final.csv
 )
 )
 findstr /V "IPAddress" final.csv >> C:\test\new.csv
 echo identified machines for Install
 start excel C:\test\new.csv
 echo opened csv file`

The command I want to use to get the username is:

 `wmic.exe /NODE: %%A COMPUTERSYSTEM GET USERNAME`

Thanks Mark

Matt Williamson · Accepted Answer · 2013-10-18 14:49:50Z

1

Here is a function I wrote to do just what you are trying to do:

    :GetLoggedInUser comp user
    for /f %%u in (
    'wmic /NODE:"%1" Computersystem get username^|find "\"') do (
    if not errorlevel 1 ( for /f "tokens=2 delims=\" %%a in (
        'wmic /NODE:"%1" Computersystem get username^|find "\"' ) do  (
          For /f %%b in ("%%a") do (set %2=%%b))    
    ) ELSE (for /f "skip=1" %%a in (
        'wmic /NODE:"%1" Computersystem get username' ) do ( 
          For /f %%b in ("%%a") do (set %2=%%b))
    ))  
    Exit /b

Here is my function for pinging. It returns a 0 if the ping succeeded and a 1 otherwise.

    :IsPingable comp
    ping -n 1 -w 3000 -4 -l 8 "%~1" | Find "TTL=">nul  
    exit /b

    Usage example:

    for /l %%a in (1,1,255) do (
      call:IsPingable 10.6.1.%%a && (
      echo ping 10.6.1.%%a used )||( echo ping 10.6.1.%%a unused )
    )

And here is for if you're pinging IP's and want to return the hostname as well:

    :IsPingable2 comp ret
    setlocal
    for /f "tokens=2"  %%a in (
      '"ping -a -n 1 -4 "%~1" | Find "Pinging" 2>nul"') do set name=%%a
    endlocal & set %~2=%name%
    ping -n 1 -w 3000 -4 -l 8 "%~1" | Find "TTL=">nul  
    exit /b

    Usage example:

    @echo off
    setlocal enabledelayedexpansion
    for /l %%a in (1,1,255) do (
      call:IsPingable2 10.6.1.%%a host && (
      echo ping !host! - 10.6.1.%%a used )||( echo ping !host! - 10.6.1.%%a unused )
    )

I just posted these because they just might come in handy for this type of thing in the future. You can use the :IsPingable now though.

You would use it like this in your code:

 IF EXIST C:\test\final.csv (del C:\test\final.csv)
 set ComputerList=C:\test\ClientList.txt
 Echo Computer Name,IP Address,Logged In User>Final.csv
 setlocal enabledelayedexpansion
 echo please wait...
 echo.
 for /f "usebackq tokens=*" %%A in ("%ComputerList%") do (
   for /f "tokens=3" %%B in ('ping -n 1 -l 1 %%A ^|find "TTL="') do (
      if not errorlevel 1 (
         set IPadd=%%B
         call :GetLoggedInUser %%B uname
         echo %%A,!IPadd:~0,-1!,!uname!>>final.csv
      )
   )
 )
 echo identified machines for Install
 start excel C:\test\final.csv
 echo opened csv file
 goto :eof

:GetLoggedInUser comp user
for /f %%u in (
'wmic /NODE:"%1" Computersystem get username^|find "\"') do (
if not errorlevel 1 ( for /f "tokens=2 delims=\" %%a in (
    'wmic /NODE:"%1" Computersystem get username^|find "\"' ) do  (
      For /f %%b in ("%%a") do (set %2=%%b))    
) ELSE (for /f "skip=1" %%a in (
    'wmic /NODE:"%1" Computersystem get username' ) do ( 
      For /f %%b in ("%%a") do (set %2=%%b))
))  
Exit /b

edited Oct 18, 2013 at 14:49

answered Oct 18, 2013 at 10:45

Matt Williamson

7,1691 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mark-13 Over a year ago

I also need to add the output to an existing csv file... I want to loop through each line of a csv file and find the user and then output all 3 values to a new csv file if i cannot add the username to a current csv file

Matt Williamson Over a year ago

First, you don't need 2 csv files. Just check the errorlevel of the ping and only write to the file if it succeeds. You can break that part out into a function too. I added it above. Then, add a call to my function inside of your loop and use the variable it returns with !var! expanded syntax to get the username.

mark-13 Over a year ago

maybe I am being stupid (which the day Im having could be!) how do I output those values to a csv file? the code you provided just runs really quickly then closes

mark-13 Over a year ago

That is perfect! Works exactly as I wanted! Really appreciate the help. I will vote when it lets me! Wont let me say the answer is useful dont have enough reputation.. Sorry

Sarath Subramanian · Accepted Answer · 2016-09-03 11:07:09Z

0

The below code will count the number of lines in two files sequentially and is set to the variables SalaryCount and TaxCount.

@ECHO OFF    
echo Process started, please wait...    
for /f %%C in ('Find /V /C "" ^< "D:\Trial\Salary.txt"') do set SalaryCount=%%C    
echo Salary,%SalaryCount%
for /f %%C in ('Find /V /C "" ^< "D:\Trial\Tax.txt"') do set TaxCount=%%C
echo Tax,%TaxCount%

Now if you need to output these values to a csv file, you could use the below code.

@ECHO OFF
cd "D:\CSVOutputPath\"
echo Process started, please wait...
echo FILENAME,FILECOUNT> SUMMARY.csv
for /f %%C in ('Find /V /C "" ^< "D:\Trial\Salary.txt"') do set Count=%%C
echo Salary,%Count%>> SUMMARY.csv
for /f %%C in ('Find /V /C "" ^< "D:\Trial\Tax.txt"') do set Count=%%C
echo Tax,%Count%>> SUMMARY.csv

The > will overwrite the existing content of the file and the >> will append the new data to existing data. The CSV will be generated in D:\CSVOutputPath

answered Sep 3, 2016 at 11:07

Sarath Subramanian

21.6k12 gold badges89 silver badges95 bronze badges

1 Comment

Stephan Over a year ago

Your answer has nothing in common with the question. It looks like you added an answer to the wrong question. Please check.

Collectives™ on Stack Overflow

Batch file help - adding output from command to csv file

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related