How to find substrings in log file and increment the counter using dos batch file

Question

Can anybody help me in making the windows batch file to find the substring from the log file. Sample of the log.log file is as below

ID,Date,Time,Description,IP Address,Host Name,MAC Address
10,02/21/14,00:29:45,Assign,172.20.55.50,PC1,123456789AB1,
31,02/21/14,00:29:45,DNS Update,172.20.55.50,PC1,123456789AB1,
10,02/21/14,00:29:45,Assign,172.30.55.50,PC2,123456789AB2,
31,02/21/14,00:29:45,DNS Update,172.30.55.50,PC1,123456789AB2,
10,02/21/14,00:29:45,Assign,172.20.56.60,PC3,123456789AB3,
10,02/21/14,00:29:45,Assign,172.30.55.60,PC4,123456789AB4,
**11,02/21/14,00:30:45,Assign,172.30.55.10,PC2,123456789AB5,**
**11,02/21/14,00:30:46,Assign,172.30.55.10,PC2,123456789AB5,**
**31,02/21/14,00:00:37,DNS Update Failed,172.17.110.13,TAR-CAR-051180L.WTPK.local,-1,**

This is basically DHCP log file. The objective is to count number of New Assign IP requests (whose ID is 10) and number of renewal IP requests (whose ID is 11).

For ID 10, if the IP starts with 172.20.55 or 172.20.56 it should increment in the counter "NewPoolA" and if the IP starts with 172.30.55 or 172.30.56 it should increment in "NewPoolB".

Similarly for ID 11 if the IP starts with 172.20.55 or 172.20.56 it should increment in the counter "RenewPoolA" and if the IP starts with 172.30.55 or 172.30.56 it should increment in "RenewPoolB".

so far what I have done is below

@echo off 
Setlocal EnableDelayedExpansion

set /a NewPoolA=0
set /a NewPoolB=0
set /a RenewPoolA=0
set /a RenewPoolB=0

for /F "tokens=1-6 delims=," %%a in (log.log) do (
    if %%a equ 10 (
        rem if %%e contains 172.20.55 (
            set /a NewPoolA += 1
            goto someLabel
        )
        rem else if %%e contains 172.20.56 (
            set /a NewPoolA += 1
            goto someLabel
        )
        rem else if %%e contains 172.30.55 (
            set /a NewPoolB += 1
            goto someLabel
        )
        rem else if %%e contains 172.30.56 (
            set /a NewPoolB += 1
            goto someLabel
        )
        rem -------- if id 10 and not match any condition then
        goto someLabel
    ) else if %%a equ 11 (
        rem if %%e contains 172.20.55 (
            set /a RenewPoolA += 1
            goto someLabel
        )
        rem else if %%e contains 172.20.56 (
            set /a RenewPoolA += 1
            goto someLabel
        )
        rem else if %%e contains 172.30.55 (
            set /a RenewPoolB += 1
            goto someLabel
        )
        rem else if %%e contains 172.30.56 (
            set /a RenewPoolB += 1
            goto someLabel
        )
        rem -------- if id 11 and not match any condition then
        goto someLabel
    )
)
echo Total new request in Pool A is %NewPoolA%
echo Total renewal request in Pool A is %RenewPoolA%

echo Total new request in Pool B is %NewPoolB%
echo Total renewal request in Pool B is %RenewPoolB%

This is the logic and my understanding that how it will work. I dont know the syntax of dos batch commands.

These two pools are just example. I have total 80 pools for which i have to do this. In log file there are almost 100,000 entries. To match this number of lines to each of the pool will take too much time. So, the goal is to jump out of the 'if condition' after incrementing the value and to shorten the execution time of the batch file.

One more thing, the log file contains duplicate entries in different timings for same mac address. I need the increment will run only for unique entries of mac address.

Answer 1

Does this do what you need:

@echo off
for /f %%a in ('type "file.csv"^|findstr "^10," ^|findstr ",172.20.55 ,172.20.56"^|find /c /v ""') do set AAA-NewPoolA=%%a
for /f %%a in ('type "file.csv"^|findstr "^10," ^|findstr ",172.30.55 ,172.30.56"^|find /c /v ""') do set AAA-NewPoolB=%%a
for /f %%a in ('type "file.csv"^|findstr "^11," ^|findstr ",172.20.55 ,172.20.56"^|find /c /v ""') do set AAA-ReNewPoolA=%%a
for /f %%a in ('type "file.csv"^|findstr "^11," ^|findstr ",172.30.55 ,172.30.56"^|find /c /v ""') do set AAA-ReNewPoolB=%%a
set aaa
pause

Answer 2

The simplest and fastest way to accumulate results when they are selected via complicated tests is to not identify each one separately, but accumulate all groups of related results and extract the desired ones at end. In your case, this is easily achieved via a two dimensional array this way:

@echo off
setlocal EnableDelayedExpansion

rem Accumulate results for all ID.IP (first 3 groups) combinations
for /F "skip=1 tokens=1,5-7 delims=,." %%a in (log.log) do (
   set /A requests[%%a.%%b.%%c.%%d]+=1
)

rem Get desired results
set /A NewPoolA = requests[10.172.20.55] + requests[10.172.20.56]
set /A NewPoolB = requests[10.172.30.55] + requests[10.172.30.56]

set /A RenewPoolA = requests[11.172.20.55] + requests[11.172.20.56]
set /A RenewPoolB = requests[11.172.30.55] + requests[11.172.30.56]

echo Total new request in Pool A is %NewPoolA%
echo Total renewal request in Pool A is %RenewPoolA%

echo Total new request in Pool B is %NewPoolB%
echo Total renewal request in Pool B is %RenewPoolB%

You may also make good use of the flexibility that arrays provide to define your 80 pools in a much simpler way. For example:

@echo off
setlocal EnableDelayedExpansion

rem Accumulate results for all ID.IP (first 3 groups) combinations
for /F "skip=1 tokens=1,5-7 delims=,." %%a in (log.log) do (
   set /A requests[%%a.%%b.%%c.%%d]+=1
)

rem Get and show desired results from a long pool definition list
for %%A in ("NewPoolA=10 172.20.55+10 172.20.56"
            "NewPoolB=10 172.30.55+10 172.30.56"
            "RenewPoolA=11 172.20.55+11 172.20.56"
            "RenewPoolB=11 172.30.55+11 172.30.56") do (
   for /F "tokens=1-5 delims==+ " %%a in (%%A) do (
      set /A %%a=requests[%%b.%%c]+requests[%%d.%%e]
      echo Total %%a request is !%%a!
   )
)

Output example with your data:

C:\> test
Total NewPoolA request is 2
Total NewPoolB request is 2
Total RenewPoolA request is 0
Total RenewPoolB request is 1

You may even replace the "NewPoolA" message in these results by "new request in Pool A" one using a second array! ;-)

LAST EDIT

This solution has been modified several times because new details not included in the original question. In order to avoid confusions, I removed previous solutions and posted just the final one. The Batch code below accumulate requests for unique MAC addresses only, ignore MAC addresses equal to -1, and allows points in any field besides IP address.

@echo off
setlocal EnableDelayedExpansion

rem Accumulate results for all ID.IP(first 3 groups).MAC_addr combinations
rem NEW: Ignore MAC_addresses equal to -1
for /F "skip=1 tokens=1,5,7 delims=," %%a in (log.log) do (
   if "%%c" neq "-1" for /F "tokens=1-3 delims=." %%i in ("%%b") do (
      set /A requests[%%a.%%i.%%j.%%k.%%c]+=1
   )
)

rem Get and show desired results from a long definition list
rem NEW: Only accumulate requests for unique MAC addresses (count=1)
for %%A in ("NewPoolA=10 172.20.55+10 172.20.56"
            "NewPoolB=10 172.30.55+10 172.30.56"
            "RenewPoolA=11 172.20.55+11 172.20.56"
            "RenewPoolB=11 172.30.55+11 172.30.56") do (
   for /F "tokens=1-5 delims==+ " %%a in (%%A) do (
      set %%a=0
      for /F "tokens=2 delims==" %%x in ('set requests[%%b.%%c 2^>NUL') do (
         if %%x equ 1 set /A %%a+=1
      )
      for /F "tokens=2 delims==" %%x in ('set requests[%%d.%%e 2^>NUL') do (
         if %%x equ 1 set /A %%a+=1
      )
      echo Total %%a request is !%%a!
   )
)

Answer 3

Here you go. I'm sure you can work out the rest

@echo off
setlocal

set "NewPoolA=0"
set "NewPoolB=0"
set "RenewPoolA=0"
set "RenewPoolB=0"

for /F "tokens=1-6 delims=," %%a in (log.log) do (
  if "%%~a" equ "10" (
    for /f %%b in ('echo "%%e"^|Findstr /c:"172.20.55" /c:"172.20.56"') do (
      set /a NewPoolA+=1    
    )
  )
)
echo Total new request in Pool A is %NewPoolA%

Answer 4

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
:: remove variables starting $ or #
For %%b IN ($ #) DO FOR  /F "delims==" %%a In ('set %%b 2^>Nul') DO SET "%%a="
FOR /f "tokens=1-4delims=. " %%a IN (q21935716.txt) DO (
 IF "%%d"=="" (SET $%%a=%%b) ELSE (SET #%%b.%%c.%%d=%%a)
)

FOR /f "tokens=1,5,6,7delims=.," %%a IN (q21935716.log) DO (
 IF DEFINED $%%a IF DEFINED #%%b.%%c.%%d (SET /a !$%%a!!#%%b.%%c.%%d!+=1)
)

FOR /f "tokens=2delims==" %%a IN ('set $') DO SET %%a

GOTO :EOF

Here's a reasonably flexible routine, given that you have 80 pools.

The file q21935716.txt contains the following:

10 newpool
11 renewpool
A 172.20.55
A 172.20.56
B 172.30.55
B 172.30.56

Fairly obviously, lines of either ID+poolname or poolsection+IP.

The first step is to clear eny variables that start with either $ or #.

Next step is to read the file q21935716.txt using . or space as delimiters. This means that the poolname lines will have 2 tokens and the ip lines 4. The variables $poolname and #ip are then set up, containing the ID and poolsection respectively.

It's then a matter of reading the logfile, delimiers of . , or space which means tokens 1,5,6,7 are of interest. Only if $ID and #IP both exist is the line of interest. In tose lines, you need to increment the poolnamepoolsection.

The final for simply lists the poolnames.