https://sqlitebrowser.org/
The Canary MQTT collector utilizes a SQLite database for configuration settings. It is located at c:\programdata\canary labs\logger\storeandforward\mqttcollector\mqttcollector.sqlite (The base path can be modified by the system administrator and may vary from the default). The collector will automatically create the database configuration file upon first run.
- To configure the collector, open the database file with DB browser.
ServerGroups
- Click on the Browse Data tab and select the ServerGroups table in the drop down list. New in v20.1 the collector now supports a group of mqtt brokers for high availability installations. Each group shares subscription and tag configuration across the list of servers configured.

- Click on New Record
Enter information for each of the fields:
ID: Auto incremented field (leave default)
Name: Name of the group. Displayed in Canary Admin. Must be non-null
Enabled: 0 or 1. Set to 0. Controls whether the group is enabled. This can be toggled from the Canary Admin
MQTTClientId: This identifies the Canary collector within the MQTT broker and must be unique within the broker (default = Canary)
IsPrimaryApplication: 0 or 1: Set to 1 to identify the Canary collector as the primary host to the MQTT devices. This is used to enable buffering of historical data at the device level if supported.
SendBirthRequests: 0 or 1: If set to 1, the collector will attempt to send node and device rebirth commands when it starts and wait for those birth messages from the device before logging. If set to 0, the collector will not send node and device rebirth commands when starting and will simply log any tag values published to the broker. If the nodes or devices publishing do not support rebirth commands, set this to false. Note: if the devices do not support rebirth commands, the collector may not discover all of the tags on the node or device until they rebirth at some point in the future.
- Click File>Write Changes
Servers
- Click on the Browse Data tab and select the Servers table in the drop down list

- Click on>New Record
Enter information for each of the fields:
ID: Auto incremented field (leave default)
ServerGroupID: ID of the ServerGroups table record you want to associate this server to
Host (MQTT Server): IP address, machine name, or domain name (ex. Mqtttest.canarylabs.com or 192.168.0.1)
Port (MQTT Server Port): Port number the mqtt server is listening on (standard ports are 1883 unsecure and 8883 for secure)
Secure (Whether or not security is being used): 1 = true, 0 = false
SSL_Protocol (type of security being used): 0 = none, 1 = SSLv3, 2 = TLSv1.0, 3 = TLSv1.1, 4 = TLSv1.2
Username: Username to use when connecting (if connecting anonymously put in a space)
Password: Password to use when connecting (if connecting anonymously put in a space)
MQTTServerReconnectInterval: Leave default (00:01:00) = 1 minute
IgnoreSSLErrors: 0 or 1. Set to 1 if using security and a self-signed certificate on the mqtt server
CleanSession: 0 or 1: Set to 1 (default)
SessionKeepAlive: 0-65535 seconds: Leave set at 0.
- Click File>Write Changes
Subscriptions
Select the Subscriptions table

- Click>New Record

ID: Auto incremented field (leave default)
ServerGroupID: This references the ServerGroups table ID field of the record added to the ServerGroups table
TopicPath: This is the MQTT topic that you would like to subscribe to. All Sparkplug B topics start with spBv1.0/. The hash ‘#’ stands for multi-level wildcard whereas the ‘+’ is a single level wildcard. Sparkplug B topic names are in the format spBv1.0/<GroupName>/<MessageType>/<NodeName>/<DeviceName>.
* In order to ensure correct processing when ServerGroups.SendBirthRequests == true, please ensure that you do not subscribe to the device level. The collector needs contextual information from the node level to ensure correct template processing. Ex: spBv1.0/Group/+/Node/#.
* In SparkplugB, the third level of the topic path refers to a specific command sent. Please ensure this is set to a ‘+’ so that the collector can receive all message types. Ex: spBv1.0/Group/+/Node/#.
* spBv1.0/Group1/+/Node1/# subscribes to all tags under node ‘Node1’
QOS_Level: 0,1,or2. Corresponds to the publish subscribe model in MQTT. Set this to 0.
Enabled: 0 (disabled), 1 (enabled and logging), or 2 (enabled and discover without logging). 0 and 1 are normal operating settings. 2 can be used upon initial startup to subscribe to the mqtt server, discover the tags, and preview what will be logged into Canary (via the SubscriptionTags table) before logging any data.
DatasetPrefix: The dataset that the subscribed tags will be written to. If the number of tags in the subscription exceeds the TagsPerDataset setting, then the discovered tags will be placed in additional datasets such as <DatasetPrefix>1, <DatasetPrefix>2, etc… If the SubscriptionSenderSession table AutoCreateDatasets setting = true, the datasets will be created automatically in the historian.
TagsPerDataset: Maximum number of tags to place in each dataset. Due to the dynamic nature of MQTT and the number of datapoints, this setting limits (for performance reasons) how many tags are placed in a dataset before creating more datasets.
RemoveFirstNBranchesFromTagName: Leave set to 0. This is used to truncate n levels from the beginning of the tag name to eliminate redundancy between tag name and dataset (shorter tag names). Use with caution as the more levels are removed, the more likely there will be for tag name conflicts and tags overwriting each other’s data.
AutoLogAllTags: 0 or 1. Set to 1. This setting determines whether tags discovered by the collector are automatically logged to the Canary historian. If set to 0, the user must set the Enabled status in the SubscriptionTags table after discovery to permit the tag to be logged. This setting permits the user to make a decision whether they want all tags in the historian or they want more control over those that are logged to it.
- Click File>Write Changes
SubscriptionSenderSessions

- Click>New Record

ID: Auto increment (leave default)
SubscriptionID: This references the Subscriptions table ID field of the subscription created
Historian: Canary historian machine. Ex. ‘localhost’
AutoGenerateHistorianTags: Not used (leave default). Current version always generates tags
Enabled: 0 (Disabled) or 1 (Enabled).
AutoCreateDatasets: 0 or 1. If = 1 the dataset will be created in the historian if it doesn’t exist.
ExternalPropertyStorage: 0 or 1. Leave default (0).
To log the same subscription data to multiple historians, add a record for each historian to the SubscriptionSenderSessions table that references the subscription.
- Click on File>Write Changes in DB Browser.
<Optional> Configure static properties (metadata) to assign to each tag in a particular subscription. Any configuration here will override any same name metadata coming from the MQTT subscription. Select the SubscriptionTagProperties table

- Click>New Record

Configure the metadata for the subscription.
ID: Auto increment (leave default)
SubscriptionID: This references the Subscriptions table ID field of the subscription created
PropertyName: Name of the metdata. Ex. Eng Units or Business Unit
PropertyValue: Value to assign for all tags in the subscription. Ex. psi or 1234
- Click on File>Write Changes in DB Browser.
<Optional> Configure filters for the subscribed tags. This is helpful if you want to subscribe to the root of the MQTT broker (spBv1.0/#), but control which tags are logged into Canary. Filters utilize regular expressions for power and flexibility. There are many helpful tutorials online that can help in forming the correct expression filter. Open the SubscriptionFilters table

- Click>New Record

ID: Auto increment (leave default)
SubscriptionID: This references the Subscriptions table ID field of the subscription created
MatchExpression: Regular expression to apply to the auto generated Canary tag name. The auto generated Canary tag name is formed by concatenating the Sparkplug B topic information (group id, node id, and device id) with the metric name. The concatenated string, which is delineated in Sparkplug B with forward slashes, is then modified by replacing the forward slashes with periods (which is what Canary uses as a hierarchy separator). Ex: Sparkplug B metric name ‘Compressor1/Tank/Pressure/PSI’ is published on Sparkplug B topic ‘spBv1.0/Texas/DBIRTH/West/PLC1’. The Canary tag name with this structure would be ‘Texas.West.PLC1.Compressor1.Tank.Pressure.PSI’. The configured filter would exclude any tags that end with ‘psi’.
Exclude: If true (default), the filter is exclusive. Otherwise, the filter is inclusive.
ExecOrder: For multiple filters, this controls which order the filter is executed in.
Rule filters are applied as such:
- Inclusive filters are applied first
- If any inclusive filters are created, then none of the tags from the topic are included by default
- If no inclusive filters are created, then all of the tags from the topic are logged by default
- Exclusive filters are applied last
- If tags match any of the exclusive rules, they are excluded from being logged (even if an inclusive rule was matched)
- Rule filtering is applied to the auto generated Canary tag name minus the dataset.
- The auto generated name has some sanitation built into it for leading/trailing spaces in branch & tag names
- Click on File>Write Changes in DB Browser.
Open the Canary Administrator
Stop and Start the MQTT collector from the Canary Admin.





Comments
0 comments
Article is closed for comments.