Previous Next

Thread: IIS log queries

Last post 07-22-2008 5:20 AM by Rovastar. 7 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (8 items)

Sort Posts:

  • 07-20-2008, 10:48 PM

    • mikie
    • Not Ranked
    • Joined on 07-15-2008, 1:43 AM
    • Posts 6

    IIS log queries

    Hi Guys,

     Im failry new to log parser, i am gradually getting the hang of it, however would like to know if anyone can help me with a query i am trying to run. I want to search through the latest IIS log on a 2003 server and pull out all posts with response times over 600. Any info apreciated

     

    Cheers

  • 07-21-2008, 12:03 AM In reply to

    Re: IIS log queries

    Here is something to get you started...

    SELECT top 50 cs-uri-stem,count(cs-uri-stem) As Count,max(time-taken) as Max,min(time-taken) as Min,avg(time-taken) as Avg from c:\c.log GROUP BY cs-uri-stem ORDER BY avg(time-taken) DESC 

    This will give you the top 50 pages with the max and min time taken and the avg time taken. You can play with this and make it work for your needs. 

  • 07-21-2008, 12:46 AM In reply to

    • mikie
    • Not Ranked
    • Joined on 07-15-2008, 1:43 AM
    • Posts 6

    Re: IIS log queries

    Thanks for your help Joel

  • 07-21-2008, 4:02 AM In reply to

    • yellowdog.dave
    • Top 50 Contributor
    • Joined on 07-18-2008, 7:17 AM
    • Johannesburg, South Africa
    • Posts 83

    Re: IIS log queries

    Hi Mikie, 

    I have written something that you could possibly use, it is a combination of configuration and batch files and it embeds logparser commands that monitor our server farm at work for min, max and average responses that exceed a parameterised value.

    You may be able to use these to achieve what you want - I use the scheduler to run this on an interval and then if there is any response over the threshold, send out Emails and SMS messages.

    The batch file accepts the name of a configuration file for each of the servers it must monitor which has the "well known"  server name and the share where the logs can be found.I have inserted one sample, as we run multiple different clusters and they have different criteria for response times. You can create a directory on your machine and copy and paste the following into the appropriate files in that directory.

    The config file looks something like this and can be called whatever you like as it is input to the batch file as a parameter;

    iis-server1 \\iis-server1\IISLogs\W3SVC1\
    iis-server2 \\iis-server2\IISLogs\W3SVC1\
    iis-server3 \\iis-server3\IISLogs\W3SVC1\
    iis-server4 \\iis-server4\IBSLogs\W3SVC1\

    The batch file looks something like this, mine is called performance.bat and it accepts 2 parameters the first being response time in milliseconds, the second, the name of your config file. You may need to play with your date and time variables in the batch file (ourdate and exdate), as this is dependent on your regional settings and your log file names; 

    @echo off
    if %1.==. (
        echo Missing parameter for response time threshold in milliseconds
            echo Missing parameter for server config file
        exit 1
        )
    if %2.==. (
        echo Missing parameter for server config file
        exit 1
        )
    %~d0
    cd %~p0
    if not exist %2 echo No server config file called %2 found in the performance directory. & goto :EOF
    set ourdate=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%
    set exdate=%ourdate:~2,6%
    echo ########################################################################
    echo Processing the IIS logs for %ourdate% on %DATE% at %TIME%
    echo ########################################################################
    if not exist %ourdate% mkdir %ourdate%
    cd %ourdate%
    if exist perf_%2 del perf_%2
    for /F "tokens=1,2" %%i in (..\%2) do logparser "SELECT '%%i' as Server, max(time-taken) AS MaxTime, min(time-taken) AS MinTime, avg(time-taken) AS AvgTime, count(*) as Transactions from %%jex%ourdate:~2,6%.log" -i:iisw3c -iCheckPoint:%%i.lpc |findstr iis >> perf_%2
    for /F "tokens=1,2,3,4,5" %%i in (perf_%2) do echo %TIME% %%i max %%j min %%k average %%l transactions %%m
    for /F "tokens=1,2,3,4,5" %%i in (perf_%2) do if %%l GTR %1 echo Average transaction time is %%l miliseconds which is greater than the setting of %1 & exit %%l
     

    When you run the job, it will create a sub diretory with the name of the date in YYYYMMDD format, if it doesn't already exist, in here it will put it's output files, which should look something like this;

    iis-server1 5531    0       250     148
    iis-server2 15062   0       297     125
    iis-server3 1687    0       160     138
    iis-server4 2265    0       229     154

    As I do not know your technical level, if you don't understand any of this, let me know and I will elaborate and put in more detail for you.

    If you like this, I have written a complimentary set that runs at end of each business day and produces various reports and charts for the entire day.

    Trust this helps, good luck and enjoy.

    Cheers, Dave  

     

    Yes, dear
  • 07-21-2008, 6:14 AM In reply to

    • Rovastar
    • Top 10 Contributor
    • Joined on 03-13-2008, 2:00 PM
    • London, UK
    • Posts 758

    Re: IIS log queries

    I think you guys are making it too complicated

    SELECT
    *
    FROM
    *.log
    WHERE
    time-taken > ''600'

    then you can add

    ORDER BY date, time 

    (for ordering by order by time and date lowest first)

    or 

    ORDER BY time-taken DESC

    (for ordering by order by time-taken highest first)

    Most overused word in IT is 'should' as in 'That should work!?!'
  • 07-21-2008, 6:47 AM In reply to

    Re: IIS log queries

    Sure, you can do that and it would simply give you what you need. We are just giving complex solutions and you finding an easier one is great. The only thing to note is that your query will give a lot of data (select *). You also have no filter on your files (perhaps that is a requirement) and you will get jpg, css, and jscript files. Also, you will get every request even if duplicated so you may want to group data.

    However,  if this query works for you...run with it!

  • 07-21-2008, 8:33 PM In reply to

    • mikie
    • Not Ranked
    • Joined on 07-15-2008, 1:43 AM
    • Posts 6

    Re: IIS log queries

    Hi Dave,

    Thanks for your effort in explaining this to me, i have just started to play around more in depth with IIS web pages, and what you are saying will definitley help, but i will need your help in setting this up, please elaborate as much as you can on how i would set this up, day by day i am learning new ways in making my job better by tips from this forum, and as a windows administrator i appreciate all your help and enthusiasm.

    Regards

    Mikie

  • 07-22-2008, 5:20 AM In reply to

    • Rovastar
    • Top 10 Contributor
    • Joined on 03-13-2008, 2:00 PM
    • London, UK
    • Posts 758

    Re: IIS log queries

    joelangley:

    Sure, you can do that and it would simply give you what you need. We are just giving complex solutions and you finding an easier one is great. The only thing to note is that your query will give a lot of data (select *). You also have no filter on your files (perhaps that is a requirement) and you will get jpg, css, and jscript files. Also, you will get every request even if duplicated so you may want to group data.

    However,  if this query works for you...run with it!

     

    Yeah I was just generating a simple query that did the job. I find this easier for people when they start learmning about log parser and queries.

    Also the this one I presumed that they would be more interested in the longest running (instance of a) page not an average that 'could' be misleading. e.g. sometimes a page take a long time to run then you want to identify the time of day this was to see other activity collate with firewall logs, etc, etc, etc,

    But yes the average is useful too for an overall indication of health of the site.
    Most overused word in IT is 'should' as in 'That should work!?!'
Page 1 of 1 (8 items)
Page view counter