krypted.com

Tiny Deathstars of Foulness

A SQL JOIN clause combines rows from tables, based on a field shared between them (often a joining or ID field). There are four types of JOINs:

  • INNER JOIN: Show rows when there’s a match in BOTH tables
  • LEFT JOIN: Show rows in the left table with the rows that match up from the right table
  • RIGHT JOIN: Show rows in the right table with rows that match up in the left table
  • FULL JOIN: Show rows with a match in at least one table

In this article, we’ll use the same “Customers” table from our first articles:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

Let’s look at a selection from the “IPs” table:

IPID SiteID IP
101 1 202.76.2.111
102 2 172.67.33.90
103 3 44.75.32.12
104 4 44.75.32.13
105 5 44.75.32.13

In the above two tables, each has their own ID field, with ID being the Customers table ID and IPID being the ID of an IP address. We have a series of IPs for SiteIDs 3-5, with 104 and 105 being the same IP address. Note that the IPs are fake. I made ’em up. Sorry about not using production data, but it didn’t make that much sense…

Here, we’ll use a SELECT and identify the fields, defining the table followed by a dot and then the field name as the data to load into memory. We’ll pull that from our initial table and use an INNER JOIN with the second, using an ON to define which field is used to map out results:

SELECT IPs.IPID, Customers.ID, IPs.IP
FROM IPs
INNER JOIN Customers
ON IPs.IPID=Customers.ID;

We’ll then get something like this, with the three fields that we defined in the SELECT as the columns to display in the output:

IPID ID IP
101 1 202.76.2.111
102 2 172.67.33.90
103 3 44.75.32.12
104 4 44.75.32.13
105 5 44.75.32.13

This is a pretty basic JOIN. But shows the power we have. We can then use a LEFT JOIN alongside an ORDER BY:

SELECT Customers.Site, IPs.IP
FROM Customers
LEFT JOIN IPs
ON Customers.ID=IPs.IPID
ORDER BY Customers.Site;

The output would then appear as follows:

Site IP
Apple 172.67.33.90
Facebook 44.75.32.13
JAMF 44.75.32.13
Krypted 202.76.2.111
Microsoft 44.75.32.12

The different types of JOINs give you the ability to string together some pretty awesome logic, to locate assets in multiple tables and display them. For example, let’s say I also had a Registrar table. I could then also use that ID attribute from our original database as a key to access data connected to our initial table from other tables. Overall, simple and straight forward means of querying data and displaying those results, or leveraging them into a variable or file in order to make use of them elsewhere (e.g. with a script).

February 12th, 2016

Posted In: Uncategorized

Tags: , , , , , , ,

Databases and Tables

A SQL database is an organized collection of data. Or at least that’s what they taught me in college. In real life, it’s only as organized as the people putting data into the database. Databases contain schemas, tables, stored procedures, reports, views and other objects. Most databases will contain multiple tables. Tables contain rows that have data in them. I like to think of a database kinda’ like an Excel spreadsheet. Each tab on a spreadsheet is similar to a table; each row is similar to a row in a database and each column in the spreadsheet is somewhat similar to a column, or attribute. The headers are kinda’ like the schema.

These are overly simplistic explanations. And whenever you oversimplify something, you run the risk of miscommunication, but it helps as a starting place. This page is meant to be a short and easy guide to get started writing SQL queries. More links will appear throughout the page that point to other posts on my site, so stay tuned. Throughout my exercises in this page, I will use the following sample database, which has five records (one for each ID) and seven columns (ID,Site,Contact,Address,City,Zip, and Country).

Below is a selection from the “Customers” table (note that when querying data, SQL commands are NOT case sensitive)

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

SQL Statements

Most tasks you will execute against a database are done with SQL statements. Think of this as a query, an insert, a change, or a delete operating. For example, to see all of your data, you would select all of the records from a database using the SELECT statement. Then we’ll ask for all, or *, and tell the command to show us where the data is coming from, which is the Customers table. Finally, we’ll be nice and tidy and put a semi-colon at the end; although if you forget, you can always do so after you hit return:

SELECT * FROM Customers;

The SELECT statement is the most common command I run in SQL. This is how you query data, build reports, derive the layout of a database and so, so much more.

Other Important SQL Commands covered in this series (if there is no link, I haven’t written that article yet):

  • SELECT – Query and pull information from a database
  • CREATE TABLE – Create tables in specified databases
  • DELETE – Delete data
  • UPDATE – Update data in a database
  • DROP TABLE – Delete tables
  • INSERT INTO – Inserts new data into a specified database
  • CREATE DATABASE – Create databases
  • ALTER DATABASE – Modify databases
  • ALTER TABLE – Modify tables
  • CREATE INDEX – Create indexes
  • DROP INDEX – Deletes indexes
  • INNER JOIN – Merge rows in a database

January 26th, 2016

Posted In: SQL

Tags: , , , , , , , , ,

You can leverage the API built into the Casper Suite to do lots and lots of cool stuff, without interacting directly with the database. Here, I’ll use a simple curl command in a bash script that has myuser as the username for a server and mypassword as the password. The server is myserver.jamfcloud.com. Basically, we’re going to ask the computers and mobiledevices tables for all their datas. Once we have that, we’ll constrain the output to just the size attribute for each using sed:

curl -s -u myuser:mypassword https://myserver.jamfcloud.com/JSSResource/computers | sed -n -e 's/.*<size>\(.*\)<\/size>.*/\1/p'
curl -s -u myuser:mypassword https://myserver.jamfcloud.com/JSSResource/mobiledevices | sed -n -e 's/.*<size>\(.*\)<\/size>.*/\1/p'

This same logic can then be applied to any payload of XML data coming out of a REST API. Some API’s have different options to constrain output of a request, some don’t. But no matter whether there is or isn’t, you can loop through a bunch of statements like this. Why would you look to the API to constrain data, etc? Well, it comes down to a cost issue. Each time you run the above commands, you’re costing yourself runtime, you’re taxing the server with potentially a substantial query, and you’re potentially transferring a considerable amount of data over the wires between you and where the script is being run. So if the API is smart enough to give you less data, then you might as well do that. In this case, it isn’t, but if you apply this same sed logic in other scripts, it’s great to be cognizant of remaining as efficient as you can.

December 18th, 2015

Posted In: JAMF

Tags: , , , , , , , , ,

DNS is DNS. And named is named. Except in OS X Server. The configuration files for the DNS services in OS X Server are stored in /Library/Server/named. This represents a faux root of named configuration data, similar to how that configuration data is stored in /var/named on most other platforms. Having the data in /Library/Server/named makes it more portable across Mac DNS Servers.

Traditionally, you would edit this configuration data by simply editing the configuration files, and that’s absolutely still an option. In Yosemite Server, a command is available at /Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework called dnsconfig, introduced back in Mavericks. The dnsconfig command appears simple at first. However, the options available are actually far more complicated than they initially appear. The verbs available include help (show help information), list (show the contents of configurations and zone files), add (create records and zones) and delete (remove records and zones).

To view data available in the service, use the list verb. Options available when using the list verb include –acl (show ACLs), –view (show BIND view data), –zone (show domains configured in the service), –rr (show resource records) and –rrtype (show types of resource records). For example, let’s say you have a domain called krypted.com and you would like to view information about that zone. You could use the dnsconfig command along with the list verb and then the –zone option and the domain name:

/Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig list --zone=krypted.com

The output would show you information about the listed zone, usually including View data:

Views:
com.apple.ServerAdmin.DNS.public
Zones:
krypted.com
Options:
allow-transfer: none
allow-update: none

To see a specific record, use the –rr option, followed by = and then the fqdn, so to see mavserver.krypted.com:

/Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig list --rr=mavserver.krypted.com

By default views are enabled and a view called com.apple.ServerAdmin.DNS.public is created when the DNS server first starts up. You can create other views to control what different requests from different subnets see; however, even if you don’t create any views, you’ll need to add the –view option followed by the name of the view (–view=com.apple.ServerAdmin.DNS.public) to any records that you want to create. To create a record, use the add verb. You can add a view (–view), a zone (–zone) or a record (–rr). Let’s start by adding a record to the krypted.com from our previous example. In this case we’ll add an A record called www that points to the IP address of 192.168.210.201:

/Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig add --view=com.apple.ServerAdmin.DNS.public --zone=krypted.com --rr=www A 192.168.210.201

You can add a zone, by providing the –view to add the zone to and not providing a –rr option. Let’s add krypted.lan:

/Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig add --view=com.apple.ServerAdmin.DNS.public --zone=krypted.lan

Use the delete verb to remove the data just created:

/Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig delete --view=com.apple.ServerAdmin.DNS.public --zone=krypted.lan

Or to delete that one www record earlier, just swap the add with a delete:

/Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig delete --view=com.apple.ServerAdmin.DNS.public --zone=krypted.com --rr=www A 192.168.210.201

Exit codes would be “Zone krypted.lan removed.” and “Removed 1 resource record.” respectively for the two commands. You can also use the –option option when creating objects, along with the following options (each taken as a value followed by an =, with this information taken by the help page):

  • allow-transfer Takes one or more address match list entry. Address match list entries consist of any of these forms: IP addresses, Subnets or Keywords.
  • allow-recursion Takes one or more address match list entry.
  • allow-update Takes one or more address match list entry.
  • allow-query Takes one or more address match list entry.
  • allow-query-cache Takes one or more address match list entry.
  • forwarders Takes one or more IP addresses, e.g. 10.1.1.1
  • directory Takes a directory path
  • tkey-gssapi-credential Takes a kerberos service principal
  • tkey-domain Takes a kerberos realm
  • update-policy Takes one complete update-policy entry where you can grant or deny various matched objects and specify the dentity of the user/machine that is allowed/disallowed to update.. You can also identify match-type (Type of match to be used in evaulating the entry) and match-name (Name used to match) as well as rr-types (Resource record types that can be updated)

Overall, this command shows a commitment to continuing to make the service better, when you add records or remove them you can instantly refresh the Server app and see the updates. It’s clear a lot of work went into this and it’s a great tool for when you’re imaging systems and want to create records back on a server or when you’re trying to script the creation of a bulk list of records (e.g. from a cached file from a downed host). It also makes working with Views as easy as I’ve seen it in most platforms and is overall a breeze to work with as compared to using the serveradmin command to populate objects so the GUI doesn’t break when you update records by hitting files directly.

October 16th, 2014

Posted In: Mac OS X, Mac OS X Server, Mac Security, Mass Deployment, Network Infrastructure

Tags: , , , , , , , , ,

Mac OS X Server 10.7, Lion Server, comes with a few substantial back-end changes. One of these is the move from SQLite3 to PostgreSQL for many of the back-end databases, including Wiki and Podcast Producer (collab), Webmail (roundcubemail), iCal Server and Address Book Server (caldav) and as the back-end to the newest service in Lion Server, Profile Manager (device_management). As such, it’s now important to be able to use PostgreSQL the way we once used SQLite3, when trying to augment the data that these databases contains, as there currently aren’t a lot of options for editing this data (aside from manually of course).

Postgres has a number of commands that can be used to interact with databases. The most important is probably psql. Many of the other commands simply provide automated options to psql, and over time I’ve started using psql for most everything. For example, PostgreSQL comes with a command /user/bin/createuser. However, as it’s usually more verbose with errors, I like to use psql to do this. In Lion Server, the only user that can access the Postgres databases is _postgres, installed by default with Lion Server. Because a lot of commands require passwords and we might not always want to provide write access to the databases, we’re going to create a new SuperUser, called krypted with a password of daneel.

To do so, we will have to use the _postgres user to invoke psql. Any time you want to invoke psql with a different user than the user you are currently logged in as, use the -U option. To define a database, use the -d option (device_management providing access to Profile Manager data, caldav to iCal Server data roundcubemail to WebMail data and collar to Wiki data). To string this together, for accessing the device_management database as _postgres:

psql -U _postgres -d device_management

To then create a new user called krypted with a password of daneel we’ll use the create option, defining a user as the type of object to create, followed by the user name and then with password followed by the password (single quoted) and then createuser; as follows:

device_management=# create user krypted with password 'daneel' create user;

Now that there’s a valid user, let’s see what else we can do. To see all of the tables, use d:

device_management=# d

As you can tell, there are a bunch of them. Run the help command to see a list of SQL commands that can be run and ? for a list of psql options. To put some SQL commands into action, we’re going to look at the tasks that have been performed by Profile Manager. These are stored in the tasks table (aptly named), so we’re going to run the following SQL query (note a space followed by a semi-colon is required at the end of this thing):

device_management=# select * from "public"."tasks" limit 1000 offset 0 ;

Or to make it a bit simpler if you don’t have a lot of data in there yet:

device_management=# select * from "public"."tasks" ;

After seeing the output, you’ll probably be a little appreciative of Apple’s formatting. Next, let’s look at dumping the databases. We’re going to create a folder on the root of the volume called db_backups first:

sudo mkdir /db_backups

This is where these backups will end up getting stored. We’ll continue using the _postgres user for now. To do our database dumps, we’re going to use pg_dump, located at /usr/bin. First, we’ll dump the device_management database (but first we’ll stop the service and after we’ll start it – all commands from here on out also assume you’re sudo’d):

serveradmin stop devicemgr
pg_dump -U _postgres device_management -c -f /db_backups/device_management.sql
serveradmin start devicemgr

And the other 3 (stopping and starting each in the process):

serveradmin stop web
pg_dump -U _postgres roundcubemail -c -f /db_backups/roundcubemail.sql
serveradmin start web
serveradmin stop wiki
pg_dump -U _postgres collab -c -f /db_backups/collab.sql
serveradmin start wiki
serveradmin stop addressbook
serveradmin stop calendar
pg_dump -U _postgres caldav -c -f /db_backups/caldav.sql
serveradmin start addressbook
serveradmin start calendar

I haven’t had any problems running the dumps with the services running, but it’s better safe than sorry I guess. I’d probably also add some logging and maybe dump the output of full status for each service to try and track if all is well with each. Any time a service didn’t fire back up I’d then build in a sanity check for that event. There’s also a database for postgres itself, so let’s back that up as well since we’re here:

pg_dump -U _postgres postgres -c -f /db_backups/postgres.sql

These can then be restored using psql with the -d option to define the database being restored into and the -f option to define the file being restored from. For example, to restore collab:

psql -U _postgres -d collab -f /db_backups/collab

The databases are all dumped daily using pg_dumpall. These are stored in /var/pgsql but can be changed using serveradmin settings (for example, to move them to /var/pgsql1):

serveradmin settings postgres:dataDir = "/var/pgsql1"

If you mess up the Profile Manager database (before you put any real data into it) you can always use the /usr/share/devicemgr/backend/wipeDB.sh script to trash the database and start anew (although I’d just use a snapshot of a VM for all this and restore from that).

You can also connect to Postgres remotely, or locally through a network socket (common in Apache uses) by adding a listener. To do so, we’ll need to restart the Postgres LaunchDaemon. First, back up the file, just in case:

cp org.postgresql.postgres.plist org.postgresql.postgres.plist.OLD_CSE

Then stop postgres:

serveradmin stop postgres

Then edit the org.postgresql.postgres.plist file to change the following line:

listen_addresses=

To read:

listen_addresses=127.0.0.1

Then fire up postgres again:

serveradmin start postgres

And now let’s scan port 5432 (the default TCP and UDP port used for postgres) for localhost:

/Applications/Utilities/Network Utility.app/Contents/Resources/stroke 127.0.0.1 5432 5432

We could have used another IP address for the listen_addresses as well, but with that _postgres user not requiring a password it didn’t really seem prudent to do so. Once you’ve enabled a socket, you’ll then be able to use one of the many GUI tools to manage postgres. Navicat is available on the Mac App Store for $5 and PGnJ is a nice, easy to use, free one. There are tons of others, but I don’t spend a lot of time in a SQL GUI and so don’t need more than a cheap app will get me. One nice thing about most of these is that they help you to form SQL queries (or they help me). This can get really nice if you are, for example, trying to get some good reporting on Profile Manager (a feature it’s a bit light on right now).

Finally, don’t do any of this stuff on a production box, except maybe if you want more than nightly backups unless you think pretty hard about what you’re doing and know the exact impact of doing something. If you were to edit the databases on a live boxen, then you can safely assume that with how all of the objects in those databases use GUIDs that you’re probably going to break something, if not bring the whole house of cards tumbling down.

January 4th, 2012

Posted In: iPhone, Mac OS X Server, Mac Security, Mass Deployment, SQL

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,