Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

perl script with awk

Posted on 2014-04-14
7
Medium Priority
?
465 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
5 Comments
 
LVL 27

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 27

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 27

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
I have written articles previously comparing SARDU and YUMI.  I also included a couple of lines about Easy2boot (easy2boot.com).  I have now been using, and enjoying easy2boot as my sole multiboot utility for some years and realize that it deserves …
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Suggested Courses

783 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