Log parser: Using a text file as an input in WHERE clause

Question

I am working on a batch script where I am parsing IIS logs using Log Parser. So, I have 2 questions here:

Q1. What I want is, not to see the entries having username written in a text file. So, one can update that text file without worrying about the code syntax.

In other words, Instead of putting every username(could be around 30-50) in 'WHERE' clause using 'AND' again & again, I will have a text file having list of usernames.

Code Example:

"LogParser.exe" -i:csv "SELECT DISTINCT date, cs-username, clientun, cs-uri-stem FROM D:\temp.csv WHERE NOT cs-username IN ('NULL';'abc';'def';'hij';'and_so_on')" >D:\final_output.txt -o:NAT -rtp:-1

I was trying to do this via sub-query first, but it is not supported in Log parser it seems. I found OPENROWSET as a solution here but that doesn't seems to be working for me or may be I am not getting how to make it work. Other solution I found were for SQL and were not working for log parser.

Q2. I want the logs to be read between the dates mentioned. So, I am taking a start and an end date from user in YYYY-MM-DD format and putting them in query

"LogParser.exe" -i:iisw3c "SELECT DISTINCT cs-username, REVERSEDNS(C-IP), date, cs-uri-stem, FROM \logs\location\* WHERE date BETWEEN %date_1% AND %Date_2%" >D:\temp.csv -o:csv

The error I get here is:

Error: WHERE clause: Semantic Error: left interval of BETWEEN operator ("'2016-02-15'") has a different type than operand ("date")

Please note, the output file generated from the Q2 is used as input in Q1.

MC ND · Accepted Answer · 2016-12-26 20:40:59Z

0

Q1: Instead of filtering the data using logparser use findstr and the /g:file /v switches to filter input files or output lines (depending on the case)

Q2: Strings are not timestamps. Use

BETWEEN TO_TIMESTAMP('%date_1%','yyyy-MM-dd') AND TO_TIMESTAMP('%date_2%','yyyy-MM-dd')

edited Dec 26, 2016 at 20:40

answered Dec 26, 2016 at 16:12

MC ND

71.1k8 gold badges95 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Moktik Dhawan Over a year ago

Ok. So, if I use 'findstr'. Can I put a file in '/v' parameter? Like, 'findstr /v excluded.txt' As said, the file will have the usernames which are to be excluded.

MC ND Over a year ago

@MoktikDhawan, the /v asks for lines that does not match the provided strings/patterns, you use the /g:file.txt switch to indicate the source of the strings/patterns to match. The combination of the two will retrieve the lines that do not match the strings/patterns in the file.

Moktik Dhawan Over a year ago

Got it now. I didn't see the /g:file as parameter. i thought its a different function. My bad. I will test this once and will update! Thanks a lot.

Collectives™ on Stack Overflow

Log parser: Using a text file as an input in WHERE clause

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related