Primer for Writing SQL Select Statement Queries on the K1000 (wCustom SQL Report example)
__________________________________________________________________________________

The K1000's Reports, Smart Labels and Custom Ticket Rules are tremendously powerful and useful - if you know how to write MySQL queries.  For example, the most common situation I've encountered with custom reports is having the information that I want in two or more different Report Topics (i.e. Computers, Patch, Software) or not accessible at all, except in the canned reports (i.e. Warranty Info).  If only I could pull info from all of these....

Well, the good news is - using JOIN statements, you can!  

In this breakdown, I will attempt to explain some of the most common parts of a Select statement (a query that requests information from specified tables and formats it accordingly) and how you can write your own for the K1000.  Once you understand the basic format and functionality, tweaking the K1000 wizard-generated SQL statements will be less like a mystery and more like a puzzle that you just need to add your own pieces to in order to make it work the way you want.

If you've researched MySQL tutorials to try and find an "easy answer", you may have ended up more frustrated than enlightened due to the very simple examples given in most tutorials (along with the fact that there are multiple ways to write the same query).  I always thought, "If only there was a K1000-specific write up...", and that's why I'm doing this now.  On the bright side, since the K1000's tables are static, you don't need to be concerned about messing up anything (at least until you get into update queries with Custom Ticket Rules...).

One point I want to stress is that in order to save time and avoid too much repetition, this guide jumps around a bit.  However, hopefully any questions about earlier points will become clearer once you've read through everything.  In fact, I'd recommend going through this twice, just to be sure of that.
___________________________________________

Disclosure - I am *not* by any means a MySQL guru (i.e. someone who can write and troubleshoot queries blind).  I have had a ton of assistance in the past from others such as GillySpy, dchristian and airwolf to help me with my SQL reports, especially when I first started.  I'm a bit more comfortable these days with getting things the way I want, but please keep this in mind if/when you find things that could/should be done differently and/or better.  I'm only trying to help others get started with this, and hopefully it's more like a black belt student helping a no-belt student (with the sensei making sure everything is being done correctly) than the blind leading the blind.

Hope something here helps!

John
__________________________________________________________________________________

Primer
__________________________________________________________________________________

Since I'll be using these terms a *lot*, it's probably only fair to explain them a bit.  These are my own definitions, so please bear this in mind.
___________________________________________

Column - a list of data specific to one characteristic

Examples:
BIOS_SERIAL_NUMBER = service tag
CS_MODEL = model
RAM_TOTAL = memory
etc
___________________________________________

Table - a group of related columns

Examples:
The MACHINE table contains columns for BIOS_SERIAL_NUMBER, CS_MODEL, RAM_TOTAL, etc
The SOFTWARE table contains columns for DISPLAY_NAME, PUBLISHER, INSTALL_DATE, etc
The HD_TICKET table contains columns for TITLE, CREATED, OWNER_ID, SUBMITTER_ID, etc
___________________________________________

Database - the main container for related tables

Examples:
The ORG1 database contains tables for MACHINE, SOFTWARE, HD_TICKET, etc
The KBSYS database contains tables for DELL_ASSET, DELL_WARRANTY, PATCHLINK_PATCH, etc  
__________________________________________________________________________________

Example report
__________________________________________________________________________________

This is the custom report code I'll be dissecting and analyzing in an attempt to explain how things work.  While this example is by no means the most sophisticated, it will hopefully serve the purpose of illustrating the common parts of Select statements and how JOIN statements are used to pull data from multiple tables (i.e. something the report wizard currently does not do, as discussed above).  

The tables used in this report are:

MACHINE - contains general data on all of your machines
KBSYS.DELL_ASSET - has Dell-specific data, like warranty info
MACHINE_LABEL_JT - a MACHINE & LABEL "join table" (my own term?)
LABEL - consists of all of the various labels you have created
___________________________________________

*Title*
Company Production Systems

*Category*
Hardware (Custom)

*Description*
Lists all machines by name, make, model, service tag, location and ship date.

*SQL Select Statement*
SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE, M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG, L.NAME AS LOCATION, DA.SHIP_DATE
FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'East Canton computers|Greensboro computers|Hammond computers|Hillsborough computers|King Of Prussia computers|Marelan computers|Moulton computers|Oak Hill computers|Pittsburgh computers|Remote computers|Santa Fe Springs computers|Shenango computers|Tarentum computers|Testnet computers|UK computers|Warren computers|Warren Warehouse computers|Wellston computers')
ORDER BY NAME
___________________________________________

Here is a sample of the output (in text format, with column sizes slightly reduced for this article):

Title: Company Production Systems
Description: Lists all company machines by name, make, model, service tag, location and ship date.
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/05/22 08:36:19

#    Name    Make     Model           Service Tag  Location              Ship Date            
  1  AALLEN  laptop   Latitude D520   14QZ2C1      Pittsburgh computers  2007-03-04 00:00:00  
  2  ABELL   desktop  OptiPlex 160L   46ZQD11      Marelan computers     2004-02-18 00:00:00  
  3  ACOX    laptop   Latitude D510   56Y7Q91      Remote computers      2006-03-26 01:00:00  
  4  AHALL   desktop  Dimension 4700  24X1L61      Tarentum computers    2005-01-18 00:00:00  
  5  AHILL   desktop  OptiPlex 210L   1RDJ0B1      Shenango computers    2006-10-27 00:00:00  
  6  AKING   laptop   Latitude D530   1Q3NJF1      Remote computers      2008-01-26 00:00:00  
  7  ALEE    desktop  OptiPlex 360    DK3KWH1      Moulton computers     2008-12-22 00:00:00  
  8  AREED   desktop  Dimension 4550  3NF8T31      Pittsburgh computers  2003-05-13 00:00:00  
  9  ASCOTT  laptop   Latitude D520   79BE1C1      Remote computers      2006-11-01 01:00:00  
 10  AYOUNG  desktop  OptiPlex 170L   JNMXH31      East Canton computers 2005-05-07 00:00:00  
etc...
__________________________________________________________________________________

Analysis and explanations
__________________________________________________________________________________

(01) Using aliases to customize column headers
___________________________________________

In the first part of a Select statement, the columns we wish to target are listed.  For example:

SELECT DISTINCT MACHINE.NAME, MACHINE.CHASSIS_TYPE, MACHINE.CS_MODEL, MACHINE.BIOS_SERIAL_NUMBER, LABEL.NAME, KBSYS.DELL_ASSET.SHIP_DATE

Using these, the report columns will subsequently be:

Name, Chassis Type, Cs Model, Bios Serial Number, Name, Ship Date
___________________________________________

Those are pretty straightforward to understand, but may not be how you want the columns to appear in your report.  This is where the alias command (AS) is used.  You follow a column name with what you want to appear - that's about all there is to it.  For example:

SELECT DISTINCT MACHINE.NAME AS NAME, MACHINE.CHASSIS_TYPE AS MAKE, MACHINE.CS_MODEL AS MODEL, MACHINE.BIOS_SERIAL_NUMBER AS SERVICE_TAG, LABEL.NAME AS LOCATION, KBSYS.DELL_ASSET.SHIP_DATE

Now the report columns will be:

Name, Make, Model, Service Tag, Location, Ship Date            
___________________________________________

You may notice that I'm aliasing MACHINE.NAME even though it already resolves to Name.  This is just a personal preference, as I want my statement to be perfectly clear (to me, at least) due to another "name" column being used (LABEL.NAME).

Another note is that I've also used (and have seen others use) aliasing in the following format - MACHINE.NAME as 'Name'.  I primarily used this format with the Classic Reports, but have switched formats with the current Reports module (again, as a personal choice).  As I mentioned earlier, MySQL is pretty flexible and there are multiple ways of writing the same thing, something you'll notice the more you look at others' code.
___________________________________________

Points to be aware of when using aliases:

Some words are "special" in MySQL and if used as aliases will generate (obtuse) errors when the query is run.  These are typically functions and operators in the MySQL language - words like SELECT, CASE and TYPE.  Some may be obvious since there are used frequently in queries, while others... not so much.  The best way to avoid issues completely is to test the query using the MySQL Query Browser (section 07 below), as this will highlight these "special words" in blue.  So if you see an alias in blue - change it.

Another point recently came up when a fellow ninja was trying to alias a CASE statement with the original column name (OS_NAME in this case) and the query produced unexpected results (the GROUP BY statement referenced this alias and grouped on the original column, not the alias).  The idea here is to be sure to use unique aliases and not existing column names, otherwise the query may not work as intended.
__________________________________________________________________________________

(02) Using aliases to abbreviate table names (i.e. save time typing)
___________________________________________

You'll notice in the example statements above that each column has the table name spelled out completely (i.e. MACHINE._____).  Fortunately, in practice, this isn't necessary to do as MySQL also provides table name aliasing to cut down on repetition.  Rather than using AS (as with the column name aliasing above), the abbreviated name is simply listed following the table name when it gets called.  The thing to keep in mind is that this is typically done later in the statement, with the FROM and JOIN statements:

FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
___________________________________________

Concerning only the aliases, what this basically says is:

Abbreviate "MACHINE" as "M"
Abbreviate "KBSYS.DELL_ASSET" as "DA"
Abbreviate "MACHINE_LABEL_JT" as "ML"
Abbreviate "LABEL" as "L"

...which means the initial Select statement can be written more expeditiously as:

SELECT DISTINCT M.NAME, M.CHASSIS_TYPE, M.CS_MODEL, M.BIOS_SERIAL_NUMBER, L.NAME, DA.SHIP_DATE

Or with our column header aliases, as:

SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE, M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG, L.NAME AS LOCATION, DA.SHIP_DATE
__________________________________________________________________________________

(03) Using FROM to specify the main table for data queries
___________________________________________

The FROM statement is used to indicate the main table used for pulling data.  For example, if you want to include all of your machines, I'd recommend using the MACHINE table as this will have all of the machines regardless:

FROM MACHINE M

Likewise, if you want to include all of your software, tickets or users, you'd want to specify the SOFTWARE, HD_TICKET or USER tables, respectively.  Following the FROM statement, use JOIN statements to include other tables as necessary.

In reviewing other Select statements, sometimes you'll see multiple tables listed in the FROM statement, such as:

FROM MACHINE M, KBSYS.DELL_ASSET DA

This is actually one way to write a JOIN statement, the most common of which are summarized further below.
___________________________________________

For those running multiple ORGs (databases), be aware that by default the query will target the ORG1 database.  When you need to pull results from additional ORGS, just specify the ORG# which contains the tables you want to target.  Note that this is not necessary for the KBSYS database, as it is a separate entity.

For example, the FROM and JOIN statements from original example query could be rewritten as follows (for clarity) to indicate that ORG1 is being targeted:

FROM ORG1.MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN ORG1.LABEL L ON (ML.LABEL_ID = L.ID)

For targeting ORG2 (for example), it would be:

FROM ORG2.MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN ORG2.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN ORG2.LABEL L ON (ML.LABEL_ID = L.ID)

Again, note that KBSYS.DELL_ASSET remains the same throughout.
__________________________________________________________________________________

(04) Filter results using WHERE
___________________________________________

If you have used the filter in the report wizard, you'll be familiar with the conditions you can use to filter report results.  In the actual SQL code, these end up in the WHERE statement(s).  What I typically do for figuring out what code to use in the SQL report is to use the report wizard, (specifying the filter(s) I want to use), and then scavenge the corresponding code from the resulting SQL version of the report.  It's an optional statement, which is why you won't see them in every report.
___________________________________________

In my example report, I'm filtering on specific labels using a REGEX statement so that only the labels I specify (which indicate the machine's location) will be listed in the report:

WHERE (L.NAME rlike 'East Canton computers|Greensboro computers|Hammond computers|Hillsborough computers|King Of Prussia computers|Marelan computers|Moulton computers|Oak Hill computers|Pittsburgh computers|Remote computers|Santa Fe Springs computers|Shenango computers|Tarentum computers|Testnet computers|UK computers|Warren computers|Warren Warehouse computers|Wellston computers')
___________________________________________

Another example from a canned warranty report specifies that only Dell machines without a blank service tag number will be included in the report:

WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
__________________________________________________________________________________

(05) Specify how results are organized with ORDER BY
___________________________________________

Typically the last statement in a Select statement is ORDER BY, which (as the name suggests) specifies which column should be used to sort everything in the report.  It's also optional, but can be useful if results aren't ordered correctly by default.
___________________________________________

In my example report, I specify the MACHINE.NAME alias (NAME) so that machines are sorted by name:

ORDER BY NAME
___________________________________________

The default sort order is ascending, but you can specify descending order by adding "desc" after the column name, i.e.:

ORDER BY LAST_SYNC desc

This is useful in reports dealing with time, for example listing all machines by warranty date or last sync time, in which having the oldest ones at the top makes the most sense.
__________________________________________________________________________________

(06) Understanding MySQL JOIN statements
___________________________________________

OK, this is a little out of order, but goes fairly deep so I wanted to save it for last.  Before diving into a JOIN breakdown and examples, I want to share the article that ultimately helped me understand MySQL Joins.  Reading this helped me understand exactly what I was trying to do, along with terms such as tables, etc:

http://www.devshed.com/c/a/MySQL/MySQL-Left-and-Right-Joins/

In short, here are the three main types of joins in terms of K1000 tables:

1) A plain JOIN statement lists every possible combination between tables, which generally isn't very useful (aside from the concept).

2) An INNER JOIN statement only pulls records that have matches in the two tables specified in the query, that is to say it retrieves rows that meet a specified condition.  An important point to make here is that if any records have a NULL (empty) entry, the report won't include them.

3) A LEFT JOIN statement is basically the same as an INNER JOIN, but also includes any records that have NULL entries.
___________________________________________

For example, if we want a report to pull info from the K1000's MACHINE table (i.e. MACHINE.NAME, MACHINE.CHASSIS_TYPE, MACHINE.CS_MODEL - NAME, MAKE & MODEL in plain terms) and also list the ship date (i.e. date of initial service) for these machines, we will need to include results from the KBSYS.DELL_ASSET table (where warranty, ship date and other Dell-specific items are stored).

An INNER JOIN statement like this will *only* list the PCs that have all of these items (i.e. only Dell machines with a ship date listed).  For example:

JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)

In plain English, this says "include the KBSYS.DELL_ASSET table data, match up the service tag data with the MACHINE table's bios serial number data, but exclude any machines that aren't listed in the KBSYS.DELL_ASSET table".

A LEFT JOIN statement will list all machines - even non-Dell machines and Dell machines without a ship date listed.  Each has its place, depending on what you want your report to list.

LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)

In plain English, this one says "include the KBSYS.DELL_ASSET table data, match up the service tag data with the MACHINE table's bios serial number data, but don't filter out any machines, even if they aren't in the KBSYS.DELL_ASSET table".
___________________________________________

Multiple JOIN statements can add a layer of complexity, so it's often useful to run test reports using only a single JOIN statement when expected results aren't being returned.  But if you keep in mind the differences between INNER JOIN and LEFT JOIN statements and are able to figure out which columns to match up, the rest should just be a matter of fine tuning.
__________________________________________________________________________________

(07) Connecting to the K1000 databases
___________________________________________

In order to gain a better understanding of how the K1000's data is arranged (and to figure out what different tables are available, when dissecting someone else's SQL statement or planning your own), it's essential to gain access to the K1000's tables.  This is where the MySQL Query Browser becomes an indispensable tool.

A Kace KB on the subject:

How to connect to the K1000 appliance database using MySQL Query Browser
http://www.kace.com/support/resources/kb/article/How-to-connect-to-the-K1000-appliance-database-using?action=artikel&cat=9&id=10&artlang=en

In short, download from here:
http://dev.mysql.com/downloads/workbench/

* I recommend the "Without installer" version, so you can unzip it and just run the MySQLQueryBrowser.exe program directly.
___________________________________________

Another Kace KB on connecting to the K1000's database:

Can I access the K1000 appliance database using a third-party reporting tool?
http://www.kace.com/support/resources/kb/article/Can-I-access-the-K1000-appliance-database-using?action=artikel&cat=9&id=9&artlang=e

* Here are the (default) settings I'm using to connect:

Server Host: K1000 (or your K1000 DNS name or IP)
Port: 3306
Username: R1
Password: box747
Default Schema: ORG1

Assuming this connects, you should see a list on the right under Schemata for information_schema, KBSYS, ORG1, etc, with ORG1 items expanded (ADVISORY, ADVISORY_LABEL_JT, etc).  If you see this, you are all set for browsing the K1000's tables and finding out what's in them.
___________________________________________

Note that if you want to connect to additional ORGs (ORG2, ORG3, etc), you will need to change the username to correspond (R2, R3, etc).  For example:

Server Host: K1000 (or your K1000 DNS name or IP)
Port: 3306
Username: R2
Password: box747
Default Schema: ORG2
__________________________________________________________________________________

(08) Pulling info from the K1000 databases
___________________________________________

Drag an item from the Schemata frame on the right (for example, ORG1 - MACHINE) into the SQL Query Area (top frame).  It should automatically create a SQL query that says "SELECT * FROM MACHINE M;".  To see what this query returns, hit the green "Execute the entered query" (lightning bolt) button to run the query, and you should now see a listing of all of your machines in the results frame below.  Use the scrollbar at the bottom and you can review all of the columns for the MACHINE table.  Note that some are marked NULL and that INNER JOIN statements between tables will exclude machines with NULL entries from the results.

If you go back to the Schemata frame (on the right) and expand the MACHINE table, the very first entry you'll see (ID) has a yellow key icon.  This indicates that this is the Primary Key for the table, which is used as a unique identifier for the table.  Typically, this is what you'll see referenced in JOIN statements, such as this:

JOIN MACHINE_LABEL_JT ON (MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID)

This statement basically says to include data from the MACHINE_LABEL_JT table where the MACHINE table's primary key (ID) matches the MACHINE_LABEL_JT table's primary key (MACHINE_ID).  
___________________________________________

A side note - the MACHINE_LABEL_JT table itself is a "Join Table" of the MACHINE and LABEL tables, the latter of which keeps track of all of the K1000's labels.  Just clear any existing statements out, drag the LABEL table over (to create a "SELECT * FROM LABEL L;" query), run the query (lightning bolt button) and you'll see all of your labels (machine and otherwise).
___________________________________________

It should also be noted that other columns can be referenced in JOIN statements as long as data correlates directly, such as:

LEFT JOIN MACHINE ON (MACHINE.BIOS_SERIAL_NUMBER = KBSYS.DELL_ASSET.SERVICE_TAG)

In this case, MACHINE.BIOS_SERIAL_NUMBER is not the primary key for the MACHINE table, but the data in the MACHINE.BIOS_SERIAL_NUMBER column corresponds directly with the data in the KBSYS.DELL_ASSET.SERVICE_TAG primary key column.
__________________________________________________________________________________

(09) How to find out which tables and columns to use
___________________________________________

* Look at example SQL reports in the K1000

There are number of canned reports that come with the K1000 that are in SQL format (Open Tickets last 7 days by Owner, for example).  If there's already an existing report that targets what you are after (but just doesn't do *exactly* what you need), you can use the Duplicate button to copy it and play with the code, determine which tables you need to target and pick up MySQL tricks for use in your other tickets (like using CONCAT statements and DATE_FORMAT, using the referenced report's code as an example).
___________________________________________

* Look at others' reports posted on ITNinja

Same idea as point 1, run a search on ITNinja for "report", "smart label" or "custom ticket rule", and whatever else it is you are trying to do.
___________________________________________

* Create test reports using the Report wizards

If you aren't having any luck with points 1 or 2, or are pretty comfortable with playing around and just need to know which tables to use or a template to tweak, this is probably the most useful approach.  

For reports, start out with the Add New Report wizard to target the items you want to include in the report.  Then once you have your report, select it from the Reports list and hit the Edit SQL link.  This will take you to the SQL code view of the report and let you see exactly what column and table names are being used, as well as any filters you have defined (in the WHERE section).  Depending on the number of tables you need to target, you may need to do this several times.  Once you have all of your columns and tables picked out, it's just a matter of determining how to write the JOIN statements - something that points 1 and 2 can usually clarify, along with test runs.
___________________________________________

* Use the KBSYS database for Dell-specific data

If you need to target warranty info, ship date, patches or other Dell-specific info, look at the KBSYS database.  This is not accessible from the report wizard, so SQL reports are currently the only way to access it.  There are some canned reports on the K1000 that should give you a good start, as well as some great examples on ITNinja.
__________________________________________________________________________________

(10) In conclusion...
___________________________________________

The most important things to keep in mind when trying to write your own SQL Select statements are:

1) Don't expect to get it right the first time.  SQL syntax errors are common (unless you are a MySQL guru), so double-checking for typos and making sure everything corresponds correctly (particularly when cobbling together existing code) are vital.  

2) When results aren't exactly what you expect, review the tables themselves and make sure you are targeting the table that contains everything you want in the FROM statement.  I ran into this situation with one report where not all of my machines were being listed, and it turned out to be due to the first table being called not containing all of my machines.

3) Simplify things and focus on basics - i.e. remove the WHERE statements (filters), test with minimum JOIN statements.  This often helps when running into errors that I don't completely understand.  Sometimes building multiple reports that each get partially what I need and then combining them into a more complex report have helped me understand why my original was throwing errors.

4) Use the MySQL Query Browser to test your Select statement.  This can be a great time saver by letting you see the exact results you'll get in the report without having to actually save and run the report on the K1000.  It can also be fun!  (Depending on your definition of fun, of course...)

5) When all else fails, post a question on ITNinja under the Dell KACE K1000 and K1000 Reporting or K1000 Labels.  This is where I've picked up most of the code I use for reference, have been assisted countless times, and is by far the greatest resource for getting things working.
__________________________________________________________________________________

updated 2012-10-17 - jv