Exchange Active Sync Reporting

[mb]

EHLO just posted this
(http://blogs.technet.com/exchange/archive/2006/02/14/419562.aspx) SQL
query to generate reports on EAS users.

I tried to get this to run but it isn't working. LogParser is given me
the following error:

"WARNING: Input format not specified - using TEXTLINE input format.
Error: SELECT clause: Syntax Error: unknown field 'cs-username'

To see valid fields for the TEXTLINE input format type:
LogParser -h -i:TEXTLINE"

It seems to me that the SQL query is creating a data type called
cs-username which is supposed to pull all the user info from the
Exchange logs.

I noticed that they are references the log file at c:\public\logs\*.log
which is not where mine are stored.

Obivously I'm not getting the info from the logs, but I can't figure
out why. Has anyone else tried this or might know what I need to edit
for my configuration.

Thanks.

[Michael]

Hi Mike,

Try explicitly specifying the correct input format -- IISW3C

like this...

LogParser.exe -i:IISW3C
file:c:\Public\LogParser\SQL\Hits_By_DeviceType.sql

Post by mb
EHLO just posted this
(http://blogs.technet.com/exchange/archive/2006/02/14/419562.aspx) SQL
query to generate reports on EAS users.
I tried to get this to run but it isn't working. LogParser is given me
"WARNING: Input format not specified - using TEXTLINE input format.
Error: SELECT clause: Syntax Error: unknown field 'cs-username'
LogParser -h -i:TEXTLINE"
It seems to me that the SQL query is creating a data type called
cs-username which is supposed to pull all the user info from the
Exchange logs.
I noticed that they are references the log file at c:\public\logs\*.log
which is not where mine are stored.
Obivously I'm not getting the info from the logs, but I can't figure
out why. Has anyone else tried this or might know what I need to edit
for my configuration.
Thanks.

[mb]

It seems to be getting closer to working.

Now I get a new error message that says:

Task completed with parse errors.
Parse errors:
30 parse errors occurred during processing (To see details about the
parse
error(s), execute the command again with a non-zero value for the
"-e"
argument)

Statistics:
-----------
Elements processed: 0
Elements output: 0
Execution time: 0.08 seconds

I add the -e:1 option and this is what it returns now...

Task aborted.
Too many parse errors - aborting
Parse errors:
Cannot find '#Fields' directive in header of file
"C:\WINDOWS\system32\LogFil
es\IN051102.log". Lines 1 to 7 have been ignored
Cannot find '#Fields' directive in header of file
"C:\WINDOWS\system32\LogFil
es\IN051103.log". Lines 1 to 4 have been ignored

Statistics:
-----------
Elements processed: 0
Elements output: 0
Execution time: 0.08 seconds

[Michael]

Could you take a look at the first few lines in your logs and see if it
looks similiar to this...

#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2005-12-07 22:00:01
#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem
cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent)
cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status
sc-bytes cs-bytes time-taken

If it doesn't look similiar to this, could you paste in what you have?

Also, based on the log name "IN*.log" it looks as though you are using
the "Microsoft IIS Log File Format" instead of the "W3C Extended Log
File Format". While you can still use this with log parser you will
need to do two things to get Log Parser to recognize the correct field
names:
- Change the input format to -i:IIS (from -i:IISW3C)
- Modify the query slightly to use the IIS input format fields.

Here's a quick remapping for you from IISW3C format to IIS: (based on
the fields used in the blog). NOTE: The Log Parser help has the fields
documented.

cs-username -- UserName
cs-uri-query -- Parameters
cs-uri-stem -- Target

Let's see if that works for you.

[mb]

Thanks!

Turns out that the logfiles were actually in a subdirectory of
C:\WINDOWS\system32\LogFiles\

I missed this because it was outside of the textbox to specify the
location of the logfiles, right below it actually, oh well. The log
files I was orginally trying were in the IIS format (and about 2 years
old), but it worked perfectly once I referenced the proper files, in
the W3C Extended Log File Format.

I've exported the information into a csv file and then imported into
Excel. I could make graphs from this, however the EHLO blog says there
is a way to dynamically generate them using Web Components. Have you
used this or tried this? If not I'll tinker with it and see what I can
turn up.

Thanks again.

[Michael]

The second SQL Statement example in the blog shows how you can output
to a 3D pie chart by using the "INTO" function.

The command line syntax would look like this...

LogParser.exe -i:IISW3C -o:CHART -values:ON -ChartType:Pie3D
-ChartTitle:"Server Hits by DeviceType" -view:ON file:<<LOCATION TO SQL
FILE>>\Hits_By_DeviceType.sql