|
|
| Author |
Message |
streaker69
Cacti Pro User
Joined: 27 Mar 2006
Posts: 647
Location: Psychic Amish Network Administrator
|
 Posted: Thu Jun 12, 2008 10:49 am Post subject: Monitoring MS SQL from *nix (Yes, it really works!) |
 |
|
I think many people have been looking for a solution to monitoring your MS SQL databases from Linux but haven't found a good one yet, at least I haven't found a good one yet. Here's an example script that I hope you guys can use as a roadmap to create your own templates. I'm not going to make an example template as I think this is a little too customized per location.
Basically it's rather a simple thing to get running. Your command line will look like this:
perl ms_sql.pl hostname dbinstance dbname1,dbname2,dbname3
Do not put the $ in front of the dbinstance value, the script does that automatically. You can list any number of individual databases seperated by comma's at the end, including the _Total if you want that value.
When you make your Data Input method you'll have to create three input fields, and however many output fields you're expecting. Which is exactly why I'm not creating templates ahead of time.
Feel free to make changes to this code to get whatever values you want. I just did this to provide examples of how to retrieve the values using the check_nt program.
Enjoy
Here's the code:
| Code: |
#!/usr/bin/perl
# Replace '/usr/local/nagios/libexec/check_nt' with the localtion where your check_nt binary is located
$CheckNTPath='/usr/lib/nagios/plugins';
@array1 = split(/\,/, $ARGV[2]);
$tempdb = `$CheckNTPath/check_nt -H $ARGV[0] -p 1248 -v COUNTER -l '\\MSSQL\$$ARGV[1]:Transactions\\Free Space in tempdb (KB)'`;
chomp $tempdb;
$TActions = `$CheckNTPath/check_nt -H $ARGV[0] -p 1248 -v COUNTER -l '\\MSSQL\$$ARGV[1]:Transactions\\Transactions'`;
chomp $TActions;
$Users = `$CheckNTPath/check_nt -H $ARGV[0] -p 1248 -v COUNTER -l '\\MSSQL\$$ARGV[1]:General Statistics\\User Connections'`;
chomp $Users;
foreach $dbname (@array1)
{
$dbSize = `$CheckNTPath/check_nt -H $ARGV[0] -p 1248 -v COUNTER -l '\\MSSQL\$$ARGV[1]:Databases($dbname)\\Data File(s) Size (KB)'`;
chomp $dbSize;
$cache = `$CheckNTPath/check_nt -H $ARGV[0] -p 1248 -v COUNTER -l '\\MSSQL\$$ARGV[1]:Catalog Metadata($dbname)\\Cache Hit Ratio'`;
chomp $cache;
$Output1 .= @array1[$x] . "_size:" . $dbSize . " ";
$Output2 .= @array1[$x] . "_cache:" . $cache . " ";
push(@PrintVal,$Output1);
push(@PrintVal,$Output2);
$x = $x + 1;
}
print "Users:" . $Users . " " .
"Transactions:" . $TActions . " " .
"TempDb:" . $tempdb . " " .
$Output1 .
$Output2 .
"\n";
exit 0;
|
|
|
| Back to top |
|
 |
Lecorps4fun
Joined: 25 Apr 2008
Posts: 5
|
| Posted: Wed Dec 03, 2008 3:27 pm Post subject: |
|
|
| Exactly what I'm looking for but prefer a complete template. I'm sorry but I'm a newb when it comes to adding my own template. Can you go into further details?
|
|
| Back to top |
|
|
claymen
Joined: 18 Aug 2008
Posts: 27
Location: Australia
|
| Posted: Wed Dec 10, 2008 8:06 am Post subject: |
|
|
I have been working on something like this for work except we are using WMI to query the information.
I wrote a generic wmi script which uses wmic (deb package available) to query the information from the remote server. It is then simply a matter of setting up the correct data input, data template and then graph template and you are done.
I'll post up some instructions when I am finished. Also note it doesnt require the plugin arch which is a big reason why I wrote it.
|
|
| Back to top |
|
|
claymen
Joined: 18 Aug 2008
Posts: 27
Location: Australia
|
| Posted: Thu Dec 11, 2008 6:49 am Post subject: |
|
|
This is one of the templates I have below.
What exactly were you looking to graph?
The way I have set things up is I wrote a php wrapper which is referenced by cacti. It takes input, feeds it to wmic and takes that output and feeds it back to cacti. It does a bit of cleanup and also hides away the auth so cacti doesnt ever see the login details.
If you are interested grab the attached template file, and download the files from here http://svn.parkingdenied.com/CactiWMI/tags/0.0.1/
Just check over the included CDEF's as I had some funny issues when importing them into my test environment from the production one. We are running an older version of cacti in production so it could be the cause.
Once you have the wmi.php script in place edit the wmi-logins.php and add your login details into the appropriate place and then start setting things up.
Cacti calls wmi.php like so /path/to/php -q /path/to/script/wmi.php <host> <credential> <class> <columns> <filter (optional)> <filter value (optional)> which does the processing and then all you really need to do is add hosts.
The template should include everything to reproduce that graph and you should get an idea of how it ties together. I've tried to make it as dynamic as possible and is at a point where I don't touch the wmi.php at all I just write up new templates and data inputs into cacti to get what I need. See for example the attached Disk I/O, it uses the same wmi.php but just calls a different WMI class and columns of data.
So that should get you going. I have also thrown in the template for the Disk IO and also one for grabbing disk usage. One thing to be aware of is when it asks you for your drive letter to query you must escape the colon. Failure to do so will result in all sorts of problems and so far it appears to be a limitation of cacti. This goes for any other special characters that the bash shell might trip up on.
Ideally all things going correctly you should end up with something like this in your Cacti poller cache (note this is disk i/o, I don't run SQL at home).
| Code: | Script: /usr/bin/php -q /usr/share/cacti/site/wmi.php 10.0.x.x cacti Win32_PerfRawData_PerfDisk_LogicalDisk PercentFreeSpace_Base,PercentFreeSpace,DiskWritesPersec,DiskWriteBytesPersec,DiskReadsPersec,DiskReadBytesPersec,CurrentDiskQueueLength Name C\:
RRD: /var/lib/cacti/rra/dnb_freespace_166.rrd |
| Description: |
|
| Filesize: |
49.02 KB |
| Viewed: |
702 Time(s) |
|
| Description: |
|
| Filesize: |
47.27 KB |
| Viewed: |
702 Time(s) |
|
| Description: |
|
Download |
| Filename: |
cacti_graph_template_windows_-_disk_space_wmi.xml |
| Filesize: |
22.89 KB |
| Downloaded: |
34 Time(s) |
| Description: |
|
Download |
| Filename: |
cacti_graph_template_windows_-_disk_io_wmi.xml |
| Filesize: |
23.87 KB |
| Downloaded: |
29 Time(s) |
| Description: |
|
Download |
| Filename: |
cacti_graph_template_windows_-_sql_performance_wmi.xml |
| Filesize: |
27.89 KB |
| Downloaded: |
40 Time(s) |
|
|
| Back to top |
|
|
Lecorps4fun
Joined: 25 Apr 2008
Posts: 5
|
| Posted: Thu Dec 11, 2008 8:16 pm Post subject: |
|
|
| Wow this is great! thanks for your response. What I'm really looking for is to graph/monitor replication? Is this possible?
|
|
| Back to top |
|
|
claymen
Joined: 18 Aug 2008
Posts: 27
Location: Australia
|
| Posted: Thu Dec 11, 2008 8:33 pm Post subject: |
|
|
| Lecorps4fun wrote: | | Wow this is great! thanks for your response. What I'm really looking for is to graph/monitor replication? Is this possible? |
Do you know which performance counters they would be? Like for example if you can find it in perfmon and tell me what object, counter and instance you selected from perfmon I can throw something together.
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
