Just to double check again - have you actually try normal / direct logpaser.exe at command line without the INTO SQL part ?
LogParser.exe file:testlog.sql -i:w3c
where testlog.sql is ->
SELECT 1 AS urilog_id,
TO_TIMESTAMP(date, time) AS date,
TO_INT(time-taken) AS time-taken,
TO_STRING(c-ip) AS clientIP,
TO_STRING(cs-user) AS clientUserName,
TO_STRING(x-exception-id) AS exception-id,
TO_STRING(sc-filter-result) AS sc-filter-result,
TO_STRING(cs-categories) AS cs-categories,
TO_STRING(sc-status) AS sc-status,
TO_STRING(s-action) AS s-action,
TO_STRING(cs-method) AS cs-method,
TO_STRING(rs(Content-Type)) AS Content-Type,
TO_STRING(cs-uri-scheme) AS cs-uri-scheme,
TO_STRING(cs-host) AS cs-host,
TO_STRING(cs-uri-port) AS cs-uri-port,
TO_STRING(cs-uri-path) AS cs-uri-path,
TO_STRING(cs-uri-query) AS cs-uri-query,
TO_STRING(cs-uri-extension) AS cs-uri-extension,
TO_STRING(cs(Referer)) AS referer,
TO_STRING(cs(User-Agent)) AS User-Agent,
TO_STRING(s-ip) AS s-ip,
TO_STRING(sc-bytes) AS sc-bytes,
TO_STRING(cs-bytes) AS cs-bytes,
TO_STRING(x-virus-id) AS x-virus-id,
TO_STRING(r-supplier-ip) AS r-supplier-ip,
TO_STRING(r-supplier-port) AS r-supplier-port
FROM test.log
I have tried this many times with your log samples, swap them upside down many times and still can't crash it.
I don't have a sql box to test this, can you do again at command line with the INTO to SQL, does it crash when you do it at command line manually withouth your vbscript.
Without the INTO statement the logfiles are parsing correctly.
So the error is on the db site then? I dont know what logparser does, that some logs will get imported correctly and some dont. As i already stated several times, when i switch the positions from the entries, its working. I assume it doesnt work when there
are spaces in the first quotes.
Well, it looks like a combination of INTO DB clause + certain format of the log entry cause that. Unfortunately I can't repro this (the DB part). I don't know any workaround for this except to see if you can try remove the white space for the csCategories
field before load it into the DB.
KenjinxD
9 Posts
Re: Importing Logs into a mssql db (maybe a bug?)
Nov 27, 2012 08:25 AM|LINK
Im sorry: the errors from the event log i posted were from the VBScript.
The actual error from logparser.exe which got executed manually is:
So i tested some more and figured something out.
For example if you have the following logfile:
The logparser crashes with the above logfile. Now here comes the strange thing: When i edit the
"Malicious Sources;Software Downloads" to "MaliciousSources;SoftwareDownloads" the logparser wont crash and will it import it.
My conclusion was, that it's because of the whitespaces in the cs-categories field.
So after all i've tried to switch both entries so it looks like that:
As you can see in the first entry there is a whitespace in cs-categories as well. BUT that logfile was imported correctly without any crash.
I cant explain that behaviour and it's getting stranger and stranger.
qbernard
5016 Posts
MVP
Moderator
Re: Importing Logs into a mssql db (maybe a bug?)
Nov 29, 2012 02:20 AM|LINK
I'm sorry, but I can't repro this in my machine.
Just to double check again - have you actually try normal / direct logpaser.exe at command line without the INTO SQL part ?
LogParser.exe file:testlog.sql -i:w3c
where testlog.sql is ->
SELECT 1 AS urilog_id, TO_TIMESTAMP(date, time) AS date, TO_INT(time-taken) AS time-taken, TO_STRING(c-ip) AS clientIP, TO_STRING(cs-user) AS clientUserName, TO_STRING(x-exception-id) AS exception-id, TO_STRING(sc-filter-result) AS sc-filter-result, TO_STRING(cs-categories) AS cs-categories, TO_STRING(sc-status) AS sc-status, TO_STRING(s-action) AS s-action, TO_STRING(cs-method) AS cs-method, TO_STRING(rs(Content-Type)) AS Content-Type, TO_STRING(cs-uri-scheme) AS cs-uri-scheme, TO_STRING(cs-host) AS cs-host, TO_STRING(cs-uri-port) AS cs-uri-port, TO_STRING(cs-uri-path) AS cs-uri-path, TO_STRING(cs-uri-query) AS cs-uri-query, TO_STRING(cs-uri-extension) AS cs-uri-extension, TO_STRING(cs(Referer)) AS referer, TO_STRING(cs(User-Agent)) AS User-Agent, TO_STRING(s-ip) AS s-ip, TO_STRING(sc-bytes) AS sc-bytes, TO_STRING(cs-bytes) AS cs-bytes, TO_STRING(x-virus-id) AS x-virus-id, TO_STRING(r-supplier-ip) AS r-supplier-ip, TO_STRING(r-supplier-port) AS r-supplier-port FROM test.logI have tried this many times with your log samples, swap them upside down many times and still can't crash it.
I don't have a sql box to test this, can you do again at command line with the INTO to SQL, does it crash when you do it at command line manually withouth your vbscript.
Bernard Cheah
KenjinxD
9 Posts
Re: Importing Logs into a mssql db (maybe a bug?)
Dec 03, 2012 05:58 AM|LINK
Without the INTO statement the logfiles are parsing correctly.
So the error is on the db site then? I dont know what logparser does, that some logs will get imported correctly and some dont. As i already stated several times, when i switch the positions from the entries, its working. I assume it doesnt work when there are spaces in the first quotes.
That (above) one is working but
isnt. So i my guess its the csCategories field then. However when the table got created it was like: "[csCategories] [varchar](255) NULL"
Im going round in circles, because that's killing me and i cant narrow it down. Argh.
qbernard
5016 Posts
MVP
Moderator
Re: Importing Logs into a mssql db (maybe a bug?)
Dec 11, 2012 12:11 AM|LINK
Sorry, was travelling.
Well, it looks like a combination of INTO DB clause + certain format of the log entry cause that. Unfortunately I can't repro this (the DB part). I don't know any workaround for this except to see if you can try remove the white space for the csCategories field before load it into the DB.
Bernard Cheah