3 comments

Forefront UAG (TMG) Remote SQL Logging Database Size

Published on Monday, October 22, 2012 in

A while ago I did a basic install of UAG and enabled both Firewall and Web Proxy logging to SQL. I configured a trunk and published an application. Now one month later I checked the size of the SQL database which holds the logging information. It was 1,4 GB… Not really special, but taking into account that during that month I visited the published application like 5 times or so, it’s a lot…

So just out of curiosity I tried finding out if there were any records being logged which I didn’t care about.

In the database I did a select top 1000 rows and just glared at the rule names:

clip_image002

At first sight I saw a lot of [System] rules. To be honest I really don’t care if my UAG servers are accessing the SQL server configured for logging, or if they contact Active Directory for authentication. So I executed the following query:

image

This would delete all entries related to the logging configured in the TMG System Policy rules. Here’s the size before and after:

Before:

clip_image002[4]

After:

clip_image002[6]

So I only won like 122 MB. Not really as much as I’d expect. After looking at the SQL data some more I executed this query:

image

This would delete all events logged because the request was denied by the “default deny” rule in TMG. Now the database lost another 880 MB! Now we’re talking!

clip_image002[8]

So it seems to me that a large amount of data is related to the “default deny” rule in TMG. If you feel like you don’t need this information, you could disable the logging for this rule in the TMG console:

clip_image002[10]

However this seems to be impossible:

clip_image004

There’s some articles explaining a way around this, but I don’t like those. In troubleshooting times you might want to have logging enabled again for this rule. I feel more like setting a checkbox then start importing and exporting the configuration of a TMG server in a production environment. Here’s an example of an article explaining how to alter the default rule: ISAServer.org: Tweaking the configuration of Forefront TMG with customized TMG XML configuration files

So what I did, for now, is to create my own rule which is positioned just in front of the default deny rule. It’s almost exactly the same, but it has logging disabled:

clip_image006

Whether or not this is a good Idea you have to see for yourself. It all depends what you want the logs for. If you want to have statistics about your published applications this might be fine. If you want statistics about the amount of undesired traffic hitting your UAG’s you might want the default behavior. If you still feel you need some but not all of the default deny rule logging here’s some additional idea’s: configure a deny rule in front of the rule we just created, but now configure it to actually log requests. In this rule you can specify to only log requests concerning HTTP(S) traffic. Or only log requests hitting the external interface. This would avoid all the chitchat which is happing on the LAN side.

Related Posts

3 Response to Forefront UAG (TMG) Remote SQL Logging Database Size

Anonymous
23 October, 2012 17:30

Hi Thomas,

You can disable logging for any/all system policy rules programmatically by following the instructions detailed in this article:

http://tmgblog.richardhicks.com/2012/07/05/disable-logging-on-system-policy-rules-in-forefront-tmg-2010/

In addition, you can also use this method to disable logging for the default rule by altering the code as follows:

Array.ArrayPolicy.PolicyRules.Item("default rule")

Enjoy!

23 October, 2012 20:50

Hey Richard!

Thanks for taking the time to post back. To be honest this post has been sitting in my drafts folder for quit some time. Back at the time I dont think you posted that already or I would have found it on google :) I would choose your approach any time over the one I found at isaserver.org.

Kind regards,
Thomas

04 June, 2013 14:11

After reading your post i also check the size of my SQL and found same thing you mention above in your post. I don't know what's the reason behind this but forefront uag is working fine.

Add Your Comment