Querying logs with DM Query

Refer to the following topics in the UNICOM Intelligence Developer Documentation Library for information on using DM Query with log files:

▪How to Run the Example Queries on Log Files Using DM Query

The relevant fields for each log entry that can be used when querying the logs are:

The user who performed the live interview or data entry operation

Free form entry, with additional details for a log entry, that includes:

▪various modes of operation, such as completing initial data entry for a case

▪project related work, such as opening a project and creating or importing deployment packages

After loading the appropriate logs in DM Query, you can perform queries such as the following:

Note To query logs used in UNICOM Intelligence Interviewer, use AppGroup = 'MIV' in your query.

SELECT * FROM vdata WHERE AppGroup = 'MIV' AND Project = ‘PROJECTNAME’

Finding all log entries for a specific project, batch, operator, and date range

SELECT * FROM vdata WHERE AppGroup = 'MIV' AND Project = ‘PROJECTNAME’ AND (LogEntry.Find('Batch') <> -1) AND LogEntry LIKE '%B1%’
AND InterviewId = 'USER' AND DateTime > '3/26/2009 7:14:05 AM' AND DateTime < '3/26/2009 8:49:13 AM'

Finding all entries for a specific project, operator, and after a certain date and time, when a case is opened for initial data entry

SELECT * FROM vdata WHERE AppGroup = 'MIV' AND InterviewId = 'USER' AND Project = ‘PROJECTNAME’ AND (LogEntry.Find('entry
mode initial') <> -1) AND DateTime > '2009-03-16'

To find the various entry modes, the following mode keywords can be used in place of initial (as shown above).

Mode keyword	Description
initial	Initial data entry
full	Full verification
partial	Partial verification
interview or live	Live interviews

Finding the number of live interviews completed by an operator for a specific project

SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed live') <> -1) AND
InterviewId = 'USER'

Finding the number of initial data entry cases completed by an operator for a specific project

SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed initial') <> -1) AND
InterviewId = 'USER'

Finding the number of full verification cases completed by an operator for a specific project

SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed full') <> -1) AND
InterviewId = 'USER'

Finding the number of partial verification cases completed by an operator for a specific project in a specific data range

SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed partial') <> -1) AND
InterviewId = 'USER' AND DateTime > '3/26/2009 7:14:05 AM' AND DateTime < '3/26/2009 8:49:13 AM'

Timestamps for a specific operator who was working on a case (identified by its RespondentId), batch, and project

SELECT DateTime, Project, RespondentId, InterviewId FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND InterviewId
= 'USER' AND LogEntry LIKE '%Batch B1%' AND RespondentId = '6e486ab8-5c6a-4624-9c3f-ab99646442e7' AND DateTime > '3/26/2009 7:14:05
AM' AND DateTime < '3/26/2009 8:49:13 AM'

The example above could be used within an application to determine the amount of time it took for someone to complete their work.