Category: Technology

Oracle: Query Timestamp Today

I wanted a quick way to query a logging table for today’s records. I figured there had to be some way to put a variable “today” into the query rather than put in the numbers for the current date. Voila — a query that retrieves records where the timestamp is greater than or equal to today:

select *
from ossa_central_logging
where action_ts >= cast(trunc(current_timestamp) as timestamp)
order by action_ts desc;

Notes on Adding Drive to Linux Host

Create partition, format, find UUID, and add line to fstab to mount the volume

[lisa@linuxhost ~]# parted /dev/sdb
GNU Parted 3.2.153
Using /dev/sdb
Welcome to GNU Parted! Type ‘help’ to view a list of commands.
(parted) mklabel GPT
Warning: The existing disk label on /dev/sdb will be destroyed and all data on this disk will be lost. Do you want to continue?
Yes/No? y
(parted) mkpart primary 2048s 100%
(parted) q
Information: You may need to update /etc/fstab.

[lisa@linuxhost ~]# mkfs.xfs -f /dev/sdb1
meta-data=/dev/sdb1 isize=512 agcount=4, agsize=163839872 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=1, sparse=1, rmapbt=0
= reflink=1
data = bsize=4096 blocks=655359488, imaxpct=5
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0, ftype=1
log =internal log bsize=4096 blocks=319999, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0

[lisa@linuxhost ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 19G 0 part
├─fedora-lisa 253:0 0 17G 0 lvm /
└─fedora-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 2.5T 0 disk
└─sdb1 8:17 0 2.5T 0 part
sr0 11:0 1 650M 0 rom
[lisa@linuxhost ~]# blkid | grep sdb1
/dev/sdb1: UUID=”801ebed3-ddd6-459d-bd62-04a0a75f91b8″ TYPE=”xfs” PARTLABEL=”primary” PARTUUID=”b9a9a340-28f5-4efb-b649-af804ef5bc4c”

Add a line to /etc/fstab to mount the volume — here I’m mounting it to /mnt/data/mythtv:

UUID=801ebed3-ddd6-459d-bd62-04a0a75f91b8 /mnt/data/mythtv xfs defaults 0 0

SolarWinds Attack and Access to MS Source Code

Reading Microsoft’s publication about their impact from the SolarWinds hack, I see the potential for additional (unknown) attack vectors. Quoted from MS:

“We detected unusual activity with a small number of internal accounts and upon review, we discovered one account had been used to view source code in a number of source code repositories. The account did not have permissions to modify any code or engineering systems and our investigation further confirmed no changes were made. These accounts were investigated and remediated.”

 

While the potential for attackers to have read something that provides them some sort of insight is obvious, the less obvious scenario would be the SolarWinds attack having obtained credentials with write access elsewhere. Worst case, even inserting another attack vector as was done in the SolarWinds attack. That’s a good reason to establish firewalls with least-required access (i.e. nothing can get to any destination on any port unless there’s a good reason for that access) instead of the internally wide open connectivity that I’ve seen as the norm (even in places with firewall rules defined, I’ve seen servers where either everything is allowed or low ports are blocked but >1024 is opened).

 

 

OwnTracks WebSockets MQTT SSL Error

A few weeks ago, we stopped getting location updates from OwnTracks on our phones. Checking the status, I see an error indicating that the connection failed because my certificate does not have a SAN. Which … true, it does not. I knew some consortium agreed that all certs should have SAN values (and RFCs had been updated to reflect this new direction). Evidently version 2.2.2 of OwnTracks has added SAN verification. I reissued the certificate from my CA and added a SAN. I had to put the cert on both my MQTT websockets reverse proxy and the mosquitto server; but, once both were using the new cert, OwnTracks connected and cleared through the queued updates.

VSCode Search/Replace Using Regex Capture Groups

Regex adds a lot of flexibility to search/replace operations. Capture groups allow you to manipulate the found data in the replacement text. As an example, I have simple mathematical equations that are not spaced out reasonably. I could replace “+” with ” + “, “-” with ” – “, “*” with ” * “, “/” with ” / “, and “=” with ” = “, but using a capture group to identify non-whitespace characters and the range of operators allows a single search/replace operation to add spaces to my equations.

Selecting the regex option (in blue below), I can use the regular expression (\S+)([\+,\-,\*,\/])(\S+)=(\S+) as my search string. This means the first capture group is one or more non-whitespace characters, the second capture group is one of the characters +,-,*,/, the third capture group is one or more non-whitespace characters, there’s an equal sign (which I could make into a fourth capture group), and the fourth capture group is one or more non-whitespace characters.

An alternate regex finds zero or more whitespace characters — (\S*)([\+,\-,\*,\/])(\S*)=(\S*)

The replacement text then uses each capture group — $1 $2 $3 = $4 — to add spaces around the operators and around the equal sign.

 

Discourse acme.sh Script Failure

I had a hellacious time updating the certificate on my Dockerized Discourse server — the acme.sh script doesn’t have a slash delimiter between the hostname and the ./well-known folder within the URI. Which means the request fails. Repeatedly.

 

[Sat Oct 10 00:01:09 UTC 2020] _post_url='https://acme-v02.api.letsencrypt.org/acme/chall-v3/7784162898/nr42-g'
[Sat Oct 10 00:01:09 UTC 2020] _CURL='curl -L --silent --dump-header /shared/letsencrypt/http.header -g '
[Sat Oct 10 00:01:10 UTC 2020] _ret='0'
[Sat Oct 10 00:01:10 UTC 2020] code='200'
[Sat Oct 10 00:01:10 UTC 2020] trigger validation code: 200
[Sat Oct 10 00:01:10 UTC 2020] sleep 2 secs to verify
[Sat Oct 10 00:01:12 UTC 2020] checking
[Sat Oct 10 00:01:12 UTC 2020] url='https://acme-v02.api.letsencrypt.org/acme/chall-v3/7784162898/nr42-g'
[Sat Oct 10 00:01:12 UTC 2020] payload
[Sat Oct 10 00:01:12 UTC 2020] POST
[Sat Oct 10 00:01:12 UTC 2020] _post_url='https://acme-v02.api.letsencrypt.org/acme/chall-v3/7784162898/nr42-g'
[Sat Oct 10 00:01:12 UTC 2020] _CURL='curl -L --silent --dump-header /shared/letsencrypt/http.header -g '
[Sat Oct 10 00:01:13 UTC 2020] _ret='0'
[Sat Oct 10 00:01:13 UTC 2020] code='200'
[Sat Oct 10 00:01:13 UTC 2020] discourse.example.com:Verify error:Fetching https://discourse.example.com.well-known/acme-challenge/XY02T_40TL92IADByQ45JMj4JzC2qJCatVd2odJMAlU: Invalid host in redirect target
[Sat Oct 10 00:01:13 UTC 2020] pid
[Sat Oct 10 00:01:13 UTC 2020] No need to restore nginx, skip.

 

Turns out that’s my bad config — I’ve got a reverse proxy in front of Discourse, and we don’t use the clear text http site. The reverse proxy just bounces you over to the https site. Two problems — one, I failed to put the trailing slash after my redirect, s http://discourse.example.com/.well-known/blah is being redirected to https://discourse.example.com.well-known/blah

<VirtualHost 10.1.2.3:80>
ServerName discourse.example.com
ServerAlias discourse

Redirect 301 / https://discourse.example.com

</VirtualHost>

 

That’s easy enough to fix — add the trailing slash I should have had anyway. But the subsequent problem is that the bootstrap nginx config that is used to serve up the validation page only listens on port 80. So I cannot redirect the clear-text traffic over to the SSL site. I have to reverse proxy the clear text site as well (at least whenever the certificate needs to be renewed).

ProxyPass / https://discourse.example.com/
ProxyPassReverse / https://discourse.example.com/

Voila, a web server with an updated certificate.

New Teams Features for Developers

Two new features announced that might be of interest to developers — the first one might provide a mechanism to move (well, copy) content between Teams spaces. You can read channel messages from a Teams space, analyze it, then use this new API to mirror the content into a new Teams space.

Importing third-party platform messages to Microsoft Teams is now available in beta

These new capabilities give you the ability to import channel messages into a new team, specify the message sender and timestamp and link to files. These capabilities are built with scale in mind. At a high level, the import process consists of the following:
• Create a team with a back-in-time timestamp
• Create a channel with a back-in-time timestamp
• Import external back-in-time dated messages
• Complete the team and channel migration process
• Add team members
https://developer.microsoft.com/en-us/microsoft-365/blogs/importing-3rd-party-platform-messages-to-microsoft-teams-is-now-available-in-beta/

Teams Meeting Scheduling Link Template

With the new Teams Meeting Scheduling Link Template, developers can embed a meeting link generator directly into their scheduling platform–providing an easy way for users to create Teams meeting links and share them directly with participants.
https://developer.microsoft.com/en-us/microsoft-teams/meeting-scheduling

 

Using Process Monitor To Troubleshoot Applications

SysInternals used to produce a suite of tools for working with Microsoft Windows systems — the company appears to have been acquired by Microsoft, and the tools continue to be developed. I used PSKill and PSExec to automate a lot of system administration tasks. ProcessMonitor is like truss/strace for Windows. Unlike the HFS standard, Windows files end up all over the place (plus info is stashed in the registry). Sometimes applications or services fall over for no reason. Process monitor reports out

When you open procmon, you can build filters to exclude uninteresting operations — there’s a default set of exclusions (no need to log out what procmon is doing!)

Adding exclusions for specific process names can eliminate a lot of I/O — I was looking to troubleshoot a problem on a Domain Controller that had nothing to do with AD specifically, so excluding activity by lsass.exe significantly reduced the amount of data being logged. If I’m using a browser to troubleshoot the problem, I’ll exclude the firefox.exe or chrome.exe binary too.

From the filter screen, click “OK” to begin grabbing data. The easiest thing I’ve found to do is stop capturing data when the program opens (use ctrl-a followed by ctrl-x to clear the already logged stuff). Stage whatever you want to log, use ctrl-e to start capturing. Perform the actions you want to log, return to procmon and use ctrl-e to stop again.

You’ll see reads (and writes) against the registry, including the specific keys. Network operations. File reads and writes. In the “Result” and “Detail” column, you can determine if the operation was successful. There are a lot of expected not found failures — I see these in truss/strace logs too, programs try a bunch of different things and one of them needs to work.

I’ve had programs using a specific, undocumented file for a critical operation — like the service would fail to start because the file didn’t exist. And seeing the path and file open failure allowed me to create that needed file and run my service. I’ve wanted to find out where a program stashes data, and procmon makes that easy to identify.