Geekly Articles each Day

рдПрдордПрд╕рдПрдордЬреА рдбреЗрдЯрд╛рдмреЗрд╕ рдХреЗ рд╡реЗрдм рдФрд░ рдлрд╝рд╛рдпрд░рд╡реЙрд▓ рдбреЗрдЯрд╛рдмреЗрд╕ рд╕реЗ рдбреЗрдЯрд╛ рдХреЛ MySQL рдбреЗрдЯрд╛рдмреЗрд╕ рдореЗрдВ рдХреЙрдкреА рдХрд░рдирд╛

рд╕рднреА рдХреЛ рдирдорд╕реНрдХрд╛рд░!
рдмреНрд░рд╛рдЙрдЬрд╝рд░ рдХреЗ рдорд╛рдзреНрдпрдо рд╕реЗ рд░рд┐рдкреЛрд░реНрдЯ рджреЗрдЦрдирд╛ рд╕реБрд╡рд┐рдзрд╛рдЬрдирдХ рд╣реИ, рдЙрдкрдпреЛрдЧрдХрд░реНрддрд╛ рдХреЛ рдХрд┐рд╕реА рднреА рдкреНрд░реЛрдЧреНрд░рд╛рдо рдХреЛ рд╕реНрдерд╛рдкрд┐рдд рдХрд░рдиреЗ рдХреА рдЖрд╡рд╢реНрдпрдХрддрд╛ рдирд╣реАрдВ рд╣реИред
рдЗрдВрдЯрд░рдиреЗрдЯ рдЙрдкрдпреЛрдЧ рд▓реЙрдЧ (WEB) рдФрд░ MS TMG рд╕рд░реНрд╡рд░ рдХреЗ рдлрд╝рд╛рдпрд░рд╡реЙрд▓ рд▓реЙрдЧ (FWS) рдХреЗ рд╕рд╛рде рдЕрдзрд┐рдХ рд╕реБрд╡рд┐рдзрд╛рдЬрдирдХ рдХрд╛рд░реНрдп рдХреЗ рд▓рд┐рдП, рдЙрдиреНрд╣реЗрдВ MySQL рдбреЗрдЯрд╛рдмреЗрд╕ рдореЗрдВ рд▓реЛрдб рдХрд┐рдпрд╛ рдЬрд╛ рд╕рдХрддрд╛ рд╣реИред рдЖрдк рдЗрд╕ рдбреЗрдЯрд╛ рдкрд░ рдХреЛрдИ рднреА рд░рд┐рдкреЛрд░реНрдЯ рд▓рд┐рдЦ рд╕рдХрддреЗ рд╣реИрдВред рд╡реЗрдм рдЗрдВрдЯрд░рдлреЗрд╕ рдХрд╛ рдЙрдкрдпреЛрдЧ рд░рд┐рдкреЛрд░реНрдЯреЛрдВ рддрдХ рдкрд╣реБрдВрдЪрдиреЗ рдХреЗ рд▓рд┐рдП рдХрд┐рдпрд╛ рдЬрд╛рддрд╛ рд╣реИред
рдмрд┐рд▓реНрд▓реА рдХреЗ рдиреАрдЪреЗ рдПрдХ рдЙрджрд╛рд╣рд░рдг рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрдиред

рд▓реЗрдХрд┐рди, рд╢реБрд░реБрдЖрдд рдХреЗ рд▓рд┐рдП, рдЖрдкрдХреЛ SQL Server Express Edition рд╕рд░реНрд╡рд░ рд╕реЗ рдбреЗрдЯрд╛ рдХреЛ рдХреЙрдкреА рдХрд░рдирд╛ рд╣реЛрдЧрд╛ (рдбреЗрдЯрд╛рдмреЗрд╕ рдХрд╛ рдЙрдкрдпреЛрдЧ MS TMG рд╕рд░реНрд╡рд░ рджреНрд╡рд╛рд░рд╛ рдХрд┐рдпрд╛ рдЬрд╛рддрд╛ рд╣реИ) MySQL рдбреЗрдЯрд╛рдмреЗрд╕ рдореЗрдВред рдРрд╕рд╛ рдХрд░рдиреЗ рдХреЗ рд▓рд┐рдП, рд╣рд░ рд░рд╛рдд рдПрдХ рдмреИрдЯ рдлрд╝рд╛рдЗрд▓ рдирд┐рд╖реНрдкрд╛рджрд┐рдд рдХрд░реЗрдВред рдлрд╝рд╛рдЗрд▓ рд╕рд╛рдордЧреНрд░реА:
cscript "start.vbs" %1

Start.vbs рдлрд╝рд╛рдЗрд▓ рдХреА рд╕рд╛рдордЧреНрд░реА:
 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

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-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

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 рдбреЗрдЯрд╛рдмреЗрд╕ рдореЗрдВ рдХреЙрдкреА рдХрд░рддрд╛ рд╣реИред
рдмреИрдЯ рдлрд╝рд╛рдЗрд▓ рд╕рд╛рдордЧреНрд░реА:
 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

рд╕рд╣рд╛рдпрдХ рдлрд╝рд╛рдЗрд▓реЛрдВ рдХреА рд╕рд╛рдордЧреНрд░реАред
рдореЗрдХрдЕрдк 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

рдореЗрдХрдЕрдк 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

рдореЗрдХрдЕрдк 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

рдбреЗрдЯрд╛ MySQL рдбреЗрдЯрд╛рдмреЗрд╕ рдкрд░ рдЕрдкрд▓реЛрдб рдХрд┐рдпрд╛ рдЧрдпрд╛ рд╣реИред рдЖрдк рдЙрдиреНрд╣реЗрдВ рдХрдИ рддрд░рд╣ рдХреА рд░рд┐рдкреЛрд░реНрдЯ рдФрд░ рдПрдХ рд╡реЗрдм рдЗрдВрдЯрд░рдлреЗрд╕ рдмрдирд╛ рд╕рдХрддреЗ рд╣реИрдВред
рдореИрдВрдиреЗ рдЦрд╛рддреЗ рдФрд░ рдкрд╛рд╕рд╡рд░реНрдб рдкрд░ рдкреНрд░рд╛рдзрд┐рдХрд░рдг рдХреЛ рдкреНрд░рдХрд╛рд╢рд┐рдд рдХрд┐рдпрд╛ (рдкреНрд░рдХрд╛рд╢рд┐рдд, рд╡реИрд╕реЗ, рд╣реИрдмреЗ рдкрд░) рдФрд░ 14 рд░рд┐рдкреЛрд░реНрдЯред
рдпрд╣ рдореБрдЦреНрдп рд░рд┐рдкреЛрд░реНрдЯрд┐рдВрдЧ рд╕реНрдХреНрд░реАрди рдХреА рддрд░рд╣ рджрд┐рдЦрддрд╛ рд╣реИ:


рд╕реИрдВрдкрд▓ рд░рд┐рдкреЛрд░реНрдЯред






Source: https://habr.com/ru/post/In202792/

More articles:


All Articles