MySQL Logging with Splunk

Getting Splunk running and monitoring common log formats, such as apache logs and system logs, is a pretty straightforward process. Some would even call it intuitive but setting up some of the optional plugins can be tricky the first time you set it up. The following is a quick and dirty guide to getting the MySQL monitor from remora up and running in your splunk instance.

This article assumes you have a splunk server as well as a separate database server running a splunk forwarder that is pushing logs to the main splunk server.

The first step is to prepare your splunk server for the incoming mysql stats. We’ll need to make a custom index (called mysql in our case) on both the server and the database host.  See below:

Once that’s done we’ll also need to create a custom tcp listener on the splunk server.  This is different from the standard listener that runs on port 9997.  Go to the manager and then data inputs to create:

As you see we used port 9936 to be a listener that automatically imports into the mysql index. You’ll want to ensure that this port is reachable from your database server to ensure there are no firewalls blocking your connection. You can test this with a simple telnet command.  If you see a prompt that says “Escape character is” then you’re good to go.

Once we have verified the listener is up and running the next step is to get the mysql monitor installed on all the machines. It’s easily available via the splunk marketplace. All you need is to create a username and password.

Once in the market place locate the Mysql monitor

And then restart splunk

Now that that’s installed we need to make sure all the dependencies for the mysql monitor are setup on the database servers that will be pushing data to the main splunk server.

To install on a debian based os use this command:

    apt-get install python-mysqldb

For a redhat based os use this

    yum install MySQL-python

Accept all the dependencies and assuming there were no issues you’re just about ready.

Next on the list is to make sure your splunk monitoring daemon can talk to the local mysql server. On the machine in our test, we only have mysql running on the internal ip and have to ensure that the mysql user splunk@172.16.154.141 can connect and has permission. You may need to run the following command to grant yourself permission.

     grant all privileges on *.* to 'splunk'@'mysql_ip' identified by 'your-password';

To verify that splunk can access your tables use the following command

     mysql -u splunk -h mysql_ip -p

Once you’ve got that down the last step is to configure the mysql monitor’s config.ini. Here’s the config.ini we used:

[mysql]
 host=172.16.154.141
 port=3306
 username=splunk
 password=your-password

[splunk]
 host=172.16.154.250
 port=9936

[statusvars]
 interval=10

[slavestatus]
 interval=10

[tablestats]
 interval=3600

[processlist]
 interval=10

As of this writing, the place to put that config file is: /opt/splunk/etc/apps/mysqlmonitor/bin/daemon

To start the mysql monitor type this on the db server: /opt/splunk/etc/apps/mysqlmonitor/bin/daemon/splunkmysqlmonitor.py

That’s it!  If you check the Splunk server then you should start seeing the mysql logs popping in immediately.

Pretty nice eh?

Next time I’ll show you how to make the splunk monitor daemon start on boot.

PresStore by Archiware is a multi-platform data backup and archive solution. Rather than writing a GUI control panel application for each platform Archiware uses a web-based front end.

By default PresStore uses port 8000 for access:

http://localhost:8000

This is a common port number, though, for many applications such as Splunk, HTTP proxies, games and applications that communicate with remote server services. 8000 isn’t a special port—it’s just a common port.

If PresStore is installed on a UNIX-based server with another application also using port 8000, changing its port number to something else is as simple as renaming a file. This file is located in PresStore’s install directory and is called lexxserv:8000:

/usr/local/aw/conf/lexxserv:8000

A local administrator can change the name of this file using the mv command. Assuming he wants to change it to port 8001, he’d use:

sudo mv /usr/local/aw/conf/lexxserv:8000 /usr/local/aw/conf/lexxserv:8001

After changing the port, stop the PresStore service:

sudo /usr/local/aw/stop-server

And start it again:

sudo /usr/local/aw/start-server

Or just use the restart-server command:

sudo /usr/local/aw/restart-server

Windows administrators will need to open the PresStore Server Manager utility and change the port number in the Service Functions section.

Forwarding an email message is fairly simple but forwarding multiple messages can be inconvenient for either the sender or the receiver.

If the sender forwards multiple messages as attachments then the recipient receives one message with a variety of potentially unrelated information. This also makes sorting by subject or Date Sent impossible. If the recipient wants individual messages then the forwarder has no option but to send each message individually. This is time consuming.

Like most email clients for Mac OS X, Outlook for Mac can forward messages but it has a unique feature that makes automating forwarding individual messages easy without resorting to scripting—it can forward using a rule.

But Apple’s Mail, Thunderbird and practically any other email client for Mac has rules too! What makes Outlook different?

Outlook can run disabled rules individually. Both Mail and Thunderbird support creating rules and then disabling them so that they won’t be applied to incoming messages, however, for either to run a single rule manually it must run all rules whether they’re enabled or disabled. Running a long list of rules is potentially troublesome.

To configure a fule in Outlook:

1. Select Tools menu –> Rules… and select the type of email account using this rule (POP, IMAP or Exchange).
2. Click the + (plus) button to add a new rule.
3. Give the rule a descriptive name such as “Forward to <email address>”.
4. Set the rule to apply to All Messages.
5. Set the rule to Forward To <email address>.
6. Deselect the Enabled option. This prevents the rule from firing when new mail arrives.

To use this rule to forward multiple messages individually:

1. Select one or more messages in Outlook’s message list.
2. Right-click or Control-click anywhere within the selected messages.
3. Select Rules –> Apply –> Forward to <email address>.

The rule will run for each message and should take only a few seconds to run. The recipient will receive individually forwarded messages. Both sides save time.

As an old school unix geek I have to admit that I’ve been dragging my feet in my efforts to learn and really grasp the idea of Access Control Lists (ACL’s) until embarrassingly recently. As you may know, *nix OS’s of the past have had only basic levels of access control over files on a system and for a surprisingly long amount of time, these simple controls were enough to get by. You were given 3 permission scopes per file, representing the ability to assign specific permissions for the file’s owner, a single group, and everyone else. This was enough for smallish deployments and implementations but when you start having 1000′s of users, setting specific permissions per user started to get needlessly complicated.

Enter ACL’s which grant extremely granular control over every operation you can do on a file. Need a folder to propagate a single user’s permissions to all files but not folders? No problem. Need to give read only access and disallow deletes for a set of folders? No problem there as well. It’s this fine level control that makes using ACL’s important, even mandatory, in some specific cases.

Just a few days ago, encountered an program that was behaving strangely. It was crunching a large number of files and creating the correct output files but for some strange reason, it was deleting them immediately after creating them. I noticed this as I control – C’d the program and saw my file only for it to be deleted once the process resumed. If only there was a way for the OS to disallow the offending program from removing it’s output files…

This is where ACL’s come in and why they are so powerful. I was able to tell the OS to block a program from deleting anything in it’s output folder. Here’s the command I used to check the ACL’s and set them on my mac:

>ls -le

As you see there are no ACL’s set. To set the append only attribute I typed the following.

>chmod +a ‘alt229 deny delete’ ‘output folder’

You see, the ACL has been set.  I’ll try and delete something now.

What gives? Unlike Linux systems ACL inheritance isn’t enabled by default when set from the command line. We’ll need to tweak our original command to enable that.

Clear old permissions first:
>chmod -R -N *
>chmod +a ‘alt229 deny delete,file_inherit,directory_inherit’ ‘output folder’

Now permissions will inherit but only to newly created folders.  You’ll see that the extra permissions have only been set on the newly created folder named ‘subfolder3′

Rerun the command like this to apply it to existing folders.

>chmod -R +a ‘alt229 deny delete,file_inherit,directory_inherit’

Now, you won’t be able to delete any file that’s contained within the main folder and it’s sub folders.

 There are many other special permissions available to tweak your system and help pull you out of strange binds that you may find yourself in. Here’s a list of some of the other ACL’s available in OSX that you can use to customize your environment. This is straight from the man page.

How to keep an Outlook Database tidy. From the get go, it’s important to lay the foundation for Outlook and the user so that their database doesn’t grow out of hand.  This is done by:

1. Organizing Folders the way the user would like them
2. Creating Rules for users (if they need them)
3. Creating an Archive Policy that moves their email to another database (PST).
4. Mounting the archive PST in Outlook so that it’s searchable.
5. Checking the size of the archive PST every quarter or half year to ensure the size hasn’t grown above it’s maximum.
6. Creating a new archive folder for every year.

Organizing Folders the way the user would like them.

Sit down the user, and see how they would like to organize their folders.  If they don’t know, them revisit this with them in a couple of weeks / months.  Speak to them regarding their workflow and make recommendations to streamline their productivity as necessary.  Creating folder is as simple as right clicking the directory tree in Outlook and clicking on “Create Folder”.  It can also be done to create subfolders.

Creating Rules for users (if they need them).

Some users use rules, others don’t, some don’t even know they exist.  Start up a conversation with a user and see if they know what Outlook rules are, and see if they would like to know more about them, use some, or give it a test run for a day or so.  In a nutshell, Outlook rules move email from the Inbox to any mail enabled folder based on a set of, well rules.  You can move by sender, subject, keywords, etc.  Where to create rules is a little different depending on the version of Outlook you’re using:

Creating Rules in Outlook 2003: http://www2.lse.ac.uk/intranet/LSEServices/itservices/guides/softwareAndTraining/microsoftOffice/outlook/email-rules.aspx

Creating Rules in Outlook 2007: http://uis.georgetown.edu/email/clients/outlook2007/outlook2007.createrule.html

Creating Rules in Outlook 2010: http://office.microsoft.com/en-us/outlook-help/manage-email-messages-by-using-rules-HA010355682.aspx

Try to create rules that run from the Exchange server when possible.  This will allow the rules to run on the server and organize them before they hit the Outlook mail client.

Creating an Archive Policy that moves email to another database (PST)

NOTE: If autoarchiving from Outlook, the e-mail will not be available in Outlook Web Access / Active Sync.  If archiving in Exchange 2010 for a user, the Archive databases can be available in Outlook Web Access.  Proper licensing on Exchange and Outlook apply: http://www.microsoft.com/exchange/en-us/licensing-exchange-server-email.aspx

There are some defaults that Outlook uses.

• Generally, it will automatically auto archive to an archive pst called archive.pst.
• By default, it will tend to run every 14 days, and tend to archive all messages older than 6 months.
• The archive.pst will be on the local workstation.
• Microsoft best practice is to NOT store the PST file on the network due to it being fragile and if it receives any incomplete data it will get corrupt.
• You cannot put the PST in read only mode, if you do, you will not be able to mount it until you take it out of read only mode.

Setting up AutoArchive, or manually archiving for Outlook 2003: http://office.microsoft.com/en-us/outlook-help/back-up-or-delete-items-using-autoarchive-HP005243393.aspx

Setting up AutoArchive, or manually archiving for Outlook 2007: http://office.microsoft.com/en-us/outlook-help/automatically-move-or-delete-older-items-with-autoarchive-HA010105706.aspx

Auto Archive Explained for Outlook 2010: http://office.microsoft.com/en-us/outlook-help/autoarchive-settings-explained-HA010362337.aspx

Turning off AutoArchive, or manually archiving for Outlook 2010: http://office.microsoft.com/en-us/outlook-help/archive-items-manually-HA010355564.aspx

Outlook PST Size limitations:

Outlook 2003 default is 20GB, but it can be changed: http://support.microsoft.com/kb/832925

Outlook 2007 default is 20GB, but it can be changed: http://support.microsoft.com/kb/832925

Outlook 2010 default is 50GB, but it can be changed: http://support.microsoft.com/kb/832925

Searching PSTs

For Outlook 2003 with latest updates:

1. Open PST in Outlook
   1. File > Open > Outlook Data File
   2. When using Advanced Find make sure the archive.pst file is select to be searched.

For Outlook 2007:

1. Ensure Windows Search is installed
2. Go to Control Panel > Index Options and ensure your achive.pst is selected to be indexed.
3. Now when you run a search, ensure “search all Outlook folders” is selected.  This will now allow the user to search ALL folders in Outlook at once, including the archive.pst.

For Outlook 2010

1. Ensure archive.pst is open in Outlook
2. Search using Instant Search in Outlook or Windows Search

Searching  doesn’t work in Outlook2007 and 2010: Troubleshooting steps you can do:

1. Check the Event Logs for anything unusual with Office, Outlook, or Windows Search, and troubleshoot the errors that you find.
2. Ensure that the pst file has been marked for being indexed:
   1. Outlook 2007:  Tools > Options > Search Options
   2. Outlook 2010: File > Options > section Search > Indexing Options > Modify > Microsoft Outlook
   3. Ensure the pst hasn’t gone over it’s maximum limit, if it has you will need to run scanpst.exe to repair it (you will lose some data within the PST, and there’s no way to control what will be removed).  If not, skip to  Step #4. Scanpst.exe can be found in different places depending on the version of Outlook you have:
      1. Outlook 2010

i.      Windows: C:\Program Files\Microsoft Office\Office14

ii.      Windows 64-bit: C:\Program Files (x86)\Microsoft Office\Office14

iii.      Outlook x64: C:\Program Files\Microsoft Office\Office14

1. Outlook 2007

i.      Windows: C:\Program Files\Microsoft Office\Office12

ii.      Windows 64-bit: C:\Program Files (x86)\Microsoft Office\Office12

1. After the repair has completed, open Outlook again and allow it to index (how long depends on how big the PST is).  If you check the Indexing Status, you should see it update at least every half hour.

i.      Check Indexing Status in Outlook 2010: Click in Search field > Click Search Tools button > Select Indexing Status

ii.      Check Indexing Status in Outlook 2008: Click on Tools > Instant Search > Indexing Status

1. Proceed to Step #4.
2. Disable and then re-enable the file for indexing.  Go to Search Options and remove the checkmark for the PST that is giving you issues.  Close Outlook and wait a couple of minutes.  Open Task Manager and ensure Outlook.exe is not running anymore. Once you’ve confirmed it’s stopped running on its own, open Outlook again and go back to the Search Options and put a check mark back on the PST that was giving you issues.  Leave Outlook open and alone and allow it to index until that Indexing Status says “0 items remaining”.
3. If after indexing, it still doesn’t go down to “0 items remaining”, or isn’t even close, or the search STILL isn’t working properly, it’s possible the search index is corrupt.  To rebuild it, go to: Control Panel > Indexing Options > Advanced > Rebuild.  This is something that would best be done overnight as it will not only slow down Outlook, but slow down the computer as well.
4. If rebuilding the Search Index still doesn’t work, then you may need to “Restore Defaults” .  On Windows 7, this can be done by clicking on the “Troubleshoot search and indexing” link under Control Panel > Indexing Options > Advanced.  Then click on “E-mail doesn’t appear in search results”.
5. If after all of that, it still doesn’t work, it’s possible you have a corrupt PST.  In which case, follow through with step #3.
6. If that still doesn’t work, consider patching up Microsoft Office to it’s latest updates.
7. If that doesn’t work, consider repairing Microsoft Office by going to: Control Panel > Uninstall a Program > Microsoft Office 2010 > click on the Modify button > Click Repair.  Proceed to Step #4.
8. If that still doesn’t work, create a new PST and import the data (using the Import function, or drag and drop)  from the bad PST into the new PST.  Proceed to Step #3.

When troubleshooting apache issues it becomes necessary sometimes to turn up the level of logging so that we can further determine what a given server is doing and why.  One new handy feature of the Apache 2 series is the ability to log how long it takes to serve a page.  This allows us to track load times throughout the entire website so that we can pipe it into our favourite analytical tool such as splunk or for you old admins, webalyzer or awstats.

Adding this new variable is straightforward.  Just navigate over to your httpd.conf file and look for the section that defines the various log formats.  We’re going to add the %D variable there which represents the time it takes to serve a page in microseconds.  Here is my httpd.conf for example:

The quick and dirty way to get this mod installed is to look for the type of log that your server is configured to use (usually common or combined) and add the %D to the end (although you could put it anywhere)  As you see below I’ve added it to the combined part of the logfile.

The other option is to make a new type of log and put it in there.  I’m going to make a new LogFormat named custom and put it there below.  Note that you’ll have to make sure that your vhost is set to use this type of log.

It seems like the whole world’s gone mobile, and along with it the tools to transition the stampede of devices coming through businesses doors into something manageable. For iOS, it wasn’t long ago that activation was through iTunes only(*gasp!*) and MDM was a hand-coded webpage with xml and redeemable code links on it. Back then Apple ID’s were a monumental headache (no change there) and Palm wasn’t dead yet. It could cause one to reminisce back to the first coming of Palm. Folklore has it there was a job duty at Palm called ‘tap counter’, to ensure nothing took longer than 3 taps to achieve. If you’ve deployed any number of iOS devices like iPads, you may be painfully aware just how many more than that there are just to take one of these devices out of the box before they get into a usable state:
Manually doing each individual device “over the air”, you need to tap 16 times to activate and use the device with an open wireless network (17 if it’s a newer iPad with Siri integration)

And the ‘iTunes Store Activation Mode’ method leaves 9 taps, since it skips the language selection and time zone choices along with the option to bypass Wi-Fi setup.

If you have access to a Mac running Apple Configurator, it takes only 13 after ‘Prepare’ the device for use. It would seem like things haven’t actually improved. But Apple Configurator has more tricks than just the newer one we discussed recently, which is getting Apple TV’s on a wireless network. When you want to do iOS’s version of Managed Preferences, configuration profiles (a.k.a .mobileconfig files,) that’s another two taps PER PROFILE. This is an opportunity to really learn to love Apple Configurator, though, as it shows two of it’s huge advantages here (the third being the fact it can do multi-user assignment on a single iPad, including checking sets of applications out and reclaiming the app licenses as desired)

- You can restore a backup of an activated device (or as many as 30 at once), which answers all of the setup questions in one automated step (along with any other manual customizations you may want)

- If you put the device in Supervision mode, you can even apply configuration profiles WITHOUT tapping “accept” and “install” for each and every one

There are so many things to consider with all the different ownership models for apps, devices, and the scenarios regarding MDM and BYOD, I thought it was worth just to have a mini-topic of ‘how do folks approach getting them iPads out of the box and into a usable state?’

Data backup is a touchy subject. Nobody does it because they want to. They do it because sometimes bad things happen, and we need some way to take a dead server and transform it into a working one again. For Mac OS X Server, that wasn’t always easy. Because of it’s basic nature – a mixture of Open Source components and proprietary Apple technology – backing up OS X Server effectively would usually mean coming up with at least two backup solutions.

To help with all of this, 318 put together the sabackup package. Its purpose was to use Apple’s built-in server management command line tool (serveradmin) to export service settings in such a way that you could turn around and import them with serveradmin and get your server working again. I know that having those backed up settings not only allowed me to resurrect more than one server, but I also have used them to find out when a specific change was made. (Usually after we realized that said change had broken something.)

With Lion and Mountain Lion, Apple decided to address the problem of properly backing up services and service data, and Time Machine now includes a mechanism for backing up running on OS X Server. Inside the Server.app bundle, in the ServerRoot folder that is now the faux root for all Server.app services, you’ll find a ServerBackup command. This tool uses a selection of backups scripts in /Applications/Server.app/Contents/ServerRoot/usr/libexec that allow for backup and restore of specific services. There’s also a collection of SysV-style scripts in /Applications/Server.app/Contents/ServerRoot/etc/server_backup that contain the parameters that ServerBackup will use when backing up services. As with all things Apple, they’re XML Plists. Certain services merit their own specfic backup scripts: Open Directory, PostgreSQL, File Sharing (called “sharePoints” in this context), Web, and Message Server. The OD script produces an Open Directory archive in /var/backups, the PostgreSQL script produces a dump of all your databases, and Message Server will give you a backup of the Jabber database. Web backs up settings, but it’s important to note that it doesn’t back up data. And then there’s the ServerSettings script, which produces a serveradmin dump of all settings for all services. Everything is logged in /var/log/server_backup.

This is what sabackup was designed to do, only Apple has done it in a more modular, more robust, and 100% more Apple-supported way. With that in mind, we’ve decided to cease development on sabackup. Relying on Apple’s tools means that as new services are added, they should be backed up without any additional work on your part – ServerBackup will be updated along with Server.app.

ServerBackup has its quirks, mind you. It’s tied to Time Machine, which means Time Machine has to be enabled for it to work. That doesn’t mean you have to use Time Machine for anything else. If you exclude all the Finder-visible folders, you’ll still get a .ServerBackup folder at the root of the volume backup, with all the server backups. You’ll also get /private, including var (where backups and logs are), and etc, where a lot of config files live. You can dedicate a small drive to Time Machine, let Time Machine handle the backup of settings and data from Server.app services, and make sure that drive is a part of your primary backup solution. You do have a primary backup solution, don’t you?

Serving pages over a dynamic ip can be frustrating, especially if you try to use a free dynamic dns account.  Many of them expire if not used in X number of days, some cost more many than your actual domain and a lot of the built in clients in many of today’s popular routers don’t work reliably.

This is where some custom script foo comes in.  Using industry standards like SSH, SSI’s and cronjobs we can setup a super lightweight script that sends your dynamic ip to a webserver so that it can direct visitors to your in house server.

The graphic below should help visualize:

As you can see from the diagram this script runs, gathers a single variable and then pushes it out to a server via ssh.  From there the server calls that file and uses the ip as a variable to pass along to clients visiting the website by using a simple meta refresh.

Dynamic IP Configuration

After getting ssh keys setup there are really only 2 steps to getting this script to work.  If you haven’t set those up before refer to this guide for help.

Step 1.  Download the ip_updater.sh script here and change the following 4 variables to your own setup

IDENTITY = path to your ssh identity file (usually ~/.ssh/identity or ~/.ssh/id_rsa)

DEST_SERVER = ip or hostname of the server you’re sending your ip to

DEST_FILE = temp file on the server that holds your ip (/tmp/myip)

USERNAME = username to logon as

Step 2.  Setup a crontab to run this script at certain intervals

Here is my sample crontab which runs this script once an hour at 15 after:

# m    h    dom    mon    dow    command
15     *    *      *      *      /home/alt229/bin/ip_update.sh

Web Server Configuration

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Zync Catalog Redirect</title>
	<meta http-equiv="refresh" content="0;URL='http://<!--#include virtual="cableip" -->'" />
    </head>
    <body bgcolor="#000000">
	<p>Redirecting you to <!--#include virtual="myip" --></p> 
    </body> 
</html>

When clients hit your server and get served this page they will automatically get redirected to your dynamic address.

If I had a megabyte for the number of times I praised a cloud provider’s service while simultaneously lamenting that my data had to leave my LAN to live out the rest of it’s days on their servers then I’d have collected enough MB’s to fill a DVD.

That statement is definitely a tad hyperbolic but the need for good “cloud” software that average users can admin and control is definitely much sought after and needed.

Enter Owncloud which has the lofty goal of making all your data accessible everywhere and over all your devices. I said it was lofty right? I was more than a tad skeptical when I read this too but Owncloud delivers.

Setup is almost deceptively straightforward as all you have to do is download a tar file and extract it to your web root folder. From there, make sure your permissions are all owned by the apache daemon user (www-data for ubuntu and apache for redhat / centos).  The only real tricky part is making sure you have all the prerequisites installed and have SSL running properly.  Check the official site for a list of prerequisites http://owncloud.org/support/install/

wget http://mirrors.owncloud.org/releases/owncloud-4.5.3.tar.bz2
tar -jxvf owncloud-4.5.3.tar.bz2
mv owncloud /var/www
chown -R www-data /var/www/owncloud/

You’ll want to make sure that the Allow Overrides variable is set to all or else the custom .htacces that comes with Owncloud won’t work and certain modifications (such as moving the data folder outside the web root) will need to be made due to security concerns.

Next step is to login to your domain and create a username and password.  You also have the option of connecting to a mysql database or using SQLite.  If unsure choose SQLite as it’s the easiest to setup and most compatible.

Next, you have to install the sync client on your local machine.  Grab the latest version from the official website: https://owncloud.com/download.

Run the installer and open the app.  The first time you run it it’ll require your connection settings.  Enter them like so:

Hit next and with any luck you’ll be off to the races!  The default folder is ~/ownCloud and you can start syncing files immediately simply by dragging and dropping.

Next time we’ll go over some more in depth configurations such as configuring contact / calendar syncing as well as syncing to an amazone s3 bucket.

If you get stuck anywhere in the process please refer to the official install guide located here: http://owncloud.org/support/install/