kjkamin
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!
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\LogFil es\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!
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\LogFil
It got us exactly what we needed.
Thanks for taking the time to help!
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!
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\W 3SVC3\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
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\W
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)?
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
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\W 3SVC3\u_ex *.log TO Test.csv WHERE cs-uri-stem
LIKE '%natasha%'"
no luck...
C:\Program Files (x86)\Log Parser 2.2>LogParser "SELECT date,time,c-ip,cs-uri-st
em FROM C:\inetpub\logs\LogFiles\W
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
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()
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\W 3SVC3\u_ex *.log TO c:\temp\Output.csv WHERE cs-username LIKE '%jdoe%'"
"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\W
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.
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.