Querying logs with DM Query
Fields in log entries
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
Additional information, including:
▪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.
Examples
To query logs used in UNICOM Intelligence Interviewer, use AppGroup = 'MIV' in your query.
To find all log entries for a project
SELECT *
FROM vdata
WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME'
To find all log entries for a 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'
To find all entries for a 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 other entry modes, use the following mode keywords instead of initial.
|
Entry mode
|
Mode keyword
|
|
Initial data entry
|
initial
|
|
Full verification
|
full
|
|
Partial verification
|
partial
|
|
Live interviews
|
interview or live
|
To find the number of live interviews completed by an operator for a project
SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed live') <> -1) AND
InterviewId = 'USER'
To find the number of initial data entry cases completed by an operator for a project
SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed initial') <> -1) AND
InterviewId = 'USER'
To find the number of full verification cases completed by an operator for a project
SELECT Count(*) FROM vdata WHERE AppGroup = 'MIV' AND Project = 'PROJECTNAME' AND (LogEntry.Find('completed full') <> -1) AND
InterviewId = 'USER'
To find the number of partial verification cases completed by an operator for a project in a 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'
To find timestamps for an operator who was working on a case (identified by its RespondentId), batch, and project
The following example could be used in an application to calculate 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