Thread: How to use logparser sql query?

Last post 07-21-2008 9:08 AM by Rovastar. 3 replies.

Average Rating Rate It (5)Thank you for the rating!

RSS

Page 1 of 1 (4 items)

Sort Posts:

07-21-2008, 5:28 AM

hmfive
Joined on 07-21-2008, 8:48 AM
Posts 1

How to use logparser sql query?

Reply Contact

I have a IIS log.

2008-04-21 20:00:00 218.54.63.138 - W3SVC1 xx.xx.xx.xx 80 GET /2.asp - 304 0 259 1631 0 HTTP/1.1

2008-04-21 20:00:00 221.154.37.72 - W3SVC1 xx.xx.xx.xx 80 GET /go/index.asp baduk 302 0 0 601 0 HTTP/1.1

2008-04-21 20:00:00 121.165.111.35 - W3SVC1 xx.xx.xx.xx 80 GET /2.js - 304 0 258 605 0 HTTP/1.1

2008-04-21 20:00:00 218.54.63.138 - W3SVC1 xx.xx.xx.xx 80 GET /1.asp - 200 0 0 1696 0 HTTP/1.1

I want users request interval data.

result

User IP, Request interval (avg)

111.222.111.111, 5sec

222.222.111.111, 10.3sec

222.111.222.111, 30.sec

...

Request interval is...

requestinterval

http://blogfiles16.naver.net/data33/2008/7/21/79/requestinterval_hmfive.gif

click click

222.111.111.111 |-----------------------------------------------------------------|

07.07 01:00 07.07 01:15

22.111.111.111 is request interval 15sec.

How to use logparser sql query?

thanks.

07-21-2008, 5:45 AM In reply to

Rovastar
Joined on 03-13-2008, 2:00 PM
London, UK
Posts 496

Re: How to use logparser sql query?

Reply Contact

I do a far bit of SQL and that is going to be fairly complicated to do, I think. You are going to have to store the different IPs in a temp table to get all the times and then workout the different between them and then get an average. That will make a massive, messy query.

Maybe someone here knows an easier way. I'm all ears.

But I suggest you speak to some SQL expert at your company or try dedicated SQL forums.

Most overused word in IT is 'should' as in 'That should work!?!'

07-21-2008, 6:48 AM In reply to

joelangley
Joined on 07-20-2008, 6:37 PM
Posts 80

Re: How to use logparser sql query?

Reply Contact

hmfive, have you attempted at all to solve the query? Can we see what you have done so far?

07-21-2008, 9:08 AM In reply to

Rovastar
Joined on 03-13-2008, 2:00 PM
London, UK
Posts 496

Re: How to use logparser sql query?

Reply Contact

I have not tried tbh it will take me too long. I just wanted you to know it was difficult although it sounded reasonably straight forward. Also I am more used to having a the IIS logs in a full SQL database where I have more flexability for queries. (I don't know/think that logparser can create temp tables, run stored procs, etc)

Also I am not sure how useful it will be if you have an IP adress that view pages with lots of elements. A page has 100 jpgs on for example then all/most will be in the same second as each other and that will mess up you stats. x IP has than an average of 0.01seconds, etc.

You could limit it to say html pages, etc that will help.

Most overused word in IT is 'should' as in 'That should work!?!'

Page 1 of 1 (4 items)