General Discussion

Inner Join 2 files

frank@slimcd.com — Tue, 17 Nov 2009 16:05:53 GMT

Can log parser be used to inner join to separate log files to build a combined report?

A dedicate serveur for logs management

zeraphul — Fri, 13 Nov 2009 16:24:59 GMT

I'm still loocking a way to manage and centralize on one server:

- The logs collecting
- The logs processing
- The logs reports

Does someone knew a good third-party app that do this kind of job ?
I'll thingking of create a collections of scripts to do the job but i think that this is sometings that may be allready exist ?

IIS log parsing timestamp woes

Apoco — Wed, 11 Nov 2009 23:34:43 GMT

I'm trying to count the number of hits for a particular URL on our web site by parsing our IIS logs using Log Parser 2.2. Everything seems to be working fine, except that its handling of timestamps is greatly confusing me.

The IIS logs have all of the timestamps expressed in UTC time. Therefore, in my application, I convert the server's time to UTC before plugging it into the query. However, when I try to query for the current day's data, I get back a zero count, despite me seeing the records in the log file. The generated query I try to run to get everything within the current day looks something like this:

    SELECT COUNT(*) 
    FROM \\Server\IIS Logs\LogFiles\W3SVC1\u_ex*.log
    WHERE 
        cs-method = 'GET' 
        AND cs(Referer) NOT LIKE '%ntorus%'
        AND c-ip NOT LIKE '192%'
        AND c-ip NOT LIKE '127%'
        AND (
            cs-uri-stem = '/' 
            OR cs-uri-stem = '/myurl')
        AND sc-status BETWEEN 200 AND 299 
        AND date BETWEEN 
            TIMESTAMP('2009-11-11 07:00', 'yyyy-MM-dd hh:mm') 
            AND TIMESTAMP('2009-11-12 07:00', 'yyyy-MM-dd hh:mm')

It looks like for some reason the current day's data is getting skipped. When expanding the date range, I get back data just fine. Why is this happening?

New GUI tool for MS Log Parser, Log Parser Lizard

lizardlabs — Wed, 05 Dec 2007 12:10:29 GMT

Becouse the command-line interface for Microsoft Log Parser is not very intuitive, I have created Log Parser Lizard, a Microsoft Log Parser GUI tool for managing queries, exporting results to Excel, charts, etc… In addition I have added input filters for log4net file format and SQL server T-SQL queries.

You can download Log Parser Lizard from here www.lizardl.com

Microsoft.Net 2.0 is required.

Help with parsing activesync data

Ebucel — Thu, 22 Oct 2009 14:48:07 GMT

I copied the information from http://msexchangeteam.com/archive/2006/02/14/419562.aspx

but It does not output any info:

Statistics:
-----------
Elements processed: 762475
Elements output: 0
Execution time: 2.25 seconds

is what i get after running the sql with this commandline:

C:\Program Files\Log Parser 2.2>LogParser.exe -i:IISW3C -o:CSV file:C:\lpar\Hits
_by_User.sql

I know we have Iphone users and other activesync devices.

I want to generate a report that list users, device, last sync, last sync attempt which I thought the SQL i found would do it just doesn't seem to output the data or create the csv file.

Thanks In Advance

How to divide a number in SQL

massel — Tue, 13 Oct 2009 17:33:39 GMT

Hello, I have the following SQL query that works fine:

SELECT cs-uri-stem, SUM(sc-bytes) as GB, count(*) AS hits INTO C:\test.csv FROM * GROUP BY cs-uri-stem" -i: w3c -o: csv

What I want to do is divide the number I get in SUM(sc-bytes) as GB by 1000000000. I was able to use the DIV function, but it drops all the zeros after the decimal and I want to keep those. I tried using a simple statement like SUM(sc-bytes) / 1000000000 as GB, but I get an error in LogParser. Thanks for any help.

Windows Event Logs

Hybrid125 — Wed, 11 Nov 2009 15:31:01 GMT

I've been trying to import specific security events from about 50 computers on my network to a SQL database using a scheduled automated task.I have a batch file (Batch1) that first populates a text file with a list of all the computers on our network using a vbscript. Then the batch file (Batch1) runs another VBScript that adds the servername and time range variables to another batch file (Batch2). Batch2 executes logparser with the variables specified and imports from every computer on my network to my SQL database.This works all fine and dandy, but when I go to specify the events I'd like to import on the Logparser execution line in Batch2, I exceed the limit of the batch line.

I setup a sql file with the complete query for logparser and have used logparser to do a "file:filename" but when I do this, I can no longer use the variable for the computer name.

I'm not very proficient with VBscript. The couple scripts I'm using both were modified from others I've found online where people were trying to accomplish a similar objective.

Does anyone know of a way to integrate both methods so I can use the SQL file in the Logparser batch with a variable for the computername?

What is the future (or sucessor) to Log Parser?

msdn_dave — Tue, 10 Nov 2009 18:21:57 GMT

Not trying to be provactive here, but just wondering if there is anything that I might have missed indicating that we'll see any fixes (or a sucessor) to Log Parser 2.2.10?

Looks like its been three years since the creator's departure. In Nov 2006, there was a hint at good things to come in a post from the creator, but doesn't look like that has happened. Has anyone from MS made any sort of satement one way or the other, or pointed to what we "should" be moving to?

Web files not hit

jasoncamp — Fri, 24 Apr 2009 19:17:36 GMT

Is it possible to use the log parser to demermine what pages under wwwroot have not been hit in the past month?

Oddity processing logs: transition from 10-31 to 11-01

REHilliard — Thu, 05 Nov 2009 14:41:29 GMT

Using version 2.2.10, processing Exchange 2003 tracking logs, grouping by day, when I tried to process the week 10-26 thru 11-01, I would only get data thru 10-31. The output from 11-01 would never show. I've been using the job to process logs for a couple of months and had not had any issues. When I process 11-1 by itself, it works just fine. Immediately I assumed it was because of the single-digit day (2009-11-1), but that proved wrong, because transitioning from 09-30 to 10-1 showed no such problems. But to test, I manually changed the day to 2-digits...to no avail. If I "reverse" the processing order (11-1 and then 10-31), it works just fine. If I try 11-2 instead of 11-1, the same thing happens: "Error while parsing field Date: Vaule "2009-11-1" is not a vaild W3C date ("yyyy-MM-dd")" is the error. As I said, processing by itself or ahead of the 10-31 logs works just fine, it's only when it transitions from 10-31 to 11-1. (and not from 09-30 to 10-01). It's not a show stopper, except trying to use the charting options for the entire 7 day spread. Really just passing this along in case someone runs into this issue.

Create combined chart (or table)

saturno — Thu, 05 Nov 2009 10:28:46 GMT

Hi,

I have a log file from which I would like to produce a combined chart.

That file log's all the fax's sent by a fax server.

From that log I would like to extract (and already done that) the total fax's sent by specific users and (not achieved yet) in the same chart, the total number which were sent with result = "success".

The log has many other fields, but for this I only need "SenderName" and "result".

I can extract and chart each COUNT in two different graphs, one for total fax's sent by a User, and a second for total fax's sent with 'SUCCESS'.

I would like to create just one chart with two columns for each user: one for the total fax's sent, and another with all which were successfully sent.

Can someone help me?

Thanks

I need help figuring out how to tell what is an .exe using logparser on IIS 5.0 -6.1

warpig — Mon, 02 Nov 2009 15:04:30 GMT

Hi Folks,

I have been trying to figure out how to get this info from multiple places on the net , have come close but not all the way.

So here is the deal I need to know if there is a way I can use log parser with a script command to tell what the real running applications are on IIS web servers.

I have 31 web servers some have 180 folders some have 30 and cannot assume each web folder is running application in the virtual directory but cannot assume they are not.

can someone help me with this? is it possible?

Need help parse IIS log

togo413 — Wed, 30 Sep 2009 04:00:13 GMT

Hi everyone,

I am new to log parse. Currently download logparse2.2 . I need to implement a .net web app to parse the iis site usage detail for iis log. I have try the sample provided in logparse2.2 . It seem doesn't work accordingly. Need help for sample code or any good guidance web site.

Thanks

Querying logs having single quotes in field data.

rhitam_sanyal — Thu, 29 Oct 2009 06:27:12 GMT

Hi all I am trying to use the logparser vis COM using c# code . I am usign a function pareLog which returns a dataset after querying the TSV file . Lets say I have a data which looks like this : Field1 Field2 Data1 Da'ta2nsomemore Notice the single quote in the data under field2.Now my query looks something like this : string strSQL = "select * from 'C:\20091014_ErrorLogs.log' where Field2 like '%Da'ta'"; Dataset DS = parselog(strsql); the parselog function i am using from the following link : http://www.leastprivilege.com/UsingLogParserFromC.aspx obviously the above query is throwing an exception since anything after the second single quote in the where condition is taken as a part of the query. So how to tackle this problem ? Any help greatly appreciated.

Run LogParser from Removable media

keydet89 — Wed, 28 Oct 2009 02:09:42 GMT

Is it possible to run LogParser from removable media, such as a CD or a thumb drive?

The reason I'm asking this is to perform incident response on Windows 2008 systems in a data center, most often when I don't own those systems. The goal here is to get the contents of the Windows Event Logs in an easily parseable format.

Thanks, your assistance is greatly appreciated.

Cannot find any file matching "C:\inetpub\logs\LogFiles\W3SVC4\ex*.log"

saphua — Mon, 05 Oct 2009 21:17:52 GMT

Hi there,

I've made a WinForms application that gets the traffic of a single site. This works perfectly, until I decided to make a WebForms application out of it instead. The exact same code (see below) now throws the following exception:

System.IO.FileNotFoundException: Error executing query: Cannot open : Cannot find any file matching "C:\inetpub\logs\LogFiles\W3SVC4\ex*.log"

The exception makes sence, since all log files are prefixed with 'u_ex' and not 'ex'. It's just strange that it does work when ran from a WinForms application.

Could anyone please help me with this?

Thanks!

Code:

private void UpdateByDateRange(DateTime dateFrom, DateTime dateTo)

{

string siteId = Request.ServerVariables["INSTANCE_ID"];

LogQueryClassClass logger = new LogQueryClassClass();

COMIISW3CInputContextClass inputContext = new COMIISW3CInputContextClassClass();

string query = "SELECT SUM(TO_REAL(sc-bytes)) AS sentTotal, SUM(TO_REAL(cs-bytes)) AS receivedTotal FROM <" + siteId + "> WHERE TO_TIMESTAMP(date, time) >= TO_TIMESTAMP('" + dateFrom.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-MM-dd HH:mm:ss') AND TO_TIMESTAMP(date, time) <= TO_TIMESTAMP('" + dateTo.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-MM-dd HH:mm:ss')";

ILogRecord record = logger.Execute(query, inputContext).getRecord();

decimal trafficSent = decimal.Parse(record.getValue("sentTotal").ToString());

decimal trafficReceived = decimal.Parse(record.getValue("receivedTotal").ToString());

// ...

}

Import from (Unix) CSV to SQL

NickStan — Fri, 23 Oct 2009 16:36:27 GMT

I am having issues importing from CSV to a SQL table using Logparser.

The csv file was created on a unix box so the line breaks are just Char(10)

Here is the command I am using:

LogParser -o:SQL -i:CSV "SELECT merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,aw_thumb_url,brand_id,brand_name,commission_amount,commission_group,condition,currency,delivery_time,display_price,ean,in_stock,is_hot_pick,isbn,is_for_sale,language,merchant_thumb_url,model_number,mpn,parent_product_id,pre_order,product_type,promotional_text,rrp_price,specifications,stock_quantity,store_price,upc,valid_from,valid_to,warranty,web_offer INTO dbo.product_import FROM C:\Shares\data\23_10_2009.csv"
-server:UK-App -database:test -driver:"SQL Server" -username:i_writer -password:i_writer777* -createTable:OFF

Error messge:

Task aborted.
SQL table column "merchant_product_id" data type is not compatible with
SELECT clause item "merchant_product_id" (type INTEGER)

What I cant understand is that merchant_product_id is setup as an Int in the database, yet the error message is saying that the data type is not compatible?

Thanks

Nick

Logging Application Block (Enterprise Libary 4.1)LogParser GUI?

brix — Fri, 12 Jun 2009 10:57:48 GMT

Hi,

I am using the Enterprise Library Application Block and wondering if there is a Visual GUI ,heard of Log Parser Lizard or any other will do that I can easily point to a folder or something similar and view my logs etc....

Before reiventing the wheel and writing my own can somebody tell me if such a tool already exists. I have quickly looked at Log Parser but not sure it works with enterprise library logs etc...

thanks for any suggestions or input

Merge two outputs

saturno — Tue, 20 Oct 2009 16:46:31 GMT

Hi everyone,

I have two LP queries that return different results from the same origin.

One output's a 3d exploded pie chart, and a second that return a table with 4 columns with a sumarized result of a second query.

How can I run this both (separate) queries keeping in mind that the result should be (for example) just one html page? (chart on top, and data below).

I also would like to embed in the output the LogParser statistics presented in the end of processing:

Elements processed:

Elements output:

Execution time:

How can I achieve this?

P.S.: I have the Logparser Toolkit (Syngress), and altough it is quite extensive, I didn't found the answer for this questions...

Retrieving username through logparser

shikhar_99 — Thu, 22 Oct 2009 10:14:11 GMT

HI,

I need a query to retrieve a particular user from the security event logs through logparser.For eg: there is user named JOHN then i want to know how many times JOHN logged in on a particular server.Any help regarding this would be appreciated.

Thanks in Advance

Reading data FROM an ODBC data source (such as MySQL)

Kevinst — Tue, 20 Oct 2009 09:54:49 GMT

Hello,

is there a way to READ the data from an ODBC data source (such as MySQL) instead of log files?

I know that it is possible to write the results to a data base. But is it possible to read data from a data base to build charts and so on?

Thanks,

Kevin

Logparser with KMS logs

jf — Mon, 14 Sep 2009 23:13:45 GMT

Hello..

I would like to generate reports off of a KMS server. More specifically I would like to query a remote kms server {logs for the server} and output the log results to an html page every hour or so. I have figured out how to query the application/security logs on a remote server but I have not been able to get to the kms logs. Any example queries would be greatly appreciated :) Thanks.

NAT parameters -rtp:-1

dronebee — Wed, 14 Oct 2009 23:26:19 GMT

I am having some issues using the parameter -rtp-1. It comes up with an invalid value. However if I put it in single quotes -rtp:' -1' it works. All the documentation states otherwise. What am I doing wrong ?

How to write space in log parser query folder path

togo413 — Fri, 09 Oct 2009 11:03:54 GMT

Hi guys,

My iis log folder path got space in between. It generate the error below

Please help.

Below is my code :

path = @"C:\iis log\servera\log\"; query = @"SELECT distinct date, cs-uri-stem, cs-uri-query, cs-username, time FROM " + path + filename; query += " WHERE cs-uri-stem LIKE '%.aspx%' ";

Error code :

Unexpected error: Error parsing query: Syntax Error: extra token(s) after query: 'log\servera\log\ex090901.log' [SQL query syntax invalid or unsupported.]

Timestamping output file

mattsmith321 — Tue, 29 Sep 2009 19:03:17 GMT

Hi everyone!

I currently use the following format for running my LogParser queries:
- Name.bat - A batch file used for all of the command line settings. When needed, I can get fancy with looping and scripts but for the most part it is just the call to LogParser with all the params.
- Name.sql - A file that contains the LogParser query that I want to execute. I use this separate file with the .sql extension so that I can format my query a little better and the extension helps pick up some sql syntax highlighting in some editors.
- Name.txt - The actual output file.

Note that the 'Name' prefix above is often replaced with a name that is relevant to the particular queries I am performing: UniqueUsers, HitsByDay, etc.

Lately I have just been using the same .sql file and commenting out and modifying the query as needed. This works fine until I either need to get back to a previous or inadvertently overwrite an output file that I wanted to keep.

What I would like is for each execution of the batch file, the output is sent to a file that is named is name with the current timestamp. In addition, I would like for the contents of the .sql file to be copied to the top of the file. That way, all of my queries and output are saved for future reference.

I'm assuming I would probably just end up doing it in the batch file and passing in the timestamp/filename to the .sql file?

Here are some examples of my current files:

NAME.BAT
=======
"C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" file:Web.qry -i:iisw3c -o:csv -headers:on -tabs:off -filemode:1 -q:off

NAME.SQL
=======
SELECT
COUNT(*) AS Hits,
cs-username
INTO TopURIs.txt
FROM 'E:\IIS Log Files\ex090511*.log'
GROUP BY cs-username
ORDER BY Hits DESC

Thanks, Matt