krypted.com

Tiny Deathstars of Foulness

Sometimes you’re looking for IDs, prices, zip codes and the like in a range. You can use the BETWEEN operator to select integers, text, or dates within a range of items. The syntax when using the BETWEEN operator would look similar to the following:

<code>SELECT column(s)
FROM table
WHERE column_name BETWEEN value AND value;</code>

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

The following SQL statement selects all Sites with an ID BETWEEN 2 and 4:

<code>SELECT * FROM Customers
WHERE ID BETWEEN 2 AND 4;</code>

Or look for the ones that are not between those:

<code>SELECT * FROM Customers
WHERE ID NOT BETWEEN 2 AND 4;</code>

One of the best aspects of BETWEEN is the ability to specify multiples. So you can look for ID between 2 and 4 and not  3 and 4:

<code>SELECT * FROM Customers
WHERE (ID BETWEEN 2 AND 4)
AND NOT ID IN (3,4);</code>

The following SQL statement selects all Sites with a Site beginning with any of the letter BETWEEN ‘M’ and ‘Z’:

<code>SELECT * FROM Customers
WHERE Site BETWEEN ‘M’ AND ‘Z’;</code>

Or the same, using NOT BETWEEN ‘A’ and ‘L’:

<code>SELECT * FROM Customers
WHERE Site NOT BETWEEN ‘A’ AND ‘L’;</code>

If there was a Date field, the following statement would select all Sites with a Date BETWEEN ’01-01-2010′ and ’01-01-2016′:

<Code>SELECT * FROM Customers
WHERE Date BETWEEN #01/01/2010# AND #01/01/2016#;</code>

At this point, we have lots of logic we can use to further find data in large databases. We’ll also need to eventually get to joining tables, so we can take use of multiple pieces of data.

February 10th, 2016

Posted In: SQL

Tags: , , , , ,

Leave a Comment

Previously we looked at using wildcards in conjunction with the SQL LIKE operator. Wildcards allow you to search for data in a defined table. Think of them as text globbing for SQL. The wildcards available include the following:

  1. [list]: Define a ranges of characters for pattern matching
  2. [!charlist]: Matches only a character NOT specified within the brackets
  3. %: Require a single character/object in a pattern
  4. _: Allow any single character in a pattern

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 55410 US

The following SQL statement selects all customers with a City starting with “Minne”:

SELECT * FROM Customers
WHERE City LIKE 'Minne%';

The above SELECT would not locate an object that was just called Minne because the % indicates that something must be there. We could also look for something with nn but with something before and after the pattern:

SELECT * FROM Customers
WHERE City LIKE '%nn%';

Or to look for something that would contain Minneapoli:

SELECT * FROM Customers
WHERE City LIKE 'Minneapoli_';

Not that the _ is looking for a single character and that the % is looking for any string in that space. We could also look for a set of objects or omit a set of objects using brackets to define multiple items or a range. For example, let’s say we wanted to look for zip codes 55418 and 55410

SELECT * FROM Customers
WHERE Zip LIKE '[55418 55410]%';

Or 55410 through 55419, use a dash to separate the two (in ascending order):

SELECT * FROM Customers
WHERE Zip LIKE '[55410-55418]%';

Or the ones that don’t match that pattern:

SELECT * FROM Customers
WHERE Zip LIKE '[!55410-55418]%';

Overall, the globbing/pattern matching options are very basic (think DOS-like syntax) unless you use more complicated functions. But, it’s amazing what kind of stuff you can string together with simple commands, joins, and other tricks, without having to get into functions.

February 8th, 2016

Posted In: SQL

Tags: , , , , , , , ,

Leave a Comment

Previously, we covered the SQL SELECT Statement, to pull data from a SQL database. Here, we’ll constrain our search for items that match a given string, or pattern using the WHERE clause to filter search results, rather than getting all of the records and parsing the output.

The WHERE clause extracts records that fulfill a specified string and follows the general syntax as follows, replacing the word column with the name of the column in one of your tables and the word table with the name of a table that you’d like to search within:

SQL WHERE Syntax
SELECT column,column
FROM table
WHERE column operator value;

Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword:

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

The following SQL statement selects all the customers with the zip code (Zip column) matching “55418”, in the “Customers” table:

SELECT * FROM Customers
WHERE Zip=55418;

In the above search, I didn’t have to quote what I was looking for. The reason is that an integer doesn’t require quoting; however, if we were searching for a name, or any other text record, we should use quotes. So to repeat the search for Site, looking for Krypted, we would use the following:

SELECT * FROM Customers
WHERE Site=Krypted;

We used an = operator, but it’s worth noting that there are a number of others that can be super-helpful. The following operators are available when using a WHERE clause:

    • = Equal
    • <>  or != Not equal to
    • > Greater than
    • IN Indicates multiple potential values for a column
    • < Less than
    • >= Greater than or equal
    • <= Less than or equal
    • BETWEEN Between an inclusive range
    • LIKE Looks for a provided pattern

So using another operator from above, we can also search for all sites that do not contain Krypted, using the following:

SELECT * FROM Customers
WHERE Site!=Krypted;

So far, we’ve looked at searching for exact matches. We can also fuzzy our logic up by looking for items that contain a pattern AND something else, using the LIKE operator in a WHERE clause to search for items that contain part of a pattern in a search. The syntax for a SQL LIKE is similar, it begins with the SELECT statement that we’ve used throughout these articles so far, and then continues with the WHERE and then a LIKE to define the pattern, as follows, replacing column with the name of one of your columns, table with the name of the table that column is in and pattern with the actual search you’re looking to run:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

The following SQL statement selects all customers with a City starting with the letter “M” with the % inside a single quote to show where the wildcard data is (in this case, anything that appears after the letter M in a city name):

SELECT * FROM Customers
WHERE City LIKE 'M%';

The following SQL statement selects all customers with a City containing the pattern “Park (e.g. “Menlo Park”):

SELECT * FROM Customers
WHERE Country LIKE '%Park%';

You can also add NOT in front of Like to search for records that do not match a pattern (note that you don’t have all the same operators available, so this helps to get a little more logic in searches when needed).

The following SQL statement selects all customers with Country NOT containing the pattern “US” (of which there are none in our sample data set):

SELECT * FROM Customers
WHERE Country NOT LIKE '%US%';

Or if we weren’t sure that we wanted to work in a spaceship, we could search for all addresses that weren’t spaceship:

SELECT * FROM Customers
WHERE Address NOT LIKE '%spaceship%';

Overall, the SELECT statement has some pretty basic logic and operations, because searching for data within a given table is a pretty straight forward task, you have a pattern, you look for items that match that pattern. As these articles continue, we’ll get into slightly more complex operations, but much of the work that we do with SQL is done after a query with another tool in order to get our data to display or be manipulated in ways that SQL doesn’t do on its own. One of the beauties of SQL, and why it can be so fast, is that it’s simple.

February 1st, 2016

Posted In: SQL

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

Leave a Comment

Most tasks you will execute against a database are done with SQL statements. Think of statements 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;

As can be seen above, the SELECT statement is used to select data from a database. Results are stored in a result table that is simply called the result-set. The syntax to run a select is to run SELECT followed by a list of columns separated by commas and then a FROM statement to indicate which table of a database you’ll query followed by the name of the table and then a semi-colon (;).

SELECT column name,column name
FROM table name;

The initial SELECT that we ran used an * instead of any column names. This worked because the * is a wild card that pulls all data. As indicated in the above syntax though, we can constrain our output to only the columns we care about. Below is the “Customers” table from our first article:

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

This SQL statement selects the “ID” and “Site” columns from the “Customers” table:

SELECT ID,Site FROM Customers;

The result would be as follows:

ID Site
1 Krypted
2 Apple
3 Microsoft
4 Facebook
5 JAMF

In a table, each column can contain duplicate values (for example, multiple parties could be in the same city or multiple items for sale can have the same price. The DISTINCT keyword can be used to return only unique values, similar to the uniq command in bash. The syntax for the DISTINCT statement is as follows:

SELECT DISTINCT column,column
FROM table;

The following SQL statement selects only the distinct values from the “City” columns from the “Customers” table in our example database:

SELECT DISTINCT City FROM Customers;

You can also search for data
The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

SELECT * FROM Customers
WHERE Country=’US’;

As you can see in the above, we used single quotes around text. We could have also used double-quotes. You do not need to quote numbers, as seen below:

SELECT * FROM Customers
WHERE ID=1;

We used an = operator, but it’s worth noting that there are a number of others that can be super-helpful. The following operators are available when using a WHERE clause:

  • = Equal
  • <>  or != Not equal to
  • > Greater than
  • IN Indicates multiple potential values for a column
  • < Less than
  • >= Greater than or equal
  • <= Less than or equal
  • BETWEEN Between an inclusive range
  • LIKE Looks for a provided pattern

January 30th, 2016

Posted In: SQL

Tags: , , , , ,

Leave a Comment

Ever since the kids from Silicon Valley went to TechCrunch, I’ve been thinking that at some point I’d want to put a piece there. Luckily, I recently got the chance. Today, 16 Apple Security Advances To Take Note Of In 2016 went up on TechCrunch. You can access the article here.

Screen Shot 2016-01-18 at 7.36.16 PM

The original article actually listed the year that each was introduced in order. It was a lot of work to go back in time and piece the timeline together, so since the years didn’t make it through editorial, I list them here (not that anyone actually cares):

  • 2002: Managed Preferences
  • 2003: FileVault
  • 2004: Require all software installers that need system resources to prompt for a password
  • 2005: Restrict setuid and setgid in scripts
  • 2007: Time Machine
  • 2007: Application Firewall
  • 2007: ASLR(Address Space Layout Randomization)
  • 2009: Application Sandboxing
  • 2009: XProtect, or File Quarantine
  • 2008: Antiphishing
  • 2010: The Mac App Store
  • 2012: Gatekeeper
  • 2012: Mobile Device Management
  • 2013: iCloud Keychain
  • 2015: System Integrity Protection, or SIP

And yes, since I was there for each of these, I did feel old writing this… :-/

And yes, thank you for asking, I did just publish another book on Mac Security, which you can buy here. :)

January 18th, 2016

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

Tags: , , , , , , ,

It can be tough to get information about larger Mac deployments. I’ve written a few books on it. Apple has built some pages on it. But many prefer to consume their content through video. As such, Sean Collins has teamed up with Lynda.com to put together an IT Administrator’s Guide for El Capitan. With topics ranging from SIP to DEP, and all the acronyms in the middle, Sean’s soothing voice will guide you through what you need to get started with a new Mac deployment.

Screen Shot 2016-01-15 at 2.11.19 PM

Many a job can seem daunting, but with this latest addition to our arsenal, you’ll instantly feel less intimidated. It’s like the Sun A of the Mac world. But afterwards, when you go into corpse pose, you won’t fall asleep, because the content is too good. Check it out here:

http://www.lynda.com/El-Capitan-tutorials/IT-Administrators-Guide-OS-X-El-Capitan/427974-2.html

January 15th, 2016

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

Tags: , , , ,

One of the options thats a tad bit hidden in OS X is the Secure Erase option, which runs a multi-pass erase on a volume. Additionally, there’s no option to Secure Erase free space on a volume. But you can still securely erase whatever you’d like (other than you boot volume obviously), when needed. To do so, use the diskutil command along with the secureErase option.

Screen Shot 2016-01-07 at 7.44.07 AM

The format of the command to secureErase freespace is:

diskutil secureErase freespace [level] [device]

The levels are as follows (per the man page as not all of these are specified in Disk Utility):

  1. Single-pass zero-fill erase
  2. Single-pass random-fill erase
  3. US DoD 7-pass secure erase
  4. Gutmann algorithm 35-pass secure erase
  5. US DoE algorithm 3-pass secure erase

So for example, let’s say you had a volume called Seldon and you wanted to do a standard Single-pass zero-fill erase. In this example you would use the following:

diskutil secureErase freespace 0 /Volumes/Seldon

If you were to automate the command then you would want to dump the output into a log file. For example:

diskutil secureErase freespace 0 /Volumes/Seldon > /var/log/secureeraselog.tmp

You can also secureErase a volume itself. To erase a volume called /Volumes/Seldon, use the same structure of the command, but this time without the freespace option:

diskutil secureErase 0 /Volumes/Seldon

The latest update to Disk Utility removes a lot of options from the GUI, but overall, I have yet to find a scenario where a task I need to perform isn’t still available, if only from the command line.

January 7th, 2016

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

Tags: , , , , , , , ,

The world of Apple is bored with me (or at least I am) and so I think it’s about time for some new blood in the Apple speaker community. I’d be happy to help a few talented MacAdmins get some exposure in whatever way I can. If you’d like any help in preparing a presentation or a proposal, please feel free to reach out!

Screen Shot 2015-12-28 at 8.11.29 PM

To quote the most excellent organizers of the Penn State MacAdmins conference:

The MacAdmins 2016 Call for Proposals is now open!  We’re looking for engaging, well-crafted sessions and workshops that showcase the latest tools and strategies for managing Macs and iOS devices.  From technical deep dives, case studies, and soft skills, to workflow improvements, tool overviews, and essential admin skills – we want to showcase your knowledge and experience at one of the premier Mac and iOS conferences in the world.

If you’d like to present a workshop or conference session, please visit our site below and fill out a proposal:

http://macadmins.psu.edu/conference/submit-proposals/ >

Call for Sponsors

Interested in sponsoring the MacAdmins Conference in 2016?  Want to get your products and services in the hands of influential Mac and iOS administrators from across the world?  See our information below and become a sponsor!

http://macadmins.psu.edu/conference/sponsorships/ >

Save the Date

Penn State MacAdmins Conference 2016 will be held June 27-30.

Early Bird Registration

Details on registration will be released in February!  Stay tuned!

Again, I think it’s time for a whole new generation of speakers, and please feel free to reach out if you’d like to be amongst them and would like a little help getting there!

December 28th, 2015

Posted In: public speaking

Tags: , , , ,

OS X has a built-in web server called Apache. It’s been there for a long, long time. Once upon a time, you could enable web sharing using System Preferences. This is no longer a feature in the Sharing System Preference pane, but you can actually enable it quicker than you could before. To do so, we’ll use apachectl:

/usr/sbin/apachectl start

To then stop the web server:

/usr/sbin/apachectl stop

To see the apache status:

/usr/sbin/apachectl status

Or:

/usr/sbin/apachectl fullstatus

The default site is stored in /Library/WebServer/Documents. You can then edit this there, or replace the index.html.en file with a file/hierarchy that you wish to have.

Screen Shot 2015-12-08 at 10.12.25 AM

Enjoy.

December 22nd, 2015

Posted In: Mac OS X, Mac OS X Server, Mac Security

Tags: , , , , ,

Next Page »