strings in Data source/data point?

Hello

We have some data that is held in a SQL server database table that we would like to use in a dashboard. Ideally the same

data set would populate a pie chart and a small table. e.g.

We have a data source using a JDBC connection over to the SQL server and that all works, problem is that the data point does not work with

strings (in this case status).

So my question is, how can I pull all seven columns in this data set to show in my dashboard? Is there a way of doing this or

is NAN (not a number) an insurmountable block?

The data source is as follows:

The error message is NAN (not a number)

david_gray

Posted 4 years ago·Last reply 4 years ago

6 comments

david_gray

OP4 years ago

17 hours ago, Stuart Weenig said:

At the top of the linked page is this link to an example groovy script for MS SQL: https://github.com/sweenig/monitoring-recipes/blob/master/DataSources/Groovy/JDBC/Groovy_JDBC_MSSQL.groovy.

Line 38 has your SQL query, and lines 39-43 are where you'd have to write code to output the numbers based on the text values.

OMG, I really did not see that ?

Thanks Stuart.

LM User

·4 years ago

At the top of the linked page is this link to an example groovy script for MS SQL: https://github.com/sweenig/monitoring-recipes/blob/master/DataSources/Groovy/JDBC/Groovy_JDBC_MSSQL.groovy.

Line 38 has your SQL query, and lines 39-43 are where you'd have to write code to output the numbers based on the text values.

david_gray

OP4 years ago

19 hours ago, Stuart Weenig said:

You'll have to switch to scripted collection, still using JDBC. Then you can enumerate the possible values of Status and convert to a number that can be stored in the datapoint. Example script here: https://github.com/sweenig/monitoring-recipes/tree/master/DataSources/Groovy/JDBC

Hi,

Thanks for the link.

Unless I am missing something I'm not seeing any example scripts on that link though. This one shows how to setup the JDBC connector, which we

already have, though not being an admin I cannot modify.

Is the scripted collection you mention Groovy or Powershell? We have some of these already working - setup by LM professional services, they seem to generate

boilerplate Groovy code.

Thanks

LM User

·4 years ago

5 hours ago, mnagel said:

And just imagine if LM added per-DP enum support so this wasn't all such a hack.

Going on my list of FRs now.

mnagel

·4 years ago

1 hour ago, Stuart Weenig said:

You'll have to switch to scripted collection, still using JDBC. Then you can enumerate the possible values of Status and convert to a number that can be stored in the datapoint. Example script here: https://github.com/sweenig/monitoring-recipes/tree/master/DataSources/Groovy/JDBC

And just imagine if LM added per-DP enum support so this wasn't all such a hack.

LM User

·4 years ago

You'll have to switch to scripted collection, still using JDBC. Then you can enumerate the possible values of Status and convert to a number that can be stored in the datapoint. Example script here: https://github.com/sweenig/monitoring-recipes/tree/master/DataSources/Groovy/JDBC