Having spent enough of my career building Dashboards for Security Programs in various tooling, Excel continues to be the most common. However as a cell-based platform it is far from ideal when dealing with objects in general. I have however discovered the following tricks.
Select Objects
Sometimes you want to just select all graphs, text boxes etc to set fonts, styles or just move them. Using ctrl + left click or cmd + left click is a PITA if you have more than a few objects. If you try and click and drag (like trying to drag a bounding box around the objects) you end up selecting cells.
Turns out there is a “mode” where you can switch to “Object Selection” rather than “Cell Selection”.
Under the Home ribbon → Find & Select → Select Objects
Stop Objects being affected by Cell Resizing
Another annoying thing about Excel is when you (or your Pivot Tables) adjust Cell size (width or height) your Objects either move, resize or just get messed up.
Use the Shape Options → Properties and make sure “Don’t move or size with cells” is selected.
Now it doesn’t matter what you do with the underlying cells – your objects stay where you put them!
Note: This project is currently on hold. I’m restoring these notes from an older site draft and may come back to complete the build later.
I wanted to design and build my own weather station using a microcontroller.
Requirements
Run on Arduino
Use COTS hardware components
Require minimal soldering
Be battery operated
Support solar charging
Integrate with OpenHab
Avoid destructive changes to major components
Hardware
Item
Details
MCU
Arduino MKR WiFi 1010
Environmental Sensors
MKR ENV Shield
Battery
LiPo 3.7V 3300mAh
5V Regulator
Adafruit MiniBoost 5V @ 1A – TPS61023
Solar Charger
Adafruit Universal USB / DC / Solar Lithium Ion/Polymer Charger (bq24074)
Solar Panel
2W 6V small polysilicon solar panel
Power
I knew I wanted the device to live outside and not be permanently plugged in, which meant battery power and a solar panel from the outset.
Sensors
Sensors for the MKR WiFi 1010 were easy enough to solve. I opted for an MKR ENV Shield that could simply sit on top.
The MKR ENV Shield includes three sensors: LPS22HB, HTS221, and TEMT6000.
LPS22HB
This is a compact absolute pressure sensor with a range between 260 hPa and 1260 hPa. Atmospheric pressure can be read using the readPressure() function.
One thing to watch is that the reading needs to be adjusted based on altitude. The sensor is calibrated to provide a reading at sea level, so if your location is above that you will need to apply an offset.
Find your altitude using GPS or an online source, then use a correction table such as:
As a sanity check, compare your readings with a trusted local weather service. In the UK, the Met Office is the obvious place to start. Local airport weather data can also be useful. In my case, the nearest weather station was roughly 250ft lower than my location, so I had to take that into account when comparing values.
HTS221
This sensor provides temperature and humidity readings. The main functions are:
readTemperature()
readHumidity()
TEMT6000
The TEMT6000 is a phototransistor that changes behaviour depending on the amount of light falling on it. It is designed to align reasonably well with human eye sensitivity, so in practice it gives you a good sense of ambient light intensity.
Its opening angle is ±60°, and while it peaks at 570 nm, it detects light in the range from 440 nm to 800 nm.
Using the readIlluminance() command returns a value measured in lux.
When it came to designing an enclosure, I quickly realised that with the solar panel mounted on the top, the phototransistor would be of limited practical value. I still kept the readings, as it was interesting to see whether I could detect dawn, daylight, dusk, and night from within the enclosure.
Real-Time Clock
The MKR WiFi 1010 includes a Real-Time Clock (RTC), which I wanted to use partly so that I could track basic uptime behaviour.
The WiFiNINA library includes a getTime() function that polls the DHCP-advertised NTP server and returns the epoch value. Combined with the setEpoch() function from the RTCZero library, this makes it straightforward to set the internal clock correctly.
rtc.setEpoch(WiFi.getTime());
OpenHab
OpenHab, for all its quirks, is perfectly capable here — but it rewards reading the documentation first rather than charging in and hoping for the best.
MQTT
From experience, I knew I wanted each polling cycle to send as little data as possible. I also wanted a stateless, fire-and-forget protocol. MQTT was the obvious fit, and OpenHab already supports it well.
MQTT works by having clients connect to a broker and publish messages to topics that other clients can subscribe to. In this case, the weather station publishes data and OpenHab subscribes to it.
As I was already running OpenHab on Ubuntu, I chose Mosquitto as the broker:
http://www.mosquitto.org
Recent Mosquitto builds enforce authentication by default. I did not particularly want that complexity on my internal home network, but it is still the sensible default. If you are going down this route, generate credentials for:
the IoT device
OpenHab
a spare admin/user account for yourself
Documentation for Mosquitto authentication is here:
You will want to think carefully about topic structure up front if you expect to add more IoT devices later.
Issues
Debugging
One of the more annoying practical issues during development was switching cleanly between debugging and normal runtime. Using Serial.print() and Serial.println() everywhere is fine for quick testing, but I wanted an easy way to turn debug output on and off.
A simple boolean flag worked well enough.
bool debugMode = false; // set to true for debug
...
if (debugMode) {
Serial.println("debug message here");
}
The same pattern can be used inside functions.
if (debugMode) {
Serial.print("Sensor A: ");
Serial.println(sensorAVal);
}
As noted under power management, I also needed a way to toggle low-power mode so that during debugging the Arduino would not go to sleep, while still waiting for the usual interval.
Power usage when running from battery turned out to be more of an issue than I expected. I was running out of power even after a full day of sun, which suggested I still had more optimisation to do around sleep states and overall power draw.
This is one of the areas I still intend to revisit if and when I pick the project back up.
If you use Templates in Obsidian and like to create an empty task item for quick typing, you will often find empty tasks showing up in your task lists.
The first trick is to exclude your Templates folder from Tasks. You can do this in the Tasks block as below. Replace Templates with wherever you store your templates.
```tasks
path does not include Templates
```
If you also leave empty tasks in your main notes, you can filter them out by including the regex below. This simply matches the start of a string immediately followed by the end of the string, with nothing in between.
```tasks
description regex does not match /^$/
```
If you combine them together as below, you should be covered for most scenarios.
```tasks
description regex does not match /^$/
path does not include Templates
```
On Windows Server with IIS, most people would recommend using the Web Platform Installer (WebPI). The problem is that only certain specific versions are available. If, for example, you want to upgrade from PHP 7.4.13 to 7.4.27, WebPI cannot do it. You have to handle it manually.
TL;DR: Download the new version, compare the php.ini files, and swap the directories over.
Instructions
Download the version of PHP you want directly from PHP for Windows:
Locate your current PHP installation directory. In my case, because I had originally installed PHP using WebPI, it was:
C:\Program Files\PHP
Extract the downloaded version next to the existing version and rename it with -new, as below.
You will want Notepad++ with the Compare add-on, or a similar text comparison tool.
Compare the currentphp.ini with the newphp.ini-production.
Important: If you originally installed PHP using WebPI, most of the changes will be towards the end of the current php.ini, particularly around extensions. Do not miss these, as I did.
Copy across all relevant settings from the current configuration to the new php.ini-production, and make note of any newly introduced settings.
Copy and rename the new php.ini-production to php.ini within the new directory.
Shut down IIS from IIS Manager — at the server level, not just the site level.
Swap the PHP directory names around.
Start IIS again.
Acknowledgements
The post below was useful when I was working through this:
I recently bought the Hozelock Cloud Controller to automate watering in my garden. While trying to integrate it with OpenHAB, I discovered a number of useful and slightly concerning things about the REST API used by the service.
This post reflects what I found in 2019, so it should be read as a snapshot of the product and API behaviour at the time rather than a current assessment.
The Cloud Controller is made up of two parts: the hub, which connects to your router/LAN, and the controller, which connects to the hub and is attached to the outdoor tap.
Discovery
By using the Hozelock cloud support page and entering a valid Hub ID, you can retrieve the status of the hub and controller. Inspecting the browser traffic showed a GET request to the following endpoint, returning a JSON array:
GET http://hoz3.com/restful/support/hubs/{hubId}
The first thing I noticed was that there appeared to be no authentication on the endpoint, you only needed a valid Hub ID.
If you make a direct call using an invalid Hub ID, the endpoint is kind enough to tell you the expected regular expression for a valid ID.
GET http://hoz3.com/restful/support/hubs/xxxxxx
{
"errorCode": 134611463,
"httpStatus": 400,
"errorMessage": "GET request to '/restful/support/hubs/xxxxxx' failed due to invalid parameter 'hubID': Expected a valid Hub ID matching [0-9A-Z]{6}",
"cause": {
"method": "GET",
"url": "/restful/support/hubs/xxxxxx",
"contentType": null
}
}
If anybody wants to do the maths on the total number of possible permutations, I would still be interested.
It is also worth noting that this all worked over HTTP. HTTPS was supported, but it was not forced and HTTP did not appear to redirect. Throughout this post I may mix HTTP and HTTPS, but the point is that both appeared possible unless stated otherwise.
From there, I started looking at what else the API supported.
There is nothing especially exciting here from an action point of view, but the data returned for the hub can include potentially sensitive information such as location if the user has configured it. From memory, this appeared to include things like city, country, local time, and time zone.
Next, I worked through the rest of the JSON returned by GET /{hubId}.
Schedules
GET https://hoz3.com/restful/support/hubs/{hubId}/schedules
This returns an array of all schedules for the hub. Note that this does not necessarily mean the schedule is currently applied to the controller.
In the sample I captured, the watering was configured for sunrise and sunset, which meant two entries per day.
Timestamps appeared to use Unix epoch milliseconds. -2000 appeared to mean sunrise and -1000 appeared to mean sunset (or an offset from them). Duration was also expressed in milliseconds.
This was far more interesting, as POST, PUT, and PATCH appeared to allow schedule creation, replacement/upsert, and updates. Even DELETE was available.
At the time I was able to adjust a schedule using PATCH by changing the name, but I never fully worked out the complete payloads for creating, updating, or deleting schedules.
Controllers
One or more controllers can be associated with a single hub, for example, one for the front garden and one for the rear.
This section is largely credited to anthonyangel (see Credits below).
There appeared to be an undocumented part of the API called Actions, which allowed tasks to be submitted for the hub, and therefore the controller, to pick up.
Using the request structures worked out by anthonyangel, it was possible to send a POST with a body to issue a waterNow command.
POST http://hoz3.com/restful/support/hubs/{hubId}/controllers/actions/waterNow
Request Body
{
"controllerIDs": [{controllerId}],
"duration": 300000
}
Response
{
"errorCode": 0
}
Sure enough, after the controller’s polling delay, up to around 20 minutes, the phone app reported that watering had started.
Available actions
pause — pauses watering for a number of days
unpause — removes a pause
adjust — appears to increase watering duration by a percentage for a period of time
unadjust — removes the adjustment
waterNow — starts a watering session
stopWatering — stops watering
setMode — seemed to expect a mode parameter, though I did not fully decode it
ping — despite being listed, it did not appear to be implemented when I tested it
One useful pattern here was that if you built a payload with only controllerIDs, the error responses often told you what additional data was missing. Having the mobile app to hand also helped when trying to infer how API calls mapped to UI actions.
Conclusions
As is often the case with newer IoT products, security seemed to have been treated as secondary. Admittedly, the impact of somebody tampering with a garden watering system is not exactly catastrophic, perhaps they waste some water or kill a few plants, but it still reflects poor design choices.
That said, the controller → hub → cloud model appeared to limit the risk of this leading directly to compromise of the wider LAN.
Basic measures such as forcing HTTPS appeared to have been missed. Enabling and enforcing TLS at the service edge is hardly unusual, although I accept there may have been implementation constraints on the hub side that were not obvious from outside.
Authentication on the endpoints, even for GET operations, should have been a minimum expectation. Relying on obscurity is not a sensible control. Others had also reported an apparent lack of rate limiting, which meant brute-forcing Hub IDs looked technically feasible.
At the time, I also wondered whether companies like Hozelock should provide proper developer portals, API documentation, and supported integration mechanisms. That would not remove the need for real security controls, but it would at least reduce the incentive for enthusiasts to reverse engineer the platform just to make it work with their home automation stack.
Overall, I really liked the product. I am terrible at remembering to water the garden, and this solved a genuine problem for me. Initial setup was a bit painful, I suspect because of previous pairing attempts, but a full factory reset of the hub and controller fixed it.
More broadly, it felt like another example of an IoT product reaching market before security had been given the attention it deserved. Companies building connected devices need to treat security design with the same seriousness they would apply to their internal applications and services.
At the time, I looked for somewhere to report these issues but could not find an obvious route.
Credits
The original inspiration for digging into this came from the Home Assistant community, particularly a post by anthonyangel:
If you need to start, stop, or restart IIS from the command line, the quickest option is to use iisreset.
Commands
Start IIS:
iisreset /start
Stop IIS:
iisreset /stop
Restart IIS:
iisreset
Note
iisreset restarts the entire IIS service, so use it with care on shared or production servers. If you only need to restart a specific site or application pool, IIS Manager or PowerShell may be a better option.
When naming a SQL Server instance, it is worth keeping a few practical rules in mind.
General rules
Instance names are limited to 16 characters.
The first character must be a letter.
Names cannot contain spaces or special characters such as \, /, :, *, ?, ", <, >, or |.
Avoid names that are difficult to distinguish from the host name or from other instances already in use.
Keep names short, clear, and meaningful.
Good practice
In addition to the technical limits, a sensible naming convention will save confusion later.
A good instance name should:
make it obvious what the instance is for
be consistent across environments
be easy for administrators and support teams to recognise
avoid unnecessary abbreviations unless they are well understood internally
For example, you might choose names based on:
application or service
environment, such as DEV, TEST, or PROD
region or business unit, if that is relevant
Examples
Reasonable examples might include:
APP01
FINANCE
REPORTING
CRMTEST
Poor examples might include:
SQL Server
123SQL
INSTANCE!
THISISFARTOOLONGFORSQL
Final thought
The technical rules are simple, but the operational impact of a poor naming convention can linger for years. A clear, consistent standard is usually worth agreeing early.
SQL Server version numbers typically look something like 13.0.5026.0.
The list below reflects the major version numbering as it stood at the time of writing and is intended as a quick reference rather than a complete current release matrix.
SQL Server 2017 — 13.x.xxxx.x
SQL Server 2016 — 12.x.xxxx.x
SQL Server 2012 — 11.x.xxxx.x
SQL Server 2008 R2 — 10.50.xxxx.x (final build 10.50.6000.34)
SQL Server 2008 — 10.0.xxxx.xx (final build 10.0.6000.29)
SQL Server 2005 — 9.xx.xxxx.xx (final build 9.00.5000.00)
Determine SQL Server version
To determine the version of SQL Server, you can use any of the following methods.
Method 1: Connect to the server using Object Explorer in SQL Server Management Studio. Once connected, the version information is typically shown in parentheses, alongside the username used to connect to the instance.
Method 2: Look at the first few lines of the ERRORLOG file for that instance. By default, the error log is located under:
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
and archived logs will appear as ERRORLOG.n.
The entries may look something like this:
2011-03-27 22:31:33.50 Server Microsoft SQL Server 2008 (SP1) -
10.0.2531.0 (X64)
March 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit)
on Windows NT 6.1 <X64> (Build 7600: )
This entry provides the key product information, including version, service pack/update level, architecture, edition, and the operating system version.
Method 3: Connect to the instance and run:
SELECT @@VERSION
An example of the output is:
Microsoft SQL Server 2008 (SP1) -
10.0.2531.0 (X64)
March 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit)
on Windows NT 6.1 <X64> (Build 7600: )
Method 4: Connect to the instance and run the following in SQL Server Management Studio:
