Page 1 of 1
[SOLVED] Duplicate SQL query for host
Published: October 18, 2023 - 5:25 PM
by Gaetan
Good morning,
Here is a query that retrieves duplicate posts on a park:
Code: Select all
SELECT count(hosts.computer_name) AS count_double,hosts.computer_name
from hosts
group by hosts.computer_name
having count(hosts.computer_name) > 1
Re: Duplicate SQL query host
Published: June 20, 2024 - 10:29
by Gaetan
Improvement for displaying it in inventory filters:
Code: Select all
SELECT h.uuid, h.computer_name
FROM hosts h
JOIN (
SELECT computer_name
FROM hosts
GROUP BY computer_name
HAVING COUNT(computer_name) > 1
) dup ON h.computer_name = dup.computer_name
ORDER BY h.uuid;
Re: Duplicate SQL query host
Published: July 13, 2024 - 08:10
by gaelds
Hello,
thank you for the request! Could you remind me how to add this query to the "Filter with queries" search field in the console? I knew how, but I can't find the information anymore... And as I recall, it wasn't intuitive at all.
Re: Duplicate SQL query host
Published: July 15, 2024 - 09:25
by Gaetan
Hello, when you put the UUID in your query it appears
Re: Duplicate SQL query host
Published: October 9, 2025 - 4:36 PM
by Jarnaud
Hello,
We have taken the time to document the method in the official documentation:
https://www.wapt.fr/fr/doc/wapt-console ... entory-tab
Have a good day
Re: [SOLVED] Duplicate SQL query for host
Published: November 7, 2025 - 10:32 AM
by sfonteneau
Code: Select all
WITH x AS (
SELECT
uuid,
computer_name,
last_seen_on,
COUNT(*) OVER (PARTITION BY computer_name) AS cnt,
MAX(last_seen_on) OVER (PARTITION BY computer_name) AS last_seen_max
FROM hosts
)
SELECT uuid, computer_name, last_seen_on
FROM x
WHERE cnt > 1
AND last_seen_on < last_seen_max
ORDER BY computer_name, last_seen_on;