Parse CSV file as Input for Portqry testing

Cozmo · 25 Oct 2014 09:01

Hi,

I have two CSV files that have about 800 entries in each. The goal is to manually go to about 50 - 100 separate hosts and test for successful or failed port binding to a handful of destinations. The CSV files contain the hostname and the destination(s) it needs to confirm connectivity with. I can assign the hostname as a variable in the batch:
set host=%COMPUTERNAME%
echo %host%

But how do I have it check the first entry in the CSV so it knows when the hostname matches and do the telnet test or portqry test? As an example the CSV file will contain entries as such:
Hostname,Dest_IP, Dest_Port
hostname1,10.10.10.10,1433

Course if the hostname doesn't match it should skip that whole line and only test the ones where the hostname matches.
The PortQry command works like so in batch:
portqry -n 10.10.10.10 -e 1433 -p tcp /q

And it only needs to display failures. Either piped out to a text file or displayed in the command window. Anybody have an example of how this could be done?

foxidrive · 25 Oct 2014 09:11

portqry isn't a default command in my Windows 8.x and I'm not sure how you want to test if the hostname is valid.

This will rely on the portqry command returning an errorlevel 1 or higher on error but I'm not sure how portqry works.

@echo off
for /f "usebackq tokens=1,2,3 delims=," %%a in ("file.csv") do (
   portqry -n %%b -e %%c -p tcp /q || >>"file.log" echo %%a,%%b,%%c
)
pause

Last edited by foxidrive (25 Oct 2014 09:14)

Cozmo · 25 Oct 2014 10:31

foxidrive wrote:

portqry isn't a default command in my Windows 8.x and I'm not sure how you want to test if the hostname is valid.
This will rely on the portqry command returning an errorlevel 1 or higher on error but I'm not sure how portqry works.

Yes, portqry is not a default command in any Windows OS. I run it from the folder I run the batch file since it doesn't require installation. I'm not concerned about confirming the hostname as they definitely exist and are correct. Thank you for the reminder on the for loop as its been a while since I've composed one. It makes perfect sense. Portqry will give errorlevel 1, 2 and 0 and of course 0 is a good response. Now to have it pipe out to a text file or something if it cannot connect so I can know which ones have an issue.
Thank you again

Aacini · 25 Oct 2014 16:42

If your question may be rewritten this way:

Cozmo could wrote:

I have a CSV file with this format:
Hostname,Dest_IP,Dest_Port
hostname1,10.10.10.10,1433
hostname2,10.10.10.20,1344
The goal is to find the lines in the CSV file with Hostname that matches %COMPUTERNAME% variable, execute
portqry -n Dest_IP -e Dest_Port -p tcp /q
with those lines and display in the screen the data where portqry fails.

Then the Batch file below solve previous request:

@echo off
for /F "tokens=1-3 delims=," %%a in ('findstr "%COMPUTERNAME%" file.csv') do (
   portqry -n %%b -e %%c -p tcp /q
   if errorlevel 1 echo %%a,%%b,%%c
)

Cozmo · 26 Oct 2014 00:36

Aacini wrote:

@echo off
for /F "tokens=1-3 delims=," %%a in ('findstr "%COMPUTERNAME%" file.csv') do (
   portqry -n %%b -e %%c -p tcp /q
   if errorlevel 1 echo %%a,%%b,%%c
)

That is great, Aacini! Since errorlevel can be either 1, 2 or 0 I should be able to do something like:

if not errorlevel 0 echo %%a, %%b, %%c >> errorlog.log

I think?
And you're right. I should've phrased my initial question a little better.
Thank you!

Cozmo · 27 Oct 2014 17:56

Hmm... I actually need for this to do two things. Might be best to understand me if I compose the code I'm trying to have it do:

SetLocal EnableDelayedExpansion

for /F "tokens=1-5 delims=," %%a in ('%path%findstr "%COMPUTERNAME%" %path%combo.csv') do (
   %path%portqry -n %%d -e %%e -p tcp /q
   IF %ERRORLEVEL% 0 (
ECHO >> %local%%COMPUTERNAME%.log
) ELSE (
ECHO %%a,%%d,%%e >> %local%%COMPUTERNAME%Failed.log
)
)

I cannot see why it is failing on me. When I try to look at what it displays in the command prompt all I see is this:
SetLocal EnableDelayedExpansion
0 was unexpected at this time.
IF 0 0 (

I don't know what to make of this or what it is telling me. The code looks good to me. What am I missing? Does anybody know, please?

Cozmo · 27 Oct 2014 22:59

Never mind. The following is what worked well for me.

SetLocal EnableDelayedExpansion

for /F "tokens=1-5 delims=," %%a in ('%path%findstr "%COMPUTERNAME%" %path%combo.csv') do (
   %path%portqry -n %%d -e %%e -p tcp -q
   ECHO %errorlevel%
	IF errorlevel == 1 ( ECHO %%a %%d %%e - Failed >> %local%%COMPUTERNAME%Failed.log )
)

SS64 Forum

#1 25 Oct 2014 09:01

Parse CSV file as Input for Portqry testing

#2 25 Oct 2014 09:11

Re: Parse CSV file as Input for Portqry testing

#3 25 Oct 2014 10:31

Re: Parse CSV file as Input for Portqry testing

#4 25 Oct 2014 16:42

Re: Parse CSV file as Input for Portqry testing

#5 26 Oct 2014 00:36

Re: Parse CSV file as Input for Portqry testing

#6 27 Oct 2014 17:56

Re: Parse CSV file as Input for Portqry testing

#7 27 Oct 2014 22:59

Re: Parse CSV file as Input for Portqry testing

Board footer