SQL Reporting Example

[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.

[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

Status of WAPT installations

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'

List of machines with Windows OS and key

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

List of packages present in the local WAPT repository

Code: Select all

select package,version,architecture,description,section,package_uuid,count(*)
from packages
group by 1,2,3,4,5,6   

Machines awaiting update

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