Batch file for the data collection task
The batch file for searching in all *.aof files for the strings SIP Rx and SIP Tx and write the data of interest into a CSV file with name SIP Rx_Tx.csv in wanted format is:
@echo off
setlocal EnableExtensions DisableDelayedExpansion
(for /F "eol=? tokens=1* delims=:" %%G in ('%SystemRoot%\System32\findstr.exe /I /L /C:"SIP Rx" /C:"SIP Tx" *.aof 2^>nul') do for /F "eol=: tokens=2,5,7 delims=|" %%I in ("%%H") do echo %%G;%%I;%%J;%%K)>"SIP Rx_Tx.csv"
if exist "SIP Rx_Tx.csv" for %%I in ("SIP Rx_Tx.csv") do if %%~zI == 0 del "SIP Rx_Tx.csv"
endlocal
Short description of the batch file
The command FINDSTR searches in all *.aof files in current directory for lines containing either SIP Rx or SIP Tx and outputs each line with file name at beginning separated by a colon from the line with found string.
The outer FOR splits up each line into two substrings (tokens) using : as string delimiter. The first substring is the file name without path assigned to the specified loop variable G. The second substring is the line in the file assigned to next loop variable H.
The line in the file assigned to loop variable H is processed further with a second FOR to split it up into several substrings using | as string delimiter. The second vertical bar delimited substring is assigned to the specified loop variable I, the fifth substring to next loop variable J and the seventh substring to next but one loop variable K.
The file name assigned to I and the three data of interest in found line are output with command ECHO. This output is written into the file with name SIP Rx_Tx.csv in current directory.
The created CSV file is deleted in case of being an empty file because of no *.aof file is found in current directory or no *.aof file contains one of the two searched strings.
Full description of the batch file
Here is also a description of the few command lines explaining in full details why the command lines are used as it can be seen above and how they work.
Definition of the execution environment
The first two command lines define completely the required execution environment for the batch file which is:
- Command echo mode turned off to prevent the display of the executed commands during execution or writing them into the CSV file.
- Command extensions enabled as required for
for /F.
- Delayed expansion disabled to process correct also file names and data containing one or more exclamation marks.
An exclamation mark ! in a string assigned to one of the loop variables would be interpreted as beginning/end of a delayed expanded variable reference if delayed expansion would be enabled on processing the two FOR commands. That would result in replacing !string! by the value of the referenced variable with name string respectively removal of !string! if there is no such variable. A single ! would be just removed with enabled delayed expansion. That is not wanted here and for that reason it is good practice to explicitly disable delayed expansion instead of depending on status for delayed expansion defined outside of the batch file.
Correct specification of redirection operator >
The Windows command processor instance processing the batch file parses next the third command line containing twice the redirection operator >. But only the second > is for cmd.exe processing the batch file. The first > in that command line is for another cmd.exe instance and must be therefore escaped with caret character ^ to be interpreted as literal character by cmd.exe processing the batch file.
Creation of output file
The Windows command processor creates now first the file SIP Rx_Tx.csv in current directory with overwriting an existing file with that name. That could fail if the current directory is write-protected or there is already a file with name SIP Rx_Tx.csv which is write-protected by file system permissions or read-only file attribute. However, it is expected by this batch file that the creation of the output file is successful as the batch file cannot really do the task if the creation of the output file fails for whatever reason.
The output CSV file remains open until the processing of all *.aof files is finished completely preventing other processes running in background to open the CSV file.
It would be also possible to use as third line in the batch file:
for /F "eol=? tokens=1* delims=:" %%G in ('%SystemRoot%\System32\findstr.exe /I /L /C:"SIP Rx" /C:"SIP Tx" *.aof 2^>nul') do for /F "eol=: tokens=2,5,7 delims=|" %%I in ("%%H") do echo %%G;%%I;%%J;%%K>>"SIP Rx_Tx.csv"
That is not so good. It would be necessary to first make sure that the file SIP Rx_Tx.csv does not exist already in current directory, for example from a previous execution, by using the command DEL. But even more problematic is that with this command line the CSV file is handled as follows for each write operation:
- The CSV file is opened by
cmd.exe.
- The file position is set to the end of the file.
- The output line is appended to the file.
- The file is flushed and closed by
cmd.exe.
So on writing hundreds of lines to the CSV file, the CSV file is opened, extended and closed hundreds of times which is not very efficient. It makes it also possible that another process like an anti-virus application running in background opens the CSV file between two write operations for scanning the data in the file preventing cmd.exe to append the next data to the file resulting in some data not written to the CSV file.
So the usage of ( and ) around the entire command line with the two FOR commands and ECHO improves the efficiency on writing the data to the output file and prevents an unwanted loss of data by other processes running in the background and accessing the CSV file too.
Searching with FINDSTR in background for the data of interest
The outer FOR loop should run the command FINDSTR and process the lines output by this command. For that reason the cmd.exe process which is processing the batch file starts now in background without a visible console window one more cmd.exe with option /c and the command line specified within ' appended as additional arguments. The executed command is as follows on Windows being installed in C:\Windows:
C:\Windows\System32\cmd.exe /c C:\Windows\System32\findstr.exe /I /L /C:"SIP Rx" /C:"SIP Tx" *.aof 2>nul
FINDSTR executed by cmd.exe started in background searches now first in current directory for files with file extension .aof in long or short file name. If there is no file matched by this wildcard pattern, then FINDSTR would output to standard error stream (STDERR) an error message which would be captured by cmd.exe processing the batch file and would output the error message to its own standard error stream resulting in getting displayed this error message in the console window if there is a console window opened for cmd.exe processing the batch file. That error message is not wanted and for that reason 2>nul is used to redirect the error message to device NUL to suppress it.
FINDSTR searches in each found file with file extension .aof in current directory
- case-insensitive because of option
/I and
- literally because of option
/L (and also because of using /C: on omitting /L)
- for lines containing either the string
SIP Rx or the string SIP Tx.
The usage of /C: is necessary to get interpreted the space character as literal character to search for and not as OR expression. The usage of "SIP Rx" as search string would result in searching for the string SIP OR the string Rx which is not wanted here. For that reason the two search strings are specified with using twice the option /C:.
It would be also possible to use FINDSTR with the arguments /I /R /C:"SIP [RT]x" *.aof to search with a regular expression for one of the two strings in the lines of each *.aof file with space being interpreted as literal space character.
FINDSTR outputs to standard output stream (STDOUT) of background cmd.exe the lines containing one of the two searched strings with file name without path at beginning separated with a colon from the line in the file.
So there is output for filename1.aof:
filename1.aof:Event|2022-01-31 18:48:30.123049|0|Voice|"SIP Rx"|152/3/1/0|invite sip:]:6200 sip/2.0||
filename1.aof:Event|2022-01-31 18:48:30.123628|0|Voice|"SIP Tx"|152/2/1/0|sip/2.0 183 session progress||
That is the output to process further with the two FOR commands.
There is no error message output by FINDSTR if there is at least one *.aof file and no line found containing one of the two searched strings.
Processing of the found and output lines by outer FOR
The cmd.exe instance processing the batch file captures all lines output to STDOUT of background cmd.exe by FINDSTR and processes them once findstr.exe terminated itself resulting in cmd.exe started in background closed itself.
Empty lines are always ignored by for /F which is no problem here as there are no empty lines output by FINDSTR at all.
There would be split up each captured non-empty line by default into substrings using normal space and horizontal tab character as string delimiters. That line splitting behavior is modified with delims=: to split the line up on colons.
The first substring is checked for beginning with the end of line character which is by default the semicolon. It is very unlikely that a file name of a *.aof file begins with ;, but it is possible. For that reason eol=? is used to change the end of line character to a question mark which no file name can ever contain.
The first substring is assigned to specified loop variable and all other substrings would be ignored by default. That behavior is not wanted here. Everything after first (series of) : should be kept as is without splitting it up. Therefore tokens=1* is used to instruct FOR to assign the first substring (the file name) to the specified loop variable G and everything after the colon (the line) without splitting it up to next loop variable H according to the ASCII table.
Processing of the data by inner FOR
The line from the *.aof file assigned to loop variable H of which file name is assigned to loop variable G must be processed further to get just the data of interest. A second for /F is used for this purpose to process the string assigned to loop variable H.
This time a vertical bar is used as string delimiter because of the *.aof files contain lines with | separated data.
The end of line character is defined with : as the string assigned to loop variable H definitely does not start with a colon. If a line in a *.aof file starts with one or more colons, they would be removed already by the outer FOR command on splitting up the line output by FINDSTR into file name and line.
The data of interest from each line containing SIP Rx or SIP Tx are the date/time in second | separated substring, the found string in fifth data column and the string in seventh data column. For that reason tokens=2,5,7 is used to assign these three substrings to the loop variables I, J and K.
Please note that || or even more vertical bars in series is interpreted by for /F like a single | on using delims=|. So for /F is not capable processing empty data values correct in a CSV file using vertical bar as separator. That is no problem in this case.
Writing the data in wanted format to CSV file
The command ECHO is used to output with a semicolon as separator the file name assigned to loop variable G, the date/time assigned to loop variable I, the found string assigned to loop variable J (with the double quotes) and the additional string assigned to loop variable K to standard output stream of cmd.exe processing the batch file which is redirected to the CSV file being opened all the time during processing the found lines.
The usage of %%~J would result in removing the double quotes from the found strings.
Deletion of the CSV file on being empty
It is possible that the output CSV file SIP Rx_Tx.csv is created in the current directory, but is finally an empty file because of no *.aof file is found in the current working directory or none of the *.aof files contains one of the two searched strings.
The fourth command line checks the existence of the output CSV file in current directory and next its file size and deletes the file on existing, but being empty, i.e. has a file size of 0 bytes.
Restore of initial execution environment
Finally the initial execution environment is restored with execution of command ENDLOCAL which would be implicitly executed by cmd.exe on not being explicitly written into the batch file.
Please read this answer for details about the commands SETLOCAL and ENDLOCAL.
Help on used Windows commands
To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and carefully.
del /?echo /?endlocal /?findstr /?for /?if /?setlocal /?
