[RESOLVED] Windows 10 - Windows 11 compatible machines per AD domain

[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.

[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

[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

[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

[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.

[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

[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.

[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.

[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

[Catherine]

Well, you see, you shouldn't underestimate yourself.

It works very well, it's exactly what I wanted, and I thank you very much

. Thank you!