« Previous Next »

Thread: counting only highest value per i.e user

Last post 04-02-2007 12:24 PM by LogParser User : squadjot. 2 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (3 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 04-01-2007, 9:33 AM

    counting only highest value per i.e user

    Reply Contact

    Hi guys, i'm having a little trouble with this one...

    To get it right , i think i have explain my case properly.

    I need to find out how much time users are spending on specific pages, and due to technical issues on the frontend, the webapplication will send a time-"tick" every 10 secs or so.. I want only to collect the highest amount of time a user has been on a specific page (in one "session". So lets say the person visits the page .. but is only there for 30 secs..then he later visits same page.. but uses 220 secs on the page..  then i want only to use that last record ( the 220 secs), and i dont care about the 30 secs in the first visit.

    IMPORTANT the defiition of old/new session is determined by the session variable , not by the log timestamps.., and im not even sure sessionID needs to come into play in this particular case, cause all i really want do ..is simply get the highest number from "lastTime"-variable per user > per title...  right? (i'm asking cause i already confused my self, lol)..  

    again im doing it all with custom written variables, used in the uri-query string. Some strings could look like this

    titleID=TheBlackHorse&userID=EziRida87&sessionID=9876513&lastTime=30&r=etcetcetc
    titleID=TheBlackHorse&userID=EziRida87&sessionID=9876513&lastTime=40&r=etcetcetc
    titleID=TheBlackHorse&userID=EziRida87&sessionID=9956321&lastTime=80&r=etcetcetc
    titleID=TheGoldenShoe&userID=Petra&sessionID=36478421&lastTime=50&r=etcetcetc
    titleID=TheGoldenShoe&userID=Petra&sessionID=58478421&lastTime=60&r=etcetcetc
    titleID=TheGoldenShoe&userID=Petra&sessionID=6478421&lastTime=50&r=etcetcetc
    titleID=TheGoldenShoe&userID=EziRida87&sessionID=978421&lastTime=30&r=etcetcetc

    Even the output i'm not sure how to declare, i'm wondering if i have to do a query per title, cause i guess i would have to have each visiter listed, with their hiest number from lastTime

    Title                 User             Timespend            
    --------------  ------------   ----------------
    TheBlackHorse    EziRida87        80
    TheGoldenShoe   Petra             60
    TheGoldenShoe   EziRida87        30

    If this is the smartest way of outputting it..  i dont know either.. (cause in the end..its all about per title..really)

    edit: And maybe even easier, would be to  let LogParser create a whole new set of filtered W3C files, with only the highest visit numbers (seconds)..and then run querys on those?

    Still i need help on the <use only highest number> / <user>

     

    edit again: I found something in the Help file about MAX(Size) function., i guess this is the one i have to implement somehow.

     

    edit again again =).. oke..made major progress now:

    SELECT 
    EXTRACT_VALUE(cs-uri-query, 'titleID') AS TitleID,
    TO_LOWERCASE(EXTRACT_VALUE(cs-uri-query, 'userID')) AS User,
            MAX(TO_INT(EXTRACT_VALUE(cs-uri-query, 'lastTime'))) as TimeSpend
    FROM G:/testlog/*.log
    GROUP BY User, TitleID

    when running this on theese lines

    #Software: Microsoft Internet Information Services 6.0
    #Version: 1.0
    #Date: 2007-03-05 00:01:18
    #Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) cs-host sc-status sc-substatus sc-win32-status
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheBlackHorse&userID=EziRida87&sessionID=9876513&lastTime=30&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheBlackHorse&userID=EziRida87&sessionID=9876513&lastTime=40&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheBlackHorse&userID=EziRida87&sessionID=9876513&lastTime=50&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheBlackHorse&userID=Petra&sessionID=9876513&lastTime=50&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheBlackHorse&userID=Petra&sessionID=9876513&lastTime=60&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheBlackHorse&userID=Petra&sessionID=9876513&lastTime=20&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheGoldenShoe&userID=Petra&sessionID=9876513&lastTime=80&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheGoldenShoe&userID=EziRida87&sessionID=9876513&lastTime=20&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727)     http://futoo.dk/Stats/s.asp futoo.dk 200 0 0
    2007-03-05 00:22:08 W3SVC0123456789 DMZ-WTF01 192.123.4.56 GET /Stats/hit.aspx titleID=TheGoldenShoe&userID=EziRida87&sessionID=9276513&lastTime=30&s=http://gofurself.com/mediafiles/3/flash/hannibal_728x90.asp?clickTAG=.%2Findex.asp%3Fmod%3Dbanner%26id%3D17|||728x90||| 80 - 72.88.135.75 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) http://futoo.dk/Stats/s.asp futoo.dk 200 0 0

    then it gives me:

    TitleID              User           TimeSpend

    TheBlackHorse    ezirida87     50
    TheBlackHorse    petra          60
    TheGoldenShoe   petra          80
    TheGoldenShoe   ezirida87     30

    which is actaully what i want.. i think/hope i'm not missing something, im still concidering myself newbie in this stuff...so if someone can verify or tip me if i could do something better
    Any comment apreciated =)

     
    Tags:

  • 04-02-2007, 8:01 AM In reply to

    RE: counting only highest value per i.e user

    Mark as Answer
    Reply Contact
    Yes, that is exactly what I would have suggested to you.
    For further education, read the part of the help file on Aggregate Functions.

    When you group your results in a GROUP BY, any fields that are not part of the group by can be displayed by using aggregate functions to "sum them up".
    Tags:

  • 04-02-2007, 12:24 PM In reply to

    RE: counting only highest value per i.e user

    Mark as Answer
    Reply Contact

    Okei, thanks for the verification =), i think this is really close to the best way i can get it..

    The only thing what would make it even greater, was if i could somehow make further grouping by TitleID..
    I've once done it by mistake (other query example).. .but it, like, created several "outputtables" in one output.. if you know what i mean. (or is it what youre trying to tell me, that i can learn more about readuing up on the aggregate stuff =) )

    It's not necesary really.. cause all the data i'm filtering out here´are supposed to be parsed, once again, by 3rd party program.. but offcourse, the more "clean" i can get it the better..

    I'll probably figure it out.. i just love this little program , and i'm kinda forced to fiddle with it , as i'm responsible for  building this crazy stats project from scratch.

    Thanks again Daniel Einspanjer, hope you'll pop by the W3C again later on...i BET i will have some questions for you =) <3
    Tags:
Page 1 of 1 (3 items)
Microsoft Communities