Search results matching tag 'IISW3C input format'

Missing Date Field in IISW3C logfile

dinder — Fri, 11 Jan 2008 15:21:22 GMT

I need to parse logfile from server, but notice that no Date Field are configure

--------------

#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2008-01-06 00:01:02
#Fields: time cs-method cs-uri-stem cs-uri-query cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) sc-status sc-substatus sc-bytes cs-bytes time-taken
00:01:02 GET /i.php - - 90.24.187.16 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+InfoPath.2;+.NET+CLR+2.0.50727) - - 200 0 2587 427 62

--------------------------

I would like to use Logparser to rewrite my log, is it possible to extract the line 3 (#Date: 2008-01-06 00:01:02) to create and add the date field to my new file log ?

Thks

RE: Filtering IIS logs of bot entries and outputing to multiple files

LogParser User : mila — Wed, 27 Jun 2007 21:59:00 GMT

Well this query did not work:

SELECT* FROM c:\logs\april2007\*.* WHERE cs(uri-agent) NOT IN(SELECT botname FROM badbots.csv)" i:IISW3C o:W3C

The second select is being executed as having the input format specification IISW3C.

RE: Filtering IIS logs of bot entries and outputing to multiple files

LogParser User : mila — Wed, 27 Jun 2007 20:19:00 GMT

Hope this is useful to an apprentice like myself:

After some trial and error I have a working version of the second query using multiplexing.

The first query simply filters out rows containing a certain string from an aggregate of IISW3C input sources and outputs to a single w3c output format file named w3c_out.log.

It contains rows whose first fields are the original IIS log source path (it took me a few tried to figures out that the field name is logfilename2, not logfilename1). I use this field to create an output structure origfoldername/filenames via multiplexing (put the rows in separate files depending on where they originally came from as follows):

The query in run on w3c_out.log:

logparser "SELECT EXTRACT_PATH(logfilename2), EXTRACT_FILENAME(logfilename2), date, time, c-ip, s-ip, cs-method, cs-uri-stem, cs-uri-query, sc-statu
s, sc-bytes, cs-bytes, time-taken, cs-version, cs(User-Agent), cs(Cookie), cs(Referer) INTO *_Filt\FILT_* FROM w3c_out.log" -i:W3C -o:W3C -e:10

The original folder and files were named:
c:\logs\april2007\log1.log ....log5.log

The first query produced c:\w3c_out.log

The 2nd query used multiplexing to reorganize the output records as follows:
c:\logs\april2007_filt\filt_log1.log ...filt_log5.log

Now I need to figure out the syntax for running the first query against not a single search term but many terms in a txt file. Actually it seems this is possible by putting the filter terms in a csv and using a SELECT embedded in a NOT IN:

SELECT* FROM c:\logs\april2007\*.* WHERE cs(uri-agent) NOT IN(SELECT botname FROM badbots.csv)" i:IISW3C o:W3C

Filtering IIS logs of bot entries and outputing to multiple files

LogParser User : mila — Tue, 26 Jun 2007 21:04:00 GMT

I raised the question below to google groups and received an answer (also below). I'm new to SQL, Logparser, scripting with autoit, and IIS so please excuse my ignorance. I'm having trouble with syntax despite having looked at the documentation. I need to figure our how to write the two queries suggested in the answer to my google groups question pasted further below.

One thing I'm trying to figure out is whether there is an abbreviated way to refer to all fields in a WHERE clause. I want to remove all lines in the log file that contain "botname". Its easy enough to say:

logparser "SELECT* INTO A1.log FROM A.log WHERE cs(User-Agent) NOT LIKE '%botname%' " -i:IISW3C -o:IIS

But how do I tell logparser to query against not just the cs(User-Agent) field, but all fields? Or do I even need to be searching in the other fields for this particular task?

Here, in a question posted to google, are more details on what I am trying to do (the query I will ultimately need will be more complicated than the above example, but it would be nice to get it working in the simplified form first):

I just started reading about logparser and I would like to use it to
rid IIS logs of entries containing bot names listed in a text file.
I'll be using autoit and Logparser COM.

I was wondering what the best way to go about this would be. From what
I have read in the logparser documentation, it seems that the
following would not be possible:

1. Logparser query cannot be setup such that: it is passed a file (say
a list of bot names) and told to filter the inputfiles of all lines
containing any of the terms in said file.

2. Logparser cannot produce output upon running a query on a set of
multiple files such that the output is organized into files that
correspond to the source filenames. (So if files A, B, C are to be
filtered, the output cannot automatically be placed in files A1, B1,
C1 such that the contents of A1 = filtered contents of A, and
similarly for B1 and C1).

Given the above assumptions, I have concluded that If I want to filter
a large set of files and if the filtering involves a large set of
filter terms, the best way to utilize logparser would be to run a
query multiple times in nested loops (inner loop traverses the list of
bots, and outer loop traverses the list of files. [In effect: For each
file A, [for each filter term 'bot' run a query that retreives all
lines NOT containing "bot", placing the output in A1. Repeat query on
file A for all bots names] Repeat for all files].

I'd like to confirm whether or not my assumptions 1 and 2 above are
mistaken, and if my suggested way of completing this task is over
complicating things. Also any further tips or tool /scripting
recommendations are most welcome. Thanks.

ANSWER:
I think your approach is a bit complicated

You overlook the fact that LogParser allows you to make a SQL query
against an aggregation of log sources while retaining knowledge of
which log source a particular log entry came from. This means that
what you want to do can be done with two commands which pass over the
logged data only twice.

The first command is a LogParser query over the aggregate of the log
sources with a SQL WHERE clause constructed from the bot names in the
text file. This simultaneously walks both your nested loops with a
single SQL query. This allows File:A,File:B,File:C --(SQL-based-
Filter)-> File:A1B1C1

The second command chops up the output of the first command based on
the Filename field which indicates the original log source that the
data came from. This allows you to reconstruct File:A1, File:B1,
File:C1 given a file whose contents is A1B1C1 concatenated

Of course, it is possible that aggregating everything into memory all
at once is too much, in which case your incremental approach would be
necessary.

//David

RE: Need to group fields...

LogParser User : DEinspanjer — Mon, 18 Jun 2007 10:30:00 GMT

Write out the outputs of the first query to a text file using executeBatch then run the second query against that one.

RE: Custom field to IIS input

LogParser User : past777 — Mon, 18 Jun 2007 07:03:00 GMT

in qwery-
select 1 as id, ... from iisw3c_file

and set id in your real sql table as int, identity - yes

id's in your real sql table will be 1,2,3,4,5,6,...

RE: Need to group fields...

LogParser User : e.Brito — Sat, 16 Jun 2007 05:32:00 GMT

hi,
can i do this using the LP COM API let's say, in VB.NET?
thanks

Syntax Help

LogParser User : erikkl2000 — Fri, 08 Jun 2007 14:07:00 GMT

Hi,

Am i glad to find this forum...

I trying to get this syntax correct and when ever i try to access the source file from a \\Share or a c:\ paths i keep getting an error of:
The system cannot find the path specified.

***Here is my query******

"SELECT DISTINCT cs(Cookie) FROM C:\Documents and Settings\Erik\Desktop\W3SVC1452108243\*.log"

-------------------------------------

Can someone give me some pointers here to what may be wrong?

erik

RE: Input Format FROM bug?

LogParser User : Former_BigIronGuy — Sun, 27 May 2007 20:41:00 GMT

Phil,
It has been since v2.0 that I did a lot with LogParser. I *do* remember that there were some logfiles on our intra-net server that for whatever reason, were completely munged. At that time, the fix I resorted to was to list the IN_ROW_NUMBER of the output and then built a two part query to take every thing up to the bad record and one to pick up everything after.
Howsoever, it appears to me that 2.2 has lots more robustness than what I remember, and appears to be able to successfully handle the exception rows...

FBIG

Trying to capture logins and URL's within a site visited by each user

LogParser User : GKerr — Sat, 19 May 2007 06:08:00 GMT

I'm a rookie (obviously) with LogParser but was hoping to use this tool to capture a when users have logged in (date and time) to a specific site grouped by user name.

The site is a Sharepoint site so there is domain\username information available but I'm hoping to be able to capture when users visit specific URL's. I'm guessing there will need to be WHERE clauses to locate the URL in that I'm trying to count the number of visits to.

For example I'd like to see something like this:

Domain\gkerr "http:\\Sharepoint\Site1 01/01/2007 10:00am

Domain\gkerr http:\\sharepoint\site2 01/01/2007 10:10am

Domain\nsmith http:\\sharepoint\site2 01/01/2007 10:12am

I've spent a few hours experimenting with examples I've found online but I haven't found exactly what I'm looking for.

Thanks in advance

Guy