Программерское, опять с работы
Настоящий enterprise-программист это тот, который не начинает плакать горькими слезами при виде вот этого:
SELECT DISTINCT
io.Id vmId,
rhHost.CpuClockSpeed AS rhHost_CpuClockSpeed,
cpuMaxPeak.PeakValue AS cpuMaxPeak_PeakValue,
app.ImportanceLevel AS app_ImportanceLevel,
NULL AS rhHost_Version,
vwvm.ToolsStatus AS vwvm_ToolsStatus,
rhvm.NumberOfSockets * rhvm.NumberOfCores AS NumberOfCPU,
snapCountTable.HasSnap AS snapCountTable_HasSnap,
CASE
WHEN hvvm.DynamicMemoryEnabled=:trueValue THEN hvvm.DynamicMemoryMaximumMb
ELSE NULL END AS DynamicMemoryMaximum,
rhvm.MemoryReservationKb AS rhvm_MemoryReservationKb,
vwvm.ToolsVersion AS vwvm_ToolsVersion,
memMaxPeak.PeakValue AS memMaxPeak_PeakValue,
ve.UserName AS ve_UserName,
rhHost.NumberOfCores AS rhHost_NumberOfCores,
rhvm.Template AS rhvm_Template,
rhvm.IpAddress AS rhvm_IpAddress,
annot.Value AS annot_Value,
vmFiles.SizeBytes AS vmFiles_SizeBytes,
rhHost.NumberOfSockets AS rhHost_NumberOfSockets,
io.Name AS io_Name,
srcVm.Name AS srcVm_Name,
rhvm.CpuLimitMhz AS rhvm_CpuLimitMhz,
diskMaxPeak.PeakValue AS diskMaxPeak_PeakValue,
memRes.AvgValue AS memRes_AvgValue
rhvm.CreationTime AS rhvm_CreationTime
ve.HostName AS ve_HostName
rhClusterIo.Name AS rhClusterIo_Name
gdTbl.capCol AS gdTbl_capCol
gdTbl.fsCol AS gdTbl_fsCol
rhvm.HighAvailabilityEnabled AS rhvm_HighAvailabilityEnabled
hvvm.PagingFileSizeKb AS hvvm_PagingFileSizeKb
hostProp.HardwareModel AS hostProp_HardwareModel
vwvm.MemorySharingLevel AS vwvm_MemorySharingLevel
cpuRes.AvgValue AS cpuRes_AvgValue
vwvm.VmVersion AS vwvm_VmVersion
rhsrcHost.Name AS rhsrcHost_Name
snapSizeTable.TotalSize AS snapSizeTable_TotalSize
NULL AS rhvm_CpuReservationMhz
rhvm.PowerState AS rhvm_PowerState
vwvm.MemoryLimitKb AS vwvm_MemoryLimitKb
rhHost.Address AS rhHost_Address
rhvm.MemorySizeKb AS rhvm_MemorySizeKb
rhHost.MemorySize AS rhHost_MemorySize
CASE WHEN hvvm.DynamicMemoryEnabled=:trueValue THEN hvvm.MemorySizeKb ELSE NULL END AS DynamicMemoryMinimum
diskRes.AvgValue AS diskRes_AvgValue
rhvm.GuestOSType AS rhvm_GuestOSType
vwvm.CpuSharingLevel AS vwvm_CpuSharingLevel
apptype.Name AS apptype_Name
rhvm.CpuTotalMhz AS rhvm_CpuTotalMhz
rhPool.Name AS rhPool_Name
hostProp.HardwareVendor AS hostProp_HardwareVendor
FROM vk_inventory_object io
LEFT JOIN vk_inventory_tree rhioParentHV ON
io.Id = rhioParentHV.ItemId And rhioParentHV.EndDate IS NULL
AND rhioParentHV.Type = 'REDHAT_HOST_VIEW'
LEFT JOIN vk_inventory_object rhsrcHost ON
rhsrcHost.Id = rhioParentHV.ParentId AND rhsrcHost.Type = 'RH_HOST'
LEFT JOIN vk_inventory_rh_host rhHost ON rhHost.Id = rhioParentHV.ParentId
LEFT JOIN
(SELECT MAX(cpuPeak.AvgUsage) PeakValue cpuRes.InventoryId VmId FROM vk_inv_peek_result cpuPeak
INNER JOIN vk_inv_predictive_result cpuRes ON cpuRes.Id = cpuPeak.ResultId
AND cpuRes.PredictionPeriod = :inventoryAvgPeriod AND cpuRes.PartitionId IS NULL
AND cpuRes.StorageId IS NULL
AND cpuRes.IsValid = :trueValue
AND cpuRes.Type = 'CPU'
AND cpuRes.ClassType = 'CURRENT'
GROUP BY cpuRes.InventoryId) cpuMaxPeak ON
cpuMaxPeak.VmId = io.Id
INNER JOIN vk_application app
ON app.ConnectionId = io.ConnectionId AND app.Uuid = io.ExternalId
LEFT JOIN vk_inventory_vm vwvm ON io.Id = vwvm.Id
LEFT JOIN vk_inventory_rh_vm rhvm ON io.Id = rhvm.Id
LEFT JOIN
(select distinct io.Id as Id case when s.Id IS null then :falseValue else :trueValue end as HasSnap
from vk_inventory_object io
left join vk_snapshot s on s.InvId = io.Id and io.Type = 'VMWARE_VIRTUAL_MACHINE' )
snapCountTable ON snapCountTable.Id = io.Id
LEFT JOIN vk_inventory_hv_vm hvvm ON io.Id = hvvm.Id
LEFT JOIN
(SELECT MAX(memPeak.AvgUsage) PeakValue memRes.InventoryId VmId
FROM vk_inv_peek_result memPeak
INNER JOIN vk_inv_predictive_result memRes ON memRes.Id = memPeak.ResultId
INNER JOIN vk_inventory_object io1 ON io1.Id = memRes.InventoryId
WHERE memRes.PredictionPeriod = :inventoryAvgPeriod AND memRes.PartitionId IS NULL
AND memRes.StorageId IS NULL AND memRes.IsValid = :trueValue
AND memRes.Type = CASE io1.Type
WHEN N'VMWARE_VIRTUAL_MACHINE' THEN 'MEMCONS' WHEN N'HYPERV_VIRTUAL_MACHINE' THEN 'MEMORY'
WHEN N'RH_VIRTUAL_MACHINE' THEN 'MEMORY' ELSE NULL END AND memRes.ClassType = 'CURRENT'
GROUP BY memRes.InventoryId)
memMaxPeak ON memMaxPeak.VmId = io.Id
LEFT JOIN vk_event ve ON ve.ConnectionId = io.ConnectionId
AND io.ExternalId = ve.VmExternalId AND (ve.EventVMWareType in ('VmClonedEvent' 'VmDeployedEvent' 'VmCreatedEvent'))
LEFT JOIN vk_inventory_stattr_text annot ON io.Id = annot.InventoryId
LEFT JOIN
(SELECT SUM(vfi.SizeBytes) SizeBytes vvf.InventoryId VmId FROM vk_vmware_vm_file vvf
INNER JOIN vk_vmware_fileinfo vfi ON vfi.Id = vvf.FileId
AND (vfi.IsDeleted IS NULL OR vfi.IsDeleted <> :trueValue)
GROUP BY vvf.InventoryId) vmFiles ON vmFiles.VmId = io.Id
LEFT JOIN vk_event_create vce ON ve.Id = vce.Id
LEFT JOIN vk_inventory_object srcVm ON srcVm.ExternalId = vce.SrcVmExternalId AND ve.ConnectionId = srcVm.ConnectionId
LEFT JOIN
(SELECT MAX(diskPeak.AvgUsage) PeakValue diskRes.InventoryId VmId
FROM vk_inv_peek_result diskPeak
INNER JOIN vk_inv_predictive_result diskRes ON diskRes.Id = diskPeak.ResultId
AND diskRes.PredictionPeriod = :inventoryAvgPeriod
AND diskRes.PartitionId IS NULL AND diskRes.StorageId IS NULL
AND diskRes.IsValid = :trueValue AND diskRes.Type = 'DISKALL'
AND diskRes.ClassType = 'CURRENT' GROUP BY diskRes.InventoryId)
diskMaxPeak ON diskMaxPeak.VmId = io.Id
LEFT JOIN vk_inv_predictive_result memRes ON io.Id = memRes.InventoryId
AND memRes.PartitionId IS NULL AND memRes.StorageId IS NULL
AND memRes.IsValid = :trueValue
AND memRes.Type = CASE io.Type
WHEN N'VMWARE_VIRTUAL_MACHINE' THEN 'MEMCONS' WHEN N'HYPERV_VIRTUAL_MACHINE' THEN 'MEMORY'
WHEN N'RH_VIRTUAL_MACHINE' THEN 'MEMORY' ELSE NULL END AND memRes.ClassType = 'CURRENT'
AND memRes.PredictionPeriod = :inventoryAvgPeriod
LEFT JOIN vk_inventory_tree rhClusterTree ON rhClusterTree.ItemId = rhioParentHV.ParentId
AND rhClusterTree.EndDate IS NULL AND rhClusterTree.Type = 'REDHAT_HOST_VIEW'
LEFT JOIN vk_inventory_object rhClusterIo ON rhClusterIo.Id = rhClusterTree.ParentId AND rhClusterIo.Type = 'RH_CLUSTER'
LEFT JOIN
(SELECT hvgd.VmId VmId SUM(hvgd.capacityBytes) capCol
SUM(hvgd.freeSpaceBytes) fsCol FROM vk_vmware_guest_disk hvgd
GROUP BY hvgd.VmId) gdTbl ON gdTbl.VmId = io.Id
LEFT JOIN vk_inventory_tree ioParentHV ON io.Id = ioParentHV.ItemId AND ioParentHV.EndDate IS NULL
AND ioParentHV.Type = 'VMWARE_HOST_VIEW' LEFT JOIN vk_inventory_object srcHost
ON srcHost.Id = ioParentHV.ParentId AND srcHost.Type = 'VMWARE_HOST' LEFT
JOIN vk_inventory_computeresource hostProp ON hostProp.Id = srcHost.Id
LEFT JOIN vk_inv_predictive_result cpuRes ON io.Id = cpuRes.InventoryId
AND cpuRes.PartitionId IS NULL AND cpuRes.StorageId IS NULL AND cpuRes.IsValid = :trueValue
AND cpuRes.Type = 'CPU' AND cpuRes.ClassType = 'CURRENT' AND cpuRes.PredictionPeriod = :inventoryAvgPeriod
LEFT JOIN
( SELECT InvId as InvId SUM(SizeBytes) as TotalSize
FROM vk_snapshot s vk_snapshot_file sf vk_vmware_fileinfo fi
WHERE s.Id = sf.SnapshotId AND sf.FileId=fi.Id GROUP BY InvId )
snapSizeTable ON snapSizeTable.InvId = io.Id
LEFT JOIN vk_inv_predictive_result diskRes ON io.Id = diskRes.InventoryId
AND diskRes.PartitionId IS NULL AND diskRes.StorageId IS NULL
AND diskRes.IsValid = :trueValue AND diskRes.Type = 'DISKALL'
AND diskRes.ClassType = 'CURRENT' AND diskRes.PredictionPeriod = :inventoryAvgPeriod
LEFT JOIN vk_io_app_type appapptype on io.Id=appapptype.InventoryId
LEFT JOIN vk_application_type apptype on appapptype.AppTypeId = apptype.Id
LEFT JOIN vk_inventory_tree rhPoolTree ON rhPoolTree.ItemId = io.Id AND rhPoolTree.EndDate IS NULL
AND rhPoolTree.Type = 'REDHAT_VIEW'
LEFT JOIN vk_inventory_object rhPool ON rhPool.Id = rhPoolTree.ParentId AND rhPool.Type = 'RH_POOL'
WHERE (io.Id IN (3684,3697,3709,3728,3740,3752,3776,3788,3800,3812,3825,3837,3907,3976,4046,4116,4134,4158,4171))
Мне до такого звания еще далеко.
SELECT DISTINCT
io.Id vmId,
rhHost.CpuClockSpeed AS rhHost_CpuClockSpeed,
cpuMaxPeak.PeakValue AS cpuMaxPeak_PeakValue,
app.ImportanceLevel AS app_ImportanceLevel,
NULL AS rhHost_Version,
vwvm.ToolsStatus AS vwvm_ToolsStatus,
rhvm.NumberOfSockets * rhvm.NumberOfCores AS NumberOfCPU,
snapCountTable.HasSnap AS snapCountTable_HasSnap,
CASE
WHEN hvvm.DynamicMemoryEnabled=:trueValue THEN hvvm.DynamicMemoryMaximumMb
ELSE NULL END AS DynamicMemoryMaximum,
rhvm.MemoryReservationKb AS rhvm_MemoryReservationKb,
vwvm.ToolsVersion AS vwvm_ToolsVersion,
memMaxPeak.PeakValue AS memMaxPeak_PeakValue,
ve.UserName AS ve_UserName,
rhHost.NumberOfCores AS rhHost_NumberOfCores,
rhvm.Template AS rhvm_Template,
rhvm.IpAddress AS rhvm_IpAddress,
annot.Value AS annot_Value,
vmFiles.SizeBytes AS vmFiles_SizeBytes,
rhHost.NumberOfSockets AS rhHost_NumberOfSockets,
io.Name AS io_Name,
srcVm.Name AS srcVm_Name,
rhvm.CpuLimitMhz AS rhvm_CpuLimitMhz,
diskMaxPeak.PeakValue AS diskMaxPeak_PeakValue,
memRes.AvgValue AS memRes_AvgValue
rhvm.CreationTime AS rhvm_CreationTime
ve.HostName AS ve_HostName
rhClusterIo.Name AS rhClusterIo_Name
gdTbl.capCol AS gdTbl_capCol
gdTbl.fsCol AS gdTbl_fsCol
rhvm.HighAvailabilityEnabled AS rhvm_HighAvailabilityEnabled
hvvm.PagingFileSizeKb AS hvvm_PagingFileSizeKb
hostProp.HardwareModel AS hostProp_HardwareModel
vwvm.MemorySharingLevel AS vwvm_MemorySharingLevel
cpuRes.AvgValue AS cpuRes_AvgValue
vwvm.VmVersion AS vwvm_VmVersion
rhsrcHost.Name AS rhsrcHost_Name
snapSizeTable.TotalSize AS snapSizeTable_TotalSize
NULL AS rhvm_CpuReservationMhz
rhvm.PowerState AS rhvm_PowerState
vwvm.MemoryLimitKb AS vwvm_MemoryLimitKb
rhHost.Address AS rhHost_Address
rhvm.MemorySizeKb AS rhvm_MemorySizeKb
rhHost.MemorySize AS rhHost_MemorySize
CASE WHEN hvvm.DynamicMemoryEnabled=:trueValue THEN hvvm.MemorySizeKb ELSE NULL END AS DynamicMemoryMinimum
diskRes.AvgValue AS diskRes_AvgValue
rhvm.GuestOSType AS rhvm_GuestOSType
vwvm.CpuSharingLevel AS vwvm_CpuSharingLevel
apptype.Name AS apptype_Name
rhvm.CpuTotalMhz AS rhvm_CpuTotalMhz
rhPool.Name AS rhPool_Name
hostProp.HardwareVendor AS hostProp_HardwareVendor
FROM vk_inventory_object io
LEFT JOIN vk_inventory_tree rhioParentHV ON
io.Id = rhioParentHV.ItemId And rhioParentHV.EndDate IS NULL
AND rhioParentHV.Type = 'REDHAT_HOST_VIEW'
LEFT JOIN vk_inventory_object rhsrcHost ON
rhsrcHost.Id = rhioParentHV.ParentId AND rhsrcHost.Type = 'RH_HOST'
LEFT JOIN vk_inventory_rh_host rhHost ON rhHost.Id = rhioParentHV.ParentId
LEFT JOIN
(SELECT MAX(cpuPeak.AvgUsage) PeakValue cpuRes.InventoryId VmId FROM vk_inv_peek_result cpuPeak
INNER JOIN vk_inv_predictive_result cpuRes ON cpuRes.Id = cpuPeak.ResultId
AND cpuRes.PredictionPeriod = :inventoryAvgPeriod AND cpuRes.PartitionId IS NULL
AND cpuRes.StorageId IS NULL
AND cpuRes.IsValid = :trueValue
AND cpuRes.Type = 'CPU'
AND cpuRes.ClassType = 'CURRENT'
GROUP BY cpuRes.InventoryId) cpuMaxPeak ON
cpuMaxPeak.VmId = io.Id
INNER JOIN vk_application app
ON app.ConnectionId = io.ConnectionId AND app.Uuid = io.ExternalId
LEFT JOIN vk_inventory_vm vwvm ON io.Id = vwvm.Id
LEFT JOIN vk_inventory_rh_vm rhvm ON io.Id = rhvm.Id
LEFT JOIN
(select distinct io.Id as Id case when s.Id IS null then :falseValue else :trueValue end as HasSnap
from vk_inventory_object io
left join vk_snapshot s on s.InvId = io.Id and io.Type = 'VMWARE_VIRTUAL_MACHINE' )
snapCountTable ON snapCountTable.Id = io.Id
LEFT JOIN vk_inventory_hv_vm hvvm ON io.Id = hvvm.Id
LEFT JOIN
(SELECT MAX(memPeak.AvgUsage) PeakValue memRes.InventoryId VmId
FROM vk_inv_peek_result memPeak
INNER JOIN vk_inv_predictive_result memRes ON memRes.Id = memPeak.ResultId
INNER JOIN vk_inventory_object io1 ON io1.Id = memRes.InventoryId
WHERE memRes.PredictionPeriod = :inventoryAvgPeriod AND memRes.PartitionId IS NULL
AND memRes.StorageId IS NULL AND memRes.IsValid = :trueValue
AND memRes.Type = CASE io1.Type
WHEN N'VMWARE_VIRTUAL_MACHINE' THEN 'MEMCONS' WHEN N'HYPERV_VIRTUAL_MACHINE' THEN 'MEMORY'
WHEN N'RH_VIRTUAL_MACHINE' THEN 'MEMORY' ELSE NULL END AND memRes.ClassType = 'CURRENT'
GROUP BY memRes.InventoryId)
memMaxPeak ON memMaxPeak.VmId = io.Id
LEFT JOIN vk_event ve ON ve.ConnectionId = io.ConnectionId
AND io.ExternalId = ve.VmExternalId AND (ve.EventVMWareType in ('VmClonedEvent' 'VmDeployedEvent' 'VmCreatedEvent'))
LEFT JOIN vk_inventory_stattr_text annot ON io.Id = annot.InventoryId
LEFT JOIN
(SELECT SUM(vfi.SizeBytes) SizeBytes vvf.InventoryId VmId FROM vk_vmware_vm_file vvf
INNER JOIN vk_vmware_fileinfo vfi ON vfi.Id = vvf.FileId
AND (vfi.IsDeleted IS NULL OR vfi.IsDeleted <> :trueValue)
GROUP BY vvf.InventoryId) vmFiles ON vmFiles.VmId = io.Id
LEFT JOIN vk_event_create vce ON ve.Id = vce.Id
LEFT JOIN vk_inventory_object srcVm ON srcVm.ExternalId = vce.SrcVmExternalId AND ve.ConnectionId = srcVm.ConnectionId
LEFT JOIN
(SELECT MAX(diskPeak.AvgUsage) PeakValue diskRes.InventoryId VmId
FROM vk_inv_peek_result diskPeak
INNER JOIN vk_inv_predictive_result diskRes ON diskRes.Id = diskPeak.ResultId
AND diskRes.PredictionPeriod = :inventoryAvgPeriod
AND diskRes.PartitionId IS NULL AND diskRes.StorageId IS NULL
AND diskRes.IsValid = :trueValue AND diskRes.Type = 'DISKALL'
AND diskRes.ClassType = 'CURRENT' GROUP BY diskRes.InventoryId)
diskMaxPeak ON diskMaxPeak.VmId = io.Id
LEFT JOIN vk_inv_predictive_result memRes ON io.Id = memRes.InventoryId
AND memRes.PartitionId IS NULL AND memRes.StorageId IS NULL
AND memRes.IsValid = :trueValue
AND memRes.Type = CASE io.Type
WHEN N'VMWARE_VIRTUAL_MACHINE' THEN 'MEMCONS' WHEN N'HYPERV_VIRTUAL_MACHINE' THEN 'MEMORY'
WHEN N'RH_VIRTUAL_MACHINE' THEN 'MEMORY' ELSE NULL END AND memRes.ClassType = 'CURRENT'
AND memRes.PredictionPeriod = :inventoryAvgPeriod
LEFT JOIN vk_inventory_tree rhClusterTree ON rhClusterTree.ItemId = rhioParentHV.ParentId
AND rhClusterTree.EndDate IS NULL AND rhClusterTree.Type = 'REDHAT_HOST_VIEW'
LEFT JOIN vk_inventory_object rhClusterIo ON rhClusterIo.Id = rhClusterTree.ParentId AND rhClusterIo.Type = 'RH_CLUSTER'
LEFT JOIN
(SELECT hvgd.VmId VmId SUM(hvgd.capacityBytes) capCol
SUM(hvgd.freeSpaceBytes) fsCol FROM vk_vmware_guest_disk hvgd
GROUP BY hvgd.VmId) gdTbl ON gdTbl.VmId = io.Id
LEFT JOIN vk_inventory_tree ioParentHV ON io.Id = ioParentHV.ItemId AND ioParentHV.EndDate IS NULL
AND ioParentHV.Type = 'VMWARE_HOST_VIEW' LEFT JOIN vk_inventory_object srcHost
ON srcHost.Id = ioParentHV.ParentId AND srcHost.Type = 'VMWARE_HOST' LEFT
JOIN vk_inventory_computeresource hostProp ON hostProp.Id = srcHost.Id
LEFT JOIN vk_inv_predictive_result cpuRes ON io.Id = cpuRes.InventoryId
AND cpuRes.PartitionId IS NULL AND cpuRes.StorageId IS NULL AND cpuRes.IsValid = :trueValue
AND cpuRes.Type = 'CPU' AND cpuRes.ClassType = 'CURRENT' AND cpuRes.PredictionPeriod = :inventoryAvgPeriod
LEFT JOIN
( SELECT InvId as InvId SUM(SizeBytes) as TotalSize
FROM vk_snapshot s vk_snapshot_file sf vk_vmware_fileinfo fi
WHERE s.Id = sf.SnapshotId AND sf.FileId=fi.Id GROUP BY InvId )
snapSizeTable ON snapSizeTable.InvId = io.Id
LEFT JOIN vk_inv_predictive_result diskRes ON io.Id = diskRes.InventoryId
AND diskRes.PartitionId IS NULL AND diskRes.StorageId IS NULL
AND diskRes.IsValid = :trueValue AND diskRes.Type = 'DISKALL'
AND diskRes.ClassType = 'CURRENT' AND diskRes.PredictionPeriod = :inventoryAvgPeriod
LEFT JOIN vk_io_app_type appapptype on io.Id=appapptype.InventoryId
LEFT JOIN vk_application_type apptype on appapptype.AppTypeId = apptype.Id
LEFT JOIN vk_inventory_tree rhPoolTree ON rhPoolTree.ItemId = io.Id AND rhPoolTree.EndDate IS NULL
AND rhPoolTree.Type = 'REDHAT_VIEW'
LEFT JOIN vk_inventory_object rhPool ON rhPool.Id = rhPoolTree.ParentId AND rhPool.Type = 'RH_POOL'
WHERE (io.Id IN (3684,3697,3709,3728,3740,3752,3776,3788,3800,3812,3825,3837,3907,3976,4046,4116,4134,4158,4171))
Мне до такого звания еще далеко.