Home Assistant and MQTT

After getting Home Assistant up and running, the next thing I wanted to do was to add MQTT so I could connect sensors. I decided to use mosquitto for MQTT.

First to install mosquitto server, client and python mosquitto packages.

sudo apt-get install mosquitto mosquitto-clients python-mosquitto

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libmosquitto1
The following NEW packages will be installed:
python-mosquitto
The following packages will be upgraded:
libmosquitto1 mosquitto mosquitto-clients
3 upgraded, 1 newly installed, 0 to remove and 5 not upgraded.
Need to get 214 kB of archives.
After this operation, 148 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://mirrordirector.raspbian.org/raspbian/ jessie/main mosquitto-clients armhf 1.3.4-2+deb8u1 [39.5 kB]
Get:2 http://mirrordirector.raspbian.org/raspbian/ jessie/main libmosquitto1 armhf 1.3.4-2+deb8u1 [37.1 kB]
Get:3 http://mirrordirector.raspbian.org/raspbian/ jessie/main mosquitto armhf 1.3.4-2+deb8u1 [102 kB]
Get:4 http://mirrordirector.raspbian.org/raspbian/ jessie/main python-mosquitto all 1.3.4-2+deb8u1 [34.8 kB]
Fetched 214 kB in 2s (93.1 kB/s)
Reading changelogs... Done
(Reading database ... 35121 files and directories currently installed.)
Preparing to unpack .../mosquitto-clients_1.3.4-2+deb8u1_armhf.deb ...
Unpacking mosquitto-clients (1.3.4-2+deb8u1) over (1.3.4-2) ...
Preparing to unpack .../libmosquitto1_1.3.4-2+deb8u1_armhf.deb ...
Unpacking libmosquitto1 (1.3.4-2+deb8u1) over (1.3.4-2) ...
Preparing to unpack .../mosquitto_1.3.4-2+deb8u1_armhf.deb ...
Unpacking mosquitto (1.3.4-2+deb8u1) over (1.3.4-2) ...
Selecting previously unselected package python-mosquitto.
Preparing to unpack .../python-mosquitto_1.3.4-2+deb8u1_all.deb ...
Unpacking python-mosquitto (1.3.4-2+deb8u1) ...
Processing triggers for man-db (2.7.0.2-5) ...
Processing triggers for systemd (215-17+deb8u7) ...
Setting up libmosquitto1 (1.3.4-2+deb8u1) ...
Setting up mosquitto-clients (1.3.4-2+deb8u1) ...
Setting up mosquitto (1.3.4-2+deb8u1) ...
Setting up python-mosquitto (1.3.4-2+deb8u1) ...
Processing triggers for libc-bin (2.19-18+deb8u9) ...

Now that it’s installed, lets set it up. First lets create the directory where it will keep it’s persistence db files, not forgetting to change the directory owner to the mosquitto user.

mkdir /var/lib/mosquitto/
sudo chown mosquitto:homeassistant /var/lib/mosquitto/ -R

Now lets update the configuration file. Below is what I’ve got in mine.

sudo nano /etc/mosquitto/mosquitto.conf

listener 1883
persistence true
persistence_location /var/lib/mosquitto/
persistence_file mosquitto.db
log_dest syslog
log_dest stdout
log_dest topic
log_type error
log_type warning
log_type notice
log_type information
connection_messages true
log_timestamp true
allow_anonymous false
password_file /etc/mosquitto/passwd

pid_file /var/run/mosquitto.pid

log_dest file /var/log/mosquitto/mosquitto.log

include_dir /etc/mosquitto/conf.d

Now to add some usernames/passwords.

This is how you’ll create the passwd file with the first user.

sudo mosquitto_passwd -c /etc/mosquitto/passwd username
prompt for passwd

After that, you add more users without the -c parameter, like this.

sudo mosquitto_passwd /etc/mosquitto/passwd ha
prompt for passwd

Now lets restart mosquitto.

sudo systemctl restart mosquitto

After the service has restarted, verify that mosquitto has started

pi@hassbian:~ $ sudo /etc/init.d/mosquitto status
● mosquitto.service - LSB: mosquitto MQTT v3.1 message broker
Loaded: loaded (/etc/init.d/mosquitto)
Active: active (running) since Sat 2017-06-03 23:29:23 AEST; 15s ago
Process: 30495 ExecStop=/etc/init.d/mosquitto stop (code=exited, status=0/SUCCESS)
Process: 30501 ExecStart=/etc/init.d/mosquitto start (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mosquitto.service
└─30507 /usr/sbin/mosquitto -c /etc/mosquitto/mosquitto.conf

Jun 03 23:29:23 hassbian systemd[1]: Starting LSB: mosquitto MQTT v3.1 message broker...
Jun 03 23:29:23 hassbian mosquitto[30501]: Starting network daemon:: mosquitto.
Jun 03 23:29:23 hassbian systemd[1]: Started LSB: mosquitto MQTT v3.1 message broker.
Jun 03 23:29:23 hassbian mosquitto[30507]: mosquitto version 1.3.4 (build date 2017-05-29 22:25:09+0000) starting
Jun 03 23:29:23 hassbian mosquitto[30507]: Config loaded from /etc/mosquitto/mosquitto.conf.
Jun 03 23:29:23 hassbian mosquitto[30507]: Opening ipv4 listen socket on port 1883.

Alright, now it’s up and running, lets give it a test.  I tested mine connecting to my raspberry pi with two SSH sessions, one to test subscribing to messages and one to test sending messages. You’ll need to update the IP Address, port number and username/password to suit you.

Subscribe to messages with topic test_mqtt

mosquitto_sub -t test_mqtt -u -P password -h 172.16.1.13 -p 1883

Send a message to the topic test_mqtt

mosquitto_pub -d -t test_mqtt -m "Test Message" -h 172.16.1.13 -p 1883 -u -P password

You should see the “Test Message” message arrive in your SSH session running the mosquitto subscribe.

Now lets add to Home Assistant

We now need to add some additional configuration to the Home Assistant configuration file for MQTT.

sudo nano /home/homeassistant/.homeassistant/configuration.yaml

You would add similar to the following, but customise it to IP Address and port number you are running mosquitto on (from the mosquitto configuration file) and a valid username/password.

mqtt:
broker: 172.16.X.Y
port: 1883
client_id: home-assistant-1
user: ha
passwd: hapassword

And then restart Home Assistant

This will get mosquitto up and running. You can now use MQTT with Home Assistant and send/receive message to MQTT sensors and clients.

Get SfB Enterprise Voice Users

A quick tip for getting a list of Skype users which are enabled for Enterprise Voice amongst many non Enterprise Voice Skype users.

Get-CsOnlineUser -filter "EnterpriseVoiceEnabled -eq 'True'" | ft DisplayName, SipAddress, LineURI

 

Quickly retrieving data from Office 365 mailboxes

Often we need to perform searches over many mailboxes to find just those few which match certain criteria.

One that I recently had was to find users which have a forward on their mailbox (set on the mailbox attribute) in Office 365.

One option I had was I could run:

get-mailbox -resultsize unlimited | where ($_.ForwardingSmtpAddress -like '*')

This will work.  However, if you’ve got a lot of users (e.g. over 100,000 users) it can be very slow.  But why?

As Office 365 is a shared platform, Microsoft throttle PowerShell commands.  They throttle PowerShell in a number of ways.  In this instance, by the amount of data that is sent back to your local PowerShell session.

But if I’ve only got 5 matching users out of thousands, surely that can’t be much data.. why is it throttled?

This is where it becomes important to know where the work for the PowerShell command is being done and when the data is being transferred.  As you are connecting to Office 365, some parts of the PowerShell command execute on remote server (Office 365 in our case), some is executed locally where you lauched PowerShell from and they pass data between them.

In the above example, the get-mailbox -resultsize unlimited retrieves that data from every mailbox.  This is done on Office 365 remote server and then it is all sent back to your local PowerShell session.  Once it arrives at your local PowerShell, it will execute the where ($_.ForwardingSmtpAddress -like '*') on the data.  The data is being transferred between the remote Office 365 and local server contains all the mailbox data, and of course due to the size of the data it gets throttled and slows down the command, taking longer to get the results.

We can speed this up!

The where executes locally, so we really want this to be executed on the remote server, decreasing the amount of data transferred back to your PowerShell session.  So how do we do that?  We can filter the results of the get-mailbox command using the -filter parameter and the command will only return what matches the filter and it performs it all on the remote server.

So we could change this example

get-mailbox -resultsize unlimited | where ($_.ForwardingSmtpAddress -like '*')

to

get-mailbox -resultsize unlimited -filter "ForwardingSmtpAddress -like '*'"

Prove it..

Unfortunately due to way these commands work, we can’t use -resultsize parameter to prove that these commands are faster. If we did use the -resultssize parameter e.g. -resultsize 100, the first example the command would only search the first 100 mailboxes while the updated version above would search mailboxes and return only first 100 forward results.

So in order to show the results, I’ll run this on a tenancy which contains over 300,000 mailboxes.  The original PowerShell command took over 2.5 hours while the new command took just over 10 minutes.

As you can see, the larger the number of mailboxes being searched the more benefit using -filter will give you.

If you have a small amount of users in your tenancy, then you probably won’t see very much difference in using the -filter parameter, however if you’re company expands or your scripts are used on larger Office 365 tenancies, using -filter may help your scripts get quicker results.

Setting up Home Assistant on the Raspberry Pi

Lately I’ve been playing with Home Assistant (open source) on my Raspberry Pi for Home Automation.  I was surprised on the amount of support that is currently available and how flexible and easy to setup it is.  If you haven’t looked at Home Assistant yet, you can check it out here.

I’ve mainly been using a Raspberry Pi 3, but I have also tested Home Assistant on a Raspberry Pi 2 and it ran very well with no issues.

Below is some instructions for setting up Home Assistant.  These are my notes, but hopefully you might find them useful too.

Firstly, go to the Home Assistant site and download the image of Hassbian.  Grab the etcher software too for writing the image to the SD card.  After you’ve written the image to the SD card, put it in the Raspberry Pi and start it up.

NOTE: The Hassbian instructions say to wait about 5 minutes, mine took between 5-10 minutes.  During this time, Home Assistant may detect devices/sensors on the network it is connected.  It may automatically find some of your devices. e.g. it automatically found my Chromecast.

Setup the Raspberry Pi

Once Hassbian is up, SSH in using the pi user – remember the default password is raspberry.  Once in, first thing I did was setup the Raspberry Pi.  This is pretty much the same as you would do if it was running raspian.

Change the passwd for the pi user.

sudo passwd pi

Then configure the Raspberry Pi settings.

sudo raspi-config

I update the timezone, locale, wifi locale and expand the filesystem (so I have use of the full SD card).  Then reboot – raspi-config usually prompts you to.

After the reboot, update and upgrade the packages installed on the Raspberry Pi.

sudo apt-get update
sudo apt-get upgrade


Note: This normally takes quite a while.

I usually do another reboot after the updates and upgrade just to make sure everything is running on the updated versions with no issues.

Configure Home Assistant

To configure the Home Assistant, you’ll need need to edit the Home Assistant configuration file.  In Hassbian, the Home Assistant configuration files are located in /home/homeassistant/.homeassistant.

The Home Assistant site has resources on this.  You can check them out here.

Now lets update the Home Assistant configuration file.

sudo nano /home/homeassistant/.homeassistant/configuration.yaml

It probably look something like this:

homeassistant:
# Name of the location where Home Assistant is running
name: Home
# Location required to calculate the time the sun rises and sets
latitude: -33.494
longitude: 143.2104
# Impacts weather/sunrise data (altitude above sea level in meters)
elevation: 0
# metric for Metric, imperial for Imperial
unit_system: metric
# Pick yours from here: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
time_zone: UTC

# Show links to resources in log and frontend
 introduction:

# Enables the frontend
 frontend:

# Enables configuration UI
 config:

http:
 # Uncomment this to add a password (recommended!)
 # api_password: PASSWORD
 # Uncomment this if you are using SSL or running in Docker etc
 # base_url: example.duckdns.org:8123

# Checks for available updates
 # Note: This component will send some information about your system to
 # the developers to assist with development of Home Assistant.
 # For more information, please see:
 # https://home-assistant.io/blog/2016/10/25/explaining-the-updater/
 updater:

# Discover some devices automatically
 discovery:

# Allows you to issue voice commands from the frontend in enabled browsers
 conversation:

# Enables support for tracking state changes over time.
 history:

# View all events in a logbook
 logbook:

# Track the sun
 sun:

# Weather Prediction
 sensor:
 platform: yr

# Text to speech
 tts:
 platform: google

group: !include groups.yaml
 automation: !include automations.yaml

I updated latitude, longitude, elevation, unit_system and time_zone.

If you are having trouble determining the location for latitude, longitude, google maps can help you find them.  If you click on your location on the map, the location details are usually at the bottom of the browser window.  For more info, the google help page on this is here.

When updating this configuration file, it can be fussy sometimes. It’s a good idea to validate the configuration changes before restarting Home Assistant to use configuration. You can do this in GUI very easily. You can get to the GUI by going to the hostname or IP Address of you Raspberry Pi on port 8123. Something like http://172.16.13.13:8123/.

Once you’ve made changes to the configuration file, you’ll need to restart Home Assistant.

sudo systemctl restart home-assistant@homeassistant.service

Security and Certificates

I would also recommend that you password protect your Home Assistant.  It’s good practice even if you aren’t exposing it directly to the Internet.  To do this, update the http: section of the configuration.yaml file and add api_password: PASSWORD

It should look like this:

http:
  api_password: MySecurePassword!

You’ll need to restart Home Assistant for this to take effect.

sudo systemctl restart home-assistant@homeassistant.service

Going to the Home Assistant web gui after this change will prompt for a password.

You should note however that this option still transmits the password insecurely over HTTP.  You’ll need to add certificates if you want it securely transfer your traffic.

IMPORTANT NOTE: If your Rapsberry Pi has a private IP Address (e.g. 192.168.0.5) and get a certificate for a domain with a public IP address, you browser will give you a warning that the Raspberry PI is not trusted if you go to it from your private network.  If you go to it from the Internet, you will not get this message.

If you decide you want a certificate, Let’s Encrypt provide a fantastic service where you can get free certificates.  You need a domain for this as the Let’s encrypt certificates require that you can prove ownership of your domain.  The easiest way to prove this is to port forward ports 80 & 443 temporarily to your raspberry pi while you run the script which sets up, verifies and obtain a certificate.

To get the certificates, after you’ve put the port forwards in place, you run the following commands to get obtain certificate.  Make sure you update the email address and hostname to suit that of your raspberry pi.

Note: These scripts will install python if it’s not already installed.

pi@hassbian:~ $ mkdir certbot
pi@hassbian:~ $ cd certbot/
pi@hassbian:~ $ wget https://dl.eff.org/certbot-auto
pi@hassbian:~ $ chmod a+x certbot-auto
pi@hassbian:~ ./certbot-auto certonly --standalone \
--standalone-supported-challenges http-01 \
--email your@email.address \
-d yourdomain.net

This will take a while.  Once this has completed, it should provide you with information about the certificate you just obtained, similar to below:

IMPORTANT NOTES:
- Congratulations! Your certificate and chain have been saved at
/etc/letsencrypt/live/*****/fullchain.pem. Your cert
will expire on 2017-08-29. To obtain a new or tweaked version of
this certificate in the future, simply run certbot-auto again. To
non-interactively renew *all* of your certificates, run
"certbot-auto renew"

Now, to use the certificate you just got in Home Assistant, we’ll need to edit the configuration.yaml file again.

http
  api_password: YOUR_SECRET_PASSWORD
  ssl_certificate:/etc/letsencrypt/live/*****/fullchain.pem
  ssl_key: /etc/letsencrypt/live/*****/privkey.pem

Make sure you update the path of the key and certificate to match your domain.

Once you’ve made changes to the configuration file, you’ll need to restart Home Assistant.

sudo systemctl restart home-assistant@homeassistant.service

As the certificates from Lets Encrypt expire in 90 days, it’s important to renew the certificate.

The previous script secures the certifcates for only the root user, so we’ll first need to update the permissions.

cd /etc/letsencrypt
sudo chown root:homeassistant live
sudo chown root:homeassistant archive
sudo chmod 750 live
sudo chmod 750 archive

Once this has been done, add the auto-renewal setup into cron.

crontab -e

(select nano, its easiest)

@daily /home/pi/certbot/certbot-auto renew

This crontab will attempt to renew the certificate on a daily basis, but you could go weekly or monthly if you prefer.

Troubleshooting

If you have any issues with your Home Assistant, (maybe a typo in the configuration file) checking the home assistant log file can give you information on what is wrong.

cat /home/homeassistant/.homeassistant/home-assistant.log

That’s it.  Home Assistant is now configured.  The next step is to attach devices/sensors and add automation. Next thing I’m going to do is add MQTT.

AD Connect Filtering

If you’ve installed Azure AD Connect to sync objects from your local Active Directory to Office 365, you may have seen that you can use filtering to stop objects being sync.  Yeah Yeah I hear you say, you can filter objects by the OU they’re in.. Yes you can, but you can also filter by attributes on objects, which as you can imagine can be very handy.

Check out the below link for some Microsoft doco on how to do this.

https://docs.microsoft.com/en-us/azure/active-directory/connect/active-directory-aadconnectsync-configure-filtering#attribute-based-filtering

The filtering examples in the above link can be used to filter in/out users from being sync’d to Office 365 Azure AD from your local AD. It uses the Extension Attributes (on the user objects) to perform the filtering. Once you AD Connect has been setup to do this filtering, the below PowerShell examples can be used to populate the relevant ExtensionAttribute with values which will be filtered to stop users being synced to Office 365.

The Microsoft Example uses ExtensionAttribute15 being set to ‘NoSync’. In my examples, I’ve used ExtensionAttribute8 and setting to ‘DoNotSync’ as that’s how was the ExtensionAttribute and value selected in our Azure AD Connect.

To find any users within your AD which have ExtensionAttribute8 set to ‘DoNotSync’

Import-Module ActiveDirectory
$users = Get-ADUser -Filter "ExtensionAttribute8 -eq 'DoNotSync'" -properties ExtensionAttribute8

 

To find any users within your AD which have ExtensionAttribute8 set to 'DoNotSync' within a specific OU

$users = Get-ADUser -Filter "ExtensionAttribute8 -eq 'DoNotSync'" -properties ExtensionAttribute8 -SearchBase "OU=UserAccounts,DC=FABRIKAM,DC=COM"

 

Or a specific user

get-aduser -Identity username -Properties ExtensionAttribute8

To Add 'DoNotSync' in ExtensionAttribute8 to a specific user

set-aduser -Identity username -Replace @{ExtensionAttribute8='DoNotSync'}

 

Connecting to an Oracle Db from PowerShell

Sometimes you need to be able to connect to databases from PowerShell, in this case an I needed to connect to an Oracle database. First, you’ll need to download and install the Oracle client. In my case, I used the win64 11gR2 client. Make sure you get the appropriate version to suit the version of Oracle (e.g. Oracle 11g) and the Windows machine you are installing it on (win64). You can download clients from here.

After that, all you need is the PowerShell code and knowledge of the database you’re going to connect to. The PowerShell code below can be used to connect by either an Oracle SID or Service Name – choose which works best for you.

## To connect by Service Name
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_servicename = "servicename"

## To connect by SID
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_sid = "sid"

## by SID
$connection = new-object system.data.oracleclient.oracleconnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SID=$ora_sid)));User Id=$ora_user;Password=$ora_pass;")

## by ServiceName
$connection = new-object system.data.oracleclient.oracleconnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=$ora_servicename)));User Id=$ora_user;Password=$ora_pass;")

$connection.open()

$query = "select attribute from table where (attribute = 'value') order by attribute"

$list_set = new-object system.data.dataset
$list_adapter = new-object system.data.oracleclient.oracledataadapter($query, $connection)
$list_adapter.Fill($list_set) | Out-Null
$list_table = new-object system.data.datatable
$list_table = $list_set.Tables[0]
foreach ($entry in $list_table) {
}

$connection.close()

Office 365 License assignment using AD Groups

If you’ve searched for Office 365 licensing using AD groups, you usually find PowerShell using AD groups to provision Office 365 licenses, but that’s not what this is.  It’s also not using Azure Automation to run PowerShell scripts within Azure to perform licensing.  This is Azure AD functionality (in Preview) to perform Office 365 licensing based on AD group membership.

It’s been announced only recently, and if you’re currently performing licensing of users in Office 365 with a PowerShell scripts, you should definitely have a look at this functionality.

Of course, this functionality is inside Azure Active Directory (currently in preview in the new Azure Portal). In order to assign licences via groups, you need to have an Azure AD licence. The Microsoft documentation states only an Azure AD Basic licence is required.  You can check that out information here.

If you just want to have a look at the group licensing functionality and don’t want to buy a license, you can sign up for a trial for the Basic and the Premium licences inside your Office 365 tenancy for no cost.  You could use a trial for either of these to give this a go.

If you don’t have Azure, you can sign up with a free trial. There is currently an offer to sign up and get $200 credit for 30 days.  Check it out here.

If you’re signing up for the Azure with a different account or have signed up to Office 365 and azure with different credentials, check out here to find out a how to connect them together. To assign the licences in Office 365 through Azure AD Preview, you need to be able to access the Azure AD that your Office 365 tenancy uses from within the Azure portal.

Once you’ve made it into Azure, it’s actually pretty straight forward. Microsoft have put a guide together on how to do this.  I didn’t think it was easy to find, so here is a link to it – link.

If you’re interested in the full process to apply the functionality to groups, I took a screenshot for the entire process in the Azure AD Preview GUI.

Of course, groups are used to assign the licences. It requires a security group and works with groups synced from an on premise directory and Azure Cloud only groups, both direct and dynamic groups. However, you should be aware that currently nested groups are not supported. If you want to use them regardless, be aware that licenses will only be applied to those users with direct membership to the group and not those in the nested groups.

I was pleasantly surprised as the licences re-evaluated quite quickly when the group was updated, both adding and removing licences according the group membership.

 

One of things you should be careful of with the groups is changes to the license configuration. Every time this is changed, it removes licenses from every user that is in that group and after that it reapplies the licenses to he new configuration.  If you’ve got a lot of users in the group, it could potentially cause an outage to users and risk data loss.

To work around this, I would lean towards two methods.

1. Create a new group with the wanted license configurations.  Add all the users from the old group into the new one.  Make sure the new licenses apply and remove the users from the old group.

2. Create multiple groups, with each group having a specific license applies to it. (E.g. A group which only applies the E3 Exchange Online part of the license).  For each part of the license you want to assign, create a new group – although you’ll have to watch out for license dependencies. (E.g. OneDrive requires SharePoint.)

Migration is quite interesting, as you can have the same license on the same user both assigned directly from PowerShell and inherited from groups. Both will exist and you can get conflicts between them.  The good thing is that if there is a conflict you can easily get results of the license application in Azure to find any conflicts.

The recommended migration path is to leave your PowerShell script in place while group licensing is configured to provide exactly the same licenses for your users that the PowerShell script applies.  Once the same licences have been applied by group, disable the licensing PowerShell Script.  Then you need to start to remove the licenses assigned with PowerShell. Of course this is recommended to be performed in batches.

If you’re used to checking if users are licensed, both the Office 365 admin portal and the existing PowerShell cmdlets cannot see the group license application – they can only see if there is a license applied.  There are some PowerShell scripts available to provide information on the number of licences assigned and what type, but it’s still limited.  You can check those out here.  Once Azure AD is out of preview, hopefully we’ll get visibility in Office 365 and PowerShell.