Reporting on Forefront TMG’s Remote SQL Database

Posted June 12, 2012









Microsoft Forefront TMG 2010 supports a variety of different logging methods. Fastvue TMG Reporter supports the SQL Express, W3C Extended, and Remote SQL Server log methods.

When logging to SQL Express or W3C text files, you need to install the Fastvue Arbiter on your TMG Server so that the Fastvue Server can securely access the on-box log files without the need to open network access to the databases, or create file shares for the text logs (bad firewall practice).

If you have configured Forefront TMG to log to a separate SQL database (and there are some great reasons to do so), you do not need to install the Fastvue Arbiter on the TMG Server as the logs are now stored off-box.

The method of adding your remote SQL Database a source in TMG Reporter is slightly different.

Adding an SQL Source

Once Fastvue TMG Reporter is installed, browse to the site and ignore the Start screen asking you to enter the name of a TMG server. Instead, proceed to the Settings tab.

On the Sources page, click Add Source. You will notice a new SQL Server option.

Adding TMG's Remote SQL Server Log as a Source

Adding TMG’s Remote SQL Server Logs as a Source

Enter the credentials required to connect to your SQL Server, and select the database and tables that Forefront TMG is logging to.

Note: If you are using Windows Authentication, make sure the machine account (domain\machine$) of the Fastvue TMG Reporter server has read access to the database.

Tip: If you have more than one Web Proxy or Firewall table, you can enter wildcards such as *Web* to select MyWebProxy1, MyWebProxy2 etc.  Or just enter * and TMG Reporter will check every table in the Database.

Click Add SQL Server and you should soon see records being imported into the source. You can then proceed to the Dashboard or Alerts tab to view live information. You can also run reports on the Reports tab, but check the source to see which dates are available for reporting on.

Enable/Disable TMG Servers

You will also notice a ‘Manage Servers’ tab on your new SQL Source. Click this tab to view a list of all the TMG Servers found to be logging to the database. You can enable and disable importing from these servers as required.

The number of TMG Servers logging to the remote SQL Database

The number of TMG Servers logging to the remote SQL Database

Note: TMG Reporter’s licensing is based on the number of TMG Servers you need to monitor. Take a look at the servers in this list before purchasing.

Done!

That’s it! You should now be monitoring all Forefront TMG Servers logging to your remote SQL Database.

Please let us know if you have any questions or issues. We’re here to help!


Posted in , , , , ,

Scott Glew

Co-founder, Chief Product Officer and Frontend Developer at Fastvue.

about.me/scottglew
Follow me on twitter

Discussion

2 Comments
  1. Hi,

    I’ve installed the latest dev build, but I do not have the Remote SQL option. I can only add a TMG server

    • Hi Alireza,

      Thanks for letting us know. When you installed the latest dev build, did you specify a different virtual directory or site? Can you open IIS and see if there are two versions of the Fastvue site installed? Make sure you are browsing to the correct URL for the site and virtual directory that you installed the dev version to.

      Also, hard refresh your browser with ctrl + F5 (cmd + R on Mac) to clear the browser’s cache for the site.

      Let me know how you go!

      Cheers
      Scott

Leave a Response

If you have a question or comment, or just need to get in touch, please use the form below.