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

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

Windows ‘forfiles’: Show or Delete files older than X days

Using ‘forefiles’ for file management and cleanup

forfiles - show or delete files older than X days

Use forfiles to show and/or delete files older than X amount of days:

/p = path
/s = subdirectories (recursive)
/m = mask
/d = days
/c = cmd to be run

Show .log files in C:LogFiles (recursively) older than 1 year:
forfiles /p C:LogFiles /s /m *.log /d -365 /c "cmd /c echo @file is at least 1 year old."

Remove .log files in C:LogFiles (recursively) older than 1 year:
forfiles /p C:LogFiles /s /m *.log /d -365 /c "cmd /c del @file"

Save as a .bat and schedule it.

Share

RADIUS Windows Server for a Cisco ASA VPN

Connect to your Cisco ASA VPN by authenticating against a Windows RADIUS server

Thanks to FixingIT.wordpress.com. I pulled most of this post from there, made some tweaks, and added the Cisco CLI as an alternative to ASDM.

The following steps are a walk through of configuring a Windows 2008 Server Domain Controller as a RADIUS server for an ASA, and configuring that ASA as the RADIUS client. This will allow VPN users to authenticate against Active Directory instead of locally on the ASA.

These steps assume the following:

  • Windows Server 2008: 192.168.0.10
  • Cisco ASA: 192.168.0.5

 

Configure the ASA

CLI

The applicable parts of the config are as follows:

interface Vlan1
nameif inside
security-level 100
ip address 192.168.0.5 255.255.255.0

aaa-server SERVER protocol radius
accounting-mode simultaneous
aaa-server SERVER host 192.168.0.10
key mysecretkey
radius-common-pw mysecretkey

ASDM

Create an IP Name object for the target

  1. Under the Firewall section, expand the Objects link and select the IP Names.
  2. Click the Add button at the top.
  3. Enter a descriptive name, the IP address of the DC/RADIUS server and a description of the server.
  4. Click OK and then Apply

Create a new AAA Server Group

  1. Click the Remote Access VPN section.
  2. Expand AAA Setup and select AAA Server Groups.
  3. Click the Add button to the right of the AAA Server Groups section.
  4. Give the server group a name, like TEST-AD, and make sure the RADIUS protocol is selected.
  5. Accept the default for the other settings. And click OK

Add the RADIUS server to the Server Group

  1. Select the server group created in the step above.
  2. Click the Add button to the right of Servers in the Select Group.
  3. Under the Interface Name select the interface on the ASA that will have access to the RADIUS server, most likely inside.
  4. Under Server Name or IP Address enter the IP Name you created for the RADIUS server above.
  5. Skip to the Server Secret Key field and create a complex password. Make sure you document this as it is required when configuring the RADIUS server. Re-enter the secret in the Common Password field.
  6. Leave the rest of the settings at the defaults and click Ok.

 

Configuring the Windows 2008 DC/RADIUS Server

*requires domain admin privileges

Add the Network Policy Server function

  1. Connect to the Windows Server 2008 server and launch Server Manager.
  2. Click the Roles object and then click the Add Roles link on the right.
  3. Click Next on the Before You Begin page.
  4. Select the Network Policy and Access Services role and click Next.
  5. Under Role Service select only the Network Policy Server service and click Next.
  6. Click Install.

After the role finishes installing you will need to set up the server using the Network Policy Server (NPS) management tool found under Administrative Tools.

Registering the server

After launching the NPS tool right-click on the entry NPS(Local) and click the Register Server in Active Directory. Follow the default prompts.

Create a RADIUS client entry for the ASA

  1. Expand the RADIUS Clients and Servers folder.
  2. Right-click on RADIUS Clients and select New RADIUS Client.
  3. Create a Friendly Name for the ASA device. I used “CiscoASA” but if you had more than one you might want to make it more unique and identifiable. Make sure you document the Friendly Name used as it will be used later in some of the policies created.
  4. Enter the Server Secret Key specified on during the ASA configuration in the Shared secret and Confirm shared secret field.
  5. Leave the default values for the other settings and click OK. See Figure 1 for all the complete RADIUS Client properties.

Create a Connection Request Policy

  1. Expand the Policies folder.
  2. Right-click on the Connection Request Policies and click New.
  3. Set the Policy Nameto something meaningful. I used CiscoASA because this policy is geared specifically for that RADIUS client. Leave the Type of network access server as Unspecified and click Next.
  4. Under Conditions click Add. Scroll down and select the Client Friendly Name condition and click Add
  5. Specify the friendly name that you used when creating the RADIUS Client above. Click OK and Next.
  6. On the next two pages leave the default settings and click Next.
  7. Under the Specify a Realm Name select the Attribute option on the left. From the drop down menu next to Attribute: on the right select User-Name. Click Next again.
  8. Review the settings on the next page and click Finish.

Create a Network Policy

  1. Right-click the Network Policy folder and click New.
  2. Set the Policy Name to something meaningful. Leave the Type of network access server as Unspecified and click Next.
  3. Under Conditions click Add.
  4. Add a UsersGroup condition to limit access to a specific AD user group. You can use a generic group like Domain Users or create a group specifically to restrict access.
  5. Add a Client Friendly Name condition and again specify the Friendly Name you used for your RADIUS client.
  6. Click Next. Leave Access granted selected and click Next again.
  7. (Important Step) On the authentication methods leave the default selection and add Unencrypted authentication (PAP, SPAP).
  8. Accept the default Constraints and click Next.
  9. Accept the default Radius Settings and click Next. Review the settings and click Finish.

Restart the Network Policy Server service. Probably not be necessary, but not a bad idea.

Test Your RADIUS Authentication

CLI

test-fw# test aaa authentication SERVER host 192.168.0.10 username testuser password mypassword 
INFO: Attempting Authentication test to IP address <192.168.0.10> (timeout: 12 seconds)
INFO: Authentication Successful

ASDM

The ASDM utility includes functionality to test RADIUS Authentication.

  1. If necessary re-launch the ASDM utility.
  2. Return to Configuration > Remote Access VPN > AAA Setup > AAA Server Groups.
  3. Select the new Server Group you created.
  4. From the Servers in the Selected Group section highlight the server you created. Click the Test button on the right.
  5. Select the Authentication radio button. Enter the Username and Password of a user that meets the conditions specified in the Network Policy created above then click OK.
  6. If everything works as designed you should see something similar to “Authentication test to host is successful”
Share

Log Off Users from Terminal Server – .bat File

Log off all users, but with specific exceptions

RDP - Remote Desktop Protocol

I came across a situation where we needed to automatically disconnect all users from a terminal server, except for one specific user. The following script (thanks Ferdinand!) can be run as a batch file to accomplish just that; it will log off all terminal server users except for ‘userx’. It gives each user a 5 minute and 1 minute warning, then runs a ‘query session’ to see what sessions show up and writes them to a file call ‘sessions.txt’. Next it searches the session.txt file for “userx” and writes the rest of the sessions to “killts.txt”, logs off those sessions/users, and deletes the .txt files that it created. It goes through the process twice to get active AND disconnected sessions.


@ECHO OFF
msg * You will be logged off in 5 minutes.
choice /T:240 /D N /N > Nul
msg * You will be disconnected in 1 minute! Please log off now!
choice /T:60 /D N /N > Nul
query session >C:SchedTaskssessions.txt
find /v "userx" C:SchedTaskssessions.txt > C:SchedTaskskillts.txt
for /f "skip=5 tokens=3," %%i in (C:SchedTaskskillts.txt) DO logoff %%i
query session >C:SchedTaskssessions.txt
find /v "userx" C:SchedTaskssessions.txt > C:SchedTaskskillts.txt
for /f "skip=5 tokens=2," %%i in (C:SchedTaskskillts.txt) DO reset session %%i
del C:SchedTaskssessions.txt
del C:SchedTaskskillts.txt
EXIT

Here are some other related Windows TS/RDS commands (from thelazyadmin.com):

Query TermServer – Lists all terminal servers in the current domain.
QUERY TERMSERVER [/domain:domain] [/address][/continue]
* /domain:domain – specifies the domain (current logged on domain is default)
* /address – lists the IP address of the terminal server
* /continue – removes the pause between output screens

Query Session – Lists all current sessions running on a terminal server.
QUERY SESSION [sessionname | username | sessionid][/server:servername] [/mode] [/flow] [/connect] [/counter]
* sessionname is the name of the session that you want to query
* username is the name of the user you want to query
* sessionid is the ID of the session you want to query
* /server:servername is the name of the server you are querying
* /mode outputs the current line settings
* /flow outputs the current flow control settings /connect outputs the current connection settings
* /counter outputs the counter information for the server

Query User or Quser – Lists all current users on a terminal server
QUERY USER [username | sessionname | sessionid] [/server:servername]
* sessionname is the name of a specific session that you want to query
* username is the name of the specific user you want to query
* sessionid is the ID of the specific session you want to query
* /server:servername is the name of the server you are querying

Query Process – Lists all processes running on the terminal server.
QUERY PROCESS [ x | processid | username | sessionname | /id:nn | programname] [/server:servername] [/system]
* x lists information on all processes (note – replace x with an asterisk)
* processid lists information about only the specific process ID
* username lists processes running under the context of a specific user
* sessionname lists processes running under the context of a specific session
* /ID:nn lists processes running in the session with the specified session ID number
* programname lists all processes started by the specified executable
* /server:servername is the name of the server you are querying—the default is the server you are logged on to
* /system lists processes running under the system context

TSShutdn – Will shutdown/reboot the terminal server after a specified delay.
TSSHUTDN [wait_time] [/server:servername] [/reboot] [/powerdown] [/delay:logoffdelay] [/v]
* wait_time is the number of seconds to wait after notifying the users that the terminal server is about to shut down before forcibly logging them off (the default is 30 seconds)
* /server:servername is the name of the server to reboot/shutdown (the default is the server to which you are connected)
* /reboot reboots the server
* /powerdown powers down the server after Windows has shutdown; the servers BIOS must support this command
* /delay:logoffdelay the number of seconds to wait after logging out all users before shutting down the system (the default is 30 seconds)
* /v displays verbose information about actions being performed

Logoff – Will logoff the specified user off the terminal server and close the session. Caution, if you don’t specify a user it will log you off!
LOGOFF [sessionid | sessionname] [/server:servername] [/v]
* sessionid is the ID of the session you want to logoff
* sessionname is the name of the session you want to logoff
* /server:servername specifies the name of server on which the session you want to logoff is running
* /v displays verbose information about actions being performed

Reset Session – Will kill the specified users session without warning which can be useful when a users session is stuck. Caution, if you don’t specify a user it will kill your session!
RESET SESSION [sessionname | sessionid] [/server:servername] [/v]
* sessionid is the ID of the session you want to logoff
* sessionname is the name of the session you want to logoff
* /server:servername specifies the name of server on which the session you want to logoff is running
* /v displays verbose information about actions being performed

MSG – Will popup a message on the specified user(s) terminal server session.
MSG [username | sessionname | sessionid | @filename | x ][/server:servername] [/time:seconds] [/v] [/w] message
* username is the name of the user to whom you are sending the message
* sessionname is the session name to which you want to send the message
* sessionid is the ID number of the session to which you want to send the message
* @filename is the name of a text file containing usernames, sessionnames, or session IDs to which you want to send the message
* x sends the message to all users on the current or specified server (note – replace x with an asterisk)
* /server:servername specifies the server where recipients of the message are connected
* /time:seconds the number of seconds to display the message before the popup closes itself
* /v displays information about the message as it is sent
* /w causes the popup window to wait for the user to click OK before closing message is the text of the message to send

Shadow – Will allow you to shadow or take control of a users session.
SHADOW [sessionname | sessionid] [/server:servername] [/v]
* sessionid is the ID of the session you want to logoff
* sessionname is the name of the session you want to logoff
* /server:servername specifies the name of server on which the session you want to logoff is running
* /v displays verbose information about actions being performed

*** NOTE: Windows Server 2008 changed the name Terminal Services (TS) to Remote Desktop Services (RDS), but the above commands are the same. ***

Share

Changing the IP address of a Scalix Server

Change your Scalix IP without breaking things

Scalix webmail - Change your Scalix IP address

Changing the IP of a Scalix server is really easy. Along with changing the obvious stuff (etc/hosts, /etc/sysconfig/network, /etc/sysconfig/network-scripts/ifcfg-eth0…) just follow these directions from the Scalix Wiki:

Update the Postgres Client Authentication

Changing your IP address does not update Postgres and access to the Scalix API is then denied. To rectify this you need to modify the file /var/opt/scalix/NN/postgres/data/pg_hba.conf. Remember NN is the abreviation of your Scalix node, so it will change depending on the hostname of your scalix server.
Find the line that looks like:

host    scalix      scalix      192.168.1.100/32   md5

Edit the file to change this line to look like:

host    scalix      scalix      192.168.1.50/32   md5

Update the Search and Index Service

Changing your IP address does not update the Search and Index Service properties. To rectify this you need to modify the file /var/opt/scalix/NN/sis/sis.properties. Again remember NN is the abreviation of your Scalix node!
Find the lines that look like:

index.client.whitelist=192.168.1.100,127.0.0.1
search.client.whitelist=192.168.1.100,127.0.0.1

Edit the file to change the lines to look like:

index.client.whitelist=192.168.1.50,127.0.0.1
search.client.whitelist=192.168.1.50,127.0.0.1

Update the Uber Manager Service

As above changing your IP address does not fix the Uber Manager. To fix this you need to modify the file /var/opt/scalix/NN/caa/scalix.res/config/ubermanager.properties. Again remember NN is the abreviation of your Scalix node!
Find the line that looks like:

ubermanager.notification.listener.address=192.168.1.100

Edit the file to change the line to look like:

ubermanager.notification.listener.address=192.168.1.50

Reboot and enjoy your server

After you have finished this procedure please reboot. Everything should be working just like before.
Do remember, however, that this post only covered Scalix. Other services or other parts of the server which depend on the IP Address instead of hostname will still need to be changed.

Share

Configuring System Alerts in Windows Server 2003

Create an Alert in System Monitor to Track Free Disk Space

1. Click Start, point to Administrative Tools, and then click Performance.
2. Expand Performance Logs and Alerts.
3. Right-click Alerts, and then click New Alert Settings.
4. In the New Alert Settings box, type a name for the new alert (for example, Free disk space), and then click OK.

The AlertName dialog box appears, in which you configure settings for the alert that you created.
5. Click the General tab, and then in the Comment box, type something like Monitors free disk space on C drive.

Windows Alerts - Low Disk Space Alert

Configure the Alert

1. Click Add to open the Add Counters dialog box.
2. Click Select counters from computer, and then select your computer in the list.
3. In the Performance object box, click LogicalDisk.
4. Click Select counters from list, and then click % Free Space.
5. Click Select interfaces from list, and then click the logical drive or volume that you want to monitor.
6. Click Add to add the counter, and then click Close.
7. In the Alert when the value is box, click Under, and then type the value that you want in the Limit box. For example, to trigger an alert message when free disk space is under 9%, type 9.
8. Accept the default value of 5 seconds in the Sample data interval, or specify the value that you want.
9. Click Apply.
10. Click the Action tab, and then specify the action or actions that you want to perform when an alert occurs, as follows:

  •  If you want the Performance Logs and Alerts service to create an entry in the application log of event viewer when an alert occurs, click to select the Log an entry in the application event log check box.
  • If you want the Performance Logs and Alerts service to trigger the Messenger service to send a message, click to select the Send a network message to check box, and then type the IP address or name of the computer on which the alert should be displayed.
  • To run a counter log when an alert occurs, click to select the Start performance data log check box, and then specify the counter log that you want to run.
  • To run a command or program when an alert occurs, click to select the Run this program check box, and then type the file path and name of the program or command that you want to run. Or, click Browse to locate the file.

When an alert occurs, the service creates a process and runs the specified command file. The service also copies any command-line arguments that you define to the command line that is used to run the file. Click Command Line Arguments, and then click to select the appropriate check boxes to include the arguments that you want to implement when the program is run.
11. Click Apply.
12. Click the Schedule tab, and then specify the start and stop parameters for the scan, as follows:
          1. Under Start scan, do one of the following:

  • Click Manually if you want to manually start the scan. After you select this option, right-click the alert in the right pane, and then click Start to start the scan.
    • Click At to start the scan at a specific time and date, and then specify the time and date that you want.

              2. Under Stop scan, do one of the following:

    • Click Manually if you want to manually stop the scan. After you click this option, right-click the alert in the right pane, and then click Stop to stop the scan.
    • Click After to stop the scan after a specified duration, and then specify the time interval that you want.
    • Click At to stop the scan at a specific time and date, and then specify the time and date that you want.

              3. If you want to start a new scan after the alert scan is complete, click After, and then click to select the Start a new scan check box.

    Taken (and then edited) from the Microsoft KB

    Share