How to import a CSV in to Grafana

I was given a CSV and I needed to import this file in to Grafana.

I found that I couldn't actually import data directly in to Grafana because Grafana needs a data source. The data source already setup was Graphite so all I did was send the data to Graphite and sure enough, the data showed up in Grafana. There are some kinks I had to work with though and I'll explain.

First of all, if your data is older than your Graphite's retention period, nothing will show up in Grafana. So, modify the date/time to something that will fall in your retention period. However, do keep in mind that this date/time is going to be used by Grafana to represent your data. I would imagine that you could also just tell Graphite to extend the retention period. That leads us to the next gotcha.

So, you've extended your retention period but it still doesn't work. What now? Well, it turns out that you can't just extend your retention period in your Graphite configs. You have to actually tell the whisper file as well. Read the docs here "http://graphite.readthedocs.org/en/latest/config-carbon.html" and look for whisper-set-aggregation-method.py for an example.

Cool. Now that it's taken cared of, you can start sending data in to Graphite (see http://graphite.readthedocs.org/en/latest/feeding-carbon.html ).

Alright, according to the above documentation, I should be able to do the following...

echo "local.random.diceroll 4 `date +%s`" | nc -q0 myserver 2300

Sure enough, typing this and pressing enter makes some data show up in Graphite (and ultimately, Grafana). The next thing I needed to do was to send hundreds of lines with 31 fields in to Graphite. No way was I going to type it up one by one. So I cooked up a quick and dirty script to do it for me.

I took the headers out of the CSV file and placed it in to a separate file called sampleheaders.csv. I deleted the headers out of the CSV file. Below is the script I came up with.

#!/bin/bash
#Set an original starting time in EPOCH
#Then, set a starting time in EPOCH
#Calculate your own EPOCH as this date/time is for demonstration purposes only
ostarting=1450944001
starting=1450944001

#Grab all the headers from the sample headers file
headers=`head -1 sampleheaders.csv`

#There are 31 fields so we have to loop through each one (hard coded but it's quick and dirty)
for i in {1..31};do

#Set the starting point to the original starting point
starting=$ostarting

#print each line one by one and only grab the field we're working on
 for x in `cat fsample.csv | awk -F , -v var=$i '{print $var}'`;do

#get the header that we're on
#i.e. if we're on loop 3, then get field 3 of the headers
newvar=$(echo $headers | awk -F , -v myvar=$i '{print $myvar}' | sed "s/\"//g")

#echo out the header field in $newvar + the value in $x + the time in $starting
#This first echo is only for verbosity
#It is the second echo that actually pipes to nc
echo "comps.test1.$newvar $x $starting | nc -q0 localhost 2003"
echo "comps.test1.$newvar $x $starting" | nc -q0 localhost 2003
#increment the starting time by 86,400 seconds (about 24hrs)
#that way, the data is plotted one day after another.
 starting=$( echo "${starting}+86400" | bc -l )
 done

done

How to add a host using check_mk web api

check_mk has a web api that you can use to add hosts from the command line. The documentation is here:
http://mathias-kettner.com/checkmk_wato_webapi.html

I went searching for a bash script in hopes that someone may have already created one. No luck. So, I created my own. You can find it on github here:
https://github.com/gyang2300/cmk-add-host/blob/master/cmk-add-host.sh

How to monitor Oracle RAC Interconnect

I created a cron job as such:

*/10 * * * * sudo -u user $somepath/bin/cluvfy comp nodecon -n `hostname -f` > /var/log/interconnect/interconnect.out 2>&1

This cron job will run the "cluvfy" binary and send the results to a log file. This will happen every 10 minutes.

Then, I created a Nagios plugin as such:

#!/bin/bash
#
#Simple script to keep a tab on interconnect
#

addinfo="some additional information"
isupdated=`find /var/log/interconnect/interconnect.out -mmin -15`
isok=`tail -1 /var/log/interconnect/interconnect.out | grep 'Verification of node connectivity was successful'`

if [[ $isupdated ]] && [[ $isok ]];then
echo "Ok - interconnect is updated within 10 minutes and did not fail"
exit 0
else
echo "Critical - $addinfo"
exit 2
fi

This plugin checks to make sure that the interconnect.out file has been updated within the last 15 minutes. We want to make sure the file is fresh.

Then, we look at the last line to see if we find "Verification of node connectivity was successful".

If these two conditions are true, then everything is ok and we can exit with a 0. Otherwise, we throw a critical alert and exit with a 2.

Automatically restart Puppet

Previously, I wrote about how puppet v2 had memory leaks so I had to have Nagios restart puppet. (http://legendofgou.blogspot.com/2013/10/puppet-has-memory-leaks.html)

Well, I finally upgraded to puppet v3 a few months ago and the same plugin doesn't seem to work anymore. Below is the updated Nagios plugin to restart the puppet agent. I'm not saying v3 has memory problems but why not have it monitor and restart dead agents or agents that may be having issues?

#!/usr/bin/perl
# Author: Gou Yang
# Purpose: Checks and restarts puppet if the memory is too high
#          or if puppet is dead

use strict;

my $default;
my $pcount;
my @Results;
my $memResults;

my $logResults;

#puppet seems to hang randomly
#so, if puppet hasn't logged to messages then it should be restarted
$logResults=`sudo /usr/bin/tail -400 /var/log/messages | grep puppet-agent | grep Finished | wc -l`;
if ($logResults < 1) {
  `sudo /etc/init.d/puppet restart`;
  print "WARNING - puppet-agent isn't reporting, restarting puppet";
  exit 1;
}

$default=10;
chomp($pcount=`ps aux | grep "/usr/bin/ruby /usr/bin/puppet agent" | grep -v grep | wc -l`);

if ($pcount < 2) {
 if ($pcount < 1) {
  print "WARNING - No Puppet Process found, restarting puppet";
  `sudo /etc/init.d/puppet restart`;
  exit 1;
  }
   @Results=split(" ",`ps aux | grep "/usr/bin/ruby /usr/bin/puppet agent" | grep -v grep`);
   $memResults=$Results[3];
    if ($memResults > $default) {
    `sudo /etc/init.d/puppet restart`;
    print "WARNING - Memory utilization of $memResults\% is too high, restarting puppet";
    exit 1;
    }
  else{
    print "OK - Memory utilization is $memResults\%";
    exit 0;
  }
}
else{
  print "WARNING - Found $pcount puppet processes";
  exit 1;
}

How to custom install Splunk Forwarder using Puppet - Part 2

This is a continuation from "How to custom install Splunk Forwarder". I recently had to handle special inputs.conf for special servers. So, I had to separate out the inputs.conf file. In the below example, I named the source file "inputs-web.conf" and "puppet-serial-web.txt" to differentiate the original files from these files.

I added these two sections in to their own spunk::forweb class and removed the original two sections from the original splunk class. I then, simply "include splunk" and "include splunk::forweb" in my nodes declaration and I'm all set.

So, in the future, if I had a need for a special inputs.conf for say, a database machine, I can create a new splunk::fordb class and "include splunk" and "include splunk::fordb" in the node declaration for the database machine.

Example:

    file { "/opt/custom/splunk/splunkforwarder/etc/apps/search/local/inputs.conf":
      ensure  => present,
      source  => "puppet:///modules/splunk/splunk-files/inputs-web.conf",
      group   => "splunk",
      owner   => "splunk",
      mode    => "644",
      require => File["/opt/custom/splunk/splunkforwarder/etc/apps/search/local"],
    }
    file { "/opt/custom/splunk/splunkforwarder/.puppet-serial.txt":
      ensure  => present,
      source  => "puppet:///modules/splunk/splunk-files/puppet-serial-web.txt",
      group   => "splunk",
      owner   => "splunk",
      mode    => "644",
      require => File["/etc/pki/tls/private/cert4splunk.p12"],
    }

How to use phpldapadmin to configure OpenLDAP 2.4

This is assuming you currently use phpldapadmin to admin your OpenLDAP server. If you're not, you should think about it. phpldapadmin is super easy to setup and use.

In your phpldapadmin config file, scroll down to the bottom where it says "If you want to configure additional LDAP servers..." and uncomment that section. Change the name value to 'Config' and change the host value to '$yourhost'. Also add 'cn=config' (assuming, thats what you set your config to, otherwise, set it to match your config) to the base value. See Example below.

Modify other variables as you see fit.

Once you got everything completed, log on to your phpldapadmin URL. You should now see a drop down that says 'Server Select'. Select your 'Config' server and log in. Enjoy.

<snip>
/**************************************************************************
 * If you want to configure additional LDAP servers, do so below.         *
 * Remove the commented lines and use this section as a template for all  *
 * your other LDAP servers.                                               *
 **************************************************************************/

$servers->newServer('ldap_pla');
$servers->setValue('server','name','Config');
$servers->setValue('server','host','127.0.0.1');
$servers->setValue('server','port',389);
$servers->setValue('server','base',array('cn=config'));
 </snip>

Here is a screen shot. As you can see, you can now easily edit anything and simply click 'update object'. If you need to import or export, that can also be easily accomplished with the 'import' and 'export' links. No need to run any fancy ldap command line. 

How to setup ppolicy in OpenLDAP 2.4

STEP ONE - Prepare the environment

I'm running CentOS 6.5 and OpenLDAP 2.4.23

Make sure ppolicy schema exists and add it to cn=schema if it does not
Make sure your modulepath is pointing to the path where your ppolicy.la exists
Example: /usr/lib64/openldap


You're also going to have to add an overlay for ppolicy. Here is an LDIF I exported using my test instance. You can use it if you like.

# LDIF Export for olcOverlay={0}ppolicy,olcDatabase={1}bdb,cn=config
# Server: Config (127.0.0.1)
# Search Scope: base
# Search Filter: (objectClass=*)
# Total Entries: 1
#
# Generated by phpLDAPadmin (http://phpldapadmin.sourceforge.net) on February 22, 2014 3:47 pm
# Version: 1.2.3

version: 1

# Entry 1: olcOverlay={0}ppolicy,olcDatabase={1}bdb,cn=config
dn: olcOverlay={0}ppolicy,olcDatabase={1}bdb,cn=config
objectclass: olcOverlayConfig
objectclass: olcPPolicyConfig
olcoverlay: {0}ppolicy
olcppolicydefault: cn=default,ou=ppolicy,dc=test,dc=com
olcppolicyforwardupdates: FALSE
olcppolicyhashcleartext: TRUE
olcppolicyuselockout: TRUE

STEP TWO - Install the password check module
Next step is to install a password checker module, if you want to use a password checker module. It's easy to say no, but I recommend that you do. Anyway, you can get the source from their repository at:
http://tools.ltb-project.org/projects/ltb/files

This was the only password checker module I found when I was googling for one and it seems to work quite well.

Once you extract everything, you will want to edit the Makefile. Set the path to your openldap header files. You probably don't have it installed. If you do, great. If you don't, you can either install the source RPM or you can grab the source from openldap here:
http://www.openldap.org/devel/gitweb.cgi?p=openldap.git;a=summary

On this web page, just find the openldap version you need and click on it. Since I am running OpenLDAP v2.4, I clicked on OPENLDAP_REL_ENG_2_4.

On the next page, I clicked on the snapshot link for "Update dates for release  OPENLDAP_REL_ENG_2_3_23" since I am running v2.4.23

After you've extracted the source code, you need to execute "./configure" followed by "make depend"
That's it. You're done with the OpenLDAP package.

Back to the password checker. Now, I set LDAP_INC in the make file to the location where I extracted openldap source as follows:


LDAP_INC=-I/home/user/openldap-src/include \
         -I/home/user/openldap-src/servers/slapd

That's it. Now, you are ready to compile the module. See what the output is supposed to look like below. By the way, I got an error the first time I ran make. It was because crack/cracklib was not installed. I ran 'yum install cracklib-devel cracklib crack' and that resolved it.

ltb-project-openldap-ppolicy-check-password-1.1 $ make
rm -f check_password.o check_password.so check_password.lo
rm -f -r .libs
gcc -g -O2 -Wall -fpic -DHAVE_CRACKLIB -DCRACKLIB_DICTPATH="\"/usr/share/cracklib/pw_dict\"" -DCONFIG_FILE="\"/etc/openldap/check_password.conf\"" -DDEBUG -c -I/home/user/openldap-src/include -I/home/user/openldap-src/servers/slapd  check_password.c
gcc -shared -o check_password.so check_password.o -lcrack
ltb-project-openldap-ppolicy-check-password-1.1 $ 

You now should have a check_password.o and check_password.so file. Copy or move these two files in to your module path. In my case, I copied them in to  /usr/lib64/openldap.

STEP THREE - Configure your server
Restart your openldap server.

Import the below in to your openldap server. The values I have are for testing purposes. You will need to modify it for your use.

dn: cn=users,ou=ppolicy,dc=company,dc=com
cn: users
objectclass: top
objectclass: device
objectclass: pwdPolicy
objectclass: pwdPolicyChecker
pwdallowuserchange: TRUE
pwdattribute: userPassword
pwdcheckmodule: check_password.so
pwdcheckquality: 2
pwdexpirewarning: 0
pwdfailurecountinterval: 0
pwdgraceauthnlimit: 0
pwdinhistory: 2
pwdlockout: TRUE
pwdlockoutduration: 600
pwdmaxage: 0
pwdmaxfailure: 4
pwdminage: 30
pwdminlength: 8
pwdmustchange: TRUE
pwdsafemodify: FALSE

Edit your ldap.conf and insert or modify the following:

pam_password clear

pam_lookup_policy yes

Note: You need to NOT hash the password on the machine in order to allow openldap to be able to read the password. That way, the password history will be honored. If you set "pam_password md5" or anything other than clear, password history will not be honored. Don't worry about security though, just make sure you are using TLS. Also, don't worry about openldap storing the password in the clear because by default it doesn't. It should store it in SSHA like below. I took this screen shot using phpldapadmin "show internal attributes".

Create the configuration file for password checker at /etc/openldap/check_password.conf

The content of the conf file is fully explained at the LTB site:

http://ltb-project.org/wiki/documentation/openldap-ppolicy-check-password