LogicMonitor DataMagic's purpose is to present much of the information available in LogicMonitor as an offline relational database.

Currently Postgres and Microsoft SQL Server are supported.

Configuration

JSON file creation

LogicMonitor DataMagic configuration can get very complex. Currently, this is managed using a JSON document.  We recommend that you create and manage this document offline using a JSON editor such as Visual Studio Code.  The contents should then be pasted into the "Configuration" text area in the Connection.

Configuration template

NCalc expressions, including the NCalc Extensions by Panoramic Data are used in a few places here. You can use NCalc101 to test NCalc expressions.

Lines 1-4

These provide the database set up. The only thing you will need to change here is Line 2. This is the connection ID of the database you wish to sync into. This should be omitted if there is only one sync per database, in which case the sync will use the ID of the connection that this configuration is applied to. If no database of that ID exists, the sync will create one.

If a connection to an external Database is required, you can instead provide the following:

• DatabaseType
• Required
• "SqlServer" OR "Postgres"
• DatabaseServerName
• Required
• The FQDN of the external database server
• DatabaseName
• Required
• The name of the database on the database server
• DatabaseUsername
• The username for connecting to the database
• When using SqlServerAuthenticationMethod of ActiveDirectoryServicePrincipal, this is the ClientId
• DatabasePassword
• The password for connecting to the database
• When using SqlServerAuthenticationMethod of ActiveDirectoryServicePrincipal, this is the ClientSecret
• DatabaseServerPort
• Optional
• Set only if your database is on a non-standard TCP port
• SqlServerAuthenticationMethod
• Optional
• Only set if using a DatabaseType of "SqlServer"
• Only set if using a SqlAuthenticationMethod other than "NotSpecified"
• Valid values:
• NotSpecified
• SqlPassword
• ActiveDirectoryPassword
• ActiveDirectoryIntegrated
• ActiveDirectoryInteractive
• ActiveDirectoryServicePrincipal
• ActiveDirectoryDeviceCodeFlow
• ActiveDirectoryManagedIdentity
• ActiveDirectoryMSI
• ActiveDirectoryDefault
• ActiveDirectoryWorkloadIdentity

Lines 5-7

These provide the LogicMonitor properties you wish to be collected per device. These will be stored on the “Devices” table, in the columns “PropertyX”. We currently have space for 20 properties, but this can be increased. 

Line 9

This should currently always be as shown.

Lines 10 – 37

These provide the configuration for syncing 2 DataPoints of a DataSource 

Line 12 – The DataSource Name

This should be exactly as it appears in the “Name” Field on the DataSource definition in the Settings tab of the LogicMonitor portal 

Line 13 – The AppliesTo field

This works exactly as an applies to function in LogicMonitor, and you should make use of this in the portal to test any function you wish to use here. This determines which instances are collected in the Dimensions sync and what data is collected.

Line 14 – Instance Inclusion expression

This takes an NCalc expression and can based on the fields brought back from the instance API query. For example, the expression "contains(Description,'//')" would only bring in instances whose description contains // 

Lines 16-27

These provide the DataPoint definition for a single DataPoint. These can be in any order  

Line 17  – The DataPoint Name

This is what will be stored in the “Name” field in the “DataSourceDataPoint” Table.  This should be exactly as it appears in the DataPoint definition, unless a calculation is used as per Line 19 

Line 18 – The measurement unit 

This is what will be stored in the “MeasurementUnit” field in the “DataSourceDataPoint” Table.  This is what the metrics put into the database will be measured in. This should be the same as the measurement unit in LogicMonitor unless a calculation is used 

Line 19 -  Calculation

This can be used to manipulate the data as it is collected. If this is used the “Name” field can be anything, but this field must contain at least one DataPoint name exactly as it appears in the DataPoint definition. 

For example: To the percent used into the Database when a DataSource has no such datapoint, but it does have a percent free DataPoint 

• “Name”: “PercentUsed” 
• “Calculation” : “100-PercentFree” 

Alternatively, if no percentages are present but used and total value are, you can use multiple DataPoints in the calculation 

• “Name”: “PercentUsed” 
• “Calculation”: “(BytesUsed/TotalBytes)*100”

Line 20 – Global Alert expression

Stored on the “DataSourceDataPoints” table under the “GlobalAlertExpression” Column. By default we pull in the Global Alert Expression on the datapoint N.B. we do not currently pull group/device/instance level thresholds, it is the global setting only. You can use this line if none is set, or if you want to overwrite it. If you are using the calculation line, as this is not a DataPoint that exists in LogicMonitor there is no Global Alert Expression, this field will be blank unless this line is used. 

Line 21 – Tags

This is what will be stored in the “Tags” field in the “DataSourceDataPoint” Table. This can be any useful data you want stored against the DataPoint 

Lines 22-24 – Properties

These will be stored in the “DataSourceDataPoints” Table under the columns “PropertyX”. We currently have space for 10 properties. This is functionally the same a tags but having the name separation can be useful.

Lines 25 and 26 – InstanceDatapointProperties

These will be stored in the “DeviceDataSourceInstanceDataPoints” Table under the columns “InstanceDatapointPropertyX” . We currently have space for 10 properties. These must take an NCalc expression. In the example shown on line 25, there are two conditions 

• Condition 1 checks if the property system.categories is set, if it is it moves to condition 2, otherwise it enters a blank string as indicated by the empty quotes at before the final close parentheses  
• 
  
• Condition 2 switches on the value of system.categories and enters the value immediately after that class. If the value is A, the property stored is B, if its C, the value is D, and if its neither then the property value is blank, as denoted by the empty quotes after ‘D’ 

Optional Terms

"FakeExecutionTime" : "YYYY-MM-DD HH:mm:SS"

To be used within the “Database” section, for testing. Allows you to fake when the sync occurs so that it can be done e.g. in a previous month.

"ExcludeSdtPeriods" : false

To be used within the “Database” section. The default is false (e.g. when the term is omitted), but when explicitly set to true, the time series aggregations (stored in the TimeSeriesDataAggregations table) will ignore data points that were deemed to be within a period of Service Down Time (SDT). Historical SDT periods are queried via the LogicMonitor API for the Device Data Source Instance, the Device Data Source, the Device, and the Device Groups all the way to the root. SDT periods are then flattened and only data that does not fall inside an SDT period is included. 'Current' SDT periods are not queries, i.e. if there is an ongoing period of SDT, because we are only concerned with historical data.

When the property is missing it is equivalent to false.

"LimitAlertSyncToDataSourceAppliesTo" : true

To be used prior (or after) the "Database" section (e.g. underneath the "DimensionSyncHaltOnError" property on line 9), this setting, when present, allows you only sync Alerts for the devices relevant to the "AppliesTo" property, when set and not empty, of each DataSource (e.g. the "AppliesTo" on line 13).

When the property is missing it is equivalent to false, and so Alerts for all Devices will be synced.

"MinutesOffset" : xxx 

To be used within the “Database” section, can be set up to 720 positive or negative (i.e. 12 hours) to account for time offsets from UTC. E.G. Setting it at -600 means data gathered from 2pm UTC on the day before the end of the month will be treated as being collected at 00:00 on the first day of the month. This should be coupled with starting the sync at the appropriately offset time i.e. for the above, start it at 2pm on the last day of the month 

"StartDateTimeUtc" : "YYYY-MM-DD HH:mm:SS"

To be used within the database section, allows you to set when to start gathering aggregations the first time a sync is run. Useful for if you want start with multiple months worth of data. Aggregations will still only span 1 month each but the first sync can gather multiple months. 

Once data is gathered, you cannot set a date prior to that data’s date, so this can only work when the sync is first run, or if the data is cleared from a database prior to this being used. 

AggregationReset

To be used within the database section, this allows you to reset the time series data aggregations (i.e. truncate the table / reset them), only if time series data is set to be processed. This is controlled via the Admin app via the DataMagic > Syncs menu, by ticking the "Sync Time Series Data" checkbox on a Sync.

Connection configuration

Once you have created the configuration file, you will need to create a new LogicMonitor connection with a user that has read access, and copy the JSON into the “Configuration” Field.

Sync configuration

You will then need to go to the DataMagic Syncs page.

Ensure the Connection field is set to the connection you just set up. It is recommended to select the options shown above, i.e. Sync to Database, Sync Dimensions, and Sync Time Series Data.

• Sync to file would create a downloadable file of the data 
• Sync Dimensions is needed to get the Devices, DataSources, DataPoints, and Instances 
• Sync Logs works as expected 
• Sync Events gathers the alerts, however, this adds a lot of time to the sync 
• Sync Time Series Data gathers the aggregations for each of the instances. 
• Sync to trigger will allow you to select another sync to automatically happen after this one finishes 
• Schedule to trigger allows you to select a schedule to automatically run after the sync finishes 

The first time you run the sync, the database will be created, unless the “DatabaseConnectionId” line is used and set to an existing database ID. Once the Database has been created, a connection to it will have to be set up before the data can be used in any macros.

Accessing the data

Data access is via a SQL connection.  Please see support for credentials and access.

Database Schema

Whether you have configured for SQL Server or Postgres, you will need to understand the Database Schema.

Most table relations are self-explanatory (Websites has a foreign key into WebsiteGroups on Website.WebsiteGroupId to WebsiteGroup.Id).  However, the instance relationships are as follows: