Make Gadgets Work

Network routing Router - Router is the device at home that connects all devices in your house to the internet. It does so by assigning IP address to each of your device but this IP address is only relevant for devices connected to this router. What this means is that if you use an IP address allocated by this router from a coffee shop and you are not connected to your home router then you will not be able to reach the device that IP address belonged to. For this reason these IP addresses are called internal IP addresses. Now what happens is when you try to access a website, your device sends the request to your router, router in turn sends it to the router of your ISP which in turn eventually connects to the server that is hosting the website. The information flows back through same route and your router finally gets the information and then passes it back to your device and it is able to do so because it identifies your device based on the Internal IP address it assigned to your device. By default pretty much all routers are configured to allocate these Internal IP Addresses using DHCP which is just fancy term for allocating a random number to the device. They do also provide the facility to reserve an IP address for a particular device if we tell it to do so - This is called "assigning a static IP on the router" in technical speak. This is important when we are trying to host service from home based server and is explained next Many ISP provided routers are locked in and it will serve you well to get a router and flash it with an open source firmware like DD-WRT. Internal LAN set-up - Ensure that on your internal LAN network you assign static IP address to device that will be running the server. If your router allows for LAN Domain to be configured then do so and allocate appropriate LAN Hostname as it makes it easier to access the server from within LAN than just by trying to remember the IP address. Port Forwarding - When the request comes from Internet, it will be with http, ftp, smtp those kind of headers. These headers operate on standard ports. However for security as well as application related issues, the ports on actual server within your home network can be significantly different. All this needs to be translated for communication to be completed and application to provide requested information. Port Forwarding at router level is simply us telling router which IP address to pass on the received request based on the port that request is trying to access. For example http requests are on port 80 so if we tell router that any requests coming on port 80 must be forwarded to a laptop in your house with internal ip address of 192.168.1.44 then port forwarding should be the way to do so. Internet to Home Use of Dynamic DNS Services Ensure that the router is informing your Dynamic DNS provider with latest IP address assigned by your ISP. Use of duckdns.org or dtdns.com etc Use of DNS-O-Matic to dynamically update the External IP address for your home router as soon as it is changed by the ISP. Allocate the Dynamic DNS to DNS name purchased from Domain Name registrar like Namecheap etc. Hosting a service from Home Server: Question we are first going to answer is - If we want to host on server from home. what do we need to do? 1. Ensure you are able to do changes to your router setting that allows you to: a. Create port forwarding rules b. Update the DynamicDNS each time your ISP changes External IP address for your router. The concept for these two topics are covered in previous sections but we will revisit in greater detail later. For now let's start at something even more basic. 2. We need to decide the operating system and web server we want to use? The request from Internet when it reaches the router it is just dealing in numbers and not strings so it does not really what information to send back unless it gets it back from the device where the information resides. In order for the device that has the information to provide it such that router can send it out, we need to have these web servers that do the job of translating the information into a format that the router and eventually the device can understand. So as an example if the request was on http protocol, it would mean it is for a web page and router knows that it is for port 80, and will forward it to the IP address you assigned - Router is assuming ofcourse that you know what you are doing. In other words it is your (human) responsibility to ensure that when this request lands on said IP address someone is there to receive it - that someone is the "Web Server" we are now talking about. Web Servers The two commonly used web-servers are: Apache Nginx There are other servers too for various other scenarios but the most popular options are the two above. QUESTION: Can we have both web servers running on same machine? ANSWER: Yes, the routing of information will be similar to the flow below: Internet -> Home Router -> Primary Server -> Other servers in home network accessed through reverse proxy and connected to world only through Primary Server. Operating System Assuming that we decide to go with Apache Server. You will need to install it on your laptop. This brings us to the first controversial topic of operating system that this laptop is using. It can be anything from Windows, Mac to a range of Linux Distros. As we are talking open source, lets assume it is will be a linux distro but there are so many to chose from so which one? A safe choice is to opt for Debian based distro as it is easy to start with. In that too Debian 9 (code named: Stretch) at the time of writing is latest stable version and is most recommended. I will assume for the purpose of this article that the reader will use Debian 9 (Stretch). I will also assume that reader is not aware of working on a Linux environment. Advanced users may find some of the information here obvious or boring as I explain few things at length. Prepare the laptop: Step 1 - Find out whether the laptop is 32 bit or 64 bit. Step 2 - Download a copy of the Debian 9 (Stretch) applicable to the specific version of your laptop (32 bit / 64 bit) Step 3 - Create a Live DVD / USB and test drive the OS Step 4 - Install it You will be asked to provide root password, username and user password. Make note of each of these as you will need them later. Step 5 - Update, Upgrade and Dist Upgrade Open the terminal of the laptop and type following commands: sudo apt update sudo apt upgrade sudo apt dist-upgrade ....More to come...Work in progress.

Home Networking

Network routing Router - Router is the device at home that connects all devices in your house to the internet. It does so by assigning IP address to each of your device but this IP address is only relevant for devices connected to this router. What this means is that
On my post MySQL function to calculate elapsed working time I was asked in comments if the assumptions can be reversed such that given start date, starting time and closing time of site and the elapsed working hours, function should return the closed date. I was convinced that it will be possible to achieve this with minor tweaks to original logic and so I only concentrated on original code and how to tweak it to achieve the result. It is very likely that there might exist a more elegant solution but frankly I did not have a usecase I did find a usecase afterall - Clue was in redefining the problem description for this. Presented below is the function that with my minimal testing seems to give correct results. Feel free to try it and as always any feedback is welcome. :) Problem Description: Find out the WorkDay date by when a ticket must be closed given: Country of site for which incident was logged. Time and Date when Incident was logged. Elapsed working hours in decimal. (Ten and a half hours as 10.5 and so on) Opening time of the site for which the incident was logged. Closing Time of the site for which incident was logged. SLA Type - Is the output date to be based purely on number of hours or based on SLA in days (eg: Next Business Day). Assumption: It is assumed that opening and closing times are same on all working days and that all the sites are closed on holidays and weekends. Example of problem Let's say an incident was logged on "Friday 23rd Feb 2018 at 15:00" for a site in the "UK" which opens between 08:00 to 16:00. Now if the SLA is purely based on hours i.e. Tickets must be closed withing 16 hours then for the above incident function should calculate the closing date as: 2018-02-27 15:00 = 1 hour on Friday, then skip Saturday and Sunday, 8 hours on Monday and 7 hours on Tuesday the 27th of Feb 2018. However, if the SLA is based on number of days, then 16 hours will translate to an SLA of next business day hence in this case function should return 2018-02-26 16:00 which is a Monday end of day and the next business day for this incident. The use case really is in creating a report that shows the target closed date based on SLA. Specifically Next Business Day SLA, which isn't direct hours calculation and is really always the next working day but based on cut-off time when the call is logged. So a call logged on a working day 1 after cut-off will be counted as logged on working day 2 and hence will need to be closed by working day 3 whereas a call logged within cut-off of working day 1 will need to be closed by close of business working day 2. This is turn can allow for a comparison between Target Closed Date from this function and Actual Closed Date to show SLA failures where Actual Closed Date is greater than Target Closed Date. Pre-Requisites: Follow from previous post. Function code and explanation CREATE DEFINER=`ankit`@`%` FUNCTION `get_closed_date_given_start_time`( `param_country` VARCHAR(20), `assigneddatetime` VARCHAR(20), `param_elapsedhours` VARCHAR(10), `starttime` VARCHAR(20), `endtime` VARCHAR(20), `sla_type` VARCHAR(10) ) RETURNS TEXT LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Set @starttime = starttime; Set @endtime = endtime; Select time_to_sec(timediff(@endtime,@starttime))/3600 into @maxhoursaday; Set @assigneddate = assigneddatetime; Set @timecount = param_elapsedhours; Set @timevar1 = @assigneddate; Set @nextdate = @assigneddate; Set @timevar2 = null; Set @param_country = param_country; ############ Check if the assigned time was before the starttime or closed time was after the endtime provided ############# Set @checkstart = null; Set @checkend = null; Set @slatype = sla_type; Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@slatype = 'H') then if (@assigneddate < @checkstart) then SET @nextdate = @checkstart; end if; else if (@assigneddate < @checkend) then SET @nextdate = @checkstart; end if; end if; Set @count = @timecount; while @count>0 do select weekday(@nextdate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; #Check if the date stored in nextdate (which is assigneddate on first run of while loop) is a holiday and set the holiday flag if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in nextdate variable is neither weekend nor a holiday if (@count = @timecount) then #Check if it is first run.ie. if nextdate is assigneddate Set @timevar1 = @nextdate; #assign assigndate to variable timevar1 SELECT CONCAT(SUBSTRING_INDEX(@nextdate, ' ', 1), ' ',@endtime) INTO @timevar2;#get site closing time on assigned date and store it on to timevar2 else Select CONCAT(substring_index(@nextdate,' ',1),' ',@starttime) into @timevar1; SELECT CONCAT(substring_index(@nextdate,' ',1), ' ', @endtime) INTO @timevar2; end if; SELECT LEAST(Greatest(((TIME_TO_SEC(TIMEDIFF(@timevar2, @timevar1))) / 3600),0),@maxhoursaday) INTO @timecounttemp; Set @count = @count - @timecounttemp; end if; if @count > 0 then Select adddate(concat(substring_index(@nextdate,' ',1),' ',@starttime),1) INTO @nextdate; else Select SUBSTRING_INDEX(@nextdate, ' ', 1) INTO @nextdate; end if; end while; Set @finaldate = null; Select concat(@nextdate,' ',substring_index(addtime(@endtime,sec_to_time(3600*@count)),':',2)) INTO @finaldate; RETURN @finaldate; END Calling the function Function will expect 6 parameters and with specific format as explained below: param_country - This is the country code as specified in holiday table assigneddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2018-02-23 15:00:00 param_elapsedhours - This must be provided in decimal hours format. Bear in mind that if SLA Type is not H, you need to understand the number of working hours in allowed number of days in SLA. So for 8 working hours in a day: Same day SLA will have 8 hours Next Business Day will have 16 hours(2*8) A 2 day SLA will have 24 hours and so on starttime - This must be in the format %H:%i. So for our example it will be 08:00 endtime - This must be in the format %H:%i. So for our example it will be 16:00 sla_type - This must be 'H' if the sla is based on hours or anything else (say 'D') if SLA is based on days. The call for this function will be as below: For hours SLA: Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','16','08:00','16:00','H'); This will give an output of 2018-02-27 15:00 For Next Business Day SLA: Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','16','08:00','16:00','D'); This will give an output of 2018-02-26 16:00 For just calculating next working day date given number of hours: Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','10.5','08:00','16:00','H'); This will give an output of 2018-02-27 09:30

MySQL Function to calculate closing WorkDay date given elapsed working time

On my post MySQL function to calculate elapsed working time I was asked in comments if the assumptions can be reversed such that given start date, starting time and closing time of site and the elapsed working hours, function should return the closed date. I was convinced that it will
I have found the recent ghost upgrades quite painless but there have been few hiccups for last two times so I kept a record of what helped and it is as listed below: General Update should be as simple as: cd /var/www/html/ghost/ ghost update If after this step, there are any errors or an indication to update ghost-cli, following command should be used. sudo npm install -g ghost-cli After this if there are issues accessing the blog over internet, we may need to do a bit of checks. Logical sequence is to first check that access for all folders is right and proper. If it needs to be updated, command to be used is: cd ghost sudo chown -R <your username>:www-data . cd content/ sudo chown -R ghost:ghost images/ If there are any errors when starting ghost, following command is indicated by the ghost-cli and it does help. ghost setup linux-user systemd If still accessing the blog is an issue following command will list the ghost log file: ghost log If there are database migration errors on log file, following commands may help: sudo npm install -g knex-migrator ghost setup migrate ghost setup migrate does work very well and must be remembered. If you have digitalocean setup such as mine, memory can be an issue you may want to restart the virtual machine and reload the swap files. sudo reboot now sudo swapon /swapfile1 sudo swapon /swapfile2 If update if failing with the message not enough memory try running the following: ghost update --no-check-mem If there are still issues accessing the blog with error 503, check the apache logs: sudo nano /var/log/apache2/error.log If issue is with accessing the upstream ghost server. Try changing the port on ghost config and updating the apache conf files. ##Change Directory to ghost install cd /var/www/html/ghost ##Stop the ghost server ghost stop ##Change port to another number nano config.production.json ##Change directory apache2 server cd /etc/apache2/sites-available/ ##Open the ghost.conf file and change the localhost port to same number that was changed in config.production.json sudo nano ghost.conf ##Open the ghost-le-ssl.conf file and change the localhost port to same number that was changed in config.production.json sudo nano ghost-le-ssl.conf ##Disable and enable the conf files on apache. sudo a2dissite ghost.conf ghost-le-ssl.conf sudo a2ensite ghost-le-ssl.conf ghost.conf sudo service apache2 reload ##Finally change directory to your ghost install and start ghost server. cd /var/www/html/ghost ghost start

Ghost Upgrade errors and fixes (1.19.x)

I have found the recent ghost upgrades quite painless but there have been few hiccups for last two times so I kept a record of what helped and it is as listed below: General Update should be as simple as: cd /var/www/html/ghost/ ghost update If after this
A friend of mine today had an issue. He had created a template for some really complex calculations and to ensure he does not mess up with the forumlae by mistake he had password protected sheets and cells. He had done this back in 2012 and now he wanted change something in there but he had forgotten the password so he asked me if I can help. Now I do not know much about how it could be done on windows or on excel but I knew a small trick on LibreOffice so I asked him to send me that excel file by email. I then took following steps: Opened the excel file Locked.xls in LibreCalc. Saved it as an Locked.ods file. From file browser, right click on newly saved Locked.ods file -> Open With -> Archive Manager as shown below. Now open the content.xml Find table:protected="true" and Replace All with table:protected="false" Save the xml file. Now open the Locked.ods in LibreCalc and save it as Unlocked.xlsx This should do the trick and unlock all password protected sheets and cells to be freely modified.

Unprotect Sheets in Libre Calc, Excel

A friend of mine today had an issue. He had created a template for some really complex calculations and to ensure he does not mess up with the forumlae by mistake he had password protected sheets and cells. He had done this back in 2012 and now he wanted change
Right then, the Ghost V1.0 was out a while back and they made Ghost 0.11.x an LTS so I was not in any rush to upgrade too. I have not had much time to sort this out for a while and two days back when I finally came around to check how to upgrade, my first moment of concern was that officially supported stack is for NGINX. I have moved my blog to the Apache Stack on DigitalOcean and while on my sandbox environment I still have NGINX, that is not a place I want to host my blog from. Anyhoo, I realised soon enough that while not officially supported it s easy to bypass the restrictions so I went ahead. The upgrade itself couldn't have been simpler considering the major version bump. The answer to the question "Was it worth it?" is something we will have to wait and see although I am liking what I see except for the initial hiccups. EDITED AFTER THE POST Boy oh boy - just after I finished this post I saw the latest version of Ghost V1.12 is out and it was such a painless process compared to past. Just a simple command 'ghost update' and job done. That itself makes this whole pain kind of worth it. OK so the steps I took are as below: Backup Download and Install Setup Wizard Configure Apache Restore Tweak Backup We will take the back-up from front end for all the posts and we will also backup on the server the entire directory where old instance of the blog is residing. To take backup of all the content and download it in a json file, open your ghost site on a browser, navidate to "Settings" and then click on "Export". Next for the backup of folder on the server itself. To do this issue the following commands on the terminal. #Updateand upgrade the OS repo sudo apt-get update sudo apt-get upgrade #Stop the ghost server pm2 stop Ghost "assuming Ghost is the pm2 id for the site" # Change directory to web-server root cd /var/www/html/"path to your ghost directory say 'ghost' " # Create a new directory for backup sudo mkdir old_ghost_bkp sudo mv ghost old_ghost_bkp # Recreate the ghost directory sudo mkdir ghost cd ghost # Give right privileges to the new directory sudo chown -R <your username>:www-data . Download and Install As we are already in the right directory lets get on with installing the latest version of Ghost using npm. sudo npm install -g ghost-cli #Make sure you are in the directory where new ghost is to be installed. #If you have followed all commands so far, you will already be in #required the directory ghost install It is at this point that you will have to deviate from official guide if you have Apache instead of NGINX. You will be prompted by the installer that it could not find NGINX and do you still want to continue. Default is "No" so make sure you enter "Y" and then press enter. For me rest of the install went smoothly. Setup Wizard Immediately after the install is complete, you will be presented with following questions: Please note that if you have configured SSL using LetsEncrypt as explained in previous posts on this blog then even if you are using https, the answer to blog url must be the with http and not https. For example: I gave http://mgw.dumatics.com and not https://mgw.dumatics.com Enter your blog url: http://your.blog.url Enter your MySQL Hostname: localhost Enter your MySQL Username: root Enter your MySQL Password: "your mysql root password" Enter your Ghost database Name: "a relevant name - for security reasons you may want to keep it different from your blog name" Do you wish to set up Nginx: no Do you wish to set uo Ghost MySQL User: yes Do you wish to set up Systemd: yes Do you want to start Ghost: yes Please do note that the response on line 6 above to "Setup Nginx" must be "no" After the questions are complete you will get a notification You can access you blog at http://your.blog.url. At this point, it is best to see which port is configured by ghost CLI for this installation. you can do so by checking the configuration file like so: nano config.production.json You can change the port if you like but if it is different than the port you originally had for old version of ghost you can either change it here or you need to change Apache conf file in next step. If you do decide to change the port here, then there should be no need to carry out the next step - Configure Apache. Configure Apache Assuming that the port in Ghost config file was 1234, there will be some changes that you will need to make in Apache conf files like so: cd /etc/apache2/sites-available/ sudo nano ghost.conf Now change the port on ProxyPass and ProxyPassReverse to be same as what is in the config.production.json file and save it by pressing Ctrl+x and y.- so for this example it will be changed to 1234 and change will look as below: ProxyRequests off ProxyPass / http://localhost:1234/ ProxyPassReverse / http:/localhost:1234/ Now open the ssl config file for the site using commands below and make the same changes as above. sudo nano ghost-le-ssl.conf TIP: If done using LetsEncrypt, it will be named something like `ghost-le-ssl.conf`. Once the changes are saved, disable and enable the configurations using following commands: sudo a2dissite ghost.conf sudo a2dissite ghost-le-ssl.conf sudo a2ensite ghost.conf sudo a2ensite ghost-le-ssl #Restart the server sudo service apache2 restart Now if you enter you blog url in a browser, you should be presented with vanilla Ghost site. If not, something in server set-up has not worked and you will need to troubleshoot it and fix - luckily for me all worked like a charm. Restore Right, so you are now on the browser looking at the Vanilla Ghost install. First thing you need to do now is create the user with same credentials you had on your old version of ghost. To do this you will first need to enter the admin url for ghost and follow the steps to create your user. Once you are into the admin interface, navigate to "Settings" -> "Labs" and click on "Choose File" button, select the json backup that was exported from your old version of the blog and then click on "Import" button. Now to restore the images from your old blog on the server issue following commands: cd /var/www/html/ghost/content sudo rm images sudo cp /var/www/html/old_ghost_bkp/ghost/content/images /var/www/html/ghost/content/ ### Make the new image directory is writable or image uploads will fail sudo chown -R ghost:ghost images/ ### Restart Ghost cd .. ghost restart 1. The ghost CLI commands like stop, start and restart will require you to be in the directory where ghost is installed. 2. While start and stop commands of ghost specifically ask for sudo credentials, restart command just keeps rotating and hence it is better to issue a command with sudo before you issue `ghost restart`. This is it. Your old blog is now fully restored. Tweak This section is a bit of a pain because there are quite a few things that break with this version. So if you have heavily used html, you will painstakingly need to go through posts and add a new line between markdown and html content for it to be parsed properly or else it will display quite wiered outputs on your blog. If you have used code blocks with syntax highlighting, another change is with older version you would have given three backticks followed by language-sql but now you just need to give three backticks followed by sql. If you have used line numbering using prism.js, it just wont work and you will need to apply changes to your theme the way I did. Without going too much in detail on that, you can get the copy of prism.js, prism-custom-line-number.js, prism.css, prism-line-number.css using the github links for my theme and place them in assets directory of your theme. Then make sure you include them in relevant files where your theme calls the javascripts. Once done, issue the command ghost restart and things should look pretty again. Happy Migrating !!!

Ghost V1.0 Upgrade on Apache stack, related quirks and fixes

Right then, the Ghost V1.0 was out a while back and they made Ghost 0.11.x an LTS so I was not in any rush to upgrade too. I have not had much time to sort this out for a while and two days back when I finally