Solved

perl script with awk

Posted on 2014-04-14
7
370 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
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…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now