find


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 \.


Viber Desktop on Fedora 27 64bit: Viber cannot connect to the internet (OpenSSL error) 1

After updating our GNU/Linux distribution from Fedora 26 to Fedora 27, and maybe updating Viber Desktop client as well, Viber stopped working. To be more precise it could not connect to the internet any more. (Note: we used the RPM package of Viber Desktop to install Viber on our Fedora system).

Solution for the OpenSSL issue – Create a symbolic link to the missing library in the libraries folder of Viber

#Make sure all necessary packages are installed
sudo dnf install openssl openssl-devel;
#Create a symbolic link from the system installed libssl shared object to the Viber installation folder
sudo ln -s /usr/lib64/libssl.so.10 /opt/viber/lib/libssl.so;

Explanation and details – Optional Reading

To find the installation folder of Viber, we used the following command:

find / -iname viber 2>/dev/null;

that led us to the installation location of Viber which was /opt/viber/Viber.

Executing the binary in a terminal gave us the following errors

$ /opt/viber/Viber
 Qt WebEngine ICU data not found at /opt/viber/resources. Trying parent directory...
 Qt WebEngine resources not found at /opt/viber/resources. Trying parent directory...
 Qt WebEngine ICU data not found at /opt/viber/resources. Trying parent directory...
 Qt WebEngine resources not found at /opt/viber/resources. Trying parent directory...
 qt.network.ssl: QSslSocket: cannot resolve CRYPTO_num_locks
 qt.network.ssl: QSslSocket: cannot resolve CRYPTO_set_id_callback
 qt.network.ssl: QSslSocket: cannot resolve CRYPTO_set_locking_callback
 qt.network.ssl: QSslSocket: cannot resolve ERR_free_strings
 qt.network.ssl: QSslSocket: cannot resolve EVP_CIPHER_CTX_cleanup
 qt.network.ssl: QSslSocket: cannot resolve EVP_CIPHER_CTX_init
 qt.network.ssl: QSslSocket: cannot resolve sk_new_null
 qt.network.ssl: QSslSocket: cannot resolve sk_push
 qt.network.ssl: QSslSocket: cannot resolve sk_free
 qt.network.ssl: QSslSocket: cannot resolve sk_num
 qt.network.ssl: QSslSocket: cannot resolve sk_pop_free
 qt.network.ssl: QSslSocket: cannot resolve sk_value
 qt.network.ssl: QSslSocket: cannot resolve SSL_library_init
 qt.network.ssl: QSslSocket: cannot resolve SSL_load_error_strings
 qt.network.ssl: QSslSocket: cannot resolve SSL_get_ex_new_index
 qt.network.ssl: QSslSocket: cannot resolve SSLv23_client_method
 qt.network.ssl: QSslSocket: cannot resolve SSLv23_server_method
 qt.network.ssl: QSslSocket: cannot resolve X509_STORE_CTX_get_chain
 qt.network.ssl: QSslSocket: cannot resolve OPENSSL_add_all_algorithms_noconf
 qt.network.ssl: QSslSocket: cannot resolve OPENSSL_add_all_algorithms_conf
 qt.network.ssl: QSslSocket: cannot resolve SSLeay
 qt.network.ssl: QSslSocket: cannot resolve SSLeay_version
 qt.network.ssl: Incompatible version of OpenSSL
 qt.network.ssl: QSslSocket: cannot call unresolved function SSLv23_client_method
 qt.network.ssl: QSslSocket: cannot call unresolved function SSL_library_init
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 qt.network.ssl: QSslSocket: cannot call unresolved function SSLv23_client_method
 qt.network.ssl: QSslSocket: cannot call unresolved function SSL_library_init
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 qt.network.ssl: QSslSocket: cannot call unresolved function SSLv23_client_method
 qt.network.ssl: QSslSocket: cannot call unresolved function SSL_library_init

From the error we could see that the application was trying to access the functions SSLv23_client_method and SSL_library_init and it could not find them.
It was obvious that the missing functions were related to the SSL protocol, so we executed the following to make sure all necessary OpenSSL packages were installed.

sudo dnf install openssl openssl-devel;

After that we tried Viber again and gave us the same error.
So we made the assumption that Viber could not ‘see’ the installation of OpenSSL.
Then we decided to use the following command to:

  1. find all shared objects (shared libraries) of OpenSSL on our system,
  2. list all of the available functions in each shared library and
  3. identify the ones that contain the SSLv23_client_method method.
find / -iname libssl.so* -print -exec sh -c 'nm -D "$1" | grep SSLv23_client_method' _ {} \; 2>/dev/null;

Executing the command yielded the following results:

$ find / -iname libssl.so* -print -exec sh -c 'nm -D "$1" | grep SSLv23_client_method' _ {} \; 2>/dev/null;
 /usr/lib/libssl.so
 /usr/lib/libssl.so.1.1
 /usr/lib/libssl.so.10
 00027880 T SSLv23_client_method
 /usr/lib/libssl.so.1.0.2m
 00027880 T SSLv23_client_method
 /usr/lib/libssl.so.1.1.0g
 /usr/lib64/libssl.so
 /usr/lib64/libssl.so.1.1
 /usr/lib64/libssl.so.10
 000000000002dd10 T SSLv23_client_method
 /usr/lib64/libssl.so.1.0.2m
 000000000002dd10 T SSLv23_client_method
 /usr/lib64/libssl.so.1.1.0g
 /opt/viber/lib/libssl.so

From the results two libraries in the lib64 folder got our interest, so we executed ls -l on both to check them out:

$ ls -l /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.1.0.2m;
lrwxrwxrwx. 1 root root 16 Nov 13 13:52 /usr/lib64/libssl.so.10 -> libssl.so.1.0.2m
-rwxr-xr-x. 1 root root 448640 Nov 13 13:52 /usr/lib64/libssl.so.1.0.2m

As you can see in the results, the two files that got us interested were in end the same file so we decided to use /usr/lib64/libssl.so.10 whose name was not tight to a minor distribution version of the library and would automatically link to any minor updates in the future.

After examining the Viber installation folder in /opt/viber/, we saw that there was a lib folder there with no SSL libraries available (even though all the QT and networking libraries were there already):

$ ls /opt/viber/lib/
libicudata.so.52 libQt5Gui.so.5 libQt5Qml.so.5 libQt5WebEngine.so.5
libicui18n.so.52 libQt5Location.so.5 libQt5QuickControls2.so.5 libQt5WebEngineWidgets.so.5
libicuuc.so.52 libQt5MultimediaQuick_p.so.5 libQt5Quick.so.5 libQt5WebSockets.so.5
libpng12.so.0 libQt5Multimedia.so.5 libQt5QuickTemplates2.so.5 libQt5Widgets.so.5
libqgsttools_p.so.1 libQt5MultimediaWidgets.so.5 libQt5QuickWidgets.so.5 libQt5XcbQpa.so.5
libqrencode.so libQt5Network.so.5 libQt5Sql.so.5
libQt5Concurrent.so.5 libQt5OpenGL.so.5 libQt5Svg.so.5 libxcb-xinerama.so.0
libQt5Core.so.5 libQt5Positioning.so.5 libQt5WebChannel.so.5
libQt5DBus.so.5 libQt5PrintSupport.so.5 libQt5WebEngineCore.so.5

Examining our OS, we saw that in the rest of the locations were the QT libraries were installed there was a copy of the libssl as well.
So we decided to make a soft symbolic link of the /usr/lib64/libssl.so.10 shared library in the Viber libraries folder.
We did two tests:

  1. Creating a symbolic link named libssl.so.10 failed.
  2. Creating a symbolic link name libssl.so succeeded and solved the issue!

The command that we used was the following:

sudo ln -s /usr/lib64/libssl.so.10 /opt/viber/lib/libssl.so;

After performing this step, Viber was working again as expected!

$ sudo ln -s /usr/lib64/libssl.so.10 /opt/viber/lib/libssl.so;
 $ /opt/viber/Viber
 Qt WebEngine ICU data not found at /opt/viber/resources. Trying parent directory...
 Qt WebEngine resources not found at /opt/viber/resources. Trying parent directory...
 Qt WebEngine ICU data not found at /opt/viber/resources. Trying parent directory...
 Qt WebEngine resources not found at /opt/viber/resources. Trying parent directory...
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 QQuickItem: Cannot set activeFocusOnTab to false once item is the active focus item.
 qml: type=""
 qml: type=""
 qrc:/QML/Feed/FeedView.qml:98:5: QML ListViewEx: Binding loop detected for property "bottomMargin"
 QObject: Cannot create children for a parent that is in a different thread.
 (Parent is QObject(0x7f3f5f970520), parent's thread is QThread(0x4191210), current thread is QThread(0x515f5b0)
 Invalid SOS parameters for sequential JPEG

Success!


How to execute `find` that ignores .git directories

Trying to find a source code file by its content using find and -exec grep, can some times result in getting results from the repository .git folders as well.

This behavior not only does it provide results you do not need but it also makes your search slower.
Below, we propose a couple of solutions on how to make a more efficient search.

Example 1: Ignore all .git folders no matter where they are in the search path

For find to ignore all .git folders, even if they appear on the first level of directories or any in-between until the last one, add -not -path '*/\.git*' to your command as in the example below.
This parameter will instruct find to filter out any file that has anywhere in its path the folder .git. This is very helpful in case a project has dependencies in other projects (repositories) that are part of the internal structure.

find . -type f -not -path '*/\.git/*';

Note, if you are using svn use:

find . -type f -not -path '*/\.svn/*';

Example 2: Ignore all hidden files and folders

To ignore all hidden files and folders from your find results add -not -path '*/\.*' to your command.

find . -not -path '*/\.*';

This parameter instructs find to ignore any file that has anywhere in its path the string /. which is any hidden file or folder in the search path!


Replace a character in all filenames

The following command will search for files in the current directory (.) that have in their name the colon character :.
The files that match will then be renamed and all instances of the colon character : in the names will be replaced by the full stop character ..

find . -name "*:*" -execdir sh -c 'mv "$1" "${1//:/.}"' _ {} \;
  • -execdir command {} + is like -exec, but the specified command is run from the subdirectory containing the matched file, which is not normally the directory in which you started find.

Example: if you have a file named 2017-03-15 14:34:44.116002523.png then it will be renamed to 2017-03-15 14.34.44.116002523.png.


Back Up Jenkins instance except for workspace and build logs

Our Jenkins setup has a lot of cool features and configuration.
It has ‘project-based security’, it has parametrized projects, multiple source code management blocks per project and fairly extensive tests implemented with several build steps.
Of course, we do not want to lose them, so we make backups often.
The commands we use for the backup are the following.

jenkins_folder="/var/lib/jenkins/";
 backup_folder="$HOME/jenkins/`date +%F`";
 mkdir -p "$backup_folder";
 (cd "$jenkins_folder"/jobs/; find . -mindepth 3 -type d -regex '.*/[0-9]*$' -print) | sed 's|./|jobs/|' | sudo rsync --archive --exclude 'workspace/*' --exclude-from=- "$jenkins_folder" "$backup_folder";

Explanation of commands:

  • In backup_folder="$HOME/jenkins/`date +%F`"; we used the $HOME variable instead of the tilde ~ as this would create a folder in the current directory called ~ instead of creating a new folder called jenkins in the home directory.
  • mkdir -p "$backup_folder"; instructs mkdir to create all parent folders needed to create our destination folder.
  • (cd "$jenkins_folder"/jobs/; find . -mindepth 3 -type d -regex '.*/[0-9]*$' -print) navigates to the directory of jenkins before performing the search, this way the result file names will be relative to the installation location which we need later to pass to rsync.
    Then we search for all folders which their name is numeric and they at least on depth 3. We filter by depth as well to avoid matching folders directly in the jobs folder.
  • sed 's|./|jobs/|' replaces the prefix ./ with jobs/ to match the relative path from where rsync will work from
  • sudo rsync --archive --exclude 'workspace/*' --exclude-from=- "$jenkins_folder" "$backup_folder"; it will copy everything from $jenkins_folder to the folder $backup_folder while excluding the data in workspace and the folders matched from find (the job build folders).
    --exclude-from=- instructs rsync to read from stdin the list of files to exclude.

Find files that were created, modified or accessed in the last N minutes

Find all files in $my_folder that their status changed in the last 60 minutes

find $my_folder -cmin -60

Find all files in $my_folder that their data were modified in the last 60 minutes

find $my_folder -mmin -60

Find all files in $my_folder that they were accessed in the last 60 minutes

find $my_folder -amin -60

Please remember to use negative values for the minutes. e.g. use -60 and not 60.

More examples

Find all files in $my_folder that their status changed in the last 60 minutes AND they were accessed in the last 10 minutes

find $my_folder -cmin -60 -amin -10

Find all files in $my_folder that their status changed in the last 60 minutes OR they were accessed in the last 10 minutes

find $my_folder \( -cmin -60 -o -amin -10 \)

Notes on find command

  • -cmin n Matches files which their status was last changed n minutes ago.
  • -mmin n Matches files which which data was last modified n minutes ago.
  • -amin n Matches files which they were last accessed n minutes ago.
  • -o is the logical Or operator. The second expression  is not evaluated if the first expression is true.

How to search for specific filenames in .tar archives

The following commands will search in the .tar archives found in the specified folder and print on screen all files that their paths or filenames match our search token. We provide multiple solutions, each one for a different type of .tar archive depending on the compression used.

For .tar archives

find /media/repository/packages/ -type f -iname "*.tar" -exec tar -t -f '{}' \; | grep "configurations/arm-cortexa9";

For .tar.bz2 archives

find /media/repository/packages/ -type f -iname "*.tar.bz2" -exec tar -t -j -f '{}' \; | grep "configurations/arm-cortexa9";

For .tar.xz archives

find /media/repository/packages/ -type f -iname "*.tar.xz" -exec tar -t -J -f '{}' \; | grep "configurations/arm-cortexa9";

For .tar.gz and .tgz archives

Please note that this commands uses the -o (which is the logical or) parameter on find to search for multiple filename extensions.

find /media/repository/packages/ -type f \( -iname "*.tar.gz" -o -iname "*.tgz" \) -exec tar -t -z -f '{}' \; | grep "configurations/arm-cortexa9";

find Parameters Legend

  • -type f filters out any result which is not a regular file
  • -exec command '{}' \; runs the specified command on the results of find. The string '{}' is replaced by the current file name being processed.
  • -o is the logical Or operator. The second expression  is not evaluated if the first expression is true.

tar Parameters Legend

  • -z or --gzip instructs tar to filter the archive through gzip
  • -j or --bzip2 filters the archive through bzip2
  • -J or --xz filters the archive through xz
  • -t or --list lists the contents of an archive
  • -f or --file=INPUT uses the archive file or device named INPUT