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.
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')