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.