SELECT  

            TO_LOWERCASE (cs-username) AS User,

            MyDeviceType                            AS DeviceType,

            COUNT(*)                                 AS Hits,

            SUM (MySync)                           AS Syncs,

            SUM (MyFolderSync)                  AS Folder_Syncs,

            SUM (MyPing)                            AS Pings,

            SUM (MySendMail)                     AS Emails_Sent,

            SUM (MySmartReply)                  AS SmartReplies,

            SUM (MyMeetingResponse)         AS Meeting_Responses,

            SUM (MyGetAttachment)             AS Get_Attachments

USING 

            SUBSTR (cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11), 

            INDEX_OF (SUBSTR(cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11)), '&')) AS MyDeviceType,

 

            SUBSTR (cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'Cmd='), 4), 

            INDEX_OF (SUBSTR(cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'Cmd='), 4)), '&')) AS MyCmd,

 

            CASE MyCmd

                        WHEN 'Sync' THEN 1

                        ELSE 0

            END AS MySync,

            

            CASE MyCmd

                        WHEN 'Ping' THEN 1 

                        ELSE 0

            END AS MyPing,

 

            CASE MyCmd

                        WHEN 'SendMail' THEN 1

                        ELSE 0

            END AS MySendMail,

 

            CASE MyCmd

                        WHEN 'SmartReply' THEN 1

                        ELSE 0

            END AS MySmartReply,

 

            CASE MyCmd

                        WHEN 'MeetingResponse' THEN 1

                        ELSE 0

            END AS MyMeetingResponse,

            

            CASE MyCmd

                        WHEN 'GetAttachment' THEN 1

                        ELSE 0

            END AS MyGetAttachment,

 

            CASE MyCmd

                        WHEN 'FolderSync' THEN 1

                        ELSE 0

            END AS MyFolderSync

/* CSV 

If you want to output to CSV, you will need to add the cmd-line parameter -o:CSV

EXAMPLE: LogParser.exe -i:IISW3C -o:CSV file:c:\Public\LogParser\SQL\User_Activity.sql

*/

INTO C:\Public\LogParserOutput\Hits_By_User.csv

FROM C:\Public\Logs\*.log

WHERE cs-uri-stem = '/Microsoft-Server-ActiveSync'

GROUP BY User, DeviceType

ORDER BY DeviceType, Hits DESC