0

How shall I design sql statement to also calculate the number of devices within every User-Agent ID?

I work in MS Log Parser Studio (LOGPARSER), working on programming different query Exchange logs (IIS W3C LOG) to compile statistics of mobile devices.

This is the query:

SELECT TO_STRING(To_timestamp(date, time), 'MM-dd-yyyy') as Day, cs(user-agent) as
UserAgent /* Count(MyDeviceId as DeviceId) as NrOfSameDevice 
USING
EXTRACT_VALUE(cs-uri-query,'DeviceId') as MyDeviceId */
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'
GROUP BY Day, UserAgent
ORDER BY Day

Result of this query is:

enter image description here

Christofffer
  • 413
  • 1
  • 5
  • 15

1 Answers1

0

I'm assuming you want to calculate the number of distinct devices for each Day, UserAgent pair.

This is normally done with a COUNT(DISTINCT) or a nested query, but since LogParser does not support neither (COUNT(DISTINCT) is not supported with GROUP BY), you'll have to break the query in two:

  1. The first query selects the distinct combinations of Day, UserAgent, and DeviceId:

    SELECT DISTINCT TO_STRING(To_timestamp(date, time), 'MM-dd-yyyy') as Day, cs(user-agent) as UserAgent, EXTRACT_VALUE(cs-uri-query,'DeviceId') as MyDeviceId INTO TmpOutput.csv FROM '[LOGFILEPATH]'

  2. The second query parses the result of the first one grouping by the first two fields:

    SELECT Day, UserAgent, COUNT(*) FROM TmpOutput.csv GROUP BY Day, UserAgent ORDER BY Day

Make sure that the first command is configured to also output headers into TmpOutput.csv.

Gabriele Giuseppini
  • 1,541
  • 11
  • 19