Categories
Uncategorized

XML Import to mysql via PHP

BLA BLA

<?php
#Database Connection: HOST USERNAME PASSWORD DATABASE
$conn = mysqli_connect("localhost", "DBUSERNAME", "DBPASSWORD", "DBNAME");

#Set affected rows to 0
$affectedRow = 0;

# Load XML Files
$file = glob('xmlfiles/*.xml');
foreach ($file as $input) {

// Strip Path from filename
$filename = preg_replace ( '/xmlfiles\//' , '' , $input );
// Add missing Seconds to DATETIME fields 
$seconds = ('00');

//SimpleXML For reading XML Files and output to SQL
$xml = simplexml_load_file("$input") or die("Error: Cannot create object");
foreach($xml->HUVUDUPPGIFTER as $HUVUDUPPGIFTER)
foreach ($xml->TRAVE as $TRAVE)	
foreach ($xml->ANT_TRAVE as $ANT_TRAVE)
{
    $Id = $filename;
	$T_METOD = $HUVUDUPPGIFTER->T_METOD;
	$RNR = $HUVUDUPPGIFTER->RNR;
	$VONUM = $HUVUDUPPGIFTER->VONUM;
	$LTERM = $HUVUDUPPGIFTER->LTERM;
	$INTNR2SLED1 = $HUVUDUPPGIFTER->INTNR2SLED1;
	$HKODS = $HUVUDUPPGIFTER->HKODS;
	$MOTTPL = $HUVUDUPPGIFTER->MOTTPL;
	$AVLPL = $HUVUDUPPGIFTER->AVLPL;
	$MDAT = $HUVUDUPPGIFTER->MDAT;
	$ANKOM_MPL = $HUVUDUPPGIFTER->ANKOM_MPL . $seconds;
	$AVGICK_MPL = $HUVUDUPPGIFTER->AVGICK_MPL. $seconds;
	$URSP = $HUVUDUPPGIFTER->URSP;
	$TMEDEL = $HUVUDUPPGIFTER->TMEDEL;
	$TFTAG = $HUVUDUPPGIFTER->TFTAG;
	$INTNR2TRP = $HUVUDUPPGIFTER->INTNR2TRP;
	$BILNR = $HUVUDUPPGIFTER->BILNR;
	$LASTID = $HUVUDUPPGIFTER->LASTID;
	$RNRPERLAST = $HUVUDUPPGIFTER->RNRPERLAST;
	$LKF = $HUVUDUPPGIFTER->LKF;
	$NAMNTRP = $HUVUDUPPGIFTER->NAMNTRP;
	$LNAMN = $HUVUDUPPGIFTER->LNAMN;
	$ANT_TRAVE = $ANT_TRAVE;
	$ISS = $TRAVE->ISS;
    $IT = $TRAVE->IT;
    $TVIKT_2 = $TRAVE->TVIKT_2;
    $TARA_2 = $TRAVE->TARA_2;
	$VEDVIKT_2 = $TRAVE->VEDVIKT_2;
	$FPROC1 = $TRAVE->FPROC1;
	$FPROC2 = $TRAVE->FPROC2;
	$FPROC3 = $TRAVE->FPROC3;
	$STYCK = $TRAVE->STYCK;

//SQL QUERRY
$sql = "INSERT INTO XMLIMPORT.UPPGIFTER (Id,T_METOD,RNR,VONUM,LTERM,INTNR2SLED1,HKODS,MOTTPL,AVLPL,MDAT,ANKOM_MPL,AVGICK_MPL,URSP,TMEDEL,TFTAG,INTNR2TRP,BILNR,LASTID,RNRPERLAST,LKF,NAMNTRP,LNAMN,ANT_TRAVE,ISS,IT,TVIKT_2,TARA_2,VEDVIKT_2,FPROC1,FPROC2,FPROC3,STYCK) VALUES ('" . $Id . "','" . $T_METOD . "','" . $RNR . "','" . $VONUM . "','" . $LTERM . "','" . $INTNR2SLED1 . "','" . $HKODS . "','" . $MOTTPL . "','" . $AVLPL . "','" . $MDAT . "','" . $ANKOM_MPL . "','" . $AVGICK_MPL . "','" . $URSP . "','" . $TMEDEL . "','" . $TFTAG . "','" . $INTNR2TRP . "','" . $BILNR . "','" . $LASTID . "','" . $RNRPERLAST . "','" . $LKF ."','" . $NAMNTRP . "','" . $LNAMN . "','" . $ANT_TRAVE . "','" . $ISS . "','" . $IT . "','" . $TVIKT_2 . "','" . $TARA_2 . "','" . $VEDVIKT_2 . "','" . $FPROC1 . "','" . $FPROC2 . "','" . $FPROC3 . "','" . $STYCK . "')";

// MYSQL Warning Logs 
// create logfile incase sometjing gives a warning.
$logfile = "logs/$filename.log";
$to = 'mail@domain';
$subject = "$logfile";
$from = 'mail@domain';
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: '.$from."\r\n".
	    'Reply-To: '.$from."\r\n" .
	        'X-Mailer: PHP/' . phpversion();
$result = mysqli_query($conn, $sql);
    
    if (! empty($result)) {
        $affectedRow ++;
    } else {
        $error_message = mysqli_error($conn) . "\n";
    } 
    if (mysqli_warning_count($conn)) {
   $warning = mysqli_get_warnings($conn);
   do {
	   echo file_put_contents($logfile, "Warning: $warning->errno: $warning->message\n", FILE_APPEND | LOCK_EX);
    } while ($warning->next());
	   $message = file_get_contents ("$logfile");
	mail($to, $subject, $message, $headers);
}
}
}
// How Many records was entered into database or why did it fail.
if ($affectedRow > 0) {
    $message = $affectedRow . " records inserted\n";
} else {
    $message = "No records inserted\n";
}
echo "\n";
echo $message;
if (! empty($error_message)) {
echo ($error_message);
} 
?>

Code for creating the Database

CREATE TABLE IF NOT EXISTS XMLIMPORT.UPPGIFTER (
        Id VARCHAR(255) NOT NULL,
        T_METOD VARCHAR(255) NULL,
        RNR INT NULL,
        VONUM INT NULL,
        LTERM VARCHAR(255) NULL,
        INTNR2SLED1 VARCHAR(255),
        HKODS VARCHAR(255),
        MOTTPL VARCHAR(255),
        AVLPL VARCHAR(255),
        MDAT DATE,
        ANKOM_MPL DATETIME,
        AVGICK_MPL DATETIME,
        URSP VARCHAR(255),
        TMEDEL VARCHAR(255),
        TFTAG INT NULL,
        INTNR2TRP INT NULL,
        BILNR VARCHAR(255) NULL,
        LASTID INT NULL,
        RNRPERLAST VARCHAR(255),
        LKF INT NULL,   
        NAMNTRP VARCHAR(255) NULL,
        LNAMN VARCHAR(255) NULL,
        ANT_TRAVE VARCHAR(255),
        ISS VARCHAR(255),
        IT VARCHAR(255),
        TVIKT_2 DECIMAL(6, 2),
        TARA_2 DECIMAL(6, 2),
        VEDVIKT_2 DECIMAL(6, 2),
        FPROC1 INT NULL,
        FPROC2 INT NULL,
        FPROC3 INT NULL,
        STYCK INT NULL,        
        PRIMARY KEY (Id)
        )
    ENGINE = INNODB
    CHARACTER SET utf8
    COLLATE utf8_general_ci
    COMMENT = 'XML IMPORT DATABASE';
Categories
Uncategorized

The bash scripts

Part 1: Pulse via Openconnect in Debian Buster
Part 2: Use LFTP to batch download files and then delete them on server.
Part 3: mSMTP with Office365
Part 4: The bash scripts

First we have the script that connects to the vpn and downloads everything via lftp

FILE: script.sh
#!/bin/bash
echo "Starting VPN to yourvpn"
sudo systemctl start yourvpn.service
sleep 5
echo "Downloading files"
lftp -u username,password hostname -e "mirror --exclude .profile --exclude .sh_history --Remove-source-files --verbose /remote/path /local/path/; bye"
sleep 2
echo "Stopping VPN"
sudo systemctl stop yourvpn.service
sleep 5
echo ""
echo "Mailing-Log"
/home/xml/loglftp.sh ##see loglftp.sh schript
echo""
echo "DONE"

Second script for checking lftp log for a string in this case the date from the filenames that we download and then email the result to us.

FILE:loglftp.sh
#!/bin/bash

#Function to get Yesterdays date
getdate=$(/usr/bin/date -d "$date -1 days" +"%Y%m%d")

#grep for Date in logfile
output=$(/usr/bin/grep "$getdate" "/path/to/log/lftp.log")

if  [[ -n $output ]]; then
        echo -e "Subject: New XML-files in LFTP Log\r\n\r\n$output" |msmtp --from email@domain -t email@domain
        #echo "$output"
else
        echo -e "Subject: No New XML-Files in LFTP Log\r\n\r\nNothing matching the date $getdate" |msmtp --from email@domain -t email@domain
        #echo "Nothing New"
fi

Now simply add the first script to a crontab. We wanted it to be run everyday @ 00.01

1 0 * * * /path/to/script.sh >/dev/null 2>&1
Categories
Uncategorized

Use mSMTP with Office365

Part 1: Pulse via Openconnect in Debian Buster
Part 2: Use LFTP to batch download files and then delete them on server.
Part 3: mSMTP with Office365
Part 4: The bash scripts

Howto

On Debian Buster install it with

sudo apt install -t msmtp

Now edit /etc/msmtprc

account default
host smtp.office365.com
port 587
tls on
tls_starttls on
tls_trust_file /etc/ssl/certs/ca-certificates.crt
tls_certcheck on
auth on
user user@domain.com
password "Yourpassword"
from "user@domain.com"
syslog LOG_MAIL

Now try to send a testmail

echo -e "Subject: Test Mail5\r\n\r\nThis is a test
mail, let me know if this works" |msmtp --debug --from sender@domain -t reciever@domain

Everything will be logged into /var/log/mail.log

If you want to use PHP to send mail via msmtp you also need to edit /etc/php/7.x/cli/php.ini and /etc/php/7.x/apache2/php.ini and edit sendmail_path

sendmail_path = /usr/bin/msmtp -t

Categories
VPN

Pulse via Openconnect in Debian Buster

Part 1: Pulse via Openconnect in Debian Buster
Part 2: Use LFTP to batch download files and then delete them on server.
Part 3: mSMTP with Office365
Part 4: The bash scripts

Backstory


We needed a way to automate downloads of certain files of a ftp server that is locate behind a Juniper VPN connection. To be able to automate the download of this files we first hade to solve the issue of connecting to the VPN. This was a little bit tricky since I did not get the CLI version of PulseSecure to connect. If I used the UI and connected it worked just fine. So I figured i could use Openconnect instead. It soon showed that the version shipped with Buster does not support –protocol=pulse which is need for this vpn server I am connecting to.

Howto

First of we need a newer version of Openconnect than the one provided in buster.
Therefore we need to enable backports do this by adding the following to your /etc/apt/source.list

#Backports 
deb http://deb.debian.org/debian buster-backports main contrib non-free

Now issue the following commnads

sudo apt update
sudo apt install -t buster-backports openconnect

Openconnect should now be installed
Now to connect to your vpn service type

echo "yourpassword" | sudo openconnect --protocol=pulse -u yourusername --passwd-on-stdin vpnhostname 

If it works you should now have a new interface when looking with “ip addr” and you should be able to access your vpn. If you want to automate the vpn connection this can be done via systemd.

First edit a new file example sudo vi /etc/systemd/system/my-vpn.service
This file should look something like this

[Unit]
Description=Connect to my VPN
After=network.target

[Service]
Type=simple
ExecStart=/bin/sh -c 'echo "yourpassword" | openconnect --protocol=pulse -u yourusername --passwd-on-stdin vpnhostname'
Restart=always

[Install]
WantedBy=multi-user.target

Save and close the file and run the following

sudo systemctl daemon-reload
sudo systemctl enable my-vpn.service
sudo systemctl start my-vpn.service

If you don’t want it to autostart then skip the eanble command.
To stop the service simple use

sudo systemctl stop my-vpn.service
Categories
File-MGMT

Use LFTP to batch download files and then delete them on server.

Part 1: Pulse via Openconnect in Debian Buster
Part 2: Use LFTP to batch download files and then delete them on server.
Part 3: mSMTP with Office365
Part 4: The bash scripts

Backstory

We needed a way to automate downloads of certain files of a ftp server that is locate behind a Juniper VPN connection. See
When VPN was up and running we needed a way to download the files from the server and after the files has been downloaded we need to get them deleted on the server. We also needed to be able to exclude some files that we don’t want to download or be deleted. So wee decided to go with LFTP since we had some experience with it already.

Howto

First install lftp, since we are on Debian Buster we use apt

sudo apt install lftp

We also want all logging for lftp to be logged to a certain place to do that we need to edit ~/.lftp/rc as the user that is going to run the lftp command.
Keep in mind that the user needs write privileges in the folder where it is going to store the logfile.

set log:enabled/xfer yes
set log:file/xfer /pathtolog/lftp.log

And Now we can test if it works.
Here is a simple oneliner that does what we want.

lftp -u username,password hostname -e "mirror --exclude file1 --exclude file2 --Remove-source-files --verbose remotefolderpath localfolderpath; bye"