« Previous Next »

• 12-20-2007, 9:45 AM

  kewlapps Joined on 12-20-2007, 2:40 PM Posts 19

  Quantize on CSV input files - Not working Reply Contact Hi , I have log files archieved in csv format. So with CSV input file I am trying to get a hit count and max Rt for every hour with the below query. logparser "SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) as zHour, count(*) as hits, Max(time-taken) AS MaxRT FROM ex071105u.csv GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ASC" -i:csv  but all i get is zHour  Hits   MaxRT ------ ------ ------ <NULL> 233837 192765 Please help. Thanks in adv

  Tags: Quantize CSV Input

• 12-24-2007, 8:19 AM

  In reply to

  Zhao Ji Ma - MSFT Joined on 01-05-2007, 5:36 AM Posts 765

  Re: Quantize on CSV input files - Not working Reply Contact Hi, Can you provide some records which contains date, time columns? It seems TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600))  returns null. And you need to group the output records. Zhao Ji Ma Sincerely, Microsoft Online Community Support “Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. ”

• 12-27-2007, 9:39 AM

  In reply to

  kewlapps Joined on 12-20-2007, 2:40 PM Posts 19

  Re: Quantize on CSV input files - Not working Reply Contact  Hi, Attached is some sample data date,time,cs-method,cs-uri-stem,sc-status,sc-bytes,cs-bytes,time-taken 25/12/2007,00:00:40,GET,/process_content.aspx,200,56183,1072,31 25/12/2007,00:00:40,GET,/ajax/get_process.aspx,200,27972,1002,15 25/12/2007,00:00:44,GET,/ajax/get_process.aspx,200,25135,1009,1484 25/12/2007,00:00:51,GET,/ajax/get_process.aspx,200,27972,1009,15 25/12/2007,00:01:01,GET,/ajax/get_process.aspx,200,43719,1002,31 25/12/2007,00:01:04,GET,/ajax/get_process.aspx,200,30227,1009,15 25/12/2007,00:01:05,GET,/search.aspx,200,5295,966,406 Thanks in advance for your support

• 01-02-2008, 4:41 AM

  In reply to

  kewlapps Joined on 12-20-2007, 2:40 PM Posts 19	Re: Quantize on CSV input files - Not working Reply Contact Appreciate if someone can pls help me with this.   Thanks in advance

• 01-07-2008, 11:34 PM

  In reply to

  qbernard Joined on 03-26-2003, 2:12 AM Malaysia Posts 3,238

  Re: Quantize on CSV input files - Not working Reply Contact Err. didn't test your query. but in the past I have quantize to hourly output without any issue.  ---Ch02HourlyBandwidth.sql--- SELECT     QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour,     DIV(Sum(cs-bytes),1024) AS Incoming(K),     DIV(Sum(sc-bytes),1024) AS Outgoing(K) INTO %chartname% FROM %source% GROUP BY Hour ---Ch02HourlyBandwidth.sql--- Cheers, Bernard Cheah

• 01-11-2008, 7:41 AM

  In reply to

  kewlapps Joined on 12-20-2007, 2:40 PM Posts 19

  Re: Quantize on CSV input files - Not working Reply Contact Thanks Bernard for the reply. True that Quantize works fine for IISW3C input format, i tried inteh past and it workd very well. But I have another set of web logs that are in csv format and am having trouble in using Quantize against them. When I tried using Visual Log Parser for the above sample data, it gave an error saying "String was not recognized as a valid DateTime." So I guess that it boils down to using TO_TIMESTAMP function on "date" and "time" columns together/combined. Any suggestions on how this can be done would be of great help. Thanks.

• 01-12-2008, 12:47 AM

  In reply to

  qbernard Joined on 03-26-2003, 2:12 AM Malaysia Posts 3,238	Re: Quantize on CSV input files - Not working Reply Contact Arggh I see, will need sometime to dig :) I have to go in a moment, but can you look at -iTsFormat parameters for CSV format. Take a look at the log parser help file input format, it has a sample that quantize csv log in minute basic. Cheers, Bernard Cheah

• 01-13-2008, 11:58 AM

  In reply to

  kewlapps Joined on 12-20-2007, 2:40 PM Posts 19	Re: Quantize on CSV input files - Not working Reply Contact Never mind got it. Had to concat the Date and Time fields and cast it to timestamp for quantize to understand the format. quantize(to_timestamp(strcat(to_string(Date),strcat(' ',to_string(time))),'dd/MM/yyyy hh:mm:ss'),3600) as zHour  Anyway ...thx for the support. Cheers!!!

• 01-13-2008, 11:18 PM

  In reply to

  qbernard Joined on 03-26-2003, 2:12 AM Malaysia Posts 3,238	Re: Quantize on CSV input files - Not working Reply Contact LOL.. Awesome! Cheers, Bernard Cheah