Partager via


Custom SQL Queries

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Use the custom SQL filters in Analytics and Tuning Studio views to query log data from Speech Server applications. The custom SQL filters are available according to view hierarchy, as listed in the following table.

View Custom SQL Filter

Session Reports, Session List, and Session Detail views

Custom session SQL filter

Task Reports, Task List, and Task Detail views

Custom session SQL and Custom task SQL filters

Turn Reports, Turn List, and Turn Detail views

Custom session SQL, Custom task SQL, and Custom turn SQL filters

Sample SQL Queries

Sample SQL queries are listed in the following sections.

Selecting Turns Containing Audio

To only select turns for which audio is available in the database, add the following code to the Custom turn SQL filter.

TurnInfo.SpeechRequestId IN (SELECT EngineRecoAudio.SpeechRequestId from EngineRecoAudio WHERE EngineRecoAudio.SpeechRequestId IS NOT NULL)

Selecting Sessions Likely From One Caller

To select sessions with the same automatic number identification (ANI), in other words sessions likely to be from the same caller, for the same application within a window of three days, add the following code to the Custom session SQL filter.

SessionInfo.SessionInstanceId
IN ( SELECT B.SessionInstanceId
FROM SessionInfo AS A INNER JOIN SessionInfo AS B
ON A.SourceDeviceNumber = B.SourceDeviceNumber    -- The SourceDeviceNumbers must match
AND A.ApplicationID = B.ApplicationID    -- The sessions must be for the same application
AND A.SessionInstanceId <> B.SessionInstanceID    -- Session A is a different session to session B
AND A.TimeStamp <= B.TimeStamp    -- Session A must have occured before session B
AND (B.TimeStamp - A.TimeStamp) < 3    -- Session A must have occurred within 3 days before B
)

Filtering Turns by Language

To filter on turns in a particular language, use the Language field in a Turn view. For example, English (United States) turns can be selected by adding the following code to the Custom turn SQL filter.

TurnInfo.Language = 'en-us'

Similarly, use es-us for Spanish (United States), de-de for German (Germany), and so on.

Querying On Task Duration

To filter the task views to show only tasks with a duration of at least a certain period, add the following code to the Custom task SQL filter where n is the duration value in milliseconds above which tasks should be returned.

TaskInfo.TaskDuration > [n]

Querying On Transcriptions

To filter a set of turn results on the transcriptions entered for a turn, add the following code to the Custom turn SQL filter where phrase is the complete transcription.

TurnInfo.Transcription = '[phrase]'

To apply a similar filter using a partial match on the transcription, for example a particular word within the transcribed phrase, add the following code to the Custom turn SQL filter where word is the required partial match.

TurnInfo.Transcription LIKE '%[word]%'

Finding Sessions Containing Particular ApplicationDataEvent Properties

To query sessions that contain particular ApplicationDataEvent properties, add the following code to the Custom session SQL filter where class is a string with the value logged in the Class property, subclass is the value logged in the Subclass property, and data is the value logged in the Data property.

SessionInstanceId IN ( SELECT DISTINCT(ADI.SessionInstanceId) 
FROM ApplicationDataInfo AS ADI 
WHERE ADI.Class = ???[class]??? 
AND ADI.SubClass = '[subclass]'
AND ADI.Data = ???[data]???)

Finding Turns That Use a Particular Grammar

To select turns that use a particular grammar, add the following code to the Custom turn SQL filter where GrammarURI is any part of the grammar path, name, or rule, such as MyGrammar.cfg or MyPath/MyGrammar.grxml#MyRule.

TurnInfo.TurnInstanceId IN ( SELECT TurnInstanceId FROM TurnInfo TI 
INNER JOIN SpeechGrammarUsage SGU 
ON TI.SpeechRequestId = SGU.SpeechRequestId 
WHERE SGU.GrammarId IN ( SELECT GrammarId 
FROM Grammars WHERE URI LIKE '%[GrammarURI]%'))

Finding Turns for Which a Complete Grammar Is Available in the Database

Grammar Tuning Advisor cycles are more useful if they are executed only on turns for which a grammar is complete and available in the database. To select turns for which the grammar is complete in the database, add the following code to the Custom turn SQL filter.

TurnInfo.SpeechRequestId  IN 
(SELECT SpeechGrammarUsage.SpeechRequestId FROM SpeechGrammarUsage
INNER JOIN Grammars ON SpeechGrammarUsage.GrammarId = Grammars.GrammarId
WHERE Grammars.TotalLength > 0)

See Also

Tasks

How to: View Analytics and Tuning Studio Reports

Other Resources

Speech Application Data Analysis