Locked
Discussion interactions are currently paused.
SQL Results Table Display
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...
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!
Athique_Ahmed
Posted 7 years ago·Last reply 7 years ago
11 comments
Mike_Suding
·7 years agoThe way I usually start troubleshooting is to try a direct query from the debug prompt like this example below: (other examples documented here)
where server2 is my server, database name is 'whatever', username is mike and password is secret and then my SQL query.
This way I can see the errors directly and see if it's authentication or other things like permissions.
Antony Hawkins
·7 years agoAh, 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.
krishna
·7 years agohi 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
·7 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
·7 years ago@Mike Suding
krishna
·7 years agohi 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.
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
·7 years agoI posted the demo video for the DataSource here. I will also submit it to the 'LM Exchange'
Antony Hawkins
·7 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".
Athique_Ahmed
OP7 years agoHi 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!
Mike_Suding
·7 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.
Mike Moniz
·7 years agoSo 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.