Data copying of Web and Firewall databases of MS TMG proxy server into MySQL database
Hello!
It is convenient to watch the reports through the browser, the user does not need to install any programs.
For more convenient work with the data of the Internet usage log (WEB) and the firewall log (FWS) of MS TMG server, you can upload them to the MySQL database. You can write as many reports on this data. To access the reports using a web interface.
An example implementation under the cut.
But, for a start, you need to copy the data itself from the SQL Server Express Edition server (the database is used by MS TMG server) to the MySQL database. To do this, perform a bat file every night. File contents:
Contents of the start.vbs file:
Contents of the get-TMGReports.bat file:
Scripts to create the necessary tables in the MySQL database:
On the web server, you need to install logparser .
The script on the web server copies the data to the MySQL database.
Contents of the bat file:
The contents of the auxiliary files.
make-reportFile.vbs:
make-reportFile-full.vbs
make-reportFile-fws.vbs
Data is loaded into MySQL database. You can make a variety of reports and a web interface to them.
I screwed the authorization on the account and password (published, by the way, on Habré) and 14 reports.
This is how my main report creation screen looks like:
Examples of reports.
')
It is convenient to watch the reports through the browser, the user does not need to install any programs.
For more convenient work with the data of the Internet usage log (WEB) and the firewall log (FWS) of MS TMG server, you can upload them to the MySQL database. You can write as many reports on this data. To access the reports using a web interface.
An example implementation under the cut.
But, for a start, you need to copy the data itself from the SQL Server Express Edition server (the database is used by MS TMG server) to the MySQL database. To do this, perform a bat file every night. File contents:
cscript "start.vbs" %1 Contents of the start.vbs file:
On Error Resume Next const gsHelpFile = "c:\script\TMG\help.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 Contents of the get-TMGReports.bat file:
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-SERVER\MSFW copy ISALOG_%1_WEB_000.xls \\WEB-SERVER\c$\script\TMG\Export\2012\1\ISALOG_%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-SERVER\MSFW move ISALOG_%1_WEB_000_FULL.xls \\WEB-SERVER\c$\script\TMG\Export\2012\2\ISALOG_%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-SERVER\MSFW copy ISALOG_%1_FWS_000.xls \\WEB-SERVER\c$\script\TMG\Export\2012\fws\ISALOG_%1_FWS_000_01.xls Scripts to create the necessary tables in the MySQL database:
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) */; I made the tables partitioned by months. Made indexes by user and date.On the web server, you need to install logparser .
The script on the web server copies the data to the MySQL database.
Contents of the bat file:
logparser "select * into c:\script\TMG\Export\2012\1\2012.xls from c:\script\TMG\Export\2012\1\ISALOG_201*.xls" -i:csv -headerRow:OFF -o:tsv -oSeparator:tab cscript //nologo "c:\script\TMG\Scripts\make-reportFile.vbs" c:\script\TMG\Export\2012\1\2012.xls c:\script\TMG\Export\2012\1\ISALOG_WEB_000.csv logparser "select * into c:\script\TMG\Export\2012\2\2012.xls from c:\script\TMG\Export\2012\2\ISALOG_201*.xls" -i:csv -headerRow:OFF -o:tsv -oSeparator:tab cscript //nologo "c:\script\TMG\Scripts\make-reportFile-full.vbs" c:\script\TMG\Export\2012\2\2012.xls c:\script\TMG\Export\2012\2\ISALOG_WEB_000_FULL.csv logparser "select * into c:\script\TMG\Export\2012\fws\2012.xls from c:\script\TMG\Export\2012\fws\ISALOG_201*.xls" -i:csv -headerRow:OFF -o:tsv -oSeparator:tab cscript //nologo "c:\script\TMG\Scripts\make-reportFile-fws.vbs" c:\script\TMG\Export\2012\fws\2012.xls c:\script\TMG\Export\2012\fws\ISALOG_FWS_000.csv del c:\script\TMG\Export\2012\1\2012.xls del c:\script\TMG\Export\2012\2\2012.xls del c:\script\TMG\Export\2012\fws\2012.xls cd\ subst Z: e:\WebServers Z: cd\ cd usr\local\mysql-5.1\bin\ mysqlimport.exe -u username -p12345678 --fields-terminated-by=\t --fields-enclosed-by="" --fields-escaped-by=\ --lines-terminated-by=\n --local my_base c:\script\TMG\Export\2012\1\ISALOG_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:\script\TMG\Export\2012\2\ISALOG_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:\script\TMG\Export\2012\fws\ISALOG_FWS_000.csv move c:\script\TMG\Export\2012\1\ISALOG_201*.xls e:\Export\2013 move c:\script\TMG\Export\2012\2\ISALOG_201*.xls e:\Export\2013 move c:\script\TMG\Export\2012\fws\ISALOG_201*.xls e:\Export\2013 del c:\script\TMG\Export\2012\1\ISALOG_WEB_000.csv del c:\script\TMG\Export\2012\2\ISALOG_WEB_000_FULL.csv del c:\script\TMG\Export\2012\fws\ISALOG_FWS_000.csv The contents of the auxiliary files.
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:\script\TMG\Export\2012\1\ISALOG_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:\script\TMG\Export\2012\1\ISALOG_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:\script\TMG\Export\2012\fws\ISALOG_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 Data is loaded into MySQL database. You can make a variety of reports and a web interface to them.
I screwed the authorization on the account and password (published, by the way, on Habré) and 14 reports.
This is how my main report creation screen looks like:
Examples of reports.
')
Source: https://habr.com/ru/post/202792/
All Articles