Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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..?

share|improve this answer
add comment

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

share|improve this answer
    
this is logparser not SQL -- updated the post with a link to explain –  Jeff Atwood Jul 25 '09 at 13:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.