• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5000
  • Last Modified:

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!
0
kjkamin
Asked:
kjkamin
1 Solution
 
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now