Control user application on Microsoft TMG

In many organizations, there is a question how to block unwanted applications (viruses, torrent etc) for the proxy server Microsoft TMG. In this article I show an example of application lock "torrent"  Microsoft TMG.
Background: We have a proxy server Microsoft TMG and it stores logs in Microsoft SQL

1. First, install the server "SQL Server Managment Studio"
2. Connect the "SQL Server Managment Studio" to the SQL server

create a function (convert IP address from HEX to String http://blogs.technet.com/b/isablog/archive/2009/06/04/isa-bpa-7-and-forefront-tmg-for-windows-essential-business-server.aspx) New query - run this:

CREATE FUNCTION [dbo].[fnIpAddressToText]

(

    @Ipv6Address [uniqueidentifier]

)

RETURNS varchar(40) AS

BEGIN

    DECLARE @strInAddress varchar(40)

    DECLARE @strOutAddress varchar(40)

    SET @strInAddress = LOWER(CONVERT(varchar(40), @Ipv6Address))

    SET @strOutAddress = ''

    IF (SUBSTRING(@strInAddress, 10, 4) = 'ffff')

    BEGIN

        -- ipv4 (hex to int conversion)

        DECLARE @IsNum int, @ZERO int, @IsAlpa int

        set @ZERO = ASCII('0')

        set @IsNum = ASCII('9')

        set @IsAlpa = ASCII('a') - 10

        DECLARE @intH int, @intL int

        SET @intH = ASCII(SUBSTRING(@strInAddress, 1, 1))

        IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa

        SET @intL = ASCII(SUBSTRING(@strInAddress, 2, 1))

        IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa

        SET @strOutAddress = CONVERT(varchar(3), @intH * 16 + @intL) + '.'

        SET @intH = ASCII(SUBSTRING(@strInAddress, 3, 1))

        IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa

        SET @intL = ASCII(SUBSTRING(@strInAddress, 4, 1))

        IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa

        SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL) + '.'

        SET @intH = ASCII(SUBSTRING(@strInAddress, 5, 1))

        IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa

        SET @intL = ASCII(SUBSTRING(@strInAddress, 6, 1))

        IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa

        SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL) + '.'

        SET @intH = ASCII(SUBSTRING(@strInAddress, 7, 1))

        IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa

        SET @intL = ASCII(SUBSTRING(@strInAddress, 8, 1))

        IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa

        SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL)

    END

    ELSE

    BEGIN

        -- ipv6

        SET @strOutAddress = @strOutAddress + SUBSTRING(@strInAddress, 1, 4) + ':'

                                        + SUBSTRING(@strInAddress, 5, 4) + ':'

                                        + SUBSTRING(@strInAddress, 10, 4) + ':'

                                        + SUBSTRING(@strInAddress, 15, 4) + ':'

                                        + SUBSTRING(@strInAddress, 20, 4) + ':'

                                        + SUBSTRING(@strInAddress, 25, 4) + ':'

                                        + SUBSTRING(@strInAddress, 29, 4) + ':'

                                        + SUBSTRING(@strInAddress, 33, 4)

    END

    ---- guid sample '6F9619FF-8B86-D011-B42D-FFF34FC964FF'

    RETURN @strOutAddress

END

3. Create rules Microsoft TMG blocking Internet access for the computer set "deny_pcs"

4. Create folder c:\scripts and powershell script on the TMG server. In a script, you must specify:

YourSQL.blogspot.com
SQLport
YourArray

cd c:\scripts

$ServerInstance = "YourSQL.blogspot.com\msfw,SQLport"

$FPCobjs = New-Object -comObject FPC.root

$FPCobj = $FPCobjs.arrays | where {$_.Name -eq "YourArray"}

$Database = "master"

$ConnectionTimeout = 30

$QueryTimeout = 12000

$Query = "declare @dbname nvarchar(255)

declare @db cursor

set @db = CURSOR FOR select name from sys.databases where owner_sid<>0x01

USE tempdb

IF EXISTS (SELECT 1

           FROM [INFORMATION_SCHEMA].[TABLES]

           WHERE TABLE_NAME like '%#SampleTableApps%')

DROP TABLE [tempdb].[dbo].[#SampleTableApps]

CREATE TABLE [tempdb].[dbo].[#SampleTableApps](

[app] [varchar](max) NULL,

[iphex] [uniqueidentifier] NULL,

[username] [varchar](max) NULL

) ON [PRIMARY]

OPEN @db

FETCH NEXT FROM @db into @dbname

WHILE (@@FETCH_STATUS = 0)

BEGIN

declare @Cmd nvarchar(max)

set @Cmd=N'IF (EXISTS (SELECT *

                 FROM ['+@dbname+'].INFORMATION_SCHEMA.TABLES

                 WHERE TABLE_SCHEMA = ''dbo''

                 AND TABLE_NAME = ''FirewallLog''))

   BEGIN

    insert into [tempdb].[#SampleTableApps] (app,iphex,username) select DISTINCT clientagent, SourceIP, ClientUserName from ['+@dbname+'].dbo.FirewallLog

   END

   IF (EXISTS (SELECT *

                 FROM ['+@dbname+'].INFORMATION_SCHEMA.TABLES

                 WHERE TABLE_SCHEMA = ''dbo''

                 AND TABLE_NAME = ''WebProxyLog''))

   BEGIN

    insert into [tempdb].[#SampleTableApps] (app,iphex,username) select DISTINCT clientagent, ClientIP, ClientUserName from ['+@dbname+'].dbo.WebProxyLog

   END'

  exec sp_executesql @Cmd

    FETCH NEXT FROM @db INTO @dbname

END

CLOSE @db

DEALLOCATE @db

select app, [master].[dbo].fnIpAddressToText(iphex) as ip, username from [tempdb].[#SampleTableApps]"

$conn=new-object System.Data.SqlClient.SQLConnection

$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout

$conn.ConnectionString=$ConnectionString

$conn.Open()

$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

[void]$da.fill($ds)

$apps = $ds.Tables[0].Rows

$conn.Close()

if ($apps) {$apps | Export-Csv .\app1.csv -Encoding utf8 -NoTypeInformation}

$user_torrent = $apps | where {$_.app -match "torrent"}

$user_torrent_sort = $user_torrent | Sort username,ip -unique

if ($user_torrent_sort) {$user_torrent_sort | Export-Csv .\app_torrent.csv -Encoding utf8 -NoTypeInformation}

$CS_deny_pcs = $FPCobj.RuleElements.ComputerSets | where {$_.name -eq "deny_pcs"}

foreach ($pc in $CS_deny_pcs.computers) {

$CS_deny_pcs.computers.remove($pc.name)

}

$CS_deny_pcs.computers.save()

foreach ($line in $user_torrent_sort) {

if ($line.ip -notmatch ":") {

$CS_deny_pcs.computers.add($line.username,$line.ip)

}

}

$CS_deny_pcs.computers.save()

5. Now you can schedule the script eg each hour