We upgraded to Forefront TMG (quite some time ago) and we needed a way of automatically exporting a summary of the log information into an SQL database of our own format, as the output from TMG was a bit bloated and I didn’t want to waste too much space on data I didn’t need.
Here is my solution: (I can’t take credit for all of the work as I did do a bit of Googleing and unfortunately now can’t remember where I got some of this from, If you recognise any of the code let me know and I will cite it appropriately)
Here is some SQL to setup the required database objects.
USE [ISA Server] --Rename this to the database you want to use or create a new database called 'ISA Server'
GO
/****** Object: UserDefinedFunction [dbo].[Parse_For_Domain_Name] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Parse_For_Domain_Name] (
@url nvarchar(255)
)
returns nvarchar(255)
AS
BEGIN
declare @domain nvarchar(255)
-- Check if there is the "http://" in the @url
declare @http nvarchar(10)
declare @https nvarchar(10)
declare @protocol nvarchar(10)
set @http = 'http://'
set @https = 'https://'
declare @isHTTPS bit
set @isHTTPS = 0
select @domain = CharIndex(@http, @url)
if CharIndex(@http, @url) > 1
begin
if CharIndex(@https, @url) = 1
set @isHTTPS = 1
else
select @url = @http + @url
end
if CharIndex(@http, @url) = 0
if CharIndex(@https, @url) = 1
set @isHTTPS = 1
else
select @url = @http + @url
if @isHTTPS = 1
set @protocol = @https
else
set @protocol = @http
if CharIndex(@protocol, @url) = 1
begin
select @url = substring(@url, len(@protocol) + 1, len(@url)-len(@protocol))
if CharIndex('/', @url) > 0
select @url = substring(@url, 0, CharIndex('/', @url))
declare @i int
declare @prev int
set @i = 0
while CharIndex('.', @url) > 0
begin
select @i = CharIndex('.', @url)
select @url = stuff(@url,@i,1,'/')
end
select @url = stuff(@url,@i,1,'.')
set @i = 0
while CharIndex('/', @url) > 0
begin
select @i = CharIndex('/', @url)
select @url = stuff(@url,@i,1,'.')
end
select @domain = substring(@url, @i + 1, len(@url)-@i)
if len(@domain) <= 6
Begin
set @i = 0
set @prev = 0
while CharIndex('.', @url) > 0
begin
Set @prev = @i
select @i = CharIndex('.', @url)
select @i = CharIndex('.', @url, @i)
select @url = stuff(@url,@i,1,'/')
end
--select @url = stuff(@url,@i,1,'.')
select @url = stuff(@url,@prev,1,'.')
set @i = 0
set @prev = 0
while CharIndex('/', @url) > 0
begin
Set @prev = @i
select @i = CharIndex('/', @url)
select @url = stuff(@url,@i,1,'.')
end
--select @domain = substring(@url, @i + 1, len(@url)-@i)
select @domain = substring(@url, @prev + 1, len(@url)-@prev)
End
end
return @domain
END
GO
/****** Object: Table [dbo].[tbl_ISA_ResultCodes] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ISA_ResultCodes](
[Result Code] [int] NULL,
[Description] [varchar](250) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_ISA_Log] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ISA_Log](
[Date] [date] NULL,
[ClientUserName] [nvarchar](514) NULL,
[SplitDestHost] [varchar](255) NULL,
[protocol] [varchar](13) NULL,
[transport] [varchar](8) NULL,
[rule] [nvarchar](128) NULL,
[SrcNetwork] [nvarchar](128) NULL,
[DstNetwork] [nvarchar](128) NULL,
[processingtime] [int] NULL,
[bytesrecvd] [bigint] NULL,
[bytessent] [bigint] NULL,
[Count] [int] NULL,
[resultcode] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_Files] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Files](
[FileID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [varchar](50) NOT NULL,
[Processed] [bit] NULL,
[Date] [datetime] NULL,
CONSTRAINT [PK_tbl_Files] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[vw_ISA_Log] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_ISA_Log]
AS
SELECT TOP (100) PERCENT Convert(Datetime,IL.Date) as [Date], IL.ClientUserName, IL.SplitDestHost, IL.protocol, IL.transport, IL.[rule], IL.SrcNetwork, IL.DstNetwork, IL.processingtime, IL.bytesrecvd,
IL.bytessent, IL.Count, IL.resultcode, IRC.Description
FROM dbo.tbl_ISA_Log AS IL LEFT OUTER JOIN
dbo.tbl_ISA_ResultCodes AS IRC ON IL.resultcode = IRC.[Result Code]
ORDER BY IL.Date, IL.ClientUserName
GO
/****** Object: StoredProcedure [dbo].[sp_RecreateView] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_RecreateView]
AS
BEGIN
SET NOCOUNT ON;
Declare @Name varchar(128), @vchUnion varchar(4000), @vchDrop varchar(4000), @vchCreate varchar(4000)
Set @vchUnion = ''
--Declare cursor
DECLARE curProxyLogs CURSOR FOR
SELECT [name]
FROM [master].[sys].[databases]
Where name Like 'ISALOG_%'
--Open cursor
OPEN curProxyLogs
--Get values from cursor and set variable values
FETCH NEXT FROM curProxyLogs
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
/*Add Union if not the first Database name record*/
if (@vchUnion <> '')
Begin
Set @vchUnion = @vchUnion + 'Union' + char(13) + char(10)
End
/*Add Select for current Database name record*/
Set @vchUnion = @vchUnion + 'Select * From [' + @name + '].[dbo].[WebProxyLog]' + char(13) + char(10)
if not exists(Select * From tbl_Files Where FileName = @Name)
Begin
Insert Into tbl_Files(FileName)
Values (@name)
End
FETCH NEXT FROM curProxyLogs
INTO @Name
END
CLOSE curProxyLogs
DEALLOCATE curProxyLogs
Set @vchDrop = 'IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''[dbo].[vw_WebProxyLog]''))
DROP VIEW [dbo].[vw_WebProxyLog]'
Set @vchCreate = 'CREATE VIEW [dbo].[vw_WebProxyLog]
AS
' + @vchUnion
Exec (@vchDrop)
Exec (@vchCreate)
END
GO
/****** Object: StoredProcedure [dbo].[sp_InsertSummary] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertSummary]
AS
BEGIN
SET NOCOUNT ON;
Insert Into [ISA Server].dbo.[tbl_ISA_Log]
Select
[DATE],
[ClientUserName],
Case URLDestHost
When '-' Then [ISA Server].dbo.Parse_For_Domain_Name(DestHost)
Else [ISA Server].dbo.Parse_For_Domain_Name(UrlDestHost)
End as SplitDestHost,
[protocol],
[transport],
[rule],
[SrcNetwork],
[DstNetwork],
SUM([processingtime]) [processingtime],
SUM([bytesrecvd]) [bytesrecvd],
SUM([bytessent]) [bytessent],
Sum([Count]) [Count],
resultcode
From
(
SELECT
convert(date,logTime) AS [Date],
[ClientUserName],
[DestHost],
[UrlDestHost],
[protocol],
[transport],
[rule],
[SrcNetwork],
[DstNetwork],
SUM([processingtime]) [processingtime],
SUM([bytesrecvd]) [bytesrecvd],
SUM([bytessent]) [bytessent],
COUNT([ClientUserName]) [Count],
resultcode
FROM [ISA Server].[dbo].[vw_WebProxyLog] Proxy
Where
[SrcNetwork] <> 'Local Host'
Group By
logTime,
[ClientUserName],
[DestHost],
[UrlDestHost],
[protocol],
[transport],
[rule],
[SrcNetwork],
[DstNetwork],
resultcode
) List
Group By
[Date],
Case URLDestHost
When '-' Then [ISA Server].dbo.Parse_For_Domain_Name(DestHost)
Else [ISA Server].dbo.Parse_For_Domain_Name(UrlDestHost)
End,
[ClientUserName],
[protocol],
[transport],
[rule],
[SrcNetwork],
[DstNetwork],
resultcode
END
GO
/****** Object: StoredProcedure [dbo].[sp_DropDBs] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DropDBs]
AS
BEGIN
SET NOCOUNT ON;
Declare @Name varchar(128), @vchDetach varchar(4000)
Set @vchDetach = ''
--Declare cursor
DECLARE curProxyLogs CURSOR FOR
SELECT [name]
FROM [master].[sys].[databases]
Where name Like 'ISALOG_%'
--Open cursor
OPEN curProxyLogs
--Get values from cursor and set variable values
FETCH NEXT FROM curProxyLogs
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
/*Add Select for current Database name record*/
Set @vchDetach = @vchDetach + 'exec sp_detach_db ' + @name + char(13) + char(10)
if not exists(Select * From tbl_Files Where FileName = @Name)
Begin
Insert Into tbl_Files(FileName)
Values (@name)
End
if exists(Select * From tbl_Files Where FileName = @Name)
Begin
Update tbl_Files
Set Processed = 1
Where FileName = @Name
End
FETCH NEXT FROM curProxyLogs
INTO @Name
END
CLOSE curProxyLogs
DEALLOCATE curProxyLogs
Exec (@vchDetach)
END
GO
/****** Object: Default [DF_tbl_Files_Processed] ******/
ALTER TABLE [dbo].[tbl_Files] ADD CONSTRAINT [DF_tbl_Files_Processed] DEFAULT ((0)) FOR [Processed]
GO
/****** Object: Default [DF_tbl_Files_Date] ******/
ALTER TABLE [dbo].[tbl_Files] ADD CONSTRAINT [DF_tbl_Files_Date] DEFAULT (getdate()) FOR [Date]
GO
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(0, 'The operation completed successfully.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(200, 'OK.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(201, 'Created.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(202, 'Accepted.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(204, 'No content.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(301, 'Moved permanently.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(302, 'Moved temporarily.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(304, 'Not modified.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(400, 'Bad request.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(401, 'Unauthorized.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(403, 'Forbidden.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(404, 'Not found.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(500, 'Server error.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(501, 'Not implemented.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(502, 'Bad gateway.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(503, 'Out of resources.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(995, 'Operation aborted.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(10060, 'A connection timed out.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(10061, 'A connection was refused by the destination host.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(10065, 'No route to host.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(11001, 'Host not found.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12201, 'A chained proxy server or array member requires proxy-to-proxy authentication. Please contact your server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12202, 'The Forefront TMG denied the specified Uniform Resource Locator (URL).')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12204, 'The specified Secure Sockets Layer (SSL) port is not allowed. Forefront TMG is not configured to allow SSL requests from this port. Most Web browsers use port 443 for SSL requests.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12206, 'The Forefront TMG detected a proxy chain loop. There is a problem with the configuration of the Forefront TMG routing policy. Please contact your server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12207, 'Forefront TMG dial-out connection failed. The administrator should manually dial the specified phonebook entry to determine if the number can be reached.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12208, 'Forefront TMG is too busy to handle this request. Reenter the request or renew the connection to the server (now or at a later time).')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12209, 'The Forefront TMG requires authorization to fulfill the request. Access to the Web Proxy filter is denied.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12210, 'An Internet Server API (ISAPI) filter has finished handling the request. Contact your system administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12211, 'Forefront TMG requires a secure channel connection to fulfill the request. Forefront TMG is configured to respond to outgoing secure (Secure Sockets Layer (SSL)) channel requests.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12213, 'Forefront TMG requires a client certificate to fulfill the request. A Secure Sockets Layer (SSL) Web server, during the authentication process, requires a client certificate.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12214, 'An Internet Server API (ISAPI) filter caused an error or terminated with an error.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12215, 'The size of the request header is too large. Contact your Forefront TMG administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12216, 'The size of the response header is too large. Contact your Forefront TMG administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12217, 'The request was rejected by the HTTP filter. Contact your Forefront TMG administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12218, 'Forefront TMG cannot handle your request because the DNS quota was exceeded. Contact your Forefront TMG administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12219, 'The number of HTTP requests per minute exceeded the configured limit. Contact your Forefront TMG administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12221, 'The client certificate used to establish the SSL connection with the Forefront TMG computer is not trusted.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12222, 'The client certificate used to establish the SSL connection with the Forefront TMG computer is not acceptable. The client certificate restrictions not met.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12224, 'The SSL server certificate supplied by a destination server is not yet valid.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12225, 'The SSL server certificate supplied by a destination server expired.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12226, 'The certification authority that issued the SSL server certificate supplied by a destination server is not trusted by the local computer.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12227, 'The name on the SSL server certificate supplied by a destination server does not match the name of the host requested.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12228, 'The SSL certificate supplied by a destination server cannot be used to validate the server because it is not a server certificate.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12229, 'The Web site requires a client certificate, but a client certificate cannot be supplied when HTTPS inspection is applied to the request.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12230, 'The SSL server certificate supplied by a destination server has been revoked by the certification authority that issued it.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12234, 'The traffic was blocked by IPS.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12235, 'Web traffic was blocked for a rule with URL filtering enabled because the URL filtering database is not available.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12236, 'Download failed because a third-party Web content filter does not support downloads that exceed 4GB.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12238, 'Download failed because the Compression filter does not support downloads that exceed 4GB.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12239, 'Request failed because the size of the request body is too large.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12301, 'A chained server requires authentication. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12302, 'The server denied the specified Uniform Resource Locator (URL). Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12304, 'The specified Secure Sockets Layer (SSL) port is not allowed. Forefront TMG is not configured to allow SSL requests from this port. Most Web browsers use port 443 for SSL requests.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12306, 'The server detected a chain loop. There is a problem with the configuration of the server routing policy. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12307, 'The dial-out connection failed. The dial-out connection failed with the specified phonebook entry. The administrator should manually dial the specified phonebook entry to confirm that the problem is not the Windows auto-dial facility.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12308, 'The server is too busy to handle this request. Reenter the request or try again later.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12309, 'The server requires authorization to fulfill the request. Access to the Web server is denied. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12310, 'An Internet Server API (ISAPI) filter has finished handling the request. Contact your system administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12311, 'The page must be viewed over a secure channel (Secure Sockets Layer (SSL)). Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12313, 'The page requires a client certificate as part of the authentication process. If you are using a smart card, you will need to insert your smart card to select an appropriate certificate. Otherwise, contact your server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12314, 'An Internet Server API (ISAPI) filter caused an error or terminated with an error.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12315, 'The size of the request header is too large. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12316, 'The size of the response header is too large. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12317, 'The request was rejected by the HTTP filter. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12318, 'Forefront TMG cannot handle your request because the DNS quota was exceeded. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12319, 'The number of HTTP requests per minute exceeded the configured limit. Contact the server administrator.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12320, 'Forefront TMG is configured to block HTTP requests that require authentication.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12321, 'The client certificate used to establish the SSL connection with the Forefront TMG computer is not trusted.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12322, 'The client certificate used to establish the SSL connection with the Forefront TMG computer is not acceptable. The client certificate restrictions not met.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12323, 'Authentication failed. The client certificate used to establish an SSL connection with the Forefront TMG computer does not match the user credentials that you entered.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12334, 'The traffic was blocked by IPS.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12336, 'Download failed because a third-party Web content filter does not support downloads that exceed 4GB.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12337, 'Download failed because the Link Translation filter does not support downloads that exceed 4GB.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12338, 'Download failed because the Compression filter does not support downloads that exceed 4GB.')
Insert Into [ISA Server].[dbo].[tbl_ISA_ResultCodes] Values(12339, 'Request failed because the size of the request body is too large.')
Here is a vbscript which moves the database files from the TMG server to the sql box and then processes them for you.
You will need to replace any values like #NAME# with your own values, hopefully the names a descriptive enough.
Const adCmdStoredProc = 4
Const adCmdText = 1
Const ForAppending = 8
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ADS_SERVICE_STOPPED = 1
Const ADS_SERVICE_RUNNING = 4
Dim fso, fldr, fil, fc, rootPath, destPath, sqlPath, strDBConn, intSleepValue, objConnection, strFileDate
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objShell = CreateObject("WScript.Shell")
intSleepValue = 1000 '1 second
strFileDate = "_" & Cstr(DatePart("yyyy",Now())) & Right("0" & Cstr(DatePart("m",Now())),2) & Right("0" & Cstr(DatePart("d",Now())),2) & "_"
sISAServer = "#TMG_SERVER_NAME#"
sSQLExpressService = "MSSQL$MSFW"
rootPath = "\\#TMG_SERVER_IP#\c$\Program Files\Microsoft Forefront Threat Management Gateway\Logs"
strDBConn = "Provider=sqloledb;packet size=4096;user id=#SQL_USER_NAME#;data source=#SQL_SERVER_INSTANCE#;persist security info=False;initial catalog=#SQL_DATABASE#;password=#SQL_USER_PASSWORD#"
destPath = "#UNC_SHARE_ON_SQL_SERVER#" '\\sqlserver\c$\isa
sqlPath = "#LOCAL_PATH_ON_SQL_SERVER#" 'C:\isa
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(rootPath)
Set fc = fldr.Files
WriteToLog(rootPath)
If fc.Count < 1 Then
'Wscript.Echo "No files found."
WriteToLog("No files found.")
Else
If fc.Count = 1 Then
'Wscript.Echo "1 file found."
WriteToLog("1 file found.")
Else
'Wscript.Echo fc.Count & " files found."
WriteToLog(fc.Count & " files found.")
End If
End If
If StopService(sSQLExpressService, sISAServer) Then
'WScript.Echo sSQLExpressService & " stopped"
WriteToLog(sSQLExpressService & " stopped")
Else
'WScript.Echo "Failed to connect to the service " & sSQLExpressService
WriteToLog("Failed to connect to the service " & sSQLExpressService)
End If
For Each fil in fc
If (lcase(Right(fil.Name, 3)) = "mdf" or lcase(Right(fil.Name, 3)) = "ldf") And instr(lcase(fil.Name),"_web_") > 0 And instr(lcase(fil.Name), strFileDate) = 0 then
If not FileProcessed(left(fil.Name, len(fil.name)-4)) Then
If Not fso.FileExists(destPath & "\" & fil.Name) Then
WriteToLog(fil.Name)
'WScript.Echo fil.Name
fil.Copy(destPath & "\" & fil.Name)
End If
End If
End If
Next
If StartService(sSQLExpressService, sISAServer) Then
'WScript.Echo sSQLExpressService & " started"
WriteToLog(sSQLExpressService & " started")
Else
'WScript.Echo "Failed to connect to the service " & sSQLExpressService
WriteToLog("Failed to connect to the service " & sSQLExpressService)
End If
ProcessFiles()
BuildView()
InsertSummary()
UnmountDBs()
Set fldr = fso.GetFolder(destPath)
Set fc = fldr.Files
WriteToLog(destPath)
If fc.Count < 1 Then
'Wscript.Echo "No files found."
WriteToLog("No files found.")
Else
If fc.Count = 1 Then
'Wscript.Echo "1 file found."
WriteToLog("1 file found.")
Else
'Wscript.Echo fc.Count & " files found."
WriteToLog(fc.Count & " files found.")
End If
End If
For Each fil in fc
If (lcase(Right(fil.Name, 3)) = "mdf" or lcase(Right(fil.Name, 3)) = "ldf") And instr(lcase(fil.Name),"_web_") > 0 then
If FileProcessed(left(fil.Name, len(fil.name)-4)) Then
fil.Delete
End If
End If
Next
Sub ProcessFiles()
Dim fso3, fldr3, fil3, fc3
Set fso3 = CreateObject("Scripting.FileSystemObject")
Set fldr3 = fso.GetFolder(destPath & "\")
Set fc3 = fldr3.Files
For Each fil3 in fc3
If lcase(Right(fil3.Name, 3)) = "mdf" AND (Not FileProcessed(left(fil3.Name, len(fil3.name)-4))) Then
ProcessLog(fil3.Name)
WriteToLog("File: " & fil3.Name & " processed.")
End If
Wscript.Sleep(intSleepValue)
Next
End Sub
Function FileProcessed(ByVal strFile)
FileProcessed = False
objConnection.Open strDBConn
objRecordSet.Open "SELECT * FROM tbl_Files WHERE FileName = '" & strFile & "' And Processed = 1", objConnection, adOpenStatic, adLockOptimistic
If objRecordSet.RecordCount = 1 Then FileProcessed = True
objRecordset.Close
objConnection.Close
End Function
Sub ProcessLog(ByVal strFile)
'WScript.Echo "Processing Log: " & strFile
Dim strDBName,strMDFPath,strLDFPath
strDBName = replace(lcase(strFile),".mdf","")
strMDFPath = sqlPath & "\" & strFile
strLDFPath = sqlPath & "\" & replace(lcase(strFile),".mdf",".ldf")
objConnection.Open strDBConn
Dim objCommand
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
With objCommand
.CommandType = adCmdStoredProc
.CommandText = "sp_attach_db"
.Parameters.Refresh
.Parameters("@dbname") = strDBName
.Parameters("@filename1") = strMDFPath
.Parameters("@filename2") = strLDFPath
.Execute
End With
With objCommand
.CommandType = adCmdText
.CommandText = "USE master" & vbcrlf & "ALTER DATABASE " & strDBName & vbcrlf & "SET SINGLE_USER" & vbcrlf '& "GO"
.Parameters.Refresh
.Execute
End With
objConnection.Close
Set objCommand = Nothing
End Sub
Sub BuildView()
Dim objCommand
objConnection.Open strDBConn
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
With objCommand
.CommandType = adCmdStoredProc
.CommandText = "sp_RecreateView"
.Execute
End With
objConnection.Close
Set objCommand = Nothing
End Sub
Sub InsertSummary()
Dim objCommand
objConnection.Open strDBConn
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
With objCommand
.CommandType = adCmdStoredProc
.CommandText = "sp_InsertSummary"
.CommandTimeout = 0
.Execute
End With
objConnection.Close
Set objCommand = Nothing
End Sub
Sub UnmountDBs()
Dim objCommand
objConnection.Open strDBConn
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
With objCommand
.CommandType = adCmdStoredProc
.CommandText = "sp_DropDBs"
.Execute
End With
objConnection.Close
Set objCommand = Nothing
End Sub
Public Sub WriteToLog(ByVal strLogText)
Dim objFSO2
Dim objFile2
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
If Not objFSO2.FolderExists(destPath & "\Logs\") Then
objFSO2.CreateFolder(destPath & "\Logs\")
End If
If Not objFSO2.FileExists(destPath & "\Logs\" & "LOG.csv") Then
objFSO2.CreateTextFile (destPath & "\Logs\" & "LOG.csv")
End If
Set objFile2 = objFSO2.OpenTextFile(destPath & "\Logs\" & "LOG.csv", ForAppending)
If strLogText = "" Then
objFile2.WriteLine ""
Else
If Right(strLogText, Len(vbCrLf)) = vbCrLf Then
objFile2.WriteLine Now() & ", " & Replace(Left(strLogText, Len(strLogText) - Len(vbCrLf)), vbCrLf, vbCrLf & Now() & ", ")
Else
objFile2.WriteLine Now() & ", " & Replace(strLogText, vbCrLf, vbCrLf & Now() & ", ") '& vbCrLf
End If
End If
objFile2.Close
Set objFile2 = Nothing
Set objFSO2 = Nothing
End Sub
Function StopService(sService, sNode)
Dim oComputer, oService
Set oComputer = GetObject("WinNT://" & sNode & ",computer")
On Error Resume Next
Set oService = oComputer.GetObject("Service", sService)
If Err.Number <> 0 Then
StopService = False
Exit Function
End If
If oService.Status <> ADS_SERVICE_STOPPED Then
oService.Stop
WScript.Sleep 1000
End If
StopService = True
End Function
Function StartService(sService, sNode)
Dim oComputer, oService
Set oComputer = GetObject("WinNT://" & sNode & ",computer")
On Error Resume Next
Set oService = oComputer.GetObject("Service", sService)
If Err.Number <> 0 Then
StartService = False
Exit Function
End If
If oService.Status <> ADS_SERVICE_RUNNING Then
oService.Start
WScript.Sleep 1000
End If
StartService = True
End Function
MudlThru: life & tech situations 