A Simple API to the Database

Developer Tools

It is possible to retrieve data directly from the database for use in other applications or processes. This section provides information on those tools.

The Spector 360 Database provides a stored procedure that allows you to access data directly from the database to use for other purposes. Only the System Administrator login can access this stored procedure. Remember to take into account any permissions (i.e., "read" access to Attachment and Snapshot files) necessary for accessing the data.

Definition

The ReturnRawDataSet stored procedure retrieves a list of records (with all data fields) for one event type within a time range. Seven possible parameters allow you to specify the data you want.

For example, the procedure could retrieve all Email events from January 1-10 for user Bill Smith (on the CHICAGO domain) recorded on any computer. The procedure would be called as follows:

NOTE: A call to the stored procedure requires a value for EventType_ID, StartDate and EndDate. Other parameters are optional. If you use the parameter names in the call, the order and inclusion of optional parameters will not be required. If you do NOT use the parameter name (supply just the value) all parameter values must appear in the order shown above.

• EventType_ID:
  Integer, REQUIRED argument
  The first parameter (integer) specifies the type of records to retrieve (Programs, Web Sites Visited, Email, Keywords, etc.). Each Event Type ID and the fields it retrieves are outlined in the following tables. Each call must use one of the following integer ID values to retrieve a specific event type.   

• TransactionID:
  integer, OPTIONAL argument
  Default = 0

Identifies a transaction (record) to use as a starting point. Each record inserted in the database is associated with a Transaction ID. A Transaction ID value of 0 (zero) begins a count of Transaction IDs retrieved. Use the Transaction ID to control the row pointer for successive calls.  For example, if 5889 records are retrieved in the first call, to get the next segment of data, the subsequent call would specify: ,@int_TransactionID = 5889

If you use the MaxRowCount argument, the number of rows (records) you specify will be retrieved as a maximum. If you do NOT use the MaxRowCount argument, a maximum of 50,000 records will be retrieved. To retrieve the next set of records beyond the maximum, you need to use: ,@int_TransactionID = 50000  

• StartDate / EndDate:
  datetime, REQUIRED arguments

The required StartDate and EndDate parameters specify the beginning and end of the date and time range for the data you want to retrieve. Data records are stored by date and time and usually are retrieved sequentially from earliest to latest within the time range (see the note above).  Only records within the date range will be retrieved.

Be sure to use the format: yyyy/mm/dd  hh:mm:ss
No milliseconds are used.

For example, the following call retrieves all Program records for all users from midnight, January 1, to a second before midnight, January 5 (up to the default limit of 50,000 records).

EXEC SPCTR_ADMIN.dbo.usp_Utility_ReturnRawDataSet @int_EventType_ID = 1
,@dte_StartDate = '2008/01/01 00:00:00'
,@dte_EndDate = '2008/01/05 23:59:59'

• MaxRowCountPerRequest
  integer, OPTIONAL argument
  Default = 0, or 50000 rows

Use the Maximum Row Count parameter to specify the maximum number of rows to return for each call to the procedure. The number of rows actually returned may be fewer, but will not exceed this maximum. This parameter allows you to expand the maximum if you need to retrieve more than 50k records in a single call, or reduce the maximum if your processing resources are limited. If you omit this parameter or use the default 0 value, the maximum rows retrieved per call will be 50,000.   

• FullLoginName:
  varchar(128), OPTIONAL argument
  Default = empty string, or all users

Use the Full Login Name string parameter (maximum 128 characters) to retrieve data recorded for an individual user. You can omit this parameter (or pass an empty string) to retrieve event records for ALL users.  Use the complete Domain\Username specification, as listed in the Dashboard Management Users list, or as returned by this procedure without this parameter.  

• FullComputerName:
  varchar(128), OPTIONAL argument
  Default = empty string, or all computers

Use the Computer Name string parameter (maximum 128 characters) to retrieve data recorded on a single computer. You can omit this parameter (or pass an empty string) to retrieve event records for ALL computers.  Use the complete computer name specification, as listed in the Dashboard Management Computers list, or as returned by this procedure when this parameter is not used.  

1 - Program Events

2 - Web Site (URL) Events

3 - Screen Snapshots

Notes about Snapshot Events:

• UNCPath returns a string value containing the (Universal Naming Convention) path where you can access the snapshot files. The snapshot files themselves are not returned.

• Filename is the name of the snapshot data file.

• EncryptKeyType returns the encryption type for snapshot files:
  0 = non-encrypted format
  1 = Internal encryption
  2 = 3DES encryption
  You can use the Snapshot converter (ExportFile) to decrypt the snapshots for viewing outside of a SpectorSoft product.

• EncryptKeyGUID returns the key that you will need to decrypt the files if 3DES encryption was used. Encryption type is set in the Control Center Properties for the Data Vault.

4 - Email Events

For email attachment information, make a call to Event Type 104, as described below. The first call below to Event Type 4 returns the first 50,000 (maximum) email events for the given date range. The second call returns all email attachments matching the email events returned in the first call.

104 - Email Attachments

For email events returned (event type 4) that have file attachments, a call made to Event Type 104 returns the following fields.

Notes about Email attachments:

• UNCPath returns a string value containing the (Universal Naming Convention) path where you can access email attachment files. The email attachment files themselves are not returned.

• EncryptKeyType returns the encryption type for email attachment files:
  0 = non-encrypted format
  2 = 3DES encryption
  If 3DES encryption was used, the EncryptKeyGUID field returns the key that you will need to decrypt the files in this standard format. Encryption is set in the Control Center Properties for the Data Vault.

5 - Keystroke Event

Notes about Keystrokes:

• KeystrokeCombined returns all text representation of Unicode from all keys and key combinations for the event.  

7 - Chat/IM Event

8 - Files Transferred (P2P)

10 - Keyword Alert

11 - Network Event (Port)

12 - Document Tracking Event

13 - User Activity

14 - Online Search