This will be a repository of sample SQL queries of Tartarus

For 2016/2017 financial year, summarise the spending (excl VAT) by category
select sum(cost), c.name from item i join category c on (i.category=c.category_id) 
where delivered_date >= '2016-08-01' and delivered_date < '2017-08-01' group by c.name;

For 2016/2017 financial year, show spend (supplier,description,cost (excl VAT) for specified category
select supplier,description,cost, c.name from item i join category c on (i.category=c.category_id)
join purchase_order p on (i.purchase_order=p.purchase_order_id) 
where delivered_date >= '2016-08-01' and delivered_date < '2017-08-01' and c.name='desktop' order by supplier;

For 2017/2018 financial year, show the total cost of each order, assuming VAT at 1.20
select purchase_order,sum(cost)*1.20 from item 
where delivered_date >= '2018-08-01'  and delivered_date <  '2019-08-01' group by purchase_order order by sum(cost) desc;

Show all the items of specified category (in this example 'monitor') that are less than 5 years old
select purchase_order,serial,description,delivered_date,cost \
 from item i join category c on i.category=c.category_id
 where delivered_date > (now()-interval '5 years') and c.name='monitor'
 and orphaned='f' and disposal_date is NULL
 order by delivered_date,purchase_order;

Report on DICE physical servers which have spectre vulnerabilities (ordered by delivered_date)
select cr.hostname,i.model,data->'os'->'lcfg_group' as group, data->'os'->>'upsince' as upsince,delivered_date from client_report cr 
join item i on (cr.hostname=i.hostname) 
where now()-received < interval '1 week' and data->'os'->>'spectre_status' = 'VULNERABLE' 
and data->'os'->>'virt_environment' = 'none' order by delivered_date;

Report on physical servers and storagearrays located in IF-B.02 and last seen on the network since 2019-07-01 (in format used for Energy Audit)
select hostname,role,manager,'','','IF-B.02','','',COALESCE((make || ' '|| model), description),'',round(cast(DATE_PART('day',NOW()-delivered_date) as decimal)/365) as age
from item i join category c on (i.category=c.category_id) 
where location='IF-B.02' and vm_host is null and (c.name='server' or c.name='storagearray') and last_seen > '2019-07-01'
order by role,hostname;

Report the desktops which have a root disk partition smaller than 90GB (for DICE machines which have been running in the last week)
with hostpart AS 
  (select hostname,jsonb_array_elements(data->'disk'->'df')->>'size' as size,jsonb_array_elements(data->'disk'->'df')->>'target' as target from client_report where now()-received < interval '1 week') 
select i.hostname,i.location,i.description,hp.size,hp.target from item i 
join hostpart hp on (i.hostname = hp.hostname) join category c on (i.category=c.category_id) 
where hp.target='/' and c.name='desktop'  and CAST(hp.size as integer) < 94000000 order by hostname;

Report on servers that have a GPU card
with gpuhosts as 
  (select hostname,jsonb_array_elements(data->'gpus'->'cards')->'product_name' from client_report where now()-received < interval '1 week') 
select distinct(i.hostname) from  gpuhosts join item i on (gpuhosts.hostname = i.hostname) join category c on (i.category = c.category_id) 
where c.name='server' order by hostname;

Report on servers that don't have a GPU card
 select hostname,description from item i join category c on (i.category = c.category_id)
 where c.name='server' and now()-last_reported < interval '2 weeks' and hostname not in 
   (with gpuhosts as (select hostname,jsonb_array_elements(data->'gpus'->'cards')->'product_name' from client_report where now()-received < interval '2 weeks') 
    select distinct(i.hostname) from gpuhosts join item i on (gpuhosts.hostname = i.hostname) join category c on (i.category = c.category_id) where c.name='server');

Report servers (including the number of CPU cores) that don't have a GPU card and were not purchased on G40588
select i.hostname,description,budget,cr.data->'dmi'->'cpu'->>'ncore' from item i 
join category c on (i.category = c.category_id) join client_report cr on i.hostname=cr.hostname 
where  now()-last_reported < interval '2 weeks' and c.name='server' and i.hostname not in 
  ( with gpuhosts as (select hostname,jsonb_array_elements(data->'gpus'->'cards')->'product_name' from client_report where now()-received < interval '2 weeks')
    select distinct(i.hostname) from gpuhosts join item i on (gpuhosts.hostname = i.hostname) join category c on (i.category = c.category_id) where c.name='server') 
and budget not like '%G40588%';

As above but sum the number of cores
 select sum(cast(cr.data->'dmi'->'cpu'->>'ncore' as integer)) from item i 
join category c on (i.category = c.category_id) join client_report cr on i.hostname=cr.hostname 
where  now()-last_reported < interval '2 weeks' and c.name='server' and i.hostname not in 
  ( with gpuhosts as (select hostname,jsonb_array_elements(data->'gpus'->'cards')->'product_name' from client_report where now()-received < interval '2 weeks')
    select distinct(i.hostname) from gpuhosts join item i on (gpuhosts.hostname = i.hostname) join category c on (i.category = c.category_id) where c.name='server') 
and budget not like '%G40588%';

Find UUNs of those people who have no kit allocated to them and aren't in the upstream 'person' feed
select uun from person where uun not in 
 (select distinct(allocated_to) from item where allocated_to is not null)
 and upstream='f';

Display the CPU family and processor model of all servers
select i.hostname,data->'os'->'cpu'->>'model',data->'dmi'->'cpu'->>'model',location,coalesce(allocated_to,requestor),role,make||model,last_reported,manager 
from item i join client_report cr on cr.hostname=i.hostname join category c on i.category=c.category_id 
where c.name='server'and disposal_date is null  order by cast(data->'os'->'cpu'->>'model' as integer);

-- AlastairScobie - 08 Jan 2020

Topic revision: r2 - 11 Feb 2020 - 15:55:23 - AlastairScobie
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies