/build/static/layout/Breadcrumb_cap_w.png

K1000 Reports - K1000 Database Keys Relationship Matrix Queries

Typical disclaimer:  I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate. 

Questions and/or suggestions are welcome, and as usual - hope that helps!  ^_^

John

_____________________________

Background

When building SQL queries for K1000 reports, smart labels and ticket rules, it frequently becomes necessary to use JOIN statements so that data can be pulled from multiple tables. In turn, writing JOIN statements requires a knowledge of table relationships - specifically, which columns in two tables contain data common to both tables. At one point (several versions ago), a detailed schema diagram for the K1000 database was painstakingly developed, however (as might be inferred) it was not an automated process and required a large amount of manual tweaks to accurately depict all of the table and column relationships. As the database has expanded significantly since then, it has become infeasible to maintain this diagram, and most knowledge of table relationships is either gained by referencing wizard-generated queries or pure experience. While those approaches work, when you consider all of the K1000 users (customers and internal), the time and effort spent by all to determine these relationships is considerable.

I had been contemplating the situation for a some time now, and while researching in the database for a relationship to a column (inaccessible using the wizards), I discovered a list of all of the columns used in the K1000 database: INFORMATION_SCHEMA.COLUMNS. This table inspired me to write the following queries, which will hopefully save others some time in finessing these relationships from the ORG1 database.

 

Construction

Using the INFORMATION_SCHEMA.COLUMNS table, I developed the following queries to help illustrate the table relationships - specifically primary keys (for example, MACHINE.ID) and foreign keys (columns that typically contain the same data as a primary key column in another table - for example, HD_TICKET.MACHINE_ID or MACHINE_SOFTWARE_JT.MACHINE_ID). While these are not represented graphically, hopefully they can still be good starting points and save some people some time.

I worked under the (potentially faulty) premise that primary keys would typically be named TABLE.ID and foreign key columns would typically be named FOREIGN_TABLE.TABLE_ID. This approach doesn't work in every situation, but it does get most.  

For example, there are situations where a foreign key isn't listed due to (apparently) non-standard naming and tagging conventions - one example being MACHINE_NIC.ID, which should probably be named MACHINE_NIC.MACHINE_ID and set as the primary key for the MACHINE_NIC table.  

Building a filtered query is also not fully automatic, as some knowledge of table relationships is required to get the most out of it (for example, HD_TICKET.OWNER_ID and HD_TICKET.SUBMITTER_ID relate to USER.ID), so this should also be kept in mind when constructing filters.


SQL Queries

The following queries can be setup as SQL Reports on the K1000.  When run, you should get output similar to the attached files. 


1) K1000 Database Keys Relationship Matrix Query

Lists Primary and Foreign Keys for all K1000 Tables in the ORG1 Database.

SELECT C.TABLE_NAME,
(CASE WHEN PK.PRIMARY_KEYS IS NULL THEN 'N/A'
ELSE GROUP_CONCAT(DISTINCT PK.PRIMARY_KEYS ORDER BY 1 SEPARATOR '\n') END) AS PRIMARY_KEYS,
(CASE WHEN FK.FOREIGN_KEYS IS NULL THEN 'N/A'
ELSE GROUP_CONCAT(DISTINCT FK.FOREIGN_KEYS ORDER BY 1 SEPARATOR '\n') END) AS FOREIGN_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN
 (SELECT C.TABLE_NAME,
  CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS PRIMARY_KEYS
  FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE TABLE_SCHEMA = 'ORG1'
  AND COLUMN_KEY = 'pri')
PK ON PK.TABLE_NAME = C.TABLE_NAME
LEFT JOIN
 (SELECT C.TABLE_NAME,
  CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS FOREIGN_KEYS
  FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE TABLE_SCHEMA = 'ORG1'
  AND COLUMN_KEY != 'pri'
  AND COLUMN_NAME rlike '_ID')
FK ON FK.TABLE_NAME = C.TABLE_NAME
WHERE TABLE_SCHEMA = 'ORG1'
GROUP BY C.TABLE_NAME
ORDER BY C.TABLE_NAME, PK.PRIMARY_KEYS, FK.FOREIGN_KEYS
 
Example output (K1000 5.5 GA):

As an unfiltered query it’s a bit to digest, but if a filter is added near the end of each subquery it’s potentially very useful. As an example, I’ve filtered on MACHINE.ID (primary key) and MACHINE_ID (foreign keys) in the next report.

AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id'


2) K1000 Database Keys Relationship Matrix Query (Filtered on MACHINE.ID & MACHINE_ID)

Lists Primary and Foreign Keys for all K1000 Tables in the ORG1 Database that contain either MACHINE.ID or MACHINE_ID to reflect Table relationships to MACHINE.ID.

To customize, filter in each subquery (AND CONCAT... statements) by changing "machine.id" and "machine_id" as desired.

SELECT C.TABLE_NAME,
(CASE WHEN PK.PRIMARY_KEYS IS NULL THEN 'N/A'
ELSE GROUP_CONCAT(DISTINCT PK.PRIMARY_KEYS ORDER BY 1 SEPARATOR '\n') END) AS PRIMARY_KEYS,
(CASE WHEN FK.FOREIGN_KEYS IS NULL THEN 'N/A'
ELSE GROUP_CONCAT(DISTINCT FK.FOREIGN_KEYS ORDER BY 1 SEPARATOR '\n') END) AS FOREIGN_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN
 (SELECT C.TABLE_NAME,
  CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS PRIMARY_KEYS
  FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE TABLE_SCHEMA = 'ORG1'
  AND COLUMN_KEY = 'pri'
  AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id')
PK ON PK.TABLE_NAME = C.TABLE_NAME
LEFT JOIN
 (SELECT C.TABLE_NAME,
  CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS FOREIGN_KEYS
  FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE TABLE_SCHEMA = 'ORG1'
  AND COLUMN_KEY != 'pri'
  AND COLUMN_NAME rlike '_ID'
  AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id')
FK ON FK.TABLE_NAME = C.TABLE_NAME
WHERE TABLE_SCHEMA = 'ORG1'
AND (PK.PRIMARY_KEYS AND FK.FOREIGN_KEYS) IS NOT NULL
GROUP BY C.TABLE_NAME
ORDER BY C.TABLE_NAME, PK.PRIMARY_KEYS, FK.FOREIGN_KEYS
 
Example output (K1000 5.5 GA):


3) K1000 Database Keys Relationship Matrix (Filtered on MACHINE.ID & MACHINE_ID, All Tables) 

Lists Primary and Foreign Keys for all K1000 Tables in the ORG1 Database that contain either MACHINE.ID or MACHINE_ID to reflect Table relationships to MACHINE.ID. Lists all tables in the ORG1 Database, which can be useful if a full database view is desired.

To customize, filter in each subquery (AND CONCAT... statements) by changing "machine.id" and "machine_id" as desired.

SELECT C.TABLE_NAME,
(CASE WHEN PK.PRIMARY_KEYS IS NULL THEN 'N/A'
ELSE GROUP_CONCAT(DISTINCT PK.PRIMARY_KEYS ORDER BY 1 SEPARATOR '\n') END) AS PRIMARY_KEYS,
(CASE WHEN FK.FOREIGN_KEYS IS NULL THEN 'N/A'
ELSE GROUP_CONCAT(DISTINCT FK.FOREIGN_KEYS ORDER BY 1 SEPARATOR '\n') END) AS FOREIGN_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN
 (SELECT C.TABLE_NAME,
  CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS PRIMARY_KEYS
  FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE TABLE_SCHEMA = 'ORG1'
  AND COLUMN_KEY = 'pri'
  AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id')
PK ON PK.TABLE_NAME = C.TABLE_NAME
LEFT JOIN
 (SELECT C.TABLE_NAME,
  CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS FOREIGN_KEYS
  FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE TABLE_SCHEMA = 'ORG1'
  AND COLUMN_KEY != 'pri'
  AND COLUMN_NAME rlike '_ID'
  AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id')
FK ON FK.TABLE_NAME = C.TABLE_NAME
WHERE TABLE_SCHEMA = 'ORG1'
GROUP BY C.TABLE_NAME
ORDER BY C.TABLE_NAME, PK.PRIMARY_KEYS, FK.FOREIGN_KEYS
 
Example output (K1000 5.5 GA):


Baseline Queries

For anyone that might be interested in how I developed the above queries (or for those who just want a list of primary keys or foreign keys for each table), here are the baseline queries I started with. To combine these, I used the INFORMATION_SCHEMA.COLUMNS table in the main query and joined these focused queries using the TABLE_NAME column for the JOIN criteria.

PK) Baseline Primary Key Query

Lists Primary Keys for all K1000 Tables in the ORG1 Database.

SELECT C.TABLE_NAME,
CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS PRIMARY_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA = 'ORG1'
AND COLUMN_KEY = 'pri'

PKf) Filtered Primary Key Query (Example)

Lists Primary Keys for all K1000 Tables in the ORG1 Database that contain either MACHINE.ID or MACHINE_ID.

SELECT C.TABLE_NAME,
CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS PRIMARY_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA = 'ORG1'
AND COLUMN_KEY = 'pri'
AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id'

FK) Baseline Foreign Key Query

Lists Foreign Keys for all K1000 Tables in the ORG1 Database.

SELECT C.TABLE_NAME,
CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS FOREIGN_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA = 'ORG1'
AND COLUMN_KEY != 'pri'
AND COLUMN_NAME rlike '_ID'

FKf) Filtered Baseline Foreign Key Query (Example)

Lists Foreign Keys for all K1000 Tables in the ORG1 Database that contain either MACHINE.ID or MACHINE_ID.

SELECT C.TABLE_NAME,
CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) AS FOREIGN_KEYS
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA = 'ORG1'
AND COLUMN_KEY != 'pri'
AND COLUMN_NAME rlike '_ID'
AND CONCAT(C.TABLE_NAME, '.', C.COLUMN_NAME) rlike 'machine.id|machine_id'
Posted 11 years ago 10157 views

Comments

  • John - this helps a lot! I have been trying to develop queries to provide me with insight/ trending information and not knowing the PK/FK relationships has made this task fairly difficult. I really wish a ERD was created even for just a subset of tables. I even ran the DB through MySQL workbench ERD drawing program and it did not come up with anything useful. Thanks again for this HUGE tip! - Jbr32 11 years ago
  • John, thank you for this information. I am putting together a small application (lccMySQLConsole) and have been building out the process for auto retrieving the DB schema, views, etc. from Kace and will be placing on our distribution site at http://lcc.ctc.edu/demo/projectlid as lccMySQLConsole. I plan on add basic reports as well, but, since the Logic feeding the reports is just a text file (Logic File) anyone should be able to customize. Thank you again, and I hope my contribution helps as well. - tfymninja 9 years ago
    • Very cool - I look forward to seeing what you come up with! ^_^

      John - jverbosk 9 years ago
  • Got pulled away on too many other projects the last few months, man time flys. However, I am back on it and finished the code/program. I am documenting now and should have something to share soon. *smile* (what's 5 months, eh?) - tfymninja 9 years ago
  • I finished creating the tool and it has been put on our distribution site. It is called lccMySQLConsole. Though it is a generic mySQL tool, I have included Logic Files specific to retrieving KACE schemas, tickets, etc. Hope this helps everyone. link: http://lcc.ctc.edu/demo/projectlid/lccMySQLConsole - tfymninja 9 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ