Search results matching tag 'W3C input format'

Re: can i create leading zeros?

squadjot — Thu, 17 Jan 2008 14:20:32 GMT

Yes, I know that, it was more how syntax-wise to make the IF statement.. i'm not sure how exactly to put it up.
It must be something about asking how long the string is (of number), and accordingly adding needed zeros..

It's more like.. how? :)

RE: counting only highest value per i.e user

LogParser User : squadjot — Mon, 02 Apr 2007 16:24:00 GMT

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

RE: counting only highest value per i.e user

LogParser User : DEinspanjer — Mon, 02 Apr 2007 12:01:00 GMT

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".

counting only highest value per i.e user

LogParser User : squadjot — Sun, 01 Apr 2007 13:33:00 GMT

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 =)

RE: Trying to run an sql query with a sub-query, however log parser won't run it.

LogParser User : DEinspanjer — Fri, 23 Mar 2007 19:35:00 GMT

Yes, LP doesn't support that type of sub-query.
You'll have to do it in two passes.

Trying to run an sql query with a sub-query, however log parser won't run it.

LogParser User : Darren — Fri, 23 Mar 2007 16:07:00 GMT

Hello,

I'm trying to extract the unique users using Windows Mobile devices. However the below sql statement isn't working in the logparser GUI. Does anyone know what is wrong with this query?

Select Count(*)
From (Select cs(User-Agent), cs-username FROM 'c:\ex070322.log' where cs(User-Agent) like 'MSFT%' group by cs(User-Agent), cs-username)

RE: Problem with dates comparison, I need help please!

LogParser User : juasebsto — Mon, 26 Feb 2007 21:56:00 GMT

[QUOTE User="LogParser User : juasebsto"]Quote: Hey.. i dont tknow if this is what u need, but it works for me: WHERE date >= TIMESTAMP( '2006-09-28 00:00:00', 'yyyy-MM-dd hh:mm:ss' ) AND date <= TIMESTAMP( '2006-10-28 00:0...[/QUOTE]

Hi there,

Thanks very much for your answers. You may be think I'm crazy but I resolved it with the following script:

TO_TIMESTAMP(TO_STRING(STRCAT(STRCAT(STRCAT(STRCAT(SUBSTR(Date,0,4),'-'),REPLACE_CHR(SUBSTR(Date,5,2),'-','')),'-'),REPLACE_CHR(SUBSTR(Date,7,ADD(STRLEN(Date),-7)),'-',''))),'yyyy-M-d') > TIMESTAMP('" + DateLastItem + "','yyyy-M-d')

( (TO_TIMESTAMP(TO_STRING(STRCAT(STRCAT(STRCAT(STRCAT(SUBSTR(Date,0,4),'-'),REPLACE_CHR(SUBSTR(Date,5,2),'-','')),'-'),REPLACE_CHR(SUBSTR(Date,7,ADD(STRLEN(Date),-7)),'-',''))),'yyyy-M-d') = TIMESTAMP('" + DateLastItem + "','yyyy-M-d'))

AND

(TO_TIMESTAMP(REPLACE_STR(REPLACE_CHR(Time,' ',''),'GMT',''),'h:m:s') >= TIMESTAMP('" + TimeLastItem + "','h:m:s')) ))

I tested it with many log files and it does what I need. I found out after a while that there are some little problems when dates come with the format yyyy-M-d even if you convert them with the CONVERT function to yyyy-MM-dd.

I hope it can be useful for someone.

juan!

RE: Problem with dates comparison, I need help please!

LogParser User : squadjot — Sat, 24 Feb 2007 21:59:00 GMT

Hey.. i dont tknow if this is what u need, but it works for me:

WHERE date >= TIMESTAMP( '2006-09-28 00:00:00', 'yyyy-MM-dd hh:mm:ss' )
AND date <= TIMESTAMP( '2006-10-28 00:00:00', 'yyyy-MM-dd hh:mm:ss' )

Cheers

RE: Problem with dates comparison, I need help please!

LogParser User : Jeff — Thu, 22 Feb 2007 16:43:00 GMT

this should work:

WHERE TO_TIMESTAMP(Date,'yyyy-MM-dd') = TO_TIMESTAMP('2007-02-10', 'yyyy-MM-dd')

Exchange 5.5 NDRs from tracking.log

LogParser User : david.inglis — Thu, 22 Feb 2007 11:54:00 GMT

Hi,

Complete newbie to logparser...

I had an exchange 5.5 site lose WAN connectivity recently. Their inbound mail sat in the MTA queue for two days, then they all got NDR'd (something timed out i assume).

Now the connection is back, the users want their email! I've told them they can't, but that I should be able to provide them with a list of what they've missed, so they can contact the relevant people and get the mails resent if necessary. (there are only 40 or so users involved here).

Can anybody point me in the right direction for a logparser query I can point at the tracking logs to retrieve this info? eg. a list of NDR'd email between two dates? Is this possible?

Thanks for any advice.

David