Monthly Archives: October 2018


LibreOffice Calc: Get workbook path only

Get workbook path only

For Linux and Mac

=LEFT(
  CELL("filename"),
  FIND(
    CHAR(1),
    SUBSTITUTE(
      CELL("filename"),
      "/",
      CHAR(1),
      LEN(CELL("filename")) - LEN(
        SUBSTITUTE(
          CELL("filename"),
          "/",
          ""
        )
      )
    )
  ) -1
)&"'"

For Windows

=LEFT(
  CELL("filename"),
  FIND(
    CHAR(1),
    SUBSTITUTE(
      CELL("filename"),
      "\",
      CHAR(1),
      LEN(CELL("filename")) - LEN(
        SUBSTITUTE(
          CELL("filename"),
          "\",
          ""
        )
      )
    )
  ) -1
)&"'"

Bonus

If you want to remove the ' characters as well that are around the filename and path use the following  solution, this will allow you to create hyperlinks for the folders as well

=REPLACE(
  LEFT(
    CELL("filename"),
    FIND(CHAR(1),
      SUBSTITUTE(
        CELL("filename"),
        "/",
        CHAR(1),
        LEN(CELL("filename")) - LEN(
          SUBSTITUTE(
            CELL("filename"),
            "/",
            ""
          )
        )
      )
    ) -1
  )
  ,1,1,""
)

To create hyperlink as well, use the following

=HYPERLINK(
  REPLACE(
    LEFT(
      CELL("filename"),
      FIND(CHAR(1),
        SUBSTITUTE(
          CELL("filename"),
          "/",
          CHAR(1),
          LEN(CELL("filename")) - LEN(
            SUBSTITUTE(
              CELL("filename"),
              "/",
              ""
            )
          )
        )
      ) -1
    )
    ,1,1,""
  )
)

 

 


LibreOffice Calc: Get workbook full path with filename

Get workbook full path with filename

=LEFT(CELL("filename"),FIND("#",CELL("filename"))-1)

Explanation

If you want to get the workbook filename and path, you can do so with a formula that uses the LEFT and the FIND function.

The CELL("filename") function is used to get the full file name and path along with current sheet link:

The result will look like this:

'file:///home/user/path/documentName.ods'#$sheetName

Using the FIND function we find the position of the delimiter # and then using the LEFT function, we extract just the full directory path with the filename. We know the number of characters to extract by locating the the position of the hash # character with FIND and then subtracting 1 (to exclude itself as well).

Extra

If you want to remove the ' characters as well that are around the filename and path use the following solution

=REPLACE(
  LEFT(
    CELL("filename"),
    FIND(
      "#",
      CELL("filename")
    )-2
  ), 1, 1,""
)

 


LibreOffice Calc: Get workbook filename only

Get workbook filename only

For Linux and Mac

=TRIM(
  RIGHT(
    SUBSTITUTE(
      LEFT(
        CELL("filename"),
        FIND("#", CELL("filename")) -2
      ),
      "/",
      REPT(" ", LEN(CELL("filename")))
    ),
  LEN(CELL("filename"))
  )
)

For Windows

=TRIM(
  RIGHT(
    SUBSTITUTE(
      LEFT(
        CELL("filename"),
        FIND("#", CELL("filename")) -2
      ),
      "\",
      REPT(" ", LEN(CELL("filename")))
    ),
  LEN(CELL("filename"))
  )
)

The only change is changing the delimiter from / to \.


Qubes 4.0 with Fedora 26: Setup RPM Fusion and ffmpeg

Recently we wanted to process some media on a Fedora 26 running under a Qubes OS 4.0 installation, we decided to use ffmpeg which is not part of the default repositories but it can be found in the RPM Fusion repositories. To do so, first we updated our system and enabled the RPM Fusion repositories as follows:

sudo dnf update;

sudo dnf upgrade -y;

sudo dnf install https://download1.rpmfusion.org/free/fedora/rpmfusion-free-release-$(rpm -E %fedora).noarch.rpm https://download1.rpmfusion.org/nonfree/fedora/rpmfusion-nonfree-release-$(rpm -E %fedora).noarch.rpm;

sudo dnf config-manager --set-enabled rpmfusion-free rpmfusion-nonfree;

By default DNF on the template VM did not enable the rpmfusion repositories so we had to enable them manually with the last command above or else we would get the following error:

$ sudo dnf install ffmpeg;
Last metadata expiration check: 0:17:49 ago on Tue Oct 16 09:09:22 2018.
No match for argument: ffmpeg

Then, we updated the system once more so that the information from the new repositories would get downloaded to our system and then we performed the installation of ffmpeg. While installing ffmpeg, since it was the first time that we were using the new repositories we were asked to verify the keys that were imported. We were able to manually verify the keys from this page.

The commands used to install ffmpeg are the following:

sudo dnf update;

sudo dnf install ffmpeg;



Qubes 4.0: Installation crashes when installing qubes-mgmt-salt-base-topd.noarch

Solution

  1. Disable Secure Boot from BIOS
  2. On boot order, select Legacy Devices first, again from BIOS
  3. At the first screen of the installer (like the image below), press the e button to edit the boot arguments, disabling the graphics card driver. For us what worked is the following:
    mboot.c32 xen.gz console=none --- vmlinuz inst.stage2=hd:LABEL=Qubes-R4.0-x86_64 i915.alpha_support=1 mapbs=1 noexitboot=1 modprobe.blacklist=nouveau rd.driver.blacklist=nouveau -- initrd.img
  4. Enjoy!

Back Story

Recently, we were trying to install Qubes GNU/Linux version 4.0 on a Lenovo Legion Y520 that has NVIDIA® GeForce® GTX 1050 Ti installed. After disabling secure boot from the BIOS and then setting the default boot to be on Legacy Devices, we were able to boot the anaconda installer.

We proceeded into configuring the installation (set the timezone, the keyboard layout, created the administrator user and selected the disk for the installation) and we waited for the system to install. After some serious amount of time, we realized that the installer had crashed completely while installing qubes-mgmt-salt-base-topd.noarch. We restarted the process and tried again, this time the installed did not crash but it would get stuck at the same step. Following, we gave it a few more tries just in case it would work but unfortunately it would always result either crashing or getting infinitely stuck.

Following the guide at https://www.qubes-os.org/doc/uefi-troubleshooting/ and https://www.qubes-os.org/doc/nvidia-troubleshooting/ we modified the boot parameters of the installer to add mabps and noexitboot, then disable the nouveau driver for the graphics card and it worked like a charm.


Horrible Solution: How to delete all docker logs

Recently, we needed to delete the logs of a running docker setup. To do so, we used the following horrible solution:

First, we executed docker system info | grep "Docker Root Dir";to get the installation path of docker. It resulted to the following:

Docker Root Dir: /var/lib/docker

Then, we truncated all log files using the following command while executing as root:

truncate -s 0 /var/lib/docker/containers/*/*-json.log;

Bonus

To make the command a onliner, use the following:

sudo sh -c 'truncate -s 0 $(docker system info | grep "Docker Root Dir" | cut -d ":" -f2 | cut -d " " -f2-)/containers/*/*-json.log';


Notes on how to connect from an external machine to a docker database in Google Compute Engine

A) Create a firewall rule in your VPC Network that allows you to connect to your database from outside the network: https://console.cloud.google.com/networking/firewalls/list

B) From your remote machine repair your database using mysqlcheck.

On Fedora you can install it by installing the MariaDB package as follows:

sudo dnf install mariadb -y;

The following command will automatically repair all databases and tables in that instance of MySQL, where of course the user has access:

mysqlcheck --host 45.37.15.225 --port 33061 --user admin --all-databases --auto-repair --password;

C) Edit the new firewall rule and either disable it or delete it for security.


Easy way to repair and optimize core WordPress MySql DataBase Tables

To manually repair and optimize the core WordPress tables of your MySql (or MariaDB) database:

A) edit your wp-config.php and add

define('WP_ALLOW_REPAIR', true);

right before the line

/* That's all, stop editing! Happy blogging. */

B) Visit the following link (and replace example.com with the domain or the IP of your site installation)

https://example.com/wp-admin/maint/repair.php

C) Press the Repair and Optimize Database button and wait for some time (depending on the size of the core tables of your installation)

D) Once the above step is done, remove or comment out (by adding a # at the beginning of the line) the line:

define('WP_ALLOW_REPAIR', true);

You need to do this step to to prevent this page (which could potentially take a lot of resources) from being used by unauthorized users.