Log parser 2.2 Query to count Unique user

Question

I've been trying to build a query over a custom log of mine where I sort the users based on certain criteria to have some overview of them.

My log contains a entry for each time a user tries to download a file, that entry contains date, ip, a custom generated token and how many times that user has tried.

The token is stored by SESSION and a token is only valid for 5 attempts of downloading, So that means that one ip can have multiple users(with different tokens) that each have different amount of attempts.

What I want to achieve is rather simple, I want to group the users by ip, and then count their amount of attempts, and then find out how many users there are.

The amount is not counted per IP but rather per token meaning a log entry may look like this:

IP                 TOKEN      ATTEMPT
111.111.111.111    DK1234     a1
111.111.111.111    DK9876     a1
111.111.111.111    DK9876     a2
222.222.222.222    DK5432     a1

Below is my latest attempts of trying to achieve this, but while I try to make the logic behind it work it just isn't what I want.

(The fields involved are: Ip, Token and Attempt (The attempt value looking like this: a1, a2, a3 and so on for each attempt the user makes).)

SELECT 
    Ip,
    CASE TO_INT(replace_chr(Attempt, 'a', '')) 
        WHEN 1 
        THEN 
            'MUL' 
        ELSE 
            'ONE' 
    END 
    AS Users,
    SUM(TO_INT(replace_chr(Attempt, 'a', ''))) AS Attempts
FROM 
    --LOG PATH
WHERE 
        Status = 'SUCCESS' 
    and 
        TO_DATE(TO_TIMESTAMP(LDate, 'dd/MM/yyyy-hh:mm:ss')) > SUB( TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('8','d') )
GROUP BY
    Ip,
    Users

If I could somehow store a value to increase for each unique Token per IP and store it with the results, but I cannot / do not know a way to achieve this either.

Using DISTINCT won't work either because when I do I get a error saying that DISTINCT cannot work with GROUP BY and my SUM() / Possible COUNT() won't work when Ip isn't in a GROUP BY

(The snippet below is what I have tried with DISTINCT / count)

SELECT 
    Ip,
    COUNT(DISTINCT Token),
    SUM(TO_INT(replace_chr(Attempt, 'a', ''))) AS Attempts
FROM 
    --Log Path
WHERE 
        Status = 'SUCCESS' 
    and 
        TO_DATE(TO_TIMESTAMP(LDate, 'dd/MM/yyyy-hh:mm:ss')) > SUB( TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('8','d') )
GROUP BY
    Ip

How I'd like my result grid to end up: (Without the explanation text of course)

IP                 Users     Attempts
123.456.789.012     4           4 (4 users each trying one time)
120.987.654.321     2           5 (2 users, One user tried once and the other user tried 4 times)
444.444.444.444     1           1 (One user, one attempt)

I hope I'm making sense, otherwise I'll be happy to elaborate / explain anything needed :)

`Microsoft` and `SQL` isn't clear enough - are you using Microsoft **SQL Server** (then please add the `sql-server` tag!) or are you using SQL in Microsoft Access (then please add a `ms-access` tag) — marc_s, Jan 28 '15 at 10:32
It's a custom log that is loaded through Log Parser Lizard, this is however just a GUI for Microsoft's Log parser 2.2, so I am unsure what it based upon, (http://www.microsoft.com/en-us/download/details.aspx?id=24659) — Epodax, Jan 28 '15 at 11:03

score 1 · Accepted Answer · answered Jan 29 '15 at 06:52

1

I believe you need two stages. The first stage collapses the entries per-user:

SELECT 
    Ip,
    Token,
    MAX(TO_INT(replace_chr(Attempt, 'a', ''))) AS Attempts
FROM 
    ...
GROUP BY
    Ip,
    Token

The second stage then rolls up by Ip:

SELECT 
    Ip,
    COUNT(*) AS Users,
    SUM(Attempts) As TotalAttempts
FROM 
    ...
GROUP BY
    Ip

answered Jan 29 '15 at 06:52

Gabriele Giuseppini

1,541
11
19

I realise out from your answer that I haven't explained it propperly, Each user has an Attempts counter, the attempt counter is not per IP but per token. – Epodax Jan 29 '15 at 07:58
Yes, and the first stage above will tell you the number of attempts for each user, as it groups by also by token. But are you trying to aggregate results for the same token/user across different IP's as well? – Gabriele Giuseppini Jan 29 '15 at 10:08
No, the Token is generated by country code and then a set of random numbers, and is then stored in Session, so usually a token is only used once for a single ip, while the Ip may have different tokens / users - Secondly I couldn't get to execute two curries in one, I get a error saying "Error parsing query, Syntax error, Extra token(s): SELECT – Epodax Jan 29 '15 at 10:22
Then the two-stage queries does what you're looking for. The first query returns the number of attempts for each user (identifying the user by the IP-TOKEN pair), while the second query rolls up the results of the first one giving you the number of users for each IP, and the sum of the numbers of attempts for each user in that IP. Two-stage queries are not supported out-of-the-box, you'll have to run the first query, save its results to a temporary CSV file, and run the second query on the temporary CSV file. – Gabriele Giuseppini Jan 29 '15 at 17:04
Ah, I will try it, thanks for explaining it to me, sometimes I can be a bit nimwitted. – Epodax Jan 30 '15 at 07:20
Thank you so much :) it does exactly what I want it to do – Epodax Feb 02 '15 at 08:54

Log parser 2.2 Query to count Unique user

1 Answers1