LogParser and Quantize: Add 0 for empty values

Question

I use LogParser on my IIS Log to get a graph of accesses per hour:

SELECT date,QUANTIZE(time,3600),COUNT(*) FROM ex*.log WHERE cs-uri-stem
LIKE '%SomePage.aspx' GROUP BY date,QUANTIZE(time,3600)

The problem: If there is no access to SomePage.aspx during an hour, that row is simply missing from the output. I just wonder, can I tell LogParser to insert a row for each hour, even if COUNT(*) is 0?

Basically I wound need something that tells logparser to use MIN(date) and MAX(date) as "boundaries" and "expect" one line per hour. The display is currently just into a text file and then into Excel to create a graph (hits per hour on SomePage.aspx) so to get a proper linear graph, I need an entry for each hour, even it it's 0, and if I can get it directly from LogParser that would be great.

Answer 1

You seem to be very close to a request that I have on a frequent basis from various managers here. "Can you tell me what parts of the [insert name here] website are not visited?"

The answer is simply anything that isnt reported as visited. I have gone around the explanation that its not possible to report on anything that isnt in the log so many times!

Reporting on nil hits is essentially a report of your whole website (100's or 1000's of pages??) with the visited ones excluded. It becomes less practical as the size of the site increases.

What are you intentions for the data - your query just lists the data to screen - do you put this into any sort of file/archive/database for further processing eg csv file for Excel to produce graphs, SQL DB for activity reports..?

Answer 2

You've got to outer join it with a table that lists the hours that you're interested in.