« Previous Next »

• 09-11-2006, 9:13 AM

  Need to group fields... Reply Contact Consider this two query: 1) Total Visits Per Hour: Logparser.exe "SELECT QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour, COUNT(*) AS Total INTO tph.csv FROM ex*.log WHERE EXTRACT_EXTENSION(cs-uri-stem) IN ('asp'; 'php'; 'jsp'; 'html'; 'htm'; 'xml'; 'pl') GROUP BY Hour ORDER BY Hour" -o:CSV -headersFF 2) Unique Visits Per Hour: logparser.exe "SELECT QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour, c-ip, cs(User-Agent), cs-uri-stem FROM ex060821.log GROUP BY Hour, c-ip, cs(User-Agent), cs-uri-stem" -q -o:CSV | Logparser.exe -i:CSV "SELECT Hour, COUNT(c-ip, cs(User-Agent), cs-uri-stem) AS Unique INTO uph.csv FROM STDIN WHERE EXTRACT_EXTENSION(cs-uri-stem) IN ('asp'; 'php'; 'jsp'; 'htm'; 'html'; 'pl'; 'xml') GROUP BY Hour" -o:CSV -headersFF I will have the following result: 1) 2006-08-21 03:00:00,2 2006-08-21 06:00:00,13 2006-08-21 07:00:00,50 2006-08-21 08:00:00,60 ... 2) 2006-08-21 03:00:00,2 2006-08-21 06:00:00,8 2006-08-21 07:00:00,6 2006-08-21 08:00:00,7 ... How can I Group fields to have something like this?: Hour Total Unique ------------------- ----- ------ 2006-08-21 03:00:00 2 2 2006-08-21 06:00:00 13 8 2006-08-21 07:00:00 50 6 2006-08-21 08:00:00 60 7

  Tags: IISW3C input format

• 09-11-2006, 10:00 AM

  In reply to

  	RE: Need to group fields... Mark as Not AnswerMark as Answer... Reply Contact I'm not positive but I think it can be done. Would it be possible for you to give me a sample log file that I could run your queries on? Ideally, one that would generate the output you listed above would be great.
  	Tags: IISW3C input format

• 09-11-2006, 10:47 AM

  In reply to

  	RE: Need to group fields... Mark as Not AnswerMark as Answer... Reply Contact You can download it at: http://www.megaupload.com/es/?d=YHW4JNGP Thank You.
  	Tags: IISW3C input format

• 09-11-2006, 2:45 PM

  In reply to

  RE: Need to group fields... Mark as Not AnswerMark as Answer... Reply Contact Here's the way I'd get the data: === first.sql === SELECT Hour , UniqueRequestID , COUNT(*) AS NumRequests USING QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour , HASHSEQ(STRCAT(c-ip, STRCAT([cs(User-Agent)], cs-uri-stem))) AS UniqueRequestID FROM ex060821.log WHERE EXTRACT_EXTENSION(cs-uri-stem) IN ('asp'; 'php'; 'jsp'; 'html'; 'htm'; 'xml'; 'pl') GROUP BY Hour, UniqueRequestID ORDER BY Hour === second.sql === SELECT Hour , COUNT(NumRequests) AS UniqueRequests , SUM(NumRequests) AS TotalRequests INTO DATAGRID FROM STDIN GROUP BY Hour === command line === lp file:first.sql -o:CSV -q | lp -i:CSV file:second.sql

  Tags: IISW3C input format

• 09-12-2006, 2:00 AM

  In reply to

  	RE: Need to group fields... Mark as Not AnswerMark as Answer... Reply Contact It work perfectly. Thank you very much for this help, Chief Master... ;-) I will not forget you in my stats program credits.
  	Tags: IISW3C input format

• 06-16-2007, 1:32 AM

  In reply to

  	RE: Need to group fields... Mark as Not AnswerMark as Answer... Reply Contact hi, can i do this using the LP COM API let's say, in VB.NET? thanks
  	Tags: IISW3C input format

• 06-18-2007, 6:30 AM

  In reply to

  	RE: Need to group fields... Mark as Not AnswerMark as Answer... Reply Contact Write out the outputs of the first query to a text file using executeBatch then run the second query against that one.
  	Tags: IISW3C input format