locked
Standard Deviation in LogParser (a gift ;-) RRS feed

  • Question

  • User531388329 posted

    Hi there!

    I really like LogParser and have done a lot of usefull stuff with it in the last couple of months.

    Since I got a lot of info and samples from around the net I decided to give something back.

    One thing really missing in LogParser is a function for the Standard Deviation

    Alas, this formula uses an aggregation function (AVG) inside an aggregation function (SUM) which
    produces an error in Log Parser; therefor we have to use an alternative formula:

    alternative formula

    Here's an example with an IIS logfile which gives you the Average response time for every request and the corresponding standard deviation:

    SELECT cs-uri-stem AS URL, COUNT(*) AS Hits,
    DIV ( MUL(1.0, SUM(time-taken)), Hits ) As RealAvgTime,
    SQRROOT ( SUB ( DIV ( MUL(1.0, SUM(SQR(time-taken)) ), Hits ) , SQR(RealAvgTime) ) ) AS SD
    FROM %source%
    GROUP BY URL

    We have to calculate our own average, because otherwise the rounding error would have a huge effect on the result; that's also the reason for the "weird" multiplications with 1.0

    hope you like it!
       swobi

    P.S.: If you (like me at first) are skeptical about the second formula, I can post the "proof" ;-)

    Above formulas are taken from the wikipedia-article about Standard Deviation

    Tuesday, August 21, 2007 2:21 AM

All replies

  • User-802346331 posted
    Just wanted to say thanks for this. I used it in a script about a year or more ago and could never remember where I initially found it to give proper credit. compared to MSSQL STDev function and it was right on. Great little query!
    Wednesday, April 22, 2009 1:22 PM
  • User-1342976348 posted

    Hi,

    Anyone knows how to calculate 95th % (percentile) point from the above SD calculations?

    Thx

    varadhg

     

    Monday, February 4, 2013 1:22 PM