Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

perl script with awk

Posted on 2014-04-14
7
Medium Priority
?
450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

721 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