3

The host is located in the EDT time zone. Event logs are logged using EDT. IIS logs are getting logged using UTC, and I'm not sure which logparser construct helps account for UTC.

For example, Windows event logs, logged in local time, entries logged in last 20 minutes can be retrieved with:

>logparser "SELECT * FROM Application WHERE TimeGenerated >= TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP( '20', 'mm' ) ) )"

How do I retrieve IISW3C formatted log entries logged in the previous 3 minutes?

After further review, I found this example in the help entry for SYSTEM_TIME ( ), i.e. Retrieve the IIS log entries logged in the current hour: 

logparser -i:IISW3C "SELECT * FROM <1> WHERE date = SYSTEM_DATE() AND time >= QUANTIZE( SYSTEM_TIME(), 3600 )" -o:NAT

This current attempt retrieves the last 180 seconds (I think?), still trying to grok it...;-)

logparser -i:IISW3C "SELECT Time, Date, cs-uri-stem FROM <1> WHERE date = SYSTEM_DATE() AND time >= QUANTIZE( SYSTEM_TIME(), 180 )" -RTP:-1

Other suggestions or approaches you can recommend?

2 Answers2

3

Ok, i realize i'm replying to a post that is more than a year old, but I know that some googler out there will find this information useful. Here is how I query IIS entries with the last X minutes (or hours, or seconds).

"select TO_LOCALTIME(time), c-ip, cs-method, sc-status, cs-uri-stem from <1> where TIME > SUB(SYSTEM_TIME(), TO_TIMESTAMP('00:05:00','hh:mm:ss'))"

This can be expanded to be the last day, month, year (assuming you are not using the -mindatemod option to logparser) by changing SYSTEM_TIME() to SYSTEM_TIMESTAMP() and editing the format specified in TO_TIMESTAMP().

It's also helpful to remember the logparser time format: 'yyyy-MM-dd hh:mm:ss'

-MBB

MrBlargityBlarg
  • 51
  • 1
  • 4
2

Try the TO_LOCALTIME function.

Example:

TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [LocalTime]

You should then be able to grab everything after 3 minutes ago, which for that I'd use SYSTEM_TIMESTAMP() within your QUANTIZE.

Example:

logparser "select top 10 TO_LOCALTIME(SYSTEM_TIMESTAMP()), SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:03', 'yyyy-MM-dd HH:mm')) from \\192.168.1.104\wwwroot\ex*.log"

That returns the current system time and 3 minutes ago. So roughly your WHERE clause would be LocalTime > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:03', 'yyyy-MM-dd HH:mm'))

logparser "select top 10 TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [LocalTime], * FROM \\192.168.1.104\wwwroot\ex*.log WHERE LocalTime > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:03', 'yyyy-MM-dd HH:mm'))"

Remember, date and time will be in UTC, so make sure you convert every instance to local time before comparing against them.

Of course, you could just work within the time zone of the log files, which would be removing TO_LOCALTIME from your final WHERE clause.

James Skemp
  • 862
  • 2
  • 10
  • 21