The ability to report on your indexing users output as well as processing times and volumes can be crucial to keeping track of your system. Below is some information on where to find this information in SQL as well as where to find some out of the box reports we provide with a base installation of the product.

Considerations

There are a few things to know before going into getting reporting data out of SQL:

• The default reporting database name is KL_Reporting and is installed by default alongside the rest of your databases.
• This data is pushed to this table and does not effect any components of Capture Server Pro if altered or queried against.
• If you're comfortable in SQL, you can view the SQL queries in these by right clicking a stored procedure and selecting Modify to make your own reports.

Description

The KL_Reporting database will contain 3 tables of information that can be used to generate reports: dbo.BatchHistory, dbo.EventCodes, and dbo.JobHistory.

• BatchHistory will contain historical data about your batches being processed. This includes things like statuses, times a batch started or completed an activity, as well as when they were indexed and the worker that performed the processing.
• EventCodes will contain the relevant code numbers to correlate to the EventCode column in the BatchHistory or JobHistory tables.
• Job History will contain historical data about job processing. This includes job names, start and complete times as well as the worker that ran the job.

Our out of the box reports can be found by performing the following steps:

1. Open SQL Management Studio
2. Expand the KL_Reporting database to show it's sub-folders
3. Expand the Programmability folder and then the Stored Procedures folder
4. From there you will find the following stored procedures to use for reporting:
   • BatchesAverageHoldTime - The amount of time a batch was in a hold step
   • BatchesAverageIndexWaitTime - The amount of time something was on hold before being picked up for index
   • BatchesAverageManualTime - The amount of time a user spent actively indexing a batch
   • BatchesErrored - Number of batches errored in a timeframe
   • BatchesIndexed - Number of batches indexed in a timeframe
   • BatchesProcessed - Number of batches processed to completion in a timeframe
   • DocumentsErrored - Number of documents errored in a timeframe
   • DocumentsIndexed - Number of documents indexed in a timeframe
   • DocumentsProcessed - Number of documents processed to completion in a timeframe
   • InsertBatchHistory - Used to get the history of a single batch
   • InsertJobHistory - Used to get the history of a single job
   • JobsErrored - Number of jobs errored in a timeframe
   • JobsProcessed - Number of jobs completed in a timeframe
   • PagesProcessed - Number of pages processed in a timeframe

Example

Here are a few examples of running our out of the box reports. You can use the stored procedures referenced above to write your own as well.

Processing

In this example of using an out of the box stored procedure, we will get the number of batches processed between August 1st 2018 and August 31st, 2018.

1. First, Open SQL Management Studio
2. Expand the KL_Reporting database to show it's sub-folders
3. Expand the Programmability folder and then the Stored Procedures folder
4. Right click BatchesProcessed and select Execute Procedure

In this window, you are presented with a few pieces of criteria that we can use to narrow our results.

• ActivityProcessID - The internal ID number of a process
• ActivityProcessName - The friendly name of a process
• ActivityID - The internal ID number of an activity
• ActivityName - The friendly name of an activity
• FromDate - The beginning of your date range
• ToDate - The end of your date range

If I wanted to find all batches in the date range defined above, I would want to fill out FromDate with '08-01-2018' or '08/01/2018' and ToDate with '08-31-2018' or '08/31/2018'.

If I then wanted to narrow this down to batches flowing through a specific process I could set ActivityProcessName to the name of a process like 'Invoice Processing'.

NOTE: Values being searched should be wrapped in apostrophes per SQL Query syntax. For example: August 31st would be '08/31/2018' not just 08/31/2018.

Indexing

In this example of using an out of the box stored procedure, we will get the number of documents my users have indexed between August 1st, 2018 and August 31st, 2018.

1. First, Open SQL Management Studio
2. Expand the KL_Reporting database to show it's sub-folders
3. Expand the Programmability folder and then the Stored Procedures folder
4. Right click DocumentsIndexed and select Execute Procedure

In this window, you are presented with a few pieces of criteria that we can use to narrow our results.

• IndexUserID - The username of your index user
• FromDate - The beginning of your date range
• ToDate - The end of your date range

If I wanted to find all documents indexed by users in the date range defined above, I would want to fill out FromDate with '08-01-2018' or '08/01/2018' and ToDate with '08-31-2018' or '08/31/2018'. and select Pass Null Value on the IndexUserId field.

If I then wanted to narrow this down to a specific users batches, I could then fill out IndexUserID with a user such as dan.boyher@knowledgelake that is present in my permissions page that my users log in using.

NOTE: Values being searched should be wrapped in apostrophes per SQL Query syntax. For example: August 31st would be '08/31/2018' not just 08/31/2018.

Jobs

In this example of using an out of the box stored procedure, we will get the number of jobs that went into error between August 1st, 2018 and August 31st, 2018.

1. First, Open SQL Management Studio
2. Expand the KL_Reporting database to show it's sub-folders
3. Expand the Programmability folder and then the Stored Procedures folder
4. Right click JobsErrored and select Execute Procedure

In this window, you are presented with a few pieces of criteria that we can use to narrow our results.

• JobTypeCode - Internal id for a job type
• JobTypeString - The type of job. For example: 'Import' or 'Maintenance'
• JobScriptName - The friendly name of the job. For example: 'Clean Up Cache'
• ScheduledJobName - The internal name of the job
• FromDate - The beginning of your date range
• ToDate - The end of your date range

If I wanted to find all errored jobs in the date range defined above, I would want to fill out FromDate with '08-01-2018' or '08/01/2018' and ToDate with '08-31-2018' or '08/31/2018'. and select Pass Null Value on the JobTypeString, JobScriptName, and ScheduledJobName fields.

If I then wanted to narrow this down to a specific job type of Import Jobs, I could then fill out JobTypeString with a type such as 'Import'.

NOTE: Values being searched should be wrapped in apostrophes per SQL Query syntax. For example: August 31st would be '08/31/2018' not just 08/31/2018.