Category: System Administration

MySQL: Moving Data From One Table To Another

Our OpenHAB persistence data is stored in MySQL. There’s an “items” table which correlates each ItemName string to an ItemID integer. There are then Item#### tables that store persistence data for each item. If you rename an item, this means a new table is created and previous persistence data is no longer associated with the item. For some items, that’s fine — I don’t really care when the office light was on last month. But there’s persistence data that we use over a long term — outdoor temperature, luminance, electrical usage. In these cases, we want to pull the old data into the new table. There’s a quick one-liner SQL command that accomplishes this:

INSERT INTO NewTable SELECT * from OldTable;
e.g. INSERT INTO Item3857 SELECT * FROM Item3854;

You can drop the old table too:

DROP OldTable;

But I run a cleanup script against the item list so often don’t bother to remove tables one-off.

Adding member to MS Teams without admin rights or Graph API

# To run on Linux, you need the preview mode of AzureAD
# Register-PackageSource -Trusted -ProviderName ‘PowerShellGet’ -Name ‘Posh Test Gallery’ -Location https://www.poshtestgallery.com/api/v2/
# Install-Module -Name AzureAD.Standard.Preview

# Windows, the module is
# Install-Module -Name AzureAD

# I’m lazy and just typed my creds for a proof of concept; real implementation would use the SecureString thing in the connect-azuread. See:
# https://www.rushworth.us/lisa/?p=3294
connect-azuread

# Get the object ID for the group and the user
$objMyGroup = get-azureadgroup -SearchString “LJR Sandbox Team”
$objNewMember = get-azureaduser -searchstring “NewGuy”

# Add the user to the group
add-azureadgroupmember -ObjectID $objMyGroup.ObjectId -RefObjectID $objNewMember.ObjectId

Identifying System-Only AD Attributes

This information is specific to Active Directory. MSDN has documentation for each schema attribute — e.g. CN — which documents if the attribute is “system only” or not.

For an automated process, search at the base cn=schema,cn=configuration,dc=example,dc=com with the filter (&(ldapDisplayName=AttributeName))and return the value of systemOnly. E.G. this shows that operatingSystemServicePack is user writable.

***Searching...
ldap_search_s(ld, "cn=schema,cn=configuration,dc=example,dc=com", 2, "(&(ldapDisplayName=operatingSystemServicePack))", attrList,  0, &msg)
Getting 1 entries:
Dn: CN=Operating-System-Service-Pack,CN=Schema,CN=Configuration,dc=example,dc=com
systemOnly: FALSE;

You can also list all of the system-only attributes by using the filter (&(systemOnly=TRUE)) and returning ldapDisplayName

***Searching...
ldap_search_s(ld, "cn=schema,cn=configuration,dc=example,dc=com", 2, "(&(systemOnly=TRUE))", attrList,  0, &msg)
Getting 189 entries:
Dn: CN=OM-Object-Class,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: oMObjectClass; 

Dn: CN=Canonical-Name,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: canonicalName; 

Dn: CN=Managed-Objects,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: managedObjects; 

Dn: CN=MAPI-ID,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: mAPIID; 

Dn: CN=Mastered-By,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: masteredBy; 

Dn: CN=Top,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: top; 

Dn: CN=NTDS-DSA-RO,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: nTDSDSARO; 

Dn: CN=Application-Process,CN=Schema,CN=Configuration,dc=example,dc=com
lDAPDisplayName: applicationProcess; 
...

 

Asus Router NVRAM Usage

I had a really strange problem with an Asus router — the port forwarding disappeared. And while I could use the UI and put everything back in, it didn’t stick around. Turns out the NVRAM was full — there wasn’t anywhere to put the port forwarding rules (vts_rulelist). Fortunately, there were a few old DHCP reservations I was able to delete and free up some space. For future reference, the following command reports what is using the NVRAM.

nvram show | awk '{print length(), $0 | "sort -n -r"}' | cut -d"=" -f 1

Network Manager GUI

You can use nmcli to configure network interfaces controlled by NetworkManager. But, honestly, I don’t see any advantage to learning the cryptic CLI instead of using the cryptic config file stuff I’ve already learned. And yet … I need my server to have /etc/resolv.conf populated when it reboots. So I figured out how to launch the KDE system settings (assuming you’ve got the X display redirected to your host) — systemsettings5

Mine takes a few minutes to render, during which time the window is black and a handful of errors are written out to the console. But it got there eventually, and I was able to edit the network interface.

Mosquitto 1.6.8 With Websockets Becomes Unresponsive

When I installed software on our new server, I got the “latest and greatest”. And have found that my mosquitto server hangs after about 20 minutes. No errors. Even strace doesn’t show anything beyond it not doing anything. I am able to use the command line utilities to confirm that the service isn’t there even though it looks like it is working.

Subscribe to the subtree of a topic:

mosquitto_sub -h mqtt.example.com -t testtopic/#

Publish a message to a topic:

mosquitto_pub -h mqtt.example.com -t testtopic/data/lisa -m "Test4"

To test WebSockets, I’ve put together a Python script that subscribes to a topic. Changing the commented lines switches between the WebSocket reverse proxy, the old and new MQTT servers via WebSockets, and the old and new MQTT servers directly in an attempt to isolate what is wrong.

import sys
import paho.mqtt.client as mqtt

def on_connect(mqttc, obj, flags, rc):
    print(f"rc: {str(rc)}")

def on_message(mqttc, obj, msg):
    print(f"{datetime.datetime.now()}: {msg.topic} {str(msg.qos)} {str(msg.payload)}")

def on_publish(mqttc, obj, mid):
    print(f"mid: {str(mid)}")

def on_subscribe(mqttc, obj, mid, granted_qos):
    print(f"Subscribed at {datetime.datetime.now()}: {str(mid)} {str(granted_qos)}")
def on_log(mqttc, obj, level, string):
    print(string)
# Client uses websockets
mqttc = mqtt.Client(transport='websockets', client_id="ljrTestingPythonScript", clean_session=False)
# Client uses MQTT directly
#mqttc = mqtt.Client(client_id="ljrTestingPythonScript", clean_session=False)

mqttc.username_pw_set("whateveruser", "wh@t3v3rP@s5w0rd")
mqttc.on_message = on_message
mqttc.on_connect = on_connect
mqttc.on_publish = on_publish
mqttc.on_subscribe = on_subscribe

#mqttc.connect("oldmosquitto.example.com", 80, 60)
mqttc.connect("newmosquitto.example.com", 80, 60)
#mqttc.connect("newmqtt.example.com", 1883, 60)
#mqttc.connect("newmqtt.example.com", 9001, 60)
#mqttc.connect("oldmqtt.example.com", 1883, 60)
#mqttc.connect("oldmqtt.example.com", 9001, 60)

mqttc.subscribe("owntracks/#", 0)

mqttc.loop_forever()

None of this helped, unfortunately. The reverse proxy couldn’t communicate with the MQTT server because it was unresponsive. Attempting to communicate with the server directly fails too.

I see a few issues in the Mosquitto repository that are similar — and the current discussion indicates that libwebsockets 3.2.0 introduced some incompatibility that they’ve addressed in Mosquitto 1.6.7 … since my problem relates to WebSockets, I wanted to try running the iteration before whatever changed.

git clone --branch v3.1.0 https://libwebsockets.org/repo/libwebsockets
cd libwebsockets
mkdir build
cd build
cmake ..
make
make install

git clone --branch v1.6.2 https://github.com/eclipse/mosquitto.git
cd mosquitto
vi config.mk # Change WITH_WEBSOCKETS:=no to :=yes
make
make install

ln -s /usr/local/lib/libwebsockets.so.14 /lib64/
ln -s /usr/local/lib/libmosquitto.so.1 /usr/lib64/libmosquitto.so.1

/usr/local/sbin/mosquitto -v -c /etc/mosquitto/mosquitto.conf

We’ll see if this is more reliable. It’s been up for 30 minutes … which is longer than the 1.6.8 iteration managed to run.