2

I am trying to import iis log to mysql database but I am only getting part of the file. I am using this code:

LogParser "SELECT * INTO iisLog FROM \\appdev1\c$\inetpub\logs\LogFiles\W3SVC1\u_ex130312.log" -i:W3C -o:SQL -server:app4 -database:iislogs -driver:"MySQL ODBC 5.1 Driver" -username:iis -password:P@ssW0rd -createTable:ON"

Date, Time, sport, and a few others are being populated but csusername, sip, csURIStem and others do not have any data showing. When I look at the log file I can see that there should be data populating these fields. What am I missing?

#Software: Microsoft Internet Information Services 7.5
#Version: 1.0
#Date: 2013-03-12 00:00:02
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken
2013-03-12 00:00:02 10.1.65.119 GET / - 80 - 10.1.65.190 - 401 2 5 0
2013-03-12 00:00:02 10.1.65.119 GET / - 80 - 10.1.65.191 - 401 2 5 0
2013-03-12 00:00:07 10.1.65.119 GET / - 80 - 10.1.65.190 - 401 2 5 15
2013-03-12 13:11:25 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 109
2013-03-12 13:11:25 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 93
2013-03-12 13:11:26 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 15
2013-03-12 13:11:26 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 15
2013-03-12 13:11:26 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 MCORP\TRAIN20 10.1.65.249 - 200 0 0 499
2013-03-12 13:11:26 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 MCORP\TRAIN7 10.1.65.249 - 200 0 0 499
2013-03-12 13:11:26 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 109
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 93
2013-03-12 13:11:27 10.1.65.119 GET / - 80 - 10.1.65.190 - 401 2 5 0
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 15
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/updates/net4 format=xml 80 - 10.1.65.249 - 415 0 0 15
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 MCORP\TRAIN9 10.1.65.249 - 200 0 0 109
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 - 10.1.65.249 - 401 2 5 15
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/updates/net4 format=xml 80 - 10.1.65.249 - 415 0 0 0
2013-03-12 13:11:27 10.1.65.119 POST /SP/PC/session.svc/ method=Get 80 MCORP\TRAIN8 10.1.65.249 - 200 0 0 109
2013-03-12 13:11:28 10.1.65.119 GET /SP/PC/appsets.svc/ level=0 80 MCORP\TRAIN9 10.1.65.249 - 200 0 0 46
2013-03-12 13:11:28 10.1.65.119 GET /SP/PC/appsets.svc/CCRF_TRAIN/ level=1 80 MCORP\TRAIN9 10.1.65.249 - 200 0 0 202
Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5466) 401 2 5 109
2013-03-12 15:10:56 10.1.65.119 POST /SP/PC/App/SystemConfigService/SystemConfigService.asmx - 80 - 10.36.24.125 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5466) 401 2 5 156
2013-03-12 15:10:57 10.1.65.119 GET / - 80 - 10.1.65.190 - 401 2 5 0
1
  • Could you post a snippet of your log, including the header and some entries which don't import?
    – Douglas
    Mar 12, 2013 at 20:24

1 Answer 1

0

Simplest change would be to use the -i:IISW3C option rather than -i:W3C.

Otherwise, try creating (or fixing) the MySQL table with mostly varchar fields (and then skip the createTable).

Safer (and most recommended) would be to output to -o:csv instead and then get that file into MySQL (eg, mysqlimport). This would also allow you to check that the fields are as you expect. May wish to test on a portion of the real file of course.

5
  • Attached part of log above. Mar 12, 2013 at 21:29
  • I change to -i:IISW3C but the outcome was the same. Mar 12, 2013 at 22:30
  • Would be good to look at the MySQL table "desc table". Maybe post that as well? Since you get some fields, it's possible that some of the column types aren't correct -- especially going through ODBC. I'd bet that (eg) cs-uri-stem is too short.
    – igelkott
    Mar 12, 2013 at 23:40
  • Looks like it is a MySql ODBC driver issue. I tried 3.51 driver and everything seems to work fine all fields are populated. Mar 13, 2013 at 1:01
  • Cool. Please mark as resolved.
    – igelkott
    Mar 13, 2013 at 7:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .