Skip to content

La vie rêvée d'Akim

…ou le zèbre qui voulait être un homme

  • Style de vie
  • Informatique
  • Photographie
  • Musique
  • English (US)English (US)
  • FrançaisFrançais

Category: Informatique

Populate a Microsoft Power Apps drop down with a list of unique items, and no blank value

Posted on Friday 15 January 2021 - Friday 15 January 2021 by Akim

I’ve been struggling for a while, yet I sorted it out. Wooohooo.

The challenge

I’ve been trying to populate a drop down list in a Powerapp application. The source is a column called “Category” in an Excel document stored on OneDrive. The Category column contains redundant data and empty records. The drop down list has to be populated with single occurrence of categories and but not show an empty line. I want them the list to be alphabetically sorted.

The solution

It requires to encapsulate multiple functions. This is the function you have to put in your “Items” field for the drop down.

SortByColumns(Distinct(Table1,If(Not(IsBlank(Category)),Category)),"Result")

Table1 is my source table. Category is my source column (or field). Not(IsBlank(Category)) will collect the full list except blank records. Distinct will then remove duplicates from the list. Finally, SortByColumns will sort the results based on the Category field. By default, sorting will be ascending.

Here we are.

Posted in InformatiqueTagged Microsoft Power AppsLeave a comment

Shrewsoft VPN Client connection automated

Posted on Monday 23 March 2020 - Monday 4 May 2020 by Akim

I’m lucky enough to have an unlocked Fritz!Box as a DSL router provided by Sunrise at home. Recently, I have setup an office in a different building, and I wanted to have an easy way to connect my small Windows 10 NUC as a Squeezebox server (or Logitech Media Server) from my office to my home.

This requires a VPN server somewhere, and a VPN client somewhere else. The struggle starts. Long story short, I finally setup the VPN server on the Fritz!Box. It’s easy: Just create a user with a password, and set it as “Remote Access”. You will then get the details for the connection.

Unfortunately, Windows 10 is unable to make IKE v1 transactions therefore it’s not possible to use the embedded VPN client. Fritz was providing a client for Windows up to 8. Seems things changed for Windows 10 and now they recommend using Shrewsoft VPN client which I confirm works fine. However, if you want the connection to start automatically at boot and reconnect when the connection is lost, there are none of these features in the GUI. So here is a batch script which will do exactly that. Just create adapt to your folders, and create a “run at session start” task in the scheduler. I had to ask windows to log me in automatically to have this work. Beware. It may be possible to make it work without a session, but this would require more digging.

The script below does the following. Yes, having twice the same loop is voluntary. I try only twice to connect. If twice the connection fails, I give up and log an error in the C:\vpn.txt file (which get deleted at boot by the way).

The script will create a log file in C:\vpn.txt. Make your choices, you can put it somewhere else if you wish.

[14.03.2020, 20:55:02,51] VPN Client is not running. Starting connection.
[14.03.2020, 20:55:07,31] No connection. Connecting...
[14.03.2020, 20:55:07,32] Waiting 5 seconds for negotiation
[14.03.2020, 20:55:12,31] Connection not established. Waiting 5 more seconds
[14.03.2020, 20:55:12,42] Connection established
[14.03.2020, 21:48:23,81] No connection. Connecting...
[14.03.2020, 21:48:23,94] Waiting 5 seconds for negotiation
[14.03.2020, 21:48:28,81] Connection not established. Waiting 5 more seconds
[14.03.2020, 21:48:28,92] Connection established
[15.03.2020,  0:44:45,83] No connection. Connecting...
[15.03.2020,  0:44:45,94] Waiting 5 seconds for negotiation
[15.03.2020,  0:44:50,83] Connection not established. Waiting 5 more seconds
[15.03.2020,  0:44:50,94] Connection established
[15.03.2020,  3:57:07,84] No connection. Connecting...
[15.03.2020,  3:57:07,95] Waiting 5 seconds for negotiation
[15.03.2020,  3:57:12,85] Connection not established. Waiting 5 more seconds
[15.03.2020,  3:57:12,98] Connection established
[15.03.2020, 11:57:49,84] No connection. Connecting...
[15.03.2020, 11:57:49,96] Waiting 5 seconds for negotiation
[15.03.2020, 11:57:54,85] Connection not established. Waiting 5 more seconds
[15.03.2020, 11:57:54,94] Connection established

And anything in this script can be changed to your needs or willing obviously.

@echo off

del C:\vpn.txt

:START
TIMEOUT /T 1 /NOBREAK > nul
  CALL :CHECKRUNNING
  if %ERRORLEVEL == 1 (
  echo [%date%, %time%] VPN Client is not running. Starting connection. >> C:\vpn.txt
  )

  CALL :CHECKCONN
  if %errorlevel% == 1 (
    echo [%date%, %time%] No connection. Connecting... >> C:\vpn.txt    
    CALL :CONNECT
  )
  goto :START


:CHECKRUNNING
  tasklist /FI "IMAGENAME eq ipsecc.exe" | find /I /N "ipsecc.exe" 2>NUL | find /I /N "ipsecc.exe">NUL
  if %ERRORLEVEL% == 1 (
  exit /b 1
  ) else (
  set running=ok
  exit /b 0
  ) 
  EXIT /B

:CHECKCONN
  ping 172.30.47.1 -n 1 -w 5000 > nul
  if %ERRORLEVEL% == 0 (   
    exit /B 0
    ) else (
    exit /B 1
    ) 
  exit /b 0

:CONNECT
  if "%running%"=="ok" start /WAIT taskkill /f /im "ipsecc.exe"
  start ipsecc.exe -r fritz -u  -p  -a
  echo [%date%, %time%] Waiting 5 seconds for negotiation >> C:\vpn.txt

  CALL :CHECKCONN

  if %ERRORLEVEL% == 0 (
    echo [%date%, %time%] Connection established >> C:\vpn.txt
    exit /B 0
  ) else (
    echo [%date%, %time%] Connection not established. Waiting 5 more seconds >> C:\vpn.txt
  )

  CALL :CHECKCONN

  if %ERRORLEVEL% == 0 (
    echo [%date%, %time%] Connection established >> C:\vpn.txt
    exit /B 0
  ) else (
    echo [%date%, %time%] Connection failed. Exiting >> C:\vpn.txt    
    exit /B 1 
  )
  EXIT /B 1

:EOF 
exit /B
Posted in InformatiqueTagged fritz!box, shrewsoft, VPNLeave a comment

WordPress plugins and headache

Posted on Friday 25 October 2019 - Friday 25 October 2019 by Akim

Well… Obviously, it’s time to go to sleep, and I’m on my blog trying to figure out why some of my articles don’t show up anymore.

And guess what ! This is due to … obsolete and retired plug-ins. OMG !!! I have to edit all my posts, and replace some shortcodes by other shortcodes to use other plugins. I hate computing. I’m going to end up breeding sheep somewhere in the landside.

Anyway. Bare with me, I will … as usual… find a way to automate this, and to update all my articles. Hopefully… at some point… one day.

’til then, sleep tight.

Posted in InformatiqueLeave a comment

HASS: Deploy and update Home Assistant from Docker Hub

Posted on Monday 15 January 2018 - Monday 15 January 2018 by Akim

Home Assistant deployment and update from Docker Hub

Introduction

I’ve been using Home Assistant for about one month now. And to make it simple, I deployed it inside a Docker Container out of Docker Hub. This is quite new for me. It’s my first use of Docker. And I like it. It makes things a lot simple. Continue reading “HASS: Deploy and update Home Assistant from Docker Hub” →

Posted in InformatiqueTagged docker, home assistant5 Comments

Script: Create a github repository, a git local directory, and link them in a single script

Posted on Sunday 14 January 2018 - Monday 15 January 2018 by Akim

I finally started to use Github. That will make my life easier for sure. I found git and github a bit hard to apprehend at start, until a colleague sent me the link to this great online training made by Code School and sponsored by Github. I way want to try it… Well… it’s called “Try Github” by the way: https://try.github.io/

I plan to use Github for all the little scripts and files I will be creating. Much more convenient. than syncing with any kind of cloud repository like Nextcloud. As I want to use both private and public github, I had to subscribe to github…

So, you might get it, I will create multiple gits and github repos, and as I’m lazy, I don’t want to remember and to type all commands everytime. Therefore I made that little scripts to help. Continue reading “Script: Create a github repository, a git local directory, and link them in a single script” →

Posted in InformatiqueTagged bash, script, githubLeave a comment

Vérifier par script bash si la connexion ssh fonctionne

Posted on Thursday 11 January 2018 - Thursday 6 June 2019 by Akim

En partant à la découverte de Nutanix Calm, je cherche à scripter le maximum du choses, forcément. Aussi, j’ai cherché comment vérifier si une connexion ssh fonctionnait correctement en script, pour permettre à celui-ci de lancer une action ou de s’interrompre si ce n’est pas le cas. Voici ce que ça donne:
 
Remplacez bien sûr les éléments , et par vos besoin.

Ce script va lancer une requête SSH, et sortir immédiatement. Il ne va pas contrôler la clé de host, ceci permet que le script ne s’interrompe pas pour demander oui/non (fingerprint). On renvoie ensuite le résutat vers /dev/null, comme ça c’est totalement silencieux. Puis on récupère enfin le code de sortie. S’il est 0, c’est bon. Si il est autre chose, la connexion a échoué, et on arrête le script.

Une erreur 255 indique par contre un access denied. On peut donc considérer qu’en tel cas, la connexion fonctionne. Donc si vous souhaitez savoir si un utilisateur peut s’authentifier, 255 est un échec. Si vous souhaitez juste savoir si SSH accepte les connexions, 255 peut être considéré comme un succès.

# Silently check if ssh connection is working for new user
ssh -q -o "StrictHostKeyChecking no" @ -p  exit &> /dev/null
ret_code=$?

# If connection failed, stop the script
if [ $ret_code != 0 ] && [ $ret_code != 255 ]
then
   echo "SSH connection for user  failed. Stopping script. Error code $ret_code"
   exit $ret_code
fi

Posted in InformatiqueTagged AutomatisationLeave a comment

MaxSmart Power Station: Fabriquant, modèles similaires et APIs

Posted on Sunday 17 December 2017 by Akim

J’ai continué mes recherches sur les prises MaxSmart. J’ai découvert, grâce aux MAC Address (adresse matérielle des composants réseau)

Continue reading “MaxSmart Power Station: Fabriquant, modèles similaires et APIs” →

Posted in InformatiqueTagged domotique, maxsmart27 Comments

MaxSmart/Revogi – Prise multiple automatisable et RestAPI

Posted on Sunday 3 December 2017 - Sunday 17 December 2017 by Akim

J’ai récemment acheté plusieurs prises multiples MaxSmart de Max Hauri. Digitec a fait une offre exceptionnelle à CHF 75.00 pour la Power Station (multiprise six sockets).

C’est un produit intéressant à plus d’un titre. La multiprise elle-même comporte un port RJ45. Une fois branchée sur le routeur réseau, elle crée un réseau PowerLAN 500Mb/s. Il est dès lors possible de contrôler jusqu’à 15 périphériques MaxSmart dans la maison, à condition que l’on soit, bien entendu, derrière le même compteur électrique. Il s’agit ici d’une limitation PowerLAN, et non MaxSmart. Il est ensuite possible de contrôler et d’automatiser chaque prise depuis son téléphone mobile ou depuis internet.

Continue reading “MaxSmart/Revogi – Prise multiple automatisable et RestAPI” →

Posted in Informatique8 Comments

Même site, plusieurs login dans Firefox

Posted on Wednesday 8 November 2017 by Akim

Depuis deux mois, j’ai rejoint la société Nutanix. Je passe sur les détails pour l’instant, je ferai peut-être des articles sur le sujet dans le futur. Ici n’est pas l’objet.

Depuis deux mois, je galère avec:

  • Deux sessions web Whatsapp
    • Une sur mon numéro de mobile privé
    • Une sur mon numéro de mobile professionnel
  • Deux sessions Google (même chose)
  • Deux sessions Facebook, Twitter, etc…

Continue reading “Même site, plusieurs login dans Firefox” →

Posted in InformatiqueLeave a comment

Suppression bannière SSH DD-WRT

Posted on Saturday 28 January 2017 - Saturday 28 January 2017 by Akim

Dans le cadre de scripts d’automatisation que je suis en train de préparer, j’exécute des commandes SSH à distance sur mon routeur WIFI piloté par DD-WRT. Ceci peut être utile pour la domotique par exemple, ou le monitoring de l’activité Wifi.

Pour ceux qui ne le connaissent pas, DD-WRT est un firmware Open Source pour routeur. A la base, il a été créé pour remplacer le firmware des WRT-54G de Linksys. Puis il s’est étendu à tout une liste de routeurs dont on retrouve la base de donnée sur le site dédié. On trouve des firmwares similaires tels que Open-WRT, ou Tomato. Tous ont leurs avantages, leurs inconvénients. J’ai choisi DD-WRT depuis plusieurs années. Continue reading “Suppression bannière SSH DD-WRT” →

Posted in InformatiqueLeave a comment

Envoyer un email depuis Debian (ou Ubuntu)

Posted on Thursday 26 January 2017 by Akim

Pour envoyer un email depuis le terminal ou un script sur Debian, on peut utiliser mailx.

Créer un fichier .mailrc dans le dossier home de l’utilisateur. Dans cet exemple, j’utilise un compte gmail et le serveur smtp gmail à authentification TLS. Continue reading “Envoyer un email depuis Debian (ou Ubuntu)” →

Posted in InformatiqueLeave a comment

Protégez vos données, les cartes SD et microSD ne sont pas fiables

Posted on Thursday 25 August 2016 by Akim

Carte SD (Secure Digital) et MicroSD .. Secure… Vraiment ?

Voici mon expérience et quelques conseils pour avoir l’esprit tranquille Continue reading “Protégez vos données, les cartes SD et microSD ne sont pas fiables” →

Posted in InformatiqueLeave a comment

Posts navigation

Older posts

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 12 other subscribers

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
Proudly powered by WordPress | Theme: micro, developed by DevriX.