Collector - SQL

The collector capable of running Logscape apps, and due to this can be used to collect metrics from SQL server databases. All you need to do is create an app which contains the necessary scripts to connect to, and retrieve metrics from, your SQL server. Below is an example of a Groovy script to connect to, and retrieve metrics from your database.

For a guide on how to build your own Logscape apps, you can visit the App Introduction Page.

Retrieving SQL metrics

To retrieve metrics from the SQL server database we will use a simple Groovy script, the script will build a connection, connect, query the database and then write the output to file. Aswell as the Groovy script you will need the SQL JDBC jar, which can be found here.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import groovy.sql.Sql;
import groovy.sql.GroovyResultSetExtension;
				
def sql = Sql.newInstance("YOUR_SERVER_IP", "YOUR_USERNAME", "YOUR_PASSWORD")
 
sql.eachRow("select * FROM yourTable;"){ row -> 
	pout << log_kv(row)
}
 

To run this class, you'll need to include the JDBC driver in your class path, or when putting the app into Logscape, include the jar in your lib folder. The log_kv method can be seen below.

def log_kv(map){
def timestamp = new Date()
builder=[]
 
for(def key:map.keySet()){
   def  v = map[key]
	builder.add(key + ":" + v)
}
 
message = " "  +  timestamp + " " + builder.join(", ")
return message
}

This method simply iterates over a map, prepends a timestamp, and returns it in a Logscape compliant format.

Save this file, name it whatever you choose appropriate.

The Bundle file

After creating a working groovy script, it's time to design the .bundle file, the bundle gives Logscapes jobs to run relating to the app.

<Bundle name="ProjectSymphonyApp" version="1.0" system="false">
<status>UNINSTALLED</status>
	<owner>info@logscape.com</owner>
	<services>
		<Service>
			<name>YOUR_SERVICE_NAME</name>
			<resourceSelection>YOUR_FILTER</resourceSelection>
			<fork>true</fork>
			<background>true</background>
			<instanceCount>-1</instanceCount>
			<pauseSeconds>YOUR_PAUSE</pauseSeconds>
			<script>YOUR_GROOVY_SCRIPT.groovy</script>
		</Service>
	</services>
</Bundle>

This bundle is a small section of a much larger bundle, but shows how to include your Groovy script.

To finish, simply zip, and deploy your app as shown in the app guide, your collector will now collect stats from your SQL database.