Need to supress several rows into log fileRSS

4 replies

Last post Feb 22, 2007 11:59 AM by Anonymous

  • Need to supress several rows into log file

    Feb 15, 2007 03:37 PM|Anonymous|LINK

    I am using the Log Parser to investigate data into my log file.I need your help to undestand the best way to supress differents rows  into the IISW3C log file.

    I have several cs(referer) that I need to supress from my output file. I know that I can use "not like" but it is only work for one condition. I have some condition like these:

    %www.eu.edica.%

    http://webmail.ascolfa.edu.co%

    http://us.f347.mail.yahoo.com/ym%

    https://mail.google.com/mail/?%

    LogParser -i:W3C -o:NAT "select distinct  cs(referer) as REFERER INTO out020714.csv from 021407.log where cs(referer) not like '%http://us.%.mail.yahoo.com%' and cs(referer) not like '%www.%.edica.%'"

    This work fine only for this 2 conditions, but I have several more conditions that I need to supress into my fle.

    I thougth something like this:

    LogParser -i:W3C -o:NAT "select distinct  cs(referer) as REFERER INTO out020714.csv from 021407.log where cs(referer)  cs(referer) not in ( select all from archivo.log)"

    where archivo.log should contain the distinct words that should contain the cs(referer) into the log file. That should be the idea.

    I will appreciatte your help a lot.

    Regards

    Gonzalo

    IISW3C input format

  • Re: Need to supress several rows into log file

    Feb 19, 2007 01:32 PM|Anonymous|LINK

    I'm just listening here, cause i would like to know if that would work as well.

    So you mean you would have a file containing the referrer URLs u want to parse?

     

    //jot

    IISW3C input format

  • Re: Need to supress several rows into log file

    Feb 20, 2007 06:58 AM|Anonymous|LINK

    You can't use the CSV list of referrers to filter because LP doesn't support subqueries against a different input format than the main query.

    If you really don't want to have a static list of long OR referer LIKE ... clauses in your query, the easiest thing I can think of would be to use a meta-query to query your CSV list of filter strings and have it create a new query file with that long list of LIKE clauses from it using the TPL output format. You could then run LP with that generated SQL file.

    I posted an example of using the TPL output format to write a new SQL query a while back in the General Discussion forum.

    IISW3C input format

  • Re: Need to supress several rows into log file

    Feb 21, 2007 10:19 AM|Anonymous|LINK

    Thanks so much for your response. I already tried to find your example but i did not find the one that cover my needs in this case.

    I am attaching a log example to be more clear in my doubts. If you see into the example log, you will find some referer that start with http:\\bancoplex.com.... ,another ones that start with http://by107fd.bay107.hotmail... , another ones that start with http://www.google.com . I need to supress these links into my new file. So I just want to include the ones that don't have the condition above.

    I hope you can help me to indetify the best query to do that and get the results that I need in my final file.

    Thanks.

    Gonzalo

    IISW3C input format

  • Re: Need to supress several rows into log file

    Feb 22, 2007 11:59 AM|Anonymous|LINK

    You only choice is to list each referer out in the where clause.  You do have a choice of how to do this:

    where (not cs(referer) like 'http://www.google.com/%') and (not cs(referer) like 'http://by107fd.bay.107.hotmail.com/%') and ...

    or

    where (not to_lowercase(extract_token(cs(referer),2,'/')) in ('www.google.com';'by107fd.bay.107.hotmail.com';...)

    (replacing ... with the repeat using the other sites you want to filter out.  As was pointed out, you could use a Log Parser Template to build this list (or at least make it a little easier to build).  Though this might be just as easy (since the array given to the IN statement can span multiple lines in a .sql file):

    select strcat(strcat('\'',to_lowercase(extract_token(cs(referer),2,'/')) ,'\';') as refererstrings from logfilename.log group by refererstrings order by refererstrings

    Which will give you:

    'www.google.com';

    'by107fd.bay.107.hotmail.com';

    ...

     

     

    IISW3C input format