3CX Data Connectors - System Metrics

[leeu]

Good day,

Apologies if this is posted in the incorrect section.

The new documentation and data connectors page on the admin console mention that system metrics can be exported to an external database.
I have configured a data connection to a postgresql database and only see the following tables:

cdrbilling
cdroutput
cl_quality
recording_participant
recordings


Is there a reason why there is no system metric data being exported to the database despite the option being selected?

Additionally, is there any roadmap to add additional tables - the current data is insufficient for 3CX to be an attractive option for many call centre solutions as there is currently no exporting of data on agent login history, or queue call poling history (yes, I am aware there are reports for this, but this does not help for Grafana integration without having to manually pull data into a database).

Edit - I am referring to work I am doing on an enterprise installation of 3cx (128 sim call license) - I had to setup a trial account to be able to post here.

 

[KyriacosS_3CX]

Hi,

The 128 should be exporting those tables too. In PostgreSQL an extra namespace is created tsdb under which this data resides. The provided Grafana templates know where to find the data.

Polling history can be inferred from the data already exported, but it depends I would say on exactly what you wish to see.

Regarding additional data type exports, more are being considered but I cannot provide any specific information.

 

[leeu]

Hi,

These options are selected:


No system metrics data is exported.

Can you advise if there are troubleshooting steps?
Obviously this would not be a config issue on the postgresql server as it's receiving all the other information.

 

[KyriacosS_3CX]

Are you checking on the DB server or thr Grafana dashboard?

In DBeaver at least those tables appear like this, separate schema:

 

[Colby D.]

I had the issue where I had to uncheck system metrics, save, then recheck it and save again before it showed up. It did this for 2 or 3 different systems

 

[KyriacosS_3CX]

I had the issue where I had to uncheck system metrics, save, then recheck it and save again before it showed up. It did this for 2 or 3 different systems

Can you advise on system details? Version, OS, new install, upgrade from, etc? Long running systems will take time to do their initial sync, but some data usually pops up fairly fast.

 

[Colby D.]

Can you advise on system details? Version, OS, new install, upgrade from, etc? Long running systems will take time to do their initial sync, but some data usually pops up fairly fast.

Unfortunately its been a few weeks since so I dont remember exactly which systems it did happen on. In general though:

All were existing system upgrades
At least one or two from v16 or earlier
All running in AWS Lightsail or EC2 with above recommended specs

The behavior I had is I first turned on the sync and checked all 4 boxes and started the sync, the first two tables for billing and output showed up but recordings and metrics did not. I noticed after a day or two and did the unchecking > save> recheck > save. And after maybe 10 minutes the table showed up with data.

I do have a few more systems that I have not yet set up the data integration on but will notate down the information before/after in case another one does it.

 

[JacquesMulti]

If I am understanding correctly, the cl_quality table does not exist anymore. From my setup on Grafana Dashboard 13 uses data from this table and now cannot pull any data to populate the dashboard. This is looking for network and jitter info and gives an error when refreshing the dashboard.

Running version V20.0.7.1057
Running SQL Express DB
Only sending CDR and System Metrics to the database.

 

[KyriacosS_3CX]

Only sending CDR and System Metrics to the database.

The call quality data will not be transferred unless the Call Audio Metrics is selected.

 

[JacquesMulti]

The call quality data will not be transferred unless the Call Audio Metrics is selected.

I have but am still not seeing that table in the database

 

[JacquesMulti]

I have but am still not seeing that table in the database

Having a look at the system log I am getting an error 50032
Data Transferring to sql server failed due to the following reason: Failed to offload `tsdb_system`: System.InvalidOperationException:

 

[KyriacosS_3CX]

Hi,

Could you

A) post the rest of the error

B) if you can go to the latest u7, you are missing the hotfixes I believe, and see if it resolves it.

 

[JacquesMulti]

Hi,

Could you

A) post the rest of the error

B) if you can go to the latest u7, you are missing the hotfixes I believe, and see if it resolves it.

Data Transferring to sql server failed due to the following reason: Failed to offload `tsdb_system`: System.InvalidOperationException: The given value '1.2781304705460528' of type String from the data source cannot be converted to type float for Column 1 [cpu_usage]. ---> System.FormatException: Failed to convert parameter value from a String to a Double. ---> System.FormatException: The input string '1.2781304705460528' was not in a correct format. at System.Number.ThrowFormatException[TChar](ReadOnlySpan`1 value) at System.String.System.IConvertible.ToDouble(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source) --- End of stack trace from previous location --- at _3CX.TableDataOffload.Uploader.SqlServerUploader.ExportAsync(String pathToCsvFile, Table table, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.RetryPolicy.<>c__DisplayClass2_0.<<ExecuteAsync>b__0>d.MoveNext() --- End of stack trace from previous location --- at _3CX.TableDataOffload.Uploader.RetryPolicy.ExecuteAsync[T](Func`2 action, ILogger logger, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.RetryPolicy.ExecuteAsync(Func`2 action, ILogger logger, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.BaseUploader.HandleItemAsync(UploadItem item, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.BaseUploader.UploadAsync(UploadItem item, CancellationToken cancellationToken) Failed to offload `public_cl_quality`: System.InvalidOperationException: The given value '362.5' of type String from the data source cannot be converted to type real for Column 14 [a_rtt]. ---> System.FormatException: Failed to convert parameter value from a String to a Single. ---> System.FormatException: The input string '362.5' was not in a correct format. at System.Number.ThrowFormatException[TChar](ReadOnlySpan`1 value) at System.String.System.IConvertible.ToSingle(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source) --- End of stack trace from previous location --- at _3CX.TableDataOffload.Uploader.SqlServerUploader.ExportAsync(String pathToCsvFile, Table table, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.RetryPolicy.<>c__DisplayClass2_0.<<ExecuteAsync>b__0>d.MoveNext() --- End of stack trace from previous location --- at _3CX.TableDataOffload.Uploader.RetryPolicy.ExecuteAsync[T](Func`2 action, ILogger logger, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.RetryPolicy.ExecuteAsync(Func`2 action, ILogger logger, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.BaseUploader.HandleItemAsync(UploadItem item, CancellationToken cancellationToken) at _3CX.TableDataOffload.Uploader.BaseUploader.UploadAsync(UploadItem item, CancellationToken cancellationToken)

 

[KyriacosS_3CX]

What exact database engine/server version are you using?

 

[JacquesMulti]

What exact database engine/server version are you using?

Microsoft SQL server 2019

 

[KyriacosS_3CX]

Let me check in with the team and I'll let you know if more info is needed.

 

[KyriacosS_3CX]

Hi, could you advice what local/regional settings you have on the PBX and on the database machine/engine?