I typically have two issues that I run into with our Forefront TMG (aka ISA 2010) proxy server:
- I have certain blocks of IP addresses blocked on the Cisco firewall and occasionally a user will repeatedly hit a website that sits in one of those blocks. I will then have to find out who is doing this as the firewall will always just report the address of the proxy server for the traffic.
- Occasionally a user will download malware and I’ll need to find out where they got it from so that the source of the traffic can go into the block list in point #1.
Either way I end up running a report on a system at the site which has SQL Server Management Studio installed on it with a query something like the below:
WHERE [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog].[DestHostIP] = ‘d3bd0e17-FFFF-0000-0000-000000000000’
You’ll note that the address on the bottom line is rather unintelligible since it uses a semi-IPv6 format used by TMG. To work around this I had been doing hand translation using the calculator in Windows to figure out what the address I am looking for should be. However, I grew tired of that and wrote a program that will handle the translation/conversion to and from a regular IPv4 address and Forefront TMG 2010 address (because of some apparent vague interest, I’ve packed a zip with just the executable (no code) here).
I want to note though, that since it was casually put together I didn’t code it to handle any exceptions. The source code is included though, so if you want to code around some fat fingering then feel free.
Last year the company I work for decided to begin using the Positive Pay process offered by Key Total Treasury*. The issue we had when we were looking to implement was that we had three separate bank accounts and no easy way to program an export within each system to match the format needed for the Positive Pay file used by Key Bank. Fortunately though, we could get CSV (comma delimited) check reports rather easily out of each system. Although I knew next to nothing about programming in any of the three systems that we use, I knew that I could cobble something together in Visual Basic that could translate, or convert, the CSV file formats to the semi-proprietary fixed width format required by Key Bank. After a period of use, I requested permission to make a more generic version of our translator programs that others might make use of. In my spare time I acquired enough knowledge about XML programming to put together the program below that uses an XML initialization file:
http://cid-f206759e5151f410.skydrive.live.com/self.aspx/PositivePayFiles/positive%20pay%20translator.zip (requires a Live ID I think)
The 2.0 version of the .Net runtime is required, and also although it’s not required I would recommend compiling your own version from the source due to the sensitive nature of the information that the program handles (otherwise the executable is under the ‘bin’ folder). If you do not have Visual Studio, you can download the free Express version. If you do not use Windows, Mono’s migration tool states that the program should run under the Mono framework, though I have no experience in that area.
When exporting a CSV from your system the fields will need to be in the following order (unless you change the ‘Read_Exported_File’ method in the program):
- The first field is the check number
- The second field is the check date
- The third field is the check amount
- The fourth field is the void code
- The fifth field is the vendor name
With the program is an example of the XML configuration file (ppaysettings.xml) which has the following options:
- AcctName – generally the bank’s name. It is used for operator verification
- AcctNum – the bank account number that will appear in the positive pay file
- VoidCode – string code in the CSV that designates that a check has been voided (can be blank)
- NonVoidCode – string code in the CSV that designates that a check has NOT been voided (can be blank)
- OmitPayee – do not put the customer name in the Positive Pay file. Required for checks printed with dot matrix printers (True or False only, not blank)
In the not too distant future I should have a more fully vetted version along with some exception conditions that would be related to how wire transfers are handled.
As a note, this version drops checks that are canceled in the same run. This is typically due to a printer malfunction and since the check is never cut, it is never verified against the positive pay data anyway.
This is an early build and may not be bug free as I haven’t tested it fully. The information in the Paysettings.xml file is not required, but the file itself is at this time.
*Key Bank/Key Total Treasury does not endorse anything to do with this program