Link to home
Start Free TrialLog in
Avatar of F4SX
F4SX

asked on

perl script with awk

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

Avatar of wilcoxon
wilcoxon
Flag of United States of America image
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

Avatar of F4SX
F4SX

ASKER

everything seems ok expect 0000-00-00 00:00:00 date
how should we over come this.
Avatar of wilcoxon
wilcoxon
Flag of United States of America image
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.
Avatar of F4SX
F4SX

ASKER

I want to record that Apr/15/2014 05:49:09  into mysql as 2014-08-15 05:49:09
ASKER CERTIFIED SOLUTION
Avatar of wilcoxon
wilcoxon
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial