.. 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 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. You can import SQL queries from the Tranquil IT store by clicking on :menuselection:`Reporting --> Import queries... --> From Url` or on :menuselection:`Reporting --> Import queries... --> From File`. .. figure:: wapt-resources/wapt_console-reporting-import-query.png :align: center :alt: Importing a SQL query report in the WAPT Console Importing a SQL query report in the WAPT Console If you choose to import a query from the Tranquil IT store, select one or several queries, then click on :guilabel:`Save selected queries`. The new queries will appear in the WAPT Console. .. figure:: wapt-resources/wapt_console-reporting-import-query-from-store.png :align: center :alt: Importing a SQL query report in the WAPT Console from a URL Importing a SQL query report in the WAPT Console from a URL If you import a query from a file, select your query from the file explorer. The query can be either a :mimetype:`.query` or a :mimetype:`.json` formated file. .. figure:: wapt-resources/wapt_console-reporting-import-query-from-file.png :align: center :alt: Importing a SQL query report in the WAPT Console from a file Importing a SQL query report in the WAPT Console from a file .. note:: Irrelevant of the method you use to import queries, remember to click on the button :guilabel:`Save Queries` to save the imported queries. To create a new SQL 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, select the query and press the :kbd:`F2` key. * In the top banner, you can write your SQL query. To edit / modify / save your reports: * The :guilabel:`Refresh` 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 adds a new blank query to the list. * The :guilabel:`Delete query` button deletes the selected query from the WAPT Server. * The :guilabel:`Save queries` button saves the query on the WAPT Server. * The :guilabel:`Execute` button executes the selected query. * The :guilabel:`Export to spreadsheet` button exports the result of your query to a spreadsheet. The button is enabled only in edit mode. * The :guilabel:`Duplicate` button duplicates an existing query to avoid writing a SQL query from scratch. The button is enabled only in edit mode. You have several options available when you right click on a query. .. figure:: wapt-resources/wapt_console_sql-report-query-options.png :align: center :alt: Options available for a SQL query report in the WAPT Console Options available for a SQL query report in the WAPT Console .. list-table:: List of actions available on a selection of query on WAPT Console :header-rows: 1 :widths: auto :align: center * - Name - Description * - :guilabel:`Execute` - Executes the SQL query. * - :guilabel:`Edit` - Edits the SQL query. * - :guilabel:`Edit Name` - Edits the name of the SQL query. * - :guilabel:`Export to spreadsheet` - Exports the result of the SQL query to a :mimetype:`csv` formatted file. * - :guilabel:`Duplicate` - Duplicates the SQL query. * - :guilabel:`Export queries to file...` - Exports the selected SQL queries to a file. This method allows to share or backup the SQL queries. * - :guilabel:`Import queries...` - Imports queries from a file. * - :guilabel:`Delete queries` - Deletes the selected queries. .. 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. ********************************************** Using queries as a filter in the inventory tab ********************************************** You can create a filter for use in the :guilabel:`Inventory` tab based on a SQL query. In this exemple, we use a query which lists hosts onto which the 7zip software has been installed. .. hint:: This method is powerful because it allows to seach search for software titles that have not been installed using WAPT. On the query result, right-click on :guilabel:`host_id`, which is a unique identifier, select this entry and click on the :guilabel:`Choose as Host UUID` button. .. figure:: wapt-resources/wapt_console_sql-report-query-host-uuid.png :align: center :alt: Querying on host UUID to create a dynamic view in the inventory tab Querying on host UUID to create a dynamic view in the inventory tab When done, save the query by pressing the :guilabel:`Save query` button then go to the :guilabel:`Main Inventory` tab. Then, enable the :guilabel:`Advanced Search` panel and select the query in the drop-down field :guilabel:`Filter hosts on SQL query`. .. figure:: wapt-resources/wapt_console_sql-report-host-uuid-inventory.png :align: center :alt: Filtering the host inventory using a SQL query Filtering the host inventory using a SQL query You will then see a host list based on the selected query. ************** 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.last_seen_on::timestamp, h.connected_users from hostsoftwares s left join hosts h on h.uuid=s.host_id where s.key='WAPT_is1' and h.last_seen_on<'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 `_. 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, the name of software titles can be standardized in WAPT. Go to the :guilabel:`Softwares Inventory` tab. .. 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. Finally press :kbd:`Enter` to save the changes. .. 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 the :guilabel:`Save Modifications` button to upload the changes onto the WAPT Server. You can now run queries using this standardized name. .. hint:: The :guilabel:`Show host` checkbox allows to see the software titles that are installed on the hosts. .. figure:: wapt-resources/wapt_console_normalize_container-window-show-host.png :align: center :alt: Showing hosts with selected software in the Software Inventory tab Showing hosts with selected software in the Software Inventory tab Using normalized software titles as a filter in the inventory tab ================================================================= You can create a filter in the :guilabel:`Main Inventory` tab that uses the normalized software title names. To do so, normalize the software title names in :guilabel:`Software Inventory` then select one or several hosts. In the :guilabel:`Software inventory` tab of the selected hosts, drag and drop the software in the inventory list, it will create a view. .. hint:: This method is powerful because it allows to seach search for software titles that have not been installed using WAPT. Do not forget to normalize software titles beforehand. .. figure:: wapt-resources/wapt_console_sql-report-normalize-software-example.png :align: center :alt: Defining a normalized software title name Defining a normalized software title name .. figure:: wapt-resources/wapt_console_sort-normalized-software-inventory.gif :align: center :alt: Adding a normalized software title name to the software inventory for the selected hosts Adding a normalized software title name to the software inventory for the selected hosts You will see a host list sorted according to the normalized software title 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';