Perl for System Administration

Querying Information

As an administrator, the SQL command you’ll probably use the most often is SELECT. SELECT is used to query information from a server. Before we talk about this command, a quick disclaimer: SELECT is a gateway into a whole wing of the SQL language. We’re only going to demonstrate some of its simpler forms. There is an art to constructing good queries (and designing databases so they can be queried well), but more in-depth coverage like this is best found in books entirely devoted to SQL and databases.

The simplest SELECT form is used mostly for retrieving server and connection-specific information. With this form, you do not specify a data source. Here are two examples:

-- both of these are database vendor specific
SELECT @@SERVERNAME

SELECT VERSION(  );

The first statement returns the name of the server from a Sybase or MS-SQL server; the second returns the current version number of a MySQL server.

Retrieving All of the Rows in a Table

To get at all of the data in our hosts table, use this SQL code:

USE sysadm
SELECT * FROM hosts

This returns all of the rows and columns in the same column order as our table was created:

name ipaddr aliases owner dept bldg room manuf model --------- ------------ ---------------------------- ----------------- -------- ------ ---- ---------- --------- shimmer 192.168.1.11 shim shimmy shimmydoodles David Davis Software Main 309 Sun Ultra60 bendir 192.168.1.3 ben bendoodles Cindy Coltrane IT West 143 Apple 7500/100 sander 192.168.1.55 ...