How it works
Integration between IREX and Apache Superset enables Apache Superset to send user-defined queries to the IREX database to extract information of interest and present it as charts.
The IREX database is a ClickHouse-type database. Apache Superset exposes its tables as Datasets. Each dataset has Columns: the fields available in charts and SQL queries. Under Explanation in the tables below, wording follows IREX product terms.
Apache Superset can query any of these columns. For example, a condition of "channel_id={value}" on kx_events returns events detected on the camera with ID={value}. More advanced queries can be created with standard SQL syntax in the SQL Editor.
Columns
The tables below list Columns for each Dataset available when building charts.
The database provides the following Datasets:
- kx_events: events recorded by video analytics modules.
- channel: current camera state.
- channel_history: camera change history.
- bioterminal: bioterminals and their settings.
- alarm_monitor: alarm monitors and their settings.
- face: persons.
- plate: vehicles.
- permission: permission assignments to user groups.
- users: system users.
- license: licenses.
kx_events
|
Column |
Explanation |
|---|---|
|
start_time |
Event start timestamp for durable events |
|
end_time |
Event end timestamp for durable events |
|
id |
Event ID |
|
channel_id |
Camera ID |
|
channel_type |
Sensor type (bioterminal, external device, etc.) |
|
expiration |
Event retention end date |
|
resource_groups |
Resource groups |
|
tag_ids |
Tag IDs |
|
module |
Video analytics module name |
|
topic |
Event type |
|
level |
Event priority |
|
source |
Rule name |
|
source_id |
Rule ID for rule-based events |
|
list_ids |
Person/vehicle list IDs for recognized faces/license plates |
|
matched_ids |
Recognized face/license plate IDs |
|
first_names |
Recognized person's first name |
|
last_names |
Recognized person's last name |
|
matched_similarities |
Similarity for recognized faces |
|
car_plate_number |
License plate number |
|
car_color |
Vehicle body color |
|
car_brand |
Car make |
|
car_speed |
Measured speed of the vehicle |
|
tripwire_direction |
Vehicle motion direction across a directed tripwire for line crossing events |
|
light_state |
Traffic light signal |
|
face_age |
Estimated person's age |
|
face_gender |
Estimated person's gender |
|
face_race |
Demographic group attribution |
|
face_glasses |
Glasses or no glasses |
|
face_beard |
Beard or no beard |
|
face_hat |
Hat or no hat |
|
face_mask |
Mask or no mask |
|
face_spoof_score |
Estimated spoofing probability |
|
object_id |
Object ID |
|
object_type |
Object type (human, vehicle, bag, etc.) |
|
minimum_time_absence |
Permissible off-duty time limit for "Off-duty" events |
|
crowd_size |
Number of people in a crowd |
|
external_event_direction |
Passage direction in an event from an external device |
|
external_code |
Event code from an external device |
|
contact_id |
External device contact ID |
|
contact_name |
External device contact name |
|
contact_server_id |
External device contact server ID |
|
external_device_type |
External device type |
|
external_device_id |
External device ID |
channel
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
channel_id |
Camera ID |
|
name |
Camera name |
|
status |
Camera status |
|
enabled |
Camera enabled flag |
|
deleted |
Deleted flag |
|
changed_at |
Last change time of the record |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
|
channel_type |
Video source type |
|
host |
Camera server host / address |
|
quality_name |
Quality profile name |
|
pixels_resolution |
Resolution in pixels |
|
ptz_enabled |
PTZ support flag |
|
ptz_type |
PTZ type |
|
latitude |
Latitude |
|
longitude |
Longitude |
|
address_string |
Full address as a string |
|
address_country |
Country |
|
address_region |
Region |
|
address_county |
County / district |
|
address_city |
City |
|
address_district |
City district |
|
address_street |
Street |
|
address_place_info |
Additional place information |
|
place_labels |
Location labels |
|
entity_group_ids |
Resource group IDs |
|
plan_count |
Number of linked plans |
|
plan_ids |
Plan IDs |
|
plan_entity_ids |
Plan entity IDs |
|
plan_names |
Plan names |
|
module_count |
Number of analytics modules |
|
module_names |
Analytics module names |
|
enabled_module_names |
Enabled analytics module names |
|
detector_count |
Number of detectors |
|
detector_tags |
Detector tags |
|
detector_rule_types |
Detector rule types |
|
stream_count |
Number of video streams |
|
stream_ids |
Stream IDs |
|
stream_sources |
Stream sources (URL/RTSP) |
|
edge_names |
Linked edge server names |
channel_history
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
channel_id |
Camera ID |
|
name |
Camera name |
|
status |
Camera status at the time of the event |
|
enabled |
Camera enabled flag |
|
deleted |
Deleted flag |
|
changed_at |
Change time |
|
event_time |
Change time |
|
lifecycle_event |
Lifecycle event type (create, initial provisioning, update, and delete) |
|
source_operation |
Source operation type (create, initial provisioning, update, and delete) |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
|
channel_type |
Video source type |
|
host |
Camera server host / address |
|
quality_name |
Quality profile name |
|
pixels_resolution |
Resolution in pixels |
|
ptz_enabled |
PTZ support flag |
|
ptz_type |
PTZ type |
|
latitude |
Latitude |
|
longitude |
Longitude |
|
address_string |
Full address as a string |
|
address_country |
Country |
|
address_region |
Region |
|
address_county |
County / district |
|
address_city |
City |
|
address_district |
City district |
|
address_street |
Street |
|
address_place_info |
Additional place information |
|
place_labels |
Location labels |
|
entity_group_ids |
Resource group IDs |
|
plan_count |
Number of linked plans |
|
plan_ids |
Plan IDs |
|
plan_entity_ids |
Plan entity IDs |
|
plan_names |
Plan names |
|
module_count |
Number of analytics modules |
|
module_names |
Analytics module names |
|
enabled_module_names |
Enabled analytics module names |
|
detector_count |
Number of detectors |
|
detector_tags |
Detector tags |
|
detector_rule_types |
Detector rule types |
|
stream_count |
Number of video streams |
|
stream_ids |
Stream IDs |
|
stream_sources |
Stream sources (URL/RTSP) |
|
edge_names |
Linked edge server names |
bioterminal
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
bioterminal_id |
Bioterminal ID |
|
name |
Bioterminal name |
|
status |
Bioterminal status |
|
enabled |
Enabled flag |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
|
channel_id |
Linked camera ID |
|
ip |
Bioterminal IP address |
|
port |
Port |
|
event_sync |
Event synchronization flag |
|
event_level |
Event level |
|
event_expiration_days |
Event retention period, days |
|
entity_group_ids |
Resource group IDs |
|
edge_names |
Linked edge server names |
|
address_string |
Full address as a string |
|
address_country |
Country |
|
address_region |
Region |
|
address_county |
County / district |
|
address_city |
City |
|
address_district |
City district |
|
address_street |
Street |
|
address_place_info |
Additional place information |
alarm_monitor
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
alarm_monitor_id |
Alarm monitor ID |
|
name |
Alarm monitor name |
|
status |
Status |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
created_at |
Creation time |
|
updated_at |
Update time |
|
blocked_at |
Block time |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
|
filter_json |
Event filter settings (JSON) |
|
max_events_count |
Maximum number of events |
|
scheduler_active |
Active schedule flag |
|
module_names |
Analytics module names |
|
detector_names |
Detector names |
|
entity_group_ids |
Resource group IDs |
|
user_group_ids |
User group IDs |
|
settings_user_count |
Number of users with alarm monitor settings |
|
email_enabled_user_count |
Number of users with email notifications |
|
push_enabled_user_count |
Number of users with push notifications |
face
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
person_id |
Person ID |
|
full_name |
Full name |
|
first_name |
First name |
|
last_name |
Last name |
|
middle_name |
Middle name |
|
gender |
Gender |
|
birthday |
Date of birth |
|
comment |
Comment |
|
status |
Record status |
|
enabled |
Enabled flag |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
added_at |
Time added |
|
recognition_until |
Recognition valid until |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
|
facelist_id |
Face list ID |
|
facelist_name |
Face list name |
|
facelist_event_level |
Recognition priority for the list |
|
facelist_deleted |
Face list deleted flag |
|
entity_group_ids |
Resource group IDs |
plate
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
plate_id |
Vehicle ID |
|
number |
License plate number |
|
plate_label |
Plate label / tag |
|
color |
Color |
|
manufacturer |
Make |
|
model |
Model |
|
type_of_vehicle |
Vehicle type |
|
year_of_issue |
Year of manufacture |
|
state |
State / region |
|
owner |
Owner |
|
owner_full_name |
Owner full name |
|
owner_first_name |
Owner first name |
|
owner_last_name |
Owner last name |
|
owner_middle_name |
Owner middle name |
|
linked_person_id |
Linked person ID |
|
comment |
Comment |
|
status |
Record status |
|
enabled |
Enabled flag |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
recognition_until |
Recognition valid until |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
|
platelist_id |
Plate list ID |
|
platelist_name |
Plate list name |
|
platelist_event_level |
Recognition priority for the list |
|
platelist_deleted |
Plate list deleted flag |
|
entity_group_ids |
Resource group IDs |
permission
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
permission_assignment_id |
Permission assignment ID |
|
permission |
Permission code / value |
|
resource_type |
Resource type |
|
target_object_id |
Access object ID |
|
target_object_name |
Access object name |
|
user_group_id |
User group ID |
|
user_group_name |
User group name |
|
user_group_parent_id |
Parent group ID |
|
user_group_is_root |
Root group flag |
|
member_count |
Number of group members |
|
member_user_ids |
Member user IDs |
|
member_names |
Member user names |
|
role_names |
Role names |
|
role_permissions |
Role permission IDs |
|
entity_group_id |
Resource group ID |
|
entity_group_label |
Resource group name |
|
country |
Country |
|
region |
Region |
|
county |
County / district |
|
city |
City |
|
district |
City district |
|
street |
Street |
|
place_info |
Additional place information |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
users
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
user_id |
User ID |
|
full_name |
Full name |
|
first_name |
First name |
|
last_name |
Last name |
|
middle_name |
Middle name |
|
|
|
|
phone_number |
Phone |
|
position_in_company |
Job title |
|
role |
Role |
|
locale |
UI language |
|
status |
Account status |
|
service_account |
Service account flag |
|
external_id |
External user ID (when integrating with external systems) |
|
created_time |
Creation time |
|
activate_from |
Active from |
|
deactivate_from |
Deactivated from |
|
enable_email_notifications |
Email notifications enabled |
|
email_confirmed |
Email confirmed |
|
alarm_monitor_notifications_enabled |
Alarm monitor notifications enabled |
|
alarm_monitor_notifications_sound_enabled |
Alarm monitor notification sound enabled |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
revision |
Record revision number |
|
source_table |
Source table for the data |
license
|
Column |
Explanation |
|---|---|
|
id |
Row ID in the database table |
|
entity_id |
Entity ID in the system |
|
license_id |
License ID |
|
name |
Record name |
|
license_name |
License name |
|
status |
License status |
|
company_name |
Company name |
|
company_uuid |
Company UUID |
|
license_email |
License email |
|
license_phone |
License phone |
|
contract_name |
Contract name |
|
contract_number |
Contract number |
|
contract_date |
Contract date |
|
start_date |
Start date |
|
end_date |
End date |
|
end_date_support |
Support end date |
|
number_of_cameras |
Camera quota under the license |
|
cameras_created |
Number of cameras created |
|
auto_activation |
Auto-activation flag |
|
deleted |
Deleted flag |
|
changed_at |
Last change time |
|
revision |
Record revision number |
|
source_table |
Source table for the data |