R language basics

The R programming language can be downloaded then installed from https://cran.r-project.org/index.html

Next download and install R Studio from https://posit.co/download/rstudio-desktop/

Install the R tutorial by opening R Studio, in a console install the package swirl and start the tutorial.

install.packages("swirl")

library(swirl)

swirl()

Following are the initial list of commands learnt from Swirl in lesson 1 to 4

skip(), play(), nxt(), bye(), main(), info()

c(), sqrt(), info()

help commands ?c , ?`:`

getwd(), ls(), list.files(), dir(), args(), getwd(), dir.create(), setwd(), file.create(mytest.R), file.exists(), file.info(), file.rename(from, to), file.path(),setwd(),unlink("testdir",recursive=TRUE)

seq(), seq(1,10, by=0.5), length(), rep(0, times=40),rep(c(0,1,2), times=10)

rep(c(0,1,2), each=10)

Happy R gramming!

Centos 7 monitoring with cockpit

Monitor Centos Linux 7 servers through a web browser. On Centos Stream 8, Cockpit is installed by default on the most parts.

Steps to install cockpit and start the service

These require Linux Administrative user access at the command line.

sudo yum install cockpit cockpit-storaged

sudo systemctl enable --now cockpit.socket

sudo firewall-cmd --permanent --zone=public --add-service=cockpit

sudo firewall-cmd --reload

OR with iptables

sudo iptables -A INPUT -i eth0 -s 0/0 -p tcp --dport 9090 -j ACCEPT

sudo systemctl start cockpit

Access Cockpit

On web browser access URL http://<serverip>:9090

How to add remote MySQL user access.

Users created in MySQL should be for localhost access. In order for a user to be connected from a remote computer, the IP address must be mentioned in its user record. Removing a user access is a matter of deleting that user from the user record.

How to add remote user access

Example, user with login "developer" wants to access MySQL database at server 10.1.1.100 from a laptop at the IP address 10.1.2.23.

The network and database administrator received approval to allow any user to access remotely from the IP address 10.1.2.1 to 10.1.2.224 to the existing database name "tutorial". Here is how its done.

Step 1: Login as server administrator and ensure MySQL can accept connections from remote servers.

MySQL community , edit the file /etc/my.cnf

MariaDB community , edit the file /etc/my.cnf.d/server.cnf

Add the following line, save.

bind-address=0.0.0.0

Restart the MySQL server.

Ensure the server firewall allows access to MySQL port, where default is port 3306.

Example for Centos;

sudo firewall-cmd --new-zone=public --permanent

sudo firewall-cmd --reload

sudo firewall-cmd --permanent --zone=public --add-source=133.155.44.103

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp

sudo firewall-cmd --reload

sudo firewall-cmd --list-all-zones

sudo firewall-cmd --get-services

Step 2: Login to MySQL database as administrator. Add login for remote user and list users.

mysql -u root -p

> CREATE USER 'developer'@'10.2.%' IDENTIFIED BY 'password';

> SELECT user,host FROM mysql.user;

Step 3: Assign login to access database

> GRANT ALL PRIVILEGES ON 'tutorial'.* to 'developer'@'10.2.%';

> FLUSH PRIVILEGES;

> SHOW GRANTS FOR 'developer'@'10.2.%';

Step 4: Monitor connection;

> SELECT user,host, command FROM information_schema.processlist;

How to connect remotely

On the remote server, run the following client command 

mysql -u developer -p -h 10.1.1.100

The MySQL client, it should have the same configuration for SSL as the server to avoid SSL issues.

How to remove remote user access

Login as database administrator and delete login of user and its host as recorded in database;

> DELETE FROM mysql.user WHERE User='developer' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

> FLUSH PRIVILEGES;

How to add datasource to reportserver.net?

Extracting from MySQL and PostgreSql database can be done via Reportserver.net. Other relational databases are also supported as of Reportserver.net version 4.

Configuration

Step 1: Login as administrator user

Step 2: In the menu choose "Datasources", then right click "Datasource Root" and right click to choose "Insert" ->"Relational Database".

Step 3: Enter the following and click "Apply"

• Name
• Description
• Database
• Username
• Password
• URL

The Permission tab provides better control for user to access.

Postgresql database URL

jdbc:postgresql://10.1.1.102/customers

MySQL database without SSL URL

jdbc:mysql://10.1.1.100:3306/customers?useSSL=false

MySQL database URL

jdbc:mysql://10.1.1.100:3306/customers

Details for other databases can be found at https://reportserver.net/en/guides/admin/chapters/Datasources/

Troubleshooting

Host 'host_name' is blocked in MySQL

Too many attempts made to the database can be caused by having or not having ssl. 

Solution:

Try to disable SSL protocol for the client.

Missing Relational Database Driver

Reportserver.net comes with several drivers. To customise datasource, 

Login as administrator and click "File System".

Click "Fileserver Root"->"etc"->"datasources"->"datasources".

Click on tab "Edit file".

It defaults to the following:

<?xml version="1.0" encoding="UTF-8"?>

<configuration>

   <datasource>

      <defaultDatasourceName>Demo Data</defaultDatasourceName>

      <!-- or access via ID -->

      <!-- <defaultDatasource>14</defaultDatasource> -->

   </datasource>

</configuration>

Reference: https://reportserver.net/en/guides/config/chapters/Manual-Installation/

How to read excel data with PhpSpreadsheet

PhpSpreadsheet provides a PHP library of classes to read and write formats such as LibreOffice Calc, Microsoft Office and CSV. The default encoding uses UTF-8.

Ref: https://phpspreadsheet.readthedocs.io/en/latest/faq/

In Laravel 5, 6 and 7, this is available through the Composer. 

composer require phpoffice/phpspreadsheet niklasravnsborg/laravel-pdf mpdf/mpdf:8.1.6 league/csv

OR

composer require phpoffice/phpspreadsheet niklasravnsborg/laravel-pdf mpdf/mpdf league/csv

Here is an example of how to read the first three columns of an xlsx file, where the first row is considered as headers.

Step 1: Import the class for LibreOffice

use PhpOffice\PhpSpreadsheet\Reader\Ods;

Or for Microsoft Excel

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

Step 2: In the function that is to retrieve the data, declare object for LibreOffice

$reader = new Ods();

OR for Microsoft Excel

$reader = new Xlsx();

Step 3: Load the spreadsheet data 

$spreadsheet = $reader->load($path);
$sheet = $spreadsheet->getActiveSheet();

OR, load a sheet with specified name

$spreadsheet = $reader->setLoadSheetsOnly(["Sheet1"])->load($path);

$sheet = $spreadsheet->getActiveSheet();

Step 4: Process rows, and skip header in first row

$users = new Users();

if(!empty($sheet)) {

    foreach ($sheet->getRowIterator() as $row) {

      if ($row->getRowIndex() === 1) {

         continue; //Skip heading

      }

      $cells = iterator_to_array($row->getCellIterator("A", "H"));

      $data = [

            "Column A" => $cells["A"]->getValue(),

            "Column B" => $cells["B"]->getValue(),

            "Column C" => $cells["C"]->getValue(),

      ];

      $users->add($data);

}

The example can be said to retrieve the first 3 columns of the spreadsheet row as fields to create a new user.

Display GIT branch in linux

It is useful to have the current Git branch appear at user prompt. This can be done for Centos Linux BASH by updating the user's configuration file .bashrc in the user's home directory.

Add these lines

git_branch() {

    git branch 2> /dev/null | sed -e '/^[^*]/d' -e 's/* \(.*\)/(\1)/'

}

export PS1="\u@\h \[\e[32m\]\w \[\e[91m\]\$(git_branch)\[\e[00m\]$ "

This will produce a user prompt like this

login@hostname current_directory(git.branch.name)$

How to add remote to GIT repo

Git remote provide information to sync with a Git repository to do stuff like git fetch, git push, git pull.

This information is stored in .git/config. In the case of a new directory, that does not have git, start by configuring it with the command git init. Managing remote can be done by the following commands.

Adding a remote

git remote add <name> <url>

Example

git remote add git_user1 user1@git.myserver.local/repo/package.git

git remote add ssh_user1 ssh://user1@10.1.1.100/repo/package.git

View the current remote and detailed remote information

git remote

git remote -v

Remove a remote

git remote remove <name>

git remote rm <name>

Example

git remote rm remote_user1

Push changes of the specific remote

git push <name>

Example

git push ssh_user1

or to push as a new branch

git push -u ssh_user1 new_branch

Show log of the remote

git remote show <name>

Example

git remote show ssh_user1

Show status of CURRENT branch

git status

Change url of an existing remote

git remote set-url <name> <new url>

Example

git remote set-url remote_user1 ssh://user1@10.1.1.23/repo/package.git