Копирование данных баз Web и Firewall прокси-сервера MS TMG в базу MySQL

в 22:11, , рубрики: mysql, web, веб-интерфейс, информационная безопасность, Статистика в IT, метки: ,

Всем привет!
Удобно смотреть отчёты через браузер, не надо пользователю никакие программы устанавливать.
Для более удобной работы с данными журнала использования Интернета (WEB) и журналом брандмауэра (FWS) сервера MS TMG их можно загрузить в базу MySQL. Можно написать сколько угодно отчётов по этим данным. Для доступа к отчётам используется веб-интерфейс.
Пример реализации под катом.

Но, для начала, необходимо скопировать сами данные с сервера SQL Server Express Edition (база используется сервером MS TMG) в базу MySQL. Для этого каждую ночь выполнять bat-файл. Содержимое файла:

cscript "start.vbs" %1

Содержимое файла start.vbs:

On Error Resume Next

const gsHelpFile = "c:scriptTMGhelp.txt"      ' <-- В файле help.txt содержится справочная информация по работе скрипта.
'const gbDebugModeON = false
dim gsRunCmd

gsNormalDate = fuNormalizeSystemDate(cStr(DateAdd("d", -1, Date))) 	' <-- Это для отчета за вчера (нормальный режим)

if Wscript.Arguments.Count >= 1 then
	if fuNeedHelp(lCase(Wscript.Arguments(0))) then
		fuTypeTextfile(gsHelpFile)
		WScript.Quit 0
	else 
		gsNormalDate = Wscript.Arguments(0)
	end if
end if

wscript.echo gsNormalDate

Set WshShell = CreateObject("WScript.Shell")
gsRunCmd = "get-TMGReports.bat " & gsNormalDate
wscript.echo gsRunCmd
WshShell.Run gsRunCmd

' Служебные функции
function fuNormalizeSystemDate(lsDate)
	'wscript.echo "Function get: " & lsDate
	lsNormalizeDate = lsDate
	
	if InStr(lsDate, ".") then
		lArrDate = Split(lsDate, ".")
		lsNormalizeDate = lArrDate(2) & lArrDate(1) & lArrDate(0)
	elseif InStr(lsDate, "/") then
		lArrDate = Split(lsDate, "/")
		lsNormalizeDate = fuCheckDatePart(lArrDate(2)) & fuCheckDatePart(lArrDate(0)) & fuCheckDatePart(lArrDate(1))
	elseif InStr(lsDate, "") then
		lArrDate = Split(lsDate, "")
		lsNormalizeDate = fuCheckDatePart(lArrDate(2)) & fuCheckDatePart(lArrDate(0)) & fuCheckDatePart(lArrDate(1))
	end if
	
	fuNormalizeSystemDate = lsNormalizeDate
end function 

function fuNeedHelp(lsPar)
	lbFoo = false
	if  InStr(lsPar, "-h") or InStr(lsPar, "help") or InStr(lsPar, "/h") or InStr(lsPar, "?") then
		lbFoo = true
	end if
	fuNeedHelp = lbFoo
end function 

function fuTypeTextfile(lsTextfile)
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objTextFileShowHelp = objFSO.OpenTextFile(lsTextfile, 1)
	Do Until objTextFileShowHelp.AtEndOfStream
		wscript.echo objTextFileShowHelp.Readline
	Loop
	objTextFileShowHelp.Close
end function 

function fuCheckDatePart(lsDate)
	lsNormalizeDate = lsDate
	
	if len(lsDate) <= 1 then
		lsNormalizeDate = "0" & lsDate
	end if
	
	fuCheckDatePart = lsNormalizeDate
end function

Содержимое файла get-TMGReports.bat: 

bcp.exe "SELECT DISTINCT UrlDestHost,ClientUserName,SUM(processingtime) as processingtime,SUM(bytesrecvd) as bytesrecvd,SUM(bytessent) as bytessent,ClientIP FROM [ISALOG_%1_WEB_000].[dbo].[WebProxyLog] where resultcode not in (12202,12302) GROUP BY ClientUserName,UrlDestHost,ClientIP" queryout "ISALOG_%1_WEB_000.xls" -c -T -S TMG-SERVERMSFW
copy ISALOG_%1_WEB_000.xls \WEB-SERVERc$scriptTMGExport20121ISALOG_%1_WEB_000_01.xls

bcp.exe "SELECT DISTINCT UrlDestHost,ClientUserName,SUM(processingtime) as processingtime,SUM(bytesrecvd) as bytesrecvd,SUM(bytessent) as bytessent,ClientIP,convert(varchar, logTime, 108),uri FROM [ISALOG_%1_WEB_000].[dbo].[WebProxyLog] where resultcode not in (12202,12302) GROUP BY ClientUserName,UrlDestHost,ClientIP,convert(varchar, logTime, 108),uri" queryout "ISALOG_%1_WEB_000_FULL.xls" -c -T -S TMG-SERVERMSFW
move ISALOG_%1_WEB_000_FULL.xls \WEB-SERVERc$scriptTMGExport20122ISALOG_%1_WEB_000_FULL_01.xls

bcp.exe "SELECT [servername],[ClientUserName],[logTime],[Action],[resultcode],[rule],[protocol],[SourceIP],[SourcePort],[DestinationIP],[DestinationPort],[OriginalClientIP],[SourceNetwork],[DestinationNetwork],[ApplicationProtocol],[bytessent],[bytessentDelta],[bytesrecvd],[bytesrecvdDelta],[connectiontime],[connectiontimeDelta],[DestinationName],[ClientAgent],[sessionid],[connectionid],[InternalServiceInfo] FROM [ISALOG_%1_FWS_000].[dbo].[FirewallLog] WHERE [ClientUserName] <> '-' AND [protocol] = 'TCP'" queryout "ISALOG_%1_FWS_000.xls" -c -T -S TMG-SERVERMSFW
copy ISALOG_%1_FWS_000.xls \WEB-SERVERc$scriptTMGExport2012fwsISALOG_%1_FWS_000_01.xls

Скрипты для создания нужных таблиц в базе MySQL:

CREATE TABLE IF NOT EXISTS ISALOG_WEB_000 (
  dt date NOT NULL,
  UrlDestHost varchar(2048) NOT NULL,
  ClientUserName varchar(1024) NOT NULL,
  processingtime int(11) NOT NULL,
  bytesrecvd int(11) NOT NULL,
  bytessent int(11) NOT NULL,
  ClientIP varchar(64) NOT NULL,
  KEY ClientUserName (ClientUserName(1000)),
  KEY UrlDestHost (UrlDestHost(1000)),
  KEY dt (dt)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Данные с 01.01.2013'
/*!50100 PARTITION BY RANGE ( TO_DAYS(dt))
(PARTITION p2013_01 VALUES LESS THAN (735265) ENGINE = MyISAM,
 PARTITION p2013_02 VALUES LESS THAN (735293) ENGINE = MyISAM,
 PARTITION p2013_03 VALUES LESS THAN (735324) ENGINE = MyISAM,
 PARTITION p2013_04 VALUES LESS THAN (735354) ENGINE = MyISAM,
 PARTITION p2013_05 VALUES LESS THAN (735385) ENGINE = MyISAM,
 PARTITION p2013_06 VALUES LESS THAN (735415) ENGINE = MyISAM,
 PARTITION p2013_07 VALUES LESS THAN (735446) ENGINE = MyISAM,
 PARTITION p2013_08 VALUES LESS THAN (735477) ENGINE = MyISAM,
 PARTITION p2013_09 VALUES LESS THAN (735507) ENGINE = MyISAM,
 PARTITION p2013_10 VALUES LESS THAN (735538) ENGINE = MyISAM,
 PARTITION p2013_11 VALUES LESS THAN (735568) ENGINE = MyISAM,
 PARTITION p2013_12 VALUES LESS THAN (735599) ENGINE = MyISAM,
 PARTITION p2014_01 VALUES LESS THAN (735630) ENGINE = MyISAM,
 PARTITION p2014_02 VALUES LESS THAN (735658) ENGINE = MyISAM,
 PARTITION p2014_03 VALUES LESS THAN (735689) ENGINE = MyISAM,
 PARTITION p2014_04 VALUES LESS THAN (735719) ENGINE = MyISAM,
 PARTITION p2014_05 VALUES LESS THAN (735750) ENGINE = MyISAM,
 PARTITION p2014_06 VALUES LESS THAN (735780) ENGINE = MyISAM,
 PARTITION p2014_07 VALUES LESS THAN (735811) ENGINE = MyISAM,
 PARTITION p2014_08 VALUES LESS THAN (735842) ENGINE = MyISAM,
 PARTITION p2014_09 VALUES LESS THAN (735872) ENGINE = MyISAM,
 PARTITION p2014_10 VALUES LESS THAN (735903) ENGINE = MyISAM,
 PARTITION p2014_11 VALUES LESS THAN (735933) ENGINE = MyISAM,
 PARTITION p2014_12 VALUES LESS THAN (735964) ENGINE = MyISAM,
 PARTITION p2015_01 VALUES LESS THAN (735995) ENGINE = MyISAM,
 PARTITION p2015_02 VALUES LESS THAN (736023) ENGINE = MyISAM,
 PARTITION p2015_03 VALUES LESS THAN (736054) ENGINE = MyISAM,
 PARTITION p2015_04 VALUES LESS THAN (736084) ENGINE = MyISAM,
 PARTITION p2015_05 VALUES LESS THAN (736115) ENGINE = MyISAM,
 PARTITION p2015_06 VALUES LESS THAN (736145) ENGINE = MyISAM,
 PARTITION p2015_07 VALUES LESS THAN (736176) ENGINE = MyISAM,
 PARTITION p2015_08 VALUES LESS THAN (736207) ENGINE = MyISAM,
 PARTITION p2015_09 VALUES LESS THAN (736237) ENGINE = MyISAM,
 PARTITION p2015_10 VALUES LESS THAN (736268) ENGINE = MyISAM,
 PARTITION p2015_11 VALUES LESS THAN (736298) ENGINE = MyISAM,
 PARTITION p2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
 PARTITION p2016_01 VALUES LESS THAN (736360) ENGINE = MyISAM,
 PARTITION p2016_02 VALUES LESS THAN (736389) ENGINE = MyISAM,
 PARTITION p2016_03 VALUES LESS THAN (736420) ENGINE = MyISAM,
 PARTITION p2016_04 VALUES LESS THAN (736450) ENGINE = MyISAM,
 PARTITION p2016_05 VALUES LESS THAN (736481) ENGINE = MyISAM,
 PARTITION p2016_06 VALUES LESS THAN (736511) ENGINE = MyISAM,
 PARTITION p2016_07 VALUES LESS THAN (736542) ENGINE = MyISAM,
 PARTITION p2016_08 VALUES LESS THAN (736573) ENGINE = MyISAM,
 PARTITION p2016_09 VALUES LESS THAN (736603) ENGINE = MyISAM,
 PARTITION p2016_10 VALUES LESS THAN (736634) ENGINE = MyISAM,
 PARTITION p2016_11 VALUES LESS THAN (736664) ENGINE = MyISAM,
 PARTITION p2016_12 VALUES LESS THAN (736695) ENGINE = MyISAM,
 PARTITION p2017 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

CREATE TABLE IF NOT EXISTS ISALOG_WEB_000_FULL (
  dt date NOT NULL,
  UrlDestHost varchar(2048) NOT NULL,
  ClientUserName varchar(1024) NOT NULL,
  processingtime int(11) NOT NULL,
  bytesrecvd int(11) NOT NULL,
  bytessent int(11) NOT NULL,
  ClientIP varchar(64) NOT NULL,
  tm time NOT NULL,
  uri varchar(2048) NOT NULL,
  KEY ClientUserName (ClientUserName(1000)),
  KEY UrlDestHost (UrlDestHost(1000)),
  KEY dt (dt)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Данные с 19.07.2013'
/*!50100 PARTITION BY RANGE ( TO_DAYS(dt))
(PARTITION p2013_01 VALUES LESS THAN (735265) ENGINE = MyISAM,
 PARTITION p2013_02 VALUES LESS THAN (735293) ENGINE = MyISAM,
 PARTITION p2013_03 VALUES LESS THAN (735324) ENGINE = MyISAM,
 PARTITION p2013_04 VALUES LESS THAN (735354) ENGINE = MyISAM,
 PARTITION p2013_05 VALUES LESS THAN (735385) ENGINE = MyISAM,
 PARTITION p2013_06 VALUES LESS THAN (735415) ENGINE = MyISAM,
 PARTITION p2013_07 VALUES LESS THAN (735446) ENGINE = MyISAM,
 PARTITION p2013_08 VALUES LESS THAN (735477) ENGINE = MyISAM,
 PARTITION p2013_09 VALUES LESS THAN (735507) ENGINE = MyISAM,
 PARTITION p2013_10 VALUES LESS THAN (735538) ENGINE = MyISAM,
 PARTITION p2013_11 VALUES LESS THAN (735568) ENGINE = MyISAM,
 PARTITION p2013_12 VALUES LESS THAN (735599) ENGINE = MyISAM,
 PARTITION p2014_01 VALUES LESS THAN (735630) ENGINE = MyISAM,
 PARTITION p2014_02 VALUES LESS THAN (735658) ENGINE = MyISAM,
 PARTITION p2014_03 VALUES LESS THAN (735689) ENGINE = MyISAM,
 PARTITION p2014_04 VALUES LESS THAN (735719) ENGINE = MyISAM,
 PARTITION p2014_05 VALUES LESS THAN (735750) ENGINE = MyISAM,
 PARTITION p2014_06 VALUES LESS THAN (735780) ENGINE = MyISAM,
 PARTITION p2014_07 VALUES LESS THAN (735811) ENGINE = MyISAM,
 PARTITION p2014_08 VALUES LESS THAN (735842) ENGINE = MyISAM,
 PARTITION p2014_09 VALUES LESS THAN (735872) ENGINE = MyISAM,
 PARTITION p2014_10 VALUES LESS THAN (735903) ENGINE = MyISAM,
 PARTITION p2014_11 VALUES LESS THAN (735933) ENGINE = MyISAM,
 PARTITION p2014_12 VALUES LESS THAN (735964) ENGINE = MyISAM,
 PARTITION p2015_01 VALUES LESS THAN (735995) ENGINE = MyISAM,
 PARTITION p2015_02 VALUES LESS THAN (736023) ENGINE = MyISAM,
 PARTITION p2015_03 VALUES LESS THAN (736054) ENGINE = MyISAM,
 PARTITION p2015_04 VALUES LESS THAN (736084) ENGINE = MyISAM,
 PARTITION p2015_05 VALUES LESS THAN (736115) ENGINE = MyISAM,
 PARTITION p2015_06 VALUES LESS THAN (736145) ENGINE = MyISAM,
 PARTITION p2015_07 VALUES LESS THAN (736176) ENGINE = MyISAM,
 PARTITION p2015_08 VALUES LESS THAN (736207) ENGINE = MyISAM,
 PARTITION p2015_09 VALUES LESS THAN (736237) ENGINE = MyISAM,
 PARTITION p2015_10 VALUES LESS THAN (736268) ENGINE = MyISAM,
 PARTITION p2015_11 VALUES LESS THAN (736298) ENGINE = MyISAM,
 PARTITION p2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
 PARTITION p2016_01 VALUES LESS THAN (736360) ENGINE = MyISAM,
 PARTITION p2016_02 VALUES LESS THAN (736389) ENGINE = MyISAM,
 PARTITION p2016_03 VALUES LESS THAN (736420) ENGINE = MyISAM,
 PARTITION p2016_04 VALUES LESS THAN (736450) ENGINE = MyISAM,
 PARTITION p2016_05 VALUES LESS THAN (736481) ENGINE = MyISAM,
 PARTITION p2016_06 VALUES LESS THAN (736511) ENGINE = MyISAM,
 PARTITION p2016_07 VALUES LESS THAN (736542) ENGINE = MyISAM,
 PARTITION p2016_08 VALUES LESS THAN (736573) ENGINE = MyISAM,
 PARTITION p2016_09 VALUES LESS THAN (736603) ENGINE = MyISAM,
 PARTITION p2016_10 VALUES LESS THAN (736634) ENGINE = MyISAM,
 PARTITION p2016_11 VALUES LESS THAN (736664) ENGINE = MyISAM,
 PARTITION p2016_12 VALUES LESS THAN (736695) ENGINE = MyISAM,
 PARTITION p2017 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

CREATE TABLE IF NOT EXISTS ISALOG_FWS_000 (
  dt date NOT NULL,
  tm time DEFAULT NULL,
  servername varchar(128) DEFAULT NULL,
  ClientUserName varchar(514) DEFAULT NULL,
  `Action` int(3) DEFAULT NULL,
  resultcode int(11) DEFAULT NULL,
  rule varchar(128) DEFAULT NULL,
  protocol varchar(32) DEFAULT NULL,
  SourceIP varchar(64) DEFAULT NULL,
  SourcePort varchar(6) DEFAULT NULL,
  DestinationIP varchar(64) DEFAULT NULL,
  DestinationPort varchar(6) DEFAULT NULL,
  OriginalClientIP varchar(64) DEFAULT NULL,
  SourceNetwork varchar(128) DEFAULT NULL,
  DestinationNetwork varchar(128) DEFAULT NULL,
  ApplicationProtocol varchar(128) DEFAULT NULL,
  bytessent int(11) DEFAULT NULL,
  bytessentDelta int(11) DEFAULT NULL,
  bytesrecvd int(11) DEFAULT NULL,
  bytesrecvdDelta int(11) DEFAULT NULL,
  connectiontime int(11) DEFAULT NULL,
  connectiontimeDelta int(11) DEFAULT NULL,
  DestinationName varchar(255) DEFAULT NULL,
  ClientAgent varchar(255) DEFAULT NULL,
  sessionid int(11) DEFAULT NULL,
  connectionid int(11) DEFAULT NULL,
  InternalServiceInfo int(6) DEFAULT NULL,
  KEY ClientUserName (ClientUserName),
  KEY dt (dt)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY RANGE ( TO_DAYS(dt))
(PARTITION p2013_01 VALUES LESS THAN (735265) ENGINE = MyISAM,
 PARTITION p2013_02 VALUES LESS THAN (735293) ENGINE = MyISAM,
 PARTITION p2013_03 VALUES LESS THAN (735324) ENGINE = MyISAM,
 PARTITION p2013_04 VALUES LESS THAN (735354) ENGINE = MyISAM,
 PARTITION p2013_05 VALUES LESS THAN (735385) ENGINE = MyISAM,
 PARTITION p2013_06 VALUES LESS THAN (735415) ENGINE = MyISAM,
 PARTITION p2013_07 VALUES LESS THAN (735446) ENGINE = MyISAM,
 PARTITION p2013_08 VALUES LESS THAN (735477) ENGINE = MyISAM,
 PARTITION p2013_09 VALUES LESS THAN (735507) ENGINE = MyISAM,
 PARTITION p2013_10 VALUES LESS THAN (735538) ENGINE = MyISAM,
 PARTITION p2013_11 VALUES LESS THAN (735568) ENGINE = MyISAM,
 PARTITION p2013_12 VALUES LESS THAN (735599) ENGINE = MyISAM,
 PARTITION p2014_01 VALUES LESS THAN (735630) ENGINE = MyISAM,
 PARTITION p2014_02 VALUES LESS THAN (735658) ENGINE = MyISAM,
 PARTITION p2014_03 VALUES LESS THAN (735689) ENGINE = MyISAM,
 PARTITION p2014_04 VALUES LESS THAN (735719) ENGINE = MyISAM,
 PARTITION p2014_05 VALUES LESS THAN (735750) ENGINE = MyISAM,
 PARTITION p2014_06 VALUES LESS THAN (735780) ENGINE = MyISAM,
 PARTITION p2014_07 VALUES LESS THAN (735811) ENGINE = MyISAM,
 PARTITION p2014_08 VALUES LESS THAN (735842) ENGINE = MyISAM,
 PARTITION p2014_09 VALUES LESS THAN (735872) ENGINE = MyISAM,
 PARTITION p2014_10 VALUES LESS THAN (735903) ENGINE = MyISAM,
 PARTITION p2014_11 VALUES LESS THAN (735933) ENGINE = MyISAM,
 PARTITION p2014_12 VALUES LESS THAN (735964) ENGINE = MyISAM,
 PARTITION p2015_01 VALUES LESS THAN (735995) ENGINE = MyISAM,
 PARTITION p2015_02 VALUES LESS THAN (736023) ENGINE = MyISAM,
 PARTITION p2015_03 VALUES LESS THAN (736054) ENGINE = MyISAM,
 PARTITION p2015_04 VALUES LESS THAN (736084) ENGINE = MyISAM,
 PARTITION p2015_05 VALUES LESS THAN (736115) ENGINE = MyISAM,
 PARTITION p2015_06 VALUES LESS THAN (736145) ENGINE = MyISAM,
 PARTITION p2015_07 VALUES LESS THAN (736176) ENGINE = MyISAM,
 PARTITION p2015_08 VALUES LESS THAN (736207) ENGINE = MyISAM,
 PARTITION p2015_09 VALUES LESS THAN (736237) ENGINE = MyISAM,
 PARTITION p2015_10 VALUES LESS THAN (736268) ENGINE = MyISAM,
 PARTITION p2015_11 VALUES LESS THAN (736298) ENGINE = MyISAM,
 PARTITION p2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
 PARTITION p2016_01 VALUES LESS THAN (736360) ENGINE = MyISAM,
 PARTITION p2016_02 VALUES LESS THAN (736389) ENGINE = MyISAM,
 PARTITION p2016_03 VALUES LESS THAN (736420) ENGINE = MyISAM,
 PARTITION p2016_04 VALUES LESS THAN (736450) ENGINE = MyISAM,
 PARTITION p2016_05 VALUES LESS THAN (736481) ENGINE = MyISAM,
 PARTITION p2016_06 VALUES LESS THAN (736511) ENGINE = MyISAM,
 PARTITION p2016_07 VALUES LESS THAN (736542) ENGINE = MyISAM,
 PARTITION p2016_08 VALUES LESS THAN (736573) ENGINE = MyISAM,
 PARTITION p2016_09 VALUES LESS THAN (736603) ENGINE = MyISAM,
 PARTITION p2016_10 VALUES LESS THAN (736634) ENGINE = MyISAM,
 PARTITION p2016_11 VALUES LESS THAN (736664) ENGINE = MyISAM,
 PARTITION p2016_12 VALUES LESS THAN (736695) ENGINE = MyISAM,
 PARTITION p2017 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

Я сделал таблицы секционированными по месяцам. Сделаны индексы по пользователю и дате.

На веб-сервере нужно установить logparser.
Скрипт на веб-сервере копирует данные в базу MySQL.
Содержимое bat-файла:

logparser "select * into c:scriptTMGExport201212012.xls from c:scriptTMGExport20121ISALOG_201*.xls" -i:csv -headerRow:OFF -o:tsv -oSeparator:tab
cscript //nologo "c:scriptTMGScriptsmake-reportFile.vbs" c:scriptTMGExport201212012.xls c:scriptTMGExport20121ISALOG_WEB_000.csv

logparser "select * into c:scriptTMGExport201222012.xls from c:scriptTMGExport20122ISALOG_201*.xls" -i:csv -headerRow:OFF -o:tsv -oSeparator:tab
cscript //nologo "c:scriptTMGScriptsmake-reportFile-full.vbs" c:scriptTMGExport201222012.xls c:scriptTMGExport20122ISALOG_WEB_000_FULL.csv

logparser "select * into c:scriptTMGExport2012fws2012.xls from c:scriptTMGExport2012fwsISALOG_201*.xls" -i:csv -headerRow:OFF -o:tsv -oSeparator:tab
cscript //nologo "c:scriptTMGScriptsmake-reportFile-fws.vbs" c:scriptTMGExport2012fws2012.xls c:scriptTMGExport2012fwsISALOG_FWS_000.csv

del c:scriptTMGExport201212012.xls
del c:scriptTMGExport201222012.xls
del c:scriptTMGExport2012fws2012.xls

cd
subst Z: e:WebServers
Z:
cd
cd usrlocalmysql-5.1bin
mysqlimport.exe -u username -p12345678 --fields-terminated-by=t --fields-enclosed-by="" --fields-escaped-by= --lines-terminated-by=n --local my_base c:scriptTMGExport20121ISALOG_WEB_000.csv
mysqlimport.exe -u username -p12345678 --fields-terminated-by=t --fields-enclosed-by="" --fields-escaped-by= --lines-terminated-by=n --local my_base c:scriptTMGExport20122ISALOG_WEB_000_FULL.csv
mysqlimport.exe -u username -p12345678 --fields-terminated-by=t --fields-enclosed-by="" --fields-escaped-by= --lines-terminated-by=n --local my_base c:scriptTMGExport2012fwsISALOG_FWS_000.csv

move c:scriptTMGExport20121ISALOG_201*.xls e:Export2013
move c:scriptTMGExport20122ISALOG_201*.xls e:Export2013
move c:scriptTMGExport2012fwsISALOG_201*.xls e:Export2013

del c:scriptTMGExport20121ISALOG_WEB_000.csv
del c:scriptTMGExport20122ISALOG_WEB_000_FULL.csv
del c:scriptTMGExport2012fwsISALOG_FWS_000.csv

Содержимое вспомогательных файлов.
make-reportFile.vbs:

dim gsDevider
dim record
dim recordResult

if Wscript.Arguments.Count = 2 then
	sgFilename = Wscript.Arguments(0)
	sgFilenameResult = Wscript.Arguments(1)
elseif Wscript.Arguments.Count = 1 then
	sgFilename = Wscript.Arguments(0)
	sgFilenameResult = "c:scriptTMGExport20121ISALOG_WEB_000.csv"
else
	sgFilename = InputBox("Имя исходного файла", "Введите", "")
	sgFilenameResult = InputBox("Имя результирующего файла", "Введите", "")
end if

gsDevider = VBTab
record = ""
recordResult = ""

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFileOpen = objFSO.OpenTextFile(sgFilename, 1)
Set objTextFileWrite = objFSO.CreateTextFile(sgFilenameResult, True)

Do Until objTextFileOpen.AtEndOfStream
	record = trim(objTextFileOpen.Readline)

	if InStr(record, "Filename	RowNumber	Field1") = 0 then
		if inStr(record, gsDevider) then
			lArr = split(record, gsDevider)
			recordResult = 	fuNormalyzeDate(lArr(0)) & gsDevider & _
							lArr(2) & gsDevider & _
							fuNormalyzeAccount(lArr(3)) & gsDevider & _
							lArr(4) & gsDevider & _
							lArr(5) & gsDevider & _
							lArr(6) & gsDevider & _
							lArr(7)
		end if
		objTextFileWrite.WriteLine recordResult
	end if
Loop

objTextFileWrite.Close
objTextFileOpen.Close

WScript.Echo "* Операция успешно завершена." 

function fuRemoveExtention(lsFilename)
	lRes = lsFilename
	if InStr(lsFilename, ".") then
		lRes = Left(lsFilename, Len(lsFilename)-4)
	end if
	fuRemoveExtention = lRes
end function 

function fuNormalyzeDate(lsDate)
	lRes = lsDate
	if InStr(lsDate, "") then
		lArrDate = Split(lsDate, "")
		lRes = lArrDate(uBound(lArrDate))
		
		lArrDate = Split(lRes, "_")
		lRes = lArrDate(1)
		
		lRes = Left(lRes, 4) & "-" & Mid(lRes, 5, 2) & "-" & Right(lRes, 2)
	end if
	fuNormalyzeDate = lRes
end function 

function fuNormalyzeAccount(lsAccount)
	lRes = lsAccount
	lRes = Replace(lRes, "DOMAIN", "")
	fuNormalyzeAccount = lRes
end function

make-reportFile-full.vbs

dim gsDevider
dim record
dim recordResult

if Wscript.Arguments.Count = 2 then
	sgFilename = Wscript.Arguments(0)
	sgFilenameResult = Wscript.Arguments(1)
elseif Wscript.Arguments.Count = 1 then
	sgFilename = Wscript.Arguments(0)
	sgFilenameResult = "c:scriptTMGExport20121ISALOG_WEB_000.csv"
else
	sgFilename = InputBox("Имя исходного файла", "Введите", "")
	sgFilenameResult = InputBox("Имя результирующего файла", "Введите", "")
end if

gsDevider = VBTab
record = ""
recordResult = ""

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFileOpen = objFSO.OpenTextFile(sgFilename, 1)
Set objTextFileWrite = objFSO.CreateTextFile(sgFilenameResult, True)

Do Until objTextFileOpen.AtEndOfStream
	record = trim(objTextFileOpen.Readline)

	if InStr(record, "Filename	RowNumber	Field1") = 0 then
		if inStr(record, gsDevider) then
			lArr = split(record, gsDevider)
			recordResult = 	fuNormalyzeDate(lArr(0)) & gsDevider & _
							lArr(2) & gsDevider & _
							fuNormalyzeAccount(lArr(3)) & gsDevider & _
							lArr(4) & gsDevider & _
							lArr(5) & gsDevider & _
							lArr(6) & gsDevider & _
							lArr(7) & gsDevider & _
							lArr(8) & gsDevider & _
							lArr(9)
		end if
		objTextFileWrite.WriteLine recordResult
	end if
Loop

objTextFileWrite.Close
objTextFileOpen.Close

WScript.Echo "* Операция успешно завершена." 

function fuRemoveExtention(lsFilename)
	lRes = lsFilename
	if InStr(lsFilename, ".") then
		lRes = Left(lsFilename, Len(lsFilename)-4)
	end if
	fuRemoveExtention = lRes
end function 

function fuNormalyzeDate(lsDate)
	lRes = lsDate
	if InStr(lsDate, "") then
		lArrDate = Split(lsDate, "")
		lRes = lArrDate(uBound(lArrDate))
		
		lArrDate = Split(lRes, "_")
		lRes = lArrDate(1)
		
		lRes = Left(lRes, 4) & "-" & Mid(lRes, 5, 2) & "-" & Right(lRes, 2)
	end if
	fuNormalyzeDate = lRes
end function 

function fuNormalyzeAccount(lsAccount)
	lRes = lsAccount
	lRes = Replace(lRes, "DOMAIN", "")
	fuNormalyzeAccount = lRes
end function

make-reportFile-fws.vbs 

dim gsDevider
dim record
dim recordResult

if Wscript.Arguments.Count = 2 then
	sgFilename = Wscript.Arguments(0)
	sgFilenameResult = Wscript.Arguments(1)
elseif Wscript.Arguments.Count = 1 then
	sgFilename = Wscript.Arguments(0)
	sgFilenameResult = "c:scriptTMGExport2012fwsISALOG_FWS_000.csv"
else
	sgFilename = InputBox("Имя исходного файла", "Введите", "")
	sgFilenameResult = InputBox("Имя результирующего файла", "Введите", "")
end if

gsDevider = VBTab
record = ""
recordResult = ""

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFileOpen = objFSO.OpenTextFile(sgFilename, 1)
Set objTextFileWrite = objFSO.CreateTextFile(sgFilenameResult, True)

Do Until objTextFileOpen.AtEndOfStream
	record = trim(objTextFileOpen.Readline)

	if InStr(record, "Filename	RowNumber	Field1") = 0 then
		if inStr(record, gsDevider) then
			lArr = split(record, gsDevider)
			recordResult = 	fuNormalyzeDateTime(lArr(4),0) & gsDevider & _
							fuNormalyzeDateTime(lArr(4),1)  & gsDevider & _
							lArr(2)  & gsDevider & _
							fuNormalyzeAccount(lArr(3)) & gsDevider & _
							lArr(5)  & gsDevider & _
							lArr(6)  & gsDevider & _
							lArr(7)  & gsDevider & _
							lArr(8)  & gsDevider & _
							lArr(9)  & gsDevider & _
							lArr(10) & gsDevider & _
							lArr(11) & gsDevider & _
							lArr(12) & gsDevider & _
							lArr(13) & gsDevider & _
							lArr(14) & gsDevider & _
							lArr(15) & gsDevider & _
							lArr(16) & gsDevider & _
							lArr(17) & gsDevider & _
							lArr(18) & gsDevider & _
							lArr(19) & gsDevider & _
							lArr(20) & gsDevider & _
							lArr(21) & gsDevider & _
							lArr(22) & gsDevider & _
							lArr(23) & gsDevider & _
							lArr(24) & gsDevider & _
							lArr(25) & gsDevider & _
							lArr(26) & gsDevider & _
							lArr(27)
		end if
		objTextFileWrite.WriteLine recordResult
	end if
Loop

objTextFileWrite.Close
objTextFileOpen.Close

WScript.Echo "* Операция успешно завершена." 

function fuRemoveExtention(lsFilename)
	lRes = lsFilename
	if InStr(lsFilename, ".") then
		lRes = Left(lsFilename, Len(lsFilename)-4)
	end if
	fuRemoveExtention = lRes
end function 

function fuNormalyzeDate(lsDate)
	lRes = lsDate
	if InStr(lsDate, "") then
		lArrDate = Split(lsDate, "")
		lRes = lArrDate(uBound(lArrDate))
		
		lArrDate = Split(lRes, "_")
		lRes = lArrDate(1)
		
		lRes = Left(lRes, 4) & "-" & Mid(lRes, 5, 2) & "-" & Right(lRes, 2)
	end if
	fuNormalyzeDate = lRes
end function 

function fuNormalyzeDateTime(lsDateTime, liFlag)
	lRes = lsDateTime
	if InStr(lsDateTime, " ") then
		lArrDate = Split(lsDateTime, " ")
		lRes = lArrDate(liFlag)
		if liFlag = 1 then
			lArrTime = Split(lRes, ".")
			lRes = lArrTime(0)
		end if
	end if
	fuNormalyzeDateTime = lRes
end function 

function fuNormalyzeAccount(lsAccount)
	lRes = lsAccount
	lRes = Replace(lRes, "DOMAIN", "")
	fuNormalyzeAccount = lRes
end function

Данные загружены в базу MySQL. Можно делать самые разные отчеты и веб-интерфейс к ним.
Я прикрутил авторизацию по учётной записи и паролю (опубликованную, кстати, на Хабре) и 14 отчётов.
Вот так у меня выглядит главный экран создания отчётов:
Копирование данных баз Web и Firewall прокси сервера MS TMG в базу MySQL

Примеры отчётов.
Копирование данных баз Web и Firewall прокси сервера MS TMG в базу MySQL

Копирование данных баз Web и Firewall прокси сервера MS TMG в базу MySQL

Копирование данных баз Web и Firewall прокси сервера MS TMG в базу MySQL

Копирование данных баз Web и Firewall прокси сервера MS TMG в базу MySQL

Автор: luzhin_kirill

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js