What does this article applies to?
You need ShareKnowledge LMS 7.x or 8.x and MS SQL 2012 or later.
The view [Courses].[Activity] is part of ShareKnowledge LMS.
The rest of the views in this article for versions 7-8.0.1 are created by the script: LMS_Integration_Views.sql
The rest of the views in this article for versions 8.1 and later are created by the script: LMS_Integration_Views_V2.sql

What is timezone of datetime fields in DB?
UTC everywhere.

How do I get information about users?
Table [Users].[User] contains main information about all users. Table [Users].[UserData] contains extended information and might not contain rows for some users. Tables can be joined on field UserId.

How do I get information which regions users are added to?
Table [Users].[Region] contains information about all regions. Table [Users].[UserRegion] is many-to-many link between [Users].[Region] and [Users].[User].

How do I get list of all courses?
Use table [Courses].[Activity]

Expand to view all fields

General
ActivityId DB ID
ParentActivityId is null for simple courses and contains DB ID of compound course, if the current one is part of it.
Code Course ID
Name Course Name
Description Short Description
AvailabilityDate Course Availability
ExpirationDate Course Expiration
LiveStatusDate Live Status date
Type Course Type: 1 - e-learning, 2 - material, 3 - compound, 4 - training, 5 - learning task
Status Publishing Status: 1 - In Development, 2 - Live, 3 - Expired
CloseAccessForLearnersWithoutAssignments Close Access for Learners without Assignments: 0 - no, 1 - yes
Cost Cost
Credits CEU
IsFixed Block course: 0 - no, 1 - yes
Deleted This column for postponed deletion (1 - wait for deletion)
CreatorUserId DB ID of creator
Creation Creation date
LastChange Last Change date

Learner Responce
ConfirmationRequired Completion Confirmation Required: 0 - no, 1 - yes
SubmitConfirmation Confirmation Text
AttachmentLiability Attach File: 0 - allowed, 1 - prohibited, 2 - required
AllowedAttempts Number of Attempts Allowed: NULL - unlimited
PreferredAttemptOption Keep result of the: 1 - best attempt, 2 - last attempt
LearnerCommentLiability Add Comment: 0 - allowed, 1 - prohibited, 2 - required
SignatureType Signature Type: 0 - none, 1 - Checkbox, 2 - Password
SignatureComment Signature comment

Grading options
GradingOption Grade With: 1 - score, 2 - grade
RequireGrading Grade Manually: 0 - no, 1 - yes

Reporting options
DisplayReview Display “Review” button for this SCORM Course: 0 - no, 2 - yes
ShowProgress Show progress: 0 - no, 1 - yes
AllowViewUpdatedMaterial Allow Viewing the Updated Material: 0 - no, 1 - yes

Descriptions
ExtendedDescription Extended description
TaskDescription Task Text (for Learning Tasks)

Structure
ActivityOrder Activities Progression: 0 - Random, No Forced Order, 1 - Linear, In Order Of Course Layout
ShowTableOfContents 1 - Show the structure of the course for a learner before passing, 0 - don’t show the structure of the course for a learner before passing
ScoreWeight Grade Weight for activity in the Compound Course
ProgressWeight Progress Weight for activity in the Compound Course

Learning record submission
DisableSubmitRecordForLearner Do not allow learners to submit learning records: 0 - unchecked, 1 - checked
HideSubmitRecord Do not display the ‘Submit a Record’ link on course pages: 0 - unchecked, 1 - checked

Prerequisites
PrerequisiteOption Prerequisite option: 0 - All of them, 1 - Any one of them

How do I get Master Status Report?
Use view [Assignments].[CourseReport]

Expand to view all fields

User
User_UserId DB ID
User_CustomerUserId Employee ID
User_HireDate Hire Date
User_FirstName First Name
User_MiddleName Middle Name
User_LastName Last Name
User_UserName User Account
User_Email Email
User_Creation User Creation date
User_LastChange User Last Change date
User_IsBlocked 0 - user is active (this view doesn’t show blocked users)

User’s position
Department_DepartmentId Org Unit DB ID
Department_Name Org Unit
Department_Path path to Org Unit
Title_TitleId Title DB ID
Title_Name Title

Manager
Manager_UserId DB ID
Manager_CustomerUserId Employee ID
Manager_HireDate Hire Date
Manager_FirstName First Name
Manager_MiddleName Middle Name
Manager_LastName Last Name
Manager_UserName User Account
Manager_Email Email
Manager_Creation User Creation date
Manager_LastChange User Last Change date

Course
Activity_ActivityId DB ID
Activity_Name Course Name
Activity_Code Course ID
Activity_Type Course Type: 1 - e-learning, 2 - material, 3 - compound, 4 - training, 5 - learning task
Activity_SubmitConfirmation Confirmation text
Activity_SignatureType Signature Type: 0 - none, 1 - Checkbox, 2 - Password
Activity_Status Publishing Status: 1 - In Development, 2 - Live, 3 - Expired
Activity_ExpirationDate Course Catalog Expiration date
Activity_Cost Cost
Activity_Credits CEU
Activity_CloseAccessForLearnersWithoutAssignments 0 - don’t close, 1 - close

Enrollment options
Access_AccessId DB ID
Access_Requirement Enrollment Type: 1 - Required, 2 - Recommended, 3 - Open
Access_AccessOption Approval: 1 - Not Required, 2 - Requires Instructor/Course Owner Approval, 3 - Requires Manager Approval, 4 - Requires Manager or Instructor Approval, 5 - Requires Manager and Instructor Approval
Access_Assigned Assigned date

Results
CourseRecord_CourseRecordId Course record (user’s result) DB ID
CourseRecord_State Status: NULL - Not started (Not Enrolled, Request Enrollment, Enrolled, Request Enrollment), 3104 - Finished (Result Expired, Passed, Failed), 1280 - Canceled, 1152 - Rejected, 1088 - Missed, 1536 - Canceled, 4097 - Pending, 4098 - In Waitlist, 4 - Approved, 8 - In Progress, 16 - In Grading
CourseRecord_Progress Progress: 0 - 0 %, 1 - 100% (for SCORM courses - this is value from SCORM)
CourseRecord_Score Score
CourseRecord_Expired Result Expiration Date
CourseRecord_Started Start Date (“Started” in tables in LMS)
CourseRecord_Finished Finish date (“Completed” in tables in LMS)
CourseRecord_Credits Applied CEU
CourseRecord_Cost Applied Cost
CourseRecord_TotalTimeTicks Total Time in ticks (CourseRecord_TotalTimeText contains text value)
CourseRecord_Active Last: 1 - last, 0 - not last
CourseRecord_Origin Origin of Course Record: NULL - not started, 1 - internal, 2 - imported as course record, 3 - recorded after course record request approved
CourseRecord_IsSuccessful 1 - Passed, 0 - others
CourseRecord_GradeId Grade DB ID
CourseRecord_Grade Grade
Due Due Date
Visible Visibility date
IsExpectation 0 - Started, 1 - Not Started

Text strings for numeric fields
Activity_TypeText Course Type
Activity_SignatureTypeText Signature Type
Activity_StatusText Publishing Status
Access_RequirementText Enrollment Type
Access_AccessOptionText Approval
CourseRecord_StateText Status
CourseRecord_ProgressText UI Progress
CourseRecord_ScoreText UI Score
CourseRecord_TotalTimeText UI Total time

How do I get learner’s responses in quizes?
LMS stores data that SCORM course saved in DB. You can find all interactions for each row in view [Assignments].[CourseReport] . Usually, a course creates one interaction for each question, but the behaviour is fully controlled by course (not LMS).

Expand to view SQL

SELECT cr.*,
ii.[Id] as [InteractionItem_Id],
ii.[Description] as [InteractionItem_Descripion],
ii.[LearnerResponseString] as [InteractionItem_ResponseString],
ii.[LearnerResponseBool] as [InteractionItem_ResponseBool],
ii.[LearnerResponseNumeric] as [InteractionItem_ResponseNumeric],
ii.[ResultState] as [InteractionItem_State],
irs.[Name] as [InteractionItem_StateName],
ii.[InteractionType] as [InteractionItem_Type],
it.[Name] as [InteractionItem_TypeName]
FROM [Assignments].[CourseReport] cr
LEFT JOIN [Assignments].[CourseAssignment] ca on ca.CourseRecordId = cr.CourseRecord_CourseRecordId
LEFT JOIN [Assignments].[AssignmentAggregation] aa on ca.CourseAssignmentId = aa.CourseAssignmentId
LEFT JOIN Courses.Activity childact on childact.ActivityId = aa.ActivityId
LEFT JOIN [Assignments].[Assignment] a on a.AssignmentId = aa.PreferredAssignmentId
LEFT JOIN [Assignments].[ScormAssignment] sa on sa.AssignmentId = a.AssignmentId
LEFT JOIN [LearningStore].[AttemptItem] ai on ai.Id = sa.AttemptId
LEFT JOIN [LearningStore].[AttemptStatus] ats on ats.Id = ai.AttemptStatus
LEFT JOIN [LearningStore].[ActivityAttemptItem] aai on aai.AttemptId = ai.Id
LEFT JOIN [LearningStore].[InteractionItem] ii on ii.ActivityAttemptId = aai.Id
LEFT JOIN [LearningStore].[InteractionResultState] irs on irs.Id = ii.ResultState
LEFT JOIN [LearningStore].[InteractionType] it on it.Id = ii.InteractionType

How do I get scheduled events?
Use view [Assignments].[EventList]

Expand to view all fields

Training
Training_Id Training DB ID
Training_Code Training ID
Training_Name Training Name

Event
Event_Id Event DB ID
Event_IsOpened Registration status: 1 - Open, 0 - Closed
Event_Format Event Format
Event_Capacity Capacity
Event_OccupiedSeats The number of occupied seats
Event_ApprovalRequired 0 - Auto-Approve requests, 1 - others
Event_IsPreliminary 1 - Special event for pre-enrollment, dates to be finalized, 0 - others
Event_IsForTransfer 1 - Special event for credit transfer from already passed trainings, 0 - others
Event_Location Location

Region
Region_RegionId Region DB ID
Region_CustomerRegionId Region ID
Region_Code Notation
Region_Name Name

First Event Date
FirstEventDate_EventDateId Event date DB ID
FirstEventDate_StartDate Start date and time
FirstEventDate_EndDate End date and time
FirstEventDate_Comment Room
FirstEventDate_Creation Event creation date
FirstEventDate_CreatorUserId Creator DB ID

How do I get all Event Days for Event?
Use table [Assignments].[EventDate]

Expand to view all fields
EventDateId Event Date DB ID
EventId Event DB ID ([Assignments].[EventList].Event_Id)
StartDate Start date and time
EndDate End date and time
Comment Room
Creation Creation date
LastChange Last Change date
CreatorUserId DB ID

How do I link trainings and courses?
First of all do not forget, that a single training can be published several times. So there might be 2+ courses for the same training or training might not be published at all. These courses are independent.

Expand to view SQL

SELECT 
	Act.ActivityId Activity_Id -- Activity DB ID
	,Act.Code Activity_Code 
	,Act.Name Activity_Name
	,tlo.LearningObjectId Training_Id -- Training DB ID
	,tlo.Code Training_Code
	,tlo.Name Training_Name
FROM [Assignments].[TrainingLearningObject] tlo
INNER JOIN [Courses].[Activity] Act on Act.LearningObjectId = tlo.LearningObjectId

How do I get who has currently enrolled in events?
Use view [Assignments].[EventEnrollment]

Expand to view all fields
Activity_Id Course DB ID. It might be activity in compound course.
User_UserId User DB ID
User_CustomerUserId Employee ID
User_HireDate Hire Date
User_FirstName First Name
User_MiddleName Middle Name
User_LastName Last Name
User_UserName User Account
User_Email Email
User_Creation User Creation date
User_LastChange User Last Change date
User_IsBlocked 0 - user is active (this view doesn’t show blocked users)
Event_EventId Event DB ID ([Assignments].[EventList].Event_Id)
CourseRecord_Id Course Recordd DB ID (references [Assignments].[CourseReport].CourseRecord_CourseRecordId)
Assignment_Status Assignment Status (Not Started, In Application, Approved, Finish, Cancelled, Missed, Rejected, Interrupted)

How do I add information about events to [Assignments].[CourseReport]?
If training is published several times, a learner can enroll for an event in one course, pass it, and this information will not appear in another course.

Expand to view SQL

SELECT *
FROM [Assignments].[CourseReport] cr
LEFT JOIN [Assignments].[EventEnrollment] er ON er.CourseRecord_Id = cr.CourseRecord_CourseRecordId -- joining enrollments in events
LEFT JOIN [Assignments].[EventList] el ON el.Event_Id = er.Event_EventId  -- joining event information
WHERE cr.Activity_Type = 4 -- only trainings

How do I get organization structure?
Table [Users].[Department] contains all departments in LMS.
Table [Users].Title] contains all titles in LMS.
Table [Users].[DepartmentRelation] contains all relations between departments. If you need to find only direct parent-child pair, then you need only rows with “Level=1”.
Table [Users].[UserTitleInDepartment] contains assigned titles. It is a standard many-to-many table, it links users, departmetns and titles. Primary positions ID is stored in table [Users].[User].

Expand to view all fields of the table [Users].[Department]
DepartmentId Department DB ID
Name Department Name
Description Department Description
Path Department Full Path
IsExternal 1 - department is external (created via BIT tool), 0 - department is internal (created via UI)
CustomerDepartmentId Department ID for BIT tool

Expand to view all fields of the table [Users].[Title]
TitleId Title DB ID
Name Title Name
Description Title Description
IsExternal 1 - title is external (created via BIT tool), 0 - title is internal (created via UI)
CustomerPositionId Title ID for BIT tool

Expand to view all fields of the table [Users].[DepartmentRelation]
DepartmentRelationId Department Relation DB ID
DepartmentId Department DB ID
ParentDepartmentId Department DB ID
Level level of relationship (1 - direct parent-child pair)

Expand to view all fields of the table [Users].[UserTitleInDepartment]
UserTitleInDepartmentId User Title in Department DB ID
UserId User DB ID
DepartmentId Department DB ID
TitleId Title DB ID
IsExternal 1 - User Title in Department is external (created via BIT tool), 0 - User Title in Department is internal (created via UI)
Appointed Appointed Date