LogParser count(distinct ...) group by

Question

I'm using LogParser 2.2.
I would like to process IIS log to get a list of tuples:
(c-ip, number_of_different_user-agents_used_by_that_c-ip)

In Oracle I would do:

select c-ip, count(distinct cs(User-Agent))
from ...
group by c-ip

but in LogParser I get:
"Error: Semantic Error: aggregate functions with DISTINCT arguments are not supported with GROUP BY clauses"
as per the documentation
"DISTINCT can only be used when the query does not make use of the GROUP BY clause"

I can do it in two steps, but any way to do it in one step?
Thanks for your help.

Answer 1

Hi @José Miguel del Río Martínez

You can try this:

select c-ip , count(cs) from (  
select distinct c-ip,cs(User-Agent) cs from ...) a  
group by c-ip  

If there is still a problem, please post your data table.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi SamWu,
Thanks for your answer, but it seems LogParser doesn't support FROM subqueries:

select c-ip , count(cs) from (
select distinct c-ip, cs(User-Agent) cs from 'd:\tmp\ex210914.log')
group by c-ip

Error: The specified FROM-ENTITY is not a filename, a Metabase path nor a ODBC specification: The specified <from-entity> ("(
select distinct c-ip, cs(User-Agent) cs from 'd:\tmp\ex210914.log')") is not a valid file nor a valid list of files