Solved

perl script with awk

Posted on 2014-04-14
7
423 Views
Last Modified: 2014-11-18
I have multiple txt files as given below in a folder
i need a script which parse the file and send the mysql server xxx table

xxx table has 4 columns

1. date : (Apr/15/2014 05:37:01) will be converted to the mysql date time
2. ip  : 10.5.50.0
3. operation : GET / POST
4. Web page : http://ocsp.verisign.com/ 

it will be imported to mysql line by line for each file in the folder and when it complete a file it will delete it immediately

how should i do it



Apr/15/2014 05:37:01 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2014/02/windows8-rt-kb2919393-x64_a48a08c4fdd949534e6419d775c454071c13bb4d.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/12/windows8-rt-kb2903938-x64_7c37aabdbd2c33e2088ec33ffe285ffc2a923013.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/08/windows8-rt-kb2862768-x64_564dbd31bd53228eec1cf01fd1ab5559e065c48e.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/10/windows8-rt-kb2883201-x64_b1bada64e121a8f543b5f92601b828c8c80414ec.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2014/02/windows8-rt-kb2919393-x64_a48a08c4fdd949534e6419d775c454071c13bb4d.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/12/windows8-rt-kb2903938-x64_7c37aabdbd2c33e2088ec33ffe285ffc2a923013.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/10/windows8-rt-kb2883201-x64_b1bada64e121a8f543b5f92601b828c8c80414ec.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/08/windows8-rt-kb2862768-x64_564dbd31bd53228eec1cf01fd1ab5559e065c48e.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/11/windows8-rt-kb2889784-x64_bc658c3848f474559be2afd1014dd7a5ae90b944.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2014/02/windows8-rt-kb2919393-x64_a48a08c4fdd949534e6419d775c454071c13bb4d.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/12/windows8-rt-kb2903938-x64_7c37aabdbd2c33e2088ec33ffe285ffc2a923013.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/10/windows8-rt-kb2883201-x64_b1bada64e121a8f543b5f92601b828c8c80414ec.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/08/windows8-rt-kb2862768-x64_564dbd31bd53228eec1cf01fd1ab5559e065c48e.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/11/windows8-rt-kb2889784-x64_bc658c3848f474559be2afd1014dd7a5ae90b944.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2014/02/windows8-rt-kb2919393-x64_a48a08c4fdd949534e6419d775c454071c13bb4d.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/12/windows8-rt-kb2903938-x64_7c37aabdbd2c33e2088ec33ffe285ffc2a923013.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/08/windows8-rt-kb2862768-x64_564dbd31bd53228eec1cf01fd1ab5559e065c48e.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/10/windows8-rt-kb2883201-x64_b1bada64e121a8f543b5f92601b828c8c80414ec.cab  action=allow cache=MISS
Apr/15/2014 05:39:00 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/11/windows8-rt-kb2889784-x64_bc658c3848f474559be2afd1014dd7a5ae90b944.cab  action=allow cache=MISS
Apr/15/2014 05:39:01 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/08/windows8-rt-kb2862768-x64_564dbd31bd53228eec1cf01fd1ab5559e065c48e.cab  action=allow cache=MISS
Apr/15/2014 05:39:01 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/11/windows8-rt-kb2889784-x64_bc658c3848f474559be2afd1014dd7a5ae90b944.cab  action=allow cache=MISS
Apr/15/2014 05:39:02 web-proxy,account proxy: 10.5.50.0 GET http://au.v4.download.windowsupdate.com/msdownload/update/software/updt/2013/11/windows8-rt-kb2889784-x64_bc658c3848f474559be2afd1014dd7a5ae90b944.cab  action=allow cache=MISS
Apr/15/2014 05:40:28 web-proxy,account proxy: 10.5.50.0 GET http://ocsp.verisign.com/MFEwTzBNMEswSTAJBgUrDgMCGgUABBRIt2RJ89X%2B%2BhEzqoBeQg8PymQ2UQQUANhaTCXBIuWLMe9tuvPMXynxDWECEGVSJuGyLhjhWQ8phawi51w%3D  action=allow cache=MISS
Apr/15/2014 05:40:29 web-proxy,account proxy: 10.5.50.0 POST http://ocsp.verisign.com/  action=allow cache=MISS
Apr/15/2014 05:40:29 web-proxy,account proxy: 10.5.50.0 GET http://crl.verisign.com/pca3.crl  action=allow cache=MISS
Apr/15/2014 05:40:29 web-proxy,account proxy: 10.5.50.0 GET http://ocsp.verisign.com/MFEwTzBNMEswSTAJBgUrDgMCGgUABBSpuCE3aK3GivZPzGQJ6L5BRyZofwQUl9BrqCZwyKE%2FlB8ILcQ1m6ShHvICEGZmBVLUZbMfQp91J%2Bpqk78%3D  action=allow cache=MISS
Apr/15/2014 05:40:29 web-proxy,account proxy: 10.5.50.0 POST http://ocsp.verisign.com/  action=allow cache=MISS
Apr/15/2014 05:40:29 web-proxy,account proxy: 10.5.50.0 GET http://csc3-2009-2-crl.verisign.com/CSC3-2009-2.crl  action=allow cache=MISS
Apr/15/2014 05:42:02 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 05:45:43 web-proxy,account proxy: 10.5.50.0 GET http://finance.services.appex.bing.com/Market.svc/ResolveAppTileChart/136.10.000001.136.!HSCHX/1dbcc8cb-cae9-4438-8533-77d43cdf2c81?ms-scale=80&ms-contrast=standard&ms-lang=TR-TR  action=allow cache=MISS
Apr/15/2014 05:47:23 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 05:49:09 web-proxy,account proxy: 10.5.50.0 GET http://appexbingweather.trafficmanager.net/WeatherService.svc/LiveTile?city=Sidney&lat=-33.870&long=151.207&unit=C&lang=TR&mkt=en-US&region=US&appid=C98EA5B0842DBB9405BBF071E1DA76512D21FE36&pi=true  action=allow cache=MISS
Apr/15/2014 05:49:09 web-proxy,account proxy: 10.5.50.0 GET http://appexbingweather.trafficmanager.net/WeatherService.svc/LiveTile?city=Londra&lat=51.506&long=-0.127&unit=C&lang=TR&mkt=en-US&region=US&appid=C98EA5B0842DBB9405BBF071E1DA76512D21FE36&pi=true  action=allow cache=MISS
Apr/15/2014 05:49:09 web-proxy,account proxy: 10.5.50.0 GET http://appexbingweather.trafficmanager.net/WeatherService.svc/LiveTile?city=Mumbai&lat=18.927&long=72.831&unit=C&lang=TR&mkt=en-US&region=US&appid=C98EA5B0842DBB9405BBF071E1DA76512D21FE36&pi=true  action=allow cache=MISS
Apr/15/2014 05:49:09 web-proxy,account proxy: 10.5.50.0 GET http://appexbingweather.trafficmanager.net/WeatherService.svc/LiveTile?city=Paris&lat=48.857&long=2.341&unit=C&lang=TR&mkt=en-US&region=US&appid=C98EA5B0842DBB9405BBF071E1DA76512D21FE36&pi=true  action=allow cache=MISS
Apr/15/2014 05:49:09 web-proxy,account proxy: 10.5.50.0 GET http://appexbingweather.trafficmanager.net/WeatherService.svc/LiveTile?city=New%20York%20City&lat=40.714&long=-74.007&unit=C&lang=TR&mkt=en-US&region=US&appid=C98EA5B0842DBB9405BBF071E1DA76512D21FE36&pi=true  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=136.10.000001.136.!HSCHX&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=151.10.UKX&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=134.10.HSI&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexsports.trafficmanager.net/NewsV1.svc/RSSTileNews/tr-tr/20?index=2&brand=logo  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexsports.trafficmanager.net/NewsV1.svc/RSSTileNews/tr-tr/20?index=1&brand=logo  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=132.10.100000018.132.NI225&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=126.10.!DJI.126.$INDU&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 05:49:10 web-proxy,account proxy: 10.5.50.0 GET http://appexsports.trafficmanager.net/NewsV1.svc/RSSTileNews/tr-tr/20?index=0&brand=logo  action=allow cache=MISS
Apr/15/2014 05:52:24 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 05:57:26 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 05:59:09 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?cup2key=4:133347900&cup2hreq=bead78451e191b7d1f3ac74fbd742e71d6f7737ede1ec4e29e9a8425930c014b  action=allow cache=MISS
Apr/15/2014 05:59:09 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?w=6:gJZE5hgHIUmsU_QiCrTrnDv-JzyFRB1oLPsluN_4KJmAute7nlYWRskSF2WBlB0rNDvvCy_RPWmhtbiS_eg6rhM3iRm0IeUWc20TDLVD8GI6AaSebm3VqqPnNWY-uc6hVButR6XtZyF9QN9B6s68fnLCksvCL7wIWLTNzEYs5l0s action=allow cache=MISS
Apr/15/2014 05:59:09 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?cup2key=4:721154030&cup2hreq=bead78451e191b7d1f3ac74fbd742e71d6f7737ede1ec4e29e9a8425930c014b  action=allow cache=MISS
Apr/15/2014 05:59:09 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?w=6:e-M7pnYqISCtUW6pH1eCdOu7px43JsgzKAAeqbw_pchhHrmWP9hazELWQcOQZ1siWMuzHrRPgxDC49aHC5StcBQEpVLgJY5p5zJcHPv9DdzkX6VxaFYWGog-NlE_6DBTvj_cILCeuFKkEsOwrcz7IFuDS_KDVf89WvUm1xoM2fJ_ action=allow cache=MISS
Apr/15/2014 05:59:17 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?cup2key=4:912660368&cup2hreq=bead78451e191b7d1f3ac74fbd742e71d6f7737ede1ec4e29e9a8425930c014b  action=allow cache=MISS
Apr/15/2014 05:59:17 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?w=6:kiQi0omjjw_Riq40XPuBYT4u1THwj3t58vBTSfmrKzeHxpVPLAYY5xwkqkZTDaWZwmSj2MJREUP9Izeycqak68uUEJkoTSy7q82kyr7Et25vlCDp5luRwrxjlJjZ_npLMmKmKe0N4F-7KzCd0CI0TU4t4GydQUs-0KTjIM9UpP9z action=allow cache=MISS
Apr/15/2014 05:59:17 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?cup2key=4:4060789331&cup2hreq=bead78451e191b7d1f3ac74fbd742e71d6f7737ede1ec4e29e9a8425930c014b  action=allow cache=MISS
Apr/15/2014 05:59:17 web-proxy,account proxy: 10.5.50.0 POST http://tools.google.com/service/update2?w=6:QO_6FQDayHCQv1VP6mWtWkQloxtTo6xJ7OCXOmsiVgovvlicgnIC4PEYF6RRixTXLW5MPtF5KjtYpjqona4zZ6wlc3nZg_b-lfnoGl1ZfdFuq0sicNGPFOZ3y-zceU1uDLmzNiecEGCqCI4Wrz5_LBxFKfKbL-eoMOUqmV3ekvo9 action=allow cache=MISS
Apr/15/2014 06:02:27 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 06:07:29 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 06:12:15 web-proxy,account proxy: 10.5.50.0 GET http://sigs.symantec.com/http://sigs.symantec.com:80/?data=001000142b6e536d65414141555543443241475157ce336c  action=allow cache=MISS
Apr/15/2014 06:12:30 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 06:12:30 web-proxy,account proxy: 10.5.50.0 GET http://sigsmain.symantec.com/http://sigsmain.symantec.com:80/?data=001000142b6e536d65414141555543443241475157ce336c  action=allow cache=MISS
Apr/15/2014 06:12:45 web-proxy,account proxy: 10.5.50.0 GET http://sigs1alt.symantec.com/http://sigs1alt.symantec.com:80/?data=001000142b6e536d65414141555543443241475157ce336c  action=allow cache=MISS
Apr/15/2014 06:16:22 web-proxy,account proxy: 10.5.50.0 GET http://finance.services.appex.bing.com/Market.svc/ResolveAppTileChart/151.10.UKX/982c9a03-bd07-461b-b3ec-c8d94776c1e0?ms-scale=80&ms-contrast=standard&ms-lang=TR-TR  action=allow cache=MISS
Apr/15/2014 06:16:26 web-proxy,account proxy: 10.5.50.0 GET http://www.google.com/  action=allow cache=MISS
Apr/15/2014 06:17:51 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 06:19:10 web-proxy,account proxy: 10.5.50.0 GET http://appexsports.trafficmanager.net/NewsV1.svc/RSSTileNews/tr-tr/20?index=1&brand=logo  action=allow cache=MISS
Apr/15/2014 06:19:10 web-proxy,account proxy: 10.5.50.0 GET http://appexsports.trafficmanager.net/NewsV1.svc/RSSTileNews/tr-tr/20?index=0&brand=logo  action=allow cache=MISS
Apr/15/2014 06:19:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=126.10.!DJI.126.$INDU&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 06:19:10 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=132.10.100000018.132.NI225&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 06:19:11 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=151.10.UKX&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 06:19:11 web-proxy,account proxy: 10.5.50.0 GET http://appexsports.trafficmanager.net/NewsV1.svc/RSSTileNews/tr-tr/20?index=2&brand=logo  action=allow cache=MISS
Apr/15/2014 06:19:11 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=136.10.000001.136.!HSCHX&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 06:19:11 web-proxy,account proxy: 10.5.50.0 GET http://appexbingfinance.trafficmanager.net/Market.svc/AppTile?symbol=134.10.HSI&type=0&mkt=TR-TR&mode=0  action=allow cache=MISS
Apr/15/2014 06:22:52 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 06:27:53 web-proxy,account proxy: 10.5.50.0 GET http://liveupdate.symantecliveupdate.com/minitri.flg  action=allow cache=MISS
Apr/15/2014 06:28:26 web-proxy,account proxy: 10.5.50.0 GET http://ip.salay.com.tr/Mikrotik/Handler.ashx?lokasyon=1&icip=10.5.50.0&number=5074510000  action=allow cache=MISS
Apr/15/2014 06:28:43 web-proxy,account proxy: 10.5.50.0 GET http://ip.salay.com.tr/Mikrotik/Handler2.ashx?numara=5074510000&kod=IKFB2N  action=allow cache=MISS
Apr/15/2014 07:30:26 web-proxy,account proxy: 10.5.50.0 GET http://www.msftncsi.com/ncsi.txt  action=allow cache=MISS
Apr/15/2014 07:30:29 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:30 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://fe1.update.microsoft.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:32 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:33 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:33 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:33 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:33 web-proxy,account proxy: 10.5.50.0 HEAD http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:33 web-proxy,account proxy: 10.5.50.0 GET http://ds.download.windowsupdate.com/w8/2/windowsupdate/redir/wuredir.cab?1404150431  action=allow cache=MISS
Apr/15/2014 07:30:34 web-proxy,account proxy: 10.5.50.0 GET http://finance.services.appex.bing.com/Market.svc/ResolveAppTileChart/126.10.!DJI.126.$INDU/ffebe0c1-2814-4738-9132-cc34aa1d4b77?ms-scale=80&ms-contrast=standard&ms-lang=TR-TR  action=allow cache=MISS
Apr/15/2014 07:30:36 web-proxy,account proxy: 10.5.50.0 GET http://www.google.com/  action=allow cache=MISS
Apr/15/2014 07:30:49 web-proxy,account proxy: 10.5.50.0 GET http://mscrl.microsoft.com/pki/mscorp/crl/mswww(6).crl  action=allow cache=MISS
Apr/15/2014 07:30:54 web-proxy,account proxy: 10.5.50.0 GET http://www.msftncsi.com/ncsi.txt  action=allow cache=MISS
Apr/15/2014 07:30:59 web-proxy,account proxy: 10.5.50.0 GET http://ip.salay.com.tr/Mikrotik/Handler.ashx?lokasyon=1&icip=10.5.50.0&number=5074510000  action=allow cache=MISS
Apr/15/2014 07:31:21 web-proxy,account proxy: 10.5.50.0 GET http://ip.salay.com.tr/Mikrotik/Handler.ashx?lokasyon=1&icip=10.5.50.0&number=5074510000  action=allow cache=MISS
Apr/15/2014 07:31:23 web-proxy,account proxy: 10.5.50.0 GET http://www.msftncsi.com/ncsi.txt  action=allow cache=MISS
Apr/15/2014 07:31:44 web-proxy,account proxy: 10.5.50.0 GET http://ip.salay.com.tr/Mikrotik/Handler2.ashx?numara=5074510000&kod=f4xw%C4%B13  action=allow cache=MISS

Open in new window

0
Comment
Question by:F4SX
  • 3
  • 2
7 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40000839
Something like this should work:
use strict;
use warnings;
use DBI;
my $dir = shift || '.'; # default to current dir
opendir DIR, $dir or die "could not opendir $dir: $!";
my @files = grep m{\.txt$}, readdir DIR; # replace regex with something else if your files are not named *.txt
closedir DIR;
 # replace each undef var with an appropriate value below
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host;port=$port", $user, $pass);
my $sth = $dbh->prepare('insert xxx (date, ip, operation, page) values (?, ?, ?, ?)') or die $dbh->errstr;
foreach my $fil (@files) {
    open IN, "$dir/$fil" or die "could not open $dir/$fil: $!";
    while (<IN>) {
        my ($dt, $ip, $typ, $url) = m{(\S+\s+[\d:]+)\b.*\bproxy:\s+([\d\.]+)\s+(\w+)\s+(\S+)};
        # do whatever you need to to convert $dt into mysql format
        $sth->execute($dt, $ip, $typ, $url) or die $dbh->errstr;
    }
    close IN;
    unlink "$dir/$fil" or die "could not delete $dir/$fil: $!";
}

Open in new window

0
 

Author Comment

by:F4SX
ID: 40000847
everything seems ok expect 0000-00-00 00:00:00 date
how should we over come this.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40002904
Are you saying all dates come up as 0000-00-00 00:00:00 or that there is a value like this in the file?  If the latter, what do you want to do with that row?  If the former (all), I'm not sure what's happening.
0
 

Author Comment

by:F4SX
ID: 40006666
I want to record that Apr/15/2014 05:49:09  into mysql as 2014-08-15 05:49:09
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 40010975
Modification to handle the specific xforms necessary...
use strict;
use warnings;
use DBI;
my %map = (Jan => '01', Feb => '02', Mar => '03', Apr => '04', May => '05', Jun => '06',
                     Jul => '07', Aug => '08', Sep => '09', Oct => '10', Nov => '11', Dec => '12');
my $dir = shift || '.'; # default to current dir
opendir DIR, $dir or die "could not opendir $dir: $!";
my @files = grep m{\.txt$}, readdir DIR; # replace regex with something else if your files are not named *.txt
closedir DIR;
 # replace each undef var with an appropriate value below
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host;port=$port", $user, $pass);
my $sth = $dbh->prepare('insert xxx (date, ip, operation, page) values (?, ?, ?, ?)') or die $dbh->errstr;
foreach my $fil (@files) {
    open IN, "$dir/$fil" or die "could not open $dir/$fil: $!";
    while (<IN>) {
        my ($dt, $ip, $typ, $url) = m{(\S+\s+[\d:]+)\b.*\bproxy:\s+([\d\.]+)\s+(\w+)\s+(\S+)};
        if ($dt =~ m{^(\w+)/(\d+)/(\d+)\s+([\d:]+)$}) {
            $dt = "$3-$map{$1}-$2 $4";
        } else {
            die "invalid date format: $dt";
        }
        $sth->execute($dt, $ip, $typ, $url) or die $dbh->errstr;
    }
    close IN;
    unlink "$dir/$fil" or die "could not delete $dir/$fil: $!";
}

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The viewer will learn how to count occurrences of each item in an array.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question