Using the reporting functions in WAPT ¶

Working principle¶

WAPT Enterprise offers advanced reporting capabilities.

Indeed, you are best to know what you want in your reports.

With WAPT you can write your own SQL queries in the WAPT Console, or you can download ready-to-run queries from Internet.

The database structure diagram is available here wapt_db_data_structure.svg.

WAPT Query Designer¶

The query designer allows to edit and run SQL queries on the WAPT Server PostgreSQL database.

Note

The PostgreSQL database is set to Read-Only mode, so queries run from the Report Designer that attempt to update, delete or insert data will fail.

To create a new report, click on Reporting ‣ Design Mode ‣ New query.

Designing a SQL query report in the WAPT Console¶

Hint

To rename a query, press the F2 key.
In the top banner, you can write your SQL query.

To edit / modify / save your reports:

The Reload queries button is used to reload queries saved on the WAPT Server, for example, if a colleague has just edited a new query.
The New query button will add a new blank query to the list.
The Delete query button will delete the selected query from the WAPT Server.
The Export to Excel button will export the result of your query to a spreadsheet.
The Save queries button will save your query to the WAPT Server.
The Duplicate button will duplicate an existing query to avoid writing a SQL query from scratch.
The Execute button executes the selected query.

Note

SQL queries are saved in the PostgreSQL WAPT database.
Using CTRL+space allows to build queries more effectively as it will auto-complete some fields.

Query examples¶

Computers query¶

select count(*) as "number_of_hosts" from hosts

select
computer_name,
os_name,
os_version,
os_architecture,
serialnr
from hosts
order by 4,3,1

select distinct unnest(mac_addresses) as mac,
unnest(h.connected_ips) as ipaddress,
computer_fqdn,h.description,
h.manufacturer||' '||h.productname as model,
h.serialnr,
h.computer_type
from hosts h
order by 1,2,3

select
host_info->'windows_version' as windows_version,
os_name as operating_system,
count(os_name) as nb_hosts
from hosts
group by 1,2

select host_info->'windows_version' as windows_version,
os_name as "Operating_System",
count(os_name) as "number_of_hosts"
from hosts
group by 1,2

select
h.uuid,
h.computer_fqdn,
install_date::date,
version,
h.listening_timestamp::timestamp,
h.connected_users from hostsoftwares s
left join hosts h on h.uuid=s.host_id
where s.key='WAPT_is1'
and h.listening_timestamp<'20190115'

select case
dmi->'Chassis_Information'->>'Type'
when 'Portable' then '01-Laptop'
when 'Notebook' then '01-Laptop'
when 'Laptop' then '01-Laptop'
when 'Desktop' then '02-Desktop'
when 'Tower' then '02-Desktop'
when 'Mini Tower' then '02-Desktop'
else '99-'||(dmi->'Chassis_Information'->>'Type')
end as type_chassis,
string_agg(distinct coalesce(manufacturer,'?') ||' '|| coalesce(productname,''),', '),
count(*) as "number_of_hosts" from hosts
group by 1

select
computer_name,
os_name,
os_version,
host_info->'windows_product_infos'->'product_key' as windows_product_key
from hosts
order by 3,1

WAPT query¶

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

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

Packages query¶

select
package,
version,
architecture,
description,
section,
package_uuid,
count(*)
from hostpackagesstatus s
where section not in ('host','unit','group')
group by 1,2,3,4,5,6

Software query¶

select
h.uuid,
h.computer_name,
install_date::date,
version,
h.listening_timestamp::timestamp,
name
from hostsoftwares s
left join hosts h on h.uuid=s.host_id
where
s.key='WAPT_is1'
and (name ilike 'WAPT%%Discovery%%' or name ilike 'WAPT %%')

select
hosts.computer_name,
hostsoftwares.host_id,
hostsoftwares.name,
hostsoftwares.version
from hosts, hostsoftwares
where hostsoftwares.name ilike '7-zip%%'
and hosts.uuid=hostsoftwares.host_id
order by hosts.computer_name asc

select
n.normalized_name,
s.version,string_agg(distinct lower(h.computer_name),' '),
count(distinct h.uuid)
from hostsoftwares s
left join normalization n on (n.original_name = s.name) and (n.key = s.key)
left join hosts h on h.uuid = s.host_id
where (n.normalized_name is not null)
and (n.normalized_name<>'')
and not n.windows_update
and not n.banned
and (last_seen_on::date > (current_timestamp - interval '3 week')::date)
group by 1,2

select
n.normalized_name,
string_agg(distinct lower(h.computer_name),' '),
count(distinct h.uuid)
from hostsoftwares s
left join normalization n on (n.original_name = s.name) and (n.key = s.key)
left join hosts h on h.uuid = s.host_id
where (n.normalized_name is not null)
and (n.normalized_name<>'')
and not n.windows_update
and not n.banned
and (last_seen_on::date > (current_timestamp - interval '3 week')::date)
group by 1

You can also find several more examples of queries on Tranquil IT’s Forum.

Feel free to post your own queries on the forum with an explanation of what your query does, ideally with a screen capture or a table showing a sample of your query result.

Normalizing software names¶

Sometimes, the version of the software or its architecture are an integral part of the software name. When the software titles register with the WAPT Server inventory, they appear as different software titles whereas they are just one software for us humans.

To solve this problem, we can standardize the name of some software titles in WAPT.

Normalizing the name of software titles in the WAPT Console¶

Click Normalize Software Names in the Tools menu.
Select the software titles whose names to standardize, for example, all different versions of Adobe Flash Player.
On the column normalized, press F2 to assign a standardized name to the selected software titles. Then press Enter.

Note

To select several software titles, select them with the shift-up/down key combination.
You can also indicate a software like windows update or banned (press spacebar in the corresponding column).

Press on Import to upload your changes onto the WAPT Server.
Press on Write to save your changes.

You can now run your queries using this standardized name.

Connecting to the WAPT database using a PostgreSQL client¶

You can connect a PostgreSQL client to the WAPT database if you prefer to use a PostgreSQL client.

To do so, you will have to change some configuration files on your WAPT Server.

First, find the version of your PostgreSQL database.

ps -ef | grep -i sql
postgres   512     1  0 Jan05 ?        00:00:24 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

Modify pg_hba.conf of the PostgreSQL version in use. In /etc/postgresql/12/main/pg_hba.conf for Debian and /var/lib/pgsql/12/data/pg_hba.conf for RedHat and derivatives, add the IP address of the PostgreSQL client under # IPv4 local connections section.

host    wapt             all             192.168.0.65/32              md5

where 192.168.0.65 is your IP address that is authorized
to connect to the WAPT database.

Allow PostgreSQL to listen on every interface in /etc/postgresql/12/main/postgresql.conf for Debian and /var/lib/pgsql/12/data/postgresql.conf for RedHat and derivatives, section Connection Settings.

listen_addresses = '*'

Restart the service for your PostgreSQL version.

systemctl restart postgresql@12-main.service

Connect to PostgreSQL on the WAPT Server.

sudo -u postgres psql template1

Then give a password to the wapt user.

template1=# ALTER USER wapt WITH PASSWORD 'PASSWORD';