Configuring TMG logging to a remote SQL server
why would you do that ?
we may have so many reasons to uses remote SQL instead of the local SQL express on the TMG box from a security point of view you many need this so that not all your eggs in one basket and from operational point of view real time monitoring you don’t have to wait one day so that you know what is happening in your TMG some will say the query in TMG will do that , but if you used the query for a bit of time you will notice that its data is a bit overwhelming
note :when you choose to use SQL as logging you can not use the built-in reports you have to create your own reports , this is until MS release sample reports to be uploaded to the report server
so in this post I will be configuring TMG to use SQL and how to create a basic report on your new database
lets start with
Setting up SQL Server for logging by creating the database and upload TMG schema to it
Create New Database
Give the database TMGfirewallog (you can name it anything )
after creating the database we copy the sample database script from the TMG installation directory to the SQL server
C:\Program Files\Microsoft Forefront Threat Management Gateway\fwsrv.sql
this is the original command line
sqlcmd -E -S InstanceName -i “Path\ScriptFile” -d <dbName>
but because I am running the default sql
sqlcmd /E /i c:\fwsrv.sql /d TMGfirewalllog
now SQL database ready to receive TMG firewall LOG
we switch to the TMG server
go to log and reports
open configure firewall logging
we select sql database and press options
we configure the SQL server , database name and the account that TMG will use to connect to the SQL
press test you will get error and prompt from TMG to create the rule to allow the access to the SQl after you receive this press ok and apply the TMG configuration
now the data flow from the TMG to your SQL server
to test that TMG connect correctly to the SQL server go to SQL TMG database and edit the first 200 row
(we should do the same for the web proxy also
Reading the Logs
Now you can open your file (using Microsoft Excel for example) and you will be able to easily examine the data. You can use the Web Proxy Log Fields article for more information on each field within this table, plus the following ones:
· Object source http://technet.microsoft.com/en-us/library/cc441680.aspx
· Return code http://technet.microsoft.com/en-us/library/cc441734.aspx
· Cache status http://technet.microsoft.com/en-us/library/cc441710.aspx
One thing you may notice as you examine the log data is that the ClientIP field does not display an IP address as you would see it in the Forefront TMG log viewer.
The reason why the client IP address is stored using this format is because of the need to store IPV4 and IPV6 addresses in the same field type. Because IPv4 and IPv6 addresses are represented using different-sized data and we only have one field, Forefront TMG encodes the IPv4 address in a special way. In order to convert this value into a dotted-decimal IPv4 format, you need to convert it.
In the example above, C0A8019A-ffff-0000-0000-000000000000 represents an IPv4 address. In order to convert this value to dotted-decimal form:
· Separate the first eight digits into hexadecimal pairs, such as C0, A8, 01, 9A
· Convert these values to their decimal equivalent::
C0 = 192
A8 = 168
01 = 1
9A = 154
· Concatenate the decimal values in the order they appear using periods “.”: 192.168.1.154
In summary, we store the IPv4 in the following format: xxxxxxxx-ffff-0000-0000-000000000000. To automate this conversion process, you can create a new column on Excel called ClientIPV4Converted (or whatever name you prefer) and write the following formula on this cell (B2):
=HEX2DEC(MID(A17,2)) &”.”&HEX2DEC(MID(A17,3,2))&”.”&HEX2DEC(MID(A17,5,2))&”.”&HEX2DEC(MID(A17,7,2))
or you can use to translate the two at a time http://www.parkenet.com/apl/HexDecConverter.html
of course you can add this statement to covert this in your SQL report but this another post another day 
so let us customize a report
now we have the log on the SQL we need to create a report , one of the most common questions I receive that I need to know who is downloading and consuming my internet bandwidth right now
I will be creating a report the demonstrate that using SQL reporting
we start by opening SQL reports Home and lunch report builder
Microsoft did a great job in the report builder you don’t have to know code to be able to use it
we select chart wizard
new dataset
new datasource
press new and give it a name
we select the SQL server that host our firewall database and select our TMGfirwallLOG database
now we have the data source
press next
now we select what we need in our report
we need
- client originalip
- LOGtime
- bytesrecvdelta (you can use bytesrecvd but for our example we will us the delta )
so we select those
we change bytesrecvddelta to Sum -we need the total – and to remove the echo’s we also apply filter to remove the 0 value witch TMG insert it a lot
press next
we select a line (we can change that after )
press next
insert the fields into its location needed to be in the chart -does not matter we will change it anyway after we finish –
now you should arrive at a windows look like this
if not adjust the fields until you see exactly what you see
now we run the report
see how easy it is to create report
note : this was a new database(this is the entire database content ) so in order to make it the last hour or day you have to add perimeter on the report to see only the past day or hour for example
try to play with the report builder you will be surprised
if you interested more
Firewall Log Fields
http://msdn.microsoft.com/en-us/library/dd435804.aspx
Setting up SQL Server for logging
http://technet.microsoft.com/en-us/library/bb794867.aspx
Configuring logging to a remote SQL server
http://technet.microsoft.com/en-us/library/dd441079.aspx
