Unsupported Windows 10 versions

[vincent_n]

Good morning,

I'm trying to create a report that gives me a count of Windows 10 versions that are no longer supported (and later, the associated hostnames)

Code: Select all

select host_info->'windows_version' as windows_version,os_name as "Operating_System",count(os_name) as "Nb_Machines"
from hosts
where host_info->'windows_version'<'10.0.18363'
group by 1,2

Since 10.0.18363 isn't a number in the strict sense, I suppose I can't perform that type of operation. Unless I can "cast" it (is that the right term?) ) in a type that would allow me to use this operator?
Otherwise, I'm trying to do something a little ugly:

Code: Select all

select host_info->'windows_version' as "Build_number",os_name as "OS",count(os_name) as "Count"
from hosts
where CAST(host_info->'windows_version' AS CHAR) NOT LIKE '10.0.18363'
AND CAST(host_info->'windows_version' AS CHAR) NOT LIKE '10.0.19%%'
AND os_name LIKE 'Windows 10%%'
group by 1,2

The query returns everything, even what I wanted to exclude with the NOT LIKE criteria

Any ideas?

THANKS !

Note: I'm an infrastructure engineer, I hate development, and I don't have much experience with querying