Hi, LogicMonitor beginner here! I have a sql statement which outputs outstanding orders currently in the system and what date they are outstanding from. It may show as follows... 18/11/2018 - 1 19/11/2018 - 3 20/11/2018 - 2 Depending on how many orders are currently outstanding, these results can of course change for example more dates can be listed and the table can get longer if there are outstanding orders from further back in the year. My question is how best to display this data on the dashboard, is there a widget that can be used to display data like this? Thanks!

So LogicMonitor (LM) is a monitoring tool, so you need to think of your data from a monitoring perspective. Monitoring tools are designed to reach out to a resource, get the current value of something and save it. It will then do this over and over again every x minutes. For example if you are doing a ping check, the collector will ping the device every 1 minute or so, record the response time each time, then you can graph the data it collected over time. You would need to let LM do the data tracking over time. So if you wanted to use SQL to track outstanding orders, you would modify your SQL statement to just return the current outstanding orders number. LM will run your SQL statement every x minutes and track the values. Then you can create a graph (and other) widget showing values over time.

@Athique Ahmed, I'm seriously considering making a DataSource that runs any SQL query and shows the resulting table in a 'text' dashboard widget (in HTML). Would that help you? Currently the text widget is limited to 65,535 characters so it would be ~10 columns wide by 200 rows (depending on how much is in each 'cell') But, in general, @Mike Moniz is correct in that LM is mostly designed to retrieving a single number every X minutes and graphing that over time. LM config could run a SQL query and get the results but there's no dashboard widget to show the resulting config.

Hi Gents, Thanks for the responses. @Mike Moniz I see what you are saying. I currently have several different metrics I'm tracking like this using big number widgets and graphs etc and this works fine as these are basically single measures. I guess in this case I have a need for multiple measures to be tracked at once and displayed in a way that the other widgets currently do not support. Thanks for the tips! @Mike Suding This is exactly the thing I'm looking for except for me it would be really basic...2 columns and possibly several rows at most so more than adequate! If you do create this widget do let me know as it would be extremely useful :-) Thanks!

@Athique Ahmed you may also want to take a look at a ConfigSource I created that writes to dashboard text widgets: /topic/2175-configsource-that-writes-outputs-to-dashboard/ Clearly there'd need to be a different base script to do the SQL query as opposed to gathering a collector .conf file, but all the rest of the logic should "just work".

I posted the demo video for the DataSource here. I will also submit it to the 'LM Exchange'

SQL Results Table Display | LogicMonitor

11 Replies

Replies have been turned off for this discussion

Mike_Moniz
Professor
6 years ago
So LogicMonitor (LM) is a monitoring tool, so you need to think of your data from a monitoring perspective. Monitoring tools are designed to reach out to a resource, get the current value of something and save it. It will then do this over and over again every x minutes. For example if you are doing a ping check, the collector will ping the device every 1 minute or so, record the response time each time, then you can graph the data it collected over time. You would need to let LM do the data tracking over time.

So if you wanted to use SQL to track outstanding orders, you would modify your SQL statement to just return the current outstanding orders number. LM will run your SQL statement every x minutes and track the values. Then you can create a graph (and other) widget showing values over time.
Mike_Suding
6 years ago
@Athique Ahmed, I'm seriously considering making a DataSource that runs any SQL query and shows the resulting table in a 'text' dashboard widget (in HTML). Would that help you? Currently the text widget is limited to 65,535 characters so it would be ~10 columns wide by 200 rows (depending on how much is in each 'cell')

But, in general, @Mike Moniz is correct in that LM is mostly designed to retrieving a single number every X minutes and graphing that over time. LM config could run a SQL query and get the results but there's no dashboard widget to show the resulting config.
Athique_Ahmed
6 years ago
Hi Gents,

Thanks for the responses.

@Mike Moniz I see what you are saying. I currently have several different metrics I'm tracking like this using big number widgets and graphs etc and this works fine as these are basically single measures. I guess in this case I have a need for multiple measures to be tracked at once and displayed in a way that the other widgets currently do not support. Thanks for the tips!

@Mike Suding This is exactly the thing I'm looking for except for me it would be really basic...2 columns and possibly several rows at most so more than adequate! If you do create this widget do let me know as it would be extremely useful :-)

Thanks!
Antony_Hawkins
Employee
6 years ago
@Athique Ahmed you may also want to take a look at a ConfigSource I created that writes to dashboard text widgets:
/topic/2175-configsource-that-writes-outputs-to-dashboard/
Clearly there'd need to be a different base script to do the SQL query as opposed to gathering a collector .conf file, but all the rest of the logic should "just work".
Mike_Suding
6 years ago
I posted the demo video for the DataSource here. I will also submit it to the 'LM Exchange'
krishna
6 years ago
hi Mike,

many thanks for the blog post and it was helpful in connecting to SQL Server.

however, i tried to connect to MySQL DB and getting below error.

QueryException: Could not connect: Access denied for user 'COLLECTORUSER'@'localhost' (using password: NO) Current charset is windows-1252. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

code is changed as below for MySQL :

//Build connection string (set integrated security here)
String Connection_String = 'jdbc:mysql://##HOSTNAME##:3306/##DBNAME##?connectTimeout=30000&socketTimeout=30000&passwordCharacterEncoding=windows-1252'

could you please help fix the isssue.
krishna
6 years ago

Just now, krishna said:

hi Mike,

many thanks for the blog post and it was helpful in connecting to SQL Server.

however, i tried to connect to MySQL DB and getting below error.

QueryException: Could not connect: Access denied for user 'COLLECTORUSER'@'localhost' (using password: NO) Current charset is windows-1252. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

code is changed as below for MySQL :

//Build connection string (set integrated security here)
String Connection_String = 'jdbc:mysql://##HOSTNAME##:3306/##DBNAME##?connectTimeout=30000&socketTimeout=30000&passwordCharacterEncoding=windows-1252'

could you please help fix the isssue.

@Mike Suding
Antony_Hawkins
Employee
6 years ago
@krishna I suggest you contact Support directly, through the in-platform chat option. They'll be able to check credential properties, etc. are correctly set up.
krishna
6 years ago
hi Antony,

thanks for the reply. i had contacted support and they asked me check with Mike Suding as his blog posts are not supported by support.
Antony_Hawkins
Employee
6 years ago
Ah, fair point - although that error message strongly suggests a credentials (or lack of) issue, which is a fairly generic SQL troubleshooting issue. Are other SQL (JDBC) datasources applying and working for the server? If not then there's a credentials / access issue (standard support); if they are then it is indeed back to @Mike Suding to take a look at this script specifically.