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.

Using WMIC To Create SOC Based Inventory of Executables

Written by Frank Angiolelli, CISSP
www.fortknoxnetworks.com

Using WMIC To Create SOC Based Inventory of Executables

As many of us know, WMIC is an exceptionally powerful tool for collecting information. When coupled with additional tools, a useful and actionable collection of material can be created that provides visibility across an enterprise.

For example, leveraging WMIC to collect all running processes and inventory that data is an interesting use case.  For those of you unfamiliar with wmic, the following command can get you started (Pro versions of Windows only):

wmic process list brief /format:list

The information presented provides HandleCount, Name, Priority, Process ID, Threadcount and WorkingSetSize. Interesting information to say the least, however without the actual paths, usefulness is limited.
By running the following command, we can discover all the information presentable:

wmic process list /?

This is a lot of information, not all of which is usable. For my purposes, I choose the following information. Name, ProcessID, Executablepath. The Name will show you the executable, the ProcessID is useful information if you need to executable something based on your query and the Executablepath shows you the location of the executable on the system.

wmic process get name,processid,executablepath

Now as we start to move into useful information, we need to edit formatting and output it to a file that again is useful. This can be achieved using the format and output commands.

wmic /output:%computername%.csv process get name,processid,executablepath /format:csv

Now we have an output of a file that is useful for our purposes. When looking at the csv file, you will notice that the first column is called "Node" which indicates the computername variable.

Without going too in depth, this information can be collected, transported or queried from a centralized location and then input into a database engine of your choice. For example, inputting into MySQL can be done with multiple files using a type command for all csv files created/collected and then running something like. I have not used this with all queries run from a single system as of writing of this article, but this seems quite possible.




Again, in my above case, I've ignored three lines because I'm consolidating multiple queries using a simple type command resulting in the first three lines being garbage.

Once all the data is consolidated, running a query inside MySQL for key indicators like 'temp' or 'appdata' becomes simple and fast. In addition, an inventory of system executables like csrss.exe or explorer.exe can report any executable not running in standard Windows Directories.

Building a front end on the system allows for access, queries and reporting based on standardized queries on a daily basis. Build a python based email reporting system and your system is now alerting you to suspicious executables on a daily basis.

In my Watchtower product I have taken this to extremes building transport, consolidation, reporting and front end systems with email reports daily to account for inventories of processes, startup tasks and services captured from multiple endpoints on a daily basis.