SQLite3 Demo

Recommended reading:

Startup SQLite3 which should be preinstalled on your Mac

$ sqlite3 

# Turn some settings on
sqlite3> .header on
sqlite3> .mode column

All SQL commands inserted into SQLite3

Make a Devices table

CREATE TABLE DEVICES(
ID INTEGER PRIMARY KEY NOT NULL,
NAME TEXT,
IP_ADDRESS TEXT,
PROCESSOR_TYPE TEXT,
MANUFACTURER TEXT,
MEMORY INT
);

Make a Physical Disks table

CREATE TABLE PHYSICAL_DISKS(
ID INTEGER PRIMARY KEY NOT NULL,
SIZE INTEGER,
COMPUTER_ID INTEGER NOT NULL
);

Lets insert some data into devices table

INSERT INTO DEVICES (ID, NAME, IP_ADDRESS, PROCESSOR_TYPE, MANUFACTURER, MEMORY) VALUES (1, 'Jons Computer', '127.0.0.1', 'x86-64bit', 'Dell', 2147483648);
INSERT INTO DEVICES (ID, NAME, IP_ADDRESS, PROCESSOR_TYPE, MANUFACTURER, MEMORY) VALUES (2, 'Richard Mac', '127.0.0.2', 'x86-64bit', 'HP', 4294967296);
INSERT INTO DEVICES (ID, NAME, IP_ADDRESS, PROCESSOR_TYPE, MANUFACTURER, MEMORY) VALUES (3, 'Bobs PC', '127.0.0.3', 'x86-32bit', 'VMware', 1073741824);

Lets insert some data into physical_disks table

INSERT INTO PHYSICAL_DISKS (ID, SIZE, COMPUTER_ID) VALUES (1, 10000000000, 1);
INSERT INTO PHYSICAL_DISKS (ID, SIZE, COMPUTER_ID) VALUES (2, 120000000000, 2);
INSERT INTO PHYSICAL_DISKS (ID, SIZE, COMPUTER_ID) VALUES (3, 80000000000, 3);

Lets do some querying on Device:

# SYNTAX: SELECT * FROM table_name;
SELECT * FROM DEVICES;

# DEVICES output
ID          NAME           IP_ADDRESS  PROCESSOR_TYPE  MANUFACTURER  MEMORY    
----------  -------------  ----------  --------------  ------------  ----------
1           Jons Computer  127.0.0.1   x86-64bit       Dell          2147483648      
2           Richard Mac    127.0.0.2   x86-64bit       HP            4294967296      
3           Bobs PC        127.0.0.3   x86-32bit       VMware        1073741824       

# Try query phsyical_disks table
SELECT * FROM PHYSICAL_DISKS;

# Output for phsyical_disks table
ID          SIZE         COMPUTER_ID
----------  -----------  -----------
1           10000000000  1          
2           12000000000  2          
3           80000000000  3

Problem Breakdown

Verify user's original query

SELECT devices.name, devices.ip_address, devices.processor_type as 'Processor',
(physical_disks.size)/1000000000 as 'HDD (GB)',
round(devices.memory/(1024*1024*1024))+1 as 'RAM(GB)'
 
FROM devices, physical_disks
 
WHERE devices.manufacturer != "VMware"
AND physical_disks.computer_id = devices.id
ORDER BY devices.name;

Output

NAME           IP_ADDRESS  Processor   HDD (GB)    RAM(GB)   
-------------  ----------  ----------  ----------  ----------
Jons Computer  127.0.0.1   x86-64bit   10          3.0       
Richard Mac    127.0.0.2   x86-64bit   120         5.0

Solution

Assumptions: All integer values for memory and size are in bytes

Questions:

1. Why don’t any of my virtual machines and their disks/RAM show up?

The results show that HDD and RAM are presented correctly on return output, however if the input of physical_disks.size or devices.memory was not initially fully represented in bytes then the formulas used in the query would fail.

E.g. - 4 instead 4294967296

2. Also, how can I get the report to show each computer’s manufacturer (like Dell, HP, etc.?)

Add a devices.manufacturer to the SELECT statement

Modified SQL Query

# Modified SQL Query (using SQLite3 for demostration)
SELECT devices.name, 
       devices.ip_address, 
       devices.manufacturer, 
       devices.processor_type as 'Processor',
       (physical_disks.size)/1000000000 as 'HDD (GB)',
       round(devices.memory/(1024*1024*1024))+1 as 'RAM(GB)'
FROM devices, 
     physical_disks
WHERE devices.manufacturer != "VMware"
AND physical_disks.computer_id = devices.id
ORDER BY devices.name;

# Output
NAME           IP_ADDRESS  MANUFACTURER  Processor   HDD (GB)    RAM(GB)   
-------------  ----------  ------------  ----------  ----------  ----------
Jons Computer  127.0.0.1   Dell          x86-64bit   10          3.0       
Richard Mac    127.0.0.2   HP            x86-64bit   120         5.0