empty log file problem

Question

User531388329 posted

I am running a query against all log files in a directory. When one of the log files is empty (has headers and field lables but no data rows) I am getting a type error in my sum() function.

"argument of sum/avg is not int or real"

Is there an easy way to bypass this?

Dave

 

Answer 1

User531388329 posted

I'll try wrapping the field this way and see if that works

sum(REPLACE_IF_NULL( sc-bytes,0)) as bytesSent

 

Answer 2

User531388329 posted

Dave, what input format are you using?

If you're using "-i:IISW3C", then you should not hit this problem...

------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm"

Answer 3

User531388329 posted

Im using

Dim oInput As MSUtil.COMW3CInputContextClass

W3C seems to be the one that works for windows media server 9 log files.

Dave

Answer 4

User531388329 posted

I think I have it:

sum(REPLACE_IF_NULL(to_int(sc-bytes),0)) as bytesSent,
max(REPLACE_IF_NULL(to_int(s-totalclients),0)) as maxConnects,
max(REPLACE_IF_NULL(to_int(x-duration),0)) as maxDuration,
avg(REPLACE_IF_NULL(to_int(x-duration),0)) as avgDuration

My only concern now is that the "Logging Model for Windows Media Services 9 Series" Word doc says that if duration information is not sent by the client it places a - in the log. In that case I guess I'd have to figure out hte order of precendence :

max(REPLACE_IF_NULL(to_int(replace_str(x-duration,'-','0'),0)) as maxDuration,

Well actually I guess in this case there would be no nulls so I guess this would work:

max(to_int(replace_str(x-duration,'-','0'))) as maxDuration,

 

 

 

 

 

 

 

Answer 5

User531388329 posted

Arrggg@! Unfortunetly it did not work:  My max and avg functions were returning weird values way out of the range they should have been and all the same number.

If I remove the REPLACE_IF_NULL(to_int()) then I'll hang on empty logs again.

I think I'll try setting dtlines to 0 and then cast everything myself, perhaps that is what is halting on empty logs.

I am also getting double inserts on some queries and I can't understand why. When I run the query from the exe it seems to work ok, then when I run it from the DLL I get the double inserts.

Another weird thing is that when I run my query from the EXE it tells me 65 output elements processed, but I only end up wth 50 rows in sql...?