« Previous Next »

Answered Thread: Quantize on CSV input files - Not working

Last post 01-13-2008 11:18 PM by qbernard. 8 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (9 items)

Sort Posts:

  • 12-20-2007, 9:45 AM

    Quantize on CSV input files - Not working

    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

  • 12-24-2007, 8:19 AM In reply to

    Re: Quantize on CSV input files - Not working

    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

    Re: Quantize on CSV input files - Not working

     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

    Re: Quantize on CSV input files - Not working

    Appreciate if someone can pls help me with this.
     

    Thanks in advance

  • 01-07-2008, 11:34 PM In reply to

    Re: Quantize on CSV input files - Not working

    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

    Re: Quantize on CSV input files - Not working

    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

    Re: Quantize on CSV input files - Not working

    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

    Re: Quantize on CSV input files - Not working

    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

    Re: Quantize on CSV input files - Not working

    LOL.. Awesome!

    Cheers,
    Bernard Cheah
Page 1 of 1 (9 items)
Microsoft Communities