Each time a new user signs in, Acrolinx creates a new user entry. You can use information about to associate client, host, and session information. To find the number of users who have ever signed in, count the number of rows in the UserEJB table.

SQL Query Syntax

SELECT Count(*) as Number_Of_Users FROM UserEJB

Result Example

NUMBER_OF_USERS1

Tables Queried

UserEJB

Each time a user checks a document, an entry is created in the DocumentEventEjb table. To see the relative frequency at which users check documents, join from UserEjb to DocumentEventEjb table and count the numbers of rows as Number_of_Checks grouped by the username. Acrolinx uses a WHERE clause and the ORDER BY statement to list the users whose checks are successful. You'll see these in descending order from most frequent to least frequent user.

SQL Query Syntax

SELECT UserName, Count(*) as Number_of_Checks FROM UserEJB u
INNER JOIN DOCUMENTEVENTEJB de ON de.USER_ID=u.ID
WHERE de.CHECKCOMPLETEDSUCCESSFULLY=1
GROUP BY UserName
ORDER BY Number_of_Checks DESC;

Result Example

USERNAME NUMBER_OF_CHECKS
John Smith 47
Jane Smith 29

Tables Queried

UserEJB, DocumentEventEJB

Each time you check a new document, it creates an entry in the DocumentEjb table. Count the rows in the DocumentEjb table to see the total number of checked documents. The second time (and succeeding times) you check a document, no entry is added to the DocumentEjb table. Instead, the additional entries are made to the DocumentEventEjb table (see the section "Total Number of Checks").

SQL Query Syntax

SELECT Count(*) as Total_Checked_Documents FROM DOCUMENTEJB

Result Example

TOTAL_CHECKED_DOCUMENTS
3

Tables Queried

DocumentEJB

You can check a document more than one time. The second time (and succeeding times) you check a document, no entry is added to the DocumentEjb table; entries are made only to the DocumentEventEjb table. Note the difference between these two tables. To get the total number of checks, query the DocumentEventEjb table.

SQL Query Syntax

SELECT Count(*) as Total_Checks FROM DOCUMENTEVENTEJB

                        Result Example
                     

TOTAL_CHECKS
3

Tables Queried

DocumentEventEJB

Each check creates an entry in the StatisticsEJB table. Add the entries in the WordCount column of the StatisticsEJB table to see the total number of checked words.

SQL Query Syntax

SELECT SUM(WORDCOUNT) as Total_Words_Checked FROM STATISTICSEJB;

                        Result Example
                     

TOTAL_WORDS_CHECKED
3,299

Tables Queried

StatisticsEJB

Each time you check a document, an entry is created in the DocumentEventEJB table. Use the DocumentEventEJB, ClientSessionEJB, and ClientEJB tables to see how many documents are associated with each application Integration type and version.

SQL Query Syntax

SELECT Count(*) as Checked_Docs_Per_Client, Application, Clientversion FROM DOCUMENTEJB d
INNER JOIN DOCUMENTEVENTEJB de ON d.ID=de.DOCUMENT_ID
INNER JOIN CLIENTEJB c ON c.ID = de.CLIENT_ID
GROUP BY APPLICATION, CLIENTVERSION;

Result Example

CHECKED_DOCS_PER_CLIENT APPLICATION CLIENTVERSION
12 acrolinx IQ™ Batch Checker 1.2.0

Tables Queried

DocumentEJB, DocumentEventEJB, ClientEJB

This query extracts the HOUR entries from the TIMESTARTED column in the DocumentEventEJB table. It provides the number of document checks started at a specific hour of the day. Use the following query to detect bottlenecks.

SQL Query Syntax

SELECT Count(*), EXTRACT(HOUR FROM de.TIMESTARTED) as HOUR FROM DOCUMENTEVENTEJB de
GROUP BY EXTRACT(HOUR FROM de.TIMESTARTED)
ORDER BY EXTRACT(HOUR FROM de.TIMESTARTED);

To produce more detailed time-based usage statistics, add the weekday or month in the query.

Result Example

COUNT(*) HOUR
1 15
3 18
8 19

                        Tables Queried
                     

DocumentEventEJB

Employ this use case to show where most of the problems with a document are occurring. Each time an issue occurs, an entry is created in the FlagEJB table. Count the entries grouped by the Name column of the FlagTypeEJB table, then display them grouped by name. Issue types include the following:

SQL Query Syntax

SELECT Count(*) as Total_Flag_Count, ft.name as Flag_Type FROM FLAGEJB f
INNER JOIN FLAGTYPEEJB ft ON f.TYPE_ID = ft.ID
GROUP BY ft.name;

                        Result Example
                     

TOTAL_FLAG_COUNT FLAG_TYPE
11 term candidates
5 style
1 spelling
6 terminology

Tables Queried

FlagEJB, FlagTypeEJB

Employ this use case to show which rules are being invoked most frequently. Count the entries grouped by the Description column of the FlagEJB table and display them in descending order.

SQL Query Syntax

SELECT Count(*) as Count_Per_Rule, r.DESCRIPTION FROM FLAGEJB f
INNER JOIN RULEFLAGEJB rf ON f.RULEFLAG_ID = rf.ID
INNER JOIN RULEEJB r ON r.ID = rf. RULE_ID
GROUP BY r.DESCRIPTION ORDER BY Count_Per_Rule DESC;

                        Result Example 
                     

COUNT_PER_RULE DESCRIPTION
3189 avoid_future_tense
1567 missing_space

Tables Queried

FlagEJB, RuleFlagEJB, RuleEJB

Employ this use case to show how many issues are being generated for a particular writing guide. Count the entries grouped by the Name column of the RuleSetEJB table.

SQL Query Syntax

SELECT count(*) as Flags_Per_Ruleset, rs.NAME FROM RULESETEJB rs
INNER JOIN CHECKCONFIGURATIONEJB cc ON cc.RULESET_ID = rs.ID
INNER JOIN DOCUMENTEVENTEJB de ON cc.ID = de.CHECKCONFIGURATION_ID
INNER JOIN FLAGEJB f ON f.DOCUMENTEVENT_ID = de.ID
GROUP BY rs.NAME

Result Example

FLAGS_PER_RULESET NAME
23 Demo

                        Tables Queried
                     

RuleSetEJB, CheckConfigurationEJB, DocumentEventEJB, FlagEJB

Information Related to Specific Issue Types

Certain types of issues have extra information associated with them. The following tables have additional information about reuse, Term Discovery, and "sentence too long" issues:

Join them with the associated FlagEJB by using their FLAG_ID field.

Employ this use case to show the monthly Acrolinx Scores. Average the entries in the CheckingScore column of the StatisticsEJB table. Then look in the DocumentEventEJB table to get the month and year of the successful checks. Next, output them grouped and ordered by month and year.

SQL Query Syntax

SELECT avg(s.CHECKINGSCORE) as Average_Checking_Score, MONTHYEARTABLE.month_and_year FROM STATISTICSEJB s
INNER JOIN (SELECT de.STATISTICS_ID, to_char(de.TIMESTARTED, 'MM/YYYY') as Month_and_Year FROM DOCUMENTEVENTEJB de WHERE de.CHECKCOMPLETEDSUCCESSFULLY=1) MONTHYEARTABLE ON MONTHYEARTABLE.STATISTICS_ID=s.ID GROUP BY MONTHYEARTABLE.month_and_year ORDER BY MONTHYEARTABLE.month_and_year;

Result Example

AVERAGE_CHECKING_SCORE MONTH_AND_YEAR
161 07/2009
1,251.4 08/2009

                        Tables Queried
                     

StatisticsEJB, DocumentEventEJB