[SOLVED] Wapt query: machine not having certain software

[DelDemone]

Hello!

I've just discovered WAPT reporting.
I'd like to write a query that returns all the computer names of servers that don't have a specific software installed.

I admit I've been struggling with this for several hours.

Could you please provide an example query that would meet my needs?

[blemoigne]

Good morning,

Here is an example for 7-zip:

Code: Select all

select
hosts.computer_name
from hosts
where
0 = (select count(hostsoftwares.host_id)
from hostsoftwares
where hostsoftwares.name ilike '7-zip%%'
and hosts.uuid=hostsoftwares.host_id )
order by hosts.computer_name asc

We could add a condition with machine names that begin with srv:

Code: Select all

select
hosts.computer_name
from hosts
where
0 = (select count(hostsoftwares.host_id)
from hostsoftwares
where hostsoftwares.name ilike '7-zip%%'
and hosts.uuid=hostsoftwares.host_id ) and
hosts.computer_name ilike 'srv%%'
order by hosts.computer_name asc

Best regards,

Bertrand

[FlavienL]

Good morning,

Thank you very much, I've adapted it slightly to our needs:

Code: Select all

select
hosts.computer_name,
last_seen_on
from hosts
where
0 = (select count(hostsoftwares.host_id)
from hostsoftwares
where hostsoftwares.name ilike 'APPLI TATA'
and hosts.uuid=hostsoftwares.host_id ) and not
hosts.computer_name ilike 'l%%'
order by last_seen_on desc

It works perfectly, thank you again
Flavien