Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
Could you post a snippet of your log, including the header and some entries which don't import? –  Douglas Mar 12 '13 at 20:24
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
Attached part of log above. –  user2158283 Mar 12 '13 at 21:29
    
I change to -i:IISW3C but the outcome was the same. –  user2158283 Mar 12 '13 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 '13 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. –  user2158283 Mar 13 '13 at 1:01
    
Cool. Please mark as resolved. –  igelkott Mar 13 '13 at 7:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.