Querying logs with DM Query
Fields for log entries
The relevant fields for each log entry that can be used when querying the logs are:
AppGroup = 'MIV'
Identifies the UNICOM Intelligence Interviewer product logs.
InterviewId
The user who performed the live interview or data entry operation.
RespondentID
The unique case ID.
Project
The project name.
DateTime
The log entry timestamp.
LogEntry
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
▪when a case is created, opened, completed, or removed
▪batch information
▪project related work, such as opening a project and creating or importing deployment packages
▪synchronization information
▪found errors.
To query logs used in UNICOM Intelligence Interviewer, use AppGroup = 'MIV' in your query.
Examples
Finding all log entries for a specific project
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.
|
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
This example could be used in an application to determine the amount of time it took for someone to complete their work.
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'
See also