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!
kjkaminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LeeDerbyshireCommented:
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.
0
LeeDerbyshireCommented:
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.
0
LeeDerbyshireCommented:
Here's an example:

LogParser "SELECT time,cs-uri-stem FROM C:\Windows\System32\LogFiles\W3SVC1\ex*.log TO Test.csv WHERE cs-uri-stem LIKE '%/Exchange/Username%'"

Replace 'Username' with the correct logon name.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kjkaminAuthor Commented:
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!

0
AmivitCommented:
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!
0
okamonCommented:
HI guys, I just done that... but it seems I cannot find the output.csv.... where is the location?? Thank you..
0
LeeDerbyshireCommented:
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 .
0
okamonCommented:
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
0
LeeDerbyshireCommented:
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)?
0
okamonCommented:
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
0
okamonCommented:
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...
0
okamonCommented:
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
0
LeeDerbyshireCommented:
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()

0
okamonCommented:
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%'"
0
LeeDerbyshireCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.