Standard Deviation in LogParser (a gift ;-)
Last post Feb 04, 2013 01:22 PM by Varadhg
Jan 14, 2007 12:37 PM|Anonymous|LINK
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:
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
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!
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
Apr 22, 2009 01:22 PM|deathwagon|LINK
Feb 04, 2013 01:22 PM|Varadhg|LINK
Anyone knows how to calculate 95th % (percentile) point from the above SD calculations?