How to use Logparser to analyze IIS logs wrt Activesync troubleshooting?

  • Article

How to use Logparser to analyze IIS logs wrt Activesync troubleshooting?

Logparser is an generic utility o analyze log files that can be downloaded from https://www.microsoft.com/download/en/details.aspx?displaylang=en&id=24659 the current version available when I am writing this post is 2.2.

“Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory.”

 

You will have to download and install the logparser tool from where you would like to run this tool. Once installed depending on your requirement you can tweak the command to get the desired output.

Here I am only going to give you some example commands to get the desired output from IIS logfiles wrt Activesync users/devices.

Command Format

Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from '<PATH where the log files are present>' WHERE s-ip LIKE '10.1.1.185' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "<PATH where the Output CSV file must be present>"

Now let’s see the IIS header information if IIS 6.0 and IIS 7.5

  

#Software: Microsoft Internet Information Services 6.0

#Version: 1.0

#Date: 2011-02-17 00:00:09

#Fields: date time s-sitename s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status

2011-09-25 00:00:09 W3SVC1 10.1.1.71 POST /Microsoft-Server-ActiveSync/default.eas Cmd=FolderSync&User=username&DeviceId=HTCAndc4218af585&DeviceType=inc&Log=V121_St:S_LdapC0_LdapL0_RpcC15_RpcL0_Pk1137087964_ 443 domain.com\username174.252.139.112 Android-EAS/0.1 200 0 0

 

 

 

#Software: Microsoft Internet Information Services 7.5

#Version: 1.0

#Date: 2011-02-13 00:00:00

#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-take

2011-09-27 00:00:00 10.1.1.71 POST /Microsoft-Server-ActiveSync/default.eas Cmd=Sync&User=username&DeviceId=HTCAnda9644c8385&DeviceType=inc&Log=V121_Fc1_Fid:8_Ty:Co_Filt0_St:S_Sk:1045154935_Sst44_SsCmt44_BR1_BPR0_LdapC1_LdapL16_RpcC39_RpcL46_Ers1_Pk3076501135_S1_As:AllowedG_Mbx:s -mail1.susqu.edu_Throttle0_Budget:(A)Conn%3a0%2cHangingConn%3a0%2cAD%3a%24null%2f%24null%2f1%25%2cCAS%3a%24null%2f%24null%2f1%25%2cAB%3a%24null%2f%24null%2f0%25%2cRPC%3a 24null%2f%24null%2f1%25%2cFC%3a1000%2f0%2cPolicy%3aDefaultThrottlingPolicy%5F209476d2-cf80-4819-96e6-2adb208d5c65%2cNorm_ 443 domain.com\username 97.23.44.128 Android-EAS/0.1 200 0 0 202

 

 

 

The important information to note hear is the Fields section. This is what we can use to modify the logparser commands to get the desired output. Other info to note is the legacy log file name starts with “ex” where as 7.5 logfile name start with “u_ex”, this is required to change the command accordingly.

 

If we have a bunch of logfiles and need all of them to be analysed we can use u_ex*.log or ex*.log at the end of the Path to do so. In the below examples I have used the same.

 

 

Examples

To Find all users connecting to a “Particular Source”

Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from 'C:\Users\praveen\logs\u_ex*.log' WHERE s-ip LIKE '10.1.1.185' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "C:\Users\praveen\log\Output.csv"

 

 

From Some particular Client

Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from 'C:\Users\praveen\logs\u_ex*.log'
WHERE c-ip LIKE '173.136.203.224' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "C:\Users\praveen\log\Output.csv"

 

 

From Some Particular Device like android or Iphone with some specific version

Iphone

 Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from 'C:\Users\praveen\logs\u_ex*.log' WHERE
cs(User-Agent) LIKE 'Apple-iPhone3C1/802.117' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "C:\Users\praveen\log\Output.csv"

 

Android

Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from 'C:\Users\praveen\logs\u_ex*.log' WHERE
cs(User-Agent) LIKE 'Android-EAS/0.1' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "C:\Users\praveen\log\Output.csv"

           

 

                     

From All Apple Devices

Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from 'C:\Users\praveen\logs\u_ex*.log' WHERE cs(User-Agent) LIKE '%Apple%' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "C:\Users\praveen\log\Output.csv"

 

 

 

From All Android Devices

Logparser "SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits from 'C:\Users\praveen\logs\u_ex*.log' WHERE
cs(User-Agent) LIKE '%Android%' AND cs-username IS NOT NULL GROUP BY User, c-ip, cs(User-Agent) ORDER BY ExchangeHits desc" -o:csv > "C:\Users\praveen\log\Output.csv"

 

 You can modify the command as you like to get the desired Output.

Output.csv will contain the fields that are present in the “Select” section in each command.

 

 

Kestryl in this https://kestryl.com/blog/?p=6 blog has given the following information about Iphone version and the number that appears on the IIS log

901.334 = 5.0 (GM Release of iOS 5)

812.1 = 4.3.5

811.2 = 4.3.4

810.3 = 4.3.3 (Verizon iPad2)

810.2 = 4.3.3

808.8 = 4.3.2 (Verizon iPad 2)

808.7 = 4.3.2

807.4 = 4.3.1

806.191 = 4.3 (iPad 2)

806.190 = 4.3

805.6 = 4.2.10 (Verizon iPhone)

805.501 = 4.2.9 (Verizon iPhone)

805.401 = 4.2.8 (Verizon iPhone)

805.303 = 4.2.7 (Verizon iPhone)

805.200 = 4.2.6 (Verizon iPhone)

805.128 = 4.2.5 (Verizon iPhone)

803.148 = 4.2.1

802.117 = 4.1

801.400 = 4.0.2

801.306 = 4.0.1

801.293 = 4.0

705.18 = 3.1.3

704.11 = 3.1.2

703.144 = 3.1

702.5= 3.3

702.405= 3.21

702.367= 3.2

701.400 = 3.0.1

701.341 = 3.0

508.11 = 2.2.1

Hardware versions (Reported only when upgraded to iOS 4)

iPhone3C3 = 4 (Verizon)

iPhone3C1 = 4

iPhone2C1 = 3Gs

iPhone1C2 = 3G

iPad2C3 = iPad 2 (Verizon)

iPad2C2 = iPad 2 (AT&T)

iPad2C1 = iPad 2 (WiFi)

iPad1C1 = iPad

iPod2C1 = iPod Touch 2

iPod3C1 = iPod Touch 3

iPod4C1 = iPod Touch 4

 

Those who want to know more about Logparser go through the following links

https://blogs.technet.com/b/exchange/archive/2007/09/12/3403903.aspx

https://www.stevebunting.org/udpd4n6/forensics/logparser.htm

https://www.msexchange.org/tutorials/using-logparser-utility-analyze-exchangeiis-logs.html

 

If you have any queries or concerns write to me :) .