Wake-on-LAN Proxmox Backup Server

Inspired by https://wiki.archlinux.org/title/Wake-on-LAN

  1. Enable Wake on LAN (Power ON By PME) in BIOS on the target computer motherboard

     

  2. Enable WoL on the network adapter of the target computer (Linux)
    Depending on the hardware, the network driver may have WoL switched off by default.
    To query this status or to change the settings, install ethtool, determine the name of the network interface, and query it using the command:

    # ethtool enp1s0 | grep Wake-on
    Supports Wake-on: pumbag
    Wake-on: d

    The Wake-on values define what activity triggers wake up: d (disabled), p (PHY activity), u (unicast activity), m (multicast activity), b (broadcast activity), a (ARP activity), and g (magic packet activity). The value g is required for WoL to work, if not, the following command enables the WoL feature in the driver:

    # ethtool -s enp1s0 wol g

    This command might not last beyond the next reboot and in this case must be repeated via some mechanism. Common solutions are listed in the following subsections.

  3. Find MAC addres of the network card on the target computer
    To trigger WoL on a target machine, its MAC address must be known.
    To obtain it, execute the following command on the target machine:

    $ ip link
    1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    2: enp1s0: <BROADCAST,MULTICAST,PROMISC,UP,LOWER_UP> mtu 1500 qdisc fq_codel master br0 state UP group default qlen 1000 link/ether 48:05:ca:09:0e:6a brd ff:ff:ff:ff:ff:ff
  4. Make WoL on the network adapter of the target computer persistent using systemd.link
    Link-level configuration is possible through systemd-networkd#link files.
    The actual setup is performed by the net_setup_link udev builtin.
    Make a file

    /etc/systemd/network/50-wired.link

    with the following content

    [Match]
    MACAddress=aa:bb:cc:dd:ee:ff
    
    [Link]
    NamePolicy=kernel database onboard slot path
    MACAddressPolicy=persistent
    WakeOnLan=magic
  5. Test the setup
    On the target machine us netcat (nc) :

    nc -ulp 9 | hexdump

    On the client machine

    wakeonlan 50:46:5d:a3:93:20

    or

    wakeonlan -i 192.168.0.201 50:46:5d:a3:93:20

    On the target machine you should see something similar to this:

    0000000 ffff ffff ffff 4650 a35d 2093 4650 a35d
    0000010 2093 4650 a35d 2093 4650 a35d 2093 4650
    0000020 a35d 2093 4650 a35d 2093 4650 a35d 2093
    0000030 4650 a35d 2093 4650 a35d 2093 4650 a35d
    0000040 2093 4650 a35d 2093 4650 a35d 2093 4650
    0000050 a35d 2093 4650 a35d 2093 4650 a35d 2093

    Shut down the target machine issuing command

    shutdown -h now

    or remotely

    /usr/bin/ssh -t root@192.168.0.201 'shutdown -h now'

    !!! DO NOT USE halt COMMAND !!! to shut down target machine. halt command puts the system into complete power-off mode, cutting out power supply on the machine completely, so network card can not communicate with network.

    On the client machine execute again:

    wakeonlan 50:46:5d:a3:93:20

    You should see target machine start to boot

    Configure Proxmox Backup Server

    Add an API token (and record the API token secret – it will only be displayed once)

    Add / or /system/tasks permission (path) for created token

    Than following request should return the number of active backup tasks (if backup task is not running 0 is returned)

    curl -s -k -H 'Authorization: PBSAPIToken=root@pam!testN:424e33be-7bc1-44c6-82b7-474860adb495' https://192.168.0.201:8007/api2/json/nodes/localhost/tasks\?running=true | jq .total

    You should modify above mentioned request using info on the proxmox web

    https://pbs.proxmox.com/docs/api-viewer/index.html#/nodes/{node}/tasks

    Instead of about mentioned bash curl command you can use python script to test if backup task is not running

    import requests
    import time
    import subprocess
    
    import warnings
    from urllib3.exceptions import InsecureRequestWarning
    
    # Suppress the InsecureRequestWarning
    warnings.filterwarnings("ignore", category=InsecureRequestWarning)
    
    url = 'https://192.168.0.201:8007/api2/json/nodes/localhost/tasks?running=true'
    headers = {'Authorization': 'PBSAPIToken=root@pam!testN:424e33be-7bc1-44c6-82b7-474860adb495'}
    
    count = 0
    
    while True:
        try:
            response = requests.get(url, headers=headers, verify=False)
            data = response.json()
            total = data['total']
    
            print(f"Total: {total}")
    
            if total == 0:
                count += 1
            else:
                count = 0
    
            if count == 10:
                print("10 consecutive occurrences of total being 0.")
                break
    
        except requests.exceptions.RequestException as e:
            if "No route to host" in str(e):
                print("No route to host. Exiting the loop.")
                break
            else:
                raise e
    
        time.sleep(5)
    
    if count == 10:
        command = ["/usr/bin/ssh", "-t", "root@192.168.0.201", "shutdown", "-h", "now"]
    
        process = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.DEVNULL)
        output, error = process.communicate()
    
        if process.returncode == 0:
            print("Command executed successfully.")
        else:
            print("An error occurred while executing the command.")
            if error is not None:
                print("Error:", error.decode())
    
  6. than, you can write in to the /etc/crontab following

    40 23 * * *     root    /usr/bin/wakeonlan 50:46:5d:a3:93:20 > /dev/null 2>&1
    #
    1 0 * * *	root	/usr/bin/python3 /root/shutDownPBS.py >> /var/log/pbsShutDown.log 2>&1 
    #

 

Proxmox Backup Server configuration

Original here: https://www.thomas-krenn.com/en/wiki/Proxmox_Backup_Server_configuration

BUY PROXMOX BACKUP SERVER SUBSCRIPTIONS

After successful installation of the Proxmox Backup Server the configuration via the web interface can be done (https://IP:8007). Be sure to specify the port correctly. This article shows which steps are necessary to set up a Proxmox Backup Server.

Buy Proxmox Backup Server Subscriptions

Contents

  1. Create ZFS Pool & Create Datastore
  2. Prune & Garbage Collection configuration
  3. Create & store backup user
  4. Add Proxmox Backup Server
  5. Perform Proxmox Test Backup

Create ZFS Pool & Create Datastore

In this section, we will show you how to create a ZFS pool and backup datastore.

First you have to initialize all disks you want to use for the backup datastore with GPT, this can be done in the GUI via Administration > Storage / Disks > Initialize with GPT’ or on the command line with

sgdisk /dev/sdX
  • Then a ZFS pool is created via Administration > Storage / Disks > ZFS.
  • A name is assigned, the RAID level is selected, compression and ashift can usually be left at default values.
  • With ashift you control the block size – ashift 12 means 2 to the power of 12 = 4096B = 4K block size.
  • Then select the desired media and press OK.

In this test, we used a RAID mirror, but a RAID-10 or RAID-Z2/ RAID-Z3 can also be used for a larger number of hard disks. Proxmox recommends SSD-only for backup storage. If this is not feasible for cost reasons, we recommend the use of a ZFS Special Device (ZFS Instant Log – ZIL-Device). – for example Intel Optane. Once the pool has been created, it is automatically included in the PBS as a backup datastore. Now you can see the created datastore under Datastore > pbs-local.

Prune & Garbage Collection configuration

This section defines and sets the retention periods of the backups:

  • Under Datastore > pbs-local > Prune & GC you can set how many backups should be kept and when the garbage collection should be performed.
  • The best way to plan and orient yourself is by using the Proxmox Prune Simulator.
  • If the desired setting was found, you can set these via Edit and now the Datastore is configured.

Thomas Krenn recommends a test phase when introducing the Proxmox backup server to find the correct settings for Prune and GC. The Prune simulator helps very well here.

Explanation of Prune: Prune checks whether certain backups can be removed from the index based on the set retention periods. The index contains the information about all blocks of the backups, which could be restored to another system via the PBS. For example, if there are 6 versions of a block = 6 backups and a retention of 5 is set, Prune will delete the oldest version of the block from the index during the Prune Schedule, so that the retention is correctly maintained again and only 5 versions of the block are left. When Prune does this can be set via “Prune Schedule”. No load is created on the storage during the process, so the Prune Schedule can be executed at any time.

Explanation Garbage Collection: The garbage collector grabs the blocks excluded from the index by Prune and also deletes them from the storage. This creates load on the storage and may take longer if there are many backups. Depending on your needs, we recommend running the garbage collection once a day or once a week.

Create & store backup user

By default the user root@pam has full access. Via Configuration > Accesscontrol > User Management additional users can be created, whose rights can be restricted under Permissions. By default, every newly created user has the role NoAccess. The roles and permissions concept can be seen in the Proxmox documentation. At the datastore level, the assignment is even easier. Under Datastore > pbs-local you can assign Permissions to created users accordingly to the datastore and assign a role.

Add Proxmox Backup Server

This section explains how to add the Proxmox Backup Server in a PVE system:

  • To add the Proxmox Backup Server now to a Proxmox Single Host or a Proxmox Ceph Cluster first copy the fingerprint of the PBS in the Dashboard.
  • Then go to the PVE system or PVE cluster and add the Proxmox Backup Server under Datacenter > Storage > Add > Proxmox Backup Server.
  • Under ID one assigns the Proxmox-VE local datastore ID, Server one specifies the IP address or the host name of the Proxmox Backup Server.
  • Username you have to specify the LOCAL Proxmox-VE-PAM User, here is root not enough. Here in our test scenario this is root@pam.
  • Under Datastore enter the name of the datastore of the Proxmox Backup Server.
  • Finally add the copied fingerprint of the PBS and add the server to Proxmox VE with Add.

Perform Proxmox Test Backup

Now that the PBS is stored in the Proxmox VE system, you can perform a test backup. The best way to do this is to perform a one-time backup by clicking either on a container or on a VM and selecting Backup > Backup now. Please note the different backup modes, which are well described in the Proxmox documentation. After a successful backup you can see the backup in the PVE in the GUI as well as on the Proxmox Backup Server.

RAID1 Ubuntu 16.04 P2V (physical to virtual conversion) using clonezilla Ubuntu 16.04

ispirace zde: https://www.youtube.com/watch?v=wSTk9BLwF5k&ab_channel=LawrenceSystems

video z instalace zde: https://maxbox.cz/nextcloud/index.php/s/jE2bzDi9TS7kHo5

Na stávajícím fyzickém serveru maxbox je Ubuntu 16.04.6 LTS (Xenial Xerus)

software RAID1 (2 x 2TB SATA HDD WDC WD2004FBYZ)

Struktura disků je následující

petr@maxbox:~$ sudo fdisk -l
Disk /dev/sda: 1.8 TiB, 2000398934016 bytes, 3907029168 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x4d84172a

Device     Boot      Start        End    Sectors  Size Id Type
/dev/sda1  *          2048  195311615  195309568 93.1G fd Linux raid autodetect
/dev/sda2        195313662 3907028991 3711715330  1.7T  5 Extended
/dev/sda5       3891404800 3907028991   15624192  7.5G fd Linux raid autodetect
/dev/sda6        195313664 3891404799 3696091136  1.7T fd Linux raid autodetect

Partition table entries are not in disk order.


Disk /dev/sdb: 1.8 TiB, 2000398934016 bytes, 3907029168 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x076a9f4c

Device     Boot      Start        End    Sectors  Size Id Type
/dev/sdb1  *          2048  195311615  195309568 93.1G fd Linux raid autodetect
/dev/sdb2        195313662 3907028991 3711715330  1.7T  5 Extended
/dev/sdb5       3891404800 3907028991   15624192  7.5G fd Linux raid autodetect
/dev/sdb6        195313664 3891404799 3696091136  1.7T fd Linux raid autodetect

Partition table entries are not in disk order.


Disk /dev/md0: 93.1 GiB, 99931389952 bytes, 195178496 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/md2: 7.5 GiB, 7995392000 bytes, 15616000 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/md1: 1.7 TiB, 1892264443904 bytes, 3695828992 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Na ProxMoxu vytvoříme virtuální server linux (bez instalace konkrétního operačního systému) s velikostí disku 2 TB = 2048 GB = 2097152 MB 

Na tomto virtuálním serveru nabootujeme z CD Live Clonezilla

v Clonezilla odskok do terminálu

pomocí  sudo fdisk /dev/sda  

  • vytvořit disk DOS:   o   create a new empty DOS partition table
  • na něm vytvořit stejně velké oddíly (partitions) jako jsou na fyzickém (zdrojovém) serveru (Extended partition nevytvářet – není potřeba.)
  • nadefinovat typ oddílu na “Linx raid autodetect” (fd)
/dev/sda1  *          2048  195311615  195309568 93.1G fd Linux raid autodetect
/dev/sdb5       3891404800 3907028991   15624192  7.5G fd Linux raid autodetect
/dev/sdb6        195313664 3891404799 3696091136  1.7T fd Linux raid autodetect

Zdrojový server rebootovat a spustit na něm CD clonezilla live

  • vybrat klonování local partition to remote partition
  • vybrat první partition, kterou budeme přenášet.

Na cílovém stroji spustit CD Clonezilla live

  • vybrat part_to_remote_part
  • vybrat odpovídající target partition (partition se stejnou velikostí jako na zdrojovém sesrveru). 

Zahájit proces přenosu.

Takto opakovat pro všechny 3 partitions 

Na Proxmox reboot virtuálního serveru  (jako bootovací vybrat CD Ubuntu server disk).  Ve volbě na CD vybrat rescue mode.

Odskočit do shellu (Alt+F2)

cat/proc/mdstat
all disks inactive

z menu rescue Ubuntu vybrat

  • “assemble disc array”
  • partitons to assemble automatic 

 Alt+F2 vidime, ze jsou inactive

get detailed information about a RAID device 

 mdadm -D /dev/mdX  tvrdi RAID0 (ale je to nenačtené)

more detailed information by using the -E or –examine options:

mdadm  -E /dev/sda1,2,3 správně tvrdí RAID1 1 clean a 1 removed device, clean degraded

re-start RAID

mdadm -R /dev/md127
mdadm -R /dev/md126
mdadm -R /dev/md125

Run the following commands to change the array and filesystem back to read-write mode:

mdadm -w /dev/md127
mdadm -w /dev/md126
mdadm -w /dev/md125
dmesg
/dev/md125 switched to read-write mode

v menu Ubuntu “go back” + znovu “enter rescue mode”

rescue mode nabízí jako root uz i ty mdX

výběr toho, u ktereho  vedle v cat /proc/mdstat vidime, ze obsahuje sda1

v menu Ubuntu “reinstall GRUB boot loader”

zadat device /dev/sda

reboot from hdd (not from the Ubuntu rescue CD)

nabootuje Ubuntu 16.04

cat /proc/mdstat

ted uz jsou /dev/md0,1,2 

posledni uklid – neexistujici člen v RAID

mdadm --grow /dev/mdX --raid-devices=1 --force   … opet postupne

Test Kamery

					

Kitesurfing Rules of the Road

Original posters and the text files in English, Korean and Chinese are attached below.

You can help by translating them from English to other languages.

Kitesurfing Rules of the Road

  1. Before launching a kite from the beach, one must be sure that it does not create any danger, not only for other riders but also for other people on the launching spot. In addition, the fact that the kite can drag a few dozen meters during launching this should be taken into account. A rider launching his kite on the beach gives way to anybody around him.
  2. Give way to riders entering the water. Being on land with an inflatable kite is more hazardous than being on water. Always allow others to enter the water even if this means having to take another turn before being able to return to the beach.
  3. When passing one another, the upwind rider must put his kite up and downwind rider must put his kite down.
  4. Give way to riders who are not in control of their equipment. You should give space to riders without their board or who look less in control (students).
  5. If two riders are on colliding course, rider riding right hand forward has the priority. If rider is riding switch (toe side edge), he has left hand forward of course.
  6. When overtaking, faster kiter must not obstruct or impede the slower kiter. Faster kiter must keep clear by a suitable distance, either windward (upwind) or leeward (downwind).
  7. The downwind rider has the priority. The upwind rider keeps out of the way of the downwind rider.
  8. Rider riding a wave has always the priority.
  9. The multiperson meetings increase the risk of collisions and should be avoided. It concerns especially beginners. Seeing that in a moment the multiperson meeting will take place and having doubts whether it is safe, the best way is to make a U-turn on the opposite course. If there is no place for that, just stop, set your kite in the zenith and wait until the situation is cleared.
  10. Give way to other water users over. Remember, even if it is your right of way, it is your responsibility to avoid collision at all costs and to help keep kitesurfing safe and respect as a watersport.

Python MinimalModbus + SHT20 Modbus RTU RS485 Temperature and humidity sensor

#! /usr/bin/python
import minimalmodbus
import time
# Set the value minimalmodbus.CLOSE_PORT_AFTER_EACH_CALL=True 
# immediately after import minimalmodbus
minimalmodbus.CLOSE_PORT_AFTER_EACH_CALL=True

# default slave address = 1
instr = minimalmodbus.Instrument(port='/dev/ttyUSB0', slaveaddress=1)
instr.serial.baudrate = 9600
instr.serial.parity  = "N"
instr.serial.timeout  = 0.8
#instr.debug = True
print(instr)

"""
read slave address, baud rate, temp. correction, hum. correction
"""
print(instr.read_registers(257, 4,functioncode=3))

"""
change of the slave address 
the change will take effect after reboot only! 
it is possible to continue using the old address till reboot
"""
slaveNewAddress = 5
#instr.write_register(257, slaveNewAddress, functioncode=6)

"""
change of the speed - take effect imediatelly
the change will take effect after reboot only! 
it is possible to continue using the old address till reboot
"""
slaveNewBaudrate = 9600
#instr.write_register(258, slaveNewBaudrate, functioncode=6) 

# read slave address, baud rate, temp. correction, hum. correction
print(instr.read_registers(257, 4,functioncode=3)) 

time.sleep(1)

while 1:
    try:
        print instr.read_registers(1,2, functioncode=4) # read temp and hum (list)
        print instr.read_register(1,1, functioncode=4) # read temp 
        print instr.read_register(2,1, functioncode=4) # read hum 
    except minimalmodbus.NoResponseError:
        print("No Response Error")
    time.sleep(1)
root@danfoss:/home/pi/danfoss# sudo pip install -U minimalmodbus
Collecting minimalmodbus
  Downloading https://files.pythonhosted.org/packages/6c/d5/77d42e8a0b73da2b5f97acd91900ac50e303b4cb959f76350cfbb38e05a0/minimalmodbus-1.0.2-py2.py3-none-any.whl
Requirement already up-to-date: pyserial>=3.0 in /usr/local/lib/python2.7/dist-packages (from minimalmodbus)
Installing collected packages: minimalmodbus
  Found existing installation: MinimalModbus 0.7
    Uninstalling MinimalModbus-0.7:
      Successfully uninstalled MinimalModbus-0.7
Successfully installed minimalmodbus-1.0.2
root@danfoss:/home/pi/danfoss# 

Description of the module here

Power consumption (kWh) monitoring using node-red and influxDB

I have got nice 3phase 30(100)A four wire RS485 MODBUS Din Rail watt meter from Aliexpress (US $35.76) (max. baud rate is possible to set to 9600). I am able to read values using USB-RS485 adapter + Raspberry pi and sending data (json) to the mqtt server. So far no problem. Power meter is providing cummulated value (the same value you can read on the display).

At the first beginning I was sending the data in to the sqlite database using sqlite node-red node. The table was very simple:
(ID, TIMESTAMP, TOTAL_KWH).

The issue starts with the simple question: How to get daily (hourly, weekly, monthly… ) power consumption? Something like:
| 22.7.2019 | 450kWh|
| 23.7.2019 | 320kWh|
| 24.7.2019 | 300kWh|
At the first sight it looks as an easy task, but I ended up with this quite complex query. And as data was growing, the execution of the SELECT was slowing down.

The guys from the nice JOYSFERA server were pointing me to the influxDB. Installation was an easy part thanks to the:
https://www.osradar.com/install-influxdb-ubuntu-18-04-debian-9/

To import existing data in to the influxDB you need a text file with the following structure:

# DDL
CREATE DATABASE mrazirna

# DML
# CONTEXT-DATABASE: mrazirna
# CONTEXT-RETENTION-POLICY:autogen

power value=0.0 1564391161
power value=270.8 1564583065
power value=272.1 1564583462
power value=272.1 1564583542
power value=272.1 1564583621
power value=272.2 1564583701

While preparing the file, note the line ending has to be linux style and if you are importing big data-set, the error messages are displayed on the top of the import screen – you need to scroll up (which was kind of difficult to me to discover).
Now you are ready to import data in to the database:

> influx -import -path=/path/to/the/data/file -precision=s
2019/08/27 22:53:04 Processed 1 commands
2019/08/27 22:53:04 Processed 26934 inserts
2019/08/27 22:53:04 Failed 0 inserts

Next connect the node-red node to the influx database:
https://diyprojects.io/node-red-tutorial-saving-mysensors-measurements-on-influxdb/#.XWWvovxS9B8

When you are preparing data for the influxDB, remember you need to send the same data-type in to the database. In my case the value datatype was float

> show field keys
name: power
fieldKey fieldType
-------- ---------
value    float

It was necessary to writhe following (note *1.0 at the end of the first row) in to the node-red function node (otherwise the string was send to the influxDB)

msg.payload = msg.payload.POWER.toFixed(1)*1.0;
return msg;

The best part is the simplicity of extracting data:

> root@maxbox:/data/new# influx -precision rfc3339
Connected to http://localhost:8086 version 1.7.7
InfluxDB shell version: 1.7.7

> use mrazirna
Using database mrazirna

> select derivative(max(value)) from "power" where time > now()-7d group by time(1d) fill(0) tz('Europe/Prague')
name: power
time                      kWh
----                      ---
2019-08-21T00:00:00+02:00 200.19999999999982
2019-08-22T00:00:00+02:00 199.5
2019-08-23T00:00:00+02:00 202.10000000000036
2019-08-24T00:00:00+02:00 111.80000000000018
2019-08-25T00:00:00+02:00 309.89999999999964
2019-08-26T00:00:00+02:00 222.5
2019-08-27T00:00:00+02:00 278
2019-08-28T00:00:00+02:00 6.5

sqlite SELECT for power consumption reading

So far I have following working code (working with example data)

WITH miniPow as (
	select date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini
	from power 
	group by date(timestamp)
	)
, maxiPow as (
	select date(TIMESTAMP) as d, max(TOTAL_KWH) maxi
	from power 
	group by date(timestamp)
	)
select maxiPow.d, ROUND(maxi-mini, 1) from miniPow 
 join 
maxiPow
on miniPow.d = maxiPow.d	

Below my first SQL attempt (not really elegant)

WITH consumption AS
  (SELECT date(TIMESTAMP) AS d,
          min(TOTAL_KWH) mi,
          max(TOTAL_KWH) ma
   FROM power
   GROUP BY date(TIMESTAMP)),
     enumerated AS
  (SELECT *,

     (SELECT count(*)
      FROM consumption b
      WHERE a.d >= b.d) AS cnt
   FROM consumption a),
     preenumerated AS
  (SELECT *,

     (SELECT count(*)-1
      FROM consumption b
      WHERE a.d >= b.d) AS cnt
   FROM consumption a)
SELECT strftime('%d.%m.%Y', en.d) AS "období od",
       strftime('%d.%m.%Y', pre.d) AS "období do",
       printf("%.1f", en.ma) AS "počáteční kWh",
       printf("%.1f", pre.ma) AS "koncový kWh",
       printf("%.1f", pre.ma-en.ma) AS "kWh za období",
       (JulianDay(pre.d) - JulianDay(en.d)) AS "počet dnů",
       printf("%.1f", (pre.ma-en.ma)/(JulianDay(pre.d) - JulianDay(en.d))) AS "kWh za den"
FROM enumerated en
JOIN preenumerated pre ON en.cnt = pre.cnt
UNION ALL
SELECT strftime('%d.%m.%Y', min(date(TIMESTAMP))) AS "období od",
       strftime('%d.%m.%Y', max(date(TIMESTAMP))) AS "období do",
       "" AS "počáteční kWh",
       "CELKEM:" AS "koncový kWh",
       printf("%.1f kWh", max(TOTAL_KWH)) AS "kWh za období",
       printf("za %.0f dnů", (JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS "počet dnů",
       printf("ø %.1f kWh/den", (max(TOTAL_KWH)-min(TOTAL_KWH))/(JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS "kWh za den"
FROM power
Link to example table here