Page 1 of 2
[RESOLVED] Windows 10 - Windows 11 compatible machines per AD domain
Published: September 17, 2026 - 09:25
by Catherine
Hello,
I would like to create a query on the WAPT console to extract all the remaining Windows 10 PCs from my Active Directory (I have three Active Directory domains).
This is to determine how many I still need to migrate to Windows 11.
I don't know the commands to do this.
Thank you for your help.
Have a nice day.
Re: Create a query
Published: September 17, 2026 - 11:34 AM
by Jarnaud
Good morning,
You can try this query which will display the number of machines per domain, still in version 10.
Code: Select all
SELECT
COALESCE(dnsdomain, 'TOTAL') AS dnsdomain,
COUNT(*) AS nombre_machines,
STRING_AGG(computer_name, ', ' ORDER BY computer_name) AS machines
FROM hosts
WHERE os_name ~* '^Windows 10'
GROUP BY ROLLUP (dnsdomain)
ORDER BY
CASE WHEN dnsdomain IS NULL THEN 1 ELSE 0 END,
dnsdomain;
Alternatively, if you prefer a line-by-line rendering to better see the computer_names, you can try this:
Code: Select all
SELECT
dnsdomain,
computer_name,
os_name
FROM (
SELECT
dnsdomain,
computer_name,
os_name,
1 AS ordre,
computer_name AS tri
FROM hosts
WHERE os_name ~* '^Windows 10'
UNION ALL
SELECT
dnsdomain,
'TOTAL ' || dnsdomain,
COUNT(*)::text,
2 AS ordre,
''
FROM hosts
WHERE os_name ~* '^Windows 10'
GROUP BY dnsdomain
UNION ALL
SELECT
'TOTAL',
'TOTAL',
COUNT(*)::text,
3 AS ordre,
''
FROM hosts
WHERE os_name ~* '^Windows 10'
) AS resultats
ORDER BY
dnsdomain,
ordre,
tri;
Yours sincerely
Re: Create a query
Published: September 17, 2026 - 11:45 AM
by Catherine
Hello,
Correction: the second solution is correct, but it shows me all the PCs running Windows 10,
which is exactly what I asked for.
I didn't specify that in a query I asked which PCs are capable of migrating and which are not.
So, in the second formula, I would like it to only show me the PCs that are still capable of migrating.
Thank you very much for your help.
Have a nice day
Re: Create a query
Published: September 17, 2026 - 2:17 PM
by Jarnaud
Hello,
could you share your request with me so I can try to adapt it?
I'd like to start with this and add total lines if that works for you.
Best regards
Re: Create a query
Published: September 17, 2026 - 2:29 PM
by Catherine
Here's the query to find PCs that aren't compatible with Windows 11
: `SELECT
h.uuid,
h.computer_fqdn,
ha.value_section,
ha.value_key,
ha.value
FROM
hostauditdata as ha
JOIN
hosts as h on h.uuid = ha.host_id
WHERE
ha.value_section = 'w11-requirements' AND
ha.value_key = 'result' AND
ha.value ?& '{NOT CAPABLE}'`.
So I have the PCs that are compatible with Windows 11, which I was able to migrate using the console, and the "not compatible" ones; these will be changed.
And for my part, I'd like to know how many PCs I still need to migrate from Windows 10 compatible to Windows 11 in my Active Directory.
Re: Create a query
Published: September 18, 2026 - 08:36
by Jarnaud
Good morning,
Can you try this?
Code: Select all
SELECT
h.dnsdomain,
COUNT(*) AS nombre_machines,
STRING_AGG(h.computer_name, ', ' ORDER BY h.computer_name) AS machines
FROM hosts AS h
JOIN hostauditdata AS ha
ON h.uuid = ha.host_id
WHERE
h.os_name ~* '^Windows 10'
AND ha.value_section = 'w11-requirements'
AND ha.value_key = 'result'
AND ha.value ?& '{CAPABLE}'
GROUP BY
h.dnsdomain
ORDER BY
h.dnsdomain;
Or
Code: Select all
SELECT
h.dnsdomain,
h.computer_name,
h.os_name
FROM hosts AS h
JOIN hostauditdata AS ha
ON h.uuid = ha.host_id
WHERE
h.os_name ~* '^Windows 10'
AND ha.value_section = 'w11-requirements'
AND ha.value_key = 'result'
AND ha.value ?& '{CAPABLE}'
ORDER BY
h.dnsdomain,
h.computer_name;
Yours sincerely
Re: Create a query
Published: September 18, 2026 - 08:46
by Catherine
Hi Jarnaud,
thank you so much,
it works perfectly!
I'm not very good with SQL yet, lol.
Have a great day! Thanks again.
Re: Create a query
Published: September 18, 2026 - 08:57
by Catherine
Quick question: is it possible
to set the DNS domain to the number of machines and then list the corresponding machines directly below?
For example, AD --A number of machines xxxx
, and below that, all the PCs that correspond to AD A,
and so on, since I have 3 ADs.
Otherwise, it's not a big deal, it works for me
Thanks again, it's great! Have a
nice day.
Re: Create a query
Published: September 18, 2026 - 09:30
by Jarnaud
No worries, the requests can still be useful to others.
So it might be possible, but you'd have to ask someone more knowledgeable than me (probably chatgpt or others ^^)
Unfortunately, SQL works line by line.
I am not capable of giving this type of presentation.
Here's what the cat suggested, you should try it out on your end
My model only has one area, so I can't really guarantee the result.
Code: Select all
SELECT
dnsdomain,
affichage
FROM (
SELECT
h.dnsdomain,
h.dnsdomain || ' - ' || COUNT(*) || ' machines' AS affichage,
0 AS ordre,
'' AS tri
FROM hosts AS h
JOIN hostauditdata AS ha
ON h.uuid = ha.host_id
WHERE
h.os_name ~* '^Windows 10'
AND ha.value_section = 'w11-requirements'
AND ha.value_key = 'result'
AND ha.value ?& '{CAPABLE}'
GROUP BY h.dnsdomain
UNION ALL
SELECT
h.dnsdomain,
' ' || h.computer_name AS affichage,
1 AS ordre,
h.computer_name AS tri
FROM hosts AS h
JOIN hostauditdata AS ha
ON h.uuid = ha.host_id
WHERE
h.os_name ~* '^Windows 10'
AND ha.value_section = 'w11-requirements'
AND ha.value_key = 'result'
AND ha.value ?& '{CAPABLE}'
) AS resultats
ORDER BY
dnsdomain,
ordre,
tri;
Best regards
Re: Create a query
Published: September 18, 2026 - 09:40
by Catherine
