[SOLVED] Create a query

[Catherine]

Hello,

I've never created a query in Reporting before.
I'd like to create a "Cortex" query to find out which PCs have a Cortex version prior to 9.0.0.
Could you please help me?

Thank you for your help.

[jarnaud]

Hello,

No problem, here's how:

1. Create the query

In the WAPT console, go to Reporting → Design Mode → New Query. This adds an empty query that you can then edit and rename (F2 key).

2. The SQL Query

Based on the example "Listing hosts with their 7zip installed" (which uses the hostsoftwares table joined to hosts), here is a query adapted for Cortex:

`sql
select
h.uuid,
h.computer_name,
s.version,
s.install_date::date
from hostsoftwares s
left join hosts h on h.uuid = s.host_id
where
s.name ilike '%cortex%'
and string_to_array(s.version, '.')::int[] < string_to_array('9.0.0', '.')::int[]`

For your information, all the documentation related to reporting is here: https://www.wapt.fr/fr/doc/wapt-console ... rting.html

Best regards

[Catherine]

Thank you very much.

I just tried it and I got an error message: "error on server: IndexError ('tuple index out of range')".

What does that mean?

Regards.

[jarnaud]

This means that an index (a position) in a tuple (or list) does not exist.
Try again with this query:


SELECT hostsoftwares.version,
hostsoftwares.name,
hosts.computer_name,
hostsoftwares.host_id
FROM hosts, hostsoftwares
WHERE hostsoftwares.name ILIKE 'Cortex%%'
AND hosts.uuid = hostsoftwares.host_id
AND string_to_array(hostsoftwares.version, '.')::integer[] < ARRAY[9,0,0]
ORDER BY hosts.computer_name ASC


Best regards

[Catherine]

Great,

thank you so much!

Quick question: if Cortex updates, will re-executing the query change the result?

Thank you very much

Have a lovely day.
Sincerely.

[jarnaud]

Yes, a query displays results at a specific point in time (its execution).
If machines update Cortex to a version higher than 9 in the meantime, then those updates will no longer be displayed in the query results.

Best regards.