Forefront TMG log into custom SQL database
2013-02-14 Leave a comment
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