Fullmenu null

 

26 August 2017

Suppose we have a table in a MySQL database where each server in our network reports its status periodically. Let’s say that each server performs an insert / update in the table by updating a lastupdate field with the date of her system to be able to know when was the last time this server reported.

As a sysadmin you want to know how many servers there were running last minute, last hour, last day and so on, and in this way detect if you have problems.

With this script you’ll connect to the database, do a select and show the result on the console. In this way you can concatenate this script with others and react when the number of servers are upper a value to send an alarm, etc

@Grab('mysql:mysql-connector-java:5.1.6')// (1)
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

sql=Sql.newInstance(
        "jdbc:mysql://localhost/granja", // (2)
        "user", "password", "com.mysql.jdbc.Driver" // (3)
)

row = sql.firstRow('SELECT count(*) caidos FROM hosts where lastreport < ?',[new Date()-1]) //(4)

println row?.caidos //(5)
  1. We need the mysql library

  2. We need the host and the database

  3. We need the username and the password

  4. We want the first record. This query can be parametrizable

  5. Show the result by console

This script show how easy is to connect and ejecute a parametrizable query. We use the ? character to specify where we want to substitute the params, in our example we want to substitute with yesterday param.


Script
@Grab('mysql:mysql-connector-java:5.1.6')// (1)
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

sql=Sql.newInstance(
        "jdbc:mysql://localhost/granja", // (2)
        "user", "password", "com.mysql.jdbc.Driver" // (3)
)

row = sql.firstRow('SELECT count(*) caidos FROM hosts where lastreport < ?',[new Date()-1]) //(4)

println row?.caidos //(5)