Using UAG SQL logging or data retrieval

  • Article

In a previous article, I discussed querying the TMG log directly, which can be useful if one wants to create a complex query to perform advanced data-mining on the log. The same need might apply to a UAG server. Normally, UAG doesn’t log its data into the SQL service used by TMG, but with a simple configuration, this can be enabled. This is described in the following Technet article:

https://technet.microsoft.com/en-us/library/dd897065.aspx

When fiddling with logging settings, there are several things to keep in mind:

1. By default, UAG logs everything into text files stored in <UAG Path>\Logs\Events. These are XML based messages which are queried by UAGs Web Monitor whenever you use it to display data. The logs will accumulate up to a total size of 1.22 GB, and then UAG will purge older files to keep the folder from filling up the hard drive.

2. You can use the registry, as described here, to allow the logs to occupy more space, if you like. The registry key that controls this is HKEY_LOCAL_MACHINE\SOFTWARE\WhaleCom\e-Gap\von\MonitorMgr\MaxLogSizeMB

3. If you enable the SQL logging as described in TechNet, it doesn’t disable the text logging. To disable text logging, use the Admin/Event Logging and uncheck “Enable Build-in reporting”:

clip_image002

4. Note that if you disable the built-in reporting, you will lose the ability to use the Web Monitor. It will be able to query old data still in existing text files, but it will *not* be able to query the data stored in SQL

If you enable UAG to log into SQL, UAG will hook into the Web Proxy log (which, on a regular TMG server be used to store info about the Web Proxy functionality of TMG, which is not in use with UAG). As described in the article I mentioned earlier, you can query the local SQL Express by connecting to the database localhost\msfw. The tables used by UAG are named ISALOG_<DATE>_WEB_000, with <DATE> reflecting the log date. For example, the table ISALOG_20120926_WEB_000 would be for September 26th 2012. Note that like the TMG firewall log, the UAG log would also only be kept for the past 7 days.

There are many things that might be of interest in the tables. For example, if you want to track application usage, use a query such as this:

Select * from ISALOG_20120926_WEB_000 where UAGtype=’application’ and UAGseverity=’information’

Then, extracting info from the following fields/columns will probably be most useful:

GMTlogtime                 - this would show WHEN the application was accessed

Rule                               - this would list the application’s name

ClientUserName          - this would list the user who accessed the application.

FilterInfo                       - This would list whether the application was “ACCESSED” or “EXITED”

UagSessionID               - this would list the unique session ID associated with this application access

Beyond that, you can crunch the data to figure out derivative info. For example, querying the FilterInfo column for the string ACCESSED would show lines pertaining to people running a certain application. Counting the lines that result from such a query would show how many people accessed a certain application during the log period. You can also retrieve lines showing EXITED and use this to calculate concurrent session count or average session time.