October 7, 2008 at 2:31 pm
Hi folks,
I wonder who can supply an example of using logpaser in sql query, I just want to find out who(internal) visited my site. I want to join them with AD so I know user's real name.
Can I write the query as something like:
select cs-username, count(*) as hits from 'ex081002.log'
inner join AnotherTable1 on ...
where hits > 100
Thanks.
October 7, 2008 at 3:22 pm
I don't believe logparser supports joins.
One way to get the full name -- use dsquery with the output from logparser.
-- myscript.sql
SELECT DISTINCT cs-username
USING COUNT(*) as hits
FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex0809??.log
GROUP BY cs-username
HAVING hits > 100
-- Command line
logparser file:myscript.sql -i:IISW3C -rtp:-1 -headers:off -stats:off
--DSQUERY command
dsquery user -samid [username]
October 7, 2008 at 4:05 pm
Hi Thanks for your reply.
I don't understand the 'USING', it's just not working, also, QA reports error in "FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex080930.log "
October 7, 2008 at 4:14 pm
I might have misunderstood your question then.
Logparser queries aren't run inside Query Analyzer, they're run from a command prompt.
The text file contains the query that's used by logparser.
October 7, 2008 at 4:21 pm
My mistake, I didn't read your reply carefully.
What's dsquery? How do I use it? it says 'dsquery' is not recognized as an internal or external command.
October 7, 2008 at 4:23 pm
I do have a database in local containing data from AD, so I was wondering if I can use join, and if I can select directly from log file then I might be able to join them together to pull out user info.
And writing that kind of query would be easy for SQL programmer.
Correct me if I am wrong.
Thanks again for your reply.
October 7, 2008 at 4:29 pm
dsquery is one in a set of commands for working with AD. Should be installed if you have Windows XP as the client. Not sure if they're available as a seperate download. That's just one way I could think of to get the full name back and any other attribute you might want.
http://technet.microsoft.com/en-us/library/cc732952.aspx
Edit:
How about using the SQL output format from logparser to import the log data into a database. Assuming that your import from AD contains the SamID then you should be set.
October 7, 2008 at 7:47 pm
How about using the SQL output format from logparser to import the log data into a database. Assuming that your import from AD contains the SamID then you should be set.
That's a good idea, could you shed more light? Thanks again for your help.
October 8, 2008 at 6:31 am
Only two things would have to be modified on the above example to import the results into SQL. The logparser manual has a couple other examples as well, just look under References\Output Format\SQL.
1) Use an INTO clause in the SELECT statement.
2) Use an output format on the command line.
-- Command line
C:\logparser.exe file:myscript.sql -i:IISW3C -o:SQL -server:myServer -database:myDatabase
-driver:"SQL Server" -createTable:on
October 8, 2008 at 7:57 am
Thank you. Now I can actually create a task to automate the log import into sql, and in the sql, I can do whatever join I want.
October 8, 2008 at 9:56 am
Hold on, new problem::w00t:
When I use "select * INTO log from %1" to insert the logfile into sql table, at first there is no any problem, I got beautiful records. However, the log will get longer and longer, so I have to consider the performance issue, I want to add one identity field in the log table hoping this could improve select performance.
However, it's just not working. Here is my code:
logparser.exe "select * INTO log from %1" -i:iisw3c -o:SQL -server:%2 -database:PortalLog -driver:"SQL Server" -createTable:Off
table [log] was created at the first I run logparse, but I added ID into the table, which caused the code not working.
What's wrong with my code?
Thanks.
October 9, 2008 at 6:15 am
I'm not familiar with logparser, so I may be off base, but it seems like your error here at this point is trying to use SELECT INTO repeatedly. You can only use SELECT INTO to initially create your table. Once the table exists, you either need to drop it before running a SELECT INTO again, or switch to using this syntax:
INSERT INTO table(field1, field2, field3)
SELECT field1, field2, field3)
FROM ....
If you are using an identity field, you would want to leave the identity field out of both these field lists, and let SQL auto generate the number for you.
October 9, 2008 at 8:38 pm
There are lots of fields in iislog, "Select * INTO log from iislog" will create those fields + some extra fields for you automatically.
The only problem is when you use "Select * INTO log from iislog", it took over the control of creating the table in its own way (original fields + extra fields). I tried to put "set identity insert on" ahead of the select query, but it was not recognized.
Is there any work around?
Thanks.
October 9, 2008 at 8:54 pm
If I add ID as a primary key field after the table has been automatically created by logparser, and then I tried:
logparser.exe "select c-ip, cs-uri-stem into log from ex070816.log" -i:iisw3c -o:sql -server:(local) -database northwind -driver:"sql server" -createtable:on
This will generate an error:
Task aborted.
SQL table column "ID" data type is not compatible with SELECT clause item "c-ip" (type STRING)
So, this means I can't add the ID field manually? But I would think there must be kind of primary key in the table, otherwise the performance will eventually become very poor once the log size gets increased after a year.
Maybe I should create the table by myself, instead of let the logparser do it? but logparser seems create some extra field for different data source, like for iisw3c, it will create LogFilename and LogRow. The following is the table it creates:
CREATE TABLE [dbo].[log] (
[LogFilename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogRow] [int] NULL ,
[date] [datetime] NULL ,
[time] [datetime] NULL ,
[cIp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csUsername] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sSitename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sComputername] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sIp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sPort] [int] NULL ,
[csMethod] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csUriStem] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csUriQuery] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[scStatus] [int] NULL ,
[scSubstatus] [int] NULL ,
[scWin32Status] [int] NULL ,
[scBytes] [int] NULL ,
[csBytes] [int] NULL ,
[timeTaken] [int] NULL ,
[csVersion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csHost] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csUserAgent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csCookie] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[csReferer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEvent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sProcessType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sUserTime] [real] NULL ,
[sKernelTime] [real] NULL ,
[sPageFaults] [int] NULL ,
[sTotalProcs] [int] NULL ,
[sActiveProcs] [int] NULL ,
[sStoppedProcs] [int] NULL
) ON [PRIMARY]
October 10, 2008 at 1:22 am
Create your table manually before running LogParser with the identity column in place (make sure the non-identity columns match the ones in your input file exactly in order, name and type), then run your LogParser query with the -ignoreIdCols switch.
Regards,
Jacob
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply