Liquidware Labs Inc.Copyright (c) 2025 Liquidware Labs, Inc.Eclipse BIRT Designer Version 4.4.2.v201410272105 Build <4.4.2.v20150217-1805>Microsoft Windows 11 Upgrade Hardware CompatibilityFor each machine listed in the Stratusphere Machine Inventory with a CID Key, this report lists whether or not it is compatible with Microsoft Windows 11 from a hardware perspective. Please refer to the Glossary of Terms on the last page for more details on how a machine is determined to be compatible for Windows 11 Operating System.
This report can be filtered based on Machine Groups that can be configured within the Stratusphere Web UI under Inventory > Machines tab and is sorted by the Machine Name column in ascending order.hub_versionstringtruelwlReportNamestringtruelwlCategoryNamestringtruelwlDefaultFiltersstringtruelwlDiscardIfEmptybooleantruelwlDoAlertbooleantruelwlAlertMessagestringtruelwlOutputFormatstringtruelwlDefaultSchedulestringtrueReportVersionstringtrue6.70Microsoft Windows 11 Upgrade Hardware CompatibilityWindows MigrationfalsefalseXLS, PDFResourceStringTableinlwl-styles.defaultThemeMicrosoft Windows 11 Upgrade Hardware Compatibilityauto layoutltrlwl-masterpage.rptlibrarylwl-masterpagelwl-styles.rptlibrarylwl-styleslwl-defaultparams.rptlibrarylwl-defaultparamsstaticintegertruevar dait = new Date();
var gmtOffset = -dait.getTimezoneOffset() * 60 * 1000;
gmtOffset;simpletext-boxUnformattedstaticstringtrueUS/Easternsimpletext-boxUnformattedPlease enter a short timezone strings for display purposesstaticstringtrueETsimpletext-boxUnformattedtruestaticdecimaltruevar dait = new Date();
var gmtOffset = dait.getTimezoneOffset() * 60 * 1000;
dait.setTime(dait.getTime() + gmtOffset);
dait.valueOf();simpletext-boxUnformattedMachine group namestatictruestringtrue%simplefalsetext-boxUnformattedMaximum Rows in Reportstaticintegertrue3650simpletext-boxUnformattedcontentBidiFormatStrILYNNmetadataBidiFormatStrILYNNdisabledMetadataBidiFormatStrdisabledContentBidiFormatStrorg.postgresql.Driverjdbc:postgresql://enter.hubdb.ipordns:5432/portalreportscmVwb3J0cw==free_hdd_gbvar freehdd = 0.0;
if (row["node_max_disk_size_gb"] - row["node_max_disk_used_gb"] > 0.0) {
freehdd = row["node_max_disk_size_gb"] - row["node_max_disk_used_gb"];
}
freehdd;floatwin11_inplace_upgrade_labelvar label = "Unsupported";
var incompLabel = "Unsupported";
var compLabel = "Supported";
var maybeLabel = "Partial";
var installedLabel = "Installed";
var strOsPlatform = row["node_os_desc"];
var strOsVersion = row["node_os_version"];
//if (strOsPlatform != null && strOsPlatform.indexOf("Windows") > 0 && strOsVersion != null && strOsVersion.indexOf("10.0.22") == 0) {
// label = installedLabel;
//}
if ((row["node_cpu_speed_ghz"] < 1.0) ||
(row["node_per_cpu_cores"] < 2) ||
(row["ram_alloc_gb"] < 4.0) ||
(row["node_cpu_bitwidth"] != null && row["node_cpu_bitwidth"] < 64.0) ||
(row["free_hdd_gb"] < 20.0) ||
(row["node_gpu_color_depth"] != null && row["node_gpu_color_depth"] < 8) ||
(row["os_dx12orwddm2"] != null && row["os_dx12orwddm2"] === 0) ||
(row["max_display_res_y"] != null && row["max_display_res_y"] < 720) ||
(row["max_display_size_in_inches"] != null && row["max_display_size_in_inches"] < 9) ||
(row["machine_bios_uefi_capable"] != null && row["machine_bios_uefi_capable"] === false) ||
(row["machine_secure_boot"] != null && row["machine_secure_boot"] === 'Not supported') ||
(row["machine_tpm_specification"] != null && row["machine_tpm_specification"] === 0)
) {
label = incompLabel;
}
else if (row["node_cpu_bitwidth"] === null ||
row["node_gpu_color_depth"] === null ||
row["os_dx12orwddm2"] === null ||
row["max_display_res_y"] === null ||
row["max_display_size_in_inches"] === null ||
row["machine_bios_uefi_capable"] === null ||
row["machine_secure_boot"] === null ||
row["machine_tpm_specification"] === null) {
label = maybeLabel;
}
else {
label = compLabel;
}
label;stringwin11_image_upgrade_labelvar label = "Unsupported";
var incompLabel = "Unsupported";
var compLabel = "Supported";
var maybeLabel = "Partial";
var installedLabel = "Installed";
var strOsPlatform = row["node_os_desc"];
var strOsVersion = row["node_os_version"];
//if (strOsPlatform != null && strOsPlatform.indexOf("Windows") > 0 && strOsVersion != null && strOsVersion.indexOf("10.0.22") == 0) {
// label = installedLabel;
//}
if ((row["node_cpu_speed_ghz"] < 1.0) ||
(row["node_per_cpu_cores"] < 2) ||
(row["ram_alloc_gb"] < 4.0) ||
(row["node_cpu_bitwidth"] != null && row["node_cpu_bitwidth"] < 64.0) ||
(row["node_max_disk_size_gb"] < 64.0) ||
(row["node_gpu_color_depth"] != null && row["node_gpu_color_depth"] < 8) ||
(row["os_dx12orwddm2"] != null && row["os_dx12orwddm2"] === 0) ||
(row["max_display_res_y"] != null && row["max_display_res_y"] < 720) ||
(row["max_display_size_in_inches"] != null && row["max_display_size_in_inches"] < 9) ||
(row["machine_bios_uefi_capable"] != null && row["machine_bios_uefi_capable"] === false) ||
(row["machine_secure_boot"] != null && row["machine_secure_boot"] === 'Not supported') ||
(row["machine_tpm_specification"] != null && row["machine_tpm_specification"] === 0)
) {
label = incompLabel;
}
else if (row["node_cpu_bitwidth"] === null ||
row["node_gpu_color_depth"] === null ||
row["os_dx12orwddm2"] === null ||
row["max_display_res_y"] === null ||
row["max_display_size_in_inches"] === null ||
row["machine_bios_uefi_capable"] === null ||
row["machine_secure_boot"] === null ||
row["machine_tpm_specification"] === null) {
label = maybeLabel;
}
else {
label = compLabel;
}
label;stringcpu_support_statusvar statusStr = "N";
var model = row["cpu_model_number"];
var brand = row["cpu_brand"];
var subBrand = row["cpu_sub_brand"];
var subBrandMod = row["cpu_sub_brand_modifier"];
var gen = row["cpu_generation"];
var sku = row["cpu_sku"];
if (brand === 'Intel') {
if ((subBrand === 'Atom') &&
(model === 'x6200FE' || model === 'x6211E' || model === 'x6212RE' || model === 'x6413E' ||
model === 'x6414RE' || model === 'x6425E' || model === 'x6425RE' || model === 'x6427FE')) {
statusStr = "Y";
}
else if (subBrand === 'Celeron') {
if ((subBrandMod === 'G') &&
(sku === '4900' || sku === '4920' || sku === '4930' || sku === '4932' || sku === '4950' ||
sku === '5900' || sku === '5905' || sku === '5920' || sku === '5925')) {
statusStr = "Y";
}
else if ((subBrandMod === 'J') &&
(sku === '4005' || sku === '4025' || sku === '4105' || sku === '4115' ||
sku === '4125' || sku === '6412' || sku === '6413')) {
statusStr = "Y";
}
else if ((subBrandMod === 'N') &&
(sku === '4000' || sku === '4020' || sku === '4100' || sku === '4120' || sku === '4500' ||
sku === '4505' || sku === '5100' || sku === '5105' || sku === '6210' || sku === '6211')) {
statusStr = "Y";
}
else if ((subBrandMod === null) &&
(sku === '6305' || sku === '3867' || sku === '4205' || sku === '4305' ||
sku === '5205' || sku === '5305' || sku === '6305')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else if (subBrand === 'Core') {
if (gen >= 8) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else if (subBrand === 'Pentium') {
if ((subBrandMod === 'Gold') &&
(sku === '4417' || sku === '4425' || sku === '5405' || sku === '6405' || sku === '6500' || sku === '7505' ||
sku === 'G5400' || sku === 'G5400T' || sku === 'G5420' || sku === 'G5420T' || sku === 'G5500' || sku === 'G5500T' ||
sku === 'G5600' || sku === 'G5600T' || sku === 'G5620' || sku === 'G6400' || sku === 'G6400E' || sku === 'G6400T' ||
sku === 'G6400TE' || sku === 'G6405' || sku === 'G6405T' || sku === 'G6500' || sku === 'G6500T' || sku === 'G6505' ||
sku === 'G6505T' || sku === 'G6600' || sku === 'G6605')) {
statusStr = "Y";
}
else if ((subBrandMod === 'Silver') &&
(sku === 'J5005' || sku === 'J5040' || sku === 'N5000' || sku === 'N5030' || sku === 'N6000' || sku === 'N6005')) {
statusStr = "Y";
}
else if ((subBrandMod === null) &&
(sku === '6805' || sku === 'J6426' || sku === 'N6415')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else if (subBrand === 'Xeon') {
if ((subBrandMod === 'Bronze') && (sku === '04' || sku === '06')) {
statusStr = "Y";
}
else if (subBrand === 'Silver') {
if ((gen === 1) && (sku === '08' || sku === '09' || sku === '10' || sku === '12' || sku === '14' || sku === '16')) {
statusStr = "Y";
}
else if ((gen === 2) && (sku === '08' || sku === '09' || sku === '10' || sku === '14' || sku === '15' || sku === '16')) {
statusStr = "Y";
}
else if ((gen === 3) && (sku === '09' || sku === '10' || sku === '14' || sku === '16')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else if (subBrand === 'Gold') {
if (model < 6000) {
if ((gen === 1) && (sku === '15' || sku === '18' || sku === '19' || sku === '20' || sku === '22')) {
statusStr = "Y";
}
else if ((gen === 2) && (sku === '15' || sku === '17' || sku === '18' || sku === '20' || sku === '22')) {
statusStr = "Y";
}
else if ((gen === 3) && (sku === '15' || sku === '17' || sku === '18' || sku === '20' || sku === '22')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else {
if ((gen === 1) &&
(sku === '26' || sku === '28' || sku === '30' || sku === '32' || sku === '34' ||
sku === '36' || sku === '38' || sku === '40' || sku === '42' || sku === '44' ||
sku === '46' || sku === '48' || sku === '50' || sku === '52' || sku === '54')) {
statusStr = "Y";
}
else if ((gen === 2) &&
(sku === '08' || sku === '09' || sku === '10' || sku === '12' || sku === '22' ||
sku === '26' || sku === '30' || sku === '34' || sku === '38' || sku === '40' ||
sku === '42' || sku === '44' || sku === '46' || sku === '48' || sku === '50' ||
sku === '52' || sku === '54' || sku === '56' || sku === '58' || sku === '62')) {
statusStr = "Y";
}
else if ((gen === 3) &&
(sku === '12' || sku === '14' || sku === '26' || sku === '30' || sku === '34' || sku === '36' ||
sku === '38' || sku === '42' || sku === '46' || sku === '48' || sku === '54')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
}
else if (subBrand === 'Platinum') {
if (model < 9000) {
if ((gen === 1) &&
(sku === '53' || sku === '56' || sku === '58' || sku === '60' || sku === '64' ||
sku === '68' || sku === '70' || sku === '76' || sku === '80')) {
statusStr = "Y";
}
else if ((gen === 2) &&
(sku === '53' || sku === '56' || sku === '60' || sku === '68' || sku === '70' || sku === '76' || sku === '80')) {
statusStr = "Y";
}
else if ((gen === 3) &&
(sku === '51' || sku === '52' || sku === '58' || sku === '60' || sku === '62' || sku === '68' || sku === '80')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else if (model >= 9000) {
if ((gen === 2) && (sku === '21' || sku === '22' || sku === '42' || sku === '82')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else {
statusStr = "N";
}
}
else if (subBrand === 'E') {
if (model === 'E-2176M' || model === 'E-2124' || model === 'E-2124G' || model === 'E-2126G' || model === 'E-2134' || model === 'E-2136' ||
model === 'E-2144G' || model === 'E-2146G' || model === 'E-2174G' || model === 'E-2176G' || model === 'E-2176M' || model === 'E-2186G' ||
model === 'E-2186M' || model === 'E-2224' || model === 'E-2224G' || model === 'E-2226G' || model === 'E-2226GE' || model === 'E-2234' ||
model === 'E-2236' || model === 'E-2244G' || model === 'E-2246G' || model === 'E-2254ME' || model === 'E-2254ML' || model === 'E-2274G' ||
model === 'E-2276G' || model === 'E-2276M' || model === 'E-2276ME' || model === 'E-2276ML' || model === 'E-2278G' || model === 'E-2278GE' ||
model === 'E-2278GEL' || model === 'E-2286G' || model === 'E-2286M' || model === 'E-2288G') {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else if (subBrand === 'W') {
if (model === 'W-10855M' || model === 'W-10885M' || model === 'W-11855M' || model === 'W-11955M' || model === 'W-1250' || model === 'W-1250E' ||
model === 'W-1250P' || model === 'W-1250TE' || model === 'W-1270' || model === 'W-1270E' || model === 'W-1270P' || model === 'W-1270TE' ||
model === 'W-1290' || model === 'W-1290E' || model === 'W-1290P' || model === 'W-1290T' || model === 'W-1290TE' || model === 'W-2102' ||
model === 'W-2104' || model === 'W-2123' || model === 'W-2125' || model === 'W-2133' || model === 'W-2135' || model === 'W-2145' ||
model === 'W-2155' || model === 'W-2175' || model === 'W-2195' || model === 'W-2223' || model === 'W-2225' || model === 'W-2235' ||
model === 'W-2245' || model === 'W-2255' || model === 'W-2265' || model === 'W-2275' || model === 'W-2295' || model === 'W-3175X' ||
model === 'W-3223' || model === 'W-3225' || model === 'W-3235' || model === 'W-3245' || model === 'W-3245M' || model === 'W-3265' ||
model === 'W-3265M' || model === 'W-3275' || model === 'W-3275M') {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else {
statusStr = "N";
}
}
else {
statusStr = "N";
}
}
else if (brand === 'AMD') {
if ((subBrand === 'EPYC') &&
(model === '7252' || model === '7262' || model === '7272' || model === '7282' || model === '7302' || model === '7313' ||
model === '7343' || model === '7352' || model === '7402' || model === '7413' || model === '7443' || model === '7452' ||
model === '7453' || model === '7502' || model === '7513' || model === '7532' || model === '7542' || model === '7543' ||
model === '7552' || model === '7642' || model === '7643' || model === '7662' || model === '7663' || model === '7702' ||
model === '7713' || model === '7742' || model === '7763' || model === '7232P' || model === '7302P' || model === '72F3' ||
model === '7302P' || model === '7313P' || model === '73F3' || model === '7402P' || model === '7443P' || model === '74F3' ||
model === '7502P' || model === '7543P' || model === '75F3' || model === '7702P' || model === '7713P' || model === '7F32' ||
model === '7F52' || model === '7F72' || model === '7H12')) {
statusStr = "Y";
}
else if (subBrand === 'Ryzen') {
if ((subBrandMod === '3') &&
(model === '3100' || model === '2300X' || model === '3200G' || model === '3200GE' || model === '3200U' || model === '3250C' ||
model === '3250U' || model === '3300U' || model === '3350U' || model === '4300G' || model === '4300GE' || model === '4300U' ||
model === '5300U' || model === '5400U')) {
statusStr = "Y";
}
else if ((subBrandMod === '3 PRO') &&
(model === '3200G' || model === '3200GE' || model === '3300U' || model === '4350G' || model == '4350GE' ||
model === '4450U' || model === '5350G' || model === '5350GE' || model === '5450U')) {
statusStr = "Y";
}
else if ((subBrandMod === '5') &&
(model === '2600' || model === '3600' || model === '2500X' || model === '2600E' || model === '2600X' || model === '3400G' || model === '3400GE' ||
model === '3450U' || model === '3500' || model === '3500C' || model === '3500U' || model === '3550H' || model === '3580U' || model === '3600X' ||
model === '3600XT' || model === '4500U' || model === '4600U' || model === '4600GE' || model === '4600H' || model === '4600U' || model === '5300G' ||
model === '5300GE' || model === '5500U' || model === '5600G' || model === '5600GE' || model === '5600H' || model === '5600HS' || model === '5600U' ||
model === '5600X')) {
statusStr = "Y";
}
else if ((subBrandMod === '5 PRO') &&
(model === '2600' || model === '3600' || model === '3400G' || model === '3400GE' || model === '3500U' || model === '4650G' || model === '4650GE' ||
model === '4650U' || model === '5650G' || model === '5650GE' || model === '5650U' || model === '5750G' || model === '5750GE')) {
statusStr = "Y";
}
else if ((subBrandMod === '7') &&
(model === '2700' || model === '5800' || model === '2700E' || model === '2700X' || model === '3700C' || model === '3700U' || model === '3700X' ||
model === '3750H' || model === '3780U' || model === '3800X' || model === '3800XT' || model === '4700G' || model === '4700GE' || model === '4700U' ||
model === '4800H' || model === '4800HS' || model === '4800U' || model === '5700G' || model === '5700GE' || model === '5700U' || model === '5800H' ||
model === '5800HS' || model === '5800U' || model === '5800X')) {
statusStr = "Y";
}
else if ((subBrandMod === '7 PRO') &&
(model === '2700' || model === '2700X' || model === '3700U' || model === '4750G' || model === '4750GE' || model === '4750U' || model === '5850U')) {
statusStr = "Y";
}
else if ((subBrandMod === '9') &&
(model === '3900' || model === '3900X' || model === '3900XT' || model === '3950X' ||
model === '4900H' || model === '4900HS' || model === '5900HS' || model === '5900HX' ||
model === '5900X' || model === '5950X' || model === '5980HS' || model === '5980HX')) {
statusStr = "Y";
}
else if ((subBrandMod === '9 PRO') && (model === '3900')) {
statusStr = "Y";
}
else if ((subBrandMod === 'Threadripper') &&
(model === '2920X' || model === '2950X' || model === '2970WX' || model === '2990WX' ||
model === '3960X' || model === '3970X' || model === '3990X')) {
statusStr = "Y";
}
else if ((subBrandMod === 'Threadripper PRO') &&
(model === '3945WX' || model === '3955WZ' || model === '3975WX' || model === '3995WX')) {
statusStr = "Y";
}
else {
statusStr = "N";
}
}
else {
statusStr = "N";
}
}
else {
statusStr = "N";
}
statusStr;stringnode_idmeasurenode_idnode_idmachine_namedimensionmachine_namemachine_namemachine_fqmndimensionmachine_fqmnmachine_fqmnnode_os_versiondimensionnode_os_versionnode_os_versionnode_os_descdimensionnode_os_descnode_os_descnode_cpu_modeldimensionnode_cpu_modelnode_cpu_modelnode_cpu_short_modelsdimensionnode_cpu_short_modelsnode_cpu_short_modelsnode_cpu_speed_ghzmeasurenode_cpu_speed_ghznode_cpu_speed_ghzcpu_branddimensioncpu_brandcpu_brandcpu_sub_branddimensioncpu_sub_brandcpu_sub_brandcpu_sub_brand_modifierdimensioncpu_sub_brand_modifiercpu_sub_brand_modifiercpu_model_numberdimensioncpu_model_numbercpu_model_numbercpu_generationdimensioncpu_generationcpu_generationcpu_skudimensioncpu_skucpu_skunode_per_cpu_coresmeasurenode_per_cpu_coresnode_per_cpu_coresnode_cpu_coresmeasurenode_cpu_coresnode_cpu_coresnode_cpu_bitwidthmeasurenode_cpu_bitwidthnode_cpu_bitwidthnode_ram_allocated_gbmeasurenode_ram_allocated_gbnode_ram_allocated_gbram_alloc_gbmeasureram_alloc_gbram_alloc_gbnode_max_disk_size_gbmeasurenode_max_disk_size_gbnode_max_disk_size_gbnode_max_disk_used_gbmeasurenode_max_disk_used_gbnode_max_disk_used_gbnode_total_disk_size_gbmeasurenode_total_disk_size_gbnode_total_disk_size_gbnode_total_disk_used_gbmeasurenode_total_disk_used_gbnode_total_disk_used_gbnode_gpu_modeldimensionnode_gpu_modelnode_gpu_modelnode_discrete_gpu_modelsdimensionnode_discrete_gpu_modelsnode_discrete_gpu_modelsnode_gpu_countmeasurenode_gpu_countnode_gpu_countnode_discrete_gpu_countmeasurenode_discrete_gpu_countnode_discrete_gpu_countnode_gpu_color_depthmeasurenode_gpu_color_depthnode_gpu_color_depthos_dx12orwddm2measureos_dx12orwddm2os_dx12orwddm2node_display_dimensionsdimensionnode_display_dimensionsnode_display_dimensionsmax_display_res_ymeasuremax_display_res_ymax_display_res_ynode_display_size_in_inchesdimensionnode_display_size_in_inchesnode_display_size_in_inchesmax_display_size_in_inchesmeasuremax_display_size_in_inchesmax_display_size_in_inchesnode_display_countmeasurenode_display_countnode_display_countmachine_bios_uefi_capablemeasuremachine_bios_uefi_capablemachine_bios_uefi_capablemachine_bios_uefi_enabledmeasuremachine_bios_uefi_enabledmachine_bios_uefi_enabledmachine_secure_bootdimensionmachine_secure_bootmachine_secure_bootmachine_tpm_specificationsdimensionmachine_tpm_specificationsmachine_tpm_specificationsmachine_tpm_specificationmeasuremachine_tpm_specificationmachine_tpm_specificationparam_1MachineGroupstring121falsetruefalseparam_2MachineGroupstring122falsetruefalse1node_idinteger2machine_namestring3machine_fqmnstring4node_os_versionstring5node_os_descstring6node_cpu_modelstring7node_cpu_short_modelsstring8node_cpu_speed_ghzdecimal9cpu_brandstring10cpu_sub_brandstring11cpu_sub_brand_modifierstring12cpu_model_numberstring13cpu_generationstring14cpu_skustring15node_per_cpu_coresdecimal16node_cpu_coresdecimal17node_cpu_bitwidthinteger18node_ram_allocated_gbdecimal19ram_alloc_gbdecimal20node_max_disk_size_gbdecimal21node_max_disk_used_gbdecimal22node_total_disk_size_gbdecimal23node_total_disk_used_gbdecimal24node_gpu_modelstring25node_discrete_gpu_modelsstring26node_gpu_countdecimal27node_discrete_gpu_countdecimal28node_gpu_color_depthinteger29os_dx12orwddm2integer30node_display_dimensionsstring31max_display_res_yinteger32node_display_size_in_inchesstring33max_display_size_in_inchesinteger34node_display_countdecimal35machine_bios_uefi_capableinteger36machine_bios_uefi_enabledinteger37machine_secure_bootstring38machine_tpm_specificationsstring39machine_tpm_specificationinteger40free_hdd_gbfloat41win11_inplace_upgrade_labelstring42win11_image_upgrade_labelstring43cpu_support_statusstringData Source1node_idnode_idinteger42machine_namemachine_namestring123machine_fqmnmachine_fqmnstring124node_os_versionnode_os_versionstring125node_os_descnode_os_descstring126node_cpu_modelnode_cpu_modelstring127node_cpu_short_modelsnode_cpu_short_modelsstring128node_cpu_speed_ghznode_cpu_speed_ghzdecimal29cpu_brandcpu_brandstring1210cpu_sub_brandcpu_sub_brandstring1211cpu_sub_brand_modifiercpu_sub_brand_modifierstring1212cpu_model_numbercpu_model_numberstring1213cpu_generationcpu_generationstring1214cpu_skucpu_skustring1215node_per_cpu_coresnode_per_cpu_coresdecimal216node_cpu_coresnode_cpu_coresdecimal-517node_cpu_bitwidthnode_cpu_bitwidthinteger518node_ram_allocated_gbnode_ram_allocated_gbdecimal219ram_alloc_gbram_alloc_gbdecimal220node_max_disk_size_gbnode_max_disk_size_gbdecimal221node_max_disk_used_gbnode_max_disk_used_gbdecimal222node_total_disk_size_gbnode_total_disk_size_gbdecimal223node_total_disk_used_gbnode_total_disk_used_gbdecimal224node_gpu_modelnode_gpu_modelstring1225node_discrete_gpu_modelsnode_discrete_gpu_modelsstring1226node_gpu_countnode_gpu_countdecimal-527node_discrete_gpu_countnode_discrete_gpu_countdecimal-528node_gpu_color_depthnode_gpu_color_depthinteger529os_dx12orwddm2os_dx12orwddm2integer-730node_display_dimensionsnode_display_dimensionsstring1231max_display_res_ymax_display_res_yinteger432node_display_size_in_inchesnode_display_size_in_inchesstring1233max_display_size_in_inchesmax_display_size_in_inchesinteger434node_display_countnode_display_countdecimal-535machine_bios_uefi_capablemachine_bios_uefi_capableinteger-736machine_bios_uefi_enabledmachine_bios_uefi_enabledinteger-737machine_secure_bootmachine_secure_bootstring1238machine_tpm_specificationsmachine_tpm_specificationsstring1239machine_tpm_specificationmachine_tpm_specificationinteger-7'vendor' as bios_vendor,
-- bios_info->'smbBios' as bios_smbBios,
-- bios_info->'version' as bios_version,
-- bios_info->'revision' as bios_revision,
-- date_trunc('day', cast(bios_info->'releaseDate' as timestamptz)) as bios_release_date,
case when length(bios_info->'uefiCapable'::text) is null then null else case when bios_info->'uefiCapable' = 'false' then 'N' else 'Y' end end as bios_uefi_capable,
case when length(bios_info->'uefiEnabled'::text) is null then null else case when bios_info->'uefiEnabled' = 'false' then 'N' else 'Y' end end as bios_uefi_enabled,
-- tpm_info->'manufacturer' as tpm_manufacturer,
-- tpm_info->'version' as tpm_version,
-- (case when length(tpm_info->'owned'::text) is null then null else (case when tpm_info->'owned' = 'false' then 'N' else 'Y' end) end) as tpm_owned,
-- (case when length(tpm_info->'active'::text) is null then null else (case when tpm_info->'active' = 'false' then 'N' else 'Y' end) end) as tpm_active,
-- (case when length(tpm_info->'enabled'::text) is null then null else (case when tpm_info->'enabled' = 'false' then 'N' else 'Y' end) end) as tpm_enabled,
tpm_info->'specification' as tpm_specification
from
tnt_config.node_hardware
),
node_cpus as (
select
hardware_id,
string_agg(distinct model, ', ') as node_cpu_model,
string_agg(distinct trim(regexp_replace(replace(replace(replace(replace(replace(split_part(model, ' @', 1), ' processor', ''), ' Processor', ''), ' CPU', ''), ' Eight-Core', ''), ' 8-Core', ''), '\s+', ' ', 'g')), ',') as node_cpu_short_models,
string_agg(distinct case
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 3 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 3 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 3 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 3 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 5 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 5 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 5 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 5 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 7 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 7 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 7 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 7 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 9 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 9 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 9 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 9 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ThreadRipper ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen ThreadRipper ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ThreadRipper PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen ThreadRipper PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen Embedded ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 1), 'Pentium ', 2))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 1), 'Pentium ', 2))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, 'Intel Atom ', 2), ' CPU', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1)) then trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, 'Intel Celeron ', 2), ' CPU', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core2 ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Platinum') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Gold') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Silver') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Bronze') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_model_number,
max(bitwidth) as node_cpu_bitwidth,
string_agg(distinct cpu_manufacturer, ',') as cpu_brand,
string_agg(distinct case
when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Pentium' -- trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Atom' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1)) then 'Celeron' -- trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1)) then 'Core' -- (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2') > 1)) then 'Core2' -- (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then 'Xeon' -- (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_sub_brand,
string_agg(distinct case
when (cpu_manufacturer = 'AMD' and (strpos(model, 'EPYC ') > 1)) then null --
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then left(trim(both ' ' from split_part(model, 'Embedded', 2)), 1)
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') > 1)) then left(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 1)), 1) || ' PRO'
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then left(trim(both ' ' from split_part(model, 'Ryzen ', 2)), 1)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Pentium' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Atom' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1))
then (
case
when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
then null
else left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)
end)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1)) then left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron ', 2), ' CPU', 1)), 1)
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), '-', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2') > 1) and (strpos(model, '-') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), '-', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Platinum ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Gold ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Silver ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Bronze ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon CPU ') > 1) and (strpos(model, ' @ ') > 1)) then left(trim(both ' ' from split_part(split_part(model, ' @ ', 1), 'Intel Xeon CPU ', 2)), 1)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' ', 3), '-', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), '-', 1)))
end, ',') as cpu_sub_brand_modifier,
string_agg(distinct case
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' Embedded ', 2), ' ', 1)), 2, 1)
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') > 1)) then left(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 2)), 1)
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then left(trim(both ' ' from split_part(split_part(model, 'AMD Ryzen ', 2), ' ', 2)), 1)
-- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1))
then
(case
-- Check if Intel, Core, and m3, then the first number after the - is supposed to be the generation
when ((strpos(lower(model), ' m3-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
when ((strpos(lower(model), ' m5-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
when ((strpos(lower(model), ' m7-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- Check if Intel, Core and contains 'th Gen' to figure out generation
when ((strpos(lower(model), 'th gen ') > 1) and (trim(both ' ' from split_part(model, 'th ', 1)) ~ '^\d+(\.\d+)?$'))
then trim(both ' ' from split_part(model, 'th ', 1))
when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 )
then left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
when ((cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) >= 20))
then left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1)
end)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4)), 2, 1)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then null -- (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_generation,
string_agg(distinct case
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''))
-- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' Embedded ', 2), ' ', 1)), 4, 2)
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 2)), 2, 4)
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'AMD Ryzen ', 2), ' ', 2)), 3, 2)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium Gold ', 2), ' CPU ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium Silver ', 2), ' CPU ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1))
then (case when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
then left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
else right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
end)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1)) then right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron ', 2), ' CPU', 1)), 4)
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1))
then
(case
-- Check if Intel, Core, and m3, then the first number after the - is supposed to be the generation
when ((strpos(lower(model), ' m3-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
when ((strpos(lower(model), ' m5-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
when ((strpos(lower(model), ' m7-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- Check if Intel, Core and contains 'th Gen' to figure out generation
when (strpos(lower(model), 'th gen ') > 1)
then right(trim(both ' ' from split_part(model, '-', 2)), 3)
when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') > 1))
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 )
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),3, 2)
when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') <= 0))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),3, 3)
when ((cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) >= 20))
then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2, 3)
end)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4)), 3, 2)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon CPU ') > 1) and (strpos(model, ' @ ') > 1)) then right(trim(both ' ' from split_part(split_part(model, ' @ ', 1), 'Intel Xeon CPU ', 2)), 2)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' CPU ', 2), '-', 2)), 3, 2)
end, ',') as cpu_sku,
-- string_agg(distinct case
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' Embedded ', 2), ' ', 1)), 6, 1)
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'AMD Ryzen ', 2), ' ', 2)), 5, 1)
-- -- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1))
-- then (case when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
-- then right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)
-- -- else right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
-- end)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1))
-- then
-- (case
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') > 1))
-- then right(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)), 2)
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') <= 0))
-- then right(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)), 1)
-- when ((cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) >= 20))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)), 5, 2)
-- end)
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
-- end, ',') as cpu_suffix,
string_agg(distinct case
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then 'AMD Opteron ' || (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then 'AMD Turion ' || replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', '')
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then 'AMD Ryzen ' || replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', '')
when (cpu_manufacturer = 'AMD') then (trim(both ' ' from 'AMD ' || split_part(split_part(model, 'AMD ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Intel Pentium ' || trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Intel Atom ' || trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1)) then (trim(both ' ' from split_part(model, ' CPU ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then 'Intel Xeon ' || (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_manu_class_model_number,
count(*) as node_cpu_count,
round(avg(speed_mhz)::numeric,0) as node_per_cpu_mhz,
round(avg(cores)::numeric,0) as node_per_cpu_cores,
sum(cores) as node_cpu_cores,
round(sum(cores*speed_mhz)::numeric,0) as total_cpu_mhz
from (
select
h.hardware_id,
replace(replace(replace(h.model, '(R)', ''), '(TM)', ''), '(tm)', '') as model,
case
when split_part(replace(replace(replace(h.model, '(R)', ''), '(TM)', ''), '(tm)', ''), ' ', 1) ilike 'Pentium' then 'Intel'
when strpos(model, 'Intel') > 1 then 'Intel'
when strpos(model, 'AMD') > 1 then 'AMD'
else split_part(replace(replace(replace(h.model, '(R)', ''), '(TM)', ''), '(tm)', ''), ' ', 1)
end as cpu_manufacturer,
h.bitwidth,
h.cores as cores,
h.speed_mhz
from
tnt_config.node_hardware_cpus h
) h
group by
hardware_id
),
node_ram as (
select
sumrpts.node_id as node_id,
round((avg(sumrpts.mem_alloc)/1048576)::numeric, 3) as node_ram_allocated_gb
from
tnt_audit.summaryreports sumrpts,
( select
sumrpt.node_id as node_id,
max(sumrpt.start_date) as start_date,
max(sumrpt.end_date) as end_date
from
tnt_audit.summaryreports sumrpt
group by
sumrpt.node_id
) lsr
where
sumrpts.node_id = lsr.node_id
and sumrpts.start_date = lsr.start_date
and sumrpts.end_date = lsr.end_date
and sumrpts.end_date between lsr.start_date and lsr.end_date
group by
sumrpts.node_id
),
node_disk as (
select
part_stats.node_id as node_id,
round(max(part_stats.node_per_disk_size_kb)::numeric/1048576.0, 0) as node_max_disk_size_gb,
round(max(part_stats.node_per_disk_used_kb)::numeric/1048576.0, 0) as node_max_disk_used_gb,
round(sum(part_stats.node_per_disk_size_kb)::numeric/1048576, 0) as node_total_disk_size_gb,
round(sum(part_stats.node_per_disk_used_kb)::numeric/1048576, 0) as node_total_disk_used_gb
from (
select
part_stats.node_id as node_id,
part_stats.disk_id as node_physical_disk_id,
string_agg(distinct part_stats.name, ',') as node_disk_names,
sum(part_stats.size) as node_per_disk_size_kb,
sum(part_stats.avg_used) as node_per_disk_used_kb
from
tnt_audit.summaryreport_partition_stats part_stats,
( select
part_stats.node_id as node_id,
max(part_stats.start_date) as start_date,
max(part_stats.end_date) as end_date
from
tnt_audit.summaryreport_partition_stats part_stats
group by
part_stats.node_id
) last_part_stats
where
part_stats.node_id = last_part_stats.node_id
and part_stats.start_date = last_part_stats.start_date
and part_stats.end_date = last_part_stats.end_date
and part_stats.end_date between last_part_stats.start_date and last_part_stats.end_date
and part_stats.local_type ilike 'local'
group by
part_stats.node_id, part_stats.disk_id
) part_stats
group by
part_stats.node_id
),
node_health_stats as (
select
hstats.node_id,
hstats.sec_secure_boot
from
tnt_audit.summaryreport_machine_health_stats hstats,
( select
node_id as node_id,
max(start_date) as start_date,
max(end_date) as end_date
from
tnt_audit.summaryreport_machine_health_stats
group by
node_id
) last_health_stats
where
hstats.node_id = last_health_stats.node_id
and hstats.start_date = last_health_stats.start_date
and hstats.end_date = last_health_stats.end_date
and hstats.end_date between last_health_stats.start_date and last_health_stats.end_date
),
node_gpus as (
select
hardware_id,
string_agg(model, ', ') as node_gpu_model,
string_agg(model, ',') as node_discrete_gpu_models,
count(*) as node_discrete_gpu_count,
string_agg(format('%s', speedmhz), ', ') as node_gpu_speed_mhz,
-- string_agg(format('%s', colordepth), ', ') as node_gpu_color_depth,
max(colordepth) as node_gpu_color_depth,
count(*) as node_gpu_count
from
tnt_config.node_hardware_gpus
where
is_real = true
group by
hardware_id
),
node_displays as (
select
hardware_id,
string_agg(dimensions, ', ') as node_display_dimensions,
max(cast(split_part(dimensions, 'x', 2) as int)) as max_display_res_y,
string_agg(coalesce(cast(details->'sizeInches' as text), 'N/A'), ',') as node_display_size_in_inches,
max((cast(details->'sizeInches' as int))) as max_display_size_in_inches,
count(*) as node_display_count
from
tnt_config.node_hardware_displays
group by
hardware_id
)
select
pn.id as node_id,
pn.name as machine_name,
nde.fqmn as machine_fqmn,
-- OS
os.version as node_os_version,
replace(replace(replace(replace(replace(replace(replace(replace(os.description, ' , ', ', '), 'Service Pack ', 'SP'), '(R)', ''), '(TM)', ''), '®', ''), 'â„¢', ''), '®', ''), '™', '') as node_os_desc,
-- CPU
nodecpu.node_cpu_model as node_cpu_model,
nodecpu.node_cpu_short_models as node_cpu_short_models,
round((nodecpu.node_per_cpu_mhz/1000.0)::numeric, 3) as node_cpu_speed_ghz,
trim(both ' ' from nodecpu.cpu_brand) as cpu_brand,
trim(both ' ' from nodecpu.cpu_sub_brand) as cpu_sub_brand,
trim(both ' ' from nodecpu.cpu_sub_brand_modifier) as cpu_sub_brand_modifier,
trim(both ' ' from nodecpu.cpu_model_number) as cpu_model_number,
trim(both ' ' from nodecpu.cpu_generation) as cpu_generation,
trim(both ' ' from nodecpu.cpu_sku) as cpu_sku,
nodecpu.node_per_cpu_cores,
nodecpu.node_cpu_cores,
nodecpu.node_cpu_bitwidth,
-- RAM
noderam.node_ram_allocated_gb as node_ram_allocated_gb,
nh.ram_alloc_gb,
-- Disk storage
nodedisk.node_max_disk_size_gb as node_max_disk_size_gb,
nodedisk.node_max_disk_used_gb as node_max_disk_used_gb,
nodedisk.node_total_disk_size_gb as node_total_disk_size_gb,
nodedisk.node_total_disk_used_gb as node_total_disk_used_gb,
-- GPU
nodegpu.node_gpu_model as node_gpu_model,
nodegpu.node_discrete_gpu_models as node_discrete_gpu_models,
-- nodegpu.node_gpu_speed_mhz as node_gpu_speed_mhz,
nodegpu.node_gpu_count as node_gpu_count,
nodegpu.node_discrete_gpu_count as node_discrete_gpu_count,
nodegpu.node_gpu_color_depth,
os.dx12orwddm2 as os_dx12orwddm2,
-- Display
nodedis.node_display_dimensions as node_display_dimensions,
nodedis.max_display_res_y as max_display_res_y,
nodedis.node_display_size_in_inches,
nodedis.max_display_size_in_inches,
nodedis.node_display_count as node_display_count,
-- BIOS & TPM
case when nh.bios_uefi_capable is null then null else case when nh.bios_uefi_capable = 'Y' then true else false end end as machine_bios_uefi_capable,
case when nh.bios_uefi_enabled is null then null else case when nh.bios_uefi_enabled = 'Y' then true else false end end as machine_bios_uefi_enabled,
case when nodehealth.sec_secure_boot is null then null else nodehealth.sec_secure_boot end as machine_secure_boot,
nh.tpm_specification as machine_tpm_specifications,
case when nh.tpm_specification is null then null else case when strpos(nh.tpm_specification, '2.') > 0 then true else false end end as machine_tpm_specification
-- Windows 11 compatibility
-- initcap(nde.win11_inplace_upgrade_compatibility) as win11_inplace_upgrade_compatibility,
-- initcap(nde.win11_image_upgrade_compatibility) as win11_image_upgrade_compatibility
from
tnt_config.policynode pn
join tnt_config.node nde on (nde.id = pn.id)
left outer join tnt_config.os os on (os.id = nde.os_id)
left outer join node_hardware nh on (node_hardware_id = nde.hardware_id)
left outer join node_cpus nodecpu on (nodecpu.hardware_id = nde.hardware_id)
left outer join node_ram noderam on (noderam.node_id = pn.id)
left outer join node_disk nodedisk on (nodedisk.node_id = pn.id)
left outer join node_gpus nodegpu on (nodegpu.hardware_id = nde.hardware_id)
left outer join node_displays nodedis on (nodedis.hardware_id = nde.hardware_id)
left outer join node_health_stats nodehealth on (nodehealth.node_id = pn.id)
where
(pn.delete_time is null) -- Machine has not been deleted
and (nde.delete_time is null) -- Machine has not been deleted
and (nde.ihostversion is not null) -- Machine has a CID Key installed
and (nde.certificate is not null) -- Machine has a valid certificate
and (pn.system_object is false) -- Machine is not a system object like Stratusphere Hub, DB or Station
and (pn.dirtystatusid is null or pn.dirtystatusid <> 3) -- Machine does not have a dirty status
and (pn.type <= 2) -- Machines are either servers (1) or desktops (2)
-- and (os.description not ilike '%Windows 11%'
-- and os.description not ilike '%Server 2016 %'
-- and os.version not ilike '%11.%') -- Machine is not already running Windows 10 or Server 2016
-- and (nde.vmtype is null) -- Machine is a physical machine to be considered for upgrade to Win10
and ('%' = ? or pn.id in
(select ngn.nodeid from tnt_config.policynode pn, tnt_config.nodegroup_node ngn
where (pn.type = 5) and (pn.id = ngn.nodegroupid) and (pn.name like ?)))
-- and (os.name = 'Windows') -- Machines that currently run Microsoft Windows
order by
lower(pn.name) asc nulls last
;]]>2.0In11200UnknownIn21200Unknown]]>nulls lowestnode_idmeasurenode_idnode_idmachine_namedimensionmachine_namemachine_namemachine_fqmndimensionmachine_fqmnmachine_fqmnmachine_last_contactdimensionmachine_last_contactmachine_last_contactmachine_makedimensionmachine_makemachine_makemachine_modeldimensionmachine_modelmachine_modelmachine_chassis_typedimensionmachine_chassis_typemachine_chassis_typenode_os_versiondimensionnode_os_versionnode_os_versionnode_os_descdimensionnode_os_descnode_os_descos_build_versiondimensionos_build_versionos_build_versionnode_cpu_modeldimensionnode_cpu_modelnode_cpu_modelnode_cpu_short_modelsdimensionnode_cpu_short_modelsnode_cpu_short_modelsnode_cpu_speed_ghzmeasurenode_cpu_speed_ghznode_cpu_speed_ghzcpu_branddimensioncpu_brandcpu_brandcpu_sub_branddimensioncpu_sub_brandcpu_sub_brandcpu_sub_brand_modifierdimensioncpu_sub_brand_modifiercpu_sub_brand_modifiercpu_model_numberdimensioncpu_model_numbercpu_model_numbercpu_sku_levelmeasurecpu_sku_levelcpu_sku_levelcpu_generationmeasurecpu_generationcpu_generationcpu_skudimensioncpu_skucpu_skunode_per_cpu_coresmeasurenode_per_cpu_coresnode_per_cpu_coresnode_cpu_coresmeasurenode_cpu_coresnode_cpu_coresnode_cpu_bitwidthmeasurenode_cpu_bitwidthnode_cpu_bitwidthnode_ram_allocated_gbmeasurenode_ram_allocated_gbnode_ram_allocated_gbram_alloc_gbmeasureram_alloc_gbram_alloc_gbnode_disk0_c_size_gbmeasurenode_disk0_c_size_gbnode_disk0_c_size_gbnode_disk0_c_used_gbmeasurenode_disk0_c_used_gbnode_disk0_c_used_gbnode_disk0_c_free_gbmeasurenode_disk0_c_free_gbnode_disk0_c_free_gbnode_total_disk_size_gbmeasurenode_total_disk_size_gbnode_total_disk_size_gbnode_total_disk_used_gbmeasurenode_total_disk_used_gbnode_total_disk_used_gbnode_total_disk_free_gbmeasurenode_total_disk_free_gbnode_total_disk_free_gbnode_gpu_modeldimensionnode_gpu_modelnode_gpu_modelnode_discrete_gpu_modelsdimensionnode_discrete_gpu_modelsnode_discrete_gpu_modelsnode_gpu_countmeasurenode_gpu_countnode_gpu_countnode_discrete_gpu_countmeasurenode_discrete_gpu_countnode_discrete_gpu_countnode_gpu_color_depthmeasurenode_gpu_color_depthnode_gpu_color_depthos_dx12orwddm2measureos_dx12orwddm2os_dx12orwddm2node_display_dimensionsdimensionnode_display_dimensionsnode_display_dimensionsmax_display_res_ymeasuremax_display_res_ymax_display_res_ynode_display_size_in_inchesdimensionnode_display_size_in_inchesnode_display_size_in_inchesmax_display_size_in_inchesmeasuremax_display_size_in_inchesmax_display_size_in_inchesnode_display_countmeasurenode_display_countnode_display_countmachine_bios_uefi_capablemeasuremachine_bios_uefi_capablemachine_bios_uefi_capablemachine_bios_uefi_enabledmeasuremachine_bios_uefi_enabledmachine_bios_uefi_enabledmachine_secure_bootdimensionmachine_secure_bootmachine_secure_bootmachine_tpm_specificationsdimensionmachine_tpm_specificationsmachine_tpm_specificationsmachine_tpm_specificationmeasuremachine_tpm_specificationmachine_tpm_specificationmachine_location_city_state_countrydimensionmachine_location_city_state_countrymachine_location_city_state_countrymachine_location_countrydimensionmachine_location_countrymachine_location_countrymachine_location_statedimensionmachine_location_statemachine_location_statemachine_location_citydimensionmachine_location_citymachine_location_citycpu_support_statusdimensioncpu_support_statuscpu_support_statuswin11_inplace_upgrade_labeldimensionwin11_inplace_upgrade_labelwin11_inplace_upgrade_labelwin11_image_upgrade_labeldimensionwin11_image_upgrade_labelwin11_image_upgrade_labelparam_1TimeZoneStringstring121falsetruefalseparam_2MachineGroupstring122falsetruefalseparam_3MachineGroupstring123truefalse1node_idinteger2machine_namestring3machine_fqmnstring4machine_last_contactdate-time5machine_makestring6machine_modelstring7machine_chassis_typestring8node_os_versionstring9node_os_descstring10os_build_versionstring11node_cpu_modelstring12node_cpu_short_modelsstring13node_cpu_speed_ghzdecimal14cpu_brandstring15cpu_sub_brandstring16cpu_sub_brand_modifierstring17cpu_model_numberstring18cpu_sku_levelinteger19cpu_generationinteger20cpu_skustring21node_per_cpu_coresdecimal22node_cpu_coresdecimal23node_cpu_bitwidthinteger24node_ram_allocated_gbdecimal25ram_alloc_gbdecimal26node_disk0_c_size_gbdecimal27node_disk0_c_used_gbdecimal28node_disk0_c_free_gbdecimal29node_total_disk_size_gbdecimal30node_total_disk_used_gbdecimal31node_total_disk_free_gbdecimal32node_gpu_modelstring33node_discrete_gpu_modelsstring34node_gpu_countdecimal35node_discrete_gpu_countdecimal36node_gpu_color_depthinteger37os_dx12orwddm2integer38node_display_dimensionsstring39max_display_res_yinteger40node_display_size_in_inchesstring41max_display_size_in_inchesinteger42node_display_countdecimal43machine_bios_uefi_capableinteger44machine_bios_uefi_enabledinteger45machine_secure_bootstring46machine_tpm_specificationsstring47machine_tpm_specificationinteger48machine_location_city_state_countrystring49machine_location_countrystring50machine_location_statestring51machine_location_citystring52cpu_support_statusstring53win11_inplace_upgrade_labelstring54win11_image_upgrade_labelstringData Source1node_idnode_idinteger42machine_namemachine_namestring123machine_fqmnmachine_fqmnstring124machine_last_contactmachine_last_contactdate-time935machine_makemachine_makestring126machine_modelmachine_modelstring127machine_chassis_typemachine_chassis_typestring128node_os_versionnode_os_versionstring129node_os_descnode_os_descstring1210os_build_versionos_build_versionstring1211node_cpu_modelnode_cpu_modelstring1212node_cpu_short_modelsnode_cpu_short_modelsstring1213node_cpu_speed_ghznode_cpu_speed_ghzdecimal214cpu_brandcpu_brandstring1215cpu_sub_brandcpu_sub_brandstring1216cpu_sub_brand_modifiercpu_sub_brand_modifierstring1217cpu_model_numbercpu_model_numberstring1218cpu_sku_levelcpu_sku_levelinteger419cpu_generationcpu_generationinteger420cpu_skucpu_skustring1221node_per_cpu_coresnode_per_cpu_coresdecimal222node_cpu_coresnode_cpu_coresdecimal-523node_cpu_bitwidthnode_cpu_bitwidthinteger524node_ram_allocated_gbnode_ram_allocated_gbdecimal225ram_alloc_gbram_alloc_gbdecimal226node_disk0_c_size_gbnode_disk0_c_size_gbdecimal227node_disk0_c_used_gbnode_disk0_c_used_gbdecimal228node_disk0_c_free_gbnode_disk0_c_free_gbdecimal229node_total_disk_size_gbnode_total_disk_size_gbdecimal230node_total_disk_used_gbnode_total_disk_used_gbdecimal231node_total_disk_free_gbnode_total_disk_free_gbdecimal232node_gpu_modelnode_gpu_modelstring1233node_discrete_gpu_modelsnode_discrete_gpu_modelsstring1234node_gpu_countnode_gpu_countdecimal-535node_discrete_gpu_countnode_discrete_gpu_countdecimal-536node_gpu_color_depthnode_gpu_color_depthinteger537os_dx12orwddm2os_dx12orwddm2integer-738node_display_dimensionsnode_display_dimensionsstring1239max_display_res_ymax_display_res_yinteger440node_display_size_in_inchesnode_display_size_in_inchesstring1241max_display_size_in_inchesmax_display_size_in_inchesinteger442node_display_countnode_display_countdecimal-543machine_bios_uefi_capablemachine_bios_uefi_capableinteger-744machine_bios_uefi_enabledmachine_bios_uefi_enabledinteger-745machine_secure_bootmachine_secure_bootstring1246machine_tpm_specificationsmachine_tpm_specificationsstring1247machine_tpm_specificationmachine_tpm_specificationinteger-748machine_location_city_state_countrymachine_location_city_state_countrystring1249machine_location_countrymachine_location_countrystring1250machine_location_statemachine_location_statestring1251machine_location_citymachine_location_citystring1252cpu_support_statuscpu_support_statusstring1253win11_inplace_upgrade_labelwin11_inplace_upgrade_labelstring1254win11_image_upgrade_labelwin11_image_upgrade_labelstring12'vendor' as bios_vendor,
-- bios_info->'smbBios' as bios_smbBios,
-- bios_info->'version' as bios_version,
-- bios_info->'revision' as bios_revision,
-- date_trunc('day', cast(bios_info->'releaseDate' as timestamptz)) as bios_release_date,
case when length(bios_info->'uefiCapable'::text) is null then null else case when bios_info->'uefiCapable' = 'false' then 'N' else 'Y' end end as bios_uefi_capable,
case when length(bios_info->'uefiEnabled'::text) is null then null else case when bios_info->'uefiEnabled' = 'false' then 'N' else 'Y' end end as bios_uefi_enabled,
-- tpm_info->'manufacturer' as tpm_manufacturer,
-- tpm_info->'version' as tpm_version,
-- (case when length(tpm_info->'owned'::text) is null then null else (case when tpm_info->'owned' = 'false' then 'N' else 'Y' end) end) as tpm_owned,
-- (case when length(tpm_info->'active'::text) is null then null else (case when tpm_info->'active' = 'false' then 'N' else 'Y' end) end) as tpm_active,
-- (case when length(tpm_info->'enabled'::text) is null then null else (case when tpm_info->'enabled' = 'false' then 'N' else 'Y' end) end) as tpm_enabled,
tpm_info->'specification' as tpm_specification
from
tnt_config.node_hardware
-- where
-- id = 361955
-- id = 1219
),
-- node_cpus1 as (
-- select
-- hardware_id,
-- string_agg(distinct model, ', ') as node_cpu_model,
-- string_agg(distinct rtrim(split_part(split_part(split_part(model, 'processor', 1), 'Processor', 1), 'CPU', 1)), ',') as node_cpu_short_models,
-- array_agg(distinct bitwidth) as node_cpu_3264_bit,
-- cpu_manufacturer,
-- case
-- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Pentium' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Atom' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core ') > 1)) then 'Core' -- (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then 'Xeon' -- (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
-- end as cpu_class,
-- case
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''))
-- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core ') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
-- end as cpu_model_number,
-- case
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''))
-- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core ') > 1) and (strpos(model, '-') > 1))
-- then
-- (case
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 )
-- then left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- when ((cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) >= 20))
-- then left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1)
-- end)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
-- end as cpu_generation,
-- case
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then 'AMD Opteron ' || (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then 'AMD Turion ' || replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', '')
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then 'AMD Ryzen ' || replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', '')
-- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from 'AMD ' || split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Intel Pentium ' || trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Intel Atom ' || trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core ') > 1)) then (trim(both ' ' from split_part(model, ' CPU ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then 'Intel Xeon ' || (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
-- end as cpu_manu_class_model_number,
-- count(*) as node_cpu_count,
-- round(avg(speed_mhz)::numeric,0) as node_per_cpu_mhz,
-- round(avg(cores)::numeric,0) as node_per_cpu_cores,
-- sum(cores) as node_cpu_cores,
-- round(sum(cores*speed_mhz)::numeric,0) as total_cpu_mhz
-- from
-- tnt_config.node_hardware_cpus
-- group by
-- hardware_id
-- ),
node_cpus as (
select
hardware_id,
string_agg(distinct model, ', ') as node_cpu_model,
string_agg(distinct trim(regexp_replace(replace(replace(replace(replace(replace(split_part(model, ' @', 1), ' processor', ''), ' Processor', ''), ' CPU', ''), ' Eight-Core', ''), ' 8-Core', ''), '\s+', ' ', 'g')), ',') as node_cpu_short_models,
string_agg(distinct case
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 3 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 3 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 3 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 3 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 5 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 5 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 5 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 5 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 7 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 7 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 7 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 7 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 9 PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 9 PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen 9 ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen 9 ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ThreadRipper PRO ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen ThreadRipper PRO ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ThreadRipper ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen ThreadRipper ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), ' Ryzen Embedded ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(replace(trim(both ' ' from split_part(split_part(model, ' with ', 1), 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''), ' 8-Core Processor', ''))
when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 1), 'Pentium ', 2))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 1), 'Pentium ', 2))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, 'Intel Atom ', 2), ' CPU', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1)) then trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, 'Intel Celeron ', 2), ' CPU', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, ' @ ') > 1)) then trim(both ' ' from split_part(model, ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core Ultra ') > 1)) then (trim(both ' ' from split_part(model, ' ', 5)))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2 Duo') > 1)) then split_part(model, ' ', 5)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2') > 1)) then split_part(model, ' ', 6)
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Platinum') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Platinum') > 1) and (strpos(model, ' CPU @ ') <= 1)) then trim(both ' ' from split_part(model, ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Gold') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Gold') > 1) and (strpos(model, ' CPU @ ') <= 1)) then trim(both ' ' from split_part(model, ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Silver') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Silver') > 1) and (strpos(model, ' CPU @ ') <= 1)) then trim(both ' ' from split_part(model, ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Bronze') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' @ ') <= 1)) then trim(both ' ' from split_part(model, ' ', 3))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_model_number,
max(bitwidth) as node_cpu_bitwidth,
string_agg(distinct cpu_manufacturer, ',') as cpu_brand,
string_agg(distinct case
when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Pentium' -- trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Atom' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1)) then 'Celeron' -- trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1)) then 'Core' -- (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2') > 1)) then 'Core2' -- (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then 'Xeon' -- (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_sub_brand,
-- string_agg(distinct case
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'EPYC ') > 1)) then null --
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then left(trim(both ' ' from split_part(model, 'Embedded', 2)), 1)
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') > 1)) then left(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 1)), 1) || ' PRO'
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then left(trim(both ' ' from split_part(model, 'Ryzen ', 2)), 1)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium ', 2), ' ', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Pentium' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Atom' -- trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1))
-- then (
-- case
-- when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
-- then null
-- else left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)
-- end)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1)) then left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron ', 2), ' CPU', 1)), 1)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), '-', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, ' Core Ultra ') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Core2') > 1) and (strpos(model, '-') > 1)) then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), '-', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Platinum ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Gold ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Silver ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon Bronze ') > 1) and (strpos(model, ' CPU @ ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon CPU ') > 1) and (strpos(model, ' @ ') > 1)) then left(trim(both ' ' from split_part(split_part(model, ' @ ', 1), 'Intel Xeon CPU ', 2)), 1)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' ', 3), '-', 1)))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), '-', 1)))
-- end, ',') as cpu_sub_brand_modifier,
string_agg(distinct
(case
when (cpu_manufacturer = 'AMD')
then
(case
when (strpos(model, 'EPYC ') > 1)
then null
when (strpos(model, ' Ryzen ') > 1)
then
(case
when (strpos(model, ' Embedded ') > 1)
then left(trim(both ' ' from split_part(model, 'Embedded', 2)), 1)
when (strpos(model, ' PRO ') > 1)
then left(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 1)), 1) || ' PRO'
else
left(trim(both ' ' from split_part(model, ' Ryzen ', 2)), 1)
end)
else null
end)
when (cpu_manufacturer = 'Qualcomm')
then null
when (cpu_manufacturer = 'Intel')
then
(case
when (strpos(model, ' Atom ') > 1)
then 'Atom'
when (strpos(model, ' Celeron ') > 1)
then
(case
when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
then null
when (strpos(model, ' CPU @ ') > 1)
then left(trim(both ' ' from split_part(split_part(model, ' Celeron ', 2), ' CPU', 1)), 1)
when (strpos(model, ' @ ') > 1)
then left(trim(both ' ' from split_part(split_part(model, ' Celeron ', 2), ' @ ', 1)), 1)
-- else left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)
else null
end)
when (strpos(model, ' Core ') > 1)
then
(case
when (strpos(model, '-') > 1)
then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), '-', 1)))
when (strpos(model, ' Ultra ') > 1)
then (trim(both ' ' from split_part(split_part(model, 'Intel Core ', 2), ' ', 1)))
else null
end)
when (strpos(model, ' Core2 ') > 1)
then
(case
when (strpos(model, ' Duo ') > 1 or strpos(model, ' Quad ') > 1)
then trim(both ' ' from split_part(split_part(model, ' Core2 ', 2), ' ', 1))
else null
end)
when (strpos(model, ' Pentium ') > 1)
then
(case
when (strpos(model, ' Gold ') > 1 or strpos(model, ' Silver ') > 1)
then trim(both ' ' from split_part(split_part(model, ' Pentium ', 2), ' ', 1))
else null
end)
when (strpos(model, ' Xeon ') > 1)
then
(case
when ((strpos(model, ' Platinum ') > 1 or strpos(model, ' Gold ') > 1 or strpos(model, ' Silver ') > 1 or strpos(model, ' Bronze ') > 1) and (strpos(model, ' CPU @ ') > 1))
then trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 3))
when ((strpos(model, ' Platinum ') > 1 or strpos(model, ' Gold ') > 1 or strpos(model, ' Silver ') > 1 or strpos(model, ' Bronze ') > 1) and (strpos(model, ' CPU @ ') <= 1))
then trim(both ' ' from split_part(model, ' ', 3))
when ((strpos(model, ' CPU ') > 1) and (strpos(model, ' @ ') > 1))
then left(trim(both ' ' from split_part(split_part(model, ' @ ', 1), 'Intel Xeon CPU ', 2)), 1)
when ((strpos(model, ' CPU @ ') > 1))
then trim(both ' ' from split_part(split_part(model, ' ', 3), '-', 1))
when ((strpos(model, '-') > 1))
then trim(both ' ' from split_part(split_part(model, ' Xeon ', 2), '-', 1))
else
null
end)
when (strpos(model, ' Processor ') > 1)
then null
else
null
end)
else
null
end), ',') as cpu_sub_brand_modifier,
max(
case
when (cpu_manufacturer = 'AMD')
then null
when (cpu_manufacturer = 'Qualcomm')
then null
when (cpu_manufacturer = 'Intel')
then (
case
-- when (strpos(model, ' Atom ') > 1)
-- then null
-- when (strpos(model, ' Celeron ') > 1)
-- then null
-- when (strpos(model, ' Core Ultra ') > 1)
-- then cast(left(trim(both ' ' from split_part(model, ' ', 5)), 1) as integer)
-- when (strpos(model, ' Core ') > 1)
-- then (
-- case
-- when (strpos(model, 'th Gen ') > 1) and (strpos(model, '-') > 1)
-- then cast(left(trim(both ' ' from split_part(model, '-', 2)), 2) as integer)
-- when (strpos(model, 'th Gen ') <= 1) and (strpos(model, '-') > 1) and (length(split_part(split_part(model, '-', 2), ' ', 1)) >= 6)
-- then cast(left(trim(both ' ' from split_part(model, '-', 2)), 2) as integer)
-- when (strpos(model, 'th Gen ') <= 1) and (strpos(model, '-') > 1)
-- then cast(left(trim(both ' ' from split_part(model, '-', 2)), 1) as integer)
-- else null
-- end)
-- when (strpos(model, ' Pentium ') > 1)
-- then null
-- when (strpos(model, ' Processor ') > 1)
-- then null
when (strpos(model, ' Xeon ') > 1)
then (
case
when (strpos(model, ' Platinum ') > 1) or (strpos(model, ' Gold ') > 1) or (strpos(model, ' Silver ') > 1) or (strpos(model, ' Bronze ') > 1)
then
cast(left(trim(both ' ' from split_part(model, ' ', 4)), 1) as integer)
else
null
end)
else
null
end)
else
null
end)::integer as cpu_sku_level,
max(
case
when (cpu_manufacturer = 'AMD')
then null
when (cpu_manufacturer = 'Qualcomm')
then null
when (cpu_manufacturer = 'Intel')
then (
case
when (strpos(model, ' Atom ') > 1)
then null
when (strpos(model, ' Celeron ') > 1)
then null
when (strpos(model, ' Core Ultra ') > 1)
then cast(left(trim(both ' ' from split_part(model, ' ', 5)), 1) as integer)
when (strpos(model, ' Core ') > 1)
then (
case
when (strpos(model, 'th Gen ') > 1) and (strpos(model, '-') > 1)
then cast(left(trim(both ' ' from split_part(model, '-', 2)), 2) as integer)
when (strpos(model, 'th Gen ') <= 1) and (strpos(model, '-') > 1) and (length(split_part(split_part(model, '-', 2), ' ', 1)) >= 6)
then cast(left(trim(both ' ' from split_part(model, '-', 2)), 2) as integer)
when (strpos(model, 'th Gen ') <= 1) and (strpos(model, '-') > 1)
then cast(left(trim(both ' ' from split_part(model, '-', 2)), 1) as integer)
else null
end)
when (strpos(model, ' Pentium ') > 1)
then null
when (strpos(model, ' Processor ') > 1)
then null
when (strpos(model, ' Xeon ') > 1)
then (
case
when (strpos(model, ' Platinum ') > 1) or (strpos(model, ' Gold ') > 1) or (strpos(model, ' Silver ') > 1) or (strpos(model, ' Bronze ') > 1)
then cast(right(left(trim(both ' ' from split_part(model, ' ', 4)), 2), 1) as integer)
else
null
end)
else
null
end)
else
null
end)::integer as cpu_generation,
-- string_agg(distinct case
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''))
-- -- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' Embedded ', 2), ' ', 1)), 4, 2)
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 2)), 2, 4)
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'AMD Ryzen ', 2), ' ', 2)), 3, 2)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Gold ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium Gold ', 2), ' CPU ', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium Silver ') >= 1)) then trim(both ' ' from split_part(split_part(model, 'Pentium Silver ', 2), ' CPU ', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1))
-- then (case when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
-- then left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
-- else right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
-- end)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1)) then right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron ', 2), ' CPU', 1)), 4)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1))
-- then
-- (case
-- -- Check if Intel, Core, and m3, m5, m7, then the first number after the - is supposed to be the SKU
-- when ((strpos(lower(model), ' m3-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- when ((strpos(lower(model), ' m5-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- when ((strpos(lower(model), ' m7-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- -- Check if Intel, Core, and i3, i5, i7, i9, then the first number after the - is supposed to be the SKU
-- when ((strpos(lower(model), ' i3-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- when ((strpos(lower(model), ' i5-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- when ((strpos(lower(model), ' i7-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- when ((strpos(lower(model), ' i9-') > 1) and (left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),1) ~ '^\d+(\.\d+)?$'))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2)
-- -- Check if Intel, Core and contains 'th Gen' to figure out generation
-- when (strpos(lower(model), 'th gen ') > 1)
-- then right(trim(both ' ' from split_part(model, '-', 2)), 3)
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') > 1))
-- -- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 )
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),3, 2)
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') <= 0))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),3, 3)
-- when ((cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) >= 20))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2, 3)
-- end)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, 'Ultra') > 1))
-- then trim(both ' ' from split_part(model, ' ', 5))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1) and (strpos(model, ' CPU @ ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' CPU @ ', 1), ' ', 4)), 3, 2)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon CPU ') > 1) and (strpos(model, ' @ ') > 1)) then right(trim(both ' ' from split_part(split_part(model, ' @ ', 1), 'Intel Xeon CPU ', 2)), 2)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' CPU ', 2), '-', 2)), 3, 2)
-- end, ',') as cpu_sku,
string_agg(distinct
case
when (cpu_manufacturer = 'AMD') then
(case
when ((strpos(model, 'Ryzen Embedded ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' Embedded ', 2), ' ', 1)), 4, 2)
when ((strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'Ryzen ', 2), ' PRO ', 2)), 2, 4)
when ((strpos(model, 'Ryzen ') > 1) and (strpos(model, ' PRO ') <= 1)) then substr(trim(both ' ' from split_part(split_part(model, 'AMD Ryzen ', 2), ' ', 2)), 3, 2)
when ((strpos(model, 'Athlon II ') > 1)) then trim(both ' ' from split_part(split_part(model, ' Athlon II ', 2), ' ', 2))
when ((strpos(model, 'Athlon ') > 1) and (strpos(model, 'Athlon II ') <= 1)) then trim(both ' ' from split_part(split_part(model, ' Athlon ', 2), ' ', 2))
else null
end)
when (cpu_manufacturer = 'Qualcomm')
then null
when (cpu_manufacturer = 'Intel')
then
(case
when (strpos(model, ' Atom ') > 1)
then null
when (strpos(model, ' Celeron ') > 1)
then
(case
when (strpos(model, 'Celeron CPU ') > 1)
then
(case
when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
then left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
else right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
end)
when ((strpos(model, ' Celeron ') > 1) and (strpos(model, 'CPU @ ') > 1))
then split_part(model, ' ', 3)
when ((strpos(model, ' Celeron ') > 1) and (strpos(model, ' @ ') > 1))
then split_part(model, ' ', 3)
else null
end)
when (strpos(model, ' Core ') > 1)
then
(case
when (strpos(model, ' Ultra ') > 1)
then split_part(model, ' ', 5)
when (strpos(model, 'th Gen ') > 1) and (strpos(model, '-') > 1)
then split_part(split_part(model, '-', 2), ' ', 1)
when (strpos(model, 'th Gen ') <= 1) and (strpos(model, '-') > 1)
then split_part(split_part(model, '-', 2), ' ', 1)
else null
end)
when (strpos(model, ' Core2 ') > 1)
then
(case
when (strpos(model, ' Duo ') > 1)
then split_part(model, ' ', 5)
else null
end)
when (strpos(model, ' Pentium ') > 1)
then
(case
when ((strpos(model, ' Gold ') > 1))
then trim(both ' ' from split_part(split_part(model, ' Gold ', 2), ' CPU ', 1))
when ((strpos(model, ' Silver ') > 1))
then trim(both ' ' from split_part(split_part(model, ' Silver ', 2), ' CPU ', 1))
when ((strpos(model, 'Pentium ') >= 1))
then trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1))
else null
end)
when (strpos(model, ' Xeon ') > 1)
then
(case
when (strpos(model, ' Platinum ') > 1) or (strpos(model, ' Gold ') > 1) or (strpos(model, ' Silver ') > 1) or (strpos(model, ' Bronze ') > 1)
then substr(split_part(model, ' ', 4), 3, 2)
when ((strpos(model, 'Xeon CPU ') > 1) and (strpos(model, ' @ ') > 1))
then right(trim(both ' ' from split_part(split_part(model, ' @ ', 1), 'Intel Xeon CPU ', 2)), 2)
-- when (strpos(model, ' CPU @ ') > 1)
-- then substring(split_part(split_part(model, ' CPU @ ', 1), ' ', 4), 3, 2)
when (strpos(model, ' CPU @ ') > 1)
then split_part(split_part(model, ' CPU @ ', 1), '-', 2)
when ((strpos(model, 'Xeon ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' CPU ', 2), '-', 2)), 3, 2)
else
null
end)
when (strpos(model, ' Processor ') > 1)
then null
else
null
end)
else
null
end, ',') as cpu_sku,
-- string_agg(distinct case
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', ''))
-- -- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then (replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', ''))
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen Embedded ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, ' Embedded ', 2), ' ', 1)), 6, 1)
-- when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then substr(trim(both ' ' from split_part(split_part(model, 'AMD Ryzen ', 2), ' ', 2)), 5, 1)
-- -- when (cpu_manufacturer = 'AMD') then (trim(both ' ' from split_part(split_part(model, 'AMD ', 2), ' ', 1)))
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
-- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Celeron CPU ') > 1))
-- then (case when ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) >= 48 and ascii(left(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)) <= 57
-- then right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 1)
-- -- else right(trim(both ' ' from split_part(split_part(model, 'Intel Celeron CPU ', 2), ' @ ', 1)), 4)
-- end)
-- when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1) and (strpos(model, '-') > 1))
-- then
-- (case
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') > 1))
-- then right(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)), 2)
-- when (cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) <= 11 and (strpos(model, 'G') <= 0))
-- then right(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)), 1)
-- when ((cast(left(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)),2) as int) >= 20))
-- then substr(trim(both ' ' from split_part(split_part(model, '-', 2), ' ', 1)), 5, 2)
-- end)
-- -- when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
-- end, ',') as cpu_suffix,
string_agg(distinct case
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Opteron ') > 1)) then 'AMD Opteron ' || (trim(both ' ' from split_part(model, 'AMD Opteron Processor ', 2)))
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Turion ') > 1)) then 'AMD Turion ' || replace(trim(both ' ' from split_part(model, 'AMD Turion ', 2)), 'Dual-Core ', '')
when (cpu_manufacturer = 'AMD' and (strpos(model, 'Ryzen ') > 1)) then 'AMD Ryzen ' || replace(trim(both ' ' from split_part(model, 'AMD Ryzen ', 2)), 'Eight-Core Processor', '')
when (cpu_manufacturer = 'AMD') then (trim(both ' ' from 'AMD ' || split_part(split_part(model, 'AMD ', 2), ' ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Pentium ') >= 1)) then 'Intel Pentium ' || trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Atom ') > 1)) then 'Intel Atom ' || trim(both ' ' from split_part(split_part(model, ' CPU', 2), '@', 1))
when (cpu_manufacturer = 'Intel' and (strpos(model, ' Core ') > 1)) then (trim(both ' ' from split_part(model, ' CPU ', 1)))
when (cpu_manufacturer = 'Intel' and (strpos(model, 'Xeon ') > 1)) then 'Intel Xeon ' || (trim(both ' ' from split_part(split_part(model, ' CPU ', 2), ' @', 1)))
end, ',') as cpu_manu_class_model_number,
count(*) as node_cpu_count,
round(avg(speed_mhz)::numeric,0) as node_per_cpu_mhz,
round(avg(cores)::numeric,0) as node_per_cpu_cores,
sum(cores) as node_cpu_cores,
round(sum(cores*speed_mhz)::numeric,0) as total_cpu_mhz
from (
select
h.hardware_id,
replace(replace(replace(h.model, '(R)', ''), '(TM)', ''), '(tm)', '') as model,
case
when split_part(replace(replace(replace(h.model, '(R)', ''), '(TM)', ''), '(tm)', ''), ' ', 1) ilike 'Pentium' then 'Intel'
when strpos(model, 'Intel') > 1 then 'Intel'
when strpos(model, 'AMD') > 1 then 'AMD'
else split_part(replace(replace(replace(h.model, '(R)', ''), '(TM)', ''), '(tm)', ''), ' ', 1)
end as cpu_manufacturer,
h.bitwidth,
h.cores as cores,
h.speed_mhz
from
tnt_config.node_hardware_cpus h
) h
group by
hardware_id
),
-- node_ram1 as (
-- with
-- latest_sum_report as (
-- select
-- sumrpt.node_id as node_id,
-- max(sumrpt.start_date) as start_date,
-- max(sumrpt.end_date) as end_date
-- from
-- tnt_audit.summaryreports sumrpt
-- group by
-- sumrpt.node_id
-- )
-- select
-- sumrpts.node_id as node_id,
-- round((avg(sumrpts.mem_alloc)/1048576)::numeric, 3) as node_ram_allocated_gb
-- from
-- tnt_audit.summaryreports sumrpts, latest_sum_report lsr
-- where
-- sumrpts.node_id = lsr.node_id
-- and sumrpts.start_date = lsr.start_date
-- and sumrpts.end_date = lsr.end_date
-- and sumrpts.end_date between lsr.start_date and lsr.end_date
-- group by
-- sumrpts.node_id
-- ),
-- node_ram2 as (
-- select
-- sumrpts.node_id as node_id,
-- round((avg(sumrpts.mem_alloc)/1048576)::numeric, 3) as node_ram_allocated_gb
-- from
-- tnt_audit.summaryreports sumrpts
-- join tnt_config.node nde on (nde.id = sumrpts.node_id and nde.delete_time is null)
-- where
-- -- sumrpts.node_id = nde.id
-- -- and sumrpts.start_date = nde.last_report_seen_start_date
-- -- and sumrpts.end_date = nde.last_report_seen_end_date
-- -- and sumrpts.end_date between nde.last_report_seen_start_date and nde.last_report_seen_end_date
-- (sumrpts.end_date >= nde.last_report_seen_end_date)
-- group by
-- sumrpts.node_id
-- ),
node_ram as (
select
sumrpts.node_id as node_id,
round((avg(sumrpts.mem_alloc)/1048576)::numeric, 3) as node_ram_allocated_gb
from
tnt_audit.summaryreports sumrpts,
( select
sumrpt.node_id as node_id,
max(sumrpt.start_date) as start_date,
max(sumrpt.end_date) as end_date
from
tnt_audit.summaryreports sumrpt
group by
sumrpt.node_id
) lsr
where
sumrpts.node_id = lsr.node_id
and sumrpts.start_date = lsr.start_date
and sumrpts.end_date = lsr.end_date
and sumrpts.end_date between lsr.start_date and lsr.end_date
group by
sumrpts.node_id
),
-- node_disk1 as (
-- with
-- last_node_disk as (
-- select
-- part_stats.node_id as node_id,
-- max(part_stats.start_date) as start_date,
-- max(part_stats.end_date) as end_date
-- from
-- tnt_audit.summaryreport_partition_stats part_stats
-- group by
-- part_stats.node_id
-- ),
-- part_stats as (
-- select
-- part_stats.node_id as node_id,
-- part_stats.disk_id as node_physical_disk_id,
-- string_agg(distinct part_stats.name, ',') as node_disk_names,
-- sum(part_stats.size) as node_per_disk_size_kb,
-- sum(part_stats.avg_used) as node_per_disk_used_kb
-- from
-- tnt_audit.summaryreport_partition_stats part_stats, last_node_disk
-- where
-- part_stats.node_id = last_node_disk.node_id
-- and part_stats.start_date = last_node_disk.start_date
-- and part_stats.end_date = last_node_disk.end_date
-- and part_stats.end_date between last_node_disk.start_date and last_node_disk.end_date
-- and part_stats.local_type ilike 'local'
-- group by
-- part_stats.node_id, part_stats.disk_id
-- )
-- select
-- part_stats.node_id as node_id,
-- round(max(part_stats.node_per_disk_size_kb)::numeric/1048576.0, 0) as node_max_disk_size_gb,
-- round(max(part_stats.node_per_disk_used_kb)::numeric/1048576.0, 0) as node_max_disk_used_gb,
-- round(sum(part_stats.node_per_disk_size_kb)::numeric/1048576, 0) as node_total_disk_size_gb,
-- round(sum(part_stats.node_per_disk_used_kb)::numeric/1048576, 0) as node_total_disk_used_gb
-- from
-- part_stats
-- group by
-- part_stats.node_id
-- ),
-- node_disk2 as (
-- select
-- part_stats.node_id as node_id,
-- round(max(part_stats.node_per_disk_size_kb)::numeric/1048576.0, 0) as node_max_disk_size_gb,
-- round(max(part_stats.node_per_disk_used_kb)::numeric/1048576.0, 0) as node_max_disk_used_gb,
-- round(sum(part_stats.node_per_disk_size_kb)::numeric/1048576, 0) as node_total_disk_size_gb,
-- round(sum(part_stats.node_per_disk_used_kb)::numeric/1048576, 0) as node_total_disk_used_gb
-- from (
-- select
-- part_stats.node_id as node_id,
-- part_stats.disk_id as node_physical_disk_id,
-- string_agg(distinct part_stats.name, ',') as node_disk_names,
-- sum(part_stats.size) as node_per_disk_size_kb,
-- sum(part_stats.avg_used) as node_per_disk_used_kb
-- from
-- tnt_audit.summaryreport_partition_stats part_stats
-- join tnt_config.node nde on (nde.id = part_stats.node_id and nde.delete_time is null)
-- where
-- part_stats.node_id = nde.id
-- and part_stats.start_date = nde.last_report_seen_start_date
-- and part_stats.end_date = nde.last_report_seen_end_date
-- and part_stats.end_date between nde.last_report_seen_start_date and nde.last_report_seen_end_date
-- and part_stats.local_type ilike 'local'
-- group by
-- part_stats.node_id, part_stats.disk_id
-- ) part_stats
-- group by
-- part_stats.node_id
-- ),
-- node_disk1 as (
-- select
-- part_stats.node_id as node_id,
-- round(max(part_stats.node_per_disk_size_kb)::numeric/1048576.0, 0) as node_max_disk_size_gb,
-- round(max(part_stats.node_per_disk_used_kb)::numeric/1048576.0, 0) as node_max_disk_used_gb,
-- round(sum(part_stats.node_per_disk_size_kb)::numeric/1048576, 0) as node_total_disk_size_gb,
-- round(sum(part_stats.node_per_disk_used_kb)::numeric/1048576, 0) as node_total_disk_used_gb
-- from (
-- select
-- part_stats.node_id as node_id,
-- part_stats.disk_id as node_physical_disk_id,
-- string_agg(distinct part_stats.name, ',') as node_disk_names,
-- sum(part_stats.size) as node_per_disk_size_kb,
-- sum(part_stats.avg_used) as node_per_disk_used_kb
-- from
-- tnt_audit.summaryreport_partition_stats part_stats,
-- ( select
-- part_stats.node_id as node_id,
-- max(part_stats.start_date) as start_date,
-- max(part_stats.end_date) as end_date
-- from
-- tnt_audit.summaryreport_partition_stats part_stats
-- -- where
-- -- part_stats.node_id = 13047
-- group by
-- part_stats.node_id
-- ) last_part_stats
-- where
-- part_stats.node_id = last_part_stats.node_id
-- and part_stats.start_date = last_part_stats.start_date
-- and part_stats.end_date = last_part_stats.end_date
-- and part_stats.end_date between last_part_stats.start_date and last_part_stats.end_date
-- and part_stats.local_type ilike 'local'
-- -- and part_stats.node_id = 13047
-- group by
-- part_stats.node_id, part_stats.disk_id
-- ) part_stats
-- group by
-- part_stats.node_id
-- ),
node_disk as (
select
disk_stats.node_id as node_id,
-- round(max(disk_stats.node_per_disk_size_kb)::numeric/1048576.0, 0) as node_max_disk_size_gb,
-- round(max(disk_stats.node_per_disk_used_kb)::numeric/1048576.0, 0) as node_max_disk_used_gb,
-- round(sum(disk_stats.node_per_disk_size_kb)::numeric/1048576.0, 0) as node_total_disk_size_gb,
-- round(sum(disk_stats.node_per_disk_used_kb)::numeric/1048576.0, 0) as node_total_disk_used_gb
round(disk_stats.node_total_disk_size_kb::numeric/1048576.0, 0) as node_total_disk_size_gb,
round(disk_stats.node_total_disk_used_kb::numeric/1048576.0, 0) as node_total_disk_used_gb,
round((disk_stats.node_total_disk_size_kb - disk_stats.node_total_disk_used_kb)::numeric/1048576.0, 0) as node_total_disk_free_gb,
round(disk_stats.node_disk0_c_size_kb::numeric/1048576.0, 0) as node_disk0_c_size_gb,
round(disk_stats.node_disk0_c_used_kb::numeric/1048576.0, 0) as node_disk0_c_used_gb,
round((disk_stats.node_disk0_c_size_kb - disk_stats.node_disk0_c_used_kb)::numeric/1048576.0, 0) as node_disk0_c_free_gb
from (
select
disk_stats.node_id as node_id,
-- disk_stats.disk_id as node_physical_disk_id,
string_agg(distinct disk_stats.name, ',') as node_disk_names,
sum(disk_stats.size) as node_total_disk_size_kb,
sum(disk_stats.avg_used) as node_total_disk_used_kb,
sum(case when disk_stats.name ilike '%disk0 ( C:%' then disk_stats.size end) as node_disk0_c_size_kb,
sum(case when disk_stats.name ilike '%disk0 ( C:%' then disk_stats.avg_used end) as node_disk0_c_used_kb
from
-- tnt_audit.summaryreport_partition_stats part_stats,
tnt_audit.summaryreport_disk_stats disk_stats,
( select
disk_stats.node_id as node_id,
max(disk_stats.start_date) as start_date,
max(disk_stats.end_date) as end_date
from
-- tnt_audit.summaryreport_partition_stats disk_stats
tnt_audit.summaryreport_disk_stats disk_stats
-- where
-- disk_stats.node_id = 13047
group by
disk_stats.node_id
) last_disk_stats
where
disk_stats.node_id = last_disk_stats.node_id
and disk_stats.start_date = last_disk_stats.start_date
and disk_stats.end_date = last_disk_stats.end_date
and disk_stats.end_date between last_disk_stats.start_date and last_disk_stats.end_date
and disk_stats.type ilike 'LOCAL'
-- and disk_stats.node_id = 13047
group by
disk_stats.node_id--, disk_stats.disk_id
) disk_stats
-- group by
-- disk_stats.node_id
),
node_health_stats as (
select
hstats.node_id,
hstats.sec_secure_boot
from
tnt_audit.summaryreport_machine_health_stats hstats,
-- lwl_rollup.sr_machine_health_stats_da hstats,
( select
node_id as node_id,
max(start_date) as start_date,
max(end_date) as end_date
from
tnt_audit.summaryreport_machine_health_stats
-- lwl_rollup.sr_machine_health_stats_da hstats
group by
node_id
) last_health_stats
where
hstats.node_id = last_health_stats.node_id
and hstats.start_date = last_health_stats.start_date
and hstats.end_date = last_health_stats.end_date
and hstats.end_date between last_health_stats.start_date and last_health_stats.end_date
),
node_gpus as (
select
hardware_id,
string_agg(model, ', ') as node_gpu_model,
string_agg(model, ',') as node_discrete_gpu_models,
count(*) as node_discrete_gpu_count,
string_agg(format('%s', speedmhz), ', ') as node_gpu_speed_mhz,
-- string_agg(format('%s', colordepth), ', ') as node_gpu_color_depth,
max(colordepth) as node_gpu_color_depth,
count(*) as node_gpu_count
from
tnt_config.node_hardware_gpus
where
is_real = true
group by
hardware_id
),
node_displays as (
select
hardware_id,
string_agg(dimensions, ', ') as node_display_dimensions,
max(cast(split_part(dimensions, 'x', 2) as int)) as max_display_res_y,
string_agg(coalesce(cast(details->'sizeInches' as text), 'N/A'), ',') as node_display_size_in_inches,
max((cast(details->'sizeInches' as int))) as max_display_size_in_inches,
count(*) as node_display_count
from
tnt_config.node_hardware_displays
group by
hardware_id
),
-- Todo: Add
-- Disk encryption providers,
-- Cloud drives in use (OneDrive, Google Drive, AWS Drive),
-- Top 10 Apps by user count,
-- Top 10 Apps by CPU, RAM, Disk Space
base_sql as (
select
pn.id as node_id,
pn.name as machine_name,
nde.fqmn as machine_fqmn,
nde.lastcontact::timestamptz at time zone ? as machine_last_contact,
-- Machine hardware make, model, and chassis type
nh.machine_make as machine_make,
nh.machine_model as machine_model,
nde.hwtype as machine_chassis_type,
-- OS
os.version as node_os_version,
replace(replace(replace(replace(replace(replace(replace(replace(os.description, ' , ', ', '), 'Service Pack ', 'SP'), '(R)', ''), '(TM)', ''), '®', ''), 'â„¢', ''), '®', ''), '™', '') as node_os_desc,
os.buildversion as os_build_version,
-- CPU
nodecpu.node_cpu_model as node_cpu_model,
nodecpu.node_cpu_short_models as node_cpu_short_models,
round((nodecpu.node_per_cpu_mhz/1000.0)::numeric, 3) as node_cpu_speed_ghz,
trim(both ' ' from nodecpu.cpu_brand) as cpu_brand,
trim(both ' ' from nodecpu.cpu_sub_brand) as cpu_sub_brand,
trim(both ' ' from nodecpu.cpu_sub_brand_modifier) as cpu_sub_brand_modifier,
trim(both ' ' from nodecpu.cpu_model_number) as cpu_model_number,
nodecpu.cpu_sku_level as cpu_sku_level,
-- trim(both ' ' from nodecpu.cpu_generation) as cpu_generation,
nodecpu.cpu_generation as cpu_generation,
trim(both ' ' from nodecpu.cpu_sku) as cpu_sku,
nodecpu.node_per_cpu_cores,
nodecpu.node_cpu_cores,
nodecpu.node_cpu_bitwidth,
-- RAM
noderam.node_ram_allocated_gb as node_ram_allocated_gb,
nh.ram_alloc_gb,
-- Disk storage
nodedisk.node_disk0_c_size_gb as node_disk0_c_size_gb,
nodedisk.node_disk0_c_used_gb as node_disk0_c_used_gb,
nodedisk.node_disk0_c_free_gb as node_disk0_c_free_gb,
nodedisk.node_total_disk_size_gb as node_total_disk_size_gb,
nodedisk.node_total_disk_used_gb as node_total_disk_used_gb,
nodedisk.node_total_disk_free_gb as node_total_disk_free_gb,
-- GPU
nodegpu.node_gpu_model as node_gpu_model,
nodegpu.node_discrete_gpu_models as node_discrete_gpu_models,
-- nodegpu.node_gpu_speed_mhz as node_gpu_speed_mhz,
nodegpu.node_gpu_count as node_gpu_count,
nodegpu.node_discrete_gpu_count as node_discrete_gpu_count,
nodegpu.node_gpu_color_depth,
os.dx12orwddm2 as os_dx12orwddm2,
-- Display
nodedis.node_display_dimensions as node_display_dimensions,
nodedis.max_display_res_y as max_display_res_y,
nodedis.node_display_size_in_inches,
nodedis.max_display_size_in_inches,
nodedis.node_display_count as node_display_count,
-- BIOS & TPM
case when nh.bios_uefi_capable is null then null else case when nh.bios_uefi_capable = 'Y' then true else false end end as machine_bios_uefi_capable,
case when nh.bios_uefi_enabled is null then null else case when nh.bios_uefi_enabled = 'Y' then true else false end end as machine_bios_uefi_enabled,
case when nodehealth.sec_secure_boot is null then null else nodehealth.sec_secure_boot end as machine_secure_boot,
nh.tpm_specification as machine_tpm_specifications,
case when nh.tpm_specification is null then null else case when strpos(nh.tpm_specification, '2.') > 0 then true else false end end as machine_tpm_specification,
-- Windows 11 compatibility
-- initcap(nde.win11_inplace_upgrade_compatibility) as win11_inplace_upgrade_compatibility,
-- initcap(nde.win11_image_upgrade_compatibility) as win11_image_upgrade_compatibility
-- Machine location
loc_stats.city || ', ' || loc_stats.state || ', ' || loc_stats.country as machine_location_city_state_country,
loc_stats.country as machine_location_country,
loc_stats.state as machine_location_state,
loc_stats.city as machine_location_city
from
tnt_config.policynode pn
join tnt_config.node nde on (nde.id = pn.id)
left outer join tnt_config.os os on (os.id = nde.os_id)
left outer join node_hardware nh on (node_hardware_id = nde.hardware_id)
left outer join node_cpus nodecpu on (nodecpu.hardware_id = nde.hardware_id)
left outer join node_ram noderam on (noderam.node_id = pn.id)
left outer join node_disk nodedisk on (nodedisk.node_id = pn.id)
left outer join node_gpus nodegpu on (nodegpu.hardware_id = nde.hardware_id)
left outer join node_displays nodedis on (nodedis.hardware_id = nde.hardware_id)
left outer join node_health_stats nodehealth on (nodehealth.node_id = pn.id)
left outer join (
select
loc_stats.node_id as node_id,
first(loc_stats.country) as country,
first(loc_stats.state) as state,
first(loc_stats.city) as city
from
tnt_audit.summaryreport_machine_location_stats loc_stats,
-- lwl_rollup.sr_machine_location_stats_da loc_stats,
( select
loc_stats.node_id as node_id,
max(loc_stats.start_date) as start_date,
max(loc_stats.end_date) as end_date
from
tnt_audit.summaryreport_machine_location_stats loc_stats
-- lwl_rollup.sr_machine_location_stats_da loc_stats
group by
loc_stats.node_id
) latest_loc_stats
where
loc_stats.node_id = latest_loc_stats.node_id
and loc_stats.start_date = latest_loc_stats.start_date
and loc_stats.end_date = latest_loc_stats.end_date
and loc_stats.end_date between latest_loc_stats.start_date and latest_loc_stats.end_date
group by
loc_stats.node_id
) loc_stats on (loc_stats.node_id = pn.id)
where
(pn.delete_time is null) -- Machine has not been deleted
and (nde.delete_time is null) -- Machine has not been deleted
and (nde.ihostversion is not null) -- Machine has a CID Key installed
and (nde.certificate is not null) -- Machine has a valid certificate
and (pn.system_object is false) -- Machine is not a system object like Stratusphere Hub, DB or Station
and (pn.dirtystatusid is null or pn.dirtystatusid <> 3) -- Machine does not have a dirty status
and (pn.type <= 2) -- Machines are either servers (1) or desktops (2)
-- and (os.name = 'Windows') -- Machines that currently run Microsoft Windows
-- and (os.description not ilike '%Windows 11%' -- Machine is already running Windows 11
-- and os.description not ilike '%Windows Server %') -- Machine is not already running Windows Server
-- and (nde.vmtype is null) -- Machine is a physical machine to be considered for upgrade to Win10
-- Machine group filter
and ('%' = ? or pn.id in
(select ngn.nodeid from tnt_config.policynode pn, tnt_config.nodegroup_node ngn
where (pn.type = 5) and (pn.id = ngn.nodegroupid) and (pn.name like ?)))
-- Threshold value for machine having called back within last N days
-- and (nde.last_report_seen_end_date >= (current_date - (interval '1 days' * ?))) -- Machine has called back within last 90 days
order by
lower(pn.name) asc nulls last
),
base_with_cpu_support_sql as (
select
s.*,
-- case
-- -- First check for incompatible hardware/software
-- when (node_cpu_speed_ghz < 1.0)
-- or (node_per_cpu_cores < 2)
-- or (ram_alloc_gb < 4.0)
-- or (node_cpu_bitwidth is not null and node_cpu_bitwidth < 64.0)
-- or (node_disk0_c_free_gb < 20.0)
-- or (node_gpu_color_depth is not null and node_gpu_color_depth < 8)
-- or (os_dx12orwddm2 is not null and os_dx12orwddm2 is false)
-- or (max_display_res_y is not null and max_display_res_y < 720)
-- or (max_display_size_in_inches is not null and max_display_size_in_inches < 9)
-- or (machine_bios_uefi_capable is not null and machine_bios_uefi_capable is false)
-- or (machine_secure_boot is not null and machine_secure_boot = 'Not supported')
-- or (machine_tpm_specification is not null and machine_tpm_specification is false)
-- then 'Unsupported'
-- -- Then check for partial/maybe support (when values are NULL)
-- when node_cpu_bitwidth is null
-- or node_gpu_color_depth is null
-- or os_dx12orwddm2 is null
-- or max_display_res_y is null
-- or max_display_size_in_inches is null
-- or machine_bios_uefi_capable is null
-- or machine_secure_boot is null
-- or machine_tpm_specification is null
-- then 'Partial'
-- -- Otherwise it's supported
-- else 'Supported'
-- end as win11_inplace_upgrade_label,
-- case
-- -- First check for incompatible hardware/software
-- when (node_cpu_speed_ghz < 1.0)
-- or (node_per_cpu_cores < 2)
-- or (ram_alloc_gb < 4.0)
-- or (node_cpu_bitwidth is not null and node_cpu_bitwidth < 64.0)
-- or (node_disk0_c_free_gb < 64.0)
-- or (node_gpu_color_depth is not null and node_gpu_color_depth < 8)
-- or (os_dx12orwddm2 is not null and os_dx12orwddm2 is false)
-- or (max_display_res_y is not null and max_display_res_y < 720)
-- or (max_display_size_in_inches is not null and max_display_size_in_inches < 9)
-- or (machine_bios_uefi_capable is not null and machine_bios_uefi_capable is false)
-- or (machine_secure_boot is not null and machine_secure_boot = 'Not supported')
-- or (machine_tpm_specification is not null and machine_tpm_specification is false)
-- then 'Unsupported'
-- -- Then check for partial/maybe support (when values are NULL)
-- when node_cpu_bitwidth is null
-- or node_gpu_color_depth is null
-- or os_dx12orwddm2 is null
-- or max_display_res_y is null
-- or max_display_size_in_inches is null
-- or machine_bios_uefi_capable is null
-- or machine_secure_boot is null
-- or machine_tpm_specification is null
-- then 'Partial'
-- -- Otherwise it's supported
-- else 'Supported'
-- end as win11_image_upgrade_label,
case
when cpu_brand = 'Intel' then
case
-- Intel Atom processors
when cpu_sub_brand = 'Atom' and
(cpu_model_number = 'x6200FE' or cpu_model_number = 'x6211E' or cpu_model_number = 'x6212RE' or
cpu_model_number = 'x6413E' or cpu_model_number = 'x6414RE' or cpu_model_number = 'x6425E' or
cpu_model_number = 'x6425RE' or cpu_model_number = 'x6427FE' or
cpu_model_number = 'x7211E' or cpu_model_number = 'x7213E' or
cpu_model_number = 'x7425E')
then 'Y'
-- Intel Celeron processors
when cpu_sub_brand = 'Celeron' then
case
when cpu_sub_brand_modifier = 'G' and
(cpu_sku = '4900' or cpu_sku = '4920' or cpu_sku = '4930' or cpu_sku = '4932' or
cpu_sku = '4950' or cpu_sku = '5900' or cpu_sku = '5905' or cpu_sku = '5920' or
cpu_sku = '5925' or cpu_sku = '6900')
then 'Y'
when cpu_sub_brand_modifier = 'J' and
(cpu_sku = '4005' or cpu_sku = '4025' or cpu_sku = '4105' or cpu_sku = '4115' or
cpu_sku = '4125' or cpu_sku = '6412' or cpu_sku = '6413')
then 'Y'
when cpu_sub_brand_modifier = 'N' and
(cpu_sku = '4000' or cpu_sku = '4020' or cpu_sku = '4100' or cpu_sku = '4120' or
cpu_sku = '4500' or cpu_sku = '4505' or cpu_sku = '5100' or cpu_sku = '5105' or
cpu_sku = '6210' or cpu_sku = '6211')
then 'Y'
when cpu_sub_brand_modifier is null and
(cpu_sku = '3867' or cpu_sku = '4205' or cpu_sku = '4305' or
cpu_sku = '5205' or cpu_sku = '5205U' or cpu_sku = '5305' or cpu_sku = '5305U' or cpu_sku = '5805' or
cpu_sku = '6305' or cpu_sku = '6600' or cpu_sku = '7300' or cpu_sku = '7305')
then 'Y'
else 'N'
end
-- Intel Core processors
when cpu_sub_brand = 'Core' then
case
when cpu_sub_brand_modifier ilike 'Ultra%'
then 'Y'
when cpu_generation >= 8
then 'Y'
else 'N'
end
-- Intel Pentium processors
when cpu_sub_brand = 'Pentium' then
case
when cpu_sub_brand_modifier = 'Gold' and
(cpu_sku = '4417' or cpu_sku = '4425' or cpu_sku = '5400' or cpu_sku = '5405' or cpu_sku = '6405' or
cpu_sku = '6500' or cpu_sku = '7505' or cpu_sku = 'G5400' or cpu_sku = 'G5400T' or
cpu_sku = 'G5420' or cpu_sku = 'G5420T' or cpu_sku = 'G5500' or cpu_sku = 'G5500T' or
cpu_sku = 'G5600' or cpu_sku = 'G5600T' or cpu_sku = 'G5620' or cpu_sku = 'G6400' or
cpu_sku = 'G6400E' or cpu_sku = 'G6400T' or cpu_sku = 'G6400TE' or cpu_sku = 'G6405' or
cpu_sku = 'G6405T' or cpu_sku = 'G6500' or cpu_sku = 'G6500T' or cpu_sku = 'G6505' or
cpu_sku = 'G6505T' or cpu_sku = 'G6600' or cpu_sku = 'G6605' or
cpu_sku = 'G7400' or cpu_sku = 'G7400E' or cpu_sku = 'G7400T' or cpu_sku = 'G7400TE')
then 'Y'
when cpu_sub_brand_modifier = 'Silver' and
(cpu_sku = 'J5005' or cpu_sku = 'J5040' or cpu_sku = 'N5000' or cpu_sku = 'N5030' or
cpu_sku = 'N6000' or cpu_sku = 'N6005')
then 'Y'
when cpu_sub_brand_modifier is null and
(cpu_sku = '6805' or cpu_sku = 'J6426' or cpu_sku = 'N6415')
then 'Y'
else 'N'
end
-- Intel Xeon processors
when cpu_sub_brand = 'Xeon' then
case
when cpu_sub_brand_modifier = 'Bronze' and
(cpu_model_number = '3104' or cpu_model_number = '3106' or
cpu_model_number = '3204' or cpu_model_number = '3206R')
then 'Y'
when cpu_sub_brand_modifier = 'Silver' then -- Fixed the condition
case
when cpu_sku_level = 4 then
case
when cpu_generation = 1 and
(cpu_sku = '08' or cpu_sku = '09' or cpu_sku = '10' or cpu_sku = '12' or
cpu_sku = '14' or cpu_sku = '16')
then 'Y'
when cpu_generation = 2 and
(cpu_sku = '08' or cpu_sku = '09' or cpu_sku = '10' or cpu_sku = '14' or
cpu_sku = '15' or cpu_sku = '16')
then 'Y'
when cpu_generation = 3 and
(cpu_sku = '09' or cpu_sku = '10' or cpu_sku = '14' or cpu_sku = '16')
then 'Y'
when cpu_generation = 4 and
(cpu_sku = '10' or cpu_sku = '16')
then 'Y'
else 'N'
end
else
'N'
end
when cpu_sub_brand_modifier = 'Gold' then -- Fixed the condition
case
when cpu_sku_level = 5 then
case
when cpu_generation = 1 and
(cpu_sku = '15' or cpu_sku = '18' or cpu_sku = '19' or cpu_sku = '20' or
cpu_sku = '22')
then 'Y'
when cpu_generation = 2 and
(cpu_sku = '15' or cpu_sku = '17' or cpu_sku = '18' or cpu_sku = '20' or
cpu_sku = '22')
then 'Y'
when cpu_generation = 3 and
(cpu_sku = '15' or cpu_sku = '17' or cpu_sku = '18' or cpu_sku = '20' or
cpu_sku = '22')
then 'Y'
when cpu_generation = 4 and
(cpu_sku = '15' or cpu_sku = '16' or cpu_sku = '18' or cpu_sku = '20')
then 'Y'
else 'N'
end
when cpu_sku_level = 6 then
case
when cpu_generation = 1 and
(cpu_sku = '26' or cpu_sku = '28' or cpu_sku = '30' or cpu_sku = '32' or
cpu_sku = '34' or cpu_sku = '36' or cpu_sku = '38' or cpu_sku = '40' or
cpu_sku = '42' or cpu_sku = '44' or cpu_sku = '46' or cpu_sku = '48' or
cpu_sku = '50' or cpu_sku = '52' or cpu_sku = '54')
then 'Y'
when cpu_generation = 2 and
(cpu_sku = '08' or cpu_sku = '09' or cpu_sku = '10' or cpu_sku = '12' or
cpu_sku = '22' or cpu_sku = '26' or cpu_sku = '30' or cpu_sku = '34' or
cpu_sku = '38' or cpu_sku = '40' or cpu_sku = '42' or cpu_sku = '44' or
cpu_sku = '46' or cpu_sku = '48' or cpu_sku = '50' or cpu_sku = '52' or
cpu_sku = '54' or cpu_sku = '56' or cpu_sku = '58' or cpu_sku = '62')
then 'Y'
when cpu_generation = 3 and
(cpu_sku = '12' or cpu_sku = '14' or cpu_sku = '26' or cpu_sku = '30' or
cpu_sku = '34' or cpu_sku = '36' or cpu_sku = '38' or cpu_sku = '42' or
cpu_sku = '46' or cpu_sku = '48' or cpu_sku = '54')
then 'Y'
when cpu_generation = 4 and
(cpu_sku = '16' or cpu_sku = '18' or cpu_sku = '30' or cpu_sku = '38' or
cpu_sku = '42')
then 'Y'
else 'N'
end
else
'N'
end
when cpu_sub_brand_modifier = 'Platinum' then
case
when cpu_sku_level = 8 then
case
when cpu_generation = 1 and
(cpu_sku = '53' or cpu_sku = '56' or cpu_sku = '58' or cpu_sku = '60' or
cpu_sku = '64' or cpu_sku = '68' or cpu_sku = '70' or cpu_sku = '71' or
cpu_sku = '76' or cpu_sku = '80')
then 'Y'
when cpu_generation = 2 and
(cpu_sku = '53' or cpu_sku = '56' or cpu_sku = '60' or cpu_sku = '68' or
cpu_sku = '70' or cpu_sku = '72' or cpu_sku = '76' or cpu_sku = '80')
then 'Y'
when cpu_generation = 3 and
(cpu_sku = '51' or cpu_sku = '52' or cpu_sku = '58' or cpu_sku = '60' or
cpu_sku = '62' or cpu_sku = '68' or cpu_sku = '80')
then 'Y'
when cpu_generation = 4 and
(cpu_sku = '52' or cpu_sku = '60' or cpu_sku = '68' or cpu_sku = '70' or
cpu_sku = '80' or cpu_sku = '90')
then 'Y'
else 'N'
end
when cpu_sku_level = 9 then
case
when cpu_generation = 2 and
(cpu_sku = '21' or cpu_sku = '22' or cpu_sku = '42' or cpu_sku = '82')
then 'Y'
else 'N'
end
else 'N'
end
when cpu_sub_brand_modifier = 'D' and
(cpu_model_number = 'D-1702' or cpu_model_number = 'D-1712TR' or cpu_model_number = 'D-1713NT' or
cpu_model_number = 'D-1713NTE' or cpu_model_number = 'D-1714' or cpu_model_number = 'D-1715TER' or
cpu_model_number = 'D-1718T' or cpu_model_number = 'D-1722NE' or cpu_model_number = 'D-1726' or
cpu_model_number = 'D-1732TE' or cpu_model_number = 'D-1733NT' or cpu_model_number = 'D-1735TR' or
cpu_model_number = 'D-1736' or cpu_model_number = 'D-1736NT' or cpu_model_number = 'D-1739' or
cpu_model_number = 'D-1746TER' or cpu_model_number = 'D-1747NTE' or cpu_model_number = 'D-1748TE' or
cpu_model_number = 'D-1749NT' or cpu_model_number = 'D-2712T' or cpu_model_number = 'D-2733NT' or
cpu_model_number = 'D-2738' or cpu_model_number = 'D-2752NTE' or cpu_model_number = 'D-2752TER' or
cpu_model_number = 'D-2753NT' or cpu_model_number = 'D-2766NT' or cpu_model_number = 'D-2775TE' or
cpu_model_number = 'D-2776NT' or cpu_model_number = 'D-2779' or cpu_model_number = 'D-2786NTE' or
cpu_model_number = 'D-2795NT' or cpu_model_number = 'D-2796NT' or cpu_model_number = 'D-2796TE' or
cpu_model_number = 'D-2798NT' or cpu_model_number = 'D-2799')
then 'Y'
when cpu_sub_brand_modifier = 'E' and
(cpu_model_number = 'E-2104G' or cpu_model_number = 'E-2124' or cpu_model_number = 'E-2124G' or
cpu_model_number = 'E-2126G' or cpu_model_number = 'E-2134' or cpu_model_number = 'E-2136' or
cpu_model_number = 'E-2144G' or cpu_model_number = 'E-2146G' or cpu_model_number = 'E-2174G' or
cpu_model_number = 'E-2176G' or cpu_model_number = 'E-2176M' or cpu_model_number = 'E-2186G' or
cpu_model_number = 'E-2186M' or cpu_model_number = 'E-2224' or cpu_model_number = 'E-2224G' or
cpu_model_number = 'E-2226G' or cpu_model_number = 'E-2226GE' or cpu_model_number = 'E-2234' or
cpu_model_number = 'E-2236' or cpu_model_number = 'E-2244G' or cpu_model_number = 'E-2246G' or
cpu_model_number = 'E-2254ME' or cpu_model_number = 'E-2254ML' or cpu_model_number = 'E-2274G' or
cpu_model_number = 'E-2276G' or cpu_model_number = 'E-2276M' or cpu_model_number = 'E-2276ME' or
cpu_model_number = 'E-2276ML' or cpu_model_number = 'E-2278G' or cpu_model_number = 'E-2278GE' or
cpu_model_number = 'E-2278GEL' or cpu_model_number = 'E-2286G' or cpu_model_number = 'E-2286M' or
cpu_model_number = 'E-2288G')
then 'Y'
when cpu_sub_brand_modifier = 'W' and
(cpu_model_number = 'W-10855M' or cpu_model_number = 'W-10885M' or cpu_model_number = 'W-11855M' or
cpu_model_number = 'W-11955M' or cpu_model_number = 'W-1250' or cpu_model_number = 'W-1250E' or
cpu_model_number = 'W-1250P' or cpu_model_number = 'W-1250TE' or cpu_model_number = 'W-1270' or
cpu_model_number = 'W-1270E' or cpu_model_number = 'W-1270P' or cpu_model_number = 'W-1270TE' or
cpu_model_number = 'W-1290' or cpu_model_number = 'W-1290E' or cpu_model_number = 'W-1290P' or
cpu_model_number = 'W-1290T' or cpu_model_number = 'W-1290TE' or cpu_model_number = 'W-2102' or
cpu_model_number = 'W-2104' or cpu_model_number = 'W-2123' or cpu_model_number = 'W-2125' or
cpu_model_number = 'W-2133' or cpu_model_number = 'W-2135' or cpu_model_number = 'W-2145' or
cpu_model_number = 'W-2155' or cpu_model_number = 'W-2175' or cpu_model_number = 'W-2195' or
cpu_model_number = 'W-2223' or cpu_model_number = 'W-2225' or cpu_model_number = 'W-2235' or
cpu_model_number = 'W-2245' or cpu_model_number = 'W-2255' or cpu_model_number = 'W-2265' or
cpu_model_number = 'W-2275' or cpu_model_number = 'W-2295' or cpu_model_number = 'W-3175X' or
cpu_model_number = 'W-3223' or cpu_model_number = 'W-3225' or cpu_model_number = 'W-3235' or
cpu_model_number = 'W-3245' or cpu_model_number = 'W-3245M' or cpu_model_number = 'W-3265' or
cpu_model_number = 'W-3265M' or cpu_model_number = 'W-3275' or cpu_model_number = 'W-3275M' or
cpu_model_number = 'W-3323' or cpu_model_number = 'W-3335' or cpu_model_number = 'W-3345' or
cpu_model_number = 'W-3365' or cpu_model_number = 'W-3375')
then 'Y'
when cpu_sub_brand_modifier = 'W3' and
(cpu_model_number = 'W3-2423' or cpu_model_number = 'W3-2425' or cpu_model_number = 'W3-2435')
then 'Y'
when cpu_sub_brand_modifier = 'W5' and
(cpu_model_number = 'W5-2445' or cpu_model_number = 'W5-2455X' or cpu_model_number = 'W5-2465X' or
cpu_model_number = 'W5-3423' or cpu_model_number = 'W5-3425' or cpu_model_number = 'W5-3433' or
cpu_model_number = 'W5-3435X')
then 'Y'
when cpu_sub_brand_modifier = 'W7' and
(cpu_model_number = ' W7-2475X' or cpu_model_number = 'W7-2495X' or cpu_model_number = 'W7-3445' or
cpu_model_number = 'W7-3465X')
then 'Y'
when cpu_sub_brand_modifier = 'W9' and
(cpu_model_number = 'W9-3475X' or cpu_model_number = 'W9-3495X')
then 'Y'
else 'N'
end -- Xeon's cpu_sub_brand_modifier
else 'N'
end -- Intel's cpu_sub_brand
-- AMD processors
when cpu_brand = 'AMD' then
case
-- AMD Athlon processors
when cpu_sub_brand = 'Athlon' and
(cpu_model_number = '3000G' or cpu_model_number = '300GE' or cpu_model_number = '300U' or cpu_model_number = '320GE' or
cpu_model_number = '7120e' or cpu_model_number = '7120U' or cpu_model_number = '7220e' or cpu_model_number = '7220U' or
cpu_model_number = 'Gold 3150C' or cpu_model_number = 'Gold 3150G' or cpu_model_number = 'Gold 3150GE' or cpu_model_number = 'Gold 3150U' or
cpu_model_number = 'Gold PRO 3125GE' or cpu_model_number = 'Gold PRO 3150G' or cpu_model_number = 'Gold PRO 3150GE' or cpu_model_number = 'Gold PRO 4150GE' or
cpu_model_number = 'PRO 300GE' or cpu_model_number = 'PRO 300U' or cpu_model_number = 'PRO 3045B' or
cpu_model_number = 'Silver 3050C' or cpu_model_number = 'Silver 3050e' or cpu_model_number = 'Silver 3050GE' or cpu_model_number = 'Silver 3050U'
)
then 'Y'
-- AMD EPYC processors
when cpu_sub_brand = 'EPYC' and
(cpu_model_number = '7252' or cpu_model_number = '7262' or cpu_model_number = '7272' or cpu_model_number = '7282' or
cpu_model_number = '7302' or cpu_model_number = '7313' or cpu_model_number = '7343' or cpu_model_number = '7352' or
cpu_model_number = '7402' or cpu_model_number = '7413' or cpu_model_number = '7443' or cpu_model_number = '7452' or cpu_model_number = '7453' or
cpu_model_number = '7502' or cpu_model_number = '7513' or cpu_model_number = '7532' or cpu_model_number = '7542' or cpu_model_number = '7543' or cpu_model_number = '7552' or
cpu_model_number = '7642' or cpu_model_number = '7643' or cpu_model_number = '7662' or cpu_model_number = '7663' or
cpu_model_number = '7702' or cpu_model_number = '7713' or cpu_model_number = '7742' or cpu_model_number = '7763' or
cpu_model_number = '7232P' or cpu_model_number = '72F3' or
cpu_model_number = '7302P' or cpu_model_number = '7313P' or cpu_model_number = '73F3' or
cpu_model_number = '7402P' or cpu_model_number = '7443P' or cpu_model_number = '74F3' or
cpu_model_number = '7502P' or cpu_model_number = '7543P' or cpu_model_number = '75F3' or
cpu_model_number = '7702P' or cpu_model_number = '7713P' or
cpu_model_number = '7F32' or cpu_model_number = '7F52' or cpu_model_number = '7F72' or cpu_model_number = '7H12' or
cpu_model_number = '8024P' or cpu_model_number = '8024PN' or
cpu_model_number = '8124P' or cpu_model_number = '8124PN' or
cpu_model_number = '8224P' or cpu_model_number = '8224PN' or
cpu_model_number = '8324P' or cpu_model_number = '8324PN' or
cpu_model_number = '8434P' or cpu_model_number = '8434PN' or
cpu_model_number = '8534P' or cpu_model_number = '8534PN' or
cpu_model_number = '9124' or
cpu_model_number = '9244' or cpu_model_number = '9254' or
cpu_model_number = '9334' or cpu_model_number = '9354' or
cpu_model_number = '9454' or
cpu_model_number = '9534' or cpu_model_number = '9554' or
cpu_model_number = '9654' or cpu_model_number = '9654' or
cpu_model_number = '9734' or cpu_model_number = '9754' or
cpu_model_number = '9174F' or cpu_model_number = '9184X' or
cpu_model_number = '9274F' or
cpu_model_number = '9354P' or cpu_model_number = '9374F' or cpu_model_number = '9384X' or
cpu_model_number = '9454P' or cpu_model_number = '9474F' or
cpu_model_number = '9554P' or
cpu_model_number = '9654P' or cpu_model_number = '9684X' or
cpu_model_number = '9754S'
)
then 'Y'
-- AMD Ryzen processors
when cpu_sub_brand = 'Ryzen' then
case
when cpu_sub_brand_modifier = '3' and
(cpu_model_number = '3100' or cpu_model_number = '4100' or
cpu_model_number = '2300X' or cpu_model_number = '3200G' or
cpu_model_number = '3200GE' or cpu_model_number = '3200U' or cpu_model_number = '3250C' or
cpu_model_number = '3250U' or cpu_model_number = '3300U' or cpu_model_number = '3350U' or
cpu_model_number = '4300G' or cpu_model_number = '4300GE' or cpu_model_number = '4300U' or
cpu_model_number = '5125C' or
cpu_model_number = '5300G' or cpu_model_number = '5300GE' or cpu_model_number = '5300U' or cpu_model_number = '5380U' or
cpu_model_number = '5400U' or cpu_model_number = '5425C' or cpu_model_number = '5425U' or
cpu_model_number = '7320e' or cpu_model_number = '7320U' or cpu_model_number = '7330U' or cpu_model_number = '7335U' or
cpu_model_number = '7440U' or
cpu_model_number = '8300G'
)
then 'Y'
when cpu_sub_brand_modifier = '3 PRO' and
(cpu_model_number = '3200G' or cpu_model_number = '3200GE' or cpu_model_number = '3300U' or
cpu_model_number = '4350G' or cpu_model_number = '4350GE' or cpu_model_number = '4355G' or cpu_model_number = '4355GE' or
cpu_model_number = '4450U' or
cpu_model_number = '5350G' or cpu_model_number = '5350GE' or cpu_model_number = '5450U' or cpu_model_number = '5475U' or
cpu_model_number = '7330U' or
cpu_model_number = '8300G' or cpu_model_number = '8300GE'
)
then 'Y'
when cpu_sub_brand_modifier = '5' and
(cpu_model_number = '2600' or cpu_model_number = '3600' or cpu_model_number = '4500' or
cpu_model_number = '5500' or cpu_model_number = '5600' or cpu_model_number = '7600' or
cpu_model_number = '2500X' or cpu_model_number = '2600E' or cpu_model_number = '2600X' or
cpu_model_number = '3350G' or cpu_model_number = '3350GE' or
cpu_model_number = '3400G' or cpu_model_number = '3400GE' or cpu_model_number = '3450U' or
cpu_model_number = '3500' or cpu_model_number = '3500C' or cpu_model_number = '3500U' or
cpu_model_number = '3550H' or cpu_model_number = '3580U' or
cpu_model_number = '3600X' or cpu_model_number = '3600XT' or
cpu_model_number = '4500U' or
cpu_model_number = '4600U' or cpu_model_number = '4600GE' or cpu_model_number = '4600H' or cpu_model_number = '4600U' or
cpu_model_number = '5300G' or cpu_model_number = '5300GE' or
cpu_model_number = '5425U' or
cpu_model_number = '5500U' or cpu_model_number = '5560U' or
cpu_model_number = '5600G' or cpu_model_number = '5600GE' or cpu_model_number = '5600H' or cpu_model_number = '5600HS' or cpu_model_number = '5600U' or cpu_model_number = '5600X' or
cpu_model_number = '5625C' or cpu_model_number = '5625U' or
cpu_model_number = '6600H' or cpu_model_number = '6600HS' or cpu_model_number = '6600U' or
cpu_model_number = '7430U' or
cpu_model_number = '7520U' or cpu_model_number = '7530U' or cpu_model_number = '7535HS' or cpu_model_number = '7535U' or cpu_model_number = '7540U' or
cpu_model_number = '7600X' or cpu_model_number = '7640H' or cpu_model_number = '7640HS' or cpu_model_number = '7640S' or cpu_model_number = '7640U' or cpu_model_number = '7645HX' or
cpu_model_number = '8500G' or cpu_model_number = '8600G'
)
then 'Y'
when cpu_sub_brand_modifier = '5 PRO' and
(cpu_model_number = '2600' or cpu_model_number = '3600' or cpu_model_number = '5645' or
cpu_model_number = '3350G' or cpu_model_number = '3350GE' or
cpu_model_number = '3400G' or cpu_model_number = '3400GE' or
cpu_model_number = '3500U' or
cpu_model_number = '4650G' or cpu_model_number = '4650GE' or cpu_model_number = '4650U' or cpu_model_number = '4655G' or
cpu_model_number = '5650G' or cpu_model_number = '5650GE' or cpu_model_number = '5650HS' or cpu_model_number = '5650HX' or cpu_model_number = '5650U' or cpu_model_number = '5675U' or
cpu_model_number = '5750G' or cpu_model_number = '5750GE' or
cpu_model_number = '6650H' or cpu_model_number = '6650HS' or cpu_model_number = '6650U' or
cpu_model_number = '7530U' or cpu_model_number = '7535U' or cpu_model_number = '7540U' or
cpu_model_number = '7640U' or
cpu_model_number = '8500G' or
cpu_model_number = '8600G' or cpu_model_number = '8600GE' or cpu_model_number = '8640HS' or cpu_model_number = '8640U' or cpu_model_number = '8645HS'
)
then 'Y'
when cpu_sub_brand_modifier = '7' and
(cpu_model_number = '2700' or cpu_model_number = '5800' or cpu_model_number = '7700' or
cpu_model_number = '2700E' or cpu_model_number = '2700X' or
cpu_model_number = '3700C' or cpu_model_number = '3700U' or cpu_model_number = '3700X' or cpu_model_number = '3750H' or cpu_model_number = '3780U' or
cpu_model_number = '3800X' or cpu_model_number = '3800XT' or
cpu_model_number = '4700G' or cpu_model_number = '4700GE' or cpu_model_number = '4700U' or
cpu_model_number = '4800H' or cpu_model_number = '4800HS' or cpu_model_number = '4800U' or
cpu_model_number = '5700G' or cpu_model_number = '5700GE' or cpu_model_number = '5700U' or cpu_model_number = '5700X' or
cpu_model_number = '5800H' or cpu_model_number = '5800HS' or cpu_model_number = '5800U' or cpu_model_number = '5800X' or
cpu_model_number = '5800X3D' or cpu_model_number = '5825C' or cpu_model_number = '5825U' or
cpu_model_number = '6800H' or cpu_model_number = '6800HS' or cpu_model_number = '6800U' or cpu_model_number = '6810U' or
cpu_model_number = '7700' or cpu_model_number = '7700X' or cpu_model_number = '7730U' or cpu_model_number = '7735HS' or
cpu_model_number = '7735U' or cpu_model_number = '7736U' or cpu_model_number = '7745HX' or
cpu_model_number = '7800X3D' or cpu_model_number = '7840H' or cpu_model_number = '7840HS' or
cpu_model_number = '7840S' or cpu_model_number = '7840U' or
cpu_model_number = '8700G')
then 'Y'
when cpu_sub_brand_modifier = '7 PRO' and
(cpu_model_number = '2700' or cpu_model_number = '2700X' or cpu_model_number = '3700' or cpu_model_number = '3700U' or
cpu_model_number = '4750G' or cpu_model_number = '4750GE' or cpu_model_number = '4750U' or
cpu_model_number = '5845' or cpu_model_number = '5850HS' or cpu_model_number = '5850HX' or cpu_model_number = '5850U' or
cpu_model_number = '5875U' or cpu_model_number = '6850H' or cpu_model_number = '6850H' or cpu_model_number = '6850HS' or
cpu_model_number = '6850U' or cpu_model_number = '6860Z' or cpu_model_number = '7730U' or cpu_model_number = '7840HS' or cpu_model_number = '7840U' or
cpu_model_number = '8700G' or cpu_model_number = '8840HS' or cpu_model_number = '8840U' or cpu_model_number = '8845HS' or
cpu_model_number = '8945HS')
then 'Y'
when cpu_sub_brand_modifier = '9' and
(cpu_model_number = '3900' or cpu_model_number = '3900X' or cpu_model_number = '3900XT' or cpu_model_number = '3950X' or
cpu_model_number = '4900H' or cpu_model_number = '4900HS' or
cpu_model_number = '5900' or cpu_model_number = '5900HS' or cpu_model_number = '5900HX' or cpu_model_number = '5900X' or
cpu_model_number = '5950X' or cpu_model_number = '5980HS' or cpu_model_number = '5980HX' or
cpu_model_number = '6900HS' or cpu_model_number = '6980HS' or cpu_model_number = '6980HX' or
cpu_model_number = '7900' or cpu_model_number = '7900X' or cpu_model_number = '7900X3D' or cpu_model_number = '7940H' or
cpu_model_number = '7940HS' or cpu_model_number = '7945HX' or cpu_model_number = '7950X' or cpu_model_number = '7950X3D'
)
then 'Y'
when cpu_sub_brand_modifier = '9 PRO' and
(cpu_model_number = '3900' or cpu_model_number = '5945' or
cpu_model_number = '6950H' or cpu_model_number = '6950HS')
then 'Y'
when cpu_sub_brand_modifier = 'Embedded' and
(cpu_model_number = 'V2516' or cpu_model_number = 'V2546' or
cpu_model_number = 'V2718' or cpu_model_number = 'V2748')
then 'Y'
when cpu_sub_brand_modifier = 'Threadripper' and
(cpu_model_number = '2920X' or cpu_model_number = '2950X' or cpu_model_number = '2970WX' or
cpu_model_number = '2990WX' or cpu_model_number = '3960X' or cpu_model_number = '3970X' or
cpu_model_number = '3990X')
then 'Y'
when cpu_sub_brand_modifier = 'Threadripper PRO' and
(cpu_model_number = '3945WX' or cpu_model_number = '3955WZ' or cpu_model_number = '3975WX' or cpu_model_number = '3995WX' or
cpu_model_number = '5945WX' or cpu_model_number = '5955WZ' or cpu_model_number = '5965WZ' or cpu_model_number = '5975WX' or cpu_model_number = '5995WX'
)
then 'Y'
else 'N'
end -- AMD Ryzen's cpu_sub_brand_modifier
else -- Unsupported AMD cpu_sub_brand
'N'
end
else -- Unsupported cpu_brand
'N'
end as cpu_support_status
from base_sql s
),
status_sql as (
select
s.*,
case
-- First check for incompatible hardware/software
when (node_cpu_speed_ghz < 1.0)
or (node_per_cpu_cores < 2)
or (ram_alloc_gb < 4.0)
or (node_cpu_bitwidth is not null and node_cpu_bitwidth < 64.0)
or (node_disk0_c_free_gb < 20.0)
-- or (node_gpu_color_depth is not null and node_gpu_color_depth < 8)
or (os_dx12orwddm2 is not null and os_dx12orwddm2 is false)
-- or (max_display_res_y is not null and max_display_res_y < 720)
-- or (max_display_size_in_inches is not null and max_display_size_in_inches < 9)
or (machine_bios_uefi_capable is not null and machine_bios_uefi_capable is false)
or (machine_secure_boot is not null and machine_secure_boot = 'Not supported')
or (machine_tpm_specification is not null and machine_tpm_specification is false)
or (cpu_support_status = 'N')
then 'Unsupported'
-- Then check for partial/maybe support (when values are NULL)
when
node_cpu_bitwidth is null
or (node_disk0_c_free_gb is null)
-- or node_gpu_color_depth is null
or os_dx12orwddm2 is null
-- or max_display_res_y is null
-- or max_display_size_in_inches is null
or machine_bios_uefi_capable is null
or machine_secure_boot is null
or machine_tpm_specification is null
or (cpu_support_status is null)
then 'Partial'
-- Otherwise it's supported
else 'Supported'
end as win11_inplace_upgrade_label,
case
-- First check for incompatible hardware/software
when (node_cpu_speed_ghz < 1.0)
or (node_per_cpu_cores < 2)
or (ram_alloc_gb < 4.0)
or (node_cpu_bitwidth is not null and node_cpu_bitwidth < 64.0)
or (node_disk0_c_size_gb < 64.0)
-- or (node_gpu_color_depth is not null and node_gpu_color_depth < 8)
or (os_dx12orwddm2 is not null and os_dx12orwddm2 is false)
-- or (max_display_res_y is not null and max_display_res_y < 720)
-- or (max_display_size_in_inches is not null and max_display_size_in_inches < 9)
or (machine_bios_uefi_capable is not null and machine_bios_uefi_capable is false)
or (machine_secure_boot is not null and machine_secure_boot = 'Not supported')
or (machine_tpm_specification is not null and machine_tpm_specification is false)
or (cpu_support_status = 'N')
then 'Unsupported'
-- Then check for partial/maybe support (when values are NULL)
when node_cpu_bitwidth is null
or (node_disk0_c_size_gb is null)
-- or node_gpu_color_depth is null
or os_dx12orwddm2 is null
-- or max_display_res_y is null
-- or max_display_size_in_inches is null
or machine_bios_uefi_capable is null
or machine_secure_boot is null
or machine_tpm_specification is null
or (cpu_support_status is null)
then 'Partial'
-- Otherwise it's supported
else 'Supported'
end as win11_image_upgrade_label
from base_with_cpu_support_sql s
)
select * from status_sql;]]>2.0In11200UnknownIn21200UnknownIn31200Unknown]]>sans-serifLandscapeMasterPagetrue100%xlstrueodstruepostscripttruexls_spudsofttruexlsxtrueodstruexls_spudsofttruexlstruehtmltrue1pt0pt0pt0ptLandscapeMasterPagetruetrue100%xlsxtrueodstruexls_spudsofttruexlstrue50%sans-serif50%#000000solidmedium#000000solidmedium#000000solidmedium21#000000solidmedium#000000solidmedium21#000000dotted1px#000000solidmedium#000000solidmedium#000000solid1px21silver#000000solidmedium#000000solidmediumsilverReportSelectionCriteriasans-serif10pt1ptleftvar grp = params["MachineGroup"];
if (grp == "%") { grp = "All Machines"; }
"Machine group: " + grp;htmlsilver#000000solidmedium#000000solidmedium#000000solidmedium21silverReportTableDataRowsans-serif10pt5ptleft"Filters: Machines observed within last " + params["ThresholdValue"].value + " days, Physical machines, and Windows 10 and older machines";html10pt1pt1ptsans-serif0ptLandscapeMasterPagetrue100%xlsxtrueodstruexls_spudsofttruexlstruesilver#000000solidmedium#000000solidmedium#000000solidthin#000000solidmediumcenter2.6.1Pie ChartStandard0.00.00.00.03.03.03.03.0-1-1-1-11255000falsetrue0.00.00.00.03.03.03.03.0-1-1-1-11255000falsetrue550255000false0.00.00.00.00.00.00.00.0West3.03.03.03.0-1-1-1-11255000falsetrue0255000false2.02.02.02.0VerticalTop_Bottom1255000trueRightCategories
<Caption>
<Value></Value>
<Font>
<Alignment/>
</Font>
</Caption>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>0</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>2.0</Left>
<Bottom>0.0</Bottom>
<Right>3.0</Right>
</Insets>
<Visible>false</Visible>
Above0.00.0212.0130.03.03.03.03.0-1-1-1-11255000false255192192192trueTwo_DimensionalPoints10.00enable.area.altfalse'A','B','C','D','E'6,4,12,8,100false
NO_DATA_FOUND=No matching records found for the specified report filters.SansSerifCenterCenter