Previous Next

• 08-26-2008, 4:36 PM

  alkhimey Joined on 10-16-2007, 4:56 PM Posts 19

  Formatting results Reply Contact Hi, 1. Is it possible to convert time of W3C logs to my local time in the sql statement itself? Is there a function for this? 2. Is it possible to format the output in certian ways? For example I want to get a list of all url requested to my iis server, the url should be formatted like this:: %host%/%cs-uri-stem%/?%cs-uri-query%  3. How to I export the above to a simple text file?  4. Where can I get documentation?   Thank you.

• 08-27-2008, 5:53 AM

  In reply to

  yellowdog.dave Joined on 07-18-2008, 7:17 AM Johannesburg, South Africa Posts 83

  Re: Formatting results Reply Contact Hi,  1. Use the TO_LOCALTIME function 2. To create a full URL do this       STRCAT( cs-uri-stem, REPLACE_IF_NOT_NULL( cs-uri-query, STRCAT( '?', cs-uri-query ) ) )    If you additionally want your host in front of that;      STRCAT(s-computername,STRCAT( cs-uri-stem, REPLACE_IF_NOT_NULL( cs-uri-query, STRCAT( '?', cs-uri-query ) ) )) 3. I don't know why you would want to do that, but I would use the NAT output format redirected to a text file. 4. RTFM - the logparser.chm help file and examples in the logparser install directory.  Good luck.  Cheers, Dave    Yes, dear

• 08-27-2008, 7:44 AM

  In reply to

  alkhimey Joined on 10-16-2007, 4:56 PM Posts 19

  Re: Formatting results Reply Contact Thank you, Sorry didn't notice the help file.  What I am trying to do is to get a number of last requests and put the in a file seperated by line breaks. I have two problems left: 1. I need to oreder the results by time, but I dont want to get time in the results (so "SELECT time, ... FROM .... ORDER BY time" is not good) 2. How can I get the name of the latest IIS log? (I have new log each day)

• 08-27-2008, 8:52 AM

  In reply to

  yellowdog.dave Joined on 07-18-2008, 7:17 AM Johannesburg, South Africa Posts 83

  Re: Formatting results Reply Contact Hi,  I don't understand why you wouldn't want the time if that is how you are ordering things, but anyhow.  1. If you actually read the mentioned help file you will see that what you want is not possible; ORDER BY Remarks: The Log Parser SQL-Like language requires that each field-expression appearing in the ORDER BY clause must also appear in the SELECT clause. 2. I use a DOS batch file to process my latest day's log file and run my logparser query against it - here is how I set it up in a variable (NB: The DATE variable is dependant on your locale, my DATE is in dd/mm/yyyy format) set ourlog=ex%DATE:~8,2%%DATE:~3,2%%DATE:~0,2%.log You then use the %ourlog% expanded literal in your queries.  There is no substitute for RTFM.  Good luck. Cheers, Dave    Yes, dear

• 08-27-2008, 9:51 AM

  In reply to

  alkhimey Joined on 10-16-2007, 4:56 PM Posts 19	Re: Formatting results Reply Contact  Hi, and thanks for the reply. I did read the docs (at least some skimmed). The obove remark is exactly why I asked the question. As I already said I wand to order by date, by I do not want it to appear in the results. This remark forces me to include it in the SELECT. If I do so, I will get it as a column in the result.

• 08-29-2008, 8:13 PM

  In reply to

  Br0phy Joined on 05-20-2008, 4:52 PM Posts 6

  Re: Formatting results Reply Contact You have to include the ORDER BY fields in your SELECT clause. There's no way to avoid this. What you can do is run two LogParser queries. One to generate the result set and another to retreive everything but the time. A batch file like this will do it: ::delete the old temp file if it exists del tempLPResults.csv ::Run LogParser LogParser "[your SQL Here]" -q:ON -i:IISW3c -o:CSV > tempLPResults.csv LogParser "SELECT [List of fields (not 'time')] FROM tempLPResults.csv" -q:ON -i:CSV -o:NAT > CleanShineyResults.txt ::Clean Up del tempLPResults.csv Add an ORDER BY in either SQL statement for good measure Cheers Chris