Below is a list of commonly use Database tables used for various reporting functions along with a relationship diagram. (Right click the diagram and select "Open in new tab" for a full size image)
Groups- Stores Area information | ||
Column | Data Type | Description |
GroupID | Bigint - PK | Unique ID for the site/Area. Links to multiple tables |
ParentGroupID | Bigint - FK | The GroupID this site/Area belongs to. |
GroupTypeID | Tinyint - FK | Where the group sits on the site tree. 1=Site/Area, 2=Customer/Group, 3=Sub-site, 4=Monitoring Station |
Title | varchar | The name of the Site/Area |
Armed | bit | Is the site Armed or Disarmed. 0=Disarmed, 1=Armed |
ToggleArmAt | DateTime | The date/time the site/area arms |
DisarmReason | Int - FK | The reason the site/area was disarmed. Links to table DisarmReasons |
DisabledAt | DateTime | The date/time the site/area was disabled on. Null for active sites. |
Address | varchar | The Address of the site/area |
Telephone1 | varchar | The first telephone number for the site/area |
Telephone2 | varchar | The second telephone number for the site/area |
Servers - Stores device connection details. | ||
Column | Data Type | Description |
ServerID | Bigint- PK | UniqueID for each device in the system. This is the S# that you see in the interface |
ServertypeID | Int - FK | Links to the Servertype table and tells you what device type the device is using. |
Groupid | Bigint - FK | The site/Area the device belongs to. Links to the Groups Table |
Title | varchar | Name of the Device. |
Host | varchar | IP address or DNS name of your device |
Port | Int | Port the device uses to connect. |
Username | varchar | Username for the device. |
Password | varchar | Password for the Device. |
Extra Value | varchar | Extra value details for the device, Varies per Servertype. |
LastAlarmReceivedAt | DateTime | The date/time this device recieved its last alarm. Useful for checking the device is sending alarms to Sureivew. |
Servertype - Stores device type parameters. | ||
Column | Data Type | Description |
Servertypeid | int - PK | Unique ID for each device type. Used to match in various tables. |
Title | varchar | Name of the device type. |
ExtraValueName | varchar | Describes the extra value the device uses. e.g SubStream = True/False |
CanEcare | bit | Is the device type able to use the Ecare service for polling the device. (Reverse Heartbeat) |
Devices - Stores peripheral device information. (Cameras / Audios / Relays ) | ||
Column | Data Type | Description |
DeviceID | Bigint - PK | Unique ID for the camera, audio or relay point. Links to various other tables. |
ServerID | Bigint - FK | Device the camera,audio or relay point belongs to. Links back to the servers Table. |
DeviceTypeID | Tinyint- FK | Tells the system what type of device this is. 1=Camera, 2=Audio, 3= Relay. |
Input1 | Int | The Input1 Value for the device. |
Input2 | Int | The Input2 Value for the device. |
Title | Varchar | Name of the Device. |
GroupID | Bigint | The site/Area the device belongs to. Links to the Groups Table. |
SettingsMeta | Varchar | String containing the device settings such as Quality, PTZ on/off, PTZ Speed etc. |
ExtraValue | Varchar | Extra information for the device. Some integrations store device GUID's here |
Users - Stores data relating to any users in the system. | ||
Column | Data Type | Description |
UserID | Bigint - PK | Unique ID for the user |
Username | varchar | If the user has login permission their username is here. |
Password | varchar | Password for the user. Encrypted using one way Salted Hash. |
PasswordSalt | varchar | String used to encrypt the users password. Cannot be used to unencrypt the password. |
FullName | varchar | Users Full Name. First name + Last name Fields in Interface |
GroupID | Bigint - FK | The site/Area this user belongs to. |
IsSiteStaff | bit | Is the user a member of Staff on this site/Area. |
IsMaintainer | bit | Is the user a maintainer/installer for this site/area. |
ReceiveIncidentEmails | bit | When events are closed with an "incident" outcome does this user receive an email.User must have permission to the site. |
DisabledAt | DateTime | Date / Time the user was disabled. |
LockedOutUntil | DateTime | Date / Time the user is locked out until if they have breeched the number of times they can enter their password. |
LastLoginAt | DateTime | The date / Time the user last logged in. |
IsSystemAdmin | bit | Does the user have access to the "System Settings" tab |
ReceiveArmDisarmEmails | bit | Does the user recieve an email each time an arm or disarm action takes place. User must have permission to site. |
MobileAppEnabled | bit | Does the user have permission to log in on the mobile app. |
User Access - Stores Permission data relating to users. All permissions relate to the site/area the user is configured at. | ||
Column | Data Type | Description |
UserID | Bigint - PK | Unique ID for the user, links back to the Users table. |
GroupID | Bigint - PK | The site/area this user belongs to. |
Inherit | bit | Does this users permission cascade to site below their location? |
CanProcessAlarms | bit | Is this user able to process alarms? |
CanDisarmSites | bit | Is this user able to disarm sites/areas? Up to 72 hours |
CanDisarmSitesExtended | bit | Is this user able to extend sites/areas longer than 72 hours? |
CanViewSiteSetup | bit | Can this user view the setup on site/area? |
CanEditDeviceSetup | bit | Can this user edit the configuration of a device? |
CanEditSiteSetup | bit | Can this user edit the details on a site/area? |
CanDeleteDisableSite | bit | Is this user able to delete a site/area that has been disabled? |
CanViewCameras | bit | Can this user view cameras? |
CanViewEvents | bit | Is this user allowed to review previous events? |
CanViewReports | bit | Is this user able to view reports? |
CanEditUserPermissions | bit | Can this user edit other user's permissions? |
CanEditSettings | bit | Can the user edit site permissions? (Setup>edit settings) |
CanProcessOlder | Int | Can this user process alarms that are older than x minutes old? |
CanProcessHigher | Int | Is this usert able to process alarm higher than x priority? |
CanProcessRoutingGroup | bigint - FK | Can this user process alarms from a sepcific routing group? (mutually exclusive with CanProcessAlarms) |
CanLaunchEdge | bit | Does this user have access to edge launch within the cameras tab? |
CanAccessVideowall | bit | Can this user lauch videowalls? |
CanEditVideowall | bit | Is this user able to create and edit videowalls? |
CanLinkAlarmToCameras | bit | Can this user link cameras to alarm within the alarm processing screen? (Heuristics) |
CanRaiseAlarmsFromVideowall | bit | Is this user able to raise an alarm directly from the videowall? |
CanUseFilters | bit | Can this user use alarm filtering? |
CanSaveFilters | bit | Can this user create and save alarm filters? |
AllowTakeControlRequest | bit | Can this user request to take control of an alarm in processing by another user? |
AllowTakeControlForce | bit | Can this user force control over another users alarm? |
Events - Information regarding events within Sureview. (Alarms, Patrols, setup events etc.) | ||
Column | Data Type | Description |
EventID | bigint - PK | Unique ID for the event. |
EventTypeID | smallint - FK | Event type, links to table EventType. 1=Alarm, 2=Patrol, 3=Site Check, 4=Setup, 6=On Test, 7=Arm/Disarm |
Created | DateTime | The Date /Time this event was created. For alarms the date/time it was received. |
Viewed | DateTime | Date/Time the event was first picked up. Time between this and created is the Response Time. |
Closed | DateTime | Date/ Time a user closed out the event. |
FilesDeleted | bit | Has housekeeping deleted the video files relating to this event? |
Preserve | bit | Is this event marked for preservaton? i.e files and database entries wont be deleted. |
EventOutcomeID | int - FK | The outcome selected to close the event. Links to table EventOutcomes |
GroupID | bigint - FK | The stie/area this event belongs to. |
ViewedUserID | bigint | The user ID of the user that first picked up the event. |
OutcomeNote | varchar | Any notes the user entered when closing the event. |
EventRecord - Details for the alarms and actions taken within a given event. | ||
Column | Data Type | Description |
EventRecordID | bigint - PK | UniqueID for the event record |
EventID | bigint - FK | The event this record belongs to. Links back to table Events |
Created | DateTime | Date/ Time the alarm or action took place. |
ObjectID | bigint | Links to various tables. Alarm link to responses / Camera viewed links to devices |
Details | varchar | Alarm title or action details. (camera viewed, audio started/stopped, relay triggered etc.) |
ServerID | bigint | ServerID of the device that triggered the alarm. |
ServerTypeEventNum | int | The Event Type of the alarm. Varies per integration. Links to table ServerTypeEvents |
EventOutcomeID | int | Event outcome if chosen for a specific alarm. |
EventUserID | UniqueIdentifier | The UserID of the user who viewed the alarm or performed the action. |
Responses - Details of alarms added to Sureview. | ||
Column | Data Type | Description |
ResponseID | bigint - PK | Unique ID for the Alarm. |
ServerID | bigint - PK / FK | Device the alarm belongs to |
Input1 | int - PK | The Input1 Value for the alarm |
Input2 | int - PK | The Input2 Value for the alarm |
ServerTypeEventNum | int - PK | The event typeId of the alarm. Links to table ServerTypeEvents |
GroupID | bigint - FK | The Site/Area the alarm belongs to. |
Title | varchar | The Alarm name. |
Armed | bit | Is the alarm armed or disarmed. 0=Disarmed, 1=Armed |
ToggleArmAt | DateTime | The Date/ Time the alarm us due to rearm. Null if the alarm is armed. |
DisarmReasonID | int - FK | The reason selected when disarming the alarm. Links to table DisarmReasons. |
Priority | int | The Priority of the alarm. |
LastOccured | DateTime | The Date/ Time this alarm was last received. |
ScriptID | int - FK | The ScriptID of the linked script. Links to table Scripts |
ExtraValue | varchar | Some Integrations store GUID's of alarms here. |
ResponseAction - Describes the actions taken upon reciept of an alarm. | ||
Column | Data Type | Description |
ResponseActionID | bigint - PK | UniqueID for the alarm action. |
ResponseActionTypeID | int - FK | What type of action it is. Links to table ResponseActionType. 1=Record Camera, 2=Activate Relay, 4=Send Email |
ResponseID | bigint - FK | Which alarm this action belongs to. Link to table Responses |
SettingsMeta | varchar | Details about the action. e.g how many frames to record, quaility, PTZ Preset etc. |
ObjectID | bigint | Varies depending on the action type. For record camera/ activate relay -deviceID, for send email -UserID |
Comments
0 comments
Please sign in to leave a comment.