.. Reminder for header structure:
  Parts (H1)          : #################### with overline
  Chapters (H2)       : ******************** with overline
  Sections (H3)       : ====================
  Subsections (H4)    : --------------------
  Subsubsections (H5) : ^^^^^^^^^^^^^^^^^^^^
  Paragraphs (H6)     : """"""""""""""""""""
  
.. meta::
  :description: Using the WAPT Console advanced features
  :keywords: WAPT, advanced, features, documentation.

.. |enterprise_feature| image:: wapt-resources/icon_wapt_enterprise.png
  :scale: 3%
  :alt: WAPT Enterprise feature only

.. |ok| image:: wapt-resources/icon-ok.png
  :scale: 5%
  :alt: feature available

.. |nok| image:: wapt-resources/icon-nok.png
  :scale: 5%
  :alt: feature not available  

.. _wapt_reporting:

##########################################################
Using the reporting functions in WAPT |enterprise_feature|
##########################################################

Working principle
*****************

..  youtube:: UjBfelmJyKo

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 :download:`wapt_db_data_structure.svg <wapt-resources/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 :menuselection:`Reporting --> Design Mode --> New query`.

.. figure:: wapt-resources/wapt_console_sql-report-designer_container-window.png
  :align: center
  :alt: Designing a SQL query report in the WAPT Console

  Designing a SQL query report in the WAPT Console

.. hint::

 * To rename a query, press the :kbd:`F2` key.

 * In the top banner, you can write your SQL query.

To edit / modify / save your reports:

* The :guilabel:`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 :guilabel:`New query` button will add a new blank query to the list.

* The :guilabel:`Delete query` button will delete the selected query from the WAPT Server.

* The :guilabel:`Export to Excel` button will export the result of your query to a spreadsheet.

* The :guilabel:`Save queries` button will save your query to the WAPT Server.

* The :guilabel:`Duplicate` button will duplicate an existing query to avoid writing a SQL query from scratch.

* The :guilabel:`Execute` button executes the selected query.

.. note::

 * SQL queries are saved in the PostgreSQL WAPT database.

 * Using :kbd:`CTRL+space` allows to build queries more effectively as it will auto-complete some fields.

**************
Query examples
**************

Computers query
===============

.. tabs::

  .. code-tab:: sql Counting hosts

    select count(*) as "number_of_hosts" from hosts

  .. code-tab:: sql Listing computers

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

  .. code-tab:: sql Listing computers MAC addresses and IP

    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

.. tabs::

  .. code-tab:: sql Listing Windows versions

    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

  .. code-tab:: sql Listing operating systems

    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

  .. code-tab:: sql Listing hosts not seen in a while

    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'

.. tabs::

  .. code-tab:: sql Filtering hosts by chassis types

    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

  .. code-tab:: sql Listing of hosts with their Windows Serial Key

    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
==========

.. tabs::

  .. code-tab:: sql Listing WAPT packages in the WAPT Server repository

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

  .. code-tab:: sql Listing hosts needing upgrade

    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
==============

.. tabs::

  .. code-tab:: sql Listing packages with their number of installation

    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
==============

.. tabs::

  .. code-tab:: sql Listing WAPT Discovery Agents

    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 %%')

  .. code-tab:: sql Listing hosts with their 7zip version associated

    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

  .. code-tab:: sql Listing hosts with their software

    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

  .. code-tab:: sql Listing normalized software

    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 <https://forum.tranquil.it/viewforum.php?f=18&sid=b2a0081dd9a8adb5c57386974d691c6d>`_.

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:

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

.. figure:: wapt-resources/wapt_console_sql-report-normalize_container-window.png
  :align: center
  :alt: Normalizing the name of software titles in the WAPT Console

  Normalizing the name of software titles in the WAPT Console

* Click :guilabel:`Normalize Software Names` in the :guilabel:`Tools` menu.

* Select the software titles whose names to standardize, for example, all different versions of Adobe Flash Player.

* On the column :guilabel:`normalized`, press :kbd:`F2` to assign a standardized name to the selected software titles. Then press :kbd:`Enter`.

.. note::

  * To select several software titles, select them with the :kbd:`shift-up/down` key combination.

  * You can also indicate a software like *windows update* or *banned* (press :kbd:`spacebar` in the corresponding column).

* Press on :guilabel:`Import` to upload your changes onto the WAPT Server.

* Press on :guilabel:`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.

.. code-block:: bash

  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 :file:`pg_hba.conf` of the PostgreSQL version in use.
  In :file:`/etc/postgresql/12/main/pg_hba.conf` for Debian and :file:`/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**.

.. code-block:: ini

  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 :file:`/etc/postgresql/12/main/postgresql.conf` for Debian and :file:`/var/lib/pgsql/12/data/postgresql.conf` for RedHat and derivatives, section **Connection Settings**.

.. code-block:: bash

  listen_addresses = '*'

* Restart the service for your PostgreSQL version.

.. code-block:: bash

  systemctl restart postgresql@12-main.service

* Connect to PostgreSQL on the WAPT Server.

.. code-block:: bash

  sudo -u postgres psql template1

* Then give a password to the *wapt* user.

.. code-block:: sql

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