Work – Page 18

Defragmenting all fragmented MyISAM tables

September 19, 2008August 8, 2014 Christian 1 Comment

I just had another look at what I wrote the week before last (you know, being home-sick/on vacation has it’s advantages) and additionally read up on “OPTIMIZE TABLE” again. The comments in the manual mention “SHOW TABLE STATUS“, which gives you a complete list, but it doesn’t allow you to filter certain kinds of things out (like I only wanted to see MyISAM tables in the list, I only wanted database and table).

So I went ahead and looked around in MySQL’s own databases and if you look closely at information_schema, it’s got a list of all databases/tables with an additional pointer whether or not databases are fragmented, the row Data_free. I only found this, because I looked at how the mysqltuner figured whether or not you have fragmented tables.

So, without further ado, here’s the final script I’m gonna torture for the next week:

#!/bin/bash

# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME 
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND 
Data_free &gt; 0' | grep -v "^+" | sed "s,t,.," )"

for fragment in $FRAGMENTED_TABLES; do
  database="$( echo $fragment | cut -d. -f1 )"
  table="$( echo $fragment | cut -d. -f2 )"
  [ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] &amp;&amp; mysql -e "USE $database;
  OPTIMIZE TABLE $table;" &gt; /dev/null 2&gt;&amp;1
done

# vim: set tw=80 ts=2 st=2 et :

#!/bin/bash

# Get a list of all fragmented tables

FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME

FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND

Data_free > 0' | grep -v "^+" | sed "s,t,.," )"

for fragment in $FRAGMENTED_TABLES; do

database="$( echo $fragment | cut -d. -f1 )"

table="$( echo $fragment | cut -d. -f2 )"

[ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;

OPTIMIZE TABLE $table;" > /dev/null 2>&1

done

# vim: set tw=80 ts=2 st=2 et :

I know it ain’t completely bullet proof and it sure as hell isn’t neat, but I think it does the job. Also, if you don’t want to paste it, here’s the file download.

What’s up, dude ?

September 16, 2008August 8, 2014 Christian Leave a comment

Some people might ask, “Gosh, what’s up with you ?” .. Well I’ve been incredibly busy with work the last month (sheeesh, another month already passed by). We finally finished the public tender; gonna get some of the components next week (that is the library extension, the LTO4 drives and cartridges, two brand new Cisco MDS9134 and some Fibre channel hard disks for the existing DS4700). After work, I made my usual visits in the gym and well, after I was home some relaxing, kicking up my feet and doing nothing (as in zip).

I’ve visited Munich in late August for a job interview (though they handed me the “Dude, you’re good. But you only finished second” answer by now), had a long weekend for a change (as in five days), which I used to visit my aunt and my cousin in Stuttgart.

Spent some more time figuring out TYPO3 stuff for work, made a terrible mistake (copied a vHost configuration and changed only the ServerName, left the ServerAlias‘es in place). I’m currently writing a review (as well as a Nagios impacted howto) for the MessPC Ethernetbox, hopefully once I’m back at work I’ll be able to finish it.

Right now, I’m home sick (yup, again) with a cold (or something similar, still having para nasal occlusion). Well, I guess I’m gonna be fine next week (at least I hope so …). Cherio.

Custom macros in host definitions

August 16, 2008August 8, 2014 Christian Leave a comment

Well, I was playing with the hostgroup inheritance earlier. One problem with that is, if you define a duplicate service Nagios is really unpredictable or rather inconsistent. Now, as Thomas Guyot-Sionnest told me, I should try custom macros for the check definition. So what I did was the following:

templates/host-windows.cfg

define host {
  name         generic-windows
  register     0
  _RDPPORT     3389
}

define host {

name generic-windows

_RDPPORT 3389

}

hostgroups/windows.cfg

define hostgroup {
  hostgroup_name          windows
  alias                   Windows Servers
  hostgroup_members       windows-terminal
}

define service {
  use                     generic-service
  check_command           check_tcp!$_HOSTRDPPORT$
  service_description     RDP
  hostgroup_name          windows
}

define hostgroup {

hostgroup_name windows

alias Windows Servers

hostgroup_members windows-terminal

}

define service {

use generic-service

check_command check_tcp!$_HOSTRDPPORT$

service_description RDP

hostgroup_name windows

}

hosts/terminal1.cfg

define host {
  use                   generic-windows
  host_name             terminal1
  alias                 terminal1.barfoo.org
  address               10.0.0.250
  parents               barfoo-home
  hostgroups            windows-terminal
  _RDPPORT              3390
}

define host {

use generic-windows

host_name terminal1

alias terminal1.barfoo.org

address 10.0.0.250

parents barfoo-home

hostgroups windows-terminal

_RDPPORT 3390

}

As you can see, the default RDP port is 3389 (as defined in the host template), but for some systems you might want to “change” the port (for example, if you’re having a Citrix farm and you changed the RDP port to something else and still want to be able to check whether or not the RDP service is active), thus the check using the macro, and a single host redefining the macro, thus having a bit more flexibility.

zypper-update-report (was: patch2mail for SLES10)

August 16, 2008August 8, 2014 Christian 2 Comments

Well, after some more refining I think I finally have a script I ain’t never gonna touch again (unless something breaks, which can happen quick as we all know).

The script now uses a sysconfig file for the common settings (like sender, receipents, categories to scan for), so it may be deployed en mass.

/etc/sysconfig/zypper-update-report

## Type: string
## Default: root
## Config: ""
#
# Sender address for the update report
FROM="Yourupdatemonkey "

## Type: string
## Default: root
## Config: ""
#
# Receiver address for the update report
#RECEIPENTS="tehsysadmin@barfoo.org"

## Type: string
## Default: "securty recommended optional"
## Config: ""
#
# List of groups, to include in the report
CLASSES="security recommended optional"

## Type: string

## Default: root

## Config: ""

# Sender address for the update report

FROM="Yourupdatemonkey "

## Type: string

## Default: root

## Config: ""

# Receiver address for the update report

#RECEIPENTS="tehsysadmin@barfoo.org"

## Type: string

## Default: "securty recommended optional"

## Config: ""

# List of groups, to include in the report

CLASSES="security recommended optional"

/usr/local/sbin/zypper-update-report

#!/bin/bash

# Checks the output of `zypper pch` for security/recommended/optional updates
# and prepares a detailed report to be mailed to the administrators

[ -f /etc/sysconfig/update-report ] || exit 1

source /etc/sysconfig/update-report

# Temporary files
TMPDIR="$( mktemp -d /tmp/update-report.XXXXXX )"
ZYPP_LIST="$TMPDIR/zypper-list"
ZYPP_DETAILS="$TMPDIR/zypper-details"
ZYPP_REPORT="$TMPDIR/zypper-report"
zypper pch 2&gt;/dev/null &gt; $ZYPP_LIST

# Figure out how much updates are still pending
PENDING="$( cat $ZYPP_LIST | grep "| Needed" | wc -l )"

if [ $PENDING -eq 0 ] ; then
  exit 0
fi

echo &gt; $ZYPP_REPORT
echo " Pending updates for $( domainname -f ) on $( date )" &gt;&gt; $ZYPP_REPORT

for severity in $CLASSES; do
  PACKAGES="$( cat $ZYPP_LIST | egrep "${severity}(.*)| Needed" | cut -d| -f2 | sed "s,^ ,," | sort -u )"
  [ -n "$PACKAGES" ] &amp;&amp; echo
  [ -n "$PACKAGES" ] &amp;&amp; echo "  Category: $severity"
  for package in $PACKAGES; do
    zypper patch-info $package 2&gt;/dev/null &gt; $ZYPP_DETAILS
    echo ""
    echo "  * Patch: $package"
    echo "    Needs reboot: $( cat $ZYPP_DETAILS | grep "Reboot Required:" | sed -e "s,Reboot Required: ,," )"
    echo "    Affected packages: "
    for atom in $( cat $ZYPP_DETAILS | grep "^atom:" | cut -d  -f2 | sort ); do
      # Let's check whether or not the package listed in atom is installed ...
      # If installed, echo the atom, otherwise don't as we don't need to update
      # the package.
      RPM_STATUS=$( rpm -qi $atom )
      if [ "$RPM_STATUS" != "package $atom is not installed" ] ; then
        echo "    - $atom "
      fi
    done
  done
done &gt;&gt; $ZYPP_REPORT

if [ -n "$RECEIPENTS" ] ; then
  cat $ZYPP_REPORT | mail -r "$FROM" -s "[$( date +%F )] Update report for $( domainname -f )" $RECEIPENTS
fi

trap "rm -rf "$TMPDIR" &gt;/dev/null 2&gt;&amp;1" ERR EXIT
# vim: set tw=80 ts=2 sw=2 et softtabstop=2

#!/bin/bash

# Checks the output of `zypper pch` for security/recommended/optional updates

# and prepares a detailed report to be mailed to the administrators

[ -f /etc/sysconfig/update-report ] || exit 1

source /etc/sysconfig/update-report

# Temporary files

TMPDIR="$( mktemp -d /tmp/update-report.XXXXXX )"

ZYPP_LIST="$TMPDIR/zypper-list"

ZYPP_DETAILS="$TMPDIR/zypper-details"

ZYPP_REPORT="$TMPDIR/zypper-report"

zypper pch 2>/dev/null > $ZYPP_LIST

# Figure out how much updates are still pending

PENDING="$( cat $ZYPP_LIST | grep "| Needed" | wc -l )"

if [ $PENDING -eq 0 ] ; then

exit 0

echo > $ZYPP_REPORT

echo " Pending updates for $( domainname -f ) on $( date )" >> $ZYPP_REPORT

for severity in $CLASSES; do

PACKAGES="$( cat $ZYPP_LIST | egrep "${severity}(.*)| Needed" | cut -d| -f2 | sed "s,^ ,," | sort -u )"

[ -n "$PACKAGES" ] && echo

[ -n "$PACKAGES" ] && echo " Category: $severity"

for package in $PACKAGES; do

zypper patch-info $package 2>/dev/null > $ZYPP_DETAILS

echo ""

echo " * Patch: $package"

echo " Needs reboot: $( cat $ZYPP_DETAILS | grep "Reboot Required:" | sed -e "s,Reboot Required: ,," )"

echo " Affected packages: "

for atom in $( cat $ZYPP_DETAILS | grep "^atom:" | cut -d -f2 | sort ); do

# Let's check whether or not the package listed in atom is installed ...

# If installed, echo the atom, otherwise don't as we don't need to update

# the package.

RPM_STATUS=$( rpm -qi $atom )

if [ "$RPM_STATUS" != "package $atom is not installed" ] ; then

echo " - $atom "

done

done >> $ZYPP_REPORT

if [ -n "$RECEIPENTS" ] ; then

cat $ZYPP_REPORT | mail -r "$FROM" -s "[$( date +%F )] Update report for $( domainname -f )" $RECEIPENTS

trap "rm -rf "$TMPDIR" >/dev/null 2>&1" ERR EXIT

# vim: set tw=80 ts=2 sw=2 et softtabstop=2

Debugging “rug”

August 15, 2008June 21, 2013 Christian Leave a comment

Well, it’s 7pm. I’m sitting at home and thinking about why in gods name rug isn’t adding my update repository. I can add the service using yast inst_source, but when yast then syncs with ZenWorks, it tells me something like:

Failed to get repomd/repodata.xml; Reason: 530 – Access denied

So my fellow co-worker turned on the debug-logging and we quickly found out why: rug isn’t using the command line credentials I was passing.

Now I only need to find out, why rug isn’t using them, and how I’m able to pass username and password to rug .. Or not, after looking through the Novell community, I found bug 204741 in Novell’s bugzilla. Guess, what .. It’s marked WONTFIX (or whatever, I can’t view the duplicate bug).

Suspected NRPE weirdness

August 10, 2008June 21, 2013 Christian Leave a comment

Well, I just noticed a really weird thing, when you have command line arguments enabled.

Here’s a snippet from my nrpe.cfg:

dont_blame_nrpe=1
command[check_disk]=/usr/lib/nagios/plugins/check_disk -E -w $ARG1$ -c $ARG2$ -p $ARG3$

1 2	dont_blame_nrpe=1 command[check_disk]=/usr/lib/nagios/plugins/check_disk -E -w $ARG1$ -c $ARG2$ -p $ARG3$

Now, if you’d check the free space for the root, it ain’t gonna show any inode percentage (that one isn’t what I’m talking about). But if you have to use bind mounts like I do (Tivoli needs a separate “domain” — that is a separate mount point for each domain), you might wanna check the free space on the *real* device, rather than the free space on the bind mount (which is gonna show you the free space of the parent file system – in my case the root fs).

Let’s take a look at what I’m talking about. If you use the check_disk locally like this:

./check_disk -w 20% -c 10% -p /apache/
DISK OK - free space: /apache 11090 MB (36% inode=36%);| /apache=19629MB;24575;27647;0;30719

1 2	./check_disk -w 20% -c 10% -p /apache/ DISK OK - free space: /apache 11090 MB (36% inode=36%);\| /apache=19629MB;24575;27647;0;30719

Means, everything is okay, you have to pass the extra trailing slash to the –partition argument, as otherwise it would pick up the bind mount at /backup.

Now, if we do the above by means of NRPE, that’s gonna get you a different result. As I showed above, I have the check_disk command in my nrpe.cfg, I also specifically enabled command arguments during compile time.

./check_nrpe -H nagios.home.barfoo.org -c check_disk -a 20% 5% /apache/
DISK CRITICAL: /apache/ not found

1 2	./check_nrpe -H nagios.home.barfoo.org -c check_disk -a 20% 5% /apache/ DISK CRITICAL: /apache/ not found

Now, why the hell isn’t it picking up the *original* mount point of the file system ? Guess why … Because I added -E to the command, because it didn’t use the original mount point but rather the bind mount in /backup. Removing the -E and it picks up the *original* mount point without any trouble *shrug*.

Nagios 3 and hostgroup inheritance

August 8, 2008August 8, 2014 Christian Leave a comment

As I wrote some time ago, I was trying to utilize Nagios 3.x’s neat feature of “nested” hostgroups. Well, as it turned out I thought it worked differently; basically like this:

define hostgroup {
        hostgroup_name      a-parent-hostgroup
        alias               Our toplevel parent hostgroup
}

define service {
       use                  generic-service
       check_command        check_dummy!0!
       service_description  SSH
       hostgroup_name       a-parent-hostgroup
}

define hostgroup {
        hostgroup_name      a-child-hostgroup
        hostgroup_members   a-parent-hostgroup
        alias               Our child hostgroup
}

define service {
       use                  generic-service
       check_command        check_dummy!0!
       service_description  LOAD
       hostgroup_name       a-child-hostgroup
}

define hostgroup {

hostgroup_name a-parent-hostgroup

alias Our toplevel parent hostgroup

}

define service {

use generic-service

check_command check_dummy!0!

service_description SSH

hostgroup_name a-parent-hostgroup

}

define hostgroup {

hostgroup_name a-child-hostgroup

hostgroup_members a-parent-hostgroup

alias Our child hostgroup

}

define service {

use generic-service

check_command check_dummy!0!

service_description LOAD

hostgroup_name a-child-hostgroup

}

As you can cleary see on line 14, I thought you define the relation between two hostgroups in the child hostgroup. The problem with it was basically (as I said in the earlier posts), that all the services defined for the child hostgroups are handed on upwards to the parent hostgroup(s).

But after talking to Tobi, I quickly found out, that the relation is in fact defined within the parent hostgroup. So if you simply put hostgroup_members within the parent hostgroup and define all child hostgroups which should inherit from the parent one, you should be just fine.

Nagios 3.x and check_pcmeasure.pl

August 7, 2008August 8, 2014 Christian Leave a comment

Recently we purchased a MessPC station for our server room, and my co-worker and myself had the wish it to be integrated within Nagios. Well, so far so good. The first I did was put both keywords into Google.

That pretty fast brought up the manufacturer’s page (sorry it’s German only) about the device supporting Nagios by means of either SNMP or a specific plugin called pcmeasure. So I went ahead and tried both ways.

Using SNMP has the advantage that it’s quickly integrated into Nagios and it doesn’t need a separate plugin for that to work. But it also has a huge disadvantage. check_snmp doesn’t support performance data, which is quite handy if you do want to do graphing from Nagios’ results.

Next I tried the pcmeasure plugin. At first it worked great (that is from plain command line), but then I tried to integrate it into Nagios (well, I did integrate it); but got “Plugin did not exit properly”.

Today, after I had the plugin commented out for about two weeks, I finally had time to look at the issue again. First I thought, simply using utils.pm’s error values would be sufficient for ePN to quit yapping, but apparently it had *real* problems with the pod2usage used within.

So I basically rewrote the plugin (well, not really; it’s still the same – but without the pod2usage and working in Nagios 3.0.3).

More VirtualCenter troubles (fini)

August 7, 2008August 16, 2014 Christian Leave a comment

Well, today the support request came back. Seems one of the originally linked VMTN dicussions really is the only way:

Export the customization specification
Edit the XML file
Import it again

The related part inside the customization specification should then look like this:

&lt;type&gt;vim.vm.customization.Password&lt;/type&gt;
&lt;plainText&gt;true&lt;/plainText&gt;
&lt;value&gt;Password01&lt;/value&gt;
&lt;/password&gt;

<type>vim.vm.customization.Password</type>

<value>Password01</value>

</password>

So if you ever think about switching the default VirtualCenter certificate (for whatever reason), make sure you use the above workaround. Otherwise VirtualCenter is gonna fail miserably during the customization phase of the cloning process.

More VirtualCenter troubles

August 4, 2008June 21, 2013 Christian 1 Comment

Well, after my co-worker switched the VirtualCenter certificates with one produced by our RA a few days ago, I can’t clone anything using a customization specification anymore.

Unable to decrypt passwords in customization specification

Guess, we’re shit outa luck. At least both of those linked VMTN discussions don’t contain any (that is for us) workable solution (well besides storing the password in cleartext in the spec — which ain’t sooo good). Gonna bug him tomorrow to open up a VMware support request, maybe that’ll help somewhat. I sure hope so.