MSSQL Query from Linux- FreeTDS for Nagios Monitoring

FreeTDS for MSSQL queries from Linux

Using FreeTDS

Need to monitor a MSSQL server instance from Nagios? You can use FreeTDS to execute a MSSQL query from a Linux box.

Here’s how:

  1. Install FreeTDS:
    yum install freetds
  2. Configure FreeTDS (vi /usr/local/etc/freetds.conf):

    [servername]
    host = 192.168.0.10
    port = 1433
    tds version = 8.0
  3. Configure ODBC driver for FreeTDS (/etc/odbcinst.ini):

    # Define where to find the driver for the Free TDS connections.
    [freetds]
    Description = MS SQL database access with Free TDS
    Driver = /usr/lib/libtdsodbc.so
    Setup = /usr/lib/libtdsS.so
    UsageCount = 1
  4. Test connectivity:

    [root@techpain]# tsql -H servername -p 1433 -U dbuser -P dbpass
    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    1> select field11 from column1 where field1 like '10%'
    2> GO
    field1
    101
    102
    103
    105
    106
    108
    109
    (7 rows affected)
    1> quit
    [root@techpain]#

Nagios MSSQL query

FreeTDS is cool because it allows you to use Nagios to query a MSSQL server using check_mssql command. Here’s how:

Download the check_mssql plugin to the plugins dir on your Nagios server. You can find it at the Nagios Exchange site.

Create command, host, and service definitions for it:

define command {
command_name check_mssql
command_line $USER1$/check_mssql -H hostname -U dbuser -P dbpass -q "select count(*) from column1 where field1 like '10%';" -r "7" -w 8 -c 10
}

define host {
host_name ServerName
alias MSSQL server
address 192.168.0.10
check_command check-host-alive
notification_interval 15
notification_options d,u,r
max_check_attempts 3
active_checks_enabled 1
passive_checks_enabled 0
notifications_enabled 1
check_period 24x7
notification_period 24x7
contact_groups admins
}

define service {
use generic-service
host_name ServerName
service_description MSSQLquery
check_command check_mssql
}

In the above example, Nagios executes the query expects a return of 7, warns at 8, and goes crit at 10.

Share

Execute a MSSQL query from DOS prompt – Nagios Monitoring using ‘sqlcmd’

MSSQL - sqlcmd

Using ‘sqlcmd’

You can use sqlcmd to execute a MSSQL query right from the DOS prompt. Here’s an example:

C:Userstechpain>sqlcmd -S servername -d dbname -U dbuser-P dbpass -q "select field1 from column1 where field1 like '10%';"
field1
------
100
101
102
105
106
108
109

 (7 rows affected)
1> quit
C:Userstechpain>

sqlcmd sytax

-a packet_size
-A (dedicated administrator connection)
-b (terminate batch job if there is an error)
-c batch_terminator
-C (trust the server certificate)
-d db_name
-e (echo input)
-E (use trusted connection)
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-h rows_per_header
-H workstation_name
-i input_file
-I (enable quoted identifiers)
-k[1 | 2] (remove or replace control characters)
-K application_intent
-l login_timeout
-L[c] (list servers, optional clean output)
-m error_level
-M multisubnet_failover
-N (encrypt connection)
-o output_file
-p[1] (print statistics, optional colon format)
-P password
-q "cmdline query"
-Q "cmdline query" (and exit)
-r[0 | 1] (msgs to stderr)
-R (use client regional settings)
-s col_separator
-S [protocol:]server[instance_name][,port]
-t query_timeout
-u (unicode output file)
-U login_id
-v var = "value"
-V error_severity_level
-w column_width
-W (remove trailing spaces)
-x (disable variable substitution)
-X[1] (disable commands, startup script, environment variables and optional exit)
-y variable_length_type_display_width
-Y fixed_length_type_display_width
-z new_password
-Z new_password (and exit)

-? (usage)

For more details on command line options, see http://technet.microsoft.com/

Nagios MSSQL query

The sqlcmd tool is awesome because it allows you to use Nagios and NSclient/nrpe to query a MSSQL server. Here’s how:

  1. Create a .bat on your MSSQL server that contains your query and sends the output to a .txt file
  2. Configure the Windows firewall and NSclient/nrpe check on the MSSQL server
  3. Configure a Windows NSclient/NRPE check in Nagios to check the .txt file
  4. Profit
Share

Rackmounting – Cage nut and screw types

Screw Types

10–32: Most common size in the US
12–24: Less common size in the US
M5-0.8: 5mm outside diameter and 0.8 mm pitch. For light and medium equipment.
M6-1.0: 6mm diameter 1.0mm pitch. For heavier equipment such as servers.

It is fairly easy to see the difference between the 10-32 and M6 sizes, since the M6 screws diameter is much bigger. Also, most of the nuts have the size engraved on the side as ’10-32′ or ‘M61’.

10-32 and M6 rackmount screws and cage nuts

Clip Nuts

A variation on cage nuts is clip nuts, which slide on to the the rack. These are used for telco racks that cannot use standard cage nuts.


Clip Nuts

Share

MSTSC/RDP: Bypassing “The terminal server has exceeded the maximum number of allowed connections”

The quick and dirty MSTSC command

mstsc /v:192.168.1.100 /admin

Getting more information

Look at current remote sessions:
query session /server:servername

Now disconnect the session of your choice by specifying the session ID in the following command:
reset session [ID] /server:servername

RDP/MSTSC

Share

Find Dell service tag from within the OS – Linux and Windows

Get your Dell service tag (sometimes referred to as a serial number) from inside your operating system. Here are the Windows and Linux commands you’ll need.

Windows command

wmic bios get serialnumber
wmic bios get serialnumber - techpain.com

Linux command

dmidecode -s system-serial-number  or lshw

Details on these and other similar commands can be found at thegeekstuff.com

Additional Notes

These commands pull the serial number from the BIOS. A SuperMicro server of mine shows serial number 1234567890 while a VM running in VirtualBox shows 0. If you receive unexpected output, try running the commands without the extra options to get more information, for example: dmidecode | less or wmic bios

Share

Monitoring Temperature and Fans with lm_sensors

How to install and configure lm_sensors for temp and fan monitoring

Install the lm_sensors package

sudo yum install lm_sensors or sudo apt install lm-sensors

Configure lm_sensors

sensors-detect
Enter ‘YES’ for all prompts
Check what module: cat /etc/sysconfig/lm_sensors
Load the module: modprobe (module name)

Check temperature and fan data

(in Fahrenheit): sensors -f

lm_sensors - techpain.com

lm_sensors Manual Page

Usage: sensors [OPTION]... [CHIP]...
-c, --config-file     Specify a config file (default: /etc/sensors.conf)
-h, --help            Display this help text
-s, --set             Execute `set' statements (root only)
-f, --fahrenheit      Show temperatures in degrees fahrenheit
-A, --no-adapter      Do not show adapter for each chip
-U, --no-unknown      Do not show unknown chips
-u, --unknown         Treat chips as unknown ones (testing only)
-v, --version         Display the program version Use `-' after `-c' to read the config file from stdin.
If no chips are specified, all chip info will be printed.
Example chip names:
lm78-i2c-0-2d   *-i2c-0-2d
lm78-i2c-0-*    *-i2c-0-*
lm78-i2c-*-2d   *-i2c-*-2d
lm78-i2c-*-*    *-i2c-*-*
lm78-isa-0290   *-isa-0290
lm78-isa-*      *-isa-*
lm78-*

Configure monitoring

Write a script to cron or check via Nagios/nrpe. This can be as simple or complex as you like. I like to use something like this since it checks each temp individually, and has a separate threshold for each in a single script:

#!/bin/bash

# By techpain 2012-12-11
# Check temperatures

TEMP1=$(sensors -f | grep -A3 'k8temp-pci-00c3' | grep Core0 | awk '{print $3}' | sed 's/\+//' | sed 's/.\{4\}$//')
TEMP2=$(sensors -f | grep -A3 'k8temp-pci-00c3' | grep Core1 | awk '{print $3}' | sed 's/\+//' | sed 's/.\{4\}$//')
TEMP3=$(sensors -f | grep -A3 'k8temp-pci-00cb' | grep Core0 | awk '{print $3}' | sed 's/\+//' | sed 's/.\{4\}$//')
TEMP4=$(sensors -f | grep -A3 'k8temp-pci-00cb' | grep Core1 | awk '{print $3}' | sed 's/\+//' | sed 's/.\{4\}$//')

if [ $TEMP1 -le 85 ] && [ $TEMP2 -le 75 ] && [ $TEMP3 -le 100 ] && [ $TEMP4 -le 100 ]
then
echo "OK - $TEMP1,$TEMP2,$TEMP3,$TEMP4 - cool as the other side of the pillow"
exit 0
else
logger "WARN - $TEMP1,$TEMP2,$TEMP3,$TEMP4 - it's getting hot in here"
echo "WARN - $TEMP1,$TEMP2,$TEMP3,$TEMP4 - it's getting hot in here"
exit 1
fi

lm_sensors monitoring script output - techpain.com

Share

Install a SSL Certificate on a Cisco ASA

To install a certificate on a Cisco ASA firewall, you’ll probably want to use ASDM. Here are the steps:

  1. Select the certificate you want to renew beneath Configuration, Device Management, Identity Certificates, and then click Add.
  2. Under Add Identity Certificate, select the Add a new identity certificate radio button, and choose your key pair from the drop−down menu or create a new key pair.
  3. Enter the appropriate certificate attributes. MAKE SURE TO CLICK ON ‘ADVANCED, AND VERIFY THE FQDN! Once completed, click OK. Then click ‘Add Certificate’.
  4. In the Identity Certificate Request popup window, save your Certificate Signing Request (CSR) to a text file, and click OK. Verify in ASDM that the CSR is pending.
  5. Submit your CSR to get your certificate issued.
  6. Select the pending certificate request under Configuration, Device Management, Identity Certificates, and click Install. In the Install Identity Certificate window, select the Paste the certificate data in base−64 format radio button, and click Install Certificate.
  7. To bind the new certificate to the interface choose Configuration, Device Management, Advanced, SSL Settings. Select your interface under Certificates, and click Edit. Choose your new certificate from the drop−down menu, click OK, and click Apply.

Don’t forget to write your changes!

You can verify the available certs from the CLI using the command

show crypto ca certificates

You can verify the cert is applied to the interface with

show running−config ssl
Share

Timestamp your bash history – Modify .bashrc

Need a date and/or time stamp in your bash history? Simply set the HISTTIMEFORMAT in your (or other users) .bashrc file:

HISTTIMEFORMAT="%D %T "

Now the ‘history’ command shows your timestamp next to the command issued:

[root@hostname ~]# history | tail -n4
998 05/21/12 11:25:41 vi .bashrc
999 05/21/12 11:25:41 exit
1000 05/21/12 11:25:43 ls
1001 05/21/12 11:25:46 history | tail -n5

history timestamp - techpain.com

Share

Using OpenSSL to convert SSL certificate format

OpenSSL Commands

Convert PEM to PFX
openssl pkcs12 -export -out cert.pfx -inkey private.key -in cert.crt -certfile cert-ca.crt

Convert PFX to PEM
openssl pkcs12 -in cert.pfx -out cert.cer -nodes

Convert PEM to DER
openssl x509 -outform der -in cert.pem -out cert.der

Convert PEM to P7B
openssl crl2pkcs7 -nocrl -certfile cert.cer -out cert.p7b -certfile cert-ca.cer

Convert DER to PEM
openssl x509 -inform der -in cert.der -out cert.pem

Convert P7B to PEM
openssl pkcs7 -print_certs -in cert.p7b -out cert.cer

Convert P7B to PFX
openssl pkcs7 -print_certs -in cert.p7b -out cert.cer
openssl pkcs12 -export -in cert.cer -inkey private.key -out cert.pfx -certfile cert-ca.cer

About each format (taken from SSLshopper.com)

PEM Format
The PEM format is the most common format that Certificate Authorities issue certificates in. PEM certificates usually have extentions such as .pem, .crt, .cer, and .key. They are Base64 encoded ASCII files and contain “—–BEGIN CERTIFICATE—–” and “—–END CERTIFICATE—–” statements. Server certificates, intermediate certificates, and private keys can all be put into the PEM format. Apache and other similar servers use PEM format certificates. Several PEM certificates, and even the private key, can be included in one file, one below the other, but most platforms, such as Apache, expect the certificates and private key to be in separate files.

DER Format
The DER format is simply a binary form of a certificate instead of the ASCII PEM format. It sometimes has a file extension of .der but it often has a file extension of .cer so the only way to tell the difference between a DER .cer file and a PEM .cer file is to open it in a text editor and look for the BEGIN/END statements. All types of certificates and private keys can be encoded in DER format. DER is typically used with Java platforms. The SSL Converter can only convert certificates to DER format. If you need to convert a private key to DER, please use the OpenSSL commands on this page.

PKCS#7/P7B Format
The PKCS#7 or P7B format is usually stored in Base64 ASCII format and has a file extention of .p7b or .p7c. P7B certificates contain “—–BEGIN PKCS7—–” and “—–END PKCS7—–” statements. A P7B file only contains certificates and chain certificates, not the private key. Several platforms support P7B files including Microsoft Windows and Java Tomcat.

PKCS#12/PFX Format
The PKCS#12 or PFX format is a binary format for storing the server certificate, any intermediate certificates, and the private key in one encryptable file. PFX files usually have extensions such as .pfx and .p12. PFX files are typically used on Windows machines to import and export certificates and private keys. When converting a PFX file to PEM format, OpenSSL will put all the certificates and the private key into a single file. You will need to open the file in a text editor and copy each certificate and private key (including the BEGIN/END statments) to its own individual text file and save them as certificate.cer, CACert.cer, and privateKey.key respectively.

Share

MySQL – Restoring a MyISAM db from /var/lib/mysql

The easiest way to do this is to use a separate instance of MySQL, in a virtual machine or otherwise. Copy your files to the ‘dev’ host that you will be using. Doing so will create the database. Then you’ll be able to do mysqldump of the database, and restore it to your original DB.

  1. Tar up the MySQL backup files (optional)
    tar -czvf mydatabase /var/lib/mysql/mydatabase
  2. Copy to the ‘dev’ host via scp, rsync, or whatever tool you’d like to use
    rsync mydatabase server2:/var/lib/mysql/mydatabase
  3. Extract the MySQL backup files (if they are archived)
    tar -xzvf mydatabase
  4. Dump the new database using ‘mysqldump’
    mysqldump -u dbuser -p mydatabase > mydatabase.sql
  5. Copy the dump back to your original host (or wherever you’re restoring to)
    rsync mydatabase.sql server1:/root/
  6. Restore from your .sql dump
    mysql -u dbuser -p mydatabase < mydatabase.sql
Share