Link to home
Start Free TrialLog in
Avatar of kjkamin
kjkaminFlag for United States of America

asked on

Log Parser query for OWA connections into report

Hi all...

My boss' [soon to be ex] wife has been reading his email.  I've already confirmed this by browsing the W3SVC1 logs.

I need to parse all existing W3SVC1 logs and filter into a report ONLY the connections to his account.  The report must include the date/time of connection, the IP address that originated the connect, and any browser type information available in the logs.  Reason for the browser type need is  to confirm the source (she is using AOL via an Apple computer).

I installed the MS Log Parser tool and toyed with some querys to collect the info, but its obviously outside my abilities.

Here's the Q ... Can anyone help with the Log Parser query statement?

Lot's o' points for the winner since he's deadlined me for an afternoon report on this matter.

Thanks in advance!
Avatar of LeeDerbyshire
LeeDerbyshire
Flag of United Kingdom of Great Britain and Northern Ireland image

Sorry, can't help with the Log Parser (I tried it once, but gave up).  I would suggest using Excel, though. Trouble is, you can only do one at a time.  Load it in as a delimited file, and then you can apply an autofilter for the username field.

Let me know if you don't fancy using Excel, and I'll have an other look at the Log Parser - I still have it installed.
Right, I've had a look at LogParser- it's a bit SQL-like.  Which fields in your IIS logs do you want to check for your boss's username?  Are you logging the clients logon ID i your IIS logs?  If not, it will have to be part of the URL.
ASKER CERTIFIED SOLUTION
Avatar of LeeDerbyshire
LeeDerbyshire
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kjkamin

ASKER

Thanks...your suggestion was right on path.

Added the other criteria I needed to the SELECT query and ended up with:

LogParser "SELECT date, time, c-ip, cs-uri-stem, cs(User-Agent)
FROM C:\Windows\System32\LogFiles\W3SVC1\ex*.log TO Output.csv WHERE cs-uri-stem LIKE '%Username%'"

It got us exactly what we needed.

Thanks for taking the time to help!

Avatar of Amivit
Amivit

Wow, how weird is that. Found this thread though Google. My boss is also in the divorce process, and the wife is the manager of a few of our branches making things even more complicated.

But thanks LeeDerbyshire for your answer, it helped me out aswell!
HI guys, I just done that... but it seems I cannot find the output.csv.... where is the location?? Thank you..
If you typed it as it appears above, it's probably in your My Documents folder.  Hard to say, really.  It ought to have a full path specified, like C:\output.csv instead of just output.csv .
Thank you for your reply!! I actually did that and it says Elements output: 0
I tried on exchange 2003 server it works though... this server is SBS2008 and the log location also different, so I used the line here and the result:

C:\Program Files (x86)\Log Parser 2.2>LogParser "SELECT date,time,c-ip,cs-uri-st
em FROM C:\inetpub\logs\LogFiles\W3SVC3\u_ex*.log TO Test.csv WHERE cs-uri-stem
LIKE '%/Exchange/natasha%'"

Statistics:
-----------
Elements processed: 1137809
Elements output:    0
Execution time:     37.67 seconds
Are you sure the files are in W3SVC3?

If so, can you find an entry in a log file in that folder which matches your query  (i.e. having /Exchange/natasha in exactly that form and capitalisation)?
Yes. it's in W3SVC3. There was no user name in W3SVC1.... Here is the sample I took from the W3SVC3. Please see the attachment. And As I am checking OWA access, why we use /exchange?? Again, I really appreciate your help!


sample.txt
I even tried this
C:\Program Files (x86)\Log Parser 2.2>LogParser "SELECT date,time,c-ip,cs-uri-st
em FROM C:\inetpub\logs\LogFiles\W3SVC3\u_ex*.log TO Test.csv WHERE cs-uri-stem
LIKE '%natasha%'"

no luck...
update: For some reason I use the above command and it went through....
but I only got few entries, the total size is 5 kb.
I checked the logs in the folder that contains from 2009 up to today... but there are only some entries from 2010 and 2011
Oh, right.  If you have E2007 or E2010, then your users don't put /Exchange on the end of the URL.  You might try something like

WHERE UPPER(cs-uri-stem) LIKE '%/OWA/%' AND UPPER(cs-username) LIKE '%NATASHA%'

assuming that (like normal SQL, it supports UPPER()

I just made it work now. I use the following:

"C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT date, time, c-ip, cs-uri-stem, cs-username, cs(User-Agent), cs-uri-query FROM C:\inetpub\logs\LogFiles\W3SVC3\u_ex*.log TO c:\temp\Output.csv WHERE cs-username LIKE '%jdoe%'"
Fine, as long as your users don't use the server's web site for anything other than OWA.  For instance, your query will probably find entries generated by ActiveSync and Outlook Anywhere users (if you have any) and anyone using Outlook 2007 or 2010 will probably have generated Autodiscover entries in there.  If you have any Mac users, then you will find Entourage entries in there.  I would also test it to make sure that if someone enters their username in upper case letters, that iis logs the user name in lower case letters, so that your query can still find them.  I don't want to sound nit-picky, but if it's important that you get completely accurate OWA stats, then you may find that you need to modify your query slightly.  If you only want something approximate, then what you have will be fine.