Page 1 of 1
SQL Reporting Example
Published: February 28, 2019 - 3:34 PM
by Mathieu
Hello,
after seeing your presentation of version 1.7, is it possible to get some examples of your queries? We saw some that might interest us (I don't know SQL at all).
I hope this is possible.
Thank you.
Re: SQL Reporting Example
Published: February 28, 2019 - 6:21 PM
by kguerineau
Hello Mathieu,
Here are some SQL queries we have internally.
Sincerely,
Kevin
Number of machines:
Code: Select all
select count(*) as "Nb_Machines" from hosts
Code: Select all
SELECT h.uuid,h.computer_fqdn,install_date::date,version,h.listening_timestamp::timestamp from hostsoftwares s
left join hosts h on h.uuid=s.host_id
where
s.key='WAPT_is1'
and
h.listening_timestamp>='20190118'
Code: Select all
select computer_name,os_name,os_version,host_info->'windows_product_infos'->'product_key' as windows_product_key from hosts order by 3,1
Code: Select all
select package,version,architecture,description,section,package_uuid,count(*)
from packages
group by 1,2,3,4,5,6
Code: Select all
select
computer_fqdn, host_status, last_seen_on::date,h.wapt_status,string_agg(distinct lower(s.package),' ')
from hosts h
left join hostpackagesstatus s on s.host_id=h.uuid and s.install_status != 'OK'
where (last_seen_on::date > (current_timestamp - interval '1 week')::date and host_status!='OK')
group by 1,2,3,4