Log Parser 2.2 skipping today's IIS logs

Question

I'm trying to count the number of hits for a particular URL on our web site by parsing our IIS logs using Log Parser 2.2. Everything seems to be working fine, except that its handling of timestamps is greatly confusing me.

The IIS logs have all of the timestamps expressed in UTC time. Therefore, in my application, I convert the server's time to UTC before plugging it into the query. However, when I try to query for the current day's data, I get back a zero count, despite me seeing the records in the log file. The generated query I try to run to get everything within the current day looks something like this (the query is run on 11/11/2009, and I'm using Arizona time):

SELECT COUNT(*) 
FROM \\Server\IIS Logs\LogFiles\W3SVC1\u_ex*.log
WHERE 
    cs-method = 'GET' 
    AND cs(Referer) NOT LIKE '%ntorus%'
    AND c-ip NOT LIKE '192%'
    AND c-ip NOT LIKE '127%'
    AND (
        cs-uri-stem = '/' 
        OR cs-uri-stem = '/myurl')
    AND sc-status BETWEEN 200 AND 299 
    AND date BETWEEN 
        TIMESTAMP('2009-11-11 07:00', 'yyyy-MM-dd hh:mm') 
        AND TIMESTAMP('2009-11-12 07:00', 'yyyy-MM-dd hh:mm')

It looks like for some reason the current day's data is getting skipped. When querying earlier dates, I get back data just fine. Why is this happening?

Answer 1

It turns out that Log Parser doesn't do time stamp comparisons properly. However, when I converted the time stamps to strings, string comparison worked fine. Here's what the modified query looks like:

SELECT COUNT(*) 
FROM \\Server\IIS Logs\LogFiles\W3SVC1\u_ex*.log
WHERE 
    cs-method = 'GET' 
    AND cs(Referer) NOT LIKE '%ntorus%'
    AND c-ip NOT LIKE '192%'
    AND c-ip NOT LIKE '127%'
    AND (
        cs-uri-stem = '/' 
        OR cs-uri-stem = '/myurl')
    AND sc-status BETWEEN 200 AND 299 
    AND TO_STRING(TO_TIMESTAMP(date, time), 'yyyy-MM-dd hh:mm') 
        BETWEEN '2009-11-11 07:00' AND '2009-11-12 07:00'

Answer 2

Jacob, Thanks for your post. I was also having trouble comparing date/times in IIS logs. By combining your question and solution I was able to search without the TO_STRING.

    TO_TIMESTAMP(date, time) 
        BETWEEN TIMESTAMP('2009-11-11 07:00', 'yyyy-MM-dd hh:mm')  
            AND TIMESTAMP('2009-11-12 07:00', 'yyyy-MM-dd hh:mm')  

Full Source:

SELECT COUNT(*)  
FROM \\Server\IIS Logs\LogFiles\W3SVC1\u_ex*.log 
WHERE  
    cs-method = 'GET'  
    AND cs(Referer) NOT LIKE '%ntorus%' 
    AND c-ip NOT LIKE '192%' 
    AND c-ip NOT LIKE '127%' 
    AND ( 
        cs-uri-stem = '/'  
        OR cs-uri-stem = '/myurl') 
    AND sc-status BETWEEN 200 AND 299  
    AND TO_TIMESTAMP(date, time) 
        BETWEEN TIMESTAMP('2009-11-11 07:00', 'yyyy-MM-dd hh:mm')  
            AND TIMESTAMP('2009-11-12 07:00', 'yyyy-MM-dd hh:mm')