Inserting DNS name of each IP address in WWW log files

[MKANET]

I use an application (stunnel) that generates log files like the below example. Ive been racking my brain trying to figure out how to make a batch file can insert the DNS name before each IP address it sees in the log file (doing reverse name lookup for each IP). Also, to reformat the time/date stamp to make the log file easier to read. Someone made a linux script using grep to do something very close to this this; but, I didn't want to install cygwin just to do that. I'm hoping it's still possible to do via a pure batch file (or at least via a ported Windows grep command to use in the batch file).

So, the batch file would convert a line that looks like this:

Code: Select all

2013.07.23 13:45:08 LOG5[10152:15604]: Service [stunnel-sslh] accepted connection from 71.194.51.232:2078

To look like this:

Code: Select all

07/23/13 - 01:45:08pm: Service [stunnel-sslh] accepted connection from: c-71-194-51-232.hsd1.il.comcast.net:2078 [70.197.3.139:2078]

Before:
2013.07.23 13:45:08 LOG5[10152:15604]: Service [stunnel-sslh] accepted connection from 71.194.51.232:2078

After:
07/23/13 - 01:45:08pm: Service [stunnel-sslh] accepted connection from: c-71-194-51-232.hsd1.il.comcast.net:2078 [70.197.3.139:2078]

Here's a small section of the stunnel log file:

Code: Select all

2013.07.23 10:16:00 LOG5[10152:15136]: Service [stunnel-sslh] connected remote server from 24.12.152.129:58773
2013.07.23 10:16:00 LOG3[10152:15136]: SSL_read: Connection reset by peer (WSAECONNRESET) (10054)
2013.07.23 10:16:00 LOG5[10152:832]: Connection reset: 272 byte(s) sent to SSL, 96 byte(s) sent to socket
2013.07.23 10:17:53 LOG5[10152:4000]: Service [stunnel-sslh] accepted connection from 71.194.51.232:5535
2013.07.23 10:17:53 LOG5[10152:16008]: connect_blocking: connected 24.12.152.129:7777
2013.07.23 10:17:53 LOG5[10152:4000]: Service [stunnel-sslh] connected remote server from 24.12.152.129:58799
2013.07.23 10:17:53 LOG5[10152:13212]: Service [stunnel-sslh] accepted connection from 71.194.51.232:5508
2013.07.23 10:17:53 LOG5[10152:3348]: Service [stunnel-sslh] accepted connection from 71.194.51.232:5509
2013.07.23 10:17:53 LOG5[10152:2884]: Service [stunnel-sslh] accepted connection from 71.194.51.232:5519

Linux script:

Code: Select all

grep -Eo '([0-9]{1,3}\.){3}[0-9]{1,3}' /log/stunnel.log | sort | uniq 
| grep -v 127.0.0 | grep -v 192.168. >stout

echo -n "" >stout2
echo -n "" >stout2x
for a in `cat stout` ; do
  echo -n $a " ">>stout2;
  echo -n "s/"$a"/"$a" " >>stout2x;
  host $a | awk '{print $5}' >>stout2;
  host $a | awk '{print $5"/g"}' >>stout2x; done sed -f stout2x /log/stunnel.log >stunnelx.log =======

[foxidrive]

Do you want only certain log lines processed? Check the list you provided as not all lines need it.

[penpen]

If the (ip address, port) pair is always at the end of each line, if present,
then you may do something like this to process all lines:

Code: Select all

@echo off
setlocal enableDelayedExpansion
set "log=stunnel.log"
set "logx=stunnelx.log"

(
   for /f "tokens=1-3* delims= " %%a in (%log%) do (
      set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"
      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"
      for /F "tokens=1 delims=0123456789.:" %%e in ("!ipPort!") do set "ipPort="
      for /F "tokens=1-3 delims=.:" %%e in ("!ipPort!") do (
         if "%%e.%%f.%%g" == "127.0.0" set "ipPort="
         if "%%e.%%f"     == "192.168" set "ipPort="
      )

      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('ping -a -n 1 -w 1 %%f ^| findstr /I /C:"ping " ') do set "IP[%%f]=%%h"

         set "host=!IP[%%f]!"
         set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
      )

      echo(!dateValue! !timeValue! !info!
   )
) > %logx%

endlocal
goto :eof

penpen

[MKANET]

WOW!!! Thanks so much penpen! It all works very well. I only had to change "ping" to "pinging" for it to work with the Win7 ping command.

I'm was able to significantly reduce the time it takes to complete processing of the batch file by using nslookup instead of the ping command. Also, I want to remove unnecessary repetition of duplicate IP:PORT (for cases where there isn't a DNS name)...

Unfortunately, there's a small glitch:

Currently my new log files look like this:

Code: Select all

07/23/13 12:18:20pm Service [stunnel-sslh] accepted connection from: :38071 [121.54.54.54:38071]
07/23/13 12:18:21pm Service [stunnel-sslh] accepted connection from: :38072 [121.54.54.54:38072]
07/23/13 12:18:21pm Service [stunnel-sslh] accepted connection from: :38073 [121.54.54.54:38073]

I would like them to look like this:

Code: Select all

07/23/13 12:18:20pm Service [stunnel-sslh] accepted connection from: [121.54.54.54:38071]
07/23/13 12:18:21pm Service [stunnel-sslh] accepted connection from: [121.54.54.54:38072]
07/23/13 12:18:21pm Service [stunnel-sslh] accepted connection from: [121.54.54.54:38073]

Could you (or anyone) please see what I need to do to remove the unnecessary port?

PS: Also, the batch file can't find stunnel.log if it's in long name directories such as:
"C:\Program Files (x86)\stunnel\stunnel.log". It seems to work ok when running the batch file from the root of a drive.

Here's the nslookup enhanced version:

Code: Select all

@echo off
setlocal enableDelayedExpansion
set "log=stunnel.log"
set "logx=stunnelx.log"

(
   for /f "tokens=1-3* delims= " %%a in (%log%) do (
      set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"
      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"
      for /F "tokens=1 delims=0123456789.:" %%e in ("!ipPort!") do set "ipPort="
      for /F "tokens=1-3 delims=.:" %%e in ("!ipPort!") do (
         if "%%e.%%f.%%g" == "127.0.0" set "ipPort="
         if "%%e.%%f"     == "192.168" set "ipPort="
      )

      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('nslookup %%f 2^> nul ^| findstr /I /C:"Name" ') do set "IP[%%f]=%%h"

         set "host=!IP[%%f]!"
         set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
      )

      echo(!dateValue! !timeValue! !info!
   )
) > %logx%

endlocal
goto :eof

[foxidrive]

Do you want only certain log lines processed? Check the list you provided as not all lines need it.

??

[penpen]

This should fix the problems:

Code: Select all

@echo off
setlocal enableDelayedExpansion
set "log=stunnel log\stunnel.log"
set "logx=stunnelx.log"

(
   for /f "tokens=1-3* usebackq delims= " %%a in ("%log%") do (
      set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"
      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"
      for /F "tokens=1 delims=0123456789.:" %%e in ("!ipPort!") do set "ipPort="
      for /F "tokens=1-3 delims=.:" %%e in ("!ipPort!") do (
         if "%%e.%%f.%%g" == "127.0.0" set "ipPort="
         if "%%e.%%f"     == "192.168" set "ipPort="
      )

      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('nslookup %%f 2^> nul ^| findstr /I /C:"Name" ') do set "IP[%%f]= %%h"
         if not defined IP[%%f] set "IP[%%f]= "

         set "host=!IP[%%f]:~1!"

         if defined host (
            set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
         ) else (
            set "info=!info: %%f:%%g=!: [!ipPort!]"
         )
      )


      echo(!dateValue! !timeValue! !info!
   )
) > "%logx%"

endlocal
goto :eof

penpen

[MKANET]

Hi Foxidrive, I'm so sorry, I don't know how I missed your post!

I intentionally inserted lines that don't have IP addresses; in hopes that it is presumed (for the batch file) to ignore those lines. Luckily penpen presumed this correctly. Also, Penen presumed correctly that IP's are only going to be at the end of each line in the original log file (based on the example given). Now that I think about it, I should have explained in more detail in my original post. Again, sorry for missing you post.

Do you want only certain log lines processed? Check the list you provided as not all lines need it.

[MKANET]

penpen,

I just wanted to thank you again for the examples you posted. They works great; and, further help me become a better batch/script maker!

I also wanted to make a slightly modified script; which gives me a very stripped down stunnelx.log file (only showing who visited the website besides me). It wasn't very hard to do that. However, I think I'm probably choosing one of the most inefficient ways to do it. The batch file literally takes several times longer to complete when I added all those exceptions.

Could you (or someone else) be kind enough to show the most efficient way to do what I'm doing below (the less time it takes to complete the batch file, the better).

Thanks again,
MKANET

Code: Select all

@ECHO OFF
setlocal enableDelayedExpansion
set "log=stunnel.log"
set "logx=stunnelx.log"

(
   for /f "tokens=1-3* usebackq delims= " %%a in ("%log%") do (
      set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"
     
     echo !info! | findstr /I /C:"127.0.0" >nul
     if not errorlevel 1 set hide=yes
     
     echo !info! | findstr /I /C:"192.168" >nul
     if not errorlevel 1 set hide=yes

     echo !info! | findstr /I /C:"byte" >nul
     if not errorlevel 1 set hide=yes
     
     echo !info! | findstr /I /C:"WSAECONNRESET" >nul
     if not errorlevel 1 set hide=yes
     
     echo !info! | findstr /I /C:"Peer suddenly disconnected" >nul
     if not errorlevel 1 set hide=yes
     
     
      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"


      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('nslookup %%f 2^> nul ^| findstr /I /C:"Name" ') do set "IP[%%f]= %%h"
         if not defined IP[%%f] set "IP[%%f]= "

         set "host=!IP[%%f]:~1!"

         if defined host (
            set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
         ) else (
            set "info=!info: %%f:%%g=!: [!ipPort!]"
         )
      )

      if not !hide! == yes (
         echo(!dateValue! !timeValue! !info!
         )
         set "hide="
      )
) > "%logx%"

endlocal
goto :eof

[MKANET]

BTW: I made an attempt to make the above batch file more efficient (see below); but, it produces a, "The syntax is of the command is incorrect". I tried putting echo ON at the beginning; but it didn't show me anything useful to me. Maybe there's a much better way to do this?

Code: Select all

@ECHO OFF
setlocal enableDelayedExpansion
set "log=stunnel.log"
set "logx=stunnelx.log"

(
   for /f "tokens=1-3* usebackq delims= " %%a in ("%log%") do (
      set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"
     
     echo !info! | findstr /I /C:"127.0.0" >nul
     if not errorlevel 1 goto :skip
     
     echo !info! | findstr /I /C:"192.168" >nul
     if not errorlevel 1 goto :skip

     echo !info! | findstr /I /C:"byte" >nul
     if not errorlevel 1 goto :skip
     
     echo !info! | findstr /I /C:"WSAECONNRESET" >nul
     if not errorlevel 1 goto :skip
     
     echo !info! | findstr /I /C:"Peer suddenly disconnected" >nul
     if not errorlevel 1 goto :skip
     
      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"


      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('nslookup %%f 2^> nul ^| findstr /I /C:"Name" ') do set "IP[%%f]= %%h"
         if not defined IP[%%f] set "IP[%%f]= "

         set "host=!IP[%%f]:~1!"

         if defined host (
            set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
         ) else (
            set "info=!info: %%f:%%g=!: [!ipPort!]"
         )
      )

      echo(!dateValue! !timeValue! !info!
     )
     
      :skip
     
) > "%logx%"

endlocal
goto :eof

[penpen]

The syntax error is caused by the position of the Label. The next command then is:

Code: Select all

) > "%logx%"

which is obviously an error.

I also assume, that the Label is at the wrong position anyway, as it should be within the for loop.
But this won't work anyway, because a goto breaks the loop:

Code: Select all

@echo off
for %%a in (1 1 3) do (
   echo 1
   if "a" == "a" goto :next
   echo 2
:next
   echo 3
)

penpen

[penpen]

This is untested (programmed it on my mobile phone):

Code: Select all

@ECHO OFF
setlocal enableDelayedExpansion
set "log=stunnel.log"
set "logx=stunnelx.log"
set "logt=stunnel.log.temp"

findstr /I /V /C:"127.0.0" /C:"192.168" /C:"byte" /C:"WSAECONNRESET"  /C:"Peer suddenly disconnected" "%log%" > "%logt%"

(
   for /f "tokens=1-3* usebackq delims= " %%a in ("%logt%") do (
      set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"

      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"

      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('nslookup %%f 2^> nul ^| findstr /I /C:"Name" ') do set "IP[%%f]= %%h"
         if not defined IP[%%f] set "IP[%%f]= "

         set "host=!IP[%%f]:~1!"

         if defined host (
            set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
         ) else (
            set "info=!info: %%f:%%g=!: [!ipPort!]"
         )
      )

         echo(!dateValue! !timeValue! !info!
      )
) > "%logx%"

endlocal
goto :eof

but it should be faster (if there were no errors).

penpen

Edit: This should work, too. Don't know which one is is faster, but you then don't Need a temp file.

Code: Select all

(
   for /f "tokens=1-3* delims= " %%a in ('findstr /I /V /C:"127.0.0" /C:"192.168" /C:"byte" /C:"WSAECONNRESET"  /C:"Peer suddenly disconnected" "%log%"') do (
:: instead of
set "logt=stunnel.log.temp"

findstr /I /V /C:"127.0.0" /C:"192.168" /C:"byte" /C:"WSAECONNRESET"  /C:"Peer suddenly disconnected" "%log%" > "%logt%"
(
  for /f "tokens=1-3* usebackq delims= " %%a in ("%logt%") do (

Edit2: Added /V option of findstr, to do an inverse search.

[MKANET]

Hi PenPen,

I think below is what you wanted me to try (without the temp file). It looks like it may be doing the opposite of what I was aiming for. It's only showing lines which include /C:"127.0.0" /C:"192.168" /C:"byte" /C:"WSAECONNRESET" /C:"Peer suddenly disconnected", instead of avoiding those. I'm surprised you were even able to do that on your mobile phone! Looking forward to hearing back from you...

Code: Select all

@ECHO OFF
setlocal enableDelayedExpansion
set "log=stunnel.log"
set "logx=stunnelx.log"


(
   for /f "tokens=1-3* delims= " %%a in ('findstr /I /C:"127.0.0" /C:"192.168" /C:"byte" /C:"WSAECONNRESET" /C:"Peer suddenly disconnected" "%log%"') do (
   
   
    set "dateValue=%%a"
      set "dateValue=!dateValue:~5,2!/!dateValue:~8,2!/!dateValue:~2,2!"

      for /F "tokens=1* delims=:" %%e in ("%%b") do (
         set /A "timeValue=%%e"
         if !timeValue! == 0 (
            set "timeValue=12:%%fam"
         ) else if !timeValue! Lss 12 (
            set "timeValue=%%e:%%fam"
         ) else if !timeValue! == 12 (
            set "timeValue=12:%%fpm"
         ) else (
            set /A "timeValue-=12"
            set "timeValue=0!timeValue!"
            set "timeValue=!timeValue:~-2!:%%fpm"
         )
      )

      set "info=%%d"

      set "ipPort="
      for %%e in (!info!) do set "ipPort=%%e"

      for /F "tokens=1,2 delims=:" %%f in ("!ipPort!") do (
         if not defined IP[%%f] for /f "tokens=2 delims= " %%h in ('nslookup %%f 2^> nul ^| findstr /I /C:"Name" ') do set "IP[%%f]= %%h"
         if not defined IP[%%f] set "IP[%%f]= "

         set "host=!IP[%%f]:~1!"

         if defined host (
            set "info=!info: %%f:%%g=!: !host!:%%g [!ipPort!]"
         ) else (
            set "info=!info: %%f:%%g=!: [!ipPort!]"
         )
      )

         echo(!dateValue! !timeValue! !info!
      )
) > "%logx%"

endlocal
goto :eof

[penpen]

Sry i wanted to use the /V Option.
I have corrected that in the code above.

penpen