Category Archives: Miscellaneous

Anything and Everything

Schedule WinSCP Folder Sync

WinSCP is one of those tools that makes my Windows-life much more useful. I was originally planning on using syncthing, but for my purposes this was proving to be not a great match.

I needed to copy files from our Windows/Samba shares over to the paperless-ngx server I recently spun up (side note, this is amazing for saving shipping docs, customer PO’s, and other pdfs the org creates). Unfortunately, paperless-ngx will delete the uploaded PDF (located in the consume directory) once it’s been ingested into the system. This tells syncthing “hey, the file is missing, so re-upload it”. Repeat. The good thing is that paperless will NOT keep adding the files as duplicates. The bad thing is that the files now live in several places, eating up precious disk space.

I also did not want non-PDF’s to appear in paperless. Since this was a new project, I also didn’t want to copy any documents older than 30 days (we have 8+ years of archival data). Enter winscp.com – not the URL, but the CLI application.

I created a batch file with the following – obviously change to fit your needs. I should note that SCP requires the hostkey to be setup; this can be found by using the WinSCP GUI, connecting to the paperless-ngx server, and then right-click on the connection tab and select “Generate session URL/code” (it also provides the basis for the script below).

@echo off

"C:\Program Files (x86)\WinSCP\WinSCP.com" ^
/log="C:\temp\Winscp_ShippingComputer_Sync.log" /ini=nul ^
/command ^
"open scp://USERNAME:PASSWORD@10.101.110.16/ -hostkey=""ssh-ed25519 255 03rzYT4K2ufZ2yY3cG4Z5/thG8/dB3UbTi9F7ja9uHY""" ^
"synchronize remote "Y:\Shipping_Scans" "/home/linuxuser/paperless-ngx/consume" -rawtransfersettings ExcludeHiddenFiles=1 -filemask=""*.pdf>=30D""" ^
"exit"

set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0 (
echo Success
) else (
echo Error
)

exit /b %WINSCP_RESULT%

HAProxy and Kace SMA

Running Kace SMA (K1000) version 11.0.273. Wanted to run it behind a reverse proxy so it wasn’t directly exposed to the internet. Generally speaking, you really don’t want things like webservers directly exposed to the internet for obvious reasons, but KACE has been really adamant about security and running it in a DMZ and it hasn’t had any known intrusions to date.

In any case, I decided to move it out of the DMZ and run HAProxy in its place. I have my outside-of-this-documents-scope reasons. However, KACE SMA 10.x didn’t play nicely with reverse proxies even with legit SSL’s installed (you can read about konea certificates vs web ones on the kace support pages).

Upgraded to version 11 and from there it splits into two – konea certificates are unpublished but necessary, and then our web certificates for all other traffic. And yes, all traffic does terminate to port 443, but the konea side changes to a new port.

global
        log /dev/log    local0
        log /dev/log    local1 notice
        chroot /var/lib/haproxy
        stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
        stats timeout 30s
        user haproxy
        group haproxy
        daemon
        lua-load        /etc/haproxy/acme-http01-webroot.lua

        # Default SSL material locations
        ca-base /etc/ssl/certs
        crt-base /etc/ssl/private

        # See: https://ssl-config.mozilla.org/#server=haproxy&server-version=2.0.3&config=intermediate
        ssl-default-bind-ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE>
        ssl-default-bind-ciphersuites TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256
        ssl-default-bind-options ssl-min-ver TLSv1.2 no-tls-tickets

defaults
        log     global
        mode    tcp
        option  tcplog
        option  dontlognull
        timeout connect 5000
        timeout client  50000
        timeout server  50000
        errorfile 400 /etc/haproxy/errors/400.http
        errorfile 403 /etc/haproxy/errors/403.http
        errorfile 408 /etc/haproxy/errors/408.http
        errorfile 500 /etc/haproxy/errors/500.http
        errorfile 502 /etc/haproxy/errors/502.http
        errorfile 503 /etc/haproxy/errors/503.http
        errorfile 504 /etc/haproxy/errors/504.http

frontend http-in
        bind *:80
        acl url_acme_http01 path_beg /.well-known/acme-challenge/
        http-request use-service lua.acme-http01 if METH_GET url_acme_http01


frontend sma
#       bind *:443 ssl crt /etc/letsencrypt/live/pem/
        bind *:443
        tcp-request inspect-delay 5s
#       tcp-request content capture req.ssl_sni len 25
        tcp-request content accept if { req_ssl_hello_type 1 }


        use_backend sma_agent if { req_ssl_sni konea }
        use_backend sma_webui if { req_ssl_sni mykacesite.domain.tld }
        default_backend sma_webui

backend sma_webui
        server sma_apache 10.130.20.6:443

backend sma_agent
        server sma_koneas 10.130.20.6:52230


I should point out that I also am utilizing letsencrypt for the web portion, however, by switching to tcp mode instead of http it bypasses that requirement. TCP mode is REQUIRED for the konea portion to actually function.

Remove Hidden KACE Agent

We utilize KACE (SMA K1000) for our helpdesk/ticketing, and inventory management. Part of our deployment packages include the KACE agent – this agent is required to send back data about the system it is installed upon (username, OS specs, hardware specs, etc).

Unfortunately, there are some times that the KACE agent doesn’t play nicely and it needs to be reinstalled. Or, in my case (pun intended), I needed to make a new GM image for deployment and it is recommended to NOT have the KACE agent installed prior to sysprep.

Open an administrator command prompt:

wmic product where "name like '%kace%'" call uninstall /nointeractive

This will find and remove any “KACE” related software currently residing on your system. You should see the messages “Method execution successful” and “ReturnValue = 0” if this runs successfully.

Note: This does not remove any existing firewall rules or files created outside of the standard install/uninstall configuration.

Stuff I Use

After recently chatting with some friends about various technologies I utilize, I figured it would be a good idea to just document all of the various products with a brief overview/review of each.

Networking

Cisco Switching
Cisco Catalyst 3560G 24 port Layer 3 Switch (my old core).
Unifi Switching
I switched (pun intended) to Unifi equipment a while back due to the price, ease of configuration, and the “underdog appeal”.
I have a US-24-250W, and 2x US-8-60W
Unifi Access Points
I’ve used Unifi AP’s ever since my free Meraki bricked itself due to lack of support contracts. Currently using Unifi UAC-AP-PRO’s (quantity 2).
All of the Unifi equipment is controlled by a Cloud Key Gen 2 Plus.
Untangle UTM
I’ve used Untangle over PFSense for a while now. I like the ability to alter everything within PFSense, and the wicked speed of it, but overall the ease-of-setup and the “it just works” of Untangle won me over. Well worth the $50 annual home license fees.
Protectli Firewall J3160 with 4GB RAM, 32GB MSATA.
Although if I had an opportunity to do it again, I’d get the upgraded model instead with its J3160, 8GB RAM, 120GB SSD.
Powerline
After trying 2 different netgear models, trendnet, and 2 different linksys models, I learned of a rather unknown manufacturer called Extollo. I use Powerline LANSocket 1500 for my hard-to-reach-network-places in the house.

Systems

I have too many systems and may eventually list them here. Currently typing on an MacBook Air while watching a movie streamed from my Plex server that’s running on a proxmox hypervisor on a supermicro server. Collectively we have 5 laptops, a gaming desktop, and 4 servers (only 1 is currently powered on to save some money). That plex box also uses a NAS from QNAP TS-228 and nas4free/ubuntu/debian/etc.

Security

I’ve used cameras both as a hobby (home use) as well as at several employers on a professional basis. These include cameras from Axis, HikVision, SuperCircuits, Unifi, Nest, Ring, and Blink.
In the house I have a Nest camera (with no storage plan) and a single Unifi UVC-G3-Micro. Outside I run the Blink XT2’s, a UVC‑G3, and a UVC‑G3‑DOME.
I also use the Nest Protects on each level of the house.

Home Automation

Honeywell Wi-Fi Smart Color Thermostat
Caseta Wireless Lighting Controls
Chamberlain MyQ Garage Door
Alexa – 2 Echo’s, an Echo Dot, and an Echo Show (5″ display)
Wink2
Panicky Solar Motion lights 3 in the backyard for the dog and 1 on the side for the garbage cans.

Power

After a 30+ hour power outage, we decided to get a standby generator. A ton of research later, we got a Kohler 14RESA Generator with service-entrance ATS. I have OnCue monitoring enabled so I can get alerts on the usage.
After a bit of a windfall of stock options, I moved forward with 15x 285w solar panels and an 5000w inverter.
Driving an electric car, we needed to enable some faster-than-120v-charging at home. Enter 50AMP 240v circuit! Ended up getting an GoPlug ESVE Car Charger.
Rechargable batteries from Fuvaly have been pretty awesome in our various remote controls.
We’ve signed up for the hourly pricing from our provider, so I also have a Rainforest EMU 2 to monitor our current power usage.

Telecom and Internet

Wowway
Tmobile
Yealink on voip.ms SIP
Apple

Other

I’m a flashlight collector.
Car, Flashlight, toilets, humidifer, ac/heat, sumppumps zoeller TV, audio equipment, roku

Reset WordPress Password

Taking over the IT department when the previous IT regime had zero plans on how to integrate the series of businesses they had taken over in the past several years makes for some fun times. I have 4 different godaddy accounts, a couple DH accounts, and even one from a German company I had never heard of. And I had to fight, beg, talk, email, reverse engineer, and guess on several logins. Something something “no documentation”.

That being said, I’ve also had the responsibility of migrating and managing some of our wordpress sites and was SOL when it came to logins. Luckily GD, DH, and even the German cpanel host company all allowed for some sort of mysql access – whether that was shell access or phpmyadmin – so I could “easily” reset the credentials.

On Dreamhost using SSH:
mysql -h MYSQL.DOMAINNAME.TLD -u MYDBUSERPASSWORDFROMTHEPANEL -p
Enter your DB User password
show databases;
use DATABASENAMEHERE;
show tables;
Look for one with “users” at the end (eg wp_users)
List the Users Table along with the ID you’ll need later (First Column)
select id, user_login, user_pass from NAMEOFUSERTABLE;
update NAMEOFUSERTABLE set user_pass = MD5('YOURNEWPASSWORDHERE') where ID = NUMBERFOUNDABOVE

Through PHPMYADMIN
Open PHPMyAdmin and click on the WP database
Find the “Users” table (eg wp_users)
Click on Browse
Click on edit by the user for which you desire to change the password
Where it says “user_pass” change the function drop down to MD5 and then type in a plain text password.
Hit save/submit

Unifi Linux and Windows Certificates

I thought I knew it all about certificates, but then I was humbled once again.

I needed to “secure” an internal linux webserver using our Windows 2016 CA as to remove the “this is an unverified site” messages that liked to pop up when browsing the various sites.

The process I had done in the past was to create the CSR using openssl, then copy the encryption data, open up my trusty http://certserverhere/certsrv/ site and go through the process of making a webserver certificate. Then, when finished, just download the certificate and the CA + chain, import on linux, and profit.

Well, the new versions of the templates (V3 and V4 specifically) no longer allowed the web enrollment using my trusty http://certserverhere/certsrv site. Booo.

I could probably get it to work by just requesting my own certificates using the MMC, but I’m still leaning towards the whole CLI phase of life. I should also note that I find the performance and management of Unifi on Linux to be significantly better and easier than that on Windows. YMMV.

By the way, this is technically how I published a certificate on our Unifi wireless controller. The CA Certificate Authority is a 2016 Windows Server that’s been published in AD. The unifi machine is running Ubuntu 17.10 and unifi version 5.6.29. I also used WinSCP, Putty, and my base machine is Win10 (not super applicable).

SSH to the Unifi Machine
(I did this as root, so add “sudo” before commands if you’re not the root god)
cd /usr/lib/unifi
java -jar lib/ace.jar new_cert unifi.domain.tld CompanyName Town State Country
This creates unifi_certificate.csr.der and unifi_certificate.csr.pem – the DER is encrypted and the PEM is what we need.

Get the PEM over to your CA Server
I just used nano to view all the data and then copy pasted, but feel free to WinSCP it over as well
nano unifi_certificate.csr.pem
Copy this text, then on the CA create a new text file and paste the data there. Save.

Certreq
Open an administrative Command Prompt on your CA server
certreq -submit -attrib "SAN:dns=unifi.yourdomain.tld&dns=unifi" -attrib "CertificateTemplate:WebServer2018" unifi_certificate.csr.pem
By default your Certificate Template will be “WebServer” instead of the one I listed above – I created my own template with the year it’s valid for the sake of record keeping.

Save the Certificate
Assuming the request went through, you’ll be able to name and save your signed certificate. In my case I named it unifi_withSAN.domain.tld.cer. I also navigated to the http://certserverhere/certsrv site and downloaded the CA certificate, Certificate chain, or CRL (I just downloaded the CA Certificate as it’s a single host with no subs).

Copy it back to Unifi
I used WinSCP to copy both the signed certificate as well as the CA Certificate I downloaded back to my /home directory on the Unifi server.

Final Touches
Back on your Unifi SSH session (in the /usr/lib/unifi directory)
java -jar lib/ace.jar import_cert /home/unifi_withSAN.domain.local.cer /home/srv-cert01-ca.cer
Replace srv-cert01-ca with the name of your CA certificate.
If successful, restart the unifi services
service unifi restart

Close your browser and open back up to https://unifi:8443 and no more error!

Solarwinds Syslog Database Cleanup

So my last senior systems administrator decided to install solarwinds on a virtual machine as a standalone package (solarwinds, licensing, sql express). He came to me a day or two later saying that he needed to migrate the database from SQL express to our production SQL server as the instance was at the maximum allowed by SQL Express. He said it was eating up almost 20GB of space – which means he filled up the first database and created a secondary and then filled that one up too (SQL Express has a 10GB per database limit).

After being unable to migrate the database from Express to Standard for 2 days, he just starts it over on the production SQL instance. Long story short we were chewing through about 18GB of database disk space every day. The admin had, for some reason, enabled syslog with Debugging on all network equipment. Damn.

So I needed to delete about 180GB worth of syslogs and, knowing my previous experiences between delete and truncate, decided to just drop the entire table:

Truncate all syslog:
Open SQL Studio Manager
Run a new query
Truncate Table Syslog

Delete Old Syslogs:
Open SQL Studio Manager
Delete from Syslog Where datetime <= '4/24/2016'