Converting RRAS Logs Into MySQL Database

Written By: Frank Angiolelli, CISSP
www.fortknoxnetworks.com

This discussion is about how to automatically pull the RRAS files and convert them into a formatted CSV file which can be imported automatically into a SQL database for querying and reporting. There are tools available that provide a manual interface or the capability to open the files and view them manually and discussion of them is outside the scope of this article.

First and foremost, Windows Servers typically log RRAS connections to %windir%\system32\logfiles and in my case, the files were called in12??.log (in1201.log, in1202.log, etc... presumably the 12 is the year and the ?? is the log file number).

Secondly, the format for RRAS logs is multi-line which is somewhat of an irritation, however can be worked around using find and piping the output.

In the case I was reviewing, the client had Server 2003 using Windows Authentication. Your case may be different and this may be a good starting point.

Running the following find command against the logs extracts what for this example are the usable portions of information concerning login.

find /I "Use Windows Authentication" %windir%\system32\logfiles\in*.log

The output will look something like this
"SERVERNAME","RAS",02/14/2012,10:02:14,4,"username",,,"<WAN IP ADDRESS>",,"<LAN IP ADDRESS>", ,"<SERVER IP ADDRESS>",132,,"<SERVER IP ADDRESS>",,132329231734,,5,,1,2,,,0,"311 1 <SERVER IP ADDRESS> 01/26/
2012 13:48:05 123",,,,,1,,,,"204",2,,,,,"275",1,,1,1,"<WAN IP ADDRESS>",,,,,,,,"MS
RASV5.20",311,,"0x00414C4142",4,,"Use Windows authentication for all users",,,,"
MSRAS-0-<REMOTE COMPUTERNAME>","MSRASV5.20"

For those of you not familiar with using the For /f command check out this link.

Now, let's output all that information into a single file which we can use.

find /I "Use Windows Authentication" %windir%\system32\logfiles\in*.log >> raslog.output.csv

Once all of that information is outputted to a single csv file, we can use the information presented, however we will need to extract only the useful parts that we want. It is not helpful to have a csv file with 35 columns of which we need, say 7. No need to re-invent the wheel here, the tokens we want are 1,3,4,6,7,8 and 31.

Let's go get them. Don't forget to adjust to single %'s if you are not calling this from a batch script.

for /f "tokens=1,3,4,6,7,8,31 delims=," %%a in ('find /I "Use Windows authentication" %windir%\system32\logfiles\in*.log') do echo %%a, %%b, %%c, %%d, %%e, %%f, %%g>> raslog.csv

Wonderful. Some suggestions here would be to use your tool of choice to schedule this as a task and have the output file placed in a location where it can be imported easily into your SQL system, unless you import directly from the RRAS server.

Now schedule that into a batch script and execute using your scheduled task operator or other task scheduler user. Now we have useful information. Notice in your output file, there are three lines for each connection and one for each disconnection and the most useful line is in this format.

"SERVER", 02/14/2012, 07:59:44, "username", "WANIP", "Assigned Lan IP", "MSRAS-0-Computername"

Import that data to a SQL database of your choice, create a front end using <insert web stuff here> and you have usable data which can now be aggregated, sorted and queried.

Each scenario and implementation will be different, please feel free to contact me if you would like assistance with your systems. I welcome any comments or improvements, as well as observations of your scenarios.