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…
-
-
Your First SQL Statement
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…
-
Use the Jamf Classic API to Extract Device Counts
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…
-
OS X Yosemite Server and dnsconfig
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…
-
Working with Postgres from the Command Line in Lion Server
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…