With the recent release of Geocortex Analytics 1.7, we’ve introduced some exciting new features, including support for database monitoring as well as Scenes and Notebooks for ArcGIS Online and Portal for ArcGIS. Version 1.7 also brings various performance improvements, including faster load times when monitoring trends for your Geocortex apps.
With that in mind, we are going to show you how Geocortex Analytics can monitor your databases in today’s tech tip. Specifically, we’ll demonstrate how to configure a database, a query, and an alarm that’s based on that query.
Video Transcript
“Hello everybody. What’s up? I’m Aaron Oxley. I’m a Technical Sales Specialist at Geocortex, and in today’s Tech Tip, I want to show you how Geocortex Analytics can monitor your databases.
It’s a new feature, released in 1.7 and basically, Analytics will probe your database to make sure it’s up and running, alive and well, but beyond that, we can also configure custom queries and Geocortex Analytics can trigger alarms if that response to that query is an unexpected value.
So, today, I’m going to show you how to configure the database, how to configure the query, and how to configure an alarm based on that query. Let’s dig in!
I’ll sign into Geocortex Analytics and we want to jump into configuration in the top right corner, and then in the bottom left corner, we can select ‘Add Resource’ and add a ‘Database’.
So, first up, ‘Display Name’ and this is arbitrary, it’s just the name that we’re going to see in this left menu. My database is hosted on a server named ‘PROD-DMSQL’, so I’ll go with that. The next field, ‘Server Installed On’ – this allows Analytics to make a connection between the CPU and ram usage for this particular database and the performance of the entire server.
I know this database is installed on my PROD-DMSQL server. So, I’ll pick that one.
Now, ‘Connection Type’, this is how Geocortex Analytics knows how to connect to the database if it’s an SQL server. We just need to get the right connection string, if it’s OLEDB or an ODBC connection then a driver would need to be present. In this case, though, I am going to use an SQL server.
By default, we have a couple of fields here. ‘Server / Data Source’, which is pretty self-explanatory – it’s just the server where the database is hosted and ‘Authentication Type’. If we select Authentication Type, we get a couple of extra fields to add credentials, but for my database, I actually need to use a more complex connection string. So, I’ll switch this toggle, so I can use a custom connection string and I can enter whatever I want in this box. If you need to add parameters other than the authentication type and credentials, then this is the way to do it.
So, I’ll just paste in my connection string value and then test it, and we’ve successfully connected to the database. Let’s say ‘ok’ and save that, and the database now shows up in the resource list and Geocortex Analytics will attempt to connect every 15 minutes by default or whatever interval you have configured.
Now, if Geocortex Analytics can’t connect to the database, we do have a default alarm configured. Let’s go take a look at that.
So here it is. What this alarm means is that if the database were to become unavailable for any reason, an alarm will be triggered. We can come over and open up this More button and click ‘Check Now’ to test the connection and Analytics lets us know that this database is responsive. If it wasn’t, this alarm would be triggered and we’d see a red triangle over here with an exclamation mark and – depending on what we have configured – this could fire a batch script send an email, or an SMS message or any combination of those.
Let’s go take a look where that happens. I’ll select configuration again and then I’m going to jump over to the alarms tab on this database and I can see the alarm here. I’ll click the pencil to edit and now I’ll switch this toggle off, so we can configure this alarm independently and now I can set this alarm to run a batch script or send notification by email or by text message.
Where this gets really powerful though, is issuing a query and having alarms based on that query. It could be how long it takes for a response to come back, or you can configure an alarm to trigger based on the value that gets returned. So, I have a table that stores active work orders – it’s essentially a queue and if it ever gets over 150, I want to know right away that’s a problem and it needs attention.
I should highlight that Analytics can only work with queries where the response will be a single value, so keep that in mind when configuring.
Let’s go set this one up. If I come over to the ‘Queries’ tab and click ‘New Query’, we need to give it a name, I will say ‘Work Orders’ and now the query itself; this one’s pretty basic. I will use a ‘SELECT COUNT’ and I need to specify a field in the brackets here, ‘(WorkOrderID)’ is my unique field, that’s what I want and we’re selecting this count from my work orders table, and I’ve got the name of that here. I’m just going to paste it in.
If I wanted to, I could continue refining this query with a where clause, but I’m interested in the total count of all work orders. So, let’s test it!
Ok the value returned was 155. So, let’s say okay and save that one. Now, if I expand this row, we can see that there are three new alarms associated with this query. The ‘Query Failure Alarm’, it’s pretty self-explanatory, if the server was down or the table was removed or if a field name was changed work order ID, if something caused this query to actually fail. The ‘Query Result Alarm’, this allows us to trigger an alarm based on the value that gets returned, and this is where we can check if it’s over 150.
I’ll click the pencil to edit. I’m going to toggle off the default settings and set the maximum to 150 and I’ll set up an email alert for myself and save. That’s it.
Now, this last alarm, ‘Query Response Time Alarm’. This lets us monitor the performance of this Database. it’s a really simple query that we’re running so it should only take a few milliseconds. I’m going to say it’s an issue if it takes over 10 milliseconds to complete and again email myself. I’ll save that one and now we can close out a configuration, and let’s expand this query and we can see the three alarms here.
On the right side, if we click one of these More buttons, we can do a check now again and we can see the orange exclamation mark here indicating that this alarm is in a triggered state.
Now, if I go and check my Outlook, I can see that I have a new email from Geocortex Analytics letting me know about the alarm. There it is an alarm for PROD-DMLSQL, the database query result exceeded the maximum value of 150.
So that’s it. Analytics database monitoring!
If you want to know more just let us know email info@geocortex.com. If this video was helpful for you please like, share, subscribe, and check out our other videos. There are tech tips and webinars all sorts of great stuff on our channel.
Thanks for watching!”
Want to get a first-hand look at how Geocortex Analytics can help optimize performance for your GIS? Click the button below to get more product information, or to schedule a personal demonstration!