puppetdb SQL queries

testqueries.sql

SELECT DISTINCT certname FROM factsets WHERE producer_timestamp > now()::timestamp - interval '1 week';

SELECT DISTINCT fs.certname, f.factset_id, f.fact_path_id, f.fact_value_id FROM facts f
INNER JOIN factsets fs ON fs.id = f.factset_id
INNER JOIN fact_values fv ON f.fact_value_id = fv.id
INNER JOIN fact_paths fp ON f.fact_path_id = fp.id
WHERE fs.producer_timestamp > now()::timestamp - interval '1 hour';


SELECT DISTINCT fs.certname, fs.producer_timestamp, v.type, fp.name, fv.value_string FROM facts f
INNER JOIN factsets fs ON fs.id = f.factset_id
INNER JOIN fact_values fv ON f.fact_value_id = fv.id
INNER JOIN fact_paths fp ON f.fact_path_id = fp.id
INNER JOIN value_types v on fv.value_type_id = v.id
WHERE fs.producer_timestamp > now()::timestamp - interval '1 hour';


-- shortened kernel release versions
SELECT DISTINCT fs.certname, fs.producer_timestamp, v.type, fp.name, fv.value_string FROM facts f
INNER JOIN factsets fs ON fs.id = f.factset_id
INNER JOIN fact_values fv ON f.fact_value_id = fv.id
INNER JOIN fact_paths fp ON f.fact_path_id = fp.id
INNER JOIN value_types v on fv.value_type_id = v.id
WHERE fs.producer_timestamp > now()::timestamp - interval '1 hour' AND
fp.name = 'kernelrelease';

-- count kernel release
SELECT COUNT(DISTINCT fs.certname) AS occurances, fv.value_string AS kernelrelease FROM facts f
INNER JOIN factsets fs ON fs.id = f.factset_id
INNER JOIN fact_values fv ON f.fact_value_id = fv.id
INNER JOIN fact_paths fp ON f.fact_path_id = fp.id
INNER JOIN value_types v on fv.value_type_id = v.id
WHERE fs.producer_timestamp > now()::timestamp - interval '1 week' AND
fp.name = 'kernelrelease'
GROUP BY fv.value_string
ORDER BY kernelrelease;



facts -> {factsets, fact_paths, fact_values}
factsets -> {certnames, environments}
fact_values -> value_types

        Table "public.facts"
    Column     |  Type  | Modifiers 
---------------+--------+-----------
 factset_id    | bigint | not null
 fact_path_id  | bigint | not null
 fact_value_id | bigint | not null
Indexes:
    "facts_factset_id_fact_path_id_fact_key" UNIQUE CONSTRAINT, btree (factset_id, fact_path_id)
    "facts_fact_path_id_idx" btree (fact_path_id)
    "facts_fact_value_id_idx" btree (fact_value_id)
Foreign-key constraints:
    "fact_path_id_fk" FOREIGN KEY (fact_path_id) REFERENCES fact_paths(id)
    "fact_value_id_fk" FOREIGN KEY (fact_value_id) REFERENCES fact_values(id)
    "factset_id_fk" FOREIGN KEY (factset_id) REFERENCES factsets(id) ON UPDATE CASCADE ON DELETE CASCADE


                                        Table "public.factsets"
       Column       |           Type           |                       Modifiers                       
--------------------+--------------------------+-------------------------------------------------------
 id                 | bigint                   | not null default nextval('factsets_id_seq'::regclass)
 certname           | text                     | not null
 timestamp          | timestamp with time zone | not null
 environment_id     | bigint                   | 
 producer_timestamp | timestamp with time zone | 
Indexes:
    "factsets_pkey" PRIMARY KEY, btree (id)
    "factsets_certname_idx" UNIQUE CONSTRAINT, btree (certname)
Foreign-key constraints:
    "factsets_certname_fk" FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE
    "factsets_environment_id_fk" FOREIGN KEY (environment_id) REFERENCES environments(id) ON UPDATE RESTRICT ON DELETE RESTRICT
Referenced by:
    TABLE "facts" CONSTRAINT "factset_id_fk" FOREIGN KEY (factset_id) REFERENCES factsets(id) ON UPDATE CASCADE ON DELETE CASCADE

                                    Table "public.fact_values"
    Column     |         Type          |                        Modifiers                         
---------------+-----------------------+----------------------------------------------------------
 id            | bigint                | not null default nextval('fact_values_id_seq'::regclass)
 value_type_id | bigint                | not null
 value_hash    | character varying(40) | not null
 value_integer | bigint                | 
 value_float   | double precision      | 
 value_string  | text                  | 
 value_boolean | boolean               | 
 value_json    | text                  | 
Indexes:
    "fact_values_pkey" PRIMARY KEY, btree (id)
    "fact_values_value_hash_key" UNIQUE CONSTRAINT, btree (value_hash)
    "fact_values_value_float_idx" btree (value_float)
    "fact_values_value_integer_idx" btree (value_integer)
Foreign-key constraints:
    "fact_values_value_type_id_fk" FOREIGN KEY (value_type_id) REFERENCES value_types(id) ON UPDATE RESTRICT ON DELETE RESTRICT
Referenced by:
    TABLE "facts" CONSTRAINT "fact_value_id_fk" FOREIGN KEY (fact_value_id) REFERENCES fact_values(id)

                                 Table "public.fact_paths"
 Column |          Type           |                        Modifiers                        
--------+-------------------------+---------------------------------------------------------
 id     | bigint                  | not null default nextval('fact_paths_id_seq'::regclass)
 depth  | integer                 | not null
 name   | character varying(1024) | 
 path   | text                    | not null
Indexes:
    "fact_paths_pkey" PRIMARY KEY, btree (id)
    "fact_paths_path_key" UNIQUE CONSTRAINT, btree (path)
    "fact_paths_name" btree (name)
Referenced by:
    TABLE "facts" CONSTRAINT "fact_path_id_fk" FOREIGN KEY (fact_path_id) REFERENCES fact_paths(id)




fac