# Standard Deviation in LogParser (a gift ;-) • ### 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: 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