« Previous Next »

Thread: Querying for a date

Last post 11-15-2007 10:24 AM by vidhya2007. 6 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (7 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 05-30-2007, 10:47 AM

    Querying for a date

    Reply Contact

    I'm struggling with querying for all events on a certain date.  I've tried to use "between" on the timegenerated fields, but it's difficult.  I actually just need a count of all events on a single day.  Here's what I've got so far:  logparser -i:evt -o nat "select count(*) from system where timegenerated between 5/22/2007 and 5/24/2007"

    Any suggestions for a non-SQL speaker?

    Tags:

  • 05-31-2007, 7:43 PM In reply to

    RE: Querying for a date

    Mark as Answer
    Reply Contact

    Must Be:

    WHERE TimeGenerated BETWEEN TO_TIMESTAMP('2007-05-22 00:00:01', 'yyyy-MM-dd hh:mm:ss') AND TO_TIMESTAMP('2007-05-24 23:59:59', 'yyyy-MM-dd hh:mm:ss')

    Probe!
    Tags:

  • 06-14-2007, 6:48 AM In reply to

    RE: Querying for a date

    Mark as Answer
    Reply Contact

    I have tried that:

    LogParser.exe "select EventID, TimeGenerated, Message, ComputerName, RESOLVE_SID(SID) as Username from C:\Log_Files\2007_6_13\evt\SecEvent_20070613_07.Evt TO TEST.html WHERE TimeGenerated BETWEEN TO_TIMESTAMP('2007-06-08 00:00:01', 'yyyy-MM-dd hh:mm:ss') AND TO_TIMESTAMP('2007-06-08 23:59:59', 'yyyy-MM-dd hh:mm:ss')" -i:EVT -o:TPL -tpl:LogParserTemplate.txt

    and keep getting this error :

    Syntax error: <tern2>: 'BETWEEN' is not a vailid operator

     

    Any ideas?

    Thanks

     
    Tags:

  • 06-14-2007, 6:24 PM In reply to

    RE: Querying for a date

    Mark as Answer
    Reply Contact

    I probe (copy & paste) your post and works fine with Logparser v2.2.10 , which version have you?

     
    Tags:

  • 06-15-2007, 12:47 PM In reply to

    RE: Querying for a date

    Mark as Answer
    Reply Contact

    I switched to the new version and its working great...Thanks!
    Tags:

  • 06-18-2007, 7:14 AM In reply to

    RE: Querying for a date

    Mark as Answer
    Reply Contact

    Ok...say my normal operating hours are 9am-5pm.

    How would I write a query to look for logons outside of that time period?

    I know how to look within that timeframe, but cant figure out how to look outside of that timeframe.

    Thanks
    Tags:

  • 11-15-2007, 10:24 AM In reply to

    Re: RE: Querying for a date

    Mark as Answer
    Reply Contact

    Hi,

    I'm working on the same date query.I have the following query. It doesn't return any records. Can you tell me what i'm doing wrong?

    logparser -o:DATAGRID "select date,time,cs-uri-stem,sc-status,count(*) from 'D:\\logfiles\\W3SVC1\\Investments.log' WHERE TO_TIME(time) BETWEEN TIMESTAMP('11/01 00:00:00', 'MM/dd hh:mm:ss') AND TIMESTAMP('11/07 00:00:00', 'MM/dd hh:mm:ss') group by cs-uri-stem, date,time,sc-status order by count(*) DESC"

     or the following query

    logparser -o:DATAGRID "select date,time,cs-uri-stem,sc-status,count(*) from 'D:\\logfiles\\W3SVC1\\Investments.log' WHERE date BETWEEN TO_TIMESTAMP('2007-11-05','yyyy-mm-dd') AND TO_TIMESTAMP('2007-11-06', 'yyyy-mm-dd') group by cs-uri-stem, date,time,sc-status order by count(*) DESC"

    My log has the following date fields as follows:

    2007-11-05 05:00:02 W3SVC1 S3W04113 10.101.158.31 HEAD /HealthCheck.asp - 80 - 10.101.158.3 Mozilla/4.06+[en]+(WinNT;+I) - 10.101.158.31 200 0 254 31

    2007-11-06 03:28:47 W3SVC1 S3W04113 10.101.158.31 GET /MyAccount/aspxPage/Holdings.aspx language=ENGLISH&system=NETA&SGID=9e7f318e82375c35f5d536c9381c0015372f&IB=1&xtid=RBC:f43e7e1bdf424fe3aabb691f8bc5537d&xbrand=AD&REMOTE_ADDR=216.106.103.70&REMOTE_HOST=216.106.103.70&LOGICALSESSID=cHTWRgDIwt4vRwwfAACgFgAAksARAA__&DISTINGUISHID=4519036551795131 80 - 10.236.41.175 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) - 200 0 2275 281

    Thanks,

    (I'm using 2.2 version) 

     

     
Page 1 of 1 (7 items)
Microsoft Communities