Installing ELK Stack(Elasticsearch,Logstash,Kibana) on CentOS with Sentinl plugin

ELK stack is also known as the Elastic stack, consists of Elasticsearch, Logstash, and Kibana. It helps you to have all of your logs stored in one place and analyze the issues by correlating the events at a particular time.

This guide helps you to install ELK stack on CentOS 7 / RHEL 7.

Components

Logstash – It does the processing (Collect, enrich and send it to Elasticsearch) of incoming logs sent by beats (forwarder).

Elasticsearch – It stores incoming logs from Logstash and provides an ability to search the logs/data in a real-time

Kibana – Provides visualization of logs.

Sentinl –  Sentinl extends Siren Investigate and Kibana with Alerting and Reporting functionality to monitor, notify and report on data series changes using standard queries, programmable validators and a variety of configurable actions – Think of it as a free an independent “Watcher” which also has scheduled “Reporting” capabilities (PNG/PDFs snapshots).

SENTINL is also designed to simplify the process of creating and managing alerts and reports in Siren Investigate/Kibana 6.xvia its native App Interface, or by using native watcher tools in Kibana 6.x+.

 

Beats – Installed on client machines, send logs to Logstash through beats protocol.

Environment

To have a full-featured ELK stack, we would need two machines to test the collection of logs.

ELK Stack

Filebeat

Prerequisites

Install Java

Since Elasticsearch is based on Java, make sure you have either OpenJDK or Oracle JDK is installed on your machine.

Here, I am using OpenJDK 1.8.

Verify the Java version.

Output:

Configure ELK repository

Import the Elastic signing key.

Setup the Elasticsearch repository and install it.

Add the below content to the elk.repo file.

Install Elasticsearch

Elasticsearch is an open source search engine, offers a real-time distributed search and analytics with the RESTful web interface. Elasticsearch stores all the data are sent by the Logstash and displays through the web interface (Kibana) on users request.

Install Elasticsearch.

Configure Elasticsearch to start during system startup.

Use CURL to check whether the Elasticsearch is responding to the queries or not.

Output:

Install Logstash

Logstash is an open source tool for managing events and logs, it collects the logs, parse them and store them on Elasticsearch for searching. Over 160+ plugins are available for Logstash which provides the capability of processing the different type of events with no extra work.

Install the Logstash package.

Create SSL certificate (Optional)

Filebeat (Logstash Forwarder) are normally installed on client servers, and they use SSL certificate to validate the identity of Logstash server for secure communication.

Create SSL certificate either with the hostname or IP SAN.

(Hostname FQDN)

If you use the Logstash server hostname in the beats (forwarder) configuration, make sure you have A record for Logstash server and also ensure that client machine can resolve the hostname of the Logstash server.

Go to the OpenSSL directory.

Now, create the SSL certificate. Replace green one with the hostname of your real Logstash server.

Configure Logstash

Logstash configuration can be found in /etc/logstash/conf.d/. Logstash configuration file consists of three sections input, filter, and the output. All three sections can be found either in a single file or separate files end with .conf.

I recommend you to use a single file for placing input, filter and output sections.

In the first section, we will put an entry for input configuration. The following configuration sets Logstash to listen on port 5044 for incoming logs from the beats (forwarder) that sit on client machines.

Also, add the SSL certificate details in the input section for secure communication – Optional.

In the filter section. We will use Grok to parse the logs ahead of sending it to Elasticsearch. The following grok filter will look for the syslog labeled logs and tries to parse them to make a structured index.

For more filter patterns, take a look at grokdebugger page.

In the output section, we will define the location where the logs to get stored; obviously, it should be Elasticsearch.

Now start and enable the Logstash service.

You can troubleshoot any issues by looking at Logstash logs.

Install & Configure Kibana

Kibana provides visualization of logs stored on the Elasticsearch. Install the Kibana using the following command.

Edit the kibana.yml file.

By default, Kibana listens on localhost which means you can not access Kibana interface from external machines. To allow it, edit the below line with your machine IP.

Uncomment the following line and update it with the Elasticsearch instance URL. In my case, it is localhost.

Start and enable kibana on system startup.

Install Sentinl plugin:

Install and Configure Filebeat

There are four beats clients available

  1. Packetbeat – Analyze network packet data.
  2. Filebeat – Real-time insight into log data.
  3. Topbeat – Get insights from infrastructure data.
  4. Metricbeat – Ship metrics to Elasticsearch.

To analyze the system logs of the client machine (Ex. client.lintel.local), we need to install filebeat. Create beats.repo file.

Add the below content to the above repo file.

Now, install Filebeat using the following command.

Set up a host entry on the client machine in case your environment does not have DNS server.

Make an host entry like below on the client machine.

Filebeat (beats) uses SSL certificate for validating Logstash server identity, so copy the logstash-forwarder.crt from the Logstash server to the client.

Skip this step, in case you are not using SSL in Logstash.

Filebeat configuration file is in YAML format, which means indentation is very important. Make sure you use the same number of spaces used in the guide.

Open up the filebeat configuration file.

On top, you would see the prospectors section. Here, you need to specify which logs should be sent to Logstash and how they should be handled. Each prospector starts with – character.

For testing purpose, we will configure filebeat to send /var/log/messages to Logstash server. To do that, modify the existing prospector under paths section.

Comment out the – /var/log/*.log to avoid sending all .log files present in that directory to Logstash.

Comment out the section output.elasticsearch: as we are not going to store logs directly to Elasticsearch.

Now, find the line output.logstash and modify the entries like below. This section defines filebeat to send logs to Logstash server server.lintel.local on port 5044 and mention the path where the copied SSL certificate is placed

Replace server.lintel.local with IP address in case if you are using IP SAN.

Restart the service.

Beats logs are typically found syslog file.

Access Kibana

Access the Kibana using the following URL.

http://your-ip-address:5601/

You would get the Kibana’s home page.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 - Kibana Starting Page
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Kibana Starting Page

On your first login, you have to map the filebeat index. Go to Management >> Index Patterns.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 - Management
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Management

Type the following in the Index pattern box.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 - Create Index Pattren
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Create Index Pattern

You should see at least one filebeat index something like above. Click Next step.

Select @timestamp and then click on Create.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 - Configure Timestamp
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Configure Timestamp

Verify your index patterns and its mappings.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 - Index Mappings
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Index Mappings

Now, click Discover to view the incoming logs and perform search queries.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 - Discover Logs
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Discover Logs

You can see sentinl plugin here

sentinl_annotation

That’s All.

 

Reference list:

https://github.com/sirensolutions/sentinl

https://www.itzgeek.com

How to Setup Redis Cluster from Source

What is redis

Redis is an open source in-memory database. It stores data in key-value format. Because of residing in memory, redis is an excellent tool for caching. Redis provides a rich set of data types. This gives redis upper hand over Memcached. Apart from caching, redis can be used as distributed message broker.

Redis Cluster and Sentinels

To achieve high availability, redis can be deployed in cluster along with Sentinels. Sentinel is a feature of redis. Multiple sentinels are deployed across redis clusters for monitoring purpose. When redis master goes down, sentinels elect a new master from slaves. When old master comes up again, it is added as slave.

Another use case of clustering is a distribution of load. In high load environment, we can send write requests to master and read request to slaves.

This tutorial is specifically focused on Redis Cluster Master Slave model. We will not cover data sharding across cluster here. In data sharding, keys are distributed across multiple redis nodes.

Setup for tutorial

For this tutorial, we will use 3 (virtual) servers. On one server Redis master will reside while other two servers will be used for slaves. Standard redis port is 6379. To differentiate easily, we will run master on 6379 port and slaves on
6380 and 6381 ports. Same will be applied for sentinel services. Master sentinel will listen on 16379 port while slave sentinels will be on 16380 and 16381.

Lets put this easy way.

This tutorial is tested on CentOS 6.9. For CentOS 7.X, check below Notes Section.

Installation

We will follow same installation steps for setting up of all servers. Only difference will be in configurations.

  • Step 1: Grab redis source, make and install
  • Step 2: Setup required directories
  • Step 3: Configure redis master
  • Step 4: Configure redis master sentinel
  • Step 5: Add low privileged user to run redis
  • Step 6: Setup init scripts
  • Step 7: Start service

Server 1 (Redis Master)


Install Redis

Setup required directories

Configure redis master

Edit config file /etc/redis/6379.conf in your favorite editor and change below options.

Configure redis master sentinel

Add config file for sentinel at /etc/redis/sentinel_6379.conf. Open a file and add below content

Add non-privileged user

Setup init scripts

You can find sample init scripts in Notes section below.

Start service

Server 2 (Redis Slave 1)


Install Redis

Setup required directories

Configure redis slave 1

Edit config file /etc/redis/6380.conf in your favorite editor and change below options.

Configure redis slave 1 sentinel

Add config file for sentinel at /etc/redis/sentinel_6380.conf. Open a file and add below content

Add non-privileged user

Setup init scripts

You can find sample init scripts in Notes section below. Change $HOST and $PORT values accordingly

Start service

Server 3 (Redis Slave 2)


Install Redis

Setup required directories

Configure redis slave 2

Edit config file /etc/redis/6381.conf in your favorite editor and change below options.

Configure redis slave 2 sentinel

Add config file for sentinel at /etc/redis/sentinel_6381.conf. Open a file and add below content

Add non-privileged user

Setup init scripts

You can find sample init scripts in Notes section below. Change $HOST and $PORT values accordingly

Start service

Sentinel Testing

Redis Fail-over Testing

For fail-over testing, we can take down redis-master either using init script or below command.

Also we can force sentinel to run fail over using below command

Sample init scripts

Redis Init Script

Sentinel Init Script

Notes

Security

  • NEVER EVER run redis on public interface
  • If redis is deployed in cloud environment like AWS, set up security groups/firewalls carefully. Most of times, cloud providers use ephemeral ips. Because of ephermal ips, even redis is bound to private ip, it can be accessed over public interface.
  • For more security, dangerous commands can be disabled(renamed). But be careful with them in cluster environment.
  • Redis also provides simple authentication mechanism. It is not covered here because of scope.

Sentinel management

  • During redis fail-over, config files are rewritten by sentinel program. So when restarting redis-cluster, be careful.

Sources

  • https://redis.io/topics/cluster-tutorial
  • https://redis.io/topics/security
  • https://redis.io/commands/debug-segfault

What is DBF file? How to read it in linux and python?

What is DBF files ?

A DBF file is a standard database file used by dBASE, a database management system application. It organises data into multiple records with fields stored in an array data type. DBF files are also compatible with other “xBase” database programs, which became an important feature because of the file format’s popularity.

Tools which can read or open DBF files

Below are list of program which can read and open dbf file.

  • Windows
    1. dBase
    2. Microsoft Access
    3. Microsoft Excel
    4. Visual Foxpro
    5. Apache OpenOffice
    6. dbfview
    7. dbf Viewer Plus
  • Linux
    1. Apache OpenOffice
    2. GTK DBF Editor

How to read file in linux ?

“dbview” command available in linux, which can read dbf files.

Below code snippet show how to use dbview command.

 How to read it using python ?

dbfread” is the library available in python to read dbf files. This library reads DBF files and returns the data as native Python data types for further processing.

dbfread requires python 3.2 or 2.7.  dbfread is a pure python module, so doesn’t depend on any packages outside the standard library.

You can install library by the command below.

The below code snippet can read dbf file and retrieve data as python dictionary.

You can also use the with statement:

By default the records are streamed directly from the file.  If you have enough memory you can load them into a list instead. This allows random access

 How to Write content in DBF file using python ?

dbfpy is a python-only module for reading and writing DBF-files.  dbfpy can read and write simple DBF-files.

You can install it by using below command

The below example shows how to create dbf files and write records in to it.

Also you can update a dbf file record using dbf module.

The below example shows how to update a record in a .dbf file.

 

Asynchronous DB Operations in Twisted

Twisted is an asynchronous networking framework. Other Database API Implementations have blocking interfaces.

For this reason, twisted.enterprise.adbapi was created. It is a non-blocking interface,which allows you to access a number of different RDBMSes.

General Method to access DB API.

1 ) Create a Connection with db.

2) create a cursor.

3) do a query.

Cursor blocks to response in asynchronous framework. Those delays are unacceptable when using an asynchronous framework such as Twisted.
To Overcome blocking interface, twisted provides asynchronous wrapper for db module such as twisted.enterprise.adbapi

Database Connection using adbapi API.

To use adbapi, we import dependencies as below

1) Connect Database using adbapi.ConnectionPool

Here, We do not need to import dbmodule directly.
dbmodule.connect are passed as extra arguments to adbapi.ConnectionPool’s Constructor.

2) Run Database Query

Here, I used ‘%s’ paramstyle for mysql. if you use another database module, you need to use compatible paramstyle. for more, use DB-API specification.

Twisted doesn’t attempt to offer any sort of magic parameter munging – runQuery(query,params,…) maps directly onto cursor.execute(query,params,…).

This query returns Deferred, which allows arbitrary callbacks to be called upon completion (or failure).

Demo : Select, Insert and Update query in Database.

Here, I have used MySQLdb api, agentdata as a database name, root as a user, 123456 as a password.
Also, I have created select, insert and update query for select, insert and update operation respectively.
runQuery method returns deferred. For this, add callback and error back to handle success and failure respectively.

DBSlayer

DBSlayer is a simpler way to proxy mysql.

DBSlayer can be queried via JSON over HTTP, and the responses can be given in either one of the following supported languages : JSON, PHP and Python which makes processing the database results.

Multi-threaded server written in C.

Features :

  • Reduce configuration
  • Reduce dependencies
  • Handle failovers
  • Simple load balancing
  • Easy to monitor
  • Minimal performance overhead
  • Work in different configuration scenarios
  • Support different programming languages

Installing DBSlayer :

Database URI :

http://machine:port/db?URLENCODED(JSON OBJECT)

http://machine:port/dbform?URLENCODED(HTML FORM)

Parameters :

SQL – SQL to execute

Example Request :

Example Response :

Example for python :

Start dbslayer :

This starts up a DBSlayer daemon on 9090 (this default port can be changed which acts as a proxy for the backend mysql server. this proxy can be queried via JSON over HTTP).

Stop dbslayer :

Other URI/API endpoints :

http://machine:port/stats [Queries per second]

http://machine:port/stats/log [Last 100 requests]

http://machine:port/stats/errors [Last 100 error]

http://machine:port/shutdown [Only from localhost]

How to delete records by disabling foreign key checks or constraints in mysql

If you have a foreign key constraints on your table, you can’t delete records from parent table, but you can delete from child table. The main reason we use the foreign key constraints is to maintain data integrity.

Some times you many need to disable this kind of enforcing against some operations like delete. To do so, you have to set mysql server system variable foreign_key_checks

Now, you can delete or update with out any restriction because of foreign key constraint. But you will mess up the data integrity.

You can reset it back to enforce for integrity

There are global and local system variables. If you set the global variables it applies to all clients which are connected. If you change the local variable it applies to that current client session only. Here is how you can query local and global variable.

To sent global variable you just need to add global , where to set local you need to local. By default local or session variable will be changed

To set global variable

NOTE: Make sure you to revert the variable back to original state to maintain data integrity in your database.

 

MySQL tips for beginners and intermediate

This article will take you through few MySQL tips those are pretty helpful.

We will start with few useful statements,

Do you know, if you are working offline with mysql  and you need some help on syntax. You can get it right away on terminal  with simple statement  help followed by command or statement.

help

The above statement in the mysql console will give you the whole syntax and it’s usage.

Here is  an example:

For suppose you want know available list of date and time functions. Here is how you will get,

 

status

The  status statement in the mysql console will give you the quick overview about connection, mysql server and client details

Pager

Are you frustrated with querying lot of records to analyze in mysql conventional command line client console? No worries,  can set the pager in mysql client console. You can set it to either less or more. Here is the way to do it,

Let’s say if you set pager to less, you will see your query output like wise when you read file using less command. You try the following statements.

Reset pager:

you can reset your pager using \P with arguments;

Processlist

The following statement show processlist will give you overview about all connected clients and their work, even queries they are being executed,

explain

An other statement which very helpful in optimizing your queries and schema is explain . You can’t imagine how helpful this statement is, it will show how mysql is using indexes how many rows or records it’s searching for a result. Based on the report you can optimize the queries and even you can add indexes to table if required to speed up results.

Here is simple example,

You will see a record for each table from which you are selecting or used in query. Find more about explain here

 

Other than few handy statements explained above, now we will go through few tips to avoid common pit falls in MySQL.

  • Always try to avoid creating the user with wildcard host . Doing so will mitigate the possible risks of getting hacked.
  • Only give required permissions to the user, so other databases or things will not get screwed up either intentionally or accidentally because of user or script.
  • When you are deleting something from production or from important place, use transactions. Here is how you will do it,

This is useful to avoid common problems caused by either typos or accidental mistakes. Before you type commit, you can always review your changes and rollback if changes are unintended.

User Account Management In MySQL

You need to have a user account to execute queries on data. When you install mysql you will prompted to enter root user details or some times you might have installed mysql with root user and no password. It is recommended to have a password for root user after completion of installation and it is best practice to use different set of mysql users to serve different purposes instead of root user to mitigate security vulnerabilities.

To execute any of the following instructions to create and edit user you have to connect with mysql server using mysql client.

mysql -u root -p

Assuming your mysql server is on localhost else specify server with option -h

Create User

To create user we use the statement CREAT USER.  Find the definitive syntax here

mysql>  CREATE USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password‘;

In mysql every user is associated with host. Here it is localhost specified in the statement above. It meas user Bob can only login from host localhost(same machine)

After your create user, he will be assigned the default permissions. To let him access specific database or table you have to grant him the permissions. To do so, we use the statement GRANT

To grant all permissions on all database,

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘Bob’@’localhost’;

To grant all permissions on specific database,

mysql> GRANT ALL PRIVILEGES ON db_name.* TO ‘Bob’@’localhost’;

Once you are done with creating user. Reload privileges using

mysql> flush privileges;

This command will reload users and their privileges.

Change User Password

User can be altered using the statement ALTER USER. To change the password of an user, use the following statement

mysql> ALTER USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password’;

You can change your own password by following statment,

mysql> ALTER USER USER() IDENTIFIED BY ‘password’;

You can also change the password of an user using following two statements as well,

SET PASSWORD
GRANT

Change password using SET PASSWORD

mysql> SET PASSWORD FOR ‘Bob’@’localhost’ =’password’;

Note: Before MySQL 5.7.6 you have to give hash to the statement SET PASSWORD using password function.

Change password using GRANT

mysql> GRANT ALL PRIVILEGES ON  *.* TO ‘Bob’@’localhost’ IDENTIFIED BY ‘new_password’;

List All Users

Following query will list all available users

mysql> select User, Host from mysql.user;

Change host associated with user account

You can change host of any user by updating the Host column of table mysql.user for that user. With out having permissions to update this table you can’t change the host of any user

mysql> update mysql.user set Host=’host’ where Host=’old_host’;

Delete User

To delete an user, we use the statement DROP USER.

mysql> DROP USER ‘Bob’@’localhost’; 

 

How to reset wordpress user password using mysql

WordPress uses md5 hash for password. We can reset wordpress user password using mysql single query.

To reset password using mysql,

Login to mysql and select the wordpress database

Following single query is helpful to reset wordpress user password

update wp_users set user_pass=md5(‘new_password’) where user_login=’username’ ;

Replace, new_password and username with your password and username.